# Import/Export System - File Organization

## Overview
The import/export system is organized into three key locations:

### 1. Database Schema (`TAFDB.pgsql`)
**Location:** `/var/www/html/TAF/TAFDB.pgsql` (around line 176)

Contains the `import_export_configs` table definition for NEW databases.

```sql
CREATE TABLE import_export_configs (
  config_id         uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name              text NOT NULL UNIQUE,
  description       text,
  type              text NOT NULL CHECK (type IN ('import', 'export', 'both')),
  table_name        text NOT NULL,
  config            jsonb NOT NULL,
  ...
);
```

### 2. Migration File (`migrations/add_import_export_configs.sql`)
**Location:** `/var/www/html/TAF/migrations/add_import_export_configs.sql`

**Purpose:** For EXISTING databases that need to be updated with the new table.

**Usage:**
```bash
psql -U postgres -d your_database -f migrations/add_import_export_configs.sql
```

### 3. Configurations (`sql/import_export.sql`)
**Location:** `/var/www/html/TAF/sql/import_export.sql`

**⚠️ IMPORTANT: All import/export configurations MUST be added here.**

**Purpose:** Central location for ALL import/export configurations. This file is loaded after table creation.

**Usage:**
```bash
psql -U postgres -d your_database -f sql/import_export.sql
```

**Contents:**
- Products catalog configuration
- Restaurant products configuration
- Template for new configurations

## Adding New Configurations

### ✅ DO: Add to sql/import_export.sql

```sql
-- In sql/import_export.sql
INSERT INTO import_export_configs (name, description, type, table_name, config) VALUES 
(
  'your_config_name',
  'Description',
  'both',
  'table_name',
  '{ ... }'
) ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name,
  config = EXCLUDED.config,
  updatedAt = now();
```

### ❌ DON'T: Add to migration or TAFDB.pgsql

- Don't add configurations to `migrations/add_import_export_configs.sql` - this is only for the table structure
- Don't add configurations to `TAFDB.pgsql` - this is only for the table schema

## Setup Process

### For New Databases:
1. Run `TAFDB.pgsql` (table is already included)
2. Run `sql/import_export.sql` (loads configurations)

### For Existing Databases:
1. Run `migrations/add_import_export_configs.sql` (creates table)
2. Run `sql/import_export.sql` (loads configurations)

## Quick Reference

| File | Purpose | When to Edit |
|------|---------|--------------|
| `TAFDB.pgsql` | Table schema | Never (unless changing table structure) |
| `migrations/add_import_export_configs.sql` | Add table to old DB | Never (unless changing table structure) |
| `sql/import_export.sql` | Configurations | ✅ **Always - add new configs here!** |

## Documentation

- **Full API Documentation:** `docs/ImportExportService.md`
- **Quick Start Guide:** `docs/ImportExport_README.md`
- **This File:** File organization reference

## Example: Adding Employee Export

```sql
-- Add to sql/import_export.sql (at the bottom before closing comments)

INSERT INTO import_export_configs (name, description, type, table_name, config) VALUES 
(
  'employees_basic',
  'Export basic employee information',
  'export',
  'employees',
  '{
    "table": "employees",
    "primary_key": "employee_id",
    "filename": "employees_export",
    "columns": ["employee_id", "full_name", "email", "department_id"],
    "field_labels": {
      "employee_id": "Employee ID",
      "full_name": "Full Name",
      "email": "Email",
      "department_id": "Department"
    },
    "export_transforms": {
      "department_id": {
        "type": "lookup",
        "table": "departments",
        "key_column": "department_id",
        "display_column": "name"
      }
    },
    "filters": {
      "isDeleted": false
    },
    "order_by": {
      "full_name": "ASC"
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name,
  config = EXCLUDED.config,
  updatedAt = now();
```

Then reload:
```bash
psql -U postgres -d your_database -f sql/import_export.sql
```

And use immediately:
```bash
curl -X GET "http://localhost/api/import-export/export/employees_basic?format=csv"
```

## Key Points

✅ **sql/import_export.sql is the single source of truth for configurations**
✅ Configurations use `ON CONFLICT ... DO UPDATE` so they can be reloaded safely
✅ No code changes needed when adding new configurations
✅ Migration file is for database upgrades only
✅ Table schema is in TAFDB.pgsql for new installations