import { test, expect, describe } from "bun:test";
import { existsSync } from "node:fs";
import { mkdtemp, rm, writeFile } from "node:fs/promises";
import { tmpdir } from "node:os";
import { join } from "node:path";

import ExcelJS from "exceljs";

import { XlsxWriter, type SheetSpec } from "../../src/xlsx/XlsxWriter.ts";

// LibreOffice headless is the recalculation oracle (formula cells are
// written without cached results). Skip recalc assertions when absent.
const SOFFICE =
  Bun.which("soffice") ??
  (existsSync("/Applications/LibreOffice.app/Contents/MacOS/soffice")
    ? "/Applications/LibreOffice.app/Contents/MacOS/soffice"
    : null);

/** Pricing-style sheet exercising the req-13 open-slot mechanism: the
 *  named range $B$2:$B$5 spans three data rows plus a blank open-slot row,
 *  and the total row sums the *name*, not a cell range. */
function makePricingSpec(): SheetSpec {
  return {
    name: "Pricing",
    rows: [
      [
        { value: "Item", style: "header" },
        { value: "Amount", style: "header" },
      ],
      [{ value: "Install" }, { value: 10, style: "input", numFmt: "#,##0.00" }],
      [
        { value: "Commission", note: "Rate held from 2025 master agreement" },
        { value: 20, style: "input" },
      ],
      [
        { value: "Descoped extra", style: "muted" },
        { value: 30, style: "muted" },
      ],
      [{ value: "(open slot)" }, {}],
      [
        { value: "Total", style: "derived" },
        { formula: "SUM(Line_Amounts)", style: "derived" },
      ],
      [
        { value: "Margin floor", style: "internal" },
        { value: 0.18, style: "internal" },
      ],
    ],
    namedRanges: [{ name: "Line_Amounts", range: "Pricing!$B$2:$B$5" }],
    outlineRows: [{ from: 2, to: 5, level: 1 }],
  };
}

async function loadWorkbook(buffer: Uint8Array): Promise<ExcelJS.Workbook> {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.load(buffer);
  return workbook;
}

function noteText(note: ExcelJS.Cell["note"]): string {
  if (typeof note === "string") return note;
  return (note?.texts ?? []).map((t) => t.text).join("");
}

describe("XlsxWriter.writeSheets — formulas, names, notes, styles", () => {
  test.skipIf(!SOFFICE)(
    "=SUM(named_range) recalculates after a row insertion within the range",
    async () => {
      const buffer = await new XlsxWriter().writeSheets([makePricingSpec()]);

      // Insert a row inside the named range. The static $B$2:$B$5 still
      // covers every data row because the blank open slot absorbs the shift.
      const workbook = await loadWorkbook(buffer);
      const sheet = workbook.getWorksheet("Pricing")!;
      sheet.insertRow(3, ["Inserted line", 15]);

      const dir = await mkdtemp(join(tmpdir(), "levelset-recalc-"));
      try {
        const inPath = join(dir, "pricing.xlsx");
        await writeFile(
          inPath,
          new Uint8Array(await workbook.xlsx.writeBuffer()),
        );

        const proc = Bun.spawn(
          [
            SOFFICE!,
            "--headless",
            "--convert-to",
            "xlsx",
            "--outdir",
            join(dir, "out"),
            inPath,
          ],
          { stdout: "pipe", stderr: "pipe" },
        );
        const exitCode = await proc.exited;
        expect(exitCode).toBe(0);

        const converted = await loadWorkbook(
          new Uint8Array(
            await Bun.file(join(dir, "out", "pricing.xlsx")).arrayBuffer(),
          ),
        );
        // Total row shifted from 6 to 7 by the insertion.
        const total = converted.getWorksheet("Pricing")!.getCell("B7");
        expect(total.formula).toContain("Line_Amounts");
        expect(total.result).toBe(10 + 15 + 20 + 30);
      } finally {
        await rm(dir, { recursive: true, force: true });
      }
    },
    30000,
  );

  test("formula cells survive a write/read round trip without cached results", async () => {
    const buffer = await new XlsxWriter().writeSheets([makePricingSpec()]);
    const workbook = await loadWorkbook(buffer);
    const cell = workbook.getWorksheet("Pricing")!.getCell("B6");
    expect(cell.formula).toBe("SUM(Line_Amounts)");
  });

  test("named ranges land in workbook.definedNames", async () => {
    const buffer = await new XlsxWriter().writeSheets([makePricingSpec()]);
    const workbook = await loadWorkbook(buffer);
    const ranges = workbook.definedNames.getRanges("Line_Amounts");
    expect(ranges.ranges).toEqual(["Pricing!$B$2:$B$5"]);
  });

  test("cell notes survive the round trip", async () => {
    const buffer = await new XlsxWriter().writeSheets([makePricingSpec()]);
    const workbook = await loadWorkbook(buffer);
    const cell = workbook.getWorksheet("Pricing")!.getCell("A3");
    expect(noteText(cell.note)).toBe("Rate held from 2025 master agreement");
  });

  test("muted style renders grey + strikethrough", async () => {
    const buffer = await new XlsxWriter().writeSheets([makePricingSpec()]);
    const workbook = await loadWorkbook(buffer);
    const cell = workbook.getWorksheet("Pricing")!.getCell("A4");
    expect(cell.font?.strike).toBe(true);
    expect(cell.font?.color?.argb).toBe("FF808080");
  });

  test("header/input/derived/internal styles apply fills", async () => {
    const buffer = await new XlsxWriter().writeSheets([makePricingSpec()]);
    const sheet = (await loadWorkbook(buffer)).getWorksheet("Pricing")!;

    const fgArgb = (addr: string) => {
      const fill = sheet.getCell(addr).fill;
      return fill.type === "pattern" ? fill.fgColor?.argb : undefined;
    };
    expect(fgArgb("A1")).toBe("FF4472C4");
    expect(sheet.getCell("A1").font?.bold).toBe(true);
    expect(fgArgb("B2")).toBe("FFFFF2CC");
    expect(fgArgb("B6")).toBe("FFF2F2F2");
    expect(fgArgb("B7")).toBe("FFFCE4D6");
  });

  test("numFmt and outline rows apply", async () => {
    const buffer = await new XlsxWriter().writeSheets([makePricingSpec()]);
    const sheet = (await loadWorkbook(buffer)).getWorksheet("Pricing")!;
    expect(sheet.getCell("B2").numFmt).toBe("#,##0.00");
    expect(sheet.getRow(3).outlineLevel).toBe(1);
    expect(sheet.getRow(6).outlineLevel ?? 0).toBe(0);
  });

  test("writeSheets supports multiple sheets", async () => {
    const buffer = await new XlsxWriter().writeSheets([
      makePricingSpec(),
      { name: "Assumptions", rows: [[{ value: "Assumption" }]] },
    ]);
    const workbook = await loadWorkbook(buffer);
    expect(workbook.getWorksheet("Pricing")).toBeDefined();
    expect(workbook.getWorksheet("Assumptions")).toBeDefined();
  });
});

describe("XlsxWriter.writeWithGanttAndSheets", () => {
  test("spec sheets compose with the schedule + Gantt sheets", async () => {
    const { makeMinimalProject } = await import("../helpers.ts");
    const project = makeMinimalProject();

    const buffer = await new XlsxWriter().writeWithGanttAndSheets(
      project,
      {
        phases: [
          {
            label: "Phase 1",
            items: [
              { name: "Task A", start: "2026-01-05", finish: "2026-01-09" },
            ],
          },
        ],
      },
      [makePricingSpec()],
    );

    const workbook = await loadWorkbook(buffer);
    expect(workbook.getWorksheet("Schedule")).toBeDefined();
    expect(workbook.getWorksheet("Gantt")).toBeDefined();
    const pricing = workbook.getWorksheet("Pricing")!;
    expect(pricing.getCell("B6").formula).toBe("SUM(Line_Amounts)");
    expect(workbook.definedNames.getRanges("Line_Amounts").ranges).toEqual([
      "Pricing!$B$2:$B$5",
    ]);
  });
});