# Import/Export Service Documentation

## Overview

The Import/Export Service provides a configurable, reusable system for importing and exporting data across various tables in the application. It supports multiple file formats (CSV, Excel, JSON, XML) and can be configured through the database without code changes.

## Architecture

### Components

1. **ImportService** (`api/services/ImportService.php`)
   - Handles data import from various file formats
   - Supports field mapping, transformations, and validations
   - Can update existing records or insert new ones

2. **ExportService** (`api/services/ExportService.php`)
   - Handles data export to various file formats
   - Supports data transformation and formatting
   - Can include joins and filters

3. **ImportExportController** (`api/controllers/ImportExportController.php`)
   - Generic REST API endpoints for import/export operations
   - Manages configurations

4. **Configuration Table** (`import_export_configs`)
   - Stores all import/export configurations
   - JSON-based configuration for flexibility

## Database Schema

```sql
CREATE TABLE import_export_configs (
  config_id         uuid PRIMARY KEY,
  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,
  tenant_id         uuid,
  department_id     uuid,
  created_by        uuid,
  isDeleted         BOOLEAN DEFAULT FALSE,
  updatedAt         timestamptz DEFAULT now(),
  createdAt         timestamptz DEFAULT now()
);
```

## Configuration Format

### Basic Structure

```json
{
  "table": "products",
  "primary_key": "product_id",
  "filename": "products_catalog",
  "columns": ["product_id", "name", "price", "..."],
  "field_mappings": { ... },
  "field_labels": { ... },
  "transforms": { ... },
  "export_transforms": { ... },
  "validations": { ... },
  "unique_fields": ["barcode"],
  "update_existing": true,
  "filters": { ... },
  "joins": [ ... ],
  "order_by": { ... }
}
```

### Field Mappings

Maps import file columns to database columns:

```json
{
  "field_mappings": {
    "Product Name": "name",
    "Product Price": "price",
    "Category": {
      "column": "category_id",
      "default": null
    }
  }
}
```

### Transformations

#### Import Transformations

Transform data during import:

```json
{
  "transforms": {
    "price": {
      "type": "number",
      "decimals": 2
    },
    "is_active": {
      "type": "boolean",
      "true_values": ["yes", "1", "true"],
      "false_values": ["no", "0", "false"]
    },
    "created_date": {
      "type": "date",
      "input_format": "d/m/Y",
      "output_format": "Y-m-d H:i:s"
    },
    "category_id": {
      "type": "lookup",
      "table": "product_categories",
      "key_column": "name",
      "value_column": "category_id"
    },
    "tax_rates": {
      "type": "json"
    }
  }
}
```

**Available Transform Types:**
- `trim` - Remove whitespace
- `uppercase` - Convert to uppercase
- `lowercase` - Convert to lowercase
- `date` - Parse and format dates
- `number` - Parse numeric values
- `boolean` - Convert to boolean
- `json` - Encode as JSON
- `lookup` - Look up value from another table
- `default` - Use default if empty

#### Export Transformations

Transform data during export:

```json
{
  "export_transforms": {
    "price": {
      "type": "number",
      "decimals": 2,
      "thousands_separator": ",",
      "decimal_separator": "."
    },
    "is_active": {
      "type": "boolean",
      "true_value": "Yes",
      "false_value": "No"
    },
    "category_id": {
      "type": "lookup",
      "table": "product_categories",
      "key_column": "category_id",
      "display_column": "name"
    },
    "created_at": {
      "type": "date",
      "format": "Y-m-d H:i:s"
    },
    "metadata": {
      "type": "json_decode",
      "extract_field": "description"
    }
  }
}
```

### Validations

Validate import data:

```json
{
  "validations": {
    "name": {
      "required": true,
      "min_length": 3,
      "max_length": 255
    },
    "email": {
      "required": true,
      "pattern": "/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$/"
    },
    "price": {
      "required": false
    }
  }
}
```

### Joins

Include related data in exports:

```json
{
  "joins": [
    {
      "type": "left",
      "table": "product_categories",
      "first": "products.category_id",
      "operator": "=",
      "second": "product_categories.category_id"
    }
  ]
}
```

### Filters

Apply default filters:

```json
{
  "filters": {
    "isDeleted": false,
    "org_id": {
      "operator": "=",
      "value": "current_org"
    }
  }
}
```

## API Endpoints

### Generic Import/Export Endpoints

#### List Configurations
```http
GET /api/import-export/configs
```

Returns all available import/export configurations.

#### Export Data
```http
GET /api/import-export/export/{configName}?format=csv&filters[field]=value
```

**Parameters:**
- `configName` - Name of the export configuration
- `format` - Export format: `csv`, `excel`, `json`, `xml`
- `filters` - Optional query filters

**Example:**
```bash
curl -X GET "http://localhost/api/import-export/export/products_catalog?format=csv"
```

#### Import Data
```http
POST /api/import-export/import/{configName}
Content-Type: multipart/form-data
```

**Parameters:**
- `file` - The file to import (required)
- `update_existing` - Update existing records (default: true)
- `skip_errors` - Continue on errors (default: true)
- `format` - Force file format (optional)

**Example:**
```bash
curl -X POST "http://localhost/api/import-export/import/products_catalog" \
  -F "file=@products.csv" \
  -F "update_existing=true" \
  -F "skip_errors=true"
```

**Response:**
```json
{
  "success": true,
  "imported": 25,
  "updated": 10,
  "skipped": 2,
  "total_processed": 37,
  "errors": [
    "Row 15: Category 'Electronics' not found",
    "Row 23: Name is required"
  ],
  "warnings": []
}
```

#### Download Template
```http
GET /api/import-export/template/{configName}?format=csv
```

Downloads a blank template file for imports.

#### Create Configuration
```http
POST /api/import-export/configs
Content-Type: application/json
```

**Body:**
```json
{
  "name": "employees_export",
  "description": "Export employee data",
  "type": "export",
  "table_name": "employees",
  "config": {
    "table": "employees",
    "columns": ["employee_id", "full_name", "email"],
    "filters": { "isDeleted": false }
  }
}
```

#### Update Configuration
```http
PUT /api/import-export/configs/{configId}
Content-Type: application/json
```

#### Delete Configuration
```http
DELETE /api/import-export/configs/{configId}
```

### POS-Specific Endpoints

#### Export Products
```http
GET /api/pos/products/export?format=csv
```

#### Import Products
```http
POST /api/pos/products/import
Content-Type: multipart/form-data
```

#### Download Products Template
```http
GET /api/pos/products/template?format=csv
```

## Usage Examples

### Example 1: Export Products to CSV

```bash
curl -X GET "http://localhost/api/pos/products/export?format=csv" \
  -o products_export.csv
```

### Example 2: Import Products from Excel

```bash
curl -X POST "http://localhost/api/pos/products/import" \
  -F "file=@products.xlsx" \
  -F "update_existing=true"
```

### Example 3: Export with Filters

```bash
curl -X GET "http://localhost/api/import-export/export/products_catalog?format=json&filters[category_id]=12345" \
  -o filtered_products.json
```

### Example 4: Create Custom Configuration

```php
// PHP example
$config = [
    'name' => 'employees_basic',
    'description' => 'Basic employee information export',
    'type' => 'export',
    'table_name' => 'employees',
    'config' => [
        'table' => 'employees',
        'primary_key' => 'employee_id',
        'columns' => ['employee_id', 'full_name', 'email', 'department_id'],
        'field_labels' => [
            'employee_id' => 'Employee ID',
            'full_name' => 'Full Name',
            'email' => 'Email Address',
            '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'
        ]
    ]
];

// POST to /api/import-export/configs
```

## File Format Support

### CSV
- UTF-8 with BOM for Excel compatibility
- Comma-separated values
- Quoted fields for special characters
- Supports empty fields

### Excel (XLSX/XLS)
- Requires PhpSpreadsheet library
- Falls back to CSV if library not available
- Preserves formatting when exporting
- Reads first worksheet when importing

### JSON
- Array of objects format
- Pretty printing option available
- Supports nested structures
- UTF-8 encoded

### XML
- Configurable root and record tags
- Attribute support
- UTF-8 encoded
- Schema validation support

## Error Handling

The service provides detailed error reporting:

```json
{
  "success": true,
  "imported": 100,
  "updated": 50,
  "skipped": 5,
  "errors": [
    "Row 23: Name is required",
    "Row 45: Invalid date format",
    "Row 67: Category 'Unknown' not found"
  ],
  "warnings": [
    "Row 12: Column count mismatch",
    "Excel parsing library not available, using CSV fallback"
  ]
}
```

## Performance Considerations

1. **Large Files**: For files with >10,000 rows, consider:
   - Processing in batches
   - Using background jobs
   - Increasing PHP memory limit

2. **Lookups**: Cache lookup tables to avoid repeated queries

3. **Validation**: Disable non-critical validations for bulk imports

4. **Transactions**: Imports are not wrapped in transactions by default

## Security

1. **File Upload Validation**: Only allowed file types are accepted
2. **SQL Injection Prevention**: Uses parameterized queries
3. **Access Control**: Integrate with existing permission system
4. **Tenant Isolation**: Configurations can be tenant-specific

## Future Enhancements

- [ ] Background job processing for large imports
- [ ] Import preview before committing
- [ ] Duplicate detection strategies
- [ ] Import history and rollback
- [ ] Scheduled exports
- [ ] Email export results
- [ ] Advanced Excel features (charts, formulas)
- [ ] Template customization UI
- [ ] Data mapping wizard
- [ ] Import/export logs and audit trail

## Troubleshooting

### Issue: "Configuration not found"
- Ensure the configuration exists in `import_export_configs` table
- Check the configuration name matches exactly

### Issue: "Invalid file format"
- Verify file extension matches content
- Check for BOM in CSV files
- Ensure Excel files are not corrupted

### Issue: "Lookup failed"
- Verify lookup table exists
- Check lookup column names
- Ensure related records exist

### Issue: "Memory limit exceeded"
- Increase PHP memory limit in php.ini
- Process file in smaller batches
- Use streaming for large exports

## Integration with Existing Code

The service integrates seamlessly with the existing TAF architecture:

1. **Uses QueryBuilder**: All database operations use the existing QueryBuilder
2. **Follows Conventions**: Respects `isDeleted`, `updatedAt`, `tenant_id` patterns
3. **Session Integration**: Uses session data for `org_id`, `tenant_id`, `user_id`
4. **Permission Compatible**: Can be integrated with existing permission system
5. **Model-Aware**: Can read table structure from models