# Configuration Reference

Settings are loaded from `.env` (key=value format) and JSON files in `config\`.

## `.env` variables

Copy [`config/.env.example`](../config/.env.example) to `.env` in the repo root.

### Access

| Variable | Default | Required for | Description |
|----------|---------|--------------|-------------|
| `ACCESS_DB_PATH` | — | `access`, `schema-only`, `all` | Absolute path to the Access `.mdb` file |
| `ACCESS_DB_PASSWORD` | empty | — | Database encrypt password (`Jet OLEDB:Database Password` / COM `OpenCurrentDatabase`). Not the MSACCESS `/pwd` switch. |
| `ACCESS_DB_USER` | empty | — | Jet user for MSys GRANT. Empty = `Application.CurrentUser` for GRANT; Access Runtime Shell uses **Admin**. |
| `ACCESS_WORKGROUP_PASSWORD` | empty | — | Workgroup account password for MSACCESS `/pwd` (with `/user`). Leave empty when Admin has no password. |

### SQL Express

| Variable | Default | Required for | Description |
|----------|---------|--------------|-------------|
| `SQL_EXPRESS_CONNECTION_STRING` | `Server=.\SQLEXPRESS;Trusted_Connection=True;TrustServerCertificate=True` | All modes except disabled legs | Base connection string. `Database=` is appended from `SQL_EXPRESS_DATABASE` if not already present |
| `SQL_EXPRESS_DATABASE` | `AnniesAngels` | `access`, `schema-only`, `all`, `mysql` | Target database name (created if missing) |
| `SQL_EXPRESS_SCHEMA` | `dbo` | `access`, `schema-only`, `all` | Schema for mirrored tables and views |

### MySQL

| Variable | Default | Required for | Description |
|----------|---------|--------------|-------------|
| `MYSQL_HOST` | `127.0.0.1` | `mysql`, `all` | MySQL server hostname |
| `MYSQL_PORT` | `3306` | `mysql`, `all` | MySQL port (1–65535) |
| `MYSQL_USER` | — | `mysql`, `all` | MySQL username |
| `MYSQL_PASSWORD` | empty | `mysql`, `all` | MySQL password |
| `MYSQL_DATABASE` | `annies_angels_shop` | `mysql`, `all` | Target database for `shop_products` |

### Shop sync log API fallback (optional)

Used only when direct MySQL INSERT into `shop_sync_log` fails. Both must be set together or both left empty.

| Variable | Default | Description |
|----------|---------|-------------|
| `SHOP_SYNC_API_URL` | empty | Absolute URL to CI4 `POST /api/shop-sync/log` |
| `SHOP_SYNC_API_KEY` | empty | Shared secret matching CI4 `shop.syncApiKey` |

### Feature switches

| Variable | Default | Description |
|----------|---------|-------------|
| `SYNC_ACCESS_TO_SQL_EXPRESS` | `true` | Enable Access → SQL Express leg |
| `SYNC_SQL_EXPRESS_TO_MYSQL` | `true` | Enable SQL Express → MySQL leg |

When a leg is disabled, its mode is skipped with an informational log message. At least one leg must be enabled.

### Access → SQL Express options

| Variable | Default | Description |
|----------|---------|-------------|
| `ACCESS_SQL_EXCLUDE_TABLES` | `Switchboard Items,adn,adni,tblDeliveryNoteItemORIG,UsysDN,UsysIN` | Comma-separated table names excluded from table sync, FK provisioning, and index provisioning. Empty = all discovered user tables in scope |

### Performance and safety

| Variable | Default | Description |
|----------|---------|-------------|
| `SYNC_BATCH_SIZE` | `5000` | Rows accumulated before a MySQL flush (and Access read batch size for Leg 1). Must be > 0. Leg 2 further splits each flush into multi-row IODKU statements of up to 500 rows (`ShopProductSql.UpsertChunkSize`). Leg 1 IN-chunk hash prefetch caps at 2000 params per query |
| `SYNC_MAX_RETRIES` | `3` | Polly retry count per batch operation. Must be ≥ 0 |
| `SYNC_COMMAND_TIMEOUT_SECONDS` | `120` | SQL command timeout |
| `SYNC_SOFT_DELETE_ENABLED` | `true` | Mark rows missing from Access as `_sync_is_deleted = 1` on SQL Express |
| `SYNC_DRY_RUN` | `false` | Log actions without writing data (see dry-run behavior below) |
| `SYNC_PLATFORM_TARGET` | `x64` | Documented ACE bitness expectation (`x86` or `x64`). Must match build and installed ACE |

## Dry-run behavior (`SYNC_DRY_RUN=true`)

| Action | Behavior |
|--------|----------|
| SQL Express DDL (tables, keys, defaults, FKs, CHECKs, views) | Skipped — logged as DRY RUN |
| SQL Express data upserts | Skipped — batches still read from Access |
| Soft deletes | Skipped |
| MySQL schema creation | Skipped |
| MySQL upserts | Counted but not written |
| `shop_sync_log` | **Still written** on success/failure |

## Config files

All files in `config\` are copied to the output folder on build.

### `config/product-sync-map.json`

Controls MySQL product field sources:

```json
{
  "barcodeColumn": "strCode",
  "imageUrl1Column": "strImagePath",
  "productIdColumn": "ProductID",
  "productTable": "tblProduct",
  "lookupJoins": { ... }
}
```

| Field | Used in code | Description |
|-------|--------------|-------------|
| `barcodeColumn` | Yes | SQL Express column for MySQL `barcode` |
| `imageUrl1Column` | Yes | SQL Express column for `image_url_1` on INSERT only (existing MySQL values preserved on conflict) |
| `productIdColumn` | Yes | SQL Express column for MySQL `sqlexpress_product_id` (default `ProductID`) |
| `productTable` | Loaded but **not used** | Product query hardcodes `tblProduct` |
| `lookupJoins` | **Not used** | JOIN tables are hardcoded in `MySqlProductSync` |

If the file is missing, defaults apply (`strCode`, `strImagePath`, `ProductID`, `tblProduct`).

### `config/access-views.json`

Optional manual overrides for Access query SQL. Used when OleDb `MSysObjects` reads and COM `Access.Application` fallback both fail.

Copy from [`config/access-views.json.example`](../config/access-views.json.example):

```json
{
  "qryDeliveryNoteItems": "SELECT ...",
  "qDelDNList": "SELECT ..."
}
```

Keys are query/view names; values are Access-compatible SQL strings translated to T-SQL views.

### `config/table-overrides.json`

Placeholder for future primary-key overrides. **Not referenced in code** — tables without a detectable PK are skipped with a warning.

## Validation rules

`SyncOptionsValidator` runs at startup. Configuration errors return exit code **2**.

### Placeholder detection

These fragments in paths or connection strings are rejected:

- `c:\path`, `/path/`, `your_`, `yoursite.com`, `changeme`, `<`, `>`

### Mode-specific requirements

| Mode | Validates |
|------|-----------|
| `access`, `schema-only`, `all` (if Access leg enabled) | `ACCESS_DB_PATH` exists, SQL Express settings |
| `mysql`, `all` (if MySQL leg enabled) | SQL Express connection, `MYSQL_HOST`, `MYSQL_USER`, `MYSQL_DATABASE`, valid port |
| Any | At least one leg enabled, `SYNC_BATCH_SIZE > 0`, `SYNC_MAX_RETRIES ≥ 0` |

## Exit codes

| Code | When |
|------|------|
| 0 | Sync completed successfully |
| 1 | Runtime failure (database, sync error, unhandled exception) |
| 2 | Configuration error (`SyncOptionsValidator` — missing/invalid `.env`) |
| 3 | Another `SyncEngine.exe` is already running on this machine |

Exit code **3** is returned before configuration is loaded. It is not controlled by any `.env` variable. See [Single-instance guard](#single-instance-guard).

## Single-instance guard

Not configured via `.env`. Enforced at process startup by `SyncInstanceLock`:

| Property | Value |
|----------|-------|
| Mechanism | Windows named mutex |
| Name | `Global\SyncCoordinator.SyncEngine` |
| Scope | One `SyncEngine.exe` per machine (all modes) |
| On conflict | Log warning, exit **3**, no database connections |
| After crash/kill | Mutex released by OS — guard does not stick |

Full behavior and Task Scheduler notes: [sync-architecture.md](sync-architecture.md#single-instance), [getting-started.md](getting-started.md#exit-code-3--another-instance-running).

## Related

- [getting-started.md](getting-started.md) — setup walkthrough
- [sync-architecture.md](sync-architecture.md) — how settings affect sync behavior
- [access-sql-express-compatibility.md](access-sql-express-compatibility.md) — schema mirroring and constraint behaviour
- [shop_products.md](shop_products.md) — MySQL target details