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

export interface SaleExportRow {
  invoiceNo: string;
  invoiceDate: string;
  customer: string;
  subTotal: number | string;
  discount: number | string;
  freightCharge: number | string;
  grandTotal: number | string;
  receivedAmount: number | string;
  balanceAmount: number | string;
  outstandingBalance: number | string;
}

const COLUMNS: { header: string; key: keyof SaleExportRow; width: number }[] = [
  { header: "Invoice No", key: "invoiceNo", width: 18 },
  { header: "Invoice Date", key: "invoiceDate", width: 16 },
  { header: "Customer", key: "customer", width: 24 },
  { header: "Sub Total", key: "subTotal", width: 14 },
  { header: "Discount", key: "discount", width: 12 },
  { header: "Freight Charge", key: "freightCharge", width: 16 },
  { header: "Grand Total", key: "grandTotal", width: 14 },
  { header: "Received Amount", key: "receivedAmount", width: 18 },
  { header: "Balance Amount", key: "balanceAmount", width: 16 },
  { header: "Outstanding Balance", key: "outstandingBalance", width: 20 },
];

const NUMERIC_KEYS = new Set<keyof SaleExportRow>([
  "subTotal",
  "discount",
  "freightCharge",
  "grandTotal",
  "receivedAmount",
  "balanceAmount",
  "outstandingBalance",
]);

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 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,
};

export async function generateSaleExcel(
  rows: SaleExportRow[],
  res: Response,
  filename = "sales-report"
): Promise<void> {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = "AVS System";
  workbook.created = new Date();

  const sheet = workbook.addWorksheet("Sales Report", {
    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", wrapText: false };
    cell.border = CELL_BORDER;
  });

  rows.forEach((rowData, idx) => {
    const row = sheet.addRow(rowData);
    row.height = 20;
    const isAlt = idx % 2 !== 0;
    row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
      const colKey = COLUMNS[colNumber - 1]?.key;
      if (isAlt) cell.fill = ALT_FILL;
      if (colKey && NUMERIC_KEYS.has(colKey)) {
        const raw = cell.value;
        const num = raw !== null && raw !== undefined && raw !== "" ? Number(raw) : null;
        cell.value = num;
        cell.numFmt = NUM_FORMAT;
        cell.alignment = { vertical: "middle", horizontal: "right" };
      } else {
        cell.alignment = { vertical: "middle", horizontal: "left" };
      }
      cell.font = { name: "Calibri", size: 10 };
      cell.border = CELL_BORDER;
    });
  });

  if (rows.length > 0) {
    const dataStartRow = 2;
    const dataEndRow = rows.length + 1;

    const totalRow = sheet.addRow({
      invoiceNo: "TOTAL",
      invoiceDate: "",
      customer: "",
      subTotal: { formula: `SUM(D${dataStartRow}:D${dataEndRow})` } as any,
      discount: { formula: `SUM(E${dataStartRow}:E${dataEndRow})` } as any,
      freightCharge: { formula: `SUM(F${dataStartRow}:F${dataEndRow})` } as any,
      grandTotal: { formula: `SUM(G${dataStartRow}:G${dataEndRow})` } as any,
      receivedAmount: { formula: `SUM(H${dataStartRow}:H${dataEndRow})` } as any,
      balanceAmount: { formula: `SUM(I${dataStartRow}:I${dataEndRow})` } as any,
      outstandingBalance: { formula: `SUM(J${dataStartRow}:J${dataEndRow})` } as any,   // customer-level field — not summed per invoice
    });

    totalRow.height = 24;
    totalRow.eachCell({ includeEmpty: true }, (cell, colNumber) => {
      cell.fill = TOTAL_FILL;
      cell.font = { name: "Calibri", size: 10, bold: true };
      cell.border = CELL_BORDER;
      const colKey = COLUMNS[colNumber - 1]?.key;
      if (colKey && NUMERIC_KEYS.has(colKey)) {
        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();
}

export function mapSaleToExportRow(item: any): SaleExportRow {
  let invoiceDateStr = "";
  if (item.invoiceDate) {
    invoiceDateStr = new Date(item.invoiceDate as string).toISOString().slice(0, 10);
  }
  return {
    invoiceNo: item.invoiceNo ?? "",
    invoiceDate: invoiceDateStr,
    customer: item.customer?.name || (item.walkInName ? `${item.walkInName} (Walk-in)` : "Walk-in Customer"),
    subTotal: Number(item.subTotal ?? 0),
    discount: Number(item.discount ?? 0),
    freightCharge: Number(item.freightCharge ?? 0),
    grandTotal: Number(item.grandTotal ?? 0),
    receivedAmount: Number(item.receivedAmount ?? 0),
    balanceAmount: Number(item.balanceAmount ?? 0),
    outstandingBalance: Number(item.customer?.outstandingBalance ?? 0),
  };
}