// QB Type -> subtype mapping (tools/chart-logic.ts) and the pure planning
// half of tools/backfill-subtypes-from-qb.ts — Task 1 of
// docs/superpowers/plans/2026-08-02-chart-management.md.
//
// The fixture workbook below is entirely FICTIONAL (a made-up "Blue Heron
// Bakery" chart) — no real company data ever enters the test suite (the
// release packaging's forbidden-string gate depends on that staying true).

import { describe, it, expect } from "vitest";
import ExcelJS from "exceljs";
import { mkdtemp, rm } from "node:fs/promises";
import { tmpdir } from "node:os";
import path from "node:path";
import {
  QB_TYPE_SUBTYPE,
  QB_TYPE_LEDGER_TYPE,
  qbTypeToSubtype,
  planAccounts,
  parseListingRows,
  type ListingRow,
} from "../../tools/chart-logic.js";
import { readQbSheet, TIPS_SHEET_NAME } from "../../tools/qb-xlsx.js";
import { planSubtypeTags, applyTags } from "../../tools/backfill-subtypes-from-qb.js";
import { createTestDb } from "../helpers/db.js";

// The full mapping table from the spec (Part 1: "QB 'Credit Card' ->
// credit-card etc."). QB's fifteen account types, one-to-one; QB has no
// line-of-credit type, so that subtype is deliberately absent here.
const EXPECTED_SUBTYPE_MAP: Record<string, string> = {
  Bank: "bank",
  "Accounts Receivable": "accounts-receivable",
  "Other Current Asset": "other-current-asset",
  "Fixed Asset": "fixed-asset",
  "Other Asset": "other-asset",
  "Accounts Payable": "accounts-payable",
  "Credit Card": "credit-card",
  "Other Current Liability": "other-current-liability",
  "Long Term Liability": "long-term-liability",
  Equity: "equity",
  Income: "income",
  "Other Income": "other-income",
  "Cost of Goods Sold": "cost-of-goods-sold",
  Expense: "expense",
  "Other Expense": "other-expense",
};

describe("QuickBooks type -> subtype mapping", () => {
  for (const [qbType, subtype] of Object.entries(EXPECTED_SUBTYPE_MAP)) {
    it(`maps "${qbType}" -> ${subtype}`, () => {
      expect(qbTypeToSubtype(qbType)).toBe(subtype);
    });
  }

  it("has no extra mappings beyond QB's fifteen types", () => {
    expect(Object.keys(QB_TYPE_SUBTYPE).sort()).toEqual(
      Object.keys(EXPECTED_SUBTYPE_MAP).sort(),
    );
  });

  it("covers exactly the same QB types as the ledger-type map (no drift)", () => {
    expect(Object.keys(QB_TYPE_SUBTYPE).sort()).toEqual(
      Object.keys(QB_TYPE_LEDGER_TYPE).sort(),
    );
  });

  it("returns null for an unknown QB type instead of guessing", () => {
    expect(qbTypeToSubtype("Some Made Up Type")).toBeNull();
    expect(qbTypeToSubtype("")).toBeNull();
  });
});

describe("planAccounts carries the subtype through the chart loader", () => {
  it("tags every planned account from its QB type; unknown types never reach the plan", () => {
    const rows: ListingRow[] = [
      { name: "Bakery Checking", qbType: "Bank" },
      { name: "Bakery Visa", qbType: "Credit Card" },
      { name: "Flour and Sugar", qbType: "Cost of Goods Sold" },
    ];
    const plan = planAccounts(rows);
    expect(plan.accounts.map((a) => [a.name, a.type, a.subtype])).toEqual([
      ["Bakery Checking", "asset", "bank"],
      ["Bakery Visa", "liability", "credit-card"],
      ["Flour and Sugar", "expense", "cost-of-goods-sold"],
    ]);
  });
});

describe("planSubtypeTags against a fictional fixture xlsx", () => {
  it("maps every mappable row and reports unknown types, straight from the workbook", async () => {
    const dir = await mkdtemp(path.join(tmpdir(), "backfill-subtypes-test-"));
    const file = path.join(dir, "blue-heron-bakery-listing.xlsx");
    try {
      const workbook = new ExcelJS.Workbook();
      const tips = workbook.addWorksheet(TIPS_SHEET_NAME);
      tips.getCell("A1").value = "Ignore me entirely";

      // Same layout as a real QB Account Listing export: header row 1,
      // column 3 = Account, column 5 = Type.
      const sheet = workbook.addWorksheet("Sheet1");
      sheet.getCell(1, 3).value = "Account";
      sheet.getCell(1, 5).value = "Type";
      const fixtureRows: Array<[string, string]> = [
        ["Blue Heron Checking", "Bank"],
        ["Blue Heron Savings", "Bank"],
        ["Bakery Receivables", "Accounts Receivable"],
        ["Ingredient Inventory", "Other Current Asset"],
        ["Ovens and Mixers", "Fixed Asset"],
        ["Recipe Rights", "Other Asset"],
        ["Bakery Payables", "Accounts Payable"],
        ["Bakery Visa", "Credit Card"],
        ["Gift Cards Owed", "Other Current Liability"],
        ["Oven Loan", "Long Term Liability"],
        ["Owner Capital", "Equity"],
        ["Pastry Sales", "Income"],
        ["Vendor Rebates", "Other Income"],
        ["Flour and Sugar", "Cost of Goods Sold"],
        ["Shop Rent", "Expense"],
        ["Storm Damage", "Other Expense"],
        ["Mystery Row", "Nonsense Type"],
      ];
      fixtureRows.forEach(([name, qbType], i) => {
        sheet.getCell(i + 2, 3).value = name;
        sheet.getCell(i + 2, 5).value = qbType;
      });
      await workbook.xlsx.writeFile(file);

      const listingRows = parseListingRows(await readQbSheet(file));
      expect(listingRows).toHaveLength(17);

      const plan = planSubtypeTags(listingRows);
      expect(plan.unknownTypes).toEqual([
        { name: "Mystery Row", qbType: "Nonsense Type" },
      ]);
      expect(plan.tags).toHaveLength(16);
      const bySubtype = new Map(plan.tags.map((t) => [t.name, t.subtype]));
      expect(bySubtype.get("Blue Heron Checking")).toBe("bank");
      expect(bySubtype.get("Blue Heron Savings")).toBe("bank");
      expect(bySubtype.get("Bakery Receivables")).toBe("accounts-receivable");
      expect(bySubtype.get("Ingredient Inventory")).toBe("other-current-asset");
      expect(bySubtype.get("Ovens and Mixers")).toBe("fixed-asset");
      expect(bySubtype.get("Recipe Rights")).toBe("other-asset");
      expect(bySubtype.get("Bakery Payables")).toBe("accounts-payable");
      expect(bySubtype.get("Bakery Visa")).toBe("credit-card");
      expect(bySubtype.get("Gift Cards Owed")).toBe("other-current-liability");
      expect(bySubtype.get("Oven Loan")).toBe("long-term-liability");
      expect(bySubtype.get("Owner Capital")).toBe("equity");
      expect(bySubtype.get("Pastry Sales")).toBe("income");
      expect(bySubtype.get("Vendor Rebates")).toBe("other-income");
      expect(bySubtype.get("Flour and Sugar")).toBe("cost-of-goods-sold");
      expect(bySubtype.get("Shop Rent")).toBe("expense");
      expect(bySubtype.get("Storm Damage")).toBe("other-expense");
      // The mapped subtype is always paired with its source row's QB type,
      // so the CLI's report can show both.
      for (const tag of plan.tags) {
        expect(EXPECTED_SUBTYPE_MAP[tag.qbType]).toBe(tag.subtype);
      }
    } finally {
      await rm(dir, { recursive: true, force: true });
    }
  });
});

// ---------------------------------------------------------------------------
// applyTags against a throwaway DB — the same fictional Blue Heron Bakery
// chart, seeded as real rows. The caller owns the transaction (that is how
// run() implements --dry-run), so the dry-run test here IS the real
// mechanism: BEGIN, applyTags, ROLLBACK.
// ---------------------------------------------------------------------------

describe("applyTags (DB half)", () => {
  it("matches by name and by alias, skips tagged rows, reports unmatched, updates subtype only, and rolls back under dry-run", async () => {
    const db = await createTestDb();
    try {
      const companyId = db.companyA;

      async function seedAccount(
        code: string, name: string, type: string, subtype: string | null,
      ): Promise<string> {
        const r = await db.admin.query<{ id: string }>(
          `INSERT INTO accounts (company_id, code, name, type, subtype)
           VALUES ($1, $2, $3, $4, $5) RETURNING id`,
          [companyId, code, name, type, subtype]);
        return r.rows[0]!.id;
      }

      const checkingId = await seedAccount("9500", "Blue Heron Checking", "asset", null);
      // Alias case: the account carries its full QB path; chart:load created
      // a leaf alias for it, and the listing row names the leaf.
      const receivablesId = await seedAccount(
        "9510", "Assets:Bakery Receivables", "asset", null);
      await db.admin.query(
        `INSERT INTO account_aliases (company_id, alias, account_id)
         VALUES ($1, $2, $3)`,
        [companyId, "Bakery Receivables", receivablesId]);
      // Already manually tagged — the script must never overwrite it.
      const inventoryId = await seedAccount(
        "9520", "Ingredient Inventory", "asset", "fixed-asset");

      const listing: ListingRow[] = [
        { name: "Blue Heron Checking", qbType: "Bank" },
        { name: "Bakery Receivables", qbType: "Accounts Receivable" },
        { name: "Ingredient Inventory", qbType: "Other Current Asset" },
        { name: "Missing Account", qbType: "Expense" },
      ];
      const { tags } = planSubtypeTags(listing);
      expect(tags).toHaveLength(4);

      const before = await db.admin.query(
        `SELECT id, code, name, type, active FROM accounts
          WHERE company_id = $1 AND code >= '9500' ORDER BY code`, [companyId]);

      const result = await applyTags(db.admin, companyId, tags);

      expect(result.updatedTotal).toBe(2);
      expect(Object.fromEntries(result.updatedBySubtype)).toEqual({
        "bank": 1, "accounts-receivable": 1,
      });
      expect(result.alreadyTagged).toBe(1);
      expect(result.unmatched).toEqual([
        { name: "Missing Account", qbType: "Expense", subtype: "expense" },
      ]);

      const after = await db.admin.query<{ id: string; subtype: string | null }>(
        `SELECT id, code, name, type, active, subtype FROM accounts
          WHERE company_id = $1 AND code >= '9500' ORDER BY code`, [companyId]);
      const subtypeById = new Map(after.rows.map((r) => [r.id, r.subtype]));
      expect(subtypeById.get(checkingId)).toBe("bank");            // by name
      expect(subtypeById.get(receivablesId)).toBe("accounts-receivable"); // by alias
      expect(subtypeById.get(inventoryId)).toBe("fixed-asset");    // untouched
      // Subtype is the ONLY column that changed.
      expect(after.rows.map(({ subtype: _s, ...rest }) => rest)).toEqual(before.rows);

      // Dry-run mechanism: caller-owned transaction, rolled back.
      const dryRunId = await seedAccount("9530", "Blue Heron Savings", "asset", null);
      await db.admin.query("BEGIN");
      const dry = await applyTags(db.admin, companyId,
        planSubtypeTags([{ name: "Blue Heron Savings", qbType: "Bank" }]).tags);
      expect(dry.updatedTotal).toBe(1);
      await db.admin.query("ROLLBACK");
      const untouched = await db.admin.query<{ subtype: string | null }>(
        "SELECT subtype FROM accounts WHERE id = $1", [dryRunId]);
      expect(untouched.rows[0]!.subtype).toBeNull();
    } finally {
      await db.close();
    }
  }, 60_000);
});
