# POS Transaction Import/Export System

## Overview

The POS Transaction Import/Export system allows you to import and export complete transaction data including receipts, line items, and payment information. This is useful for:

- **Bulk Data Entry**: Import multiple transactions from external systems
- **Data Backup**: Export all transaction data for archival
- **Data Migration**: Move transaction data between systems
- **Reporting**: Export data for external analysis

## Database Structure

### Tables

1. **`sales_receipts`** - Stores receipt/invoice header information
   - Invoice numbers, totals, verification data
   - SDC (Smart Device Controller) signatures and timestamps
   - Business and location information
   - Tax calculations and counters

2. **`lineitems_payments`** - Stores transaction details as JSONB
   - `line_items`: Array of products sold (product_id, quantity, price)
   - `payments`: Array of payment methods used (method, amount)

## Import/Export Configurations

### Configuration: `pos_transactions_complete`

**Type**: Both import and export  
**Description**: Complete POS transaction data with all fields

**Features**:
- ✅ All receipt header fields
- ✅ Line items as JSON array
- ✅ Payments as JSON array
- ✅ Tax and verification data
- ✅ Update existing transactions by invoice number

### Configuration: `pos_line_items`

**Type**: Both import and export  
**Description**: Only line items and payments for existing receipts

**Features**:
- ✅ Updates line items for existing receipts
- ✅ Updates payment methods
- ✅ Validates receipt_id exists

## Data Formats

### CSV Format

**Example** (`pos_transactions.csv`):

```csv
Invoice Number,Type,Transaction Type,SDC Date/Time,Total Amount,Business Name,TIN,Location Name,Line Items (JSON),Payments (JSON)
INV-001,Normal,sale,2024-10-02 10:30:00,125.50,ACME Store,TIN123456,Main Branch,"[{""product_id"":""0194a8b2-1234-7123-8123-123456789abc"",""product_name"":""Widget A"",""quantity"":2,""unit_price"":50.00,""total"":100.00,""tax_rate"":""G""},{""product_id"":""0194a8b2-5678-7123-8123-123456789def"",""product_name"":""Widget B"",""quantity"":1,""unit_price"":25.50,""total"":25.50,""tax_rate"":""G""}]","[{""method"":""Cash"",""amount"":125.50,""reference"":""""}]"
INV-002,Normal,sale,2024-10-02 11:00:00,75.00,ACME Store,TIN123456,Main Branch,"[{""product_id"":""0194a8b2-9abc-7123-8123-123456789ghi"",""product_name"":""Service X"",""quantity"":1,""unit_price"":75.00,""total"":75.00,""tax_rate"":""A""}]","[{""method"":""MPaisa"",""amount"":75.00,""reference"":""MP-12345""}]"
```

### JSON Format

**Example** (`pos_transactions.json`):

```json
[
  {
    "sales_receipt_id": "",
    "org_id": "0194a8b2-1234-7123-8123-123456789abc",
    "type": "Normal",
    "invoiceNumber": "INV-001",
    "requestedBy": "cashier@example.com",
    "sdcDateTime": "2024-10-02 10:30:00",
    "totalAmount": 125.50,
    "businessName": "ACME Store",
    "tin": "TIN123456",
    "locationName": "Main Branch",
    "transactiontype": "sale",
    "line_items": [
      {
        "product_id": "0194a8b2-1234-7123-8123-123456789abc",
        "product_name": "Widget A",
        "quantity": 2,
        "unit_price": 50.00,
        "total": 100.00,
        "tax_rate": "G",
        "discount": 0
      },
      {
        "product_id": "0194a8b2-5678-7123-8123-123456789def",
        "product_name": "Widget B",
        "quantity": 1,
        "unit_price": 25.50,
        "total": 25.50,
        "tax_rate": "G",
        "discount": 0
      }
    ],
    "payments": [
      {
        "method": "Cash",
        "amount": 125.50,
        "reference": ""
      }
    ]
  }
]
```

## Line Items Structure

Each line item in the `line_items` array should have:

```json
{
  "product_id": "uuid",        // Required: Reference to products table
  "product_name": "string",    // Product name (for display)
  "quantity": 2,               // Required: Number of items sold
  "unit_price": 50.00,         // Required: Price per unit
  "total": 100.00,             // Line item total (quantity × unit_price)
  "tax_rate": "G",             // Tax rate code (G = General 9%, A = Exempt, etc.)
  "discount": 0                // Optional: Discount amount
}
```

## Payments Structure

Each payment in the `payments` array should have:

```json
{
  "method": "Cash",            // Required: Payment method (Cash, Credit, MPaisa, etc.)
  "amount": 125.50,            // Required: Amount paid with this method
  "reference": "MP-12345"      // Optional: Payment reference/transaction ID
}
```

## Usage

### Importing Transactions

#### Via UI (Dashboard)

1. Navigate to POS Module
2. Click **"Import Transactions"** button
3. Select format (CSV, JSON, Excel)
4. Download template (optional)
5. Select file to upload
6. Choose "Update existing transactions" if needed
7. Click **"Start Import"**

#### Via API

```bash
curl -X POST /api/import-export/import/pos_transactions_complete \
  -F "file=@transactions.csv" \
  -F "update_existing=1"
```

### Exporting Transactions

#### Via UI (Dashboard)

1. Navigate to POS Module
2. Click **"Export Transactions"** button
3. Select format (CSV, JSON, Excel)
4. Optional: Set date range filters
5. Click **"Download"**

#### Via API

```bash
# Export all transactions
curl -O /api/import-export/export/pos_transactions_complete?format=json

# Export with date range
curl -O "/api/import-export/export/pos_transactions_complete?format=csv&start_date=2024-10-01&end_date=2024-10-31"
```

## Field Mapping

### Required Fields

- `invoiceNumber` - Unique invoice/receipt number
- `type` - Receipt type (Normal, Training, Proforma)
- `totalAmount` - Total transaction amount
- `org_id` - Organization ID (auto-set from session if not provided)
- `line_items` - Array of line items (at least 1 required)

### Optional Fields

- `sales_receipt_id` - Leave empty for new records
- `requestedBy` - User/cashier who created the receipt
- `sdcDateTime` - SDC timestamp
- `invoiceCounter`, `invoiceCounterExtension` - Receipt counters
- `taxItems` - Tax calculation details (JSONB)
- `verificationUrl`, `verificationQRCode` - SDC verification
- `journal` - Transaction journal
- `signedBy`, `signature`, `encryptedInternalData` - Digital signatures
- `businessName`, `tin`, `locationName`, `address`, `district`, `mrc` - Business info
- `payments` - Array of payment methods

## Validation

The import system validates:

- **Required fields**: invoiceNumber, type, totalAmount present
- **Data types**: Amounts are numeric, dates are valid
- **JSON structure**: line_items and payments are valid JSON arrays
- **Product references**: product_id exists in products table (if validation enabled)
- **Unique constraints**: invoiceNumber + org_id combination

## Error Handling

### Common Errors

1. **"Invoice number already exists"**
   - Solution: Enable "Update existing" option
   
2. **"Invalid JSON in line_items"**
   - Solution: Check JSON syntax, use template

3. **"Product not found"**
   - Solution: Ensure product_id exists in products table

4. **"Total amount mismatch"**
   - Solution: Verify sum of line items equals totalAmount

### Import Results

After import, you'll see:
- **Total**: Number of rows processed
- **Success**: Successfully imported/updated
- **Failed**: Rows with errors
- **Errors List**: Detailed error messages per row

## Database Installation

The configurations are stored in `sql/pos_import_export.sql`:

```sql
-- Run this to install/update the configurations
\i sql/pos_import_export.sql
```

Or via PHP:

```php
$sql = file_get_contents('sql/pos_import_export.sql');
$conn->exec($sql);
```

## Frontend Integration

Add import/export buttons to your POS module:

```javascript
// In your POS module
window.importPOSTransactions();  // Open import modal
window.exportPOSTransactions();  // Open export modal
```

## Technical Details

### Services

- **ImportService** (`api/services/ImportService.php`)
  - `importPosTransaction()` - Handles POS-specific import logic
  - Creates both `sales_receipts` and `lineitems_payments` records
  - Supports update existing by invoice number

- **ExportService** (`api/services/ExportService.php`)
  - Handles JSONB field extraction
  - Pretty-prints JSON for readability
  - Joins sales_receipts with lineitems_payments

### API Endpoints

- `POST /api/import-export/import/{configName}` - Import data
- `GET /api/import-export/export/{configName}` - Export data
- `GET /api/import-export/template/{configName}` - Download template

## Security

- ✅ Tenant isolation via org_id filtering
- ✅ Session-based org_id assignment
- ✅ File upload validation (size, type)
- ✅ SQL injection protection (prepared statements)
- ✅ XSS protection (output escaping)

## Performance

- Batch processing: Processes multiple transactions in single request
- Transaction support: Database transactions ensure data consistency
- Memory efficient: Streams large files
- Progress tracking: Real-time upload progress

## Support

For issues or questions:
1. Check error messages in import results
2. Verify data format matches template
3. Check database logs: `logs/error.log`
4. Review configuration: `SELECT * FROM import_export_configs WHERE name = 'pos_transactions_complete';`