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

export interface LotWiseSaleExportRow {
  sNo: number;
  lotNo: string;
  vegetableName: string;
  invoiceDate: string;
  invoiceNo: string;
  customerName: string;
  mobileNo: string;
  quantity: number | string;
  soldBy: string;
  bags: number | string;
  rate: number | string;
  amount: number | string;
  supplierName: string;
  paymentStatus: string;
}

const COLUMNS: { header: string; key: keyof LotWiseSaleExportRow; width: number }[] = [
  { header: "S.No", key: "sNo", width: 8 },
  { header: "Lot No", key: "lotNo", width: 16 },
  { header: "Vegetable Name", key: "vegetableName", width: 22 },
  { header: "Sold Date", key: "invoiceDate", width: 14 },
  { header: "Invoice No", key: "invoiceNo", width: 18 },
  { header: "Customer Name", key: "customerName", width: 24 },
  { header: "Mobile No", key: "mobileNo", width: 16 },
  { header: "Quantity (KG)", key: "quantity", width: 16 },
  { header: "Sold By", key: "soldBy", width: 12 },
  { header: "Bags", key: "bags", width: 12 },
  { header: "Selling Rate (INR/KG)", key: "rate", width: 20 },
  { header: "Total Amount (INR)", key: "amount", width: 18 },
  { header: "Supplier", key: "supplierName", width: 22 },
  { header: "Payment Status", key: "paymentStatus", width: 16 },
];

const NUMERIC_KEYS = new Set<keyof LotWiseSaleExportRow>([
  "quantity",
  "bags",
  "rate",
  "amount",
]);

const HEADER_FILL: ExcelJS.Fill = {
  type: "pattern",
  pattern: "solid",
  fgColor: { argb: "FF1A3C5E" }, // deep navy-blue
};

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" }, // light blue tint
};

const ALT_FILL: ExcelJS.Fill = {
  type: "pattern",
  pattern: "solid",
  fgColor: { argb: "FFF6F8FA" }, // very light grey
};

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 function mapLotWiseSaleToExportRow(item: any, index: number): LotWiseSaleExportRow {
  const qty = Number(item.quantity ?? 0);
  const total = Number(item.total ?? 0);
  const effectivePerKg = qty > 0 ? total / qty : Number(item.sellingRate ?? 0);

  let dateStr = "-";
  if (item.sale?.invoiceDate) {
    try {
      dateStr = new Date(item.sale.invoiceDate).toISOString().slice(0, 10);
    } catch {
      dateStr = String(item.sale.invoiceDate);
    }
  }

  const custName = item.sale?.customer?.name || item.sale?.walkInName || "-";
  const mobile = item.sale?.customer?.mobileNo || item.sale?.walkInMobile || "-";
  const vegName = item.purchase?.vegetable?.name || "-";
  const lotNo = item.purchase?.lotNo || "-";
  const supplierName = item.purchase?.supplier?.name || item.purchase?.cashSupplierName || "-";

  return {
    sNo: index + 1,
    lotNo,
    vegetableName: vegName,
    invoiceDate: dateStr,
    invoiceNo: item.sale?.invoiceNo || "-",
    customerName: custName,
    mobileNo: mobile,
    quantity: qty > 0 ? Number(qty.toFixed(2)) : 0,
    soldBy: item.saleUnit || "KG",
    bags: item.bags != null ? Number(Number(item.bags).toFixed(3)) : "-",
    rate: Number(effectivePerKg.toFixed(2)),
    amount: Number(total.toFixed(2)),
    supplierName,
    paymentStatus: item.sale?.paymentStatus || "-",
  };
}

export async function generateLotWiseSaleExcel(
  rows: LotWiseSaleExportRow[],
  res: Response,
  filename = "lot-wise-sales-report",
  filterInfo?: { date?: string | undefined; lotNo?: string | undefined }
): Promise<void> {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = "AVS System";
  workbook.created = new Date();

  const sheet = workbook.addWorksheet("Lot-wise Sales", {
    pageSetup: {
      paperSize: 9, // A4
      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;
  });

  let totalQty = 0;
  let totalBags = 0;
  let totalAmount = 0;

  rows.forEach((row, idx) => {
    const r = sheet.addRow(row);
    r.height = 20;

    const isEven = idx % 2 === 1;
    r.eachCell((cell, colNumber) => {
      const colKey = COLUMNS[colNumber - 1]?.key;
      cell.border = CELL_BORDER;
      cell.font = { name: "Calibri", size: 10 };

      if (isEven) {
        cell.fill = ALT_FILL;
      }

      if (colKey && NUMERIC_KEYS.has(colKey)) {
        cell.alignment = { vertical: "middle", horizontal: "right" };
        if (typeof cell.value === "number") {
          cell.numFmt = NUM_FORMAT;
        }
      } else if (colKey === "sNo" || colKey === "invoiceDate" || colKey === "soldBy" || colKey === "paymentStatus") {
        cell.alignment = { vertical: "middle", horizontal: "center" };
      } else {
        cell.alignment = { vertical: "middle", horizontal: "left" };
      }
    });

    if (typeof row.quantity === "number") totalQty += row.quantity;
    if (typeof row.bags === "number") totalBags += row.bags;
    if (typeof row.amount === "number") totalAmount += row.amount;
  });

  // Summary row
  const summaryRowIndex = sheet.rowCount + 1;
  const summaryRow = sheet.addRow({
    sNo: "" as any,
    lotNo: "",
    vegetableName: "",
    invoiceDate: "",
    invoiceNo: "",
    customerName: "Total",
    mobileNo: "",
    quantity: totalQty,
    soldBy: "",
    bags: totalBags,
    rate: "",
    amount: totalAmount,
    supplierName: "",
    paymentStatus: "",
  });

  summaryRow.height = 24;
  summaryRow.eachCell((cell, colNumber) => {
    const colKey = COLUMNS[colNumber - 1]?.key;
    cell.fill = TOTAL_FILL;
    cell.font = { name: "Calibri", size: 11, bold: true };
    cell.border = CELL_BORDER;

    if (colKey === "customerName") {
      cell.alignment = { vertical: "middle", horizontal: "right" };
    } else if (colKey === "quantity" || colKey === "bags" || colKey === "amount") {
      cell.alignment = { vertical: "middle", horizontal: "right" };
      if (typeof cell.value === "number") {
        cell.numFmt = NUM_FORMAT;
      }
    } else {
      cell.alignment = { vertical: "middle", horizontal: "center" };
    }
  });

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

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