import { describe, it, expect, beforeAll, afterAll } from "vitest";
import { createTestDb, type TestDb, insertDraftEntry, postEntry } from "../helpers/db.js";
// Races below hold a statement open across an await. inFlight() attaches its
// rejection handler at creation time, which is what stops an early failure from
// becoming an unhandled rejection that silently kills this whole worker — see
// the header of tests/helpers/in-flight.ts.
import { inFlight } from "../helpers/in-flight.js";
import type { Client } from "pg";

let db: TestDb;
let c: Client; // app client scoped to companyA

beforeAll(async () => {
  db = await createTestDb();
  c = await db.appClient(db.companyA);
}, 60_000);
afterAll(async () => {
  await db.close();
});

// ---- local helpers -------------------------------------------------------

/** A fresh asset ("bank") account for a company — isolated per test so
 * FITID/settle/reconciliation state from one test never leaks into another
 * purely by file order (mirrors ap.test.ts's createMoneyAccount). */
let moneyAccountSeq = 0;
async function createMoneyAccount(companyId: string): Promise<number> {
  moneyAccountSeq += 1;
  const r = await db.admin.query(
    `INSERT INTO accounts (company_id, code, name, type) VALUES ($1, $2, $3, 'asset') RETURNING id`,
    [companyId, "BANK-" + moneyAccountSeq, "Test Bank " + moneyAccountSeq]);
  return Number(r.rows[0].id);
}

interface StatementOpts {
  filename?: string;
  sha256?: string;
  format?: "csv" | "ofx";
}
let sha256Seq = 0;
async function createStatement(
  client: Client, companyId: string, userId: string, accountId: number, opts: StatementOpts = {},
): Promise<number> {
  sha256Seq += 1;
  const r = await client.query(
    `INSERT INTO bank_statements (company_id, money_account_id, filename, file_sha256, format, created_by)
     VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`,
    [companyId, accountId, opts.filename ?? "statement.csv",
     opts.sha256 ?? "sha-" + sha256Seq, opts.format ?? "csv", userId]);
  return Number(r.rows[0].id);
}

interface LineOpts {
  lineNo?: number;
  postedDate?: string;
  description?: string;
  fitid?: string | null;
}
let lineNoSeq = 0;
async function addLine(
  client: Client, companyId: string, statementId: number, accountId: number,
  amount: string, opts: LineOpts = {},
): Promise<number> {
  lineNoSeq += 1;
  const r = await client.query(
    `INSERT INTO bank_statement_lines
       (company_id, statement_id, money_account_id, line_no, posted_date, description, amount, fitid)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id`,
    [companyId, statementId, accountId, opts.lineNo ?? lineNoSeq,
     opts.postedDate ?? "2025-06-05", opts.description ?? "txn", amount, opts.fitid ?? null]);
  return Number(r.rows[0].id);
}

async function matchLine(client: Client, companyId: string, lineId: number, entryId: number, userId: string) {
  await client.query("SELECT match_statement_line($1, $2, $3, $4)", [companyId, lineId, entryId, userId]);
}
async function unmatchLine(client: Client, companyId: string, lineId: number, userId: string) {
  await client.query("SELECT unmatch_statement_line($1, $2, $3)", [companyId, lineId, userId]);
}
async function excludeLine(client: Client, companyId: string, lineId: number, reason: string, userId: string) {
  await client.query("SELECT exclude_statement_line($1, $2, $3, $4)", [companyId, lineId, reason, userId]);
}
async function restoreLine(client: Client, companyId: string, lineId: number, userId: string) {
  await client.query("SELECT restore_statement_line($1, $2, $3)", [companyId, lineId, userId]);
}
async function suggestLine(client: Client, companyId: string, lineId: number, entryId: number | null) {
  await client.query("SELECT suggest_statement_line($1, $2, $3)", [companyId, lineId, entryId]);
}
interface CreateEntrySpec { categoryAccountId: number; who?: string; memo?: string }
async function createEntryFromLine(
  client: Client, companyId: string, lineId: number, spec: CreateEntrySpec, userId: string,
): Promise<number> {
  const jsonSpec = { categoryAccountId: spec.categoryAccountId, who: spec.who ?? null, memo: spec.memo ?? null };
  const r = await client.query(
    "SELECT create_entry_from_line($1, $2, $3::jsonb, $4) AS id",
    [companyId, lineId, JSON.stringify(jsonSpec), userId]);
  return Number(r.rows[0].id);
}
async function reconcileStatement(
  client: Client, companyId: string, statementId: number,
  spec: { endingBalance: string; throughDate: string }, userId: string,
): Promise<number> {
  const jsonSpec = { endingBalance: spec.endingBalance, throughDate: spec.throughDate };
  const r = await client.query(
    "SELECT reconcile_statement($1, $2, $3::jsonb, $4) AS id",
    [companyId, statementId, JSON.stringify(jsonSpec), userId]);
  return Number(r.rows[0].id);
}
async function undoReconciliation(client: Client, companyId: string, accountId: number, userId: string) {
  await client.query("SELECT undo_reconciliation($1, $2, $3)", [companyId, accountId, userId]);
}

/** node-pg returns `date` columns as JS Date objects (local-midnight),
 * whose default String() is a locale string ("Tue Jun 10 2025 ..."), not
 * ISO — normalize to YYYY-MM-DD for comparisons. */
function dateStr(v: unknown): string {
  if (v instanceof Date) {
    const y = v.getFullYear();
    const m = String(v.getMonth() + 1).padStart(2, "0");
    const d = String(v.getDate()).padStart(2, "0");
    return `${y}-${m}-${d}`;
  }
  return String(v).slice(0, 10);
}

async function lineStatus(client: Client, lineId: number): Promise<string> {
  const r = await client.query("SELECT status FROM bank_statement_lines WHERE id = $1", [lineId]);
  return r.rows[0].status;
}

/** Post a manual balanced entry with one line on `accountId` (the money
 * account) for `amount` (positive => debit money account) and the mirror
 * line on the given offset account. Returns the posted entry id. */
async function postMoneyEntry(
  client: Client, companyId: string, userId: string,
  accountId: number, offsetAccountId: number, amount: string, date = "2025-06-05",
): Promise<number> {
  const isPositive = !amount.startsWith("-");
  const abs = amount.replace("-", "");
  const id = await insertDraftEntry(client, companyId, userId, {
    date,
    lines: isPositive
      ? [{ accountId, debit: abs }, { accountId: offsetAccountId, credit: abs }]
      : [{ accountId: offsetAccountId, debit: abs }, { accountId, credit: abs }],
  });
  await postEntry(client, companyId, id);
  return id;
}

// ---- tests ----------------------------------------------------------------

describe("bank_statements", () => {
  it("can only be imported against an asset account", async () => {
    await expect(
      createStatement(c, db.companyA, db.userA, db.accounts["4000"].A),
    ).rejects.toThrow(/asset account/);
  });

  it("identical file re-import against the same account is rejected", async () => {
    const account = await createMoneyAccount(db.companyA);
    await createStatement(c, db.companyA, db.userA, account, { sha256: "dup-sha" });
    await expect(
      createStatement(c, db.companyA, db.userA, account, { sha256: "dup-sha" }),
    ).rejects.toThrow(/duplicate key|unique/);
  });

  it("the same file sha256 is fine against a DIFFERENT account, or a different company", async () => {
    const accountA1 = await createMoneyAccount(db.companyA);
    const accountA2 = await createMoneyAccount(db.companyA);
    await createStatement(c, db.companyA, db.userA, accountA1, { sha256: "shared-sha" });
    await expect(
      createStatement(c, db.companyA, db.userA, accountA2, { sha256: "shared-sha" }),
    ).resolves.toBeGreaterThan(0);

    const cB = await db.appClient(db.companyB);
    await expect(
      createStatement(cB, db.companyB, db.userB, db.accounts["1000"].B, { sha256: "shared-sha" }),
    ).resolves.toBeGreaterThan(0);
  });
});

describe("bank_statement_lines", () => {
  it("money_account_id must match its statement's account", async () => {
    const account = await createMoneyAccount(db.companyA);
    const otherAccount = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    await expect(
      c.query(
        `INSERT INTO bank_statement_lines
           (company_id, statement_id, money_account_id, line_no, posted_date, amount)
         VALUES ($1, $2, $3, 1, '2025-06-01', '10.0000')`,
        [db.companyA, stmt, otherAccount]),
    ).rejects.toThrow(/must match its statement/);
  });

  it("amount cannot be zero", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    await expect(addLine(c, db.companyA, stmt, account, "0.0000")).rejects.toThrow(/violates check/);
  });

  it("FITID is unique per (company, money account), not globally", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    await addLine(c, db.companyA, stmt, account, "10.0000", { fitid: "FIT-1" });
    await expect(
      addLine(c, db.companyA, stmt, account, "20.0000", { fitid: "FIT-1" }),
    ).rejects.toThrow(/duplicate key|unique/);

    // a different statement on the SAME account still collides (per-account, not per-statement)
    const stmt2 = await createStatement(c, db.companyA, db.userA, account, { sha256: "other-file" });
    await expect(
      addLine(c, db.companyA, stmt2, account, "30.0000", { fitid: "FIT-1" }),
    ).rejects.toThrow(/duplicate key|unique/);

    // a DIFFERENT account has its own FITID space
    const account2 = await createMoneyAccount(db.companyA);
    const stmt3 = await createStatement(c, db.companyA, db.userA, account2);
    await expect(
      addLine(c, db.companyA, stmt3, account2, "10.0000", { fitid: "FIT-1" }),
    ).resolves.toBeGreaterThan(0);

    // multiple lines with NO fitid coexist freely
    await expect(addLine(c, db.companyA, stmt, account, "5.0000")).resolves.toBeGreaterThan(0);
    await expect(addLine(c, db.companyA, stmt, account, "6.0000")).resolves.toBeGreaterThan(0);
  });

  it("core fields are immutable; DELETE is forbidden even for the superuser", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    await expect(
      db.admin.query("UPDATE bank_statement_lines SET amount = '999.0000' WHERE id = $1", [line]),
    ).rejects.toThrow(/core fields are immutable/);
    await expect(
      db.admin.query("DELETE FROM bank_statement_lines WHERE id = $1", [line]),
    ).rejects.toThrow(/permanent/);
  });

  it("the app role has no UPDATE grant at all — every transition is function-only", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    await expect(
      c.query("UPDATE bank_statement_lines SET status = 'matched' WHERE id = $1", [line]),
    ).rejects.toThrow(/permission denied/);
  });

  it("TRUNCATE is forbidden on every bank-rec table, even for the superuser", async () => {
    for (const table of [
      "bank_statements", "bank_statement_lines", "bank_csv_maps", "account_reconciliations",
    ]) {
      await expect(db.admin.query(`TRUNCATE ${table}`))
        .rejects.toThrow(/TRUNCATE is not allowed|foreign key/);
    }
  });
});

describe("born-state guards (0012)", () => {
  it("trigger layer: a statement line cannot be born already 'matched' — the exploit that bypassed every match_statement_line() check", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    // an unposted DRAFT entry — match_statement_line() would refuse this
    // outright ("not posted"), which is exactly why the exploit skipped it
    const draft = await insertDraftEntry(c, db.companyA, db.userA, {
      lines: [
        { accountId: account, debit: "10.0000" },
        { accountId: db.accounts["4000"].A, credit: "10.0000" },
      ],
    });
    // superuser bypasses the column-restricted grants, exercising the TRIGGER itself
    await expect(
      db.admin.query(
        `INSERT INTO bank_statement_lines
           (company_id, statement_id, money_account_id, line_no, posted_date, amount,
            status, matched_entry_id)
         VALUES ($1, $2, $3, 1, '2025-06-01', '10.0000', 'matched', $4)`,
        [db.companyA, stmt, account, draft]),
    ).rejects.toThrow(/statement lines are created unmatched; use the matching functions/);
  });

  it("trigger layer: a statement line cannot be born 'excluded' or carrying a suggestion either", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    await expect(
      db.admin.query(
        `INSERT INTO bank_statement_lines
           (company_id, statement_id, money_account_id, line_no, posted_date, amount,
            status, excluded_reason)
         VALUES ($1, $2, $3, 1, '2025-06-01', '10.0000', 'excluded', 'born excluded')`,
        [db.companyA, stmt, account]),
    ).rejects.toThrow(/statement lines are created unmatched/);

    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000");
    await expect(
      db.admin.query(
        `INSERT INTO bank_statement_lines
           (company_id, statement_id, money_account_id, line_no, posted_date, amount,
            suggestion_entry_id)
         VALUES ($1, $2, $3, 2, '2025-06-01', '10.0000', $4)`,
        [db.companyA, stmt, account, entry]),
    ).rejects.toThrow(/statement lines are created unmatched/);
  });

  it("trigger layer: a statement cannot be born already 'reconciled' — the exploit that permanently bricked it", async () => {
    const account = await createMoneyAccount(db.companyA);
    await expect(
      db.admin.query(
        `INSERT INTO bank_statements
           (company_id, money_account_id, filename, file_sha256, format, status, created_by)
         VALUES ($1, $2, 'x.csv', 'born-reconciled-sha', 'csv', 'reconciled', $3)`,
        [db.companyA, account, db.userA]),
    ).rejects.toThrow(/statements are created open; reconcile_statement\(\) finalizes them/);
  });

  it("a normal open statement / unmatched line insert still works exactly as before", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const stmtRow = await c.query("SELECT status FROM bank_statements WHERE id = $1", [stmt]);
    expect(stmtRow.rows[0].status).toBe("open");

    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    expect(await lineStatus(c, line)).toBe("unmatched");
  });

  it("grant layer: the app role cannot even NAME status/matched_entry_id/suggestion_entry_id/excluded_reason in an INSERT", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    await expect(
      c.query(
        `INSERT INTO bank_statement_lines
           (company_id, statement_id, money_account_id, line_no, posted_date, amount, status)
         VALUES ($1, $2, $3, 1, '2025-06-01', '10.0000', 'unmatched')`,
        [db.companyA, stmt, account]),
    ).rejects.toThrow(/permission denied/);

    await expect(
      c.query(
        `INSERT INTO bank_statements
           (company_id, money_account_id, filename, file_sha256, format, status, created_by)
         VALUES ($1, $2, 'y.csv', 'grant-layer-sha', 'csv', 'open', $3)`,
        [db.companyA, account, db.userA]),
    ).rejects.toThrow(/permission denied/);
  });
});

describe("match_statement_line", () => {
  it("matches when the entry has exactly one line on the account with the sign-adjusted amount", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "150.0000");
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "150.0000");

    await matchLine(c, db.companyA, line, entry, db.userA);
    expect(await lineStatus(c, line)).toBe("matched");
    const row = await c.query("SELECT matched_entry_id FROM bank_statement_lines WHERE id = $1", [line]);
    expect(Number(row.rows[0].matched_entry_id)).toBe(entry);
  });

  it("negative bank amount matches a credit to the money account", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "-75.0000");
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["6000"].A, "-75.0000");

    await matchLine(c, db.companyA, line, entry, db.userA);
    expect(await lineStatus(c, line)).toBe("matched");
  });

  it("rejects an entry with zero or more than one line on the account", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    // zero lines on the account
    const noAccountEntry = await insertDraftEntry(c, db.companyA, db.userA, {
      lines: [
        { accountId: db.accounts["6000"].A, debit: "10.0000" },
        { accountId: db.accounts["4000"].A, credit: "10.0000" },
      ],
    });
    await postEntry(c, db.companyA, noAccountEntry);
    await expect(matchLine(c, db.companyA, line, noAccountEntry, db.userA))
      .rejects.toThrow(/exactly one line/);

    // two lines on the account
    const twoLineEntry = await insertDraftEntry(c, db.companyA, db.userA, {
      lines: [
        { accountId: account, debit: "5.0000" },
        { accountId: account, debit: "5.0000" },
        { accountId: db.accounts["4000"].A, credit: "10.0000" },
      ],
    });
    await postEntry(c, db.companyA, twoLineEntry);
    await expect(matchLine(c, db.companyA, line, twoLineEntry, db.userA))
      .rejects.toThrow(/exactly one line/);
  });

  it("rejects an amount mismatch", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "100.0000");
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "99.0000");
    await expect(matchLine(c, db.companyA, line, entry, db.userA))
      .rejects.toThrow(/does not match the statement line amount/);
  });

  it("rejects an unposted (draft) entry", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    const draft = await insertDraftEntry(c, db.companyA, db.userA, {
      lines: [
        { accountId: account, debit: "10.0000" },
        { accountId: db.accounts["4000"].A, credit: "10.0000" },
      ],
    });
    await expect(matchLine(c, db.companyA, line, draft, db.userA)).rejects.toThrow(/not posted/);
  });

  it("rejects matching a line that is already matched", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    const entry1 = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000");
    const entry2 = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000");
    await matchLine(c, db.companyA, line, entry1, db.userA);
    await expect(matchLine(c, db.companyA, line, entry2, db.userA)).rejects.toThrow(/already matched/);
  });

  it("settle-uniqueness: rejects matching a SECOND line to an entry already settled", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line1 = await addLine(c, db.companyA, stmt, account, "10.0000");
    const line2 = await addLine(c, db.companyA, stmt, account, "10.0000");
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000");
    await matchLine(c, db.companyA, line1, entry, db.userA);
    await expect(matchLine(c, db.companyA, line2, entry, db.userA))
      .rejects.toThrow(/already settled by statement line/);
  });

  it("concurrent match of TWO lines to the SAME entry: exactly one succeeds", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line1 = await addLine(c, db.companyA, stmt, account, "40.0000");
    const line2 = await addLine(c, db.companyA, stmt, account, "40.0000");
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "40.0000");

    const a = await db.appClient(db.companyA);
    const b = await db.appClient(db.companyA);
    const results = await Promise.allSettled([
      matchLine(a, db.companyA, line1, entry, db.userA),
      matchLine(b, db.companyA, line2, entry, db.userA),
    ]);
    const fulfilled = results.filter((r) => r.status === "fulfilled");
    const rejected = results.filter((r) => r.status === "rejected");
    expect(fulfilled.length).toBe(1);
    expect(rejected.length).toBe(1);
    expect((rejected[0] as PromiseRejectedResult).reason.message).toMatch(/already settled/);
    await a.end();
    await b.end();
  });

  it("concurrent match of ONE line to two different entries: exactly one succeeds (line locked FOR UPDATE)", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "25.0000");
    const entryX = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "25.0000");
    const entryY = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "25.0000");

    const a = await db.appClient(db.companyA);
    const b = await db.appClient(db.companyA);
    const results = await Promise.allSettled([
      matchLine(a, db.companyA, line, entryX, db.userA),
      matchLine(b, db.companyA, line, entryY, db.userA),
    ]);
    const fulfilled = results.filter((r) => r.status === "fulfilled");
    expect(fulfilled.length).toBe(1);
    expect(await lineStatus(c, line)).toBe("matched");
    await a.end();
    await b.end();
  });
});

describe("unmatch / exclude / restore / suggest", () => {
  it("unmatch frees the line without touching the underlying entry", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000");
    await matchLine(c, db.companyA, line, entry, db.userA);
    await unmatchLine(c, db.companyA, line, db.userA);
    expect(await lineStatus(c, line)).toBe("unmatched");
    const entryStatus = await c.query("SELECT status FROM journal_entries WHERE id = $1", [entry]);
    expect(entryStatus.rows[0].status).toBe("posted"); // untouched, still posted

    // and the entry can now be matched by a different line
    const line2 = await addLine(c, db.companyA, stmt, account, "10.0000");
    await matchLine(c, db.companyA, line2, entry, db.userA);
    expect(await lineStatus(c, line2)).toBe("matched");
  });

  it("cannot unmatch a line that is not matched", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    await expect(unmatchLine(c, db.companyA, line, db.userA)).rejects.toThrow(/not matched/);
  });

  it("exclude requires a reason, and round-trips via restore", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    await expect(excludeLine(c, db.companyA, line, "", db.userA)).rejects.toThrow(/reason is required/);

    await excludeLine(c, db.companyA, line, "personal, not business", db.userA);
    expect(await lineStatus(c, line)).toBe("excluded");
    const row = await c.query("SELECT excluded_reason FROM bank_statement_lines WHERE id = $1", [line]);
    expect(row.rows[0].excluded_reason).toBe("personal, not business");

    await restoreLine(c, db.companyA, line, db.userA);
    expect(await lineStatus(c, line)).toBe("unmatched");
    const row2 = await c.query("SELECT excluded_reason FROM bank_statement_lines WHERE id = $1", [line]);
    expect(row2.rows[0].excluded_reason).toBeNull();
  });

  it("cannot restore a line that is not excluded", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    await expect(restoreLine(c, db.companyA, line, db.userA)).rejects.toThrow(/not excluded/);
  });

  it("suggest_statement_line sets status='suggested' and is a no-op once the line is matched", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000");

    await suggestLine(c, db.companyA, line, entry);
    expect(await lineStatus(c, line)).toBe("suggested");
    const row = await c.query("SELECT suggestion_entry_id FROM bank_statement_lines WHERE id = $1", [line]);
    expect(Number(row.rows[0].suggestion_entry_id)).toBe(entry);

    await matchLine(c, db.companyA, line, entry, db.userA); // confirm the suggestion
    expect(await lineStatus(c, line)).toBe("matched");

    await suggestLine(c, db.companyA, line, entry); // engine re-runs post-hoc: no-op, no error
    expect(await lineStatus(c, line)).toBe("matched");
  });
});

describe("create_entry_from_line", () => {
  it("positive amount: debits the money account, credits the category", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "88.5000", { postedDate: "2025-06-10" });

    const entryId = await createEntryFromLine(
      c, db.companyA, line, { categoryAccountId: db.accounts["4000"].A, memo: "deposit" }, db.userA);

    const lines = await c.query(
      "SELECT account_id, debit, credit FROM journal_lines WHERE entry_id = $1 ORDER BY debit DESC",
      [entryId]);
    expect(lines.rows).toEqual([
      { account_id: String(account), debit: "88.5000", credit: "0.0000" },
      { account_id: String(db.accounts["4000"].A), debit: "0.0000", credit: "88.5000" },
    ]);
    const entryDate = await c.query("SELECT entry_date FROM journal_entries WHERE id = $1", [entryId]);
    expect(dateStr(entryDate.rows[0].entry_date)).toBe("2025-06-10");
    expect(await lineStatus(c, line)).toBe("created");
    const row = await c.query("SELECT matched_entry_id FROM bank_statement_lines WHERE id = $1", [line]);
    expect(Number(row.rows[0].matched_entry_id)).toBe(entryId);
  });

  it("negative amount: credits the money account, debits the category", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "-42.0000");

    const entryId = await createEntryFromLine(
      c, db.companyA, line, { categoryAccountId: db.accounts["6000"].A }, db.userA);

    const lines = await c.query(
      "SELECT account_id, debit, credit FROM journal_lines WHERE entry_id = $1 ORDER BY debit DESC",
      [entryId]);
    expect(lines.rows).toEqual([
      { account_id: String(db.accounts["6000"].A), debit: "42.0000", credit: "0.0000" },
      { account_id: String(account), debit: "0.0000", credit: "42.0000" },
    ]);
  });

  it("rejects the money account itself as the category", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    await expect(
      createEntryFromLine(c, db.companyA, line, { categoryAccountId: account }, db.userA),
    ).rejects.toThrow(/cannot be the money account itself/);
  });

  it("cannot create-from-line for a line that is already settled", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    await createEntryFromLine(c, db.companyA, line, { categoryAccountId: db.accounts["6000"].A }, db.userA);
    await expect(
      createEntryFromLine(c, db.companyA, line, { categoryAccountId: db.accounts["6000"].A }, db.userA),
    ).rejects.toThrow(/already/);
  });
});

describe("reconcile_statement", () => {
  it("refuses to reconcile while unmatched/suggested lines remain", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    await addLine(c, db.companyA, stmt, account, "10.0000");
    await expect(
      reconcileStatement(c, db.companyA, stmt, { endingBalance: "10.0000", throughDate: "2025-06-05" }, db.userA),
    ).rejects.toThrow(/still need a decision/);
  });

  it("exact-balance gate: off by one cent is refused with an English difference", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "100.0000", { postedDate: "2025-06-05" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "100.0000", "2025-06-05");
    await matchLine(c, db.companyA, line, entry, db.userA);

    await expect(
      reconcileStatement(c, db.companyA, stmt, { endingBalance: "100.01", throughDate: "2025-06-05" }, db.userA),
    ).rejects.toThrow(/doesn't add up.*\$0\.01.*more came in at the bank/);

    await expect(
      reconcileStatement(c, db.companyA, stmt, { endingBalance: "99.99", throughDate: "2025-06-05" }, db.userA),
    ).rejects.toThrow(/doesn't add up.*\$0\.01.*more is in your books/);
  });

  it("reconciles at exact balance: freezes the statement and its lines, writes the reconciliation row", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "250.0000", { postedDate: "2025-06-05" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "250.0000", "2025-06-05");
    await matchLine(c, db.companyA, line, entry, db.userA);

    const reconId = await reconcileStatement(
      c, db.companyA, stmt, { endingBalance: "250.00", throughDate: "2025-06-05" }, db.userA);
    expect(reconId).toBeGreaterThan(0);

    const stmtRow = await c.query("SELECT status FROM bank_statements WHERE id = $1", [stmt]);
    expect(stmtRow.rows[0].status).toBe("reconciled");

    const reconRow = await c.query(
      "SELECT reconciled_through, statement_balance FROM account_reconciliations WHERE id = $1", [reconId]);
    expect(dateStr(reconRow.rows[0].reconciled_through)).toBe("2025-06-05");
    expect(reconRow.rows[0].statement_balance).toBe("250.0000");
    // book_balance is deliberately NOT stored (CLAUDE.md §3.4) — recomputed on demand
    const book = await c.query(
      "SELECT account_book_balance_asof($1, $2, '2025-06-05') AS b", [db.companyA, account]);
    expect(book.rows[0].b).toBe("250.0000");

    // lines are now frozen
    await expect(unmatchLine(c, db.companyA, line, db.userA)).rejects.toThrow(/reconciled/);
  });

  it("excluded lines do not block reconciliation", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000", { postedDate: "2025-06-05" });
    await excludeLine(c, db.companyA, line, "not ours", db.userA);
    const reconId = await reconcileStatement(
      c, db.companyA, stmt, { endingBalance: "0.00", throughDate: "2025-06-05" }, db.userA);
    expect(reconId).toBeGreaterThan(0);
  });

  it("account-level advisory lock: concurrent reconcile attempts on the SAME account serialize", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmtX = await createStatement(c, db.companyA, db.userA, account, { sha256: "race-x" });
    const stmtY = await createStatement(c, db.companyA, db.userA, account, { sha256: "race-y" });
    const lineX = await addLine(c, db.companyA, stmtX, account, "10.0000", { postedDate: "2025-01-05" });
    const entryX = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000", "2025-01-05");
    await matchLine(c, db.companyA, lineX, entryX, db.userA);
    const lineY = await addLine(c, db.companyA, stmtY, account, "20.0000", { postedDate: "2025-02-05" });
    const entryY = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "20.0000", "2025-02-05");
    await matchLine(c, db.companyA, lineY, entryY, db.userA);

    // A holds its reconcile open (uncommitted, advisory lock held) while B's
    // concurrent reconcile on the SAME account is issued; B must block on
    // A's advisory lock rather than racing it — proven deterministically by
    // committing A only after a delay, then checking B could not possibly
    // have completed before A's commit (mirrors ap.test.ts's "concurrent
    // print runs" race pattern).
    const a = await db.appClient(db.companyA);
    const b = await db.appClient(db.companyA);
    await a.query("BEGIN");
    await reconcileStatement(a, db.companyA, stmtX, { endingBalance: "10.00", throughDate: "2025-01-05" }, db.userA);
    const pendingB = inFlight(reconcileStatement(
      b, db.companyA, stmtY, { endingBalance: "30.00", throughDate: "2025-02-05" }, db.userA));
    await new Promise((r) => setTimeout(r, 300));
    // "B could not possibly have completed before A's commit" is what the
    // comment above promises; this is the line that actually checks it. It was
    // previously left to the reconciled-through date at the end to imply.
    pendingB.stillBlocked("B's reconcile_statement");
    await a.query("COMMIT");
    const resY = await pendingB.settled();
    expect(resY).toBeGreaterThan(0);

    // B necessarily committed AFTER A (it was blocked on A's advisory lock
    // until A committed), so B's row is the latest — effective
    // reconciled-through is Y's date, deterministically.
    const thru = await c.query(
      "SELECT effective_reconciled_through($1, $2) AS thru", [db.companyA, account]);
    expect(dateStr(thru.rows[0].thru)).toBe("2025-02-05");
    await a.end();
    await b.end();
  });
});

describe("reconciled-through guards on post_entry (CLAUDE.md §3, §9)", () => {
  it("refuses a new manual entry dated on/before the account's reconciled-through, with the exact English", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "500.0000", { postedDate: "2025-03-10" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "500.0000", "2025-03-10");
    await matchLine(c, db.companyA, line, entry, db.userA);
    await reconcileStatement(c, db.companyA, stmt, { endingBalance: "500.00", throughDate: "2025-03-10" }, db.userA);

    // on the reconciled-through date itself
    const onDate = await insertDraftEntry(c, db.companyA, db.userA, {
      date: "2025-03-10",
      lines: [{ accountId: account, debit: "1.0000" }, { accountId: db.accounts["4000"].A, credit: "1.0000" }],
    });
    await expect(postEntry(c, db.companyA, onDate)).rejects.toThrow(
      /That account is reconciled through 2025-03-10 — date it after 2025-03-10 or undo the reconciliation\./);

    // before it too
    const beforeDate = await insertDraftEntry(c, db.companyA, db.userA, {
      date: "2025-01-01",
      lines: [{ accountId: account, debit: "1.0000" }, { accountId: db.accounts["4000"].A, credit: "1.0000" }],
    });
    await expect(postEntry(c, db.companyA, beforeDate)).rejects.toThrow(/reconciled through/);

    // the day after is fine
    const afterDate = await insertDraftEntry(c, db.companyA, db.userA, {
      date: "2025-03-11",
      lines: [{ accountId: account, debit: "1.0000" }, { accountId: db.accounts["4000"].A, credit: "1.0000" }],
    });
    await expect(postEntry(c, db.companyA, afterDate)).resolves.toBeGreaterThan(0);
  });

  it("does not affect entries touching OTHER accounts, even in the same company", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000", { postedDate: "2025-04-01" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000", "2025-04-01");
    await matchLine(c, db.companyA, line, entry, db.userA);
    await reconcileStatement(c, db.companyA, stmt, { endingBalance: "10.00", throughDate: "2025-04-01" }, db.userA);

    const unrelated = await insertDraftEntry(c, db.companyA, db.userA, {
      date: "2025-04-01",
      lines: [
        { accountId: db.accounts["6000"].A, debit: "1.0000" },
        { accountId: db.accounts["4000"].A, credit: "1.0000" },
      ],
    });
    await expect(postEntry(c, db.companyA, unrelated)).resolves.toBeGreaterThan(0);
  });

  it("pay_bills (AP) posting into a reconciled account period is refused through the SAME post_entry guard", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "100.0000", { postedDate: "2025-05-01" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "100.0000", "2025-05-01");
    await matchLine(c, db.companyA, line, entry, db.userA);
    await reconcileStatement(c, db.companyA, stmt, { endingBalance: "100.00", throughDate: "2025-05-01" }, db.userA);

    const vendRes = await c.query(
      `INSERT INTO vendors (company_id, name) VALUES ($1, $2) RETURNING id`,
      [db.companyA, "Reconciled Period Vendor " + Date.now()]);
    const vendorId = Number(vendRes.rows[0].id);

    await expect(
      c.query(
        "SELECT pay_bills($1, $2::jsonb, $3)",
        [db.companyA, JSON.stringify({
          vendor_id: vendorId, payment_date: "2025-05-01", amount: "10.0000",
          money_account_id: account, method: "electronic", memo: null, applications: [],
        }), db.userA]),
    ).rejects.toThrow(/That account is reconciled through 2025-05-01/);

    // a date after the reconciled-through works fine
    const okResult = await c.query(
      "SELECT pay_bills($1, $2::jsonb, $3) AS id",
      [db.companyA, JSON.stringify({
        vendor_id: vendorId, payment_date: "2025-05-02", amount: "10.0000",
        money_account_id: account, method: "electronic", memo: null, applications: [],
      }), db.userA]);
    expect(Number(okResult.rows[0].id)).toBeGreaterThan(0);
  });
});

describe("reconciled-through guards on reversal (CLAUDE.md §3.2)", () => {
  it("refuses to reverse an entry whose OWN date falls inside a reconciled period, even reversed at a LATER date", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "60.0000", { postedDate: "2025-02-10" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "60.0000", "2025-02-10");
    await matchLine(c, db.companyA, line, entry, db.userA);
    await reconcileStatement(c, db.companyA, stmt, { endingBalance: "60.00", throughDate: "2025-02-28" }, db.userA);

    // the entry itself (2025-02-10) is inside the reconciled window (through
    // 2025-02-28); reversing it, even dated safely AFTER the window, must
    // still be refused — this is the case a bare reversal-date check would miss.
    await expect(
      c.query("SELECT reverse_entry($1, $2, '2025-03-15', $3)", [db.companyA, entry, db.userA]),
    ).rejects.toThrow(/That account is reconciled through 2025-02-28/);
  });

  it("refuses a reversal dated on/before the reconciled-through", async () => {
    const account = await createMoneyAccount(db.companyA);
    // reconcile through an early date, then post (and try to reverse) a LATER entry
    const stmt0 = await createStatement(c, db.companyA, db.userA, account, { sha256: "rev-guard-0" });
    const zeroLine = await addLine(c, db.companyA, stmt0, account, "1.0000", { postedDate: "2025-01-05" });
    const zeroEntry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "1.0000", "2025-01-05");
    await matchLine(c, db.companyA, zeroLine, zeroEntry, db.userA);
    await reconcileStatement(c, db.companyA, stmt0, { endingBalance: "1.00", throughDate: "2025-01-31" }, db.userA);

    const laterEntry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "5.0000", "2025-02-15");
    // reversal dated BACK into the reconciled window is refused
    await expect(
      c.query("SELECT reverse_entry($1, $2, '2025-01-20', $3)", [db.companyA, laterEntry, db.userA]),
    ).rejects.toThrow(/reversal date cannot precede|That account is reconciled through/);
  });

  it("reversing an entry entirely outside any reconciled window succeeds normally", async () => {
    const account = await createMoneyAccount(db.companyA);
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "15.0000", "2025-06-01");
    await expect(
      c.query("SELECT reverse_entry($1, $2, '2025-06-02', $3)", [db.companyA, entry, db.userA]),
    ).resolves.toBeTruthy();
  });
});

describe("undo_reconciliation", () => {
  it("moves the effective reconciled-through back and re-opens the statement, un-freezing its lines", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "40.0000", { postedDate: "2025-07-01" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "40.0000", "2025-07-01");
    await matchLine(c, db.companyA, line, entry, db.userA);
    await reconcileStatement(c, db.companyA, stmt, { endingBalance: "40.00", throughDate: "2025-07-01" }, db.userA);

    // blocked beforehand
    const blocked = await insertDraftEntry(c, db.companyA, db.userA, {
      date: "2025-07-01",
      lines: [{ accountId: account, debit: "1.0000" }, { accountId: db.accounts["4000"].A, credit: "1.0000" }],
    });
    await expect(postEntry(c, db.companyA, blocked)).rejects.toThrow(/reconciled through/);

    await undoReconciliation(c, db.companyA, account, db.userA);

    const stmtRow = await c.query("SELECT status FROM bank_statements WHERE id = $1", [stmt]);
    expect(stmtRow.rows[0].status).toBe("open");

    const thru = await c.query(
      "SELECT effective_reconciled_through($1, $2) AS thru", [db.companyA, account]);
    expect(thru.rows[0].thru).toBeNull(); // no earlier statement, rolls back to "never reconciled"

    // now postable
    await expect(postEntry(c, db.companyA, blocked)).resolves.toBeGreaterThan(0);
    // and the statement's lines are unfrozen again
    await unmatchLine(c, db.companyA, line, db.userA);
    expect(await lineStatus(c, line)).toBe("unmatched");
  });

  it("rolls back to the PREVIOUS statement's reconciled-through when one exists", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt1 = await createStatement(c, db.companyA, db.userA, account, { sha256: "undo-prev-1" });
    const line1 = await addLine(c, db.companyA, stmt1, account, "10.0000", { postedDate: "2025-08-05" });
    const entry1 = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "10.0000", "2025-08-05");
    await matchLine(c, db.companyA, line1, entry1, db.userA);
    await reconcileStatement(c, db.companyA, stmt1, { endingBalance: "10.00", throughDate: "2025-08-05" }, db.userA);

    const stmt2 = await createStatement(c, db.companyA, db.userA, account, { sha256: "undo-prev-2" });
    const line2 = await addLine(c, db.companyA, stmt2, account, "5.0000", { postedDate: "2025-08-20" });
    const entry2 = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "5.0000", "2025-08-20");
    await matchLine(c, db.companyA, line2, entry2, db.userA);
    await reconcileStatement(c, db.companyA, stmt2, { endingBalance: "15.00", throughDate: "2025-08-20" }, db.userA);

    await undoReconciliation(c, db.companyA, account, db.userA);

    const thru = await c.query(
      "SELECT effective_reconciled_through($1, $2) AS thru", [db.companyA, account]);
    expect(dateStr(thru.rows[0].thru)).toBe("2025-08-05"); // back to stmt1's date

    const stmt2Row = await c.query("SELECT status FROM bank_statements WHERE id = $1", [stmt2]);
    expect(stmt2Row.rows[0].status).toBe("open");
    const stmt1Row = await c.query("SELECT status FROM bank_statements WHERE id = $1", [stmt1]);
    expect(stmt1Row.rows[0].status).toBe("reconciled"); // untouched
  });

  it("cannot undo an account that has never been reconciled, or twice in a row", async () => {
    const account = await createMoneyAccount(db.companyA);
    await expect(undoReconciliation(c, db.companyA, account, db.userA))
      .rejects.toThrow(/never been reconciled/);

    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "1.0000", { postedDate: "2025-09-01" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "1.0000", "2025-09-01");
    await matchLine(c, db.companyA, line, entry, db.userA);
    await reconcileStatement(c, db.companyA, stmt, { endingBalance: "1.00", throughDate: "2025-09-01" }, db.userA);
    await undoReconciliation(c, db.companyA, account, db.userA);
    await expect(undoReconciliation(c, db.companyA, account, db.userA))
      .rejects.toThrow(/not currently reconciled/);
  });

  it("append-only: account_reconciliations rejects UPDATE/DELETE, even for the superuser", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "1.0000", { postedDate: "2025-10-01" });
    const entry = await postMoneyEntry(c, db.companyA, db.userA, account, db.accounts["4000"].A, "1.0000", "2025-10-01");
    await matchLine(c, db.companyA, line, entry, db.userA);
    await reconcileStatement(c, db.companyA, stmt, { endingBalance: "1.00", throughDate: "2025-10-01" }, db.userA);

    await expect(
      c.query("UPDATE account_reconciliations SET reconciled_through = '2099-01-01'"),
    ).rejects.toThrow(/append-only|denied/);
    await expect(c.query("DELETE FROM account_reconciliations")).rejects.toThrow(/append-only|denied/);
    await expect(
      db.admin.query("UPDATE account_reconciliations SET reconciled_through = '2099-01-01'"),
    ).rejects.toThrow(/append-only/);
    await expect(db.admin.query("DELETE FROM account_reconciliations")).rejects.toThrow(/append-only/);
  });

  it("the app role has no INSERT grant on account_reconciliations — function-only", async () => {
    const account = await createMoneyAccount(db.companyA);
    await expect(
      c.query(
        `INSERT INTO account_reconciliations
           (company_id, money_account_id, reconciled_through, created_by)
         VALUES ($1, $2, '2025-01-01', $3)`,
        [db.companyA, account, db.userA]),
    ).rejects.toThrow(/permission denied/);
  });
});

describe("cross-company impossibility (CLAUDE.md §4)", () => {
  it("a statement cannot reference another company's account (composite FK)", async () => {
    await expect(
      c.query(
        `INSERT INTO bank_statements (company_id, money_account_id, filename, file_sha256, format, created_by)
         VALUES ($1, $2, 'x.csv', 'xc-sha', 'csv', $3)`,
        [db.companyA, db.accounts["1000"].B, db.userA]),
    ).rejects.toThrow(/not found|foreign key|violates/);
  });

  it("a statement line cannot reference another company's statement (composite FK)", async () => {
    const accountB = db.accounts["1000"].B;
    const cB = await db.appClient(db.companyB);
    const stmtB = await createStatement(cB, db.companyB, db.userB, accountB, { sha256: "xc-line" });
    await expect(
      c.query(
        `INSERT INTO bank_statement_lines
           (company_id, statement_id, money_account_id, line_no, posted_date, amount)
         VALUES ($1, $2, $3, 1, '2025-01-01', '10.0000')`,
        [db.companyA, stmtB, db.accounts["1000"].A]),
    ).rejects.toThrow(/not found|foreign key|violates/);
  });

  it("match_statement_line refuses an entry belonging to another company", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");
    const cB = await db.appClient(db.companyB);
    const entryB = await insertDraftEntry(cB, db.companyB, db.userB, {
      lines: [
        { accountId: db.accounts["1000"].B, debit: "10.0000" },
        { accountId: db.accounts["4000"].B, credit: "10.0000" },
      ],
    });
    await postEntry(cB, db.companyB, entryB);
    await expect(matchLine(c, db.companyA, line, entryB, db.userA)).rejects.toThrow(/not found/);
  });
});

describe("RLS isolation for bank-rec tables (CLAUDE.md §3.5, §4)", () => {
  it("with no app.company_id set, every bank-rec table returns zero rows", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    await addLine(c, db.companyA, stmt, account, "10.0000");
    await c.query(
      `INSERT INTO bank_csv_maps (company_id, money_account_id, mapping, updated_by)
       VALUES ($1, $2, '{}'::jsonb, $3)`, [db.companyA, account, db.userA]);

    const anon = await db.appClient(null);
    for (const table of [
      "bank_statements", "bank_statement_lines", "bank_csv_maps", "account_reconciliations",
    ]) {
      const res = await anon.query(`SELECT count(*)::int AS n FROM ${table}`);
      expect(res.rows[0].n).toBe(0);
    }
  });

  it("company B cannot see or act on company A's statements", async () => {
    const account = await createMoneyAccount(db.companyA);
    const stmt = await createStatement(c, db.companyA, db.userA, account);
    const line = await addLine(c, db.companyA, stmt, account, "10.0000");

    const cB = await db.appClient(db.companyB);
    const res = await cB.query("SELECT * FROM bank_statement_lines WHERE id = $1", [line]);
    expect(res.rows.length).toBe(0);

    // even naming company A explicitly, the session-company guard inside the
    // function refuses (RLS would also just find nothing to lock)
    await expect(
      cB.query("SELECT match_statement_line($1, $2, $3, $4)", [db.companyA, line, 1, db.userB]),
    ).rejects.toThrow(/session company|not found/);
  });
});
