import { createHash } from "node:crypto";
import ExcelJS from "exceljs";
import { roundMoney } from "./calculations";

export type MappedImportRow = {
  sheetName: string;
  rowNumber: number;
  date: string | null;
  paidByName: string | null;
  categoryName: string;
  vendor: string | null;
  description: string | null;
  amount: number;
  discount: number;
  finalCost: number;
  notes: string | null;
  receiptUrl: string | null;
  sourceHash: string;
  status: "READY" | "SKIPPED";
  reason: string | null;
};

function normalize(value: unknown) {
  return String(value ?? "")
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "");
}

function text(value: unknown) {
  const normalized = String(cellValue(value) ?? "").trim();
  return normalized.length > 0 ? normalized : null;
}

function amount(value: unknown) {
  value = cellValue(value);
  if (typeof value === "number") return roundMoney(value);
  const parsed = Number(String(value ?? "").replace(/[$,]/g, ""));
  return Number.isFinite(parsed) ? roundMoney(parsed) : 0;
}

function dateFromExcel(value: unknown) {
  value = cellValue(value);
  if (typeof value === "number" && value > 20000) {
    const date = new Date(Math.round((value - 25569) * 86400 * 1000));
    return date.toISOString().slice(0, 10);
  }
  if (value instanceof Date && !Number.isNaN(value.getTime())) {
    return value.toISOString().slice(0, 10);
  }
  const parsed = new Date(String(value ?? ""));
  if (!Number.isNaN(parsed.getTime())) return parsed.toISOString().slice(0, 10);
  return null;
}

function cellValue(value: unknown): unknown {
  if (value && typeof value === "object") {
    if ("text" in value && typeof value.text === "string") return value.text;
    if ("result" in value) return value.result;
    if ("richText" in value && Array.isArray(value.richText)) {
      return value.richText.map((part: { text?: string }) => part.text ?? "").join("");
    }
    if ("hyperlink" in value && "text" in value) return String(value.text ?? value.hyperlink ?? "");
  }
  return value;
}

function hashRow(row: Omit<MappedImportRow, "sourceHash" | "status" | "reason">) {
  return createHash("sha256")
    .update(
      [
        row.sheetName,
        row.rowNumber,
        row.date,
        row.paidByName,
        row.categoryName,
        row.description,
        row.amount,
        row.discount,
        row.finalCost
      ].join("|")
    )
    .digest("hex");
}

function indexFor(headers: unknown[], names: string[]) {
  const normalizedNames = names.map(normalize);
  return headers.findIndex((header) => normalizedNames.includes(normalize(header)));
}

export async function parsePurchasedWorkbook(file: File): Promise<MappedImportRow[]> {
  const buffer = await file.arrayBuffer();
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.load(buffer);
  const rows: MappedImportRow[] = [];

  for (const worksheet of workbook.worksheets) {
    const sheetName = worksheet.name;
    const table: unknown[][] = [];
    worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
      const values = Array.isArray(row.values) ? row.values.slice(1) : [];
      table[rowNumber - 1] = values.map((value) => cellValue(value));
    });
    if (table.length === 0) continue;

    const headerIndex = table.findIndex((row) =>
      (row ?? []).some((cell) => ["date", "name", "category", "amount"].includes(normalize(cell)))
    );
    const headers = table[headerIndex] ?? [];
    const dateIndex = indexFor(headers, ["Date"]);
    const nameIndex = indexFor(headers, ["Name", "Paid By"]);
    const categoryIndex = indexFor(headers, ["Category"]);
    const descriptionIndex = indexFor(headers, ["Description", "Item"]);
    const amountIndex = indexFor(headers, ["Amount"]);
    const discountIndex = indexFor(headers, ["Tax Return", "Discount", "Tax Return / Discount"]);
    const finalCostIndex = indexFor(headers, ["Final Cost"]);
    const noteIndex = indexFor(headers, ["Note", "Notes"]);
    const proofIndex = indexFor(headers, ["Proof Link", "Receipt"]);

    const dataStart = headerIndex >= 0 ? headerIndex + 1 : 0;
    for (let index = dataStart; index < table.length; index += 1) {
      const row = table[index] ?? [];
      const rowNumber = index + 1;
      const firstCell = normalize(row[0]);
      if (firstCell === "total") continue;

      const rawAmount =
        amountIndex >= 0 ? amount(row[amountIndex]) : amount(row[2] ?? row[4] ?? row[1]);
      const discount = discountIndex >= 0 ? amount(row[discountIndex]) : 0;
      const finalCost =
        finalCostIndex >= 0 && row[finalCostIndex] !== null
          ? amount(row[finalCostIndex])
          : roundMoney(rawAmount - discount);
      const description =
        descriptionIndex >= 0 ? text(row[descriptionIndex]) : text(row[1] ?? row[3]);
      const categoryName =
        (categoryIndex >= 0 ? text(row[categoryIndex]) : null) ??
        (sheetName.toLowerCase().includes("renovation") ? "Renovation Expense" : "Other");

      const base = {
        sheetName,
        rowNumber,
        date: dateIndex >= 0 ? dateFromExcel(row[dateIndex]) : null,
        paidByName: nameIndex >= 0 ? text(row[nameIndex]) : null,
        categoryName,
        vendor: categoryName.toLowerCase().includes("home depot") ? "Home Depot" : null,
        description,
        amount: rawAmount,
        discount,
        finalCost,
        notes: noteIndex >= 0 ? text(row[noteIndex]) : null,
        receiptUrl: proofIndex >= 0 ? text(row[proofIndex]) : null
      };
      const sourceHash = hashRow(base);
      const hasMoney = Math.abs(finalCost) > 0 || Math.abs(rawAmount) > 0;
      const hasDescription = Boolean(description || categoryName);

      rows.push({
        ...base,
        sourceHash,
        status: hasMoney && hasDescription ? "READY" : "SKIPPED",
        reason: hasMoney && hasDescription ? null : "No importable amount or description"
      });
    }
  }

  return rows;
}