// Integration tests for the setup/migration API (Task 4 of
// docs/superpowers/plans/2026-07-31-local-app.md): the starter-chart "fresh"
// path, the QuickBooks Account Listing / Journal upload endpoints, and the
// post gate. Boots the app builder against a throwaway DB (tests/helpers/db.ts)
// and drives the multipart uploads with hand-built xlsx fixtures (in-memory
// ExcelJS workbooks, written to a Buffer — no real files on disk beyond what
// the routes themselves save under imports/uploads/).
//
// Multipart bodies are constructed by hand (a single "file" part) rather
// than pulling in a form-data dependency — CLAUDE.md §2 "ask before adding
// any dependency", and this only needs one field.

import { describe, it, expect, beforeAll, afterAll } from "vitest";
import ExcelJS from "exceljs";
import { readdir } from "node:fs/promises";
import path from "node:path";
import { fileURLToPath } from "node:url";
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";

const testDir = path.dirname(fileURLToPath(import.meta.url));
const UPLOAD_DIR = path.resolve(testDir, "../../imports/uploads");

async function buildTestApp(dbName: string): Promise<FastifyInstance> {
  return buildApp({
    databaseUrl: await dbUrl(dbName, "app", "app"),
    adminDatabaseUrl: await dbUrl(dbName),
  });
}

// ---------------------------------------------------------------------------
// xlsx fixture builders — see qb-xlsx.ts's 1-indexed row/column convention.
// Account Listing: header row 1; columns 3=Account, 5=Type.
// Journal: header row 1; columns 2=Trans#, 4=Type, 6=Date, 8=Num, 10=Name,
// 12=Memo, 14=Account, 16=Debit, 18=Credit; column 1 = subtotal marker.
// addRow() takes a 0-indexed array (array[0] -> column A), so "column N"
// above lands at array index N-1.
// ---------------------------------------------------------------------------

async function buildListingXlsx(rows: Array<{ account: string; qbType: string }>): Promise<Buffer> {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("Sheet1");
  ws.addRow([]); // header row — content irrelevant, parseListingRows skips it
  for (const r of rows) {
    const arr: unknown[] = new Array(5).fill(undefined);
    arr[2] = r.account;
    arr[4] = r.qbType;
    ws.addRow(arr);
  }
  return Buffer.from(await wb.xlsx.writeBuffer());
}

interface JournalLineSpec {
  trans?: string | number;
  date?: Date;
  account: string;
  debit?: number;
  credit?: number;
  name?: string;
  memo?: string;
}

/** One JournalLineSpec[] per entry; each entry gets a subtotal row after its lines. */
async function buildJournalXlsx(entries: JournalLineSpec[][]): Promise<Buffer> {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("Sheet1");
  ws.addRow([]); // header row

  let totalDebit = 0;
  let totalCredit = 0;
  for (const lines of entries) {
    for (const line of lines) {
      const arr: unknown[] = new Array(18).fill(undefined);
      if (line.trans !== undefined) arr[1] = line.trans;
      arr[3] = "General Journal";
      if (line.date) arr[5] = line.date;
      if (line.name) arr[9] = line.name;
      if (line.memo) arr[11] = line.memo;
      arr[13] = line.account;
      if (line.debit !== undefined) { arr[15] = line.debit; totalDebit += line.debit; }
      if (line.credit !== undefined) { arr[17] = line.credit; totalCredit += line.credit; }
      ws.addRow(arr);
    }
    ws.addRow([" "]); // subtotal row — terminates the entry
  }
  const totalRow: unknown[] = new Array(18).fill(undefined);
  totalRow[0] = "TOTAL";
  totalRow[15] = totalDebit;
  totalRow[17] = totalCredit;
  ws.addRow(totalRow);

  return Buffer.from(await wb.xlsx.writeBuffer());
}

/** A deliberately garbage journal xlsx: a data row with no Trans# and no
 *  entry open — parseJournalRows must throw on this, loudly. */
async function buildGarbageJournalXlsx(): Promise<Buffer> {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("Sheet1");
  ws.addRow([]); // header row
  const arr: unknown[] = new Array(18).fill(undefined);
  arr[13] = "Checking Account"; // Account cell present, but no Trans# and no open entry
  arr[15] = 100;
  ws.addRow(arr);
  return Buffer.from(await wb.xlsx.writeBuffer());
}

function multipartPayload(
  fileBuffer: Buffer, filename: string, fieldName = "file",
): { payload: Buffer; contentType: string } {
  const boundary = `----setupTestBoundary${Math.random().toString(16).slice(2)}`;
  const head = Buffer.from(
    `--${boundary}\r\n` +
      `Content-Disposition: form-data; name="${fieldName}"; filename="${filename}"\r\n` +
      `Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\r\n\r\n`,
  );
  const tail = Buffer.from(`\r\n--${boundary}--\r\n`);
  return { payload: Buffer.concat([head, fileBuffer, tail]), contentType: `multipart/form-data; boundary=${boundary}` };
}

let db: TestDb;
let app: FastifyInstance;

beforeAll(async () => {
  db = await createTestDb();
  app = await buildTestApp(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 injectMultipart(url: string, companyId: string, fileBuffer: Buffer, filename: string) {
  const { payload, contentType } = multipartPayload(fileBuffer, filename);
  return app.inject({
    method: "POST", url,
    headers: { "x-company-id": companyId, "content-type": contentType },
    payload,
  });
}

describe("POST /api/c/setup/fresh", () => {
  it("creates the starter chart for a brand-new (zero-account) company", async () => {
    const companyId = await createCompany("Fresh Co");
    const res = await inject("POST", "/api/c/setup/fresh", companyId, { template: "small-business" });
    expect(res.statusCode).toBe(200);
    expect(res.json()).toEqual({ accountsCreated: 40 });

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

  it("refuses a second call with a 409 English error, and creates nothing more", async () => {
    const companyId = await createCompany("Fresh Co Two");
    const first = await inject("POST", "/api/c/setup/fresh", companyId, { template: "small-business" });
    expect(first.statusCode).toBe(200);

    const second = await inject("POST", "/api/c/setup/fresh", companyId, { template: "small-business" });
    expect(second.statusCode).toBe(409);
    expect(second.json().error).toBe("This company already has a chart of accounts");

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

  it("rejects an unknown template with a 400", async () => {
    const companyId = await createCompany("Fresh Co Three");
    const res = await inject("POST", "/api/c/setup/fresh", companyId, { template: "nonexistent" });
    expect(res.statusCode).toBe(400);
  });

  it("RLS isolation: the new company's accounts are invisible outside its own scope", async () => {
    const companyId = await createCompany("Fresh Co RLS");
    await inject("POST", "/api/c/setup/fresh", companyId, { template: "small-business" });

    // company A's app-scoped connection has NO WHERE clause here at all —
    // RLS alone must keep Fresh Co RLS's 40 accounts out of this result
    // (CLAUDE.md §11: "a query without a company filter... returns zero rows").
    const scopedToA = await db.appClient(db.companyA);
    const seenByA = await scopedToA.query("SELECT count(*)::int AS n FROM accounts");
    expect(seenByA.rows[0].n).toBe(8); // company A's own seeded chart only

    const scopedToFresh = await db.appClient(companyId);
    const seenByFresh = await scopedToFresh.query("SELECT count(*)::int AS n FROM accounts");
    expect(seenByFresh.rows[0].n).toBe(40);
  });
});

describe("POST /api/c/import/chart", () => {
  it("loads a valid Account Listing xlsx and reports the result", async () => {
    const companyId = await createCompany("Chart Import Co");
    const xlsx = await buildListingXlsx([
      { account: "Money Market", qbType: "Bank" },
      { account: "Vendor Payable", qbType: "Accounts Payable" },
      { account: "Consulting Income", qbType: "Income" },
    ]);
    const res = await injectMultipart("/api/c/import/chart", companyId, xlsx, "listing.xlsx");
    expect(res.statusCode).toBe(200);
    const body = res.json();
    expect(body).toMatchObject({
      companyCreated: false, accountsCreated: 3, accountsSkipped: 0,
    });
    expect(typeof body.aliasesCreated).toBe("number");
    expect(typeof body.aliasesSkipped).toBe("number");

    const rows = await db.admin.query(
      "SELECT name, type FROM accounts WHERE company_id = $1 AND name = 'Money Market'", [companyId],
    );
    expect(rows.rows[0]).toMatchObject({ name: "Money Market", type: "asset" });
  });

  it("422s on an unrecognized QuickBooks type, with loud details, nothing written", async () => {
    const companyId = await createCompany("Chart Unknown Type Co");
    const xlsx = await buildListingXlsx([
      { account: "Good Account", qbType: "Bank" },
      { account: "Weird Account", qbType: "Something Bogus" },
    ]);
    const res = await injectMultipart("/api/c/import/chart", companyId, xlsx, "listing.xlsx");
    expect(res.statusCode).toBe(422);
    const body = res.json();
    expect(body.error).toMatch(/type we don't recognize/i);
    expect(body.unknownTypes).toEqual([{ name: "Weird Account", qbType: "Something Bogus" }]);

    const rows = await db.admin.query("SELECT count(*)::int AS n FROM accounts WHERE company_id = $1", [companyId]);
    expect(rows.rows[0].n).toBe(0); // nothing written — rolled back
  });

  it("400s when the uploaded file isn't really an xlsx (magic-byte check)", async () => {
    const companyId = await createCompany("Chart Bad File Co");
    const notXlsx = Buffer.from("this is not an xlsx file at all");
    const res = await injectMultipart("/api/c/import/chart", companyId, notXlsx, "listing.xlsx");
    expect(res.statusCode).toBe(400);
    expect(res.json().error).toMatch(/valid excel/i);
  });

  it("400s when the filename doesn't end in .xlsx", async () => {
    const companyId = await createCompany("Chart Bad Ext Co");
    const xlsx = await buildListingXlsx([{ account: "X", qbType: "Bank" }]);
    const res = await injectMultipart("/api/c/import/chart", companyId, xlsx, "listing.csv");
    expect(res.statusCode).toBe(400);
    expect(res.json().error).toMatch(/\.xlsx/i);
  });

  it("concurrent uploads for the same company never collide on the saved filename", async () => {
    // Regression test: the saved path used to be `${companyId}-${Date.now()}-
    // ${suffix}.xlsx` — two uploads landing in the same millisecond would
    // silently clobber each other's file on disk, and both requests would
    // then parse whichever bytes won the write race. Firing many uploads
    // at once, each with DIFFERENT content, is exactly what reproduced that
    // (4/5 trials before the fix). With a uuid in the filename, every
    // request gets its own file no matter how many land in the same tick.
    const companyId = await createCompany("Chart Concurrency Co");
    const N = 20;

    const xlsxBuffers = await Promise.all(
      Array.from({ length: N }, (_, i) => buildListingXlsx([
        { account: `Concurrent Account ${i}`, qbType: "Bank" },
      ])),
    );

    const responses = await Promise.all(
      xlsxBuffers.map((buf) => injectMultipart("/api/c/import/chart", companyId, buf, "listing.xlsx")),
    );

    for (const res of responses) {
      expect(res.statusCode).toBe(200);
      expect(res.json()).toMatchObject({ accountsCreated: 1 });
    }

    // Every request's own distinctly-named account made it in — impossible
    // if two requests had parsed the same (clobbered) file.
    const rows = await db.admin.query<{ n: string }>(
      "SELECT count(*)::int AS n FROM accounts WHERE company_id = $1 AND name LIKE 'Concurrent Account %'",
      [companyId],
    );
    expect(Number(rows.rows[0]!.n)).toBe(N);

    // And at the filesystem level: N requests must have produced N distinct
    // saved files for this company, not fewer (which would mean a collision).
    const files = await readdir(UPLOAD_DIR);
    const ownFiles = files.filter((f) => f.startsWith(`${companyId}-`) && f.endsWith("-chart.xlsx"));
    expect(ownFiles).toHaveLength(N);
    expect(new Set(ownFiles).size).toBe(N); // paranoia: also distinct as a set
    // 20 concurrent multipart uploads, each parsing a real xlsx: this is
    // deliberately heavy, and alongside other workers it runs past vitest's
    // 5s default. The work is I/O, not a hang — the concurrency is the point
    // of the test, so give it room rather than shrinking N.
  }, 60_000);
});

describe("POST /api/c/import/journal + POST /api/c/import/post", () => {
  it("validates a clean 2-year journal, posts it, cross-checks totals, and reports alreadyPosted on re-post", async () => {
    const companyId = await createCompany("Journal Happy Co");
    const freshRes = await inject("POST", "/api/c/setup/fresh", companyId, { template: "small-business" });
    expect(freshRes.statusCode).toBe(200);

    const journalXlsx = await buildJournalXlsx([
      [ // 2019 entry: Checking Account debit 100 / Sales Income credit 100
        { trans: 1, date: new Date(Date.UTC(2019, 0, 15)), account: "Checking Account", debit: 100, name: "Acme" },
        { account: "Sales Income", credit: 100, name: "Acme" },
      ],
      [ // 2020 entry: Checking Account debit 200 / Sales Income credit 200
        { trans: 2, date: new Date(Date.UTC(2020, 5, 1)), account: "Checking Account", debit: 200, name: "Acme" },
        { account: "Sales Income", credit: 200, name: "Acme" },
      ],
    ]);

    const journalRes = await injectMultipart("/api/c/import/journal", companyId, journalXlsx, "journal.xlsx");
    expect(journalRes.statusCode).toBe(200);
    const journalBody = journalRes.json() as { years: Array<Record<string, unknown>> };
    expect(journalBody.years).toHaveLength(2);
    for (const y of journalBody.years) {
      expect(y.errors).toBe(0);
      expect(y.entries).toBe(1);
      expect(y.status).toBe("validated");
    }
    expect(journalBody.years.map((y) => y.year)).toEqual([2019, 2020]);

    const postRes = await inject("POST", "/api/c/import/post", companyId, {});
    expect(postRes.statusCode).toBe(200);
    const postBody = postRes.json() as {
      years: Array<{ year: number; posted: number | null; alreadyPosted: boolean; reconciliation: Record<string, unknown> | null }>;
    };
    expect(postBody.years).toHaveLength(2);
    for (const y of postBody.years) {
      expect(y.posted).toBe(1);
      expect(y.alreadyPosted).toBe(false);
      expect(y.reconciliation).toMatchObject({ matches: true });
    }

    // Cross-check against the ledger directly.
    const acct = await db.admin.query<{ id: string }>(
      "SELECT id FROM accounts WHERE company_id = $1 AND name = 'Checking Account'", [companyId],
    );
    const tb = await db.admin.query(
      "SELECT balance FROM trial_balance($1, NULL) WHERE account_id = $2", [companyId, acct.rows[0]!.id],
    );
    expect(tb.rows[0].balance).toBe("300.0000"); // 100 + 200, debit-positive asset

    // Re-post: both years already posted.
    const repostRes = await inject("POST", "/api/c/import/post", companyId, {});
    expect(repostRes.statusCode).toBe(200);
    const repostBody = repostRes.json() as { years: Array<{ alreadyPosted: boolean; posted: number | null }> };
    for (const y of repostBody.years) {
      expect(y.alreadyPosted).toBe(true);
      expect(y.posted).toBeNull();
    }
  });

  it("surfaces an unbalanced entry as a per-row error, still 200 (the report IS the product)", async () => {
    const companyId = await createCompany("Journal Unbalanced Co");
    await inject("POST", "/api/c/setup/fresh", companyId, { template: "small-business" });

    // Debit 100 / credit 90 — deliberately unbalanced. The file's own grand
    // total matches the (unbalanced) parsed sum, so parseJournalRows itself
    // doesn't throw; validate_batch is what flags the imbalance.
    const journalXlsx = await buildJournalXlsx([
      [
        { trans: 1, date: new Date(Date.UTC(2021, 0, 10)), account: "Checking Account", debit: 100 },
        { account: "Sales Income", credit: 90 },
      ],
    ]);

    const res = await injectMultipart("/api/c/import/journal", companyId, journalXlsx, "journal.xlsx");
    expect(res.statusCode).toBe(200);
    const body = res.json() as { years: Array<Record<string, unknown>> };
    expect(body.years).toHaveLength(1);
    expect(body.years[0]!.errors).toBe(2); // both lines of the unbalanced entry are flagged
    expect(body.years[0]!.status).toBe("failed");
    expect((body.years[0]!.errorGroups as Array<{ error: string }>)[0]!.error).toMatch(/does not balance/);

    // Posting this year must be refused (dirty-year gate).
    const postRes = await inject("POST", "/api/c/import/post", companyId, { years: [2021] });
    expect(postRes.statusCode).toBe(409);
    expect(postRes.json().error).toMatch(/2021/);
    expect(postRes.json().dirtyYears).toEqual([2021]);
  });

  it("422s on a garbage journal file (parse error), with the exact message, nothing written", async () => {
    const companyId = await createCompany("Journal Garbage Co");
    await inject("POST", "/api/c/setup/fresh", companyId, { template: "small-business" });
    const garbage = await buildGarbageJournalXlsx();

    const res = await injectMultipart("/api/c/import/journal", companyId, garbage, "journal.xlsx");
    expect(res.statusCode).toBe(422);
    expect(res.json().error).toMatch(/data line outside any entry/);

    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(0);
  });

  it("404s posting a year with no loaded batch at all", async () => {
    const companyId = await createCompany("Journal Nothing Loaded Co");
    await inject("POST", "/api/c/setup/fresh", companyId, { template: "small-business" });
    const res = await inject("POST", "/api/c/import/post", companyId, { years: [1999] });
    expect(res.statusCode).toBe(404);
  });
});
