// Integration tests for the universal import pipeline + API (Task 4 of
// docs/superpowers/plans/2026-08-02-switch-from-anywhere.md): upload ->
// map-preview -> chart commit / journal stage -> the EXISTING resolve/
// validate/post/rollback machinery, per-year batches, idempotency, saved
// mappings, and RLS. Boots the app builder against a throwaway DB
// (tests/helpers/db.ts) exactly like tests/server/setup.test.ts.
//
// Every fixture is 100% FICTIONAL: "Cascadia Gear Works" is an invented
// manufacturer with an invented Sage-BusinessWorks-shaped export (chart of
// 40 accounts incl. subaccount paths; two years of ~120 balanced entries
// incl. dirty amounts), plus a generic signed-amount CSV. Trial-balance
// expectations are HAND-COMPUTED in integer cents alongside the fixture
// rows — the ledger must match to the penny.

import { describe, it, expect, beforeAll, afterAll } from "vitest";
import ExcelJS from "exceljs";
import { randomUUID } from "node:crypto";
import { createTestDb, type TestDb } from "../helpers/db.js";
// Where the database is, resolved once for the whole run: your ADMIN_URL /
// DATABASE_URL if you set one, otherwise the PostgreSQL Balanced Sum installed on
// this machine. Every file in this directory used to carry its own copy of that
// decision, hardcoded to the Docker container that no longer exists.
import { dbUrl } from "../helpers/test-db-admin.js";
import { buildApp } from "../../server/src/app.js";
import type { FastifyInstance } from "fastify";

// ---------------------------------------------------------------------------
// money helpers — integer cents (bigint) only, never floats (CLAUDE.md §2)
// ---------------------------------------------------------------------------

function centsToPlain(c: bigint): string {
  const neg = c < 0n;
  const a = neg ? -c : c;
  return `${neg ? "-" : ""}${a / 100n}.${(a % 100n).toString().padStart(2, "0")}`;
}

/** "1234.56" -> "1,234.56" (for dirty-amount rendering in fixtures). */
function withCommas(c: bigint): string {
  const [w, f] = centsToPlain(c).split(".");
  return `${w!.replace(/\B(?=(\d{3})+(?!\d))/g, ",")}.${f}`;
}

/** cents -> the exact text Postgres returns for NUMERIC(19,4). */
function tb4(c: bigint): string {
  return `${centsToPlain(c)}00`;
}

/** NUMERIC(19,4) text -> cents (string surgery, truncating the trailing 00). */
function tbToCents(text: string): bigint {
  const m = /^(-?)(\d+)\.(\d{4})$/.exec(text);
  if (!m) throw new Error(`unexpected numeric text "${text}"`);
  const cents = BigInt(m[2]!) * 100n + BigInt(m[3]!.slice(0, 2));
  return m[1] === "-" ? -cents : cents;
}

function addExpected(map: Map<string, bigint>, account: string, signedCents: bigint): void {
  map.set(account, (map.get(account) ?? 0n) + signedCents);
}

// ---------------------------------------------------------------------------
// CSV + multipart builders
// ---------------------------------------------------------------------------

function csvEscape(cell: string): string {
  return /[",\r\n]/.test(cell) ? `"${cell.replaceAll('"', '""')}"` : cell;
}

function toCsv(rows: string[][]): string {
  return rows.map((r) => r.map(csvEscape).join(",")).join("\r\n") + "\r\n";
}

function multipartPayload(
  fileBuffer: Buffer, filename: string, kind?: string, presetKey?: string,
): { payload: Buffer; contentType: string } {
  const boundary = `----universalTestBoundary${Math.random().toString(16).slice(2)}`;
  const parts: Buffer[] = [];
  if (kind !== undefined) {
    parts.push(Buffer.from(
      `--${boundary}\r\nContent-Disposition: form-data; name="kind"\r\n\r\n${kind}\r\n`,
    ));
  }
  if (presetKey !== undefined) {
    parts.push(Buffer.from(
      `--${boundary}\r\nContent-Disposition: form-data; name="presetKey"\r\n\r\n${presetKey}\r\n`,
    ));
  }
  parts.push(Buffer.from(
    `--${boundary}\r\n` +
      `Content-Disposition: form-data; name="file"; filename="${filename}"\r\n` +
      `Content-Type: application/octet-stream\r\n\r\n`,
  ));
  parts.push(fileBuffer, Buffer.from(`\r\n--${boundary}--\r\n`));
  return {
    payload: Buffer.concat(parts),
    contentType: `multipart/form-data; boundary=${boundary}`,
  };
}

// ---------------------------------------------------------------------------
// FICTIONAL fixture: Cascadia Gear Works (invented manufacturer)
// ---------------------------------------------------------------------------

// 40 accounts, Sage-BusinessWorks-ish export vocabulary. Subaccount paths
// use ":" so the engine's leaf aliasing kicks in ("CNC Mill" etc.).
const CHART_ROWS: Array<[code: string, name: string, type: string]> = [
  ["1000", "Operating Checking", "Cash"],
  ["1010", "Payroll Checking", "Cash"],
  ["1020", "Petty Cash", "Cash"],
  ["1100", "Trade Receivables", "Accounts Receivable"],
  ["1200", "Raw Materials Inventory", "Other Current Asset"],
  ["1210", "Finished Goods Inventory", "Other Current Asset"],
  ["1220", "Prepaid Insurance", "Other Current Asset"],
  ["1300", "Employee Advances", "Other Current Asset"],
  ["1500", "Equipment:CNC Mill", "Fixed Asset"],
  ["1510", "Equipment:Powder Coating Line", "Fixed Asset"],
  ["1520", "Equipment:Forklift", "Fixed Asset"],
  ["1530", "Factory Building", "Fixed Asset"],
  ["1540", "Accumulated Depreciation", "Fixed Asset"],
  ["1600", "Patents and Tooling Designs", "Other Asset"],
  ["2000", "Vendor Payables", "Accounts Payable"],
  ["2100", "Shop Credit Card", "Credit Card"],
  ["2200", "Accrued Wages", "Other Current Liability"],
  ["2210", "Payroll Taxes Payable", "Other Current Liability"],
  ["2220", "Sales Tax Collected", "Other Current Liability"],
  ["2300", "Customer Deposits", "Other Current Liability"],
  ["2500", "Equipment Loan", "Long Term Liability"],
  ["2510", "Building Mortgage", "Long Term Liability"],
  ["3000", "Owner Capital", "Equity"],
  ["3100", "Retained Earnings", "Equity"],
  ["3200", "Owner Draws", "Equity"],
  ["4000", "Product Revenue:Brackets", "Income"],
  ["4010", "Product Revenue:Enclosures", "Income"],
  ["4020", "Custom Fabrication Revenue", "Income"],
  ["4100", "Freight Recovery", "Income"],
  ["5000", "Materials Cost", "Cost of Sales"],
  ["5010", "Production Labor", "Cost of Sales"],
  ["5020", "Factory Supplies", "Cost of Sales"],
  ["6000", "Wages Expense", "Expense"],
  ["6010", "Office Rent", "Expense"],
  ["6020", "Utilities", "Expense"],
  ["6030", "Insurance Expense", "Expense"],
  ["6040", "Depreciation Expense", "Expense"],
  ["6050", "Travel and Trade Shows", "Expense"],
  ["6060", "Software Subscriptions", "Expense"],
  ["6070", "Bank Fees", "Expense"],
];

const CHART_TYPE_VALUE_MAP: Record<string, string> = {
  "Cash": "asset",
  "Accounts Receivable": "asset",
  "Other Current Asset": "asset",
  "Fixed Asset": "asset",
  "Other Asset": "asset",
  "Accounts Payable": "liability",
  "Credit Card": "liability",
  "Other Current Liability": "liability",
  "Long Term Liability": "liability",
  "Equity": "equity",
  "Income": "income",
  "Cost of Sales": "expense",
  "Expense": "expense",
};

const CHART_MAPPING = { headerRows: 1, nameCol: 1, typeCol: 2, codeCol: 0 };

/** The chart as a Sage-shaped xlsx (exercises the server-side xlsx->rows
 *  conversion; codes are written as NUMBERS the way a real export would). */
async function buildChartXlsx(): Promise<Buffer> {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("General Ledger Accounts");
  ws.addRow(["Account Number", "Account Description", "Account Type"]);
  for (const [code, name, type] of CHART_ROWS) {
    ws.addRow([Number(code), name, type]);
  }
  return Buffer.from(await wb.xlsx.writeBuffer());
}

const JOURNAL_HEADERS = [
  "Trx Number", "Trx Date", "Account", "Description", "Debit Amount", "Credit Amount",
];

const JOURNAL_MAPPING = {
  headerRows: 1,
  dateCol: 1,
  dateFormat: "MM/DD/YYYY",
  accountCol: 2,
  amountShape: { kind: "debit-credit", debitCol: 4, creditCol: 5 },
  grouping: { kind: "key-column", groupKeyCol: 0 },
  memoCol: 3,
};

interface JournalFixture {
  csv: string;
  /** Signed cents per FULL account name (debit-positive), all years. */
  expected: Map<string, bigint>;
  /** Same, per year. */
  expectedByYear: Map<number, Map<string, bigint>>;
  entryCount: number;
  lineCount: number;
  entriesPerYear: number;
}

/**
 * Two years x 60 balanced entries (5 rotating shapes, one of them 3 lines),
 * with dirty amounts ("$1,234.56", parenthesized negatives in the Debit
 * column), accounts referenced by name, by CODE, and by leaf ALIAS.
 * `centsOffset` shifts every amount — used to build a "same transaction
 * numbers, different file" variant for the external_ref idempotency test.
 */
function buildManufacturerJournal(years: number[], centsOffset = 0n): JournalFixture {
  const rows: string[][] = [JOURNAL_HEADERS.slice()];
  const expected = new Map<string, bigint>();
  const expectedByYear = new Map<number, Map<string, bigint>>();
  let entryCount = 0;
  let lineCount = 0;

  for (const y of years) {
    const yearMap = new Map<string, bigint>();
    expectedByYear.set(y, yearMap);
    for (let i = 0; i < 60; i++) {
      const trx = `${y}-${i + 1}`;
      const month = String((i % 12) + 1).padStart(2, "0");
      const day = String((i % 27) + 1).padStart(2, "0");
      const date = `${month}/${day}/${y}`;
      const a = BigInt(i + 3) * 13700n + BigInt((i * 37) % 100) + centsOffset
        + (y === 2020 ? 5000n : 0n);

      function line(account: string, memo: string, debit: string, credit: string): void {
        rows.push([trx, date, account, memo, debit, credit]);
        lineCount++;
      }
      function book(account: string, signedCents: bigint): void {
        addExpected(expected, account, signedCents);
        addExpected(yearMap, account, signedCents);
      }

      switch (i % 5) {
        case 0: // sale — revenue referenced by leaf ALIAS ("Brackets")
          line("Trade Receivables", "Bracket order", centsToPlain(a), "");
          line("Brackets", "Bracket order", "", centsToPlain(a));
          book("Trade Receivables", a);
          book("Product Revenue:Brackets", -a);
          break;
        case 1: { // customer payment — checking referenced by CODE half the time
          const checkingRef = i % 2 === 0 ? "1000" : "Operating Checking";
          line(checkingRef, "Customer payment", centsToPlain(a), "");
          line("Trade Receivables", "Customer payment", "", centsToPlain(a));
          book("Operating Checking", a);
          book("Trade Receivables", -a);
          break;
        }
        case 2: // materials purchase — DIRTY debit ("$1,234.56")
          line("Raw Materials Inventory", "Steel stock", `$${withCommas(a)}`, "");
          line("Vendor Payables", "Steel stock", "", centsToPlain(a));
          book("Raw Materials Inventory", a);
          book("Vendor Payables", -a);
          break;
        case 3: { // payroll — 3 lines
          const gross = a + 40000n;
          const net = (gross * 3n) / 4n;
          const taxes = gross - net;
          line("Wages Expense", "Payroll run", centsToPlain(gross), "");
          line("Payroll Checking", "Payroll run", "", centsToPlain(net));
          line("Payroll Taxes Payable", "Payroll run", "", centsToPlain(taxes));
          book("Wages Expense", gross);
          book("Payroll Checking", -net);
          book("Payroll Taxes Payable", -taxes);
          break;
        }
        default: // equipment purchase — alias debit + PARENS credit in the Debit column
          line("CNC Mill", "Tooling upgrade", withCommas(a), "");
          line("Vendor Payables", "Tooling upgrade", `(${withCommas(a)})`, "");
          book("Equipment:CNC Mill", a);
          book("Vendor Payables", -a);
      }
      entryCount++;
    }
  }
  return {
    csv: toCsv(rows), expected, expectedByYear,
    entryCount, lineCount, entriesPerYear: 60,
  };
}

// ---------------------------------------------------------------------------
// suite setup
// ---------------------------------------------------------------------------

let db: TestDb;
let app: FastifyInstance;

beforeAll(async () => {
  db = await createTestDb();
  app = await buildApp({
    databaseUrl: await dbUrl(db.dbName, "app", "app"),
    adminDatabaseUrl: await dbUrl(db.dbName),
  });
}, 60_000);

afterAll(async () => {
  await app.close();
  await db.close();
});

async function createCompany(name: string): Promise<string> {
  const res = await app.inject({ method: "POST", url: "/api/companies", payload: { name } });
  expect(res.statusCode).toBe(201);
  return (res.json() as { id: string }).id;
}

function inject(
  method: "GET" | "POST", url: string, companyId: string, payload?: Record<string, unknown>,
) {
  return app.inject({ method, url, headers: { "x-company-id": companyId }, payload });
}

function injectUpload(
  companyId: string, fileBuffer: Buffer, filename: string, kind?: string, presetKey?: string,
) {
  const { payload, contentType } = multipartPayload(fileBuffer, filename, kind, presetKey);
  return app.inject({
    method: "POST", url: "/api/c/universal/upload",
    headers: { "x-company-id": companyId, "content-type": contentType },
    payload,
  });
}

/** Uploads and returns the uploadId, asserting success. */
async function upload(
  companyId: string, fileBuffer: Buffer, filename: string, kind: "chart" | "journal",
): Promise<{ uploadId: string; body: Record<string, unknown> }> {
  const res = await injectUpload(companyId, fileBuffer, filename, kind);
  expect(res.statusCode).toBe(200);
  const body = res.json() as Record<string, unknown>;
  return { uploadId: body.uploadId as string, body };
}

// Shared state across the sequential manufacturer flow.
let mfgId: string;
let chartUploadId: string;
let journalUploadId: string;
const journalFx = buildManufacturerJournal([2019, 2020]);
let batchIdByYear: Record<number, number> = {};

// ---------------------------------------------------------------------------
// upload
// ---------------------------------------------------------------------------

describe("POST /api/c/universal/upload", () => {
  it("chart xlsx: headers, 8 sample rows, Sage BusinessWorks preset guess, distinct types + fuzzy prefill", async () => {
    mfgId = await createCompany("Cascadia Gear Works");
    const { uploadId, body } = await upload(mfgId, await buildChartXlsx(), "gl-accounts.xlsx", "chart");
    chartUploadId = uploadId;

    expect(body.headers).toEqual(["Account Number", "Account Description", "Account Type"]);
    expect(body.dataStartIndex).toBe(1);
    expect((body.sampleRows as string[][]).length).toBe(8);
    // xlsx number cells come back as digit strings.
    expect((body.sampleRows as string[][])[0]).toEqual(["1000", "Operating Checking", "Cash"]);

    const preset = body.presetGuess as Record<string, unknown>;
    expect(preset.key).toBe("sage-businessworks");
    expect(preset.likelyKind).toBe("chart");

    expect(body.distinctTypes).toEqual(Object.keys(CHART_TYPE_VALUE_MAP));
    const suggestions = body.typeMapSuggestions as Record<string, string | null>;
    expect(suggestions["Cash"]).toBe("asset");
    expect(suggestions["Cost of Sales"]).toBe("expense");
    expect(suggestions["Credit Card"]).toBe("liability");
  });

  it("journal csv: preset guess, confident MM/DD/YYYY date sniff on the guessed date column", async () => {
    const { uploadId, body } = await upload(
      mfgId, Buffer.from(journalFx.csv), "gl-history.csv", "journal");
    journalUploadId = uploadId;

    const preset = body.presetGuess as Record<string, unknown>;
    expect(preset.key).toBe("sage-businessworks");
    expect(preset.likelyKind).toBe("journal");

    const dateSniff = body.dateSniff as Record<string, unknown>;
    expect(dateSniff.column).toBe(1);
    expect(dateSniff.format).toBe("MM/DD/YYYY");
    expect(dateSniff.confident).toBe(true);
  });

  it("refuses a missing/bad kind, a bad extension, and a fake xlsx, in English", async () => {
    const csv = Buffer.from("a,b\r\n1,2\r\n");
    const noKind = await injectUpload(mfgId, csv, "x.csv");
    expect(noKind.statusCode).toBe(400);
    expect(noKind.json().error).toMatch(/chart.*journal/i);

    const badExt = await injectUpload(mfgId, csv, "x.pdf", "chart");
    expect(badExt.statusCode).toBe(400);
    expect(badExt.json().error).toMatch(/\.csv.*\.xlsx|CSV.*Excel/i);

    const fakeXlsx = await injectUpload(mfgId, Buffer.from("not a zip at all"), "x.xlsx", "chart");
    expect(fakeXlsx.statusCode).toBe(400);
    expect(fakeXlsx.json().error).toMatch(/valid excel/i);
  });

  // Windows accounting exports are frequently UTF-16 ("Unicode Text" out of
  // Excel, several Sage/Peachtree report exports). Decoded blindly as utf8
  // they upload 200 and every header is mojibake, so no column ever matches
  // and the user has no way to see why. Review F10.
  it("reads a UTF-16 LE CSV (BOM) exactly like the utf8 one — headers, presets and sniff intact", async () => {
    const utf16 = Buffer.concat([
      Buffer.from([0xff, 0xfe]),
      Buffer.from(journalFx.csv, "utf16le"),
    ]);
    const { body } = await upload(mfgId, utf16, "gl-history-unicode.csv", "journal");

    expect(body.headers).toEqual(JOURNAL_HEADERS);
    expect((body.presetGuess as Record<string, unknown>).key).toBe("sage-businessworks");
    const dateSniff = body.dateSniff as Record<string, unknown>;
    expect(dateSniff.format).toBe("MM/DD/YYYY");
    expect(dateSniff.confident).toBe(true);
  });

  it("reads a UTF-16 BE CSV (BOM) and strips a UTF-8 BOM instead of gluing it to the first header", async () => {
    const be = Buffer.from(journalFx.csv, "utf16le");
    be.swap16();
    const utf16be = Buffer.concat([Buffer.from([0xfe, 0xff]), be]);
    const beBody = (await upload(mfgId, utf16be, "gl-history-be.csv", "journal")).body;
    expect(beBody.headers).toEqual(JOURNAL_HEADERS);

    const utf8Bom = Buffer.concat([
      Buffer.from([0xef, 0xbb, 0xbf]),
      Buffer.from(journalFx.csv, "utf8"),
    ]);
    const bomBody = (await upload(mfgId, utf8Bom, "gl-history-bom.csv", "journal")).body;
    expect(bomBody.headers).toEqual(JOURNAL_HEADERS);
    expect((bomBody.presetGuess as Record<string, unknown>).key).toBe("sage-businessworks");
  });

  it("refuses a BOM-less UTF-16 CSV in English rather than importing mojibake", async () => {
    const noBom = Buffer.from(journalFx.csv, "utf16le"); // NULs, no BOM to key off
    const res = await injectUpload(mfgId, noBom, "gl-history-nobom.csv", "journal");
    expect(res.statusCode).toBe(422);
    expect(res.json().error).toMatch(/text encoding isn't one we can read.*CSV UTF-8/i);
  });
});

// ---------------------------------------------------------------------------
// presetKey: the user's pick decides the guesses (FIX 1)
// ---------------------------------------------------------------------------
//
// A Xero-shaped journal export. Its headers score 12 against the Xero hints
// and 4 against Sage BusinessWorks', so it is exactly the file that used to
// get mapped by Xero's guesses while the wizard told a Sage BusinessWorks
// user "this file looks like a Xero export" as a statement of fact.

const XERO_SHAPED_JOURNAL = Buffer.from(toCsv([
  ["JournalNumber", "JournalDate", "AccountName", "Description", "NetAmount", "Reference"],
  ["1", "01/15/2021", "Operating Checking", "Deposit", "1200.00", "DEP-1"],
  ["1", "01/15/2021", "Product Sales", "Deposit", "-1200.00", "DEP-1"],
]));

describe("POST /api/c/universal/upload — presetKey", () => {
  let pickerId: string;

  beforeAll(async () => {
    pickerId = await createCompany("Preset Picker Co");
  });

  it("uses the PICKED product's hints, and offers the header signal instead of applying it", async () => {
    const res = await injectUpload(
      pickerId, XERO_SHAPED_JOURNAL, "ledger.csv", "journal", "sage-businessworks");
    expect(res.statusCode).toBe(200);
    const body = res.json() as Record<string, unknown>;

    const guess = body.presetGuess as { key: string; source: string; guesses: unknown[] };
    expect(guess.key).toBe("sage-businessworks");
    // The pick is the pick — never restated as a detection.
    expect(guess.source).toBe("picked");

    // Sage BusinessWorks' hints only reach this file's Reference and
    // Description columns, so that is all that gets pre-filled — no Xero
    // column claims sneaking in under the picked product's name.
    const fields = (guess.guesses as { field: string; col: number }[])
      .map((g) => `${g.field}:${g.col}`).sort();
    expect(fields).toEqual(["groupKey:5", "memo:3"]);

    // Both signals come back. The stronger one is a QUESTION, not an override.
    const alt = body.alternatePreset as { key: string; label: string; score: number; guesses: unknown[] };
    expect(alt.key).toBe("xero");
    expect(alt.label).toBe("Xero");
    expect(alt.score).toBeGreaterThan((guess as unknown as { score: number }).score);
    // …and it carries its own guesses, so accepting it is a client-side swap
    // rather than another upload.
    expect((alt.guesses as { field: string; col: number }[])
      .find((g) => g.field === "date")?.col).toBe(1);
  });

  it("says nothing about a second product when the pick and the headers agree", async () => {
    const res = await injectUpload(pickerId, XERO_SHAPED_JOURNAL, "ledger.csv", "journal", "xero");
    expect(res.statusCode).toBe(200);
    const body = res.json() as Record<string, unknown>;
    const guess = body.presetGuess as { key: string; source: string };
    expect(guess.key).toBe("xero");
    expect(guess.source).toBe("picked");
    expect(body.alternatePreset).toBeNull();
  });

  it("stays quiet when the picked product simply isn't beaten by enough", async () => {
    // The Sage-shaped fixture the rest of this file uses: Sage BusinessWorks
    // outscores every other preset, so there is no second opinion to raise.
    const res = await injectUpload(
      pickerId, Buffer.from(journalFx.csv), "sage.csv", "journal", "sage-businessworks");
    expect(res.statusCode).toBe(200);
    const body = res.json() as Record<string, unknown>;
    expect((body.presetGuess as { key: string }).key).toBe("sage-businessworks");
    expect(body.alternatePreset).toBeNull();
  });

  // Every header here is a near-miss: "Posting Date Col" CONTAINS "date" but
  // is named "date" by nobody. So the generic debit/credit preset outscores a
  // picked Xero (5 fuzzy hits to 4) while proving nothing about which
  // software wrote the file — the case a plain score threshold would have
  // interrupted the user over.
  const FUZZY_ONLY_JOURNAL = Buffer.from(toCsv([
    ["Posting Date Col", "GL Account Col", "Debit Col", "Credit Col", "Memo Col"],
    ["01/15/2021", "Operating Checking", "1200.00", "", "Deposit"],
    ["01/15/2021", "Product Sales", "", "1200.00", "Deposit"],
  ]));

  it("won't raise a second opinion on fuzzy hits alone — it takes exact column names", async () => {
    const res = await injectUpload(pickerId, FUZZY_ONLY_JOURNAL, "ledger.csv", "journal", "xero");
    expect(res.statusCode).toBe(200);
    const body = res.json() as Record<string, unknown>;
    const guess = body.presetGuess as { key: string; source: string; score: number };
    expect(guess).toMatchObject({ key: "xero", source: "picked" });

    // The bar is two columns named EXACTLY the way the other product names
    // them (spec as-built amendment). Nothing here is an exact match, so the
    // pick stands unquestioned even though another preset scores higher.
    expect(body.alternatePreset).toBeNull();

    // …and it really does score higher, so this test can't pass by accident.
    const unpicked = await injectUpload(pickerId, FUZZY_ONLY_JOURNAL, "ledger.csv", "journal");
    const detected = (unpicked.json() as Record<string, unknown>).presetGuess as
      { key: string; score: number };
    expect(detected.key).toBe("generic-debit-credit");
    expect(detected.score).toBeGreaterThan(guess.score);
  });

  it("falls back to header scoring — and only then calls it detected — with no presetKey", async () => {
    const res = await injectUpload(pickerId, XERO_SHAPED_JOURNAL, "ledger.csv", "journal");
    expect(res.statusCode).toBe(200);
    const body = res.json() as Record<string, unknown>;
    const guess = body.presetGuess as { key: string; source: string };
    expect(guess.key).toBe("xero");
    expect(guess.source).toBe("detected");
    // Nothing was picked, so there is no pick to contradict.
    expect(body.alternatePreset).toBeNull();
  });

  it("sniffs the date format on the PICKED preset's date column, not the detected one", async () => {
    // Sage BusinessWorks' hints don't reach this file's date column at all,
    // so there is nothing to sniff — the wizard asks instead of inheriting
    // Xero's answer for a column the user isn't necessarily using.
    const picked = await injectUpload(
      pickerId, XERO_SHAPED_JOURNAL, "ledger.csv", "journal", "sage-businessworks");
    expect((picked.json() as Record<string, unknown>).dateSniff).toBeNull();

    const xero = await injectUpload(
      pickerId, XERO_SHAPED_JOURNAL, "ledger.csv", "journal", "xero");
    const sniff = (xero.json() as Record<string, unknown>).dateSniff as Record<string, unknown>;
    expect(sniff.column).toBe(1);
    expect(sniff.format).toBe("MM/DD/YYYY");
  });

  it("refuses a presetKey it doesn't recognise instead of quietly ignoring it", async () => {
    const res = await injectUpload(
      pickerId, XERO_SHAPED_JOURNAL, "ledger.csv", "journal", "peachtree-2001");
    expect(res.statusCode).toBe(400);
    expect(res.json().error).toMatch(/don't know that software/i);
  });

  it("treats an empty presetKey as no pick at all", async () => {
    const res = await injectUpload(pickerId, XERO_SHAPED_JOURNAL, "ledger.csv", "journal", "");
    expect(res.statusCode).toBe(200);
    expect((res.json() as Record<string, unknown>).presetGuess).toMatchObject({
      key: "xero", source: "detected",
    });
  });

  it("applies the picked product's CHART hints on a chart upload", async () => {
    const chartCsv = Buffer.from(toCsv([
      ["Code", "Name", "Type"],
      ["1000", "Operating Checking", "Bank"],
    ]));
    const res = await injectUpload(pickerId, chartCsv, "coa.csv", "chart", "xero");
    expect(res.statusCode).toBe(200);
    const body = res.json() as Record<string, unknown>;
    const guess = body.presetGuess as { key: string; source: string; guesses: { field: string; col: number }[] };
    expect(guess.key).toBe("xero");
    expect(guess.source).toBe("picked");
    expect(guess.guesses.map((g) => `${g.field}:${g.col}`).sort())
      .toEqual(["code:0", "name:1", "type:2"]);
    // The chart's distinct types + prefill still come off the guessed type
    // column — which is now the PICKED preset's, as it should be.
    expect(body.distinctTypes).toEqual(["Bank"]);
    expect((body.typeMapSuggestions as Record<string, string>).Bank).toBe("asset");
  });
});

// ---------------------------------------------------------------------------
// chart preview + commit
// ---------------------------------------------------------------------------

describe("universal chart preview/commit", () => {
  it("preview with an unmapped type reports row-numbered English errors, maps the rest", async () => {
    const partialMap = { ...CHART_TYPE_VALUE_MAP } as Record<string, string | null>;
    partialMap["Credit Card"] = null;
    const res = await inject("POST", "/api/c/universal/chart/preview", mfgId, {
      uploadId: chartUploadId, mapping: CHART_MAPPING, typeValueMap: partialMap,
    });
    expect(res.statusCode).toBe(200);
    const body = res.json() as { accounts: unknown[]; errors: Array<{ row: number; message: string }> };
    expect(body.errors).toHaveLength(1);
    expect(body.errors[0]!.message).toMatch(/isn't mapped to one of the five kinds/);
    expect(body.errors[0]!.row).toBeGreaterThan(1);
    expect(body.accounts).toHaveLength(39);
  });

  it("preview with the full type map: 40 accounts, subtypes and leaf aliases derived", async () => {
    const res = await inject("POST", "/api/c/universal/chart/preview", mfgId, {
      uploadId: chartUploadId, mapping: CHART_MAPPING, typeValueMap: CHART_TYPE_VALUE_MAP,
    });
    expect(res.statusCode).toBe(200);
    const body = res.json() as {
      accounts: Array<{ name: string; type: string; code?: string; subtype?: string; aliases: string[] }>;
      errorTotal: number;
    };
    expect(body.errorTotal).toBe(0);
    expect(body.accounts).toHaveLength(40);

    const byName = new Map(body.accounts.map((a) => [a.name, a]));
    expect(byName.get("Shop Credit Card")).toMatchObject({ type: "liability", subtype: "credit-card" });
    expect(byName.get("Materials Cost")).toMatchObject({ type: "expense", subtype: "cost-of-goods-sold" });
    expect(byName.get("Equipment:CNC Mill")!.aliases).toEqual(["CNC Mill"]);
    expect(byName.get("Product Revenue:Brackets")!.aliases).toEqual(["Brackets"]);
    expect(byName.get("Operating Checking")!.code).toBe("1000");
  });

  it("commit creates 40 accounts with file codes, subtypes, and 5 leaf aliases", async () => {
    const res = await inject("POST", "/api/c/universal/chart/commit", mfgId, {
      uploadId: chartUploadId, mapping: CHART_MAPPING, typeValueMap: CHART_TYPE_VALUE_MAP,
    });
    expect(res.statusCode).toBe(200);
    expect(res.json()).toMatchObject({
      totalInFile: 40, accountsCreated: 40, accountsSkipped: 0,
      aliasesCreated: 5, aliasesSkipped: 0,
    });

    const accounts = await db.admin.query(
      "SELECT code, name, type, subtype FROM accounts WHERE company_id = $1", [mfgId]);
    expect(accounts.rowCount).toBe(40);
    const byName = new Map(accounts.rows.map((r) => [r.name as string, r]));
    expect(byName.get("Operating Checking")).toMatchObject({ code: "1000", type: "asset", subtype: "bank" });
    expect(byName.get("Trade Receivables")).toMatchObject({ subtype: "accounts-receivable" });
    expect(byName.get("Production Labor")).toMatchObject({ type: "expense", subtype: "cost-of-goods-sold" });
    expect(byName.get("Equipment Loan")).toMatchObject({ subtype: "long-term-liability" });

    const aliases = await db.admin.query(
      `SELECT al.alias, a.name FROM account_aliases al
         JOIN accounts a ON a.company_id = al.company_id AND a.id = al.account_id
        WHERE al.company_id = $1 ORDER BY al.alias`, [mfgId]);
    expect(aliases.rows.map((r) => r.alias)).toEqual(
      ["Brackets", "CNC Mill", "Enclosures", "Forklift", "Powder Coating Line"]);
    expect(aliases.rows.find((r) => r.alias === "CNC Mill")!.name).toBe("Equipment:CNC Mill");
  });

  it("re-commit of the same file is idempotent (all skipped, nothing duplicated)", async () => {
    const res = await inject("POST", "/api/c/universal/chart/commit", mfgId, {
      uploadId: chartUploadId, mapping: CHART_MAPPING, typeValueMap: CHART_TYPE_VALUE_MAP,
    });
    expect(res.statusCode).toBe(200);
    expect(res.json()).toMatchObject({ accountsCreated: 0, accountsSkipped: 40 });
    const n = await db.admin.query(
      "SELECT count(*)::int AS n FROM accounts WHERE company_id = $1", [mfgId]);
    expect(n.rows[0].n).toBe(40);
  });

  it("an existing account with a different type is a loud English conflict — nothing imported", async () => {
    const conflictCsv = toCsv([
      ["Account Number", "Account Description", "Account Type"],
      ["9000", "Operating Checking", "Expense"],
      ["9010", "Brand New Account", "Expense"],
    ]);
    const { uploadId } = await upload(mfgId, Buffer.from(conflictCsv), "conflict.csv", "chart");
    const res = await inject("POST", "/api/c/universal/chart/commit", mfgId, {
      uploadId, mapping: CHART_MAPPING, typeValueMap: { Expense: "expense" },
    });
    expect(res.statusCode).toBe(422);
    expect(res.json().error).toMatch(/already exists with a different type.*nothing was imported/i);
    expect(res.json().conflicts).toEqual([
      { name: "Operating Checking", existingType: "asset", mappedType: "expense" },
    ]);
    const n = await db.admin.query(
      "SELECT count(*)::int AS n FROM accounts WHERE company_id = $1", [mfgId]);
    expect(n.rows[0].n).toBe(40); // Brand New Account was NOT half-applied
  });
});

// ---------------------------------------------------------------------------
// journal preview
// ---------------------------------------------------------------------------

describe("universal journal preview", () => {
  it("clean full-file preview: counts, date range, per-year breakdown, zero errors", async () => {
    const res = await inject("POST", "/api/c/universal/journal/preview", mfgId, {
      uploadId: journalUploadId, mapping: JOURNAL_MAPPING,
    });
    expect(res.statusCode).toBe(200);
    const body = res.json() as Record<string, unknown>;
    expect(body.entryCount).toBe(journalFx.entryCount);
    expect(body.lineCount).toBe(journalFx.lineCount);
    // Max date in the generator: i=23 -> month 12, day (23 % 27) + 1 = 24.
    expect(body.dateRange).toEqual({ from: "2019-01-01", to: "2020-12-24" });
    expect(body.yearBreakdown).toEqual([
      { year: 2019, entries: 60, lines: journalFx.lineCount / 2 },
      { year: 2020, entries: 60, lines: journalFx.lineCount / 2 },
    ]);
    expect(body.errorTotal).toBe(0);
  });

  it("poisoned file: row-numbered English errors (European amount, impossible date); stage refuses with 422 and stages nothing", async () => {
    const poisonedId = await createCompany("Poisoned Import Co");
    const csv = toCsv([
      JOURNAL_HEADERS.slice(),
      ["P-1", "01/15/2021", "Operating Checking", "ok line", "100.00", ""],
      ["P-1", "01/15/2021", "Freight Recovery", "ok line", "", "100.00"],
      ["P-2", "01/20/2021", "Operating Checking", "euro amount", "1.234,56", ""],
      ["P-2", "01/20/2021", "Freight Recovery", "euro amount", "", "1234.56"],
      ["P-3", "13/45/2021", "Operating Checking", "impossible date", "50.00", ""],
      ["P-3", "01/22/2021", "Freight Recovery", "impossible date", "", "50.00"],
    ]);
    const { uploadId } = await upload(poisonedId, Buffer.from(csv), "poisoned.csv", "journal");

    const preview = await inject("POST", "/api/c/universal/journal/preview", poisonedId, {
      uploadId, mapping: JOURNAL_MAPPING,
    });
    expect(preview.statusCode).toBe(200);
    const errors = (preview.json() as { errors: Array<{ row: number; message: string }> }).errors;
    expect(errors.find((e) => e.row === 4)?.message).toMatch(/European number format/);
    expect(errors.find((e) => e.row === 6)?.message).toMatch(/invalid month/);

    const stage = await inject("POST", "/api/c/universal/journal/stage", poisonedId, {
      uploadId, mapping: JOURNAL_MAPPING,
    });
    expect(stage.statusCode).toBe(422);
    expect(stage.json().error).toMatch(/nothing was staged/i);
    expect((stage.json() as { errors: unknown[] }).errors.length).toBeGreaterThan(0);

    const batches = await db.admin.query(
      "SELECT count(*)::int AS n FROM import_batches WHERE company_id = $1", [poisonedId]);
    expect(batches.rows[0].n).toBe(0);
  });

  it("an unbalanced entry group passes the engine but fails validate_batch in English, and the post gate refuses it", async () => {
    const unbalancedId = await createCompany("Unbalanced Import Co");
    await inject("POST", "/api/c/setup/fresh", unbalancedId, {});
    const csv = toCsv([
      JOURNAL_HEADERS.slice(),
      ["U-1", "03/10/2021", "Checking Account", "short", "100.00", ""],
      ["U-1", "03/10/2021", "Sales Income", "short", "", "90.00"],
    ]);
    const { uploadId } = await upload(unbalancedId, Buffer.from(csv), "unbalanced.csv", "journal");
    const stage = await inject("POST", "/api/c/universal/journal/stage", unbalancedId, {
      uploadId, mapping: JOURNAL_MAPPING, sourceSlug: "wobbly",
    });
    expect(stage.statusCode).toBe(200);
    const year = (stage.json() as { years: Array<Record<string, unknown>> }).years[0]!;
    expect(year.status).toBe("failed");
    expect(year.errors).toBe(2);
    expect((year.errorGroups as Array<{ error: string }>)[0]!.error).toMatch(/does not balance/);

    const post = await inject("POST", "/api/c/universal/journal/post", unbalancedId, {
      sourceSlug: "wobbly",
    });
    expect(post.statusCode).toBe(409);
    expect(post.json().dirtyYears).toEqual([2021]);
  });
});

// ---------------------------------------------------------------------------
// stage -> post -> trial balance to the penny -> idempotency -> rollback
// ---------------------------------------------------------------------------

describe("universal journal stage/post (Sage-shaped fixture)", () => {
  it("stages one batch per year through the existing pipeline, keeping the ORIGINAL row as raw jsonb", async () => {
    const res = await inject("POST", "/api/c/universal/journal/stage", mfgId, {
      uploadId: journalUploadId, mapping: JOURNAL_MAPPING, sourceSlug: "Sage BusinessWorks",
    });
    expect(res.statusCode).toBe(200);
    const body = res.json() as { source: string; years: Array<Record<string, unknown>> };
    expect(body.source).toBe("universal:sage-businessworks"); // user label, sanitized
    expect(body.years.map((y) => y.year)).toEqual([2019, 2020]);
    for (const y of body.years) {
      expect(y.isNew).toBe(true);
      expect(y.status).toBe("validated");
      expect(y.errors).toBe(0);
      expect(y.entries).toBe(60);
      expect(y.rows).toBe(journalFx.lineCount / 2);
      batchIdByYear[y.year as number] = y.batchId as number;
    }

    const batches = await db.admin.query(
      `SELECT filename, source FROM import_batches WHERE company_id = $1 ORDER BY id`, [mfgId]);
    expect(batches.rowCount).toBe(2);
    expect(batches.rows.every((r) => r.source === "universal:sage-businessworks")).toBe(true);
    expect(batches.rows[0].filename).toMatch(/#2019$/);
    expect(batches.rows[1].filename).toMatch(/#2020$/);

    // Raw jsonb: the dirty "$..." materials debit survives verbatim (§12).
    const raw = await db.admin.query(
      `SELECT raw FROM staging_journal
        WHERE company_id = $1 AND entry_key = '2019-3' AND account_raw = 'Raw Materials Inventory'`,
      [mfgId]);
    expect(raw.rowCount).toBe(1);
    const rawRow = raw.rows[0].raw as { row: number; cells: string[] };
    expect(typeof rawRow.row).toBe("number");
    expect(rawRow.cells[0]).toBe("2019-3");
    expect(rawRow.cells[4]).toMatch(/^\$[\d,]+\.\d{2}$/);
  }, 30_000);

  it("re-staging the same file BEFORE posting re-validates instead of duplicating (the fix loop stays open)", async () => {
    const res = await inject("POST", "/api/c/universal/journal/stage", mfgId, {
      uploadId: journalUploadId, mapping: JOURNAL_MAPPING, sourceSlug: "sage-businessworks",
    });
    expect(res.statusCode).toBe(200);
    const years = (res.json() as { years: Array<Record<string, unknown>> }).years;
    expect(years.every((y) => y.isNew === false)).toBe(true);
    expect(years.every((y) => y.status === "validated")).toBe(true);
    const batches = await db.admin.query(
      "SELECT count(*)::int AS n FROM import_batches WHERE company_id = $1", [mfgId]);
    expect(batches.rows[0].n).toBe(2); // no new batches
  }, 30_000);

  it("posts both years, reconciles, and the trial balance matches the hand-computed expectation TO THE PENNY", async () => {
    const res = await inject("POST", "/api/c/universal/journal/post", mfgId, {
      sourceSlug: "sage-businessworks",
    });
    expect(res.statusCode).toBe(200);
    const years = (res.json() as {
      years: Array<{ year: number; posted: number | null; alreadyPosted: boolean; reconciliation: Record<string, unknown> | null }>;
    }).years;
    expect(years).toHaveLength(2);
    for (const y of years) {
      expect(y.posted).toBe(60);
      expect(y.alreadyPosted).toBe(false);
      expect(y.reconciliation).toMatchObject({ matches: true });
    }

    const tb = await db.admin.query<{ name: string; balance: string }>(
      "SELECT name, balance::text AS balance FROM trial_balance($1, NULL)", [mfgId]);
    const balances = new Map(tb.rows.map((r) => [r.name, r.balance]));

    for (const [account, cents] of journalFx.expected) {
      expect(balances.get(account), `balance of ${account}`).toBe(tb4(cents));
    }
    // The whole ledger still nets to zero, to the penny.
    const total = tb.rows.reduce((sum, r) => sum + tbToCents(r.balance), 0n);
    expect(total).toBe(0n);

    // Spec: NO customers/vendors/documents are created by a universal restore.
    for (const table of ["customers", "vendors", "invoices", "bills"]) {
      const n = await db.admin.query(
        `SELECT count(*)::int AS n FROM ${table} WHERE company_id = $1`, [mfgId]);
      expect(n.rows[0].n, table).toBe(0);
    }
  }, 30_000);

  it("re-staging the identical, fully-posted file is refused: 'This exact file was already imported.'", async () => {
    const again = await inject("POST", "/api/c/universal/journal/stage", mfgId, {
      uploadId: journalUploadId, mapping: JOURNAL_MAPPING, sourceSlug: "sage-businessworks",
    });
    expect(again.statusCode).toBe(409);
    expect(again.json().error).toBe("This exact file was already imported.");

    // Same bytes uploaded fresh (new uploadId, same filename) — still 409.
    const { uploadId } = await upload(
      mfgId, Buffer.from(journalFx.csv), "gl-history.csv", "journal");
    const fresh = await inject("POST", "/api/c/universal/journal/stage", mfgId, {
      uploadId, mapping: JOURNAL_MAPPING, sourceSlug: "sage-businessworks",
    });
    expect(fresh.statusCode).toBe(409);
    expect(fresh.json().error).toBe("This exact file was already imported.");
  }, 30_000);

  it("a DIFFERENT file re-using the same transaction numbers stages, but posting is refused in English (external_ref idempotency)", async () => {
    const variant = buildManufacturerJournal([2019], 11n); // same trx keys, different amounts
    const { uploadId } = await upload(
      mfgId, Buffer.from(variant.csv), "gl-history-restated.csv", "journal");
    const stage = await inject("POST", "/api/c/universal/journal/stage", mfgId, {
      uploadId, mapping: JOURNAL_MAPPING, sourceSlug: "sage-businessworks",
    });
    expect(stage.statusCode).toBe(200); // different file: staging itself is allowed
    expect((stage.json() as { years: Array<Record<string, unknown>> }).years[0]).toMatchObject({
      year: 2019, isNew: true, status: "validated", errors: 0,
    });

    const post = await inject("POST", "/api/c/universal/journal/post", mfgId, {
      sourceSlug: "sage-businessworks", years: [2019],
    });
    expect(post.statusCode).toBe(409);
    expect(post.json().error).toMatch(/already been imported/i);

    // Nothing from the refused batch reached the ledger — balances unchanged.
    const tb = await db.admin.query<{ name: string; balance: string }>(
      "SELECT name, balance::text AS balance FROM trial_balance($1, NULL) WHERE name = 'Trade Receivables'",
      [mfgId]);
    expect(tb.rows[0]!.balance).toBe(tb4(journalFx.expected.get("Trade Receivables")!));
  }, 30_000);

  it("rollback_import_batch REVERSES a universal batch and restores every balance to the 2019-only expectation", async () => {
    const owner = await db.admin.query<{ user_id: string }>(
      "SELECT user_id FROM user_companies WHERE company_id = $1 LIMIT 1", [mfgId]);
    const userId = owner.rows[0]!.user_id;

    const c = await db.appClient(mfgId);
    const reversed = await c.query<{ n: number }>(
      "SELECT rollback_import_batch($1, $2, $3)::int AS n",
      [mfgId, batchIdByYear[2020], userId]);
    expect(reversed.rows[0]!.n).toBe(60);

    const status = await db.admin.query(
      "SELECT status FROM import_batches WHERE company_id = $1 AND id = $2",
      [mfgId, batchIdByYear[2020]]);
    expect(status.rows[0].status).toBe("rolled_back");

    const tb = await db.admin.query<{ name: string; balance: string }>(
      "SELECT name, balance::text AS balance FROM trial_balance($1, NULL)", [mfgId]);
    const balances = new Map(tb.rows.map((r) => [r.name, r.balance]));
    const expected2019 = journalFx.expectedByYear.get(2019)!;
    for (const [account, cents] of expected2019) {
      expect(balances.get(account), `post-rollback balance of ${account}`).toBe(tb4(cents));
    }
    const total = tb.rows.reduce((sum, r) => sum + tbToCents(r.balance), 0n);
    expect(total).toBe(0n);
  }, 30_000);
});

// ---------------------------------------------------------------------------
// generic signed-amount CSV — the other amount shape, end to end
// ---------------------------------------------------------------------------

describe("generic signed-amount CSV restore", () => {
  it("uploads, stages, posts, and ties to the penny with a single signed amount column", async () => {
    const companyId = await createCompany("Signed Amount Co");
    await inject("POST", "/api/c/setup/fresh", companyId, {});

    const csv = toCsv([
      ["Date", "Account", "Amount", "Transaction ID", "Memo"],
      ["2022-03-05", "Checking Account", "500.00", "G-1", "Retainer"],
      ["2022-03-05", "Sales Income", "-500.00", "G-1", "Retainer"],
      ["2022-04-18", "Checking Account", "250.75", "G-2", "Job deposit"],
      ["2022-04-18", "Sales Income", "-250.75", "G-2", "Job deposit"],
      ["2022-06-30", "Checking Account", "100.00", "G-3", "Small job"],
      ["2022-06-30", "Sales Income", "-100.00", "G-3", "Small job"],
    ]);
    const { uploadId, body } = await upload(companyId, Buffer.from(csv), "everything.csv", "journal");
    expect((body.presetGuess as { key: string }).key).toBe("generic-signed");

    const mapping = {
      headerRows: 1, dateCol: 0, dateFormat: "YYYY-MM-DD", accountCol: 1,
      amountShape: { kind: "signed", amountCol: 2, positiveIs: "debit" },
      grouping: { kind: "key-column", groupKeyCol: 3 },
      memoCol: 4,
    };
    const stage = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId, mapping,
    });
    expect(stage.statusCode).toBe(200);
    expect((stage.json() as { source: string }).source).toBe("universal:import"); // stable default
    expect((stage.json() as { years: Array<Record<string, unknown>> }).years[0]).toMatchObject({
      year: 2022, status: "validated", errors: 0, entries: 3,
    });

    const post = await inject("POST", "/api/c/universal/journal/post", companyId, {});
    expect(post.statusCode).toBe(200);

    const tb = await db.admin.query<{ name: string; balance: string }>(
      "SELECT name, balance::text AS balance FROM trial_balance($1, NULL) WHERE name IN ('Checking Account', 'Sales Income')",
      [companyId]);
    const balances = new Map(tb.rows.map((r) => [r.name, r.balance]));
    expect(balances.get("Checking Account")).toBe("850.7500");
    expect(balances.get("Sales Income")).toBe("-850.7500");
  }, 30_000);
});

// ---------------------------------------------------------------------------
// unmatched-account resolution — the QB fix loop, source-agnostic
// ---------------------------------------------------------------------------

describe("unmatched-account fix loop on a universal batch", () => {
  it("stage flags the unmapped account loudly; adding the account and re-staging the SAME file goes clean and posts", async () => {
    const companyId = await createCompany("Fix Loop Fabrication");
    await inject("POST", "/api/c/setup/fresh", companyId, {});

    const csv = toCsv([
      ["Date", "Account", "Debit", "Credit", "Transaction"],
      ["06/15/2023", "Mystery Consulting", "300.00", "", "F-1"],
      ["06/15/2023", "Checking Account", "", "300.00", "F-1"],
    ]);
    const { uploadId } = await upload(companyId, Buffer.from(csv), "fixloop.csv", "journal");
    const mapping = {
      headerRows: 1, dateCol: 0, dateFormat: "MM/DD/YYYY", accountCol: 1,
      amountShape: { kind: "debit-credit", debitCol: 2, creditCol: 3 },
      grouping: { kind: "key-column", groupKeyCol: 4 },
    };

    const first = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId, mapping,
    });
    expect(first.statusCode).toBe(200);
    const firstYear = (first.json() as { source: string; years: Array<Record<string, unknown>> });
    expect(firstYear.source).toBe("universal:import"); // stable default, never content-derived
    // Two flagged rows: the unmapped account itself, plus its partner line —
    // with the bad row excluded, entry F-1 no longer balances. Both loud.
    expect(firstYear.years[0]).toMatchObject({ status: "failed", errors: 2 });
    const groupErrors = (firstYear.years[0]!.errorGroups as Array<{ error: string }>)
      .map((g) => g.error);
    expect(groupErrors.some((e) => /unmapped account.*Mystery Consulting/.test(e))).toBe(true);

    // The fix: create the missing account (same flow the QB wizard points at).
    const created = await inject("POST", "/api/c/accounts", companyId, {
      name: "Mystery Consulting", type: "expense",
    });
    expect(created.statusCode).toBe(201);

    // Re-stage the SAME file: re-validates the existing batch — no duplicate,
    // no false "already imported", and it comes back clean.
    const second = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId, mapping,
    });
    expect(second.statusCode).toBe(200);
    expect((second.json() as { years: Array<Record<string, unknown>> }).years[0]).toMatchObject({
      isNew: false, status: "validated", errors: 0,
    });

    const post = await inject("POST", "/api/c/universal/journal/post", companyId, {});
    expect(post.statusCode).toBe(200);
    expect((post.json() as { years: Array<{ posted: number }> }).years[0]!.posted).toBe(1);
  }, 30_000);
});

// ---------------------------------------------------------------------------
// uploadId hygiene, saved mappings, RLS
// ---------------------------------------------------------------------------

describe("uploadId hygiene", () => {
  it("traversal-shaped uploadIds are a clean 400; an unknown token is the exact expired English", async () => {
    for (const bad of ["../../etc/passwd", "..%2F..%2Fsecrets", "not-a-uuid"]) {
      const res = await inject("POST", "/api/c/universal/chart/preview", mfgId, {
        uploadId: bad, mapping: CHART_MAPPING, typeValueMap: CHART_TYPE_VALUE_MAP,
      });
      expect(res.statusCode, bad).toBe(400);
    }

    const unknown = await inject("POST", "/api/c/universal/chart/preview", mfgId, {
      uploadId: randomUUID(), mapping: CHART_MAPPING, typeValueMap: CHART_TYPE_VALUE_MAP,
    });
    expect(unknown.statusCode).toBe(404);
    expect(unknown.json().error).toBe("That upload has expired — upload the file again.");
  });

  it("another company's uploadId (and the wrong kind) both read as expired — nothing leaks", async () => {
    const otherId = await createCompany("Other Books LLC");
    const crossCompany = await inject("POST", "/api/c/universal/chart/preview", otherId, {
      uploadId: chartUploadId, mapping: CHART_MAPPING, typeValueMap: CHART_TYPE_VALUE_MAP,
    });
    expect(crossCompany.statusCode).toBe(404);
    expect(crossCompany.json().error).toMatch(/expired/);

    // A journal uploadId used on a chart route (same company) — same answer.
    const wrongKind = await inject("POST", "/api/c/universal/chart/preview", mfgId, {
      uploadId: journalUploadId, mapping: CHART_MAPPING, typeValueMap: CHART_TYPE_VALUE_MAP,
    });
    expect(wrongKind.statusCode).toBe(404);
  });
});

describe("saved mappings (universal_mappings)", () => {
  it("save-by-name upserts, lists by kind, and is invisible to other companies", async () => {
    const save = await inject("POST", "/api/c/universal/mappings", mfgId, {
      kind: "journal", name: "Sage GL layout", mapping: JOURNAL_MAPPING,
    });
    expect(save.statusCode).toBe(200);

    const list = await inject("GET", "/api/c/universal/mappings?kind=journal", mfgId);
    expect(list.statusCode).toBe(200);
    const mappings = (list.json() as { mappings: Array<Record<string, unknown>> }).mappings;
    expect(mappings).toHaveLength(1);
    expect(mappings[0]).toMatchObject({ kind: "journal", name: "Sage GL layout" });
    expect((mappings[0]!.mapping as Record<string, unknown>).dateCol).toBe(1);

    // Upsert: same name replaces, no second row.
    const changed = { ...JOURNAL_MAPPING, memoCol: undefined, refCol: 3 };
    const resave = await inject("POST", "/api/c/universal/mappings", mfgId, {
      kind: "journal", name: "Sage GL layout", mapping: changed,
    });
    expect(resave.statusCode).toBe(200);
    const after = await inject("GET", "/api/c/universal/mappings?kind=journal", mfgId);
    const afterMappings = (after.json() as { mappings: Array<Record<string, unknown>> }).mappings;
    expect(afterMappings).toHaveLength(1);
    expect((afterMappings[0]!.mapping as Record<string, unknown>).refCol).toBe(3);

    // Another company: empty list, and the same name is freely reusable.
    const otherId = await createCompany("Mapping Isolation Co");
    const otherList = await inject("GET", "/api/c/universal/mappings", otherId);
    expect((otherList.json() as { mappings: unknown[] }).mappings).toHaveLength(0);
    const otherSave = await inject("POST", "/api/c/universal/mappings", otherId, {
      kind: "journal", name: "Sage GL layout", mapping: { dateCol: 9 },
    });
    expect(otherSave.statusCode).toBe(200);
  });

  it("rejects a bad kind and a blank name in English", async () => {
    const badKind = await inject("POST", "/api/c/universal/mappings", mfgId, {
      kind: "sideways", name: "x", mapping: {},
    });
    expect(badKind.statusCode).toBe(400);
    expect(badKind.json().error).toMatch(/chart.*journal/i);

    const blankName = await inject("POST", "/api/c/universal/mappings", mfgId, {
      kind: "chart", name: "   ", mapping: {},
    });
    expect(blankName.statusCode).toBe(400);
    expect(blankName.json().error).toMatch(/name/i);
  });
});

// ---------------------------------------------------------------------------
// review fixes: double-post hole (F1/F2), English post errors (F3),
// rollback re-import loop (F5), consecutive grouping, error cap
// ---------------------------------------------------------------------------

const PROBE_HEADERS = ["Date", "Account", "Debit", "Credit", "Transaction"];
const PROBE_MAPPING = {
  headerRows: 1, dateCol: 0, dateFormat: "MM/DD/YYYY", accountCol: 1,
  amountShape: { kind: "debit-credit", debitCol: 2, creditCol: 3 },
  grouping: { kind: "key-column", groupKeyCol: 4 },
};

async function checkingBalance(companyId: string): Promise<string> {
  const tb = await db.admin.query<{ balance: string }>(
    "SELECT balance::text AS balance FROM trial_balance($1, NULL) WHERE name = 'Checking Account'",
    [companyId]);
  return tb.rows[0]!.balance;
}

describe("double-post protection (review F1/F2)", () => {
  let companyId: string;
  let renamedUploadId: string;
  const probeCsv = toCsv([
    PROBE_HEADERS.slice(),
    ["05/10/2023", "Checking Account", "100.00", "", "T-100"],
    ["05/10/2023", "Sales Income", "", "100.00", "T-100"],
  ]);

  it("the same bytes renamed + a different import name are refused at stage (content sha, source-filtered recovery)", async () => {
    companyId = await createCompany("Double Entry Probe Co");
    await inject("POST", "/api/c/setup/fresh", companyId, {});

    const first = await upload(companyId, Buffer.from(probeCsv), "books.csv", "journal");
    const staged = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId: first.uploadId, mapping: PROBE_MAPPING,
    });
    expect(staged.statusCode).toBe(200);
    expect((staged.json() as { source: string }).source).toBe("universal:import");

    // Same bytes, new filename, different label — refused before anything
    // is staged, because the batch sha is content-only and the duplicate
    // recovery no longer adopts another source's batch.
    const renamed = await upload(companyId, Buffer.from(probeCsv), "renamed-books.csv", "journal");
    renamedUploadId = renamed.uploadId;
    const rejected = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId: renamedUploadId, mapping: PROBE_MAPPING, sourceSlug: "other-books",
    });
    expect(rejected.statusCode).toBe(409);
    expect(rejected.json().error).toMatch(/identical file/i);

    // F2: the foreign-label attempt did NOT mislabel or adopt any batch.
    const foreign = await db.admin.query(
      "SELECT count(*)::int AS n FROM import_batches WHERE company_id = $1 AND source = 'universal:other-books'",
      [companyId]);
    expect(foreign.rows[0].n).toBe(0);
  }, 30_000);

  it("a slug that sanitizes to nothing resolves to the SAME default at post as at stage", async () => {
    const post = await inject("POST", "/api/c/universal/journal/post", companyId, {
      sourceSlug: "###", // sanitizes empty -> the same 'import' default the stage used
    });
    expect(post.statusCode).toBe(200);
    expect(await checkingBalance(companyId)).toBe("100.0000");
  }, 30_000);

  it("after posting, the renamed re-upload under another name is still refused — $100 stays $100, never $200", async () => {
    const rejected = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId: renamedUploadId, mapping: PROBE_MAPPING, sourceSlug: "other-books",
    });
    expect(rejected.statusCode).toBe(409);
    expect(await checkingBalance(companyId)).toBe("100.0000");
  }, 30_000);

  it("overlap guard: a tweaked re-export (different bytes, same entries) under a different name is refused in English", async () => {
    // Same books, but the re-export gained a memo column — different sha,
    // so only the posted-entry overlap guard can catch it.
    const tweakedCsv = toCsv([
      [...PROBE_HEADERS, "Memo"],
      ["05/10/2023", "Checking Account", "100.00", "", "T-100", "spring cleanup"],
      ["05/10/2023", "Sales Income", "", "100.00", "T-100", "spring cleanup"],
    ]);
    const { uploadId } = await upload(companyId, Buffer.from(tweakedCsv), "tweaked-books.csv", "journal");
    const rejected = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId, mapping: { ...PROBE_MAPPING, memoCol: 5 }, sourceSlug: "other-books",
    });
    expect(rejected.statusCode).toBe(409);
    expect(rejected.json().error).toBe(
      "Some of these entries were already imported under a different import name " +
        "(universal:import) — if this is a re-import, roll back that batch first.",
    );
    expect(await checkingBalance(companyId)).toBe("100.0000");

    const batches = await db.admin.query(
      "SELECT count(*)::int AS n FROM import_batches WHERE company_id = $1", [companyId]);
    expect(batches.rows[0].n).toBe(1); // still just the original batch
  }, 30_000);
});

describe("post errors are English at every level (review F3)", () => {
  it("a period lock hit at post time maps to the locked-year English, per year too", async () => {
    const companyId = await createCompany("Locked Period Probe Co");
    await inject("POST", "/api/c/setup/fresh", companyId, {});
    const csv = toCsv([
      PROBE_HEADERS.slice(),
      ["04/15/2023", "Checking Account", "200.00", "", "L-1"],
      ["04/15/2023", "Sales Income", "", "200.00", "L-1"],
    ]);
    const { uploadId } = await upload(companyId, Buffer.from(csv), "locked.csv", "journal");
    const staged = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId, mapping: PROBE_MAPPING,
    });
    expect(staged.statusCode).toBe(200);

    // Lock the period AFTER validation, so the refusal happens inside
    // post_batch -> post_entry, not at validate time.
    const owner = await db.admin.query<{ user_id: string }>(
      "SELECT user_id FROM user_companies WHERE company_id = $1 LIMIT 1", [companyId]);
    await db.admin.query(
      "INSERT INTO period_locks (company_id, locked_through, created_by) VALUES ($1, '2023-12-31', $2)",
      [companyId, owner.rows[0]!.user_id]);

    const post = await inject("POST", "/api/c/universal/journal/post", companyId, {});
    expect(post.statusCode).toBe(409);
    const body = post.json() as { error: string; years: Array<{ error?: string }> };
    expect(body.error).toMatch(/That year is locked \(through 2023-12-31\)/);
    expect(body.years[0]!.error).toBe(body.error); // per-year error: same English
    expect(body.years[0]!.error).not.toMatch(/cannot post into locked period/); // never the raw PG text
  }, 30_000);
});

describe("rollback then corrected re-import (review F5)", () => {
  it("post -> rollback -> re-stage (allowed, with the informative note) -> re-post -> balances correct exactly once", async () => {
    const companyId = await createCompany("Rollback Rebuild Co");
    await inject("POST", "/api/c/setup/fresh", companyId, {});
    const csv = toCsv([
      PROBE_HEADERS.slice(),
      ["03/20/2024", "Checking Account", "100.00", "", "R-1"],
      ["03/20/2024", "Sales Income", "", "100.00", "R-1"],
    ]);
    const { uploadId } = await upload(companyId, Buffer.from(csv), "rebuild.csv", "journal");
    const staged = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId, mapping: PROBE_MAPPING,
    });
    expect(staged.statusCode).toBe(200);
    const batchId = (staged.json() as { years: Array<{ batchId: number }> }).years[0]!.batchId;

    const post = await inject("POST", "/api/c/universal/journal/post", companyId, {});
    expect(post.statusCode).toBe(200);
    expect(await checkingBalance(companyId)).toBe("100.0000");

    const owner = await db.admin.query<{ user_id: string }>(
      "SELECT user_id FROM user_companies WHERE company_id = $1 LIMIT 1", [companyId]);
    const c = await db.appClient(companyId);
    const reversed = await c.query<{ n: number }>(
      "SELECT rollback_import_batch($1, $2, $3)::int AS n",
      [companyId, batchId, owner.rows[0]!.user_id]);
    expect(reversed.rows[0]!.n).toBe(1);
    expect(await checkingBalance(companyId)).toBe("0.0000");

    // Re-staging the identical file is ALLOWED after a rollback — with the
    // informative note, not the misleading "already imported" refusal.
    const restage = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId, mapping: PROBE_MAPPING,
    });
    expect(restage.statusCode).toBe(200);
    const year = (restage.json() as { years: Array<Record<string, unknown>> }).years[0]!;
    expect(year.isNew).toBe(true);
    expect(year.status).toBe("validated");
    expect(year.note).toBe(
      "This file was imported and then rolled back — re-importing will post it again.");

    const repost = await inject("POST", "/api/c/universal/journal/post", companyId, {});
    expect(repost.statusCode).toBe(200);
    expect((repost.json() as { years: Array<{ posted: number }> }).years[0]!.posted).toBe(1);

    // Exactly once: original (reversed out) + reversal + re-import = $100.
    expect(await checkingBalance(companyId)).toBe("100.0000");
  }, 30_000);
});

describe("consecutive-balanced grouping end to end", () => {
  it("a file with NO transaction column groups until balanced, keeps row-N raw provenance, and posts", async () => {
    const companyId = await createCompany("No Trx Column Co");
    await inject("POST", "/api/c/setup/fresh", companyId, {});
    const csv = toCsv([
      ["Date", "Account", "Memo", "Debit", "Credit"],
      ["01/10/2023", "Checking Account", "first sale", "150.00", ""],
      ["01/10/2023", "Sales Income", "first sale", "", "150.00"],
      ["02/12/2023", "Checking Account", "second sale", "75.25", ""],
      ["02/12/2023", "Sales Income", "second sale", "", "75.25"],
    ]);
    const { uploadId } = await upload(companyId, Buffer.from(csv), "no-trx.csv", "journal");
    const mapping = {
      headerRows: 1, dateCol: 0, dateFormat: "MM/DD/YYYY", accountCol: 1,
      amountShape: { kind: "debit-credit", debitCol: 3, creditCol: 4 },
      grouping: { kind: "consecutive-balanced" },
      memoCol: 2,
    };

    const staged = await inject("POST", "/api/c/universal/journal/stage", companyId, {
      uploadId, mapping,
    });
    expect(staged.statusCode).toBe(200);
    expect((staged.json() as { years: Array<Record<string, unknown>> }).years[0]).toMatchObject({
      year: 2023, status: "validated", errors: 0, entries: 2,
    });

    // Raw provider's row-N branch: entry "row-2" carries rows 2 and 3 verbatim.
    const raw = await db.admin.query(
      `SELECT raw FROM staging_journal
        WHERE company_id = $1 AND entry_key = 'row-2' ORDER BY id`, [companyId]);
    expect(raw.rowCount).toBe(2);
    expect(raw.rows[0].raw).toEqual({
      row: 2, cells: ["01/10/2023", "Checking Account", "first sale", "150.00", ""],
    });
    expect(raw.rows[1].raw).toEqual({
      row: 3, cells: ["01/10/2023", "Sales Income", "first sale", "", "150.00"],
    });

    const post = await inject("POST", "/api/c/universal/journal/post", companyId, {});
    expect(post.statusCode).toBe(200);
    expect(await checkingBalance(companyId)).toBe("225.2500");
  }, 30_000);
});

describe("error list cap", () => {
  it("preview returns at most 50 row errors plus the true total", async () => {
    const companyId = await createCompany("Sixty Errors Co");
    const rows: string[][] = [PROBE_HEADERS.slice()];
    for (let i = 0; i < 60; i++) {
      rows.push(["99/99/2023", "Checking Account", "10.00", "", `E-${i + 1}`]);
    }
    const { uploadId } = await upload(companyId, Buffer.from(toCsv(rows)), "sixty.csv", "journal");
    const preview = await inject("POST", "/api/c/universal/journal/preview", companyId, {
      uploadId, mapping: PROBE_MAPPING,
    });
    expect(preview.statusCode).toBe(200);
    const body = preview.json() as { errors: unknown[]; errorTotal: number };
    expect(body.errors).toHaveLength(50);
    expect(body.errorTotal).toBe(60);
  });
});

describe("RLS isolation for universal import data", () => {
  it("an app connection with NO company scope sees zero rows in every import table", async () => {
    const unscoped = await db.appClient(null);
    for (const table of ["import_batches", "staging_journal", "universal_mappings", "account_aliases"]) {
      const res = await unscoped.query(`SELECT count(*)::int AS n FROM ${table}`);
      expect(res.rows[0].n, table).toBe(0);
    }
  });

  it("another company's scope cannot see the manufacturer's batches, staging rows, or saved mappings", async () => {
    const scopedToB = await db.appClient(db.companyB);
    for (const table of ["import_batches", "staging_journal", "universal_mappings"]) {
      const res = await scopedToB.query(`SELECT count(*)::int AS n FROM ${table}`);
      expect(res.rows[0].n, table).toBe(0); // helper company B has none of its own
    }
    // And the manufacturer's own scope DOES see its rows (non-vacuous check).
    const scopedToMfg = await db.appClient(mfgId);
    const own = await scopedToMfg.query("SELECT count(*)::int AS n FROM staging_journal");
    expect(own.rows[0].n).toBeGreaterThan(0);
  });
});
