# Delete Detection in nc-calc-server-load-data

## `changeIdx` Format

```lua
changeIdx = {
    add_count = 5,
    modify_count = 3,
    delete_count = 1,
    [tableName] = {
        add_count = 3,
        modify_count = 1,
        delete_count = 1,
        add    = {[id] = row},
        modify = {[id] = row},
        delete = {[id] = {record_id = id, modify_time = "..."}}
    }
}
```

- `id` = value of `gridInfo.id_field` for that table (e.g. `product_id` for product, `record_id` for others)
- `row` = full row data, NO extra keys added (`change`, `calc` — NONE)
- `delete` entries contain `{record_id, modify_time}` plus optional `product_id` if the table has one
- Server tracks `changeIdx` internally during load, sends same structure to browser
- Browser iterates by type:

  ```text
  for id, row of changeIdx[tableName].add:    push to state.data
  for id, row of changeIdx[tableName].modify: find by id, update in place
  for id, entry of changeIdx[tableName].delete: find by id, mark deleted
  ```

## Overview

`queryData()` detects three change types between full and incremental loads:

| Type | Detection | Key Field |
|------|-----------|-----------|
| **Add** | record in query result not in grid `gridIdx` | primary key (e.g. `product_id`) |
| **Modify** | record in query result already in grid `gridIdx` | primary key (e.g. `product_id`) |
| **Delete** | record in previous cache not in database (binary search) | primary key (e.g. `product_id`) |

## Two-Phase Load Architecture

### Phase 1: Full Load (`reload_data=true`)

1. Query ALL records from database (no modify_id filter)
2. Populate grid with all data
3. **Track `last_modify_id`** — highest `modify_id` from all returned records
4. **Store `recordIdArray`** in `loc.tableStat[tableKey]` — array of primary key values for all grid records
5. Save `param.rec.last_modify_id[tableName][dbId]` as anchor for next incremental load

### Phase 2: Incremental Load (`reload_data=false`)

1. Query records where `modify_id >= last_modify_id` (filtered by qry module)
2. Add records → add to grid, track in `changeIdx[tableName].add[id] = row`
3. Modify records → update in grid via `util.recToRec`, track in `changeIdx[tableName].modify[id] = row`
4. **Delete detection** via `findDeletedByBinarySearch()` — checks IDs from previous cache against database using COUNT(*) to prune ranges where all IDs exist
5. Remove deleted records from grid, track in `changeIdx[tableName].delete[id] = {record_id, modify_time}`

## Delete Detection: `findDeletedByBinarySearch()`

### Algorithm

```text
INPUT: tableStat.recordIdArray (all cached IDs), recordIdField, queryName, orgId
OUTPUT: deletedIdx = { [id] = true }, deletedCount

1. Sort recordIdArray
2. Push full range [1, N] onto stack
3. While stack not empty:
   a. Pop range [startPos, endPos]
   b. Build rangeIdArray from sorted array
   c. COUNT(*) query: "how many of these IDs exist in database?"
   d. If count == rangeSize → all exist, skip
   e. If count < rangeSize AND rangeSize <= batchSize → EXIST query to find which IDs are missing
   f. If count < rangeSize AND rangeSize > batchSize → binary split, push both halves
4. Return deleted IDs and count
```

### Key Properties

- **COUNT query first**: cheap check, avoids expensive EXIST query when no deletions. Ranges where all IDs exist (including just-loaded) get count == rangeSize → skipped immediately
- **Positional split**: divides by array position (not by ID value), handles any ID format
- **EXIST query at leaf**: only fetches actual IDs when range is small enough
- **O(log n) queries** for finding deletions, plus one EXIST query per affected leaf

### Conditions for Delete Detection to Run

All must be true:

1. `use_delete_detection` = true (from constant)
2. `qryRet.data` exists (query returned data, even if empty)
3. `qryRet.error == nil` (no query error)
4. `loc.tableStat[tableKey].recordIdArray` has entries (previous load stored record IDs)

## Data Flow

```text
1. findDeletedByBinarySearch(tableStat.recordIdArray, ...)
   → deletedIdx = {[id] = true}

2. Remove deleted from gridRec.data

3. Track in changeIdx[tableName].delete[id] = {record_id, modify_time, product_id?}

4. Add to loc.deletedArray[orgId] = {{table, record_id, product_id?}, ...}

5. nc-calc-server-calc.lua sets gridIdx.deleted = loc.deletedArray[orgId]

6. product-need.lua passes gridIdx.deleted to:
   change-data.lua:setIncrementalChange(data, lastModifyId, gridIdx.deleted)

7. change-data.lua creates final changeIdx with:
   tbl.delete[record_id] = {record_id = record_id, modify_time = now()}
```

## Field Name Clarification

For the `product` table:

| Field | Source | Value |
|-------|--------|-------|
| `record_id` | `dschema.idField("product")` = `"pr.record_id"` | UUID, different from `product_id` |
| `product_id` | `dschema.primaryKeyField("product")` = `"pr.product_id"` | UUID, the actual primary key |
| `modify_id` | `dschema.modifyIdField("product")` = `"pr.modify_id"` | UUID, tracks last modification |

`gridInfo.record_id_field` = `"pr.product_id"` (confusingly named but holds the primary key field)
`gridInfo.id_field` = same as `record_id_field`

`changeIdx[tableName].delete` is keyed by the value of `recordIdFieldNoPrefix` which is `peg.parseAfter("pr.product_id", ".")` = `"product_id"`.

## Bugs Found and Fixed

### Bug: Full load never set `last_modify_id`

**Location**: `nc-calc-server-load-data.lua:240-249`

**Problem**: `cachedModifyIdField` was only computed inside the `reload_data ~= true` block. For full loads (`reload_data=true`), `cachedModifyIdField` was nil, so `useModifyId` stayed false, `setLastModifyIdItem()` returned early, and `lastModifyId` was never populated. The anchor `param.rec.last_modify_id[tableName][dbId]` was never set.

**Consequence**: First incremental load after full load had no anchor → query returned ALL records → delete detection was skipped (empty `last_modify_id` check failed).

**Fix**: Moved `cachedModifyIdField` computation outside the `reload_data` condition. Query parameter setup (filtering) remains inside the incremental-only block.

### Bug: `setLastModifyIdItem` used `>` instead of `>=` causing infinite reload loop

**Location**: `nc-calc-server-load-data.lua:177` (`setLastModifyIdItem`)

**Problem**: The qry module uses `modify_id >= last_modify_id` for incremental queries. This means the anchor record (the one with `modify_id == last_modify_id`) comes back every cycle. With `itemId > lastModifyId` (strict greater-than), the anchor record's modify_id didn't advance the anchor. The next cycle queried with the same `last_modify_id`, got the same record, didn't advance → infinite loop.

**Consequence**: Tables with exactly 1 record (or where the latest record has the same modify_id as the anchor) would reload endlessly.

**Fix**: Changed `>` to `>=` so the anchor record's modify_id is tracked and the anchor advances.

## Test Coverage

| Test | What it verifies |
|------|-----------------|
| `init_setsSharedState` | Module initialization loads constants correctly |
| `queryData_fullLoad_returnsGridData` | Full load populates grid and sets `last_modify_id` |
| `queryData_incrementalLoad_returnsOnlyChanges` | Incremental load adds new records to existing grid |
| `setCreateSchedule_setsFlag` | Schedule flag sets without error |
| `queryData_fullLoadSetsAnchor_incrementalFilters` | Full load anchor enables filtered incremental query |
| `changeIdxTracksAddRow` | New record tracked in `changeIdx[tableName].add[id]` |
| `changeIdxTracksModifyRow` | Updated record tracked in `changeIdx[tableName].modify[id]` |
| `changeIdxFullLoadNoTracking` | `changeIdx` is nil when incremental mode is off |
| `changeIdxNoUnderscoreField` | No `__` prefix fields in grid data |
| `changeIdxTrackSingleDelete` | Single deletion detected, removed from grid, tracked in `changeIdx[tableName].delete[id]` |
| `changeIdxMultiDelete` | Multiple deletions detected, surviving records stay in grid |
| `changeIdxMixed` | Insert + update + delete in one cycle, all tracked correctly |
| `queryData_noChanges_noFalsePositives` | No deletions or new records when nothing changed |
| `deleteEntryHasModifyTime` | Delete entry has `modify_time` field |

Run tests: `cd nc-server && lj ../nc-plugin/plugin/nc-calc-server/nc-calc-server-load-data-test.lua`
