import { readFileSync } from "node:fs";
import { describe, expect, test } from "vitest";
import { analyseImport, extractImportRows } from "@/lib/import/rows";
import { parseCsv, parseSpreadsheet, parseXlsx, SpreadsheetError } from "@/server/import/spreadsheet";

const fixture = () => readFileSync(new URL("../fixtures/import-sample.xlsx", import.meta.url));

describe("parseCsv", () => {
  test("quoted fields, escaped quotes, CRLF and a BOM", () => {
    const csv = 'Project Code,Project Name,API Notifier URL\r\n1118,"2ndLine, ""main""",https://a.example/health\r\n1103,Bill Reminder,https://b.example/health\n';
    expect(parseCsv(csv)).toEqual([
      ["Project Code", "Project Name", "API Notifier URL"],
      ["1118", '2ndLine, "main"', "https://a.example/health"],
      ["1103", "Bill Reminder", "https://b.example/health"],
    ]);
  });
  test("tab- and semicolon-separated exports are detected from the header", () => {
    expect(parseCsv("Code\tName\tURL\n1\tA\thttps://x")).toEqual([["Code", "Name", "URL"], ["1", "A", "https://x"]]);
    expect(parseCsv("Code;Name;URL\n1;A;https://x")).toEqual([["Code", "Name", "URL"], ["1", "A", "https://x"]]);
  });
});

describe("parseXlsx", () => {
  test("reads the first worksheet in workbook order with shared, rich, inline, numeric and formula cells", () => {
    const grid = parseXlsx(fixture());
    expect(grid[0]).toEqual(["BTS Apps — production list"]);
    expect(grid[1]).toEqual([]);
    expect(grid[2]).toEqual(["Project Code", "Project Name", "API Notifier URL"]);
    expect(grid[3]).toEqual(["1118", "2ndLine", "https://api.2ndline.example/health?x=1&y=2"]);
    expect(grid[4]).toEqual(["1103", "Bill Reminder ", "https://bills.example.com/health"]);
    expect(grid[5]).toEqual(["REV-1", "Revere & Co", "https://rev.example/health"]);
    expect(grid[6]).toEqual(["", ""]);
    expect(grid[7]).toEqual(["1118", "2ndLine duplicate", "https://dup.example/health"]);
  });
  test("rejects files that are not workbooks", () => {
    expect(() => parseXlsx(Buffer.from("PK\u0003\u0004 not really a zip"))).toThrow(SpreadsheetError);
    expect(() => parseSpreadsheet("list.xls", Buffer.from("x"))).toThrow(/save the sheet as \.xlsx or \.csv/);
    expect(() => parseSpreadsheet("list.pdf", Buffer.from("%PDF"))).toThrow(/Unsupported file type/);
  });
  test("parseSpreadsheet picks the parser from the extension, or the zip signature", () => {
    expect(parseSpreadsheet("Apps.XLSX", fixture())[2]).toEqual(["Project Code", "Project Name", "API Notifier URL"]);
    expect(parseSpreadsheet("apps-export", fixture())[3][0]).toBe("1118");
    expect(parseSpreadsheet("apps.csv", Buffer.from("a,b\n1,2"))).toEqual([["a", "b"], ["1", "2"]]);
  });
});

describe("extractImportRows", () => {
  test("finds the header below title rows, maps columns by name and skips empty rows", () => {
    const result = extractImportRows(parseXlsx(fixture()));
    expect(result.ok).toBe(true);
    if (!result.ok) return;
    expect(result.rows.map((r) => [r.line, r.projectCode, r.projectName, r.productionUrl])).toEqual([
      [4, "1118", "2ndLine", "https://api.2ndline.example/health?x=1&y=2"],
      [5, "1103", "Bill Reminder", "https://bills.example.com/health"],
      [6, "REV-1", "Revere & Co", "https://rev.example/health"],
      [8, "1118", "2ndLine duplicate", "https://dup.example/health"],
    ]);
  });
  test("column order does not matter and header names are matched loosely", () => {
    const result = extractImportRows([
      ["api_notifier_url", "Name", "Code", "Owner"],
      ["https://x.example/h", "X", "X-1", "someone"],
    ]);
    expect(result).toEqual({ ok: true, rows: [{ line: 2, projectCode: "X-1", projectName: "X", productionUrl: "https://x.example/h" }] });
  });
  test("a missing column or no data rows is reported", () => {
    expect(extractImportRows([["Project Code", "Project Name"], ["1", "A"]])).toMatchObject({ ok: false, error: expect.stringContaining("header row") });
    expect(extractImportRows([["Project Code", "Project Name", "API Notifier URL"]])).toMatchObject({ ok: false, error: expect.stringContaining("no data rows") });
    expect(extractImportRows([])).toMatchObject({ ok: false });
  });
});

describe("analyseImport", () => {
  const existing = [
    { id: "p1", projectCode: "1118", projectName: "2ndLine", productionUrl: "https://api.2ndline.example/health?x=1&y=2" },
    { id: "p2", projectCode: "rev-1", projectName: "Revere", productionUrl: "https://rev.example/health" },
  ];

  test("classifies rows as new, existing (changed or not), duplicate or invalid", () => {
    const rows = [
      { line: 4, projectCode: "1118", projectName: "2ndLine", productionUrl: "https://api.2ndline.example/health?x=1&y=2" },
      { line: 5, projectCode: "1103", projectName: "Bill Reminder", productionUrl: "https://bills.example.com/health" },
      { line: 6, projectCode: "REV-1", projectName: "Revere & Co", productionUrl: "https://rev.example/health" },
      { line: 7, projectCode: "bad code!", projectName: "Oops", productionUrl: "https://ok.example/h" },
      { line: 8, projectCode: "1118", projectName: "2ndLine duplicate", productionUrl: "https://dup.example/health" },
      { line: 9, projectCode: "2010", projectName: "Text RL", productionUrl: "not a url" },
      { line: 10, projectCode: "4000", projectName: "No notifier yet", productionUrl: "" },
      { line: 11, projectCode: "", projectName: "Name only", productionUrl: "" },
    ];
    const preview = analyseImport(rows, existing);
    expect(preview.rows.map((r) => [r.line, r.kind])).toEqual([
      [4, "existing"],
      [5, "new"],
      [6, "existing"],
      [7, "invalid"],
      [8, "duplicate"],
      [9, "invalid"],
      [10, "invalid"],
      [11, "invalid"],
    ]);
    expect(preview.rows[0].existing).toMatchObject({ id: "p1", changed: false });
    expect(preview.rows[2].existing).toMatchObject({ id: "p2", changed: true, projectName: "Revere" });
    expect(preview.rows[3].error).toMatch(/letters, numbers/);
    expect(preview.rows[4].error).toBe("Same Project Code as row 4");
    expect(preview.rows[5].error).toMatch(/valid http/);
    expect(preview.rows[6].error).toBe("API Notifier URL is required");
    expect(preview.counts).toEqual({ new: 1, existing: 2, changed: 1, duplicate: 1, invalid: 4 });
  });

  test("codes are compared case-insensitively, within the file too", () => {
    const preview = analyseImport(
      [
        { line: 2, projectCode: "abc", projectName: "A", productionUrl: "https://a.example/h" },
        { line: 3, projectCode: "ABC", projectName: "B", productionUrl: "https://b.example/h" },
      ],
      [],
    );
    expect(preview.rows.map((r) => r.kind)).toEqual(["new", "duplicate"]);
  });
});