-- CSV backfill import (csv-backfill-import). A connected account whose SimpleFIN
-- feed reaches back only a week has a permanent hole in its past that no sync can
-- fill; the bank publishes that history as a CSV. Imports are additive only: they
-- attach to an account sync already discovered, and never reconcile, sweep, or
-- overwrite anything sync owns.

-- One row per uploaded file. `payload` is verbatim and never mutated, mirroring
-- raw_syncs. `mapping` and `decisions` live here too because the outcome of an
-- import is a function of the bytes AND the human's choices — bytes alone would
-- not replay deterministically.
CREATE TABLE imports (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	account_id TEXT NOT NULL REFERENCES accounts (id),
	filename TEXT,
	uploaded_at TEXT NOT NULL,
	payload TEXT NOT NULL, -- verbatim uploaded bytes; never mutated
	mapping TEXT, -- JSON: field -> column, chosen by the user
	decisions TEXT, -- JSON: synthetic id -> 'keep' | 'skip'
	status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'committed', 'undone')),
	committed_at TEXT,
	undone_at TEXT
);

-- NULL means synced, so every pre-existing row is already correct and no backfill
-- is needed. Carries origin for the ledger source filter and scopes undo.
ALTER TABLE transactions ADD COLUMN import_id INTEGER REFERENCES imports (id);

CREATE INDEX idx_transactions_import ON transactions (import_id);