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

export interface CashBankExportRow {
  date: string | null;
  voucherNo: string;
  transactionType: string;
  account: string;
  paymentMode: string;
  party: string;
  description: string;
  varavu: number;
  patru: number;
  balance: number;
}

export interface CashBankExportSummary {
  openingBalance: number;
  totalVaravu: number;
  totalPatru: number;
  closingBalance: number;
  currentCashInHand?: number;
  currentCashInBank?: number;
  totalCombined?: number;
}

const COLUMNS: { header: string; key: keyof CashBankExportRow; width: number }[] = [
  { header: "Date", key: "date", width: 14 },
  { header: "Voucher / Ref No", key: "voucherNo", width: 18 },
  { header: "Transaction Type", key: "transactionType", width: 22 },
  { header: "Account", key: "account", width: 16 },
  { header: "Payment Mode", key: "paymentMode", width: 14 },
  { header: "Party / Description", key: "party", width: 26 },
  { header: "Remarks", key: "description", width: 28 },
  { header: "Credit (₹)", key: "varavu", width: 18 },
  { header: "Debit (₹)", key: "patru", width: 18 },
  { header: "Balance (₹)", key: "balance", width: 18 },
];

const HEADER_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF1B5E20" } };
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: "FFE8F5E9" } };
const ALT_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFF9FBE7" } };
const VARAVU_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFE8F8F5" } };
const PATRU_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFFDF2E9" } };
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: CashBankExportRow, idx: number): ExcelJS.Fill {
  if (row.varavu > 0 && row.patru === 0) return VARAVU_FILL;
  if (row.patru > 0 && row.varavu === 0) return PATRU_FILL;
  return idx % 2 !== 0 ? ALT_FILL : WHITE_FILL;
}

export async function generateCashBankExcel(
  rows: CashBankExportRow[],
  summary: CashBankExportSummary,
  res: Response,
  filters?: {
    account?: string | undefined;
    type?: string | undefined;
    dateFrom?: string | undefined;
    dateTo?: string | undefined;
    search?: string | undefined;
  }
) {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = "AVS Management";
  workbook.created = new Date();

  const sheetTitle =
    filters?.account === "CASH"
      ? "Cash Report"
      : filters?.account === "BANK"
      ? "Bank Report"
      : "Cash & Bank Ledger";

  const sheet = workbook.addWorksheet(sheetTitle, {
    views: [{ showGridLines: true }],
  });

  // Title Row
  sheet.mergeCells("A1:J1");
  const titleCell = sheet.getCell("A1");
  titleCell.value =
    filters?.account === "CASH"
      ? "AVS ENTERPRISES - CASH REPORT"
      : filters?.account === "BANK"
      ? "AVS ENTERPRISES - BANK REPORT"
      : "AVS ENTERPRISES - CASH & BANK LEDGER";
  titleCell.font = { bold: true, size: 14, color: { argb: "FF1B5E20" } };
  titleCell.alignment = { horizontal: "center", vertical: "middle" };
  sheet.getRow(1).height = 30;

  // Filter Details Row
  const filterParts: string[] = [];
  if (filters?.account) filterParts.push(`Account: ${filters.account}`);
  if (filters?.type) filterParts.push(`Type: ${filters.type}`);
  if (filters?.dateFrom || filters?.dateTo) {
    filterParts.push(`Date Range: ${filters.dateFrom ?? "Start"} to ${filters.dateTo ?? "Today"}`);
  }
  const filterText = filterParts.length > 0 ? filterParts.join(" | ") : "All Transactions";

  sheet.mergeCells("A2:J2");
  const subtitleCell = sheet.getCell("A2");
  subtitleCell.value = filterText;
  subtitleCell.font = { italic: true, size: 10, color: { argb: "FF555555" } };
  subtitleCell.alignment = { horizontal: "center", vertical: "middle" };
  sheet.getRow(2).height = 20;

  sheet.addRow([]); // Row 3 empty

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

  // Header Row (Row 4)
  const headerRow = sheet.getRow(4);
  COLUMNS.forEach((col, idx) => {
    const cell = headerRow.getCell(idx + 1);
    cell.value = col.header;
    cell.fill = HEADER_FILL;
    cell.font = HEADER_FONT;
    cell.alignment = {
      horizontal: ["varavu", "patru", "balance"].includes(col.key) ? "right" : "left",
      vertical: "middle",
    };
    cell.border = CELL_BORDER;
  });
  headerRow.height = 26;

  // Data Rows
  rows.forEach((row, idx) => {
    const dataRow = sheet.addRow(row);
    dataRow.height = 20;
    const fill = getFillForRow(row, idx);

    COLUMNS.forEach((col, cIdx) => {
      const cell = dataRow.getCell(cIdx + 1);
      cell.fill = fill;
      cell.border = CELL_BORDER;
      cell.font = { size: 10, name: "Calibri" };

      if (["varavu", "patru", "balance"].includes(col.key)) {
        cell.numFmt = NUM_FORMAT;
        cell.alignment = { horizontal: "right", vertical: "middle" };
        if (col.key === "varavu" && row.varavu > 0) {
          cell.font = { size: 10, name: "Calibri", bold: true, color: { argb: "FF2E7D32" } };
        } else if (col.key === "patru" && row.patru > 0) {
          cell.font = { size: 10, name: "Calibri", bold: true, color: { argb: "FFC62828" } };
        } else if (col.key === "balance") {
          cell.font = { size: 10, name: "Calibri", bold: true, color: { argb: "FF1565C0" } };
        }
      } else {
        cell.alignment = { horizontal: "left", vertical: "middle" };
      }
    });
  });

  // Summary Row
  const totalRow = sheet.addRow({
    date: "",
    voucherNo: "",
    transactionType: "",
    account: "",
    paymentMode: "",
    party: "TOTALS",
    description: `Opening: ₹${summary.openingBalance.toLocaleString("en-IN", { minimumFractionDigits: 2 })}`,
    varavu: summary.totalVaravu,
    patru: summary.totalPatru,
    balance: summary.closingBalance,
  });
  totalRow.height = 24;

  COLUMNS.forEach((col, cIdx) => {
    const cell = totalRow.getCell(cIdx + 1);
    cell.fill = TOTAL_FILL;
    cell.font = { bold: true, size: 11, color: { argb: "FF1B5E20" } };
    cell.border = CELL_BORDER;
    if (["varavu", "patru", "balance"].includes(col.key)) {
      cell.numFmt = NUM_FORMAT;
      cell.alignment = { horizontal: "right", vertical: "middle" };
    } else {
      cell.alignment = { horizontal: "left", vertical: "middle" };
    }
  });

  res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  res.setHeader("Content-Disposition", `attachment; filename="Cash_Bank_Ledger_${Date.now()}.xlsx"`);
  res.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

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