-- 0011: bank reconciliation — statement import, matching, reconciled-through
-- guards, reconcile-first home. docs/superpowers/specs/2026-08-01-bank-rec-
-- design.md is binding. Same armor as AR/AP: composite FKs, forced RLS,
-- column-hardened grants, TRUNCATE guards, definer functions with explicit
-- session-company guards and races pre-solved (mirrors 0009's header
-- discipline — every hardening lesson baked in from the start, not patched
-- on later).
--
-- Core loop: import a statement for a money account -> every line becomes
-- matched (engine, exactly-one candidate), suggested (one click "Confirm"),
-- or new (categorize on the spot -> posts an entry) -> reconcile once every
-- line is settled and the statement's ending balance equals the account's
-- book balance for the period. Reconciling freezes that account through the
-- statement end date: post_entry() and reverse_entry_internal() below gain a
-- guard refusing anything that would change a balance the user already tied
-- to a bank statement.
--
-- account_reconciliations follows the period_locks pattern EXACTLY:
-- append-only, effective state = latest row, undo is a new row (never an
-- edit) that moves reconciled_through backward. Unlike period_locks (a bare
-- app INSERT protected only by the append-only triggers), this table grants
-- NO INSERT to app at all — every row is written by reconcile_statement() or
-- undo_reconciliation(), because unlike a lock date (which anyone may simply
-- assert), a reconciliation row's correctness depends on validation
-- (zero-unsettled, exact-balance-match) that only those functions perform.
--
-- One addition beyond the spec's named function list: suggest_statement_line()
-- — a small definer function giving the (TypeScript, Task 3) matching engine
-- a legal way to write status='suggested'/suggestion_entry_id, since those
-- columns are function-only per the spec's "column-hardened... status/match
-- fields function-only" instruction (the app role gets NO UPDATE grant at
-- all on bank_statement_lines). Documented here as a Task 1 addition, not a
-- deviation from the spec's intent.
--
-- One genuine DEVIATION from the spec's literal schema wording: the spec
-- lists account_reconciliations.book_balance as a stored column. It is NOT
-- one here — see the table's own comment below for why (CLAUDE.md §3.4 is a
-- higher-precedence rule than this spec, and tests/invariants/reporting.test.ts
-- already enforces it schema-wide). statement_balance IS kept, as is
-- bank_statements.ending_balance — both are external bank-statement facts,
-- not ledger-derived numbers, so §3.4 does not reach them.

CREATE TABLE bank_statements (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  money_account_id bigint NOT NULL,     -- must be type 'asset'; checked by trigger below
  filename text NOT NULL,
  file_sha256 text NOT NULL,
  format text NOT NULL CHECK (format IN ('csv', 'ofx')),
  period_start date,
  period_end date,
  ending_balance numeric(19,4),
  status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'reconciled')),
  created_by uuid NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  -- identical re-import of the same file against the same account is
  -- rejected outright (idempotency; CLAUDE.md §8's ADP lesson applied here).
  UNIQUE (company_id, money_account_id, file_sha256),
  FOREIGN KEY (company_id, money_account_id) REFERENCES accounts (company_id, id)
);
CREATE INDEX bank_statements_by_account ON bank_statements (company_id, money_account_id);
CREATE INDEX bank_statements_by_status ON bank_statements (company_id, status);

CREATE TABLE bank_statement_lines (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  statement_id bigint NOT NULL,
  -- denormalized from the parent statement (trigger-enforced equality below)
  -- purely so the two partial-unique indexes below can be per-account
  -- without a subquery — an index predicate/expression can't reach through
  -- a FK to another table.
  money_account_id bigint NOT NULL,
  line_no int NOT NULL,                 -- position in the imported file (dedupe tiebreak)
  posted_date date NOT NULL,
  description text,
  -- bank convention: SIGNED, positive = money INTO the account (a debit to
  -- the money account). CLAUDE.md §12: sign flips happen only in the
  -- presentation layer — this already IS the presentation-layer convention
  -- for a bank statement, so it is stored as-is, never re-signed.
  amount numeric(19,4) NOT NULL CHECK (amount <> 0),
  fitid text,                           -- OFX idempotency key, when present
  raw jsonb,                            -- the original parsed row, forever (CLAUDE.md §12)
  status text NOT NULL DEFAULT 'unmatched'
    CHECK (status IN ('unmatched', 'suggested', 'matched', 'created', 'excluded')),
  matched_entry_id bigint,              -- the ledger entry this line settles against
  suggestion_entry_id bigint,           -- the matching engine's candidate
  excluded_reason text,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  FOREIGN KEY (company_id, statement_id) REFERENCES bank_statements (company_id, id),
  FOREIGN KEY (company_id, money_account_id) REFERENCES accounts (company_id, id),
  FOREIGN KEY (company_id, matched_entry_id) REFERENCES journal_entries (company_id, id),
  FOREIGN KEY (company_id, suggestion_entry_id) REFERENCES journal_entries (company_id, id),
  CHECK ((status IN ('matched', 'created')) = (matched_entry_id IS NOT NULL)),
  CHECK ((status = 'excluded') = (excluded_reason IS NOT NULL))
);
CREATE INDEX bank_statement_lines_by_statement ON bank_statement_lines (company_id, statement_id);
CREATE INDEX bank_statement_lines_by_account_status
  ON bank_statement_lines (company_id, money_account_id, status);

-- FITID is the per-line idempotency key when present, unique per (company,
-- money account) — NOT globally, and NOT merely per-statement, since the
-- same account's statements from different date ranges must not collide,
-- while a DIFFERENT account may legitimately see the same FITID string.
CREATE UNIQUE INDEX bank_statement_lines_fitid_unique
  ON bank_statement_lines (company_id, money_account_id, fitid)
  WHERE fitid IS NOT NULL;

-- Settle-uniqueness: one statement line settles one journal entry, and one
-- journal entry is settled by at most one statement line, per account.
CREATE UNIQUE INDEX bank_statement_lines_settle_unique
  ON bank_statement_lines (company_id, money_account_id, matched_entry_id)
  WHERE matched_entry_id IS NOT NULL;

CREATE TABLE bank_csv_maps (
  company_id uuid NOT NULL REFERENCES companies (id),
  money_account_id bigint NOT NULL,
  -- column indexes, date format, amount convention (signed vs debit/credit
  -- pair), header row count — structured, but the exact shape is a Task 2/3
  -- (TypeScript parser) concern; kept as jsonb here deliberately, same
  -- reasoning as staging tables (CLAUDE.md §12: "loosely typed on purpose").
  mapping jsonb NOT NULL,
  updated_by uuid NOT NULL REFERENCES users (id),
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (company_id, money_account_id),
  FOREIGN KEY (company_id, money_account_id) REFERENCES accounts (company_id, id)
);

-- §3.3-style append-only log (mirrors period_locks EXACTLY): the effective
-- reconciled-through, per account, is the latest row. Undo is a NEW row that
-- moves it backward, never an edit to history.
--
-- DEVIATION from the spec's literal column list, made to satisfy a HIGHER-
-- precedence rule (CLAUDE.md §3.4/§12: "No balances are ever stored... Never
-- store a computed balance, even as a cache, without raising it first" — and
-- tests/invariants/reporting.test.ts enforces this by scanning the whole
-- schema for any '%balance%' column, already green before this migration):
-- the spec lists "statement_balance, book_balance (equal by construction at
-- creation)". statement_balance is kept — it is an EXTERNAL fact (what the
-- bank statement itself claims), no different in kind from bills.ref_no or
-- the raw jsonb import row (CLAUDE.md §12: keep the source data forever) —
-- it cannot be derived from journal_lines at all. book_balance, by contrast,
-- IS a ledger-derived number (SUM(debit-credit) as of a date) — exactly what
-- §3.4 forbids caching, and it is ALWAYS safely re-derivable here: posted
-- entries are immutable and the reconciled-through guard this migration adds
-- to post_entry()/reverse_entry_internal() guarantees nothing can ever post
-- or reverse into a reconciled period without first undoing it — so the
-- as-of book balance for a standing reconciliation can never drift from what
-- was true when it was created. It is therefore NOT a column; reports use
-- account_book_balance_asof() (below) to recompute it on demand.
-- reconcile_statement() still validates the equality at creation time —
-- "book_balance equal to statement_balance" is enforced procedurally
-- (RAISE on mismatch before the row is even inserted), just not stored.
CREATE TABLE account_reconciliations (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  money_account_id bigint NOT NULL,
  statement_id bigint,                  -- NULL on an undo/rollback row
  reconciled_through date,              -- NULL means "not reconciled" (undo-to-never)
  statement_balance numeric(19,4),      -- external fact: what the bank statement claimed
  created_by uuid NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  FOREIGN KEY (company_id, money_account_id) REFERENCES accounts (company_id, id),
  FOREIGN KEY (company_id, statement_id) REFERENCES bank_statements (company_id, id),
  -- a rollback row (no statement) carries no statement_balance either
  CHECK ((statement_id IS NULL) = (statement_balance IS NULL))
);
CREATE INDEX account_reconciliations_by_account
  ON account_reconciliations (company_id, money_account_id);

-- Derived-only (CLAUDE.md §3.4): the money account's book balance as of a
-- date, computed from journal_lines at query time — never stored. Mirrors
-- trial_balance()'s own as-of computation (0002) and AP's bill_open_balance_
-- asof() shape (0009). This is what a report shows for "book balance as of
-- the reconciliation" instead of a stored column.
CREATE FUNCTION account_book_balance_asof(p_company_id uuid, p_account_id bigint, p_as_of date)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT coalesce(sum(l.debit - l.credit), 0)::numeric(19,4)
    FROM journal_lines l
    JOIN journal_entries e ON e.company_id = l.company_id AND e.id = l.entry_id
   WHERE l.company_id = p_company_id AND l.account_id = p_account_id
     AND e.status = 'posted' AND e.entry_date <= p_as_of
$$;

-- Row-level security: ENABLE + FORCE everywhere, keyed to current_company_id().
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'bank_statements', 'bank_statement_lines', 'bank_csv_maps', 'account_reconciliations'
  ] LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
    EXECUTE format(
      'CREATE POLICY %I_isolation ON %I USING (company_id = current_company_id())',
      t, t);
  END LOOP;
END $$;

-- TRUNCATE never fires row triggers; forbidden outright on every bank-rec
-- table (forbid_truncate() already exists, from 0002).
CREATE TRIGGER bank_statements_no_truncate
  BEFORE TRUNCATE ON bank_statements FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER bank_statement_lines_no_truncate
  BEFORE TRUNCATE ON bank_statement_lines FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER bank_csv_maps_no_truncate
  BEFORE TRUNCATE ON bank_csv_maps FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER account_reconciliations_no_truncate
  BEFORE TRUNCATE ON account_reconciliations FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();

-- account_reconciliations is append-only, full stop (mirrors period_locks'
-- own forbid_change() trigger from 0002 — no UPDATE, no DELETE, not even by
-- the superuser).
CREATE TRIGGER account_reconciliations_append_only
  BEFORE UPDATE OR DELETE ON account_reconciliations
  FOR EACH ROW EXECUTE FUNCTION forbid_change();

-- bank_statements.money_account_id must be an asset account.
CREATE FUNCTION bank_statements_account_is_asset() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_type text;
BEGIN
  SELECT type INTO v_type FROM accounts
   WHERE company_id = NEW.company_id AND id = NEW.money_account_id;
  IF v_type IS NULL THEN
    RAISE EXCEPTION 'account % not found', NEW.money_account_id;
  END IF;
  IF v_type <> 'asset' THEN
    RAISE EXCEPTION
      'bank statements can only be imported against an asset account (account % is %)',
      NEW.money_account_id, v_type;
  END IF;
  RETURN NEW;
END $$;

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

-- Statement header immutability once reconciled: the ONLY legal transition
-- out of 'reconciled' is undo_reconciliation()'s own flip back to 'open'
-- (status alone changes; nothing else does). While 'open', anything the
-- app's column grants allow is fine — mirrors bills_immutable_when_issued's
-- shape (0009) but gated on 'reconciled' rather than "ever left draft".
CREATE FUNCTION bank_statements_immutable_when_reconciled() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.status <> 'reconciled' THEN
    RETURN NEW;
  END IF;

  IF NEW.status = 'open'
     AND NEW.money_account_id IS NOT DISTINCT FROM OLD.money_account_id
     AND NEW.filename         IS NOT DISTINCT FROM OLD.filename
     AND NEW.file_sha256      IS NOT DISTINCT FROM OLD.file_sha256
     AND NEW.format           IS NOT DISTINCT FROM OLD.format
     AND NEW.period_start     IS NOT DISTINCT FROM OLD.period_start
     AND NEW.period_end       IS NOT DISTINCT FROM OLD.period_end
     AND NEW.ending_balance   IS NOT DISTINCT FROM OLD.ending_balance
     AND NEW.created_by       IS NOT DISTINCT FROM OLD.created_by
  THEN
    RETURN NEW;
  END IF;

  RAISE EXCEPTION
    'statement % is reconciled and immutable; undo the reconciliation first', OLD.id;
END $$;

CREATE TRIGGER bank_statements_immutable_when_reconciled
  BEFORE UPDATE ON bank_statements
  FOR EACH ROW EXECUTE FUNCTION bank_statements_immutable_when_reconciled();

-- bank_statement_lines.money_account_id must equal its statement's account
-- (the denormalization this table carries for the partial-unique indexes
-- above must never drift from the source of truth).
CREATE FUNCTION bank_statement_lines_account_matches_statement() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_stmt_account bigint;
BEGIN
  SELECT money_account_id INTO v_stmt_account FROM bank_statements
   WHERE company_id = NEW.company_id AND id = NEW.statement_id;
  IF v_stmt_account IS NULL THEN
    RAISE EXCEPTION 'statement % not found', NEW.statement_id;
  END IF;
  IF NEW.money_account_id <> v_stmt_account THEN
    RAISE EXCEPTION 'statement line money_account_id must match its statement''s account';
  END IF;
  RETURN NEW;
END $$;

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

-- Lines are immutable except status/match fields (column-hardened via grants
-- below), and even those freeze once the parent statement is reconciled.
-- DELETE is never legal — a statement line is a permanent record of the
-- imported file; "removing" one from consideration is exclude_statement_line().
CREATE FUNCTION bank_statement_lines_guard() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_stmt_status text;
BEGIN
  IF TG_OP = 'DELETE' THEN
    RAISE EXCEPTION
      'statement lines are permanent; exclude line % instead of deleting it', OLD.id;
  END IF;

  IF NEW.company_id       IS DISTINCT FROM OLD.company_id
     OR NEW.statement_id     IS DISTINCT FROM OLD.statement_id
     OR NEW.money_account_id IS DISTINCT FROM OLD.money_account_id
     OR NEW.line_no          IS DISTINCT FROM OLD.line_no
     OR NEW.posted_date      IS DISTINCT FROM OLD.posted_date
     OR NEW.description      IS DISTINCT FROM OLD.description
     OR NEW.amount           IS DISTINCT FROM OLD.amount
     OR NEW.fitid            IS DISTINCT FROM OLD.fitid
     OR NEW.raw              IS DISTINCT FROM OLD.raw
     OR NEW.created_at       IS DISTINCT FROM OLD.created_at
  THEN
    RAISE EXCEPTION 'statement line % core fields are immutable', OLD.id;
  END IF;

  SELECT status INTO v_stmt_status FROM bank_statements
   WHERE company_id = OLD.company_id AND id = OLD.statement_id
   FOR SHARE;
  IF v_stmt_status = 'reconciled' THEN
    RAISE EXCEPTION
      'statement % is reconciled; its lines are frozen — undo the reconciliation first',
      OLD.statement_id;
  END IF;

  RETURN NEW;
END $$;

CREATE TRIGGER bank_statement_lines_guard
  BEFORE UPDATE OR DELETE ON bank_statement_lines
  FOR EACH ROW EXECUTE FUNCTION bank_statement_lines_guard();

-- Grants. bank_statements/bank_statement_lines are written at import time by
-- the app directly (mirrors bills/bill_lines being born-draft via plain
-- INSERT); every STATE TRANSITION after that is function-only.
GRANT SELECT, INSERT ON bank_statements TO app;
GRANT UPDATE (period_start, period_end, ending_balance) ON bank_statements TO app;

-- No UPDATE/DELETE grant at all: status, matched_entry_id, suggestion_entry_id
-- and excluded_reason are function-only (match/unmatch/exclude/restore/
-- suggest/create_entry_from_line, all SECURITY DEFINER below); core fields
-- are immutable forever regardless of role (bank_statement_lines_guard fires
-- for every role, app or superuser alike).
GRANT SELECT, INSERT ON bank_statement_lines TO app;

GRANT SELECT, INSERT, UPDATE ON bank_csv_maps TO app;

-- No INSERT: every row is written by reconcile_statement()/undo_reconciliation()
-- (SECURITY DEFINER), because — unlike period_locks — a reconciliation row's
-- correctness depends on validation only those functions perform.
GRANT SELECT ON account_reconciliations TO app;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;

-- §3 invariant 3-style effective state: the latest row wins (mirrors
-- effective_locked_through from 0002 exactly, including "plain SQL, no
-- SECURITY DEFINER" — RLS already scopes it correctly for any legitimate
-- caller, app or a DEFINER function alike).
CREATE FUNCTION effective_reconciled_through(p_company_id uuid, p_account_id bigint)
RETURNS date
LANGUAGE sql STABLE AS $$
  SELECT reconciled_through FROM account_reconciliations
   WHERE company_id = p_company_id AND money_account_id = p_account_id
   ORDER BY created_at DESC, id DESC
   LIMIT 1
$$;

-- Internal guard shared by post_entry() and reverse_entry_internal() below:
-- for each of the given accounts that has ANY reconciliation history, refuse
-- when p_date falls on/before that account's current effective
-- reconciled-through. English is exact per the spec's schema section (this
-- is the one RAISE text in this migration the spec quotes verbatim).
CREATE FUNCTION assert_not_reconciled(
  p_company_id uuid, p_date date, p_account_ids bigint[]
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_account_id bigint;
  v_thru date;
BEGIN
  IF p_account_ids IS NULL THEN
    RETURN;
  END IF;
  FOREACH v_account_id IN ARRAY p_account_ids LOOP
    v_thru := effective_reconciled_through(p_company_id, v_account_id);
    IF v_thru IS NOT NULL AND p_date <= v_thru THEN
      RAISE EXCEPTION
        'That account is reconciled through % — date it after % or undo the reconciliation.',
        v_thru, v_thru;
    END IF;
  END LOOP;
END $$;

REVOKE EXECUTE ON FUNCTION assert_not_reconciled(uuid, date, bigint[]) FROM PUBLIC;

-- post_entry(): CREATE OR REPLACE of the CURRENT definition (0002's original
-- — grepped across every later migration; never replaced before now, so
-- 0002's body IS the latest version). Every line below this comment down to
-- "INSERT INTO entry_counters" is byte-for-byte identical to 0002's
-- post_entry(); the ONLY addition is the reconciled-through guard block.
CREATE OR REPLACE FUNCTION post_entry(p_company_id uuid, p_entry_id bigint) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_entry journal_entries%ROWTYPE;
  v_no bigint;
  v_lock date;
  v_account_ids bigint[];
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match entry company';
  END IF;

  SELECT * INTO v_entry FROM journal_entries
   WHERE company_id = p_company_id AND id = p_entry_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'entry % not found', p_entry_id;
  END IF;
  IF v_entry.status = 'posted' THEN
    RAISE EXCEPTION 'entry % is already posted', p_entry_id;
  END IF;

  v_lock := effective_locked_through(p_company_id);
  IF v_lock IS NOT NULL AND v_entry.entry_date <= v_lock THEN
    RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
  END IF;

  -- 0011: refuse a line on an already-reconciled money account dated
  -- on/before that account's reconciled-through — the whole point of
  -- reconciling is that the balance is now tied to a bank statement.
  SELECT array_agg(DISTINCT l.account_id) INTO v_account_ids
    FROM journal_lines l
   WHERE l.company_id = p_company_id AND l.entry_id = p_entry_id;
  PERFORM assert_not_reconciled(p_company_id, v_entry.entry_date, v_account_ids);

  INSERT INTO entry_counters (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  SELECT next_entry_no INTO v_no FROM entry_counters
   WHERE company_id = p_company_id FOR UPDATE;
  UPDATE entry_counters SET next_entry_no = v_no + 1
   WHERE company_id = p_company_id;

  UPDATE journal_entries
     SET status = 'posted', entry_no = v_no, posted_at = now()
   WHERE company_id = p_company_id AND id = p_entry_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, v_entry.created_by, 'post', 'journal_entry',
          p_entry_id::text, jsonb_build_object('entry_no', v_no));

  RETURN v_no;
END $$;

-- reverse_entry_internal(): CREATE OR REPLACE of the CURRENT definition
-- (0002's original — likewise never replaced before now). Two additions,
-- both before any row is written (fail fast):
--   (a) the ORIGINAL entry's own lines must not fall inside a reconciled
--       period, on ANY reversal date — a reconciliation ties a specific
--       bank line to this exact transaction; reversing it (even dated well
--       after the reconciled-through) would invalidate that match. This is
--       NOT redundant with (b): an entry dated inside the reconciled window
--       can legally be reversed at a date AFTER reconciled-through (nothing
--       else would catch that combination — post_entry()'s own check below,
--       run against the NEW reversal entry, would pass, since the reversal
--       date itself is past reconciled-through).
--   (b) the reversal date itself must not land on/before reconciled-through
--       either (checked here explicitly for a fast, pre-INSERT failure with
--       identical English; post_entry() re-checks this anyway once it posts
--       the new entry below, since the reversal's lines share the SAME
--       accounts as the original — this second check is redundant with that
--       one but kept explicit per the plan, and costs nothing).
CREATE OR REPLACE FUNCTION reverse_entry_internal(
  p_company_id uuid, p_entry_id bigint, p_reversal_date date, p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_entry journal_entries%ROWTYPE;
  v_new_id bigint;
  v_account_ids bigint[];
BEGIN
  SELECT * INTO v_entry FROM journal_entries
   WHERE company_id = p_company_id AND id = p_entry_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'entry % not found', p_entry_id;
  END IF;
  IF v_entry.status <> 'posted' THEN
    RAISE EXCEPTION 'only posted entries can be reversed';
  END IF;
  IF p_reversal_date < v_entry.entry_date THEN
    RAISE EXCEPTION 'reversal date cannot precede the entry it reverses';
  END IF;

  SELECT array_agg(DISTINCT l.account_id) INTO v_account_ids
    FROM journal_lines l
   WHERE l.company_id = p_company_id AND l.entry_id = p_entry_id;

  -- (a) the entry being reversed must not itself fall inside a reconciled period
  PERFORM assert_not_reconciled(p_company_id, v_entry.entry_date, v_account_ids);
  -- (b) the reversal's own date must not land back inside one either
  PERFORM assert_not_reconciled(p_company_id, p_reversal_date, v_account_ids);

  BEGIN
    INSERT INTO journal_entries
      (company_id, entry_date, memo, created_by, reverses_entry_id)
    VALUES
      (p_company_id, p_reversal_date,
       'Reversal of entry #' || v_entry.entry_no, p_user_id, p_entry_id)
    RETURNING id INTO v_new_id;
  EXCEPTION WHEN unique_violation THEN
    RAISE EXCEPTION 'entry % has already been reversed', p_entry_id;
  END;

  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit, memo)
  SELECT company_id, v_new_id, account_id, credit, debit, memo  -- swapped
    FROM journal_lines
   WHERE company_id = p_company_id AND entry_id = p_entry_id;

  PERFORM post_entry(p_company_id, v_new_id);
  RETURN v_new_id;
END $$;

-- match_statement_line: validates the candidate entry has exactly one line
-- on the statement's money account, sign-adjusted amount equal to the bank
-- line's, posted, not already settled by another line; the LINE row is
-- locked FIRST (blocks a concurrent double-match of the SAME line), then the
-- ENTRY row is locked (blocks a concurrent match of a SECOND line to the
-- SAME entry — the settle-uniqueness check below then always observes the
-- other transaction's committed write instead of racing the partial unique
-- index at commit). Consistent lock order (line, then entry) across every
-- call rules out a deadlock between two concurrent matches.
CREATE FUNCTION match_statement_line(
  p_company_id uuid, p_line_id bigint, p_entry_id bigint, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_line bank_statement_lines%ROWTYPE;
  v_stmt_status text;
  v_entry journal_entries%ROWTYPE;
  v_line_count int;
  v_debit numeric(19,4);
  v_credit numeric(19,4);
  v_settled_by bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_line FROM bank_statement_lines
   WHERE company_id = p_company_id AND id = p_line_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'statement line % not found', p_line_id;
  END IF;
  IF v_line.status NOT IN ('unmatched', 'suggested') THEN
    RAISE EXCEPTION 'statement line % is already %; unmatch it first', p_line_id, v_line.status;
  END IF;

  SELECT status INTO v_stmt_status FROM bank_statements
   WHERE company_id = p_company_id AND id = v_line.statement_id
   FOR SHARE;
  IF v_stmt_status <> 'open' THEN
    RAISE EXCEPTION
      'statement % is reconciled; its lines are frozen — undo the reconciliation first',
      v_line.statement_id;
  END IF;

  SELECT * INTO v_entry FROM journal_entries
   WHERE company_id = p_company_id AND id = p_entry_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'entry % not found', p_entry_id;
  END IF;
  IF v_entry.status <> 'posted' THEN
    RAISE EXCEPTION 'entry % is not posted', p_entry_id;
  END IF;

  SELECT count(*), coalesce(sum(debit), 0), coalesce(sum(credit), 0)
    INTO v_line_count, v_debit, v_credit
    FROM journal_lines
   WHERE company_id = p_company_id AND entry_id = p_entry_id
     AND account_id = v_line.money_account_id;
  IF v_line_count <> 1 THEN
    RAISE EXCEPTION
      'entry % must carry exactly one line on this account to be matched (found %)',
      p_entry_id, v_line_count;
  END IF;
  IF (v_debit - v_credit) <> v_line.amount THEN
    RAISE EXCEPTION
      'entry % does not match the statement line amount (entry % vs statement %)',
      p_entry_id, (v_debit - v_credit), v_line.amount;
  END IF;

  SELECT id INTO v_settled_by FROM bank_statement_lines
   WHERE company_id = p_company_id AND money_account_id = v_line.money_account_id
     AND matched_entry_id = p_entry_id
   LIMIT 1;
  IF v_settled_by IS NOT NULL THEN
    RAISE EXCEPTION 'entry % is already settled by statement line %', p_entry_id, v_settled_by;
  END IF;

  UPDATE bank_statement_lines
     SET status = 'matched', matched_entry_id = p_entry_id
   WHERE company_id = p_company_id AND id = p_line_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'match', 'bank_statement_line', p_line_id::text,
          jsonb_build_object('entry_id', p_entry_id));
END $$;

-- unmatch_statement_line: open statements only (frozen once reconciled).
-- Only frees the statement line — the underlying journal entry, whether
-- pre-existing or created via create_entry_from_line(), is untouched and
-- remains posted (CLAUDE.md §3.2: posted entries are immutable regardless).
CREATE FUNCTION unmatch_statement_line(p_company_id uuid, p_line_id bigint, p_user_id uuid)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_line bank_statement_lines%ROWTYPE;
  v_stmt_status text;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_line FROM bank_statement_lines
   WHERE company_id = p_company_id AND id = p_line_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'statement line % not found', p_line_id;
  END IF;
  IF v_line.status NOT IN ('matched', 'created') THEN
    RAISE EXCEPTION 'statement line % is not matched', p_line_id;
  END IF;

  SELECT status INTO v_stmt_status FROM bank_statements
   WHERE company_id = p_company_id AND id = v_line.statement_id
   FOR SHARE;
  IF v_stmt_status <> 'open' THEN
    RAISE EXCEPTION
      'statement % is reconciled; its lines are frozen — undo the reconciliation first',
      v_line.statement_id;
  END IF;

  UPDATE bank_statement_lines
     SET status = 'unmatched', matched_entry_id = NULL
   WHERE company_id = p_company_id AND id = p_line_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'unmatch', 'bank_statement_line', p_line_id::text,
          jsonb_build_object('was_entry_id', v_line.matched_entry_id));
END $$;

-- suggest_statement_line: the (Task 3, TypeScript) matching engine's only
-- legal write path, since status/suggestion_entry_id are function-only.
-- Silently a no-op once a line is past unmatched/suggested (the engine may
-- re-run at any time; a line the user has already acted on is not the
-- engine's business anymore — not an error, just moot).
CREATE FUNCTION suggest_statement_line(
  p_company_id uuid, p_line_id bigint, p_entry_id bigint
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_status text;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT status INTO v_status FROM bank_statement_lines
   WHERE company_id = p_company_id AND id = p_line_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'statement line % not found', p_line_id;
  END IF;
  IF v_status NOT IN ('unmatched', 'suggested') THEN
    RETURN;
  END IF;

  IF p_entry_id IS NULL THEN
    UPDATE bank_statement_lines SET status = 'unmatched', suggestion_entry_id = NULL
     WHERE company_id = p_company_id AND id = p_line_id;
  ELSE
    UPDATE bank_statement_lines SET status = 'suggested', suggestion_entry_id = p_entry_id
     WHERE company_id = p_company_id AND id = p_line_id;
  END IF;
END $$;

-- exclude_statement_line: a reason is required (English health-panel /
-- audit-trail value); open statements only.
CREATE FUNCTION exclude_statement_line(
  p_company_id uuid, p_line_id bigint, p_reason text, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_line bank_statement_lines%ROWTYPE;
  v_stmt_status text;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;
  IF p_reason IS NULL OR btrim(p_reason) = '' THEN
    RAISE EXCEPTION 'a reason is required to exclude a statement line';
  END IF;

  SELECT * INTO v_line FROM bank_statement_lines
   WHERE company_id = p_company_id AND id = p_line_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'statement line % not found', p_line_id;
  END IF;
  IF v_line.status NOT IN ('unmatched', 'suggested') THEN
    RAISE EXCEPTION 'statement line % is % — unmatch it before excluding', p_line_id, v_line.status;
  END IF;

  SELECT status INTO v_stmt_status FROM bank_statements
   WHERE company_id = p_company_id AND id = v_line.statement_id
   FOR SHARE;
  IF v_stmt_status <> 'open' THEN
    RAISE EXCEPTION
      'statement % is reconciled; its lines are frozen — undo the reconciliation first',
      v_line.statement_id;
  END IF;

  UPDATE bank_statement_lines
     SET status = 'excluded', excluded_reason = p_reason
   WHERE company_id = p_company_id AND id = p_line_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'exclude', 'bank_statement_line', p_line_id::text,
          jsonb_build_object('reason', p_reason));
END $$;

-- restore_statement_line: the mirror of exclude — back to 'unmatched'.
CREATE FUNCTION restore_statement_line(p_company_id uuid, p_line_id bigint, p_user_id uuid)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_line bank_statement_lines%ROWTYPE;
  v_stmt_status text;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_line FROM bank_statement_lines
   WHERE company_id = p_company_id AND id = p_line_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'statement line % not found', p_line_id;
  END IF;
  IF v_line.status <> 'excluded' THEN
    RAISE EXCEPTION 'statement line % is not excluded', p_line_id;
  END IF;

  SELECT status INTO v_stmt_status FROM bank_statements
   WHERE company_id = p_company_id AND id = v_line.statement_id
   FOR SHARE;
  IF v_stmt_status <> 'open' THEN
    RAISE EXCEPTION
      'statement % is reconciled; its lines are frozen — undo the reconciliation first',
      v_line.statement_id;
  END IF;

  UPDATE bank_statement_lines
     SET status = 'unmatched', excluded_reason = NULL
   WHERE company_id = p_company_id AND id = p_line_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'restore', 'bank_statement_line', p_line_id::text, '{}'::jsonb);
END $$;

-- create_entry_from_line: builds the balanced entry for a "new" line
-- (positive bank amount -> debit money account / credit category; negative
-- -> mirrored), posts it via post_entry() (so period-lock AND
-- reconciled-through guards both apply), marks the line 'created' +
-- matched_entry_id. p_spec: jsonb {categoryAccountId, who?, memo?}.
CREATE FUNCTION create_entry_from_line(
  p_company_id uuid, p_line_id bigint, p_spec jsonb, p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_line bank_statement_lines%ROWTYPE;
  v_stmt_status text;
  v_category_account bigint;
  v_who text;
  v_memo text;
  v_entry_id bigint;
  v_amount numeric(19,4);
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  v_category_account := nullif(p_spec->>'categoryAccountId', '')::bigint;
  v_who := nullif(p_spec->>'who', '');
  v_memo := nullif(p_spec->>'memo', '');
  IF v_category_account IS NULL THEN
    RAISE EXCEPTION 'a category account is required';
  END IF;

  SELECT * INTO v_line FROM bank_statement_lines
   WHERE company_id = p_company_id AND id = p_line_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'statement line % not found', p_line_id;
  END IF;
  IF v_line.status NOT IN ('unmatched', 'suggested') THEN
    RAISE EXCEPTION 'statement line % is already %', p_line_id, v_line.status;
  END IF;

  SELECT status INTO v_stmt_status FROM bank_statements
   WHERE company_id = p_company_id AND id = v_line.statement_id
   FOR SHARE;
  IF v_stmt_status <> 'open' THEN
    RAISE EXCEPTION
      'statement % is reconciled; its lines are frozen — undo the reconciliation first',
      v_line.statement_id;
  END IF;

  IF v_category_account = v_line.money_account_id THEN
    RAISE EXCEPTION 'category account cannot be the money account itself';
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM accounts WHERE company_id = p_company_id AND id = v_category_account
  ) THEN
    RAISE EXCEPTION 'account % not found', v_category_account;
  END IF;

  v_amount := abs(v_line.amount);

  INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
  VALUES (p_company_id, v_line.posted_date,
          coalesce(v_memo, v_who, v_line.description, 'Bank transaction'), p_user_id)
  RETURNING id INTO v_entry_id;

  IF v_line.amount > 0 THEN
    -- money IN: debit the money account, credit the category
    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    VALUES (p_company_id, v_entry_id, v_line.money_account_id, v_amount, 0),
           (p_company_id, v_entry_id, v_category_account, 0, v_amount);
  ELSE
    -- money OUT: credit the money account, debit the category
    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    VALUES (p_company_id, v_entry_id, v_category_account, v_amount, 0),
           (p_company_id, v_entry_id, v_line.money_account_id, 0, v_amount);
  END IF;

  PERFORM post_entry(p_company_id, v_entry_id);

  UPDATE bank_statement_lines
     SET status = 'created', matched_entry_id = v_entry_id
   WHERE company_id = p_company_id AND id = p_line_id;

  RETURN v_entry_id;
END $$;

-- reconcile_statement: requires zero unmatched/suggested lines and an exact
-- match between the supplied endingBalance and the account's computed book
-- balance through throughDate; inserts the append-only reconciliation row
-- and flips the statement to 'reconciled' (freezing its lines). Takes an
-- account-level advisory lock BEFORE any row lock (same acquisition order as
-- undo_reconciliation below) so the two can never deadlock against each
-- other, and so two concurrent reconciliations touching the SAME account
-- always serialize instead of racing the "latest row wins" append-only read.
CREATE FUNCTION reconcile_statement(
  p_company_id uuid, p_statement_id bigint, p_spec jsonb, p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_account_id bigint;
  v_stmt bank_statements%ROWTYPE;
  v_ending numeric(19,4);
  v_through date;
  v_unsettled int;
  v_book numeric(19,4);
  v_diff numeric(19,4);
  v_recon_id bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT money_account_id INTO v_account_id FROM bank_statements
   WHERE company_id = p_company_id AND id = p_statement_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'statement % not found', p_statement_id;
  END IF;

  PERFORM pg_advisory_xact_lock(hashtextextended(
    'account_reconciliation:' || p_company_id::text || ':' || v_account_id::text, 0));

  SELECT * INTO v_stmt FROM bank_statements
   WHERE company_id = p_company_id AND id = p_statement_id
   FOR UPDATE;
  IF v_stmt.status <> 'open' THEN
    RAISE EXCEPTION 'statement % is already reconciled', p_statement_id;
  END IF;

  v_ending := nullif(p_spec->>'endingBalance', '')::numeric(19,4);
  v_through := nullif(p_spec->>'throughDate', '')::date;
  IF v_ending IS NULL OR v_through IS NULL THEN
    RAISE EXCEPTION 'endingBalance and throughDate are required to reconcile';
  END IF;

  SELECT count(*) INTO v_unsettled FROM bank_statement_lines
   WHERE company_id = p_company_id AND statement_id = p_statement_id
     AND status IN ('unmatched', 'suggested');
  IF v_unsettled > 0 THEN
    RAISE EXCEPTION
      '% statement line(s) still need a decision before this statement can be reconciled',
      v_unsettled;
  END IF;

  -- book_balance is validated here (equality gate) but deliberately NOT
  -- persisted — see the account_reconciliations table comment above.
  v_book := account_book_balance_asof(p_company_id, v_stmt.money_account_id, v_through);

  v_diff := (v_ending - v_book)::numeric(19,4);
  IF v_diff > 0 THEN
    RAISE EXCEPTION
      'this doesn''t add up — $% more came in at the bank than is in your books',
      to_char(v_diff, 'FM999999999990.00');
  ELSIF v_diff < 0 THEN
    RAISE EXCEPTION
      'this doesn''t add up — $% more is in your books than came in at the bank',
      to_char(abs(v_diff), 'FM999999999990.00');
  END IF;

  INSERT INTO account_reconciliations
    (company_id, money_account_id, statement_id, reconciled_through,
     statement_balance, created_by)
  VALUES
    (p_company_id, v_stmt.money_account_id, p_statement_id, v_through, v_ending, p_user_id)
  RETURNING id INTO v_recon_id;

  UPDATE bank_statements
     SET status = 'reconciled', period_end = v_through, ending_balance = v_ending
   WHERE company_id = p_company_id AND id = p_statement_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'reconcile', 'bank_statement', p_statement_id::text,
          jsonb_build_object('reconciled_through', v_through, 'ending_balance', v_ending));

  RETURN v_recon_id;
END $$;

-- undo_reconciliation: operates per ACCOUNT (per the API shape: {accountId},
-- not a statement id) — appends a rollback row moving reconciled_through
-- back to the previous statement's reconciled_through (or NULL if there was
-- none), and flips the statement that WAS latest back to 'open' so its lines
-- thaw. Append-only: this is a NEW row, never an edit to the row it undoes.
-- Same advisory-lock-before-any-row-lock ordering as reconcile_statement().
CREATE FUNCTION undo_reconciliation(p_company_id uuid, p_account_id bigint, p_user_id uuid)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_latest account_reconciliations%ROWTYPE;
  v_prev_through date;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  PERFORM pg_advisory_xact_lock(hashtextextended(
    'account_reconciliation:' || p_company_id::text || ':' || p_account_id::text, 0));

  SELECT * INTO v_latest FROM account_reconciliations
   WHERE company_id = p_company_id AND money_account_id = p_account_id
   ORDER BY created_at DESC, id DESC
   LIMIT 1;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'account % has never been reconciled', p_account_id;
  END IF;
  IF v_latest.reconciled_through IS NULL THEN
    RAISE EXCEPTION 'account % is not currently reconciled', p_account_id;
  END IF;

  SELECT reconciled_through INTO v_prev_through FROM account_reconciliations
   WHERE company_id = p_company_id AND money_account_id = p_account_id
     AND id <> v_latest.id
   ORDER BY created_at DESC, id DESC
   LIMIT 1;

  INSERT INTO account_reconciliations
    (company_id, money_account_id, statement_id, reconciled_through,
     statement_balance, created_by)
  VALUES
    (p_company_id, p_account_id, NULL, v_prev_through, NULL, p_user_id);

  IF v_latest.statement_id IS NOT NULL THEN
    UPDATE bank_statements SET status = 'open'
     WHERE company_id = p_company_id AND id = v_latest.statement_id;
  END IF;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'undo_reconciliation', 'account_reconciliation',
          p_account_id::text,
          jsonb_build_object('was_through', v_latest.reconciled_through, 'now_through', v_prev_through));
END $$;
