# Database Synchronization Test Documentation

## Overview

The `db-sync-test.lua` file contains comprehensive tests for the database synchronization system. These tests validate both the sync planning algorithm and real database operations using the product table.

## Test Structure

The test suite is divided into two main categories:

### 1. Database Operations Tests

These tests perform actual database operations to verify the sync system can handle real data changes.

### 2. Sync Plan Logic Tests

These tests validate the sync planning algorithm that determines what operations need to be performed.

## Database Operations Test Flow

The database tests follow a complete lifecycle that safely modifies and restores data:

```mermaid
flowchart LR
    subgraph col1 ["Setup & Data Creation"]
        A[1. Setup & Connection] --> B[2. Read Existing Products]
        B --> C[3. Create Test Products]
        C --> D[4. Verify Creation]
        D --> E[5. Backup Test Products]
        E --> F[6. Modify Test Products]
    end

    subgraph col2 ["Modification & Cleanup"]
        G[7. Verify Modifications] --> H[8. Delete Test Products]
        H --> I[9. Verify Deletions]
        I --> J[10. Restore Test Products]
        J --> K[11. Verify Restoration]
        K --> L[12. Cleanup Test Data]
    end

    F --> G
```

## Test Phases Explained

### Phase 1: Setup and Preparation

1. **Database Connection** (`test.sync.database_connection`)
   - Verifies database connectivity
   - Ensures test environment is ready

2. **Table Verification** (`test.sync.product_table_exists`)
   - Confirms product table exists and is accessible
   - Validates schema availability

3. **Read Existing Data** (`test.sync.read_existing_products`)
   - Samples existing product records
   - Understands current data structure
   - Provides baseline for testing

### Phase 2: Data Creation and Verification

1. **Create Test Products** (`test.sync.create_test_products`)
   - Creates 3 unique test product records
   - Uses time-based unique product IDs to avoid conflicts
   - Tests basic INSERT operations

2. **Verify Creation** (`test.sync.verify_created_products`)
   - Confirms test products were saved correctly
   - Validates data integrity after insertion
   - Tests READ operations

### Phase 3: Backup and Modification

1. **Backup Test Products** (`test.sync.backup_test_products`)
   - Creates complete backups of test records
   - Prepares for safe restoration later
   - Tests comprehensive field reading

2. **Modify Test Products** (`test.sync.modify_test_products`)
   - Updates product names and prices
   - Tests UPDATE operations
   - Simulates real-world data changes

3. **Verify Modifications** (`test.sync.verify_modified_products`)
   - Confirms changes were applied correctly
   - Validates UPDATE operation results

### Phase 4: Deletion and Restoration

1. **Delete Test Products** (`test.sync.delete_test_products`)
   - Removes test products from database
   - Tests DELETE operations
   - Simulates record removal scenarios

2. **Verify Deletions** (`test.sync.verify_deleted_products`)
   - Confirms products were actually deleted
   - Validates DELETE operation results

3. **Restore Test Products** (`test.sync.restore_test_products`)
   - Restores products from backup data
   - Tests restoration after deletion
   - Returns database to original state

4. **Verify Restoration** (`test.sync.verify_restored_products`)
   - Confirms restored data matches original backup
   - Validates complete data integrity cycle

### Phase 5: Cleanup

1. **Cleanup Test Data** (`test.sync.cleanup_test_products`)
   - Removes any remaining test artifacts
   - Ensures clean test environment
   - Prepares for subsequent test runs

## Product Table Schema

The tests use the following key fields from the product table:

### Core Fields

- `product_id` (varchar, 80) - Primary key, unique identifier
- `name` (varchar, 200) - Product name
- `stock_unit` (varchar, 20) - Stock keeping unit
- `design_unit` (varchar, 20) - Design unit of measure

### Financial Fields

- `purchase_price` (double) - Cost to purchase
- `cost_price` (double) - Internal cost calculation
- `sales_price` (double) - Selling price

### Classification Fields

- `product_group` (varchar, 40) - Product category
- `product_state` (integer) - Current state/status
- `stock_type` (integer) - Type of stock item
- `production_type` (integer) - How product is produced

### Operational Fields

- `unit_multiplier` (double) - Unit conversion factor
- `minimum_stock` (double) - Minimum inventory level
- `calculation_lot` (double) - Standard calculation quantity

## Test Data Management

### Unique ID Generation

```lua
local function generateProductId(prefix)
    prefix = prefix or "TEST"
    local timestamp = tostring(os.time())
    local random = tostring(math.random(1000, 9999))
    return prefix .. "_" .. timestamp .. "_" .. random
end
```

This ensures:

- No conflicts with existing products
- Unique IDs across test runs
- Easy identification of test data

### Test Product Structure

```lua
local function createTestProduct(productId, name, suffix)
    return {
        product_id = productId,
        name = (name or "Test Product") .. suffix,
        stock_unit = "PCS",
        design_unit = "PCS",
        unit_multiplier = 1.0,
        product_group = "TEST_GROUP",
        product_state = 1,
        stock_type = 1,
        production_type = 1,
        purchase_price = 10.0 + math.random(),
        cost_price = 15.0 + math.random(),
        sales_price = 25.0 + math.random(),
        minimum_stock = 5.0,
        calculation_lot = 1.0
    }
end
```

## Database Operations Used

### Reading Data

```lua
-- Query setup
dqry.query("", "product_id", "=", productId)

-- Read records
local records, _ = dload.selectionToRecordArray({"product_id", "name", "sales_price"})
```

### Saving Data

```lua
-- Save parameters
local saveParam = {
    table = "product",
    record_type = "",
    schema = ""
}

-- Save operation
local _, err = dsave.saveToDatabase({productRecord}, saveParam)
```

### Deleting Data

```lua
-- Query to select records for deletion
dqry.query("", "product_id", "=", productId)

-- Delete operation
local err = dsave.deleteSelection("product")
```

## Sync Plan Tests

The original sync plan tests validate the decision-making algorithm:

### Test Scenarios

1. **Empty Destination** - When target database is empty
2. **More Records in Destination** - When target has extra records
3. **Equal Counts with Changes** - When counts match but data differs
4. **Source Has More Records** - When source has additional records
5. **Edge Cases** - Boundary conditions and error scenarios

### Test Data Structure

```lua
local function makeRec(fromAll, toAll, changed, hasPrev)
    return {
        fromCountAll = fromAll or 0,
        toCountAll = toAll or 0,
        fromCountChanged = changed or 0,
        prevSyncModifyId = hasPrev and "00000000-0000-0000-0000-000000000001" or ""
    }
end
```

## Safety Features

### Non-Destructive Testing

- Only creates uniquely named test records
- Never modifies existing production data
- Always restores database to original state

### Error Handling

- Tests include comprehensive error checking
- Failed operations are logged and reported
- Cleanup runs even if tests fail

### Data Integrity

- Backup and restore cycle ensures data consistency
- Floating-point comparisons use appropriate tolerance
- Record counts are verified at each step

## Running the Tests

### Prerequisites

1. Database connection must be available
2. Product table must exist and be accessible
3. Sufficient permissions for CREATE, READ, UPDATE, DELETE operations

### Execution

```bash
# Run all tests
lua test/db-sync-test.lua

# Run specific test category
lua -e "local t = require('test/db-sync-test'); t.test.sync.database_connection()"
```

### Expected Output

- Test progress messages showing each operation
- Success/failure status for each test
- Database operation confirmations
- Final cleanup confirmation

## Troubleshooting

### Common Issues

1. **Connection Errors** - Check database connectivity and credentials
2. **Permission Errors** - Ensure sufficient database permissions
3. **Table Not Found** - Verify product table exists in current schema
4. **Unique Constraint Violations** - Usually resolved by cleanup and retry

### Debug Information

- Each test function includes detailed logging
- Error messages include context and suggested solutions
- Database operation results are displayed in real-time

---

*This documentation provides a complete understanding of the database synchronization test suite and its role in validating the sync system.*
