import ExcelJS from "exceljs";
import type { Response } from "express";
import type { OutstandingParty, OutstandingSummary } from "../../services/OutstandingReportService.js";

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 ALT_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFF6F8FA" } };
const WHITE_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFFFFFFF" } };
const PENDING_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFFFF0F2" } }; // Light red for pending

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

const NUM_FORMAT = "#,##0.00";

const COLUMNS = [
  { header: "S.No", key: "sno", width: 8 },
  { header: "Name", key: "name", width: 30 },
  { header: "Tamil Name", key: "tamilName", width: 25 },
  { header: "Mobile No", key: "mobileNo", width: 15 },
  { header: "Address", key: "address", width: 35 },
  // { header: "Opening Balance", key: "openingBalance", width: 18 },
  { header: "Outstanding Balance", key: "outstandingBalance", width: 20 },
  // { header: "Status", key: "status", width: 12 },
];

function populateSheet(sheet: ExcelJS.Worksheet, items: OutstandingParty[], summary: OutstandingSummary) {
  sheet.columns = COLUMNS;

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

  // Data rows
  items.forEach((item, idx) => {
    const row = sheet.addRow({
      sno: idx + 1,
      name: item.name,
      tamilName: item.tamilName ?? "",
      mobileNo: item.mobileNo,
      address: item.address ?? "",
      // openingBalance: item.openingBalance,
      outstandingBalance: item.outstandingBalance,
      // status: item.status ? "Active" : "Inactive",
    });

    row.height = 20;

    const fill = item.outstandingBalance > 0 ? PENDING_FILL : (idx % 2 !== 0 ? ALT_FILL : WHITE_FILL);

    row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
      cell.fill = fill;
      cell.border = CELL_BORDER;
      cell.font = { name: "Calibri", size: 10 };
      cell.alignment = { vertical: "middle", horizontal: colNumber >= 6 ? "right" : "left" };

      if (colNumber >= 6) {
        cell.numFmt = NUM_FORMAT;
      }
    });
  });

  // Summary Row at the end
  const summaryRow = sheet.addRow({
    name: "Total Outstanding Amount:",
    outstandingBalance: summary.totalOutstandingAmount
  });
  summaryRow.height = 24;
  summaryRow.eachCell({ includeEmpty: true }, (cell, colNumber) => {
    cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFE8F4FD" } };
    cell.border = CELL_BORDER;
    cell.font = { name: "Calibri", size: 11, bold: true };
    cell.alignment = { vertical: "middle", horizontal: colNumber === 6 ? "right" : "left" };
    if (colNumber === 6) cell.numFmt = NUM_FORMAT;
  });

  sheet.mergeCells(`B${summaryRow.number}:E${summaryRow.number}`);
}

export class OutstandingReportExcel {
  static async export(payload: any, type: "customers" | "suppliers", res: Response) {
    const workbook = new ExcelJS.Workbook();
    workbook.creator = "AVS System";
    workbook.created = new Date();

    const title = type === "customers" ? "Customer Outstanding" : "Supplier Outstanding";

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

    populateSheet(sheet, payload.data, payload.summary);

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

    const safeFilename = encodeURIComponent(`${type}-outstanding-report`);
    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();
  }
}