import ExcelJS from "exceljs";
import type { Response } from "express";

export interface SupplierLedgerRow {
  date: string | null;
  voucherNo: string;
  supplierName: string;
  transactionType: string;
  description: string;
  debit: number | string;
  credit: number | string;
  runningBalance: number | string;
}

export interface SupplierLedgerSummary {
  openingBalance: number;
  totalDebit: number;
  totalCredit: number;
  closingBalance: number;
}

const COLUMNS: { header: string; key: keyof SupplierLedgerRow; width: number }[] = [
  { header: "Date",             key: "date",            width: 14 },
  { header: "Voucher No",       key: "voucherNo",       width: 18 },
  { header: "Supplier Name",    key: "supplierName",    width: 24 },
  { header: "Transaction Type", key: "transactionType", width: 18 },
  { header: "Description",      key: "description",     width: 28 },
  { header: "Debit",            key: "debit",           width: 16 },
  { header: "Credit",           key: "credit",          width: 16 },
  { header: "Balance",          key: "runningBalance",  width: 16 },
];

const NUMERIC_KEYS = new Set<keyof SupplierLedgerRow>(["debit", "credit", "runningBalance"]);

const HEADER_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF1A3C5E" } };
const HEADER_FONT: Partial<ExcelJS.Font> = { bold: true, color: { argb: "FFFFFFFF" }, size: 11, name: "Calibri" };
const TOTAL_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFE8F4FD" } };
const ALT_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFF6F8FA" } };
const DEBIT_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFFFF3E0" } };
const CREDIT_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFE8F5E9" } };
const OPENING_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFEEF2FF" } };
const WHITE_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFFFFFFF" } };

const NUM_FORMAT = "#,##0.00";
const BORDER_STYLE: Partial<ExcelJS.Border> = { style: "thin", color: { argb: "FFD1D5DB" } };
const CELL_BORDER: Partial<ExcelJS.Borders> = {
  top: BORDER_STYLE, left: BORDER_STYLE, bottom: BORDER_STYLE, right: BORDER_STYLE,
};

function getFillForRow(row: SupplierLedgerRow, idx: number): ExcelJS.Fill {
  if (row.transactionType === "Opening Balance") return OPENING_FILL;
  if (Number(row.debit) > 0 && Number(row.credit) === 0) return DEBIT_FILL;
  if (Number(row.credit) > 0 && Number(row.debit) === 0) return CREDIT_FILL;
  return idx % 2 !== 0 ? ALT_FILL : WHITE_FILL;
}

export async function generateSupplierLedgerExcel(
  rows: SupplierLedgerRow[],
  summary: SupplierLedgerSummary,
  res: Response,
  filename = "supplier-ledger"
): Promise<void> {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = "AVS System";
  workbook.created = new Date();

  const sheet = workbook.addWorksheet("Supplier Ledger", {
    pageSetup: { paperSize: 9, orientation: "landscape", fitToPage: true, fitToWidth: 1 },
    views: [{ state: "frozen", ySplit: 1 }],
  });

  sheet.columns = COLUMNS.map((col) => ({ header: col.header, key: col.key, width: col.width }));

  const headerRow = sheet.getRow(1);
  headerRow.height = 28;
  headerRow.eachCell((cell) => {
    cell.fill = HEADER_FILL;
    cell.font = HEADER_FONT;
    cell.alignment = { vertical: "middle", horizontal: "center" };
    cell.border = CELL_BORDER;
  });

  rows.forEach((rowData, idx) => {
    const row = sheet.addRow({
      date: rowData.date ?? "",
      voucherNo: rowData.voucherNo,
      supplierName: rowData.supplierName,
      transactionType: rowData.transactionType,
      description: rowData.description,
      debit: rowData.debit,
      credit: rowData.credit,
      runningBalance: rowData.runningBalance,
    });
    row.height = 20;
    const fill = getFillForRow(rowData, idx);

    row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
      const colKey = COLUMNS[colNumber - 1]?.key;
      cell.fill = fill;
      cell.border = CELL_BORDER;
      cell.font = { name: "Calibri", size: 10 };

      if (colKey && NUMERIC_KEYS.has(colKey)) {
        const val = cell.value;
        cell.value = val !== null && val !== "" ? Number(val) : null;
        cell.numFmt = NUM_FORMAT;
        cell.alignment = { vertical: "middle", horizontal: "right" };
      } else {
        cell.alignment = { vertical: "middle", horizontal: "left" };
      }
    });
  });

  // Summary section
  sheet.addRow({});

  const summaryData = [
    { label: "Opening Balance", value: summary.openingBalance },
    { label: "Total Debit",     value: summary.totalDebit },
    { label: "Total Credit",    value: summary.totalCredit },
    { label: "Closing Balance", value: summary.closingBalance },
  ];

  for (const s of summaryData) {
    const sRow = sheet.addRow({ voucherNo: s.label, runningBalance: s.value });
    sRow.height = 22;
    sRow.eachCell({ includeEmpty: true }, (cell, colNumber) => {
      cell.fill = TOTAL_FILL;
      cell.border = CELL_BORDER;
      cell.font = { name: "Calibri", size: 10, bold: true };
      const colKey = COLUMNS[colNumber - 1]?.key;
      if (colKey === "runningBalance") {
        cell.numFmt = NUM_FORMAT;
        cell.alignment = { vertical: "middle", horizontal: "right" };
      } else {
        cell.alignment = { vertical: "middle", horizontal: "left" };
      }
    });
  }

  sheet.autoFilter = { from: { row: 1, column: 1 }, to: { row: 1, column: COLUMNS.length } };

  const safeFilename = encodeURIComponent(filename);
  res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  res.setHeader(
    "Content-Disposition",
    `attachment; filename="${safeFilename}.xlsx"; filename*=UTF-8''${safeFilename}.xlsx`
  );
  res.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

  await workbook.xlsx.write(res);
  res.end();
}