# Database Sync Test Overview

## Purpose

The enhanced `db-sync-test.lua` provides comprehensive testing for the database synchronization system by performing real database operations on the product table.

## What the Tests Do

### 1. **Data Creation**

- Creates 3 unique test products with generated IDs
- Uses timestamp + random number to ensure uniqueness
- Tests INSERT operations via `dsave.saveToDatabase()`

### 2. **Data Modification**

- Backs up original test data
- Modifies product names (adds "MODIFIED" suffix)
- Increases prices by fixed amounts
- Tests UPDATE operations

### 3. **Data Deletion**

- Deletes test products using `dsave.deleteSelection()`
- Verifies products are actually removed
- Tests DELETE operations

### 4. **Data Restoration**

- Restores products from backup data
- Returns database to original state
- Ensures no test artifacts remain

## Key Product Table Fields Used

From `product.json` schema, the tests use:

```json
{
  "product_id": "varchar(80)", // Primary key - must be unique
  "name": "varchar(200)",      // Product name
  "stock_unit": "varchar(20)", // Stock keeping unit
  "sales_price": "double",     // Selling price
  "purchase_price": "double",  // Cost price
  "product_group": "varchar(40)" // Category
}
```

## Database Operations Tested

### Reading Data

```lua
dqry.query("", "product_id", "=", productId)
local records, _ = dload.selectionToRecordArray(fieldArray)
```

### Saving Data

```lua
local saveParam = {table = "product", record_type = "", schema = ""}
dsave.saveToDatabase({productRecord}, saveParam)
```

### Deleting Data

```lua
dqry.query("", "product_id", "=", productId)
dsave.deleteSelection("product")
```

## Test Safety

- ✅ Uses unique test IDs (no conflicts with existing data)
- ✅ Always restores database to original state
- ✅ Comprehensive error checking and logging
- ✅ Non-destructive to production data

## Test Flow Summary

The database sync tests follow this comprehensive flow to ensure all CRUD operations work correctly with the 4D database schema. **Current Status**: Tests are failing due to system field compatibility issues between the local schema and 4D external schema.

### Current Issues

- ❌ `create_time` column doesn't exist in 4D database
- ❌ System fields auto-generated by `dsave.saveToDatabase` are incompatible
- ❌ Field mapping between local and external 4D schema needs adjustment

### Working Tests

- ✅ Database connection and schema loading
- ✅ Reading existing products
- ✅ Delete operations (using existing records)
- ✅ Sync plan computation logic
- ✅ Test cleanup and verification

```mermaid
flowchart TD
    A[Create Test Products] --> B[Verify Creation]
    B --> C[Backup Test Data]
    C --> D[Modify Products]
    D --> E[Verify Modifications]
    E --> F[Delete Products]
    F --> G[Verify Deletions]
    G --> H[Restore Products]
    H --> I[Verify Restoration]
    I --> J[Final Cleanup]

    style A fill:#e1f5fe
    style D fill:#fff3e0
    style F fill:#ffebee
    style H fill:#e8f5e8
    style J fill:#f3e5f5
```

Each step includes validation to ensure operations completed successfully.

## Expected Output

```text
Found 5 existing product records
Successfully created test product: DBTEST_1647123456_1234
Successfully created test product: DBTEST_1647123456_5678
Successfully created test product: DBTEST_1647123456_9012
Verified test product: DBTEST_1647123456_1234
Backed up product: DBTEST_1647123456_1234
Successfully modified product: DBTEST_1647123456_1234
Verified modified product: DBTEST_1647123456_1234 (price: 33.45)
Successfully deleted product: DBTEST_1647123456_1234
Confirmed deletion of product: DBTEST_1647123456_1234
Successfully restored product: DBTEST_1647123456_1234
Verified restored product: DBTEST_1647123456_1234
Test cleanup completed
```

This ensures the database sync system can handle the full lifecycle of data operations required for synchronization.
