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

const HEADER_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF1F3A5F" } };
const HEADER_FONT: Partial<ExcelJS.Font> = { bold: true, color: { argb: "FFFFFFFF" }, size: 11, name: "Calibri" };
const ALT_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFF6F8FA" } };
const TOTAL_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFE8F4FD" } };
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 generateLotwiseStockExcel(
  groupedData: any[],
  res: Response,
  filename = "lotwise-stock-report"
): Promise<void> {
  const workbook = new ExcelJS.Workbook();
  const sheet = workbook.addWorksheet("Lotwise Stock", {
    views: [{ state: "frozen", ySplit: 1 }],
  });

  const columns = [
    { header: "ItemName", key: "ItemName", width: 20 },
    { header: "Lot No", key: "LotNo", width: 12 },
    { header: "Arr.Date", key: "ArrDate", width: 15 },
    { header: "AUnits", key: "AUnits", width: 10 },
    { header: "SUnits", key: "SUnits", width: 10 },
    { header: "BUnits", key: "BUnits", width: 10 },
    { header: "AWgts", key: "AWgts", width: 12 },
    { header: "SWgts", key: "SWgts", width: 12 },
    { header: "BWgts", key: "BWgts", width: 12 },
    { header: "Rate", key: "Rate", width: 10 },
    { header: "Sup Name", key: "SupName", width: 25 },
    { header: "LOC", key: "Loc", width: 15 },
  ];
  sheet.columns = columns;

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

  let rowIndex = 2;
  groupedData.forEach((row) => {
    const sheetRow = sheet.addRow(row);
    sheetRow.height = 20;

    sheetRow.eachCell({ includeEmpty: true }, (cell, colNumber) => {
      if (row.isHeader) {
        cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFEAEAEA" } };
        cell.font = { bold: true };
      } else if (row.isTotal) {
        cell.fill = TOTAL_FILL;
        cell.font = { bold: true, color: { argb: "FF800080" } }; // Purple total
      } else {
        if (rowIndex % 2 !== 0) cell.fill = ALT_FILL;
      }
      
      const colKey = columns[colNumber - 1]?.key;
      if (colKey && ["AUnits", "SUnits", "BUnits", "AWgts", "SWgts", "BWgts", "Rate"].includes(colKey) && !row.isHeader) {
        const raw = cell.value;
        cell.value = raw !== null && raw !== undefined && raw !== "" ? Number(raw) : null;
        cell.numFmt = "#,##0.00";
        cell.alignment = { horizontal: "right" };
      } else {
        cell.alignment = { horizontal: "left" };
      }
      
      cell.border = CELL_BORDER;
    });
    rowIndex++;
  });

  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();
}