# Import/Export System Implementation

## Summary

A complete, configurable import/export system has been implemented that allows importing and exporting data for products, restaurant products, and any other table in the system through database-driven configurations.

## Files Created

### Services
- **`api/services/ImportService.php`** - Generic import service supporting CSV, Excel, JSON, XML
- **`api/services/ExportService.php`** - Generic export service supporting CSV, Excel, JSON, XML

### Controllers
- **`api/controllers/ImportExportController.php`** - Generic REST API for import/export operations
- **`api/controllers/PosController.php`** - Updated to use new services for product import/export

### Database
- **`TAFDB.pgsql`** - Main database schema (includes `import_export_configs` table)
- **`migrations/add_import_export_configs.sql`** - Migration for existing databases
- **`sql/import_export.sql`** - All import/export configurations (⚠️ add new configs here)
- **`models/import_export_configs.php`** - Model definition for configurations

### Documentation
- **`docs/ImportExportService.md`** - Complete documentation with examples

## Features

### Multi-Format Support
- ✅ CSV (with Excel UTF-8 BOM support)
- ✅ Excel (XLSX/XLS) - requires PhpSpreadsheet, falls back to CSV
- ✅ JSON (with pretty printing)
- ✅ XML (configurable tags)

### Data Transformation
- ✅ Field mapping (import headers to database columns)
- ✅ Type transformations (date, number, boolean, JSON)
- ✅ Lookup transformations (resolve IDs from names)
- ✅ Custom callbacks
- ✅ Default values

### Validation
- ✅ Required fields
- ✅ Min/max length
- ✅ Pattern matching (regex)
- ✅ Custom validation rules

### Import Features
- ✅ Create new records
- ✅ Update existing records (by unique fields)
- ✅ Skip errors or fail fast
- ✅ Detailed error reporting per row
- ✅ Warnings for non-fatal issues

### Export Features
- ✅ Filtered exports
- ✅ Joined tables
- ✅ Sorted output
- ✅ Custom field labels
- ✅ Data formatting

### Configuration
- ✅ Database-driven (no code changes needed)
- ✅ Tenant/department specific
- ✅ Reusable across tables
- ✅ JSON-based for flexibility

## Quick Start

### 1. Setup Database

**For NEW databases:**
The `import_export_configs` table is already included in `TAFDB.pgsql`.

**For EXISTING databases (migrations):**
```bash
psql -U postgres -d your_database -f migrations/add_import_export_configs.sql
```

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

This loads all import/export configurations including products catalog.

### 2. API Endpoints

#### Export Products
```bash
# Export as CSV
curl -X GET "http://localhost/api/pos/products/export?format=csv" -o products.csv

# Export as JSON
curl -X GET "http://localhost/api/pos/products/export?format=json" -o products.json

# Export as Excel
curl -X GET "http://localhost/api/pos/products/export?format=excel" -o products.xlsx
```

#### Import Products
```bash
curl -X POST "http://localhost/api/pos/products/import" \
  -F "file=@products.csv" \
  -F "update_existing=true" \
  -F "skip_errors=true"
```

#### Download Template
```bash
curl -X GET "http://localhost/api/pos/products/template?format=csv" -o template.csv
```

### 3. Generic Endpoints

#### List All Configurations
```bash
curl -X GET "http://localhost/api/import-export/configs"
```

#### Use Any Configuration
```bash
# Export using any config
curl -X GET "http://localhost/api/import-export/export/products_catalog?format=csv"

# Import using any config
curl -X POST "http://localhost/api/import-export/import/restaurant_products" \
  -F "file=@menu_items.xlsx"
```

## Configuration Examples

### Products Configuration (Already Included)

Configurations are defined in `sql/import_export.sql`:
- Maps CSV headers to database columns
- Handles category lookups
- Transforms VAT/STT tax rates
- Validates required fields
- Updates existing products by barcode

### Creating a Custom Configuration

**⚠️ Important: All import/export configurations should be added to `sql/import_export.sql`**

Example configuration:

```sql
-- Add this to sql/import_export.sql
INSERT INTO import_export_configs (name, description, type, table_name, config) VALUES (
  'employees_export',
  'Export employee directory',
  'export',
  'employees',
  '{
    "table": "employees",
    "columns": ["employee_id", "full_name", "email", "department_id"],
    "field_labels": {
      "employee_id": "ID",
      "full_name": "Name",
      "email": "Email",
      "department_id": "Department"
    },
    "export_transforms": {
      "department_id": {
        "type": "lookup",
        "table": "departments",
        "key_column": "department_id",
        "display_column": "name"
      }
    },
    "filters": {
      "isDeleted": false
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name,
  config = EXCLUDED.config,
  updatedAt = now();
```

The template at the bottom of `sql/import_export.sql` provides a starting point for new configurations.

## Import CSV Format

### Products CSV Template

```csv
Product ID,Name,Description,Price,Quantity,Barcode,Brand,Product Number,Category,VAT Rate,STT Rate
,Fresh Apples,Organic red apples,2.99,100,123456,FreshCo,AP001,Fruits,12.5%,Not Applicable
,Banana Bunch,Yellow bananas,1.49,150,123457,TropiCo,BN001,Fruits,12.5%,Not Applicable
```

### Field Descriptions

- **Product ID**: Leave empty for new products, provide UUID for updates
- **Name**: Required, product name
- **Description**: Optional description
- **Price**: Numeric value
- **Quantity**: Integer stock quantity
- **Barcode**: Unique identifier
- **Brand**: Product brand
- **Product Number**: Internal product code
- **Category**: Category name (will be looked up)
- **VAT Rate**: "12.5%" or "0%"
- **STT Rate**: "6%" or "Not Applicable"

## Response Format

### Successful Import

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

## Integration with Frontend

### JavaScript Example

```javascript
// Export products
async function exportProducts(format = 'csv') {
  const response = await fetch(`/api/pos/products/export?format=${format}`);
  const blob = await response.blob();
  const url = window.URL.createObjectURL(blob);
  const a = document.createElement('a');
  a.href = url;
  a.download = `products.${format}`;
  a.click();
}

// Import products
async function importProducts(file) {
  const formData = new FormData();
  formData.append('file', file);
  formData.append('update_existing', 'true');
  formData.append('skip_errors', 'true');
  
  const response = await fetch('/api/pos/products/import', {
    method: 'POST',
    body: formData
  });
  
  const result = await response.json();
  console.log(`Imported: ${result.imported}, Updated: ${result.updated}`);
  if (result.errors.length > 0) {
    console.error('Errors:', result.errors);
  }
}

// Download template
function downloadTemplate() {
  window.location.href = '/api/pos/products/template?format=csv';
}
```

## Extension to Other Tables

To add import/export for any table:

1. **Add configuration to `sql/import_export.sql`**
2. **Load the configuration** (run the SQL file)
3. **Use generic endpoints** - No code changes needed!

```sql
-- Add this to sql/import_export.sql
INSERT INTO import_export_configs (name, type, table_name, config) VALUES (
  'vendors',
  'both',
  'vendors',
  '{
    "table": "vendors",
    "primary_key": "vendor_id",
    "columns": ["vendor_id", "name", "email", "phone"],
    "field_mappings": {
      "Vendor Name": "name",
      "Email": "email",
      "Phone": "phone"
    },
    "validations": {
      "name": {"required": true}
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name,
  config = EXCLUDED.config,
  updatedAt = now();
```

Then load and use:
```bash
# Load configuration
psql -U postgres -d your_database -f sql/import_export.sql

# Use the endpoints
curl -X GET "http://localhost/api/import-export/export/vendors?format=csv"
curl -X POST "http://localhost/api/import-export/import/vendors" -F "file=@vendors.csv"
```

## Advanced Features

### Conditional Exports
```bash
# Export only products in a specific category
curl -X GET "http://localhost/api/import-export/export/products_catalog?format=csv&filters[category_id]=12345"
```

### Lookup Tables
The system automatically resolves category names to IDs during import:
```csv
Product ID,Name,Category
,Apple,Fruits      <- "Fruits" is looked up to get category_id
,Carrot,Vegetables <- "Vegetables" is looked up to get category_id
```

### Tax Rate Mapping
Handles human-readable tax rates:
```csv
VAT Rate,STT Rate
12.5%,Not Applicable  <- Converted to {"vat": "G", "stt": ""}
0%,6%                 <- Converted to {"vat": "A", "stt": "E"}
```

## Testing

### Test Import
1. Download template: `GET /api/pos/products/template`
2. Fill in data
3. Upload file: `POST /api/pos/products/import`
4. Check response for errors

### Test Export
1. Export data: `GET /api/pos/products/export?format=csv`
2. Verify all columns are present
3. Check data formatting

## Next Steps

1. **Install PhpSpreadsheet** (optional, for better Excel support):
   ```bash
   cd api
   composer require phpoffice/phpspreadsheet
   ```

2. **Create UI Components**:
   - Import button with file picker
   - Export button with format selector
   - Progress indicators
   - Error display

3. **Add More Configurations**:
   - Employees
   - Vendors
   - Customers
   - Inventory items
   - Any other tables

4. **Extend Features**:
   - Background processing for large files
   - Import preview
   - Scheduled exports
   - Email notifications

## Support

See `docs/ImportExportService.md` for complete documentation including:
- Detailed API reference
- Configuration options
- Transformation types
- Validation rules
- Error handling
- Performance tips
- Security considerations