# DB-Sync Test Manual

## Overview

This manual documents the test suite for the `db-sync.lua` module. The tests are located in `test/db-sync-test.lua` and are designed to ensure the reliability and correctness of the database synchronization logic.

## Running the Tests

To run the test suite, execute the following command from the `/Volumes/nc/nc-backend/` directory:

```bash
lj test/db-sync-test.lua
```

## Record Lifecycle and Test Verification

This section illustrates the full lifecycle of a product record during sync operations, including creation, modification, deletion, and the explicit checks performed by the test suite. The flowchart below visualizes the process and the points at which the test suite verifies state and traceability.

```mermaid
flowchart TD

	%% Unified entry point
	TOP[Start: Add Products A, B, C in Source DB]

	%% Multi-product scenario (explicit for A, B, C)
	TOP --> M1[Add Product A in Source DB]
	TOP --> M2[Add Product B in Source DB]
	TOP --> M3[Add Product C in Source DB]
	M1 --> S1[Run Sync]
	M2 --> S1
	M3 --> S1
	S1 --> D1[Insert Product A in Destination DB]
	S1 --> D2[Insert Product B in Destination DB]
	S1 --> D3[Insert Product C in Destination DB]
	D1 --> V1[checkInBoth: Verify add A]
	D2 --> V2[checkInBoth: Verify add B]
	D3 --> V3[checkInBoth: Verify add C]

	%% Modify
	V1 --> M4[Modify Product A in Source DB]
	V2 --> M5[Modify Product B in Source DB]
	V3 --> M6[Modify Product C in Source DB]
	M4 --> S2[Run Sync]
	M5 --> S2
	M6 --> S2
	S2 --> D4[Update Product A in Destination DB]
	S2 --> D5[Update Product B in Destination DB]
	S2 --> D6[Update Product C in Destination DB]
	D4 --> V4[checkInBoth: Verify modify A]
	D5 --> V5[checkInBoth: Verify modify B]
	D6 --> V6[checkInBoth: Verify modify C]

	%% Delete
	V4 --> M7[Delete Product A in Source DB]
	V5 --> M8[Delete Product B in Source DB]
	V6 --> M9[Delete Product C in Source DB]
	M7 --> S3[Run Sync]
	M8 --> S3
	M9 --> S3
	S3 --> D7[Delete Product A in Destination DB]
	S3 --> D8[Delete Product B in Destination DB]
	S3 --> D9[Delete Product C in Destination DB]
	D7 --> V7[checkInBoth: Verify delete A]
	D8 --> V8[checkInBoth: Verify delete B]
	D9 --> V9[checkInBoth: Verify delete C]

	%% Legend
	classDef check fill:#1565c0,color:#fff,stroke:#003366,stroke-width:2px;
	class V1,V2,V3,V4,V5,V6,V7,V8,V9 check;

	%% Legend
	classDef check fill:#1565c0,color:#fff,stroke:#003366,stroke-width:2px;
	class D,H,L,M4,M8,M12 check;
```

### Explanation

- **Create**: Product is created in the source DB. After sync, the destination DB receives a new record with a new `record_id`, but `json_data.record_id` preserves the original. The test checks both DBs for presence and correct mapping.
- **Modify**: Product is updated in the source DB. After sync, the destination DB is updated. The test checks that `modify_time` and data match, and that traceability fields are correct.
- **Delete**: Product is deleted in the source DB. After sync, the destination DB record is removed. The test checks for absence in both DBs.
- **Multi-Change**: The test suite performs sequences of adds, modifies, and deletes, running sync and explicit `checkInBoth` verifications after each step to ensure all changes are correctly reflected and traceability is maintained.
- **checkInBoth**: At each key step, the test suite uses `checkInBoth` to assert the state of each product in both DBs, including presence/absence, data correctness, and traceability fields (`json_data.record_id`, `json_data.modify_time`).

This approach ensures that every stage of the record lifecycle is explicitly tested and auditable, with traceability from source to target and back.

The tests will connect to the configured databases, perform a series of operations, and report the results.

## Test Cases

The test suite includes the following key scenarios:

### 1. Full Sync Lifecycle & Multi-Change Test

- **Creation**: A new product is created in the source database.
- **Synchronization**: `db-sync` is run, and the test verifies that the new product is added to the destination database.
- **Modification**: The product is updated in the source database.
- **Synchronization**: `db-sync` is run again, and the test verifies that the changes are propagated to the destination database.
- **Deletion**: The product is deleted from the source database.
- **Synchronization**: `db-sync` is run a final time, and the test verifies that the product is removed from the destination database.

#### Multi-Change Scenario

- The suite includes a test that performs multiple changes in sequence:
 	- Adds and modifies products before the first sync.
 	- Deletes a product and adds another before the second sync.
 	- After each sync, explicit checks are performed for each product to verify their state in both source and destination databases (using `checkInBoth`).
 	- Note: Deletion of a product (e.g., productB) is only verified after the delete operation and a subsequent sync, not after the first sync.

### 2. Unique `product_id` Constraint

- Before creating a new test product, the test suite checks if a product with the same `product_id` already exists.
- If a conflict is found, a new `product_id` is generated until a unique one is found. This prevents test failures due to ID collisions and ensures test idempotency.

### 3. State Change Verification & Explicit Cross-DB Checks

The tests closely monitor and verify database state changes:

- **`record_id`**: When a new record is synced to the target, it is assigned a new, unique `record_id`. The tests confirm that the `record_id` in the target is different from the source, as expected.
- **`modify_time`**: After a record is updated and synced, the tests verify that the `modify_time` in the target database is updated to reflect the time of the synchronization.
- **Explicit Cross-DB Checks**: After each operation and sync, the test suite uses a helper (`checkInBoth`) to assert the presence, absence, and modification state of each product in both source and target databases. This ensures that all changes are correctly reflected and no unexpected records remain.

When syncing between other databases and Capacic Postgres database (schema is ""), the following conventions are used for traceability:

- **json_data.record_id**: In Postgres, this field contains the original `record_id` from the source database (e.g., 4D). This allows tracking the origin of each record after it is inserted into the target DB, which will have its own unique `record_id`.
- **json_data.modify_time**: Similarly, this field contains the original `modify_time` from the source database. The top-level `modify_time` in the target reflects the time of the sync or the last modification in the target DB.

These fields are used in the test suite to verify that the correct mapping and traceability are maintained during sync operations, and to ensure that the original source values are preserved for auditing and debugging purposes.

### 4. Data Integrity

- After each operation (create, modify, delete), the test suite compares the records in the source and destination databases to ensure they are consistent.
- The final state of the databases (after all tests are run) is compared to the initial state to ensure that no test data remains.

## Test Data Management

- **Test Data**: All test records are created with a `product_id` prefixed with `DBTEST_`. This allows for easy identification and cleanup of test data.
- **Cleanup**: A cleanup function runs at the end of the test suite to remove all test records from both the source and destination databases, ensuring that the tests do not leave any artifacts.

## Interpreting Test Output

The test suite provides verbose output to the console, indicating the status of each test case. A summary of passed and failed tests is provided at the end of the run. Any discrepancies in data between the source and destination databases will be reported as test failures. If a product is expected to be deleted, the test will only check for its absence after the delete operation and a subsequent sync, not before.
