-- 0012: bank statements and lines are born in their initial state — closes
-- a gap review found in 0011: bank_statements and bank_statement_lines had
-- NO born-state guard, so the app role could INSERT a row directly into a
-- terminal state, bypassing every function that's supposed to be the only
-- path there. Two exploits confirmed the gap:
--   - INSERT a bank_statement_lines row with status='matched' and
--     matched_entry_id pointing at ANY entry (even an unposted draft, or
--     one with the wrong account/amount) — none of match_statement_line()'s
--     five checks (line unlocked, statement open, entry posted, exactly-one-
--     line-on-account, sign-adjusted amount match) ever ran.
--   - INSERT a bank_statements row with status='reconciled' directly —
--     permanently bricked: it has no account_reconciliations row (only
--     reconcile_statement() writes one), so undo_reconciliation() raises
--     "never been reconciled", and bank_statement_lines_guard() (0011) now
--     freezes its lines forever since it reads the parent's status.
-- 0011 is already applied to the dev DB (forward-only migrations, CLAUDE.md
-- §2), so this fixes forward as its own migration rather than editing 0011.
--
-- Same two-layer armor as the rest of the codebase's born-draft pattern
-- (entries_born_draft in 0002, invoices_born_draft in 0005, bills_born_draft
-- in 0009): a BEFORE INSERT trigger (belt) that fires for every role,
-- including the superuser and any future SECURITY DEFINER function that
-- might try to shortcut it, PLUS column-restricted INSERT grants (braces)
-- so the app role's INSERT statements cannot even NAME the guarded columns
-- — a friendlier, earlier "permission denied for column" instead of a
-- runtime trigger exception for the common case.

CREATE FUNCTION bank_statements_born_open() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status <> 'open' THEN
    RAISE EXCEPTION 'statements are created open; reconcile_statement() finalizes them';
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER bank_statements_born_open
  BEFORE INSERT ON bank_statements
  FOR EACH ROW EXECUTE FUNCTION bank_statements_born_open();

CREATE FUNCTION bank_statement_lines_born_unmatched() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status <> 'unmatched'
     OR NEW.matched_entry_id IS NOT NULL
     OR NEW.suggestion_entry_id IS NOT NULL
     OR NEW.excluded_reason IS NOT NULL
  THEN
    RAISE EXCEPTION 'statement lines are created unmatched; use the matching functions';
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER bank_statement_lines_born_unmatched
  BEFORE INSERT ON bank_statement_lines
  FOR EACH ROW EXECUTE FUNCTION bank_statement_lines_born_unmatched();

-- Column-restricted INSERT grants: everything an import legitimately writes
-- at creation time, and NOTHING that only a matching/reconciling function
-- should ever set. Table-wide INSERT (granted by 0011) is revoked first;
-- SELECT and the other column-hardened UPDATE grants from 0011 are
-- untouched (REVOKE INSERT only touches the INSERT privilege).
REVOKE INSERT ON bank_statements FROM app;
GRANT INSERT (
  company_id, money_account_id, filename, file_sha256, format,
  period_start, period_end, ending_balance, created_by
) ON bank_statements TO app;

REVOKE INSERT ON bank_statement_lines FROM app;
GRANT INSERT (
  company_id, statement_id, money_account_id, line_no, posted_date,
  description, amount, fitid, raw
) ON bank_statement_lines TO app;
