-- LOAD CYCLE (sync): teaches a dataset the difference between "this record
-- changed" and "this record still exists".
--
-- Until now ingestion could only ever ADD or UPDATE: a record deleted at the
-- source stayed in dataset_records forever, so every aggregation the agent ran
-- kept summing a cancelled invoice. Deleting on the client side is not an
-- option either — the connector reads a table, it does not receive delete
-- events.
--
-- The fix is the classic full-load sweep. Every batch of one load carries the
-- same `sync` identifier, which is stamped on EVERY record it touches —
-- including the ones whose content did not change (being *seen* in this load
-- is what matters, not being *modified*). The last batch carries
-- `complete: true`, and only then is everything still holding an older token
-- marked removed_at = NOW(). A load that dies half-way therefore removes
-- NOTHING, which is exactly the safe failure mode.
--
-- removed_at is a soft delete on purpose: the row survives (provenance,
-- auditing, "what disappeared last night?"), the dataset VIEW filters it out,
-- and a record that comes back in a later load is simply resurrected by
-- setting removed_at back to NULL.
ALTER TABLE dataset_records
  ADD COLUMN sync_token VARCHAR(64) NULL AFTER content_hash,
  ADD COLUMN removed_at DATETIME NULL AFTER sync_token,
  -- The view filters on (dataset_id, removed_at IS NULL): this is the index
  -- that keeps it from scanning the removed rows of a big dataset.
  ADD KEY idx_dataset_records_active (dataset_id, removed_at),
  -- The sweep looks for "this dataset, token <> current": same leading column,
  -- second column the token.
  ADD KEY idx_dataset_records_sync (dataset_id, sync_token);

-- record_count now counts only the ACTIVE records (what the agent can see);
-- removed_count is kept beside it so the panel can show the load cycle working
-- instead of a number that silently shrank.
ALTER TABLE datasets
  ADD COLUMN last_sync VARCHAR(64) NULL AFTER last_ingested_at,
  ADD COLUMN last_sync_at DATETIME NULL AFTER last_sync,
  ADD COLUMN removed_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER record_count;