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

export interface ArrivalReportExportRow {
  arrivalDate: string;
  lotNo: string;
  billNo: string;
  truckNo: string;
  supplier: string;
  vegetable: string;
  quantityKg: number | string;
  bags: number | string;
  remainingQtyKg: number | string;
  remainingBags: number | string;
  freight: number | string;
  advanceAmount: number | string;
  settlementStatus: string;
}

const COLUMNS: { header: string; key: keyof ArrivalReportExportRow; width: number }[] = [
  { header: "Arrival Date",        key: "arrivalDate",       width: 16 },
  { header: "Lot No",              key: "lotNo",             width: 16 },
  { header: "Bill No",             key: "billNo",            width: 16 },
  { header: "Truck No",            key: "truckNo",           width: 14 },
  { header: "Supplier / Farmer",   key: "supplier",          width: 26 },
  { header: "Vegetable",           key: "vegetable",         width: 20 },
  { header: "Qty (KG)",            key: "quantityKg",        width: 14 },
  { header: "Bags",                key: "bags",              width: 10 },
  { header: "Rem. Qty (KG)",       key: "remainingQtyKg",    width: 16 },
  { header: "Rem. Bags",           key: "remainingBags",     width: 12 },
  { header: "Freight (Rs)",        key: "freight",           width: 14 },
  { header: "Advance (Rs)",        key: "advanceAmount",     width: 14 },
  { header: "Settlement Status",   key: "settlementStatus",  width: 20 },
];

const NUMERIC_KEYS = new Set<keyof ArrivalReportExportRow>([
  "quantityKg",
  "bags",
  "remainingQtyKg",
  "remainingBags",
  "freight",
  "advanceAmount",
]);

const HEADER_FILL: ExcelJS.Fill = {
  type: "pattern",
  pattern: "solid",
  fgColor: { argb: "FF0F5132" }, // deep green — matches Arrival theme
};

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: "FFD1E7DD" }, // light green tint
};

const ALT_FILL: ExcelJS.Fill = {
  type: "pattern",
  pattern: "solid",
  fgColor: { argb: "FFF8F9FA" },
};

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

/**
 * Generates a styled Arrivals Report Excel and streams it to the Express Response.
 */
export async function generateArrivalReportExcel(
  rows: ArrivalReportExportRow[],
  res: Response,
  filename = "arrival-report"
): Promise<void> {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = "AVS System";
  workbook.created = new Date();

  const sheet = workbook.addWorksheet("Arrivals Report", {
    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,
  }));

  // Style header row
  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;
  });

  // Data rows
  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;
    });
  });

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

    const totalRow = sheet.addRow({
      arrivalDate:    "TOTAL",
      lotNo:          "",
      billNo:         "",
      truckNo:        "",
      supplier:       "",
      vegetable:      "",
      quantityKg:     { formula: `SUM(G${dataStartRow}:G${dataEndRow})` } as any,
      bags:           { formula: `SUM(H${dataStartRow}:H${dataEndRow})` } as any,
      remainingQtyKg: { formula: `SUM(I${dataStartRow}:I${dataEndRow})` } as any,
      remainingBags:  { formula: `SUM(J${dataStartRow}:J${dataEndRow})` } as any,
      freight:        { formula: `SUM(K${dataStartRow}:K${dataEndRow})` } as any,
      advanceAmount:  { formula: `SUM(L${dataStartRow}:L${dataEndRow})` } as any,
      settlementStatus: "",
    });

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

/**
 * Maps a raw Prisma arrival (purchase) record to a flat ArrivalReportExportRow.
 */
export function mapArrivalToExportRow(item: any): ArrivalReportExportRow {
  const settlementStatus = item.arrivalSettlement?.status ?? "UNSETTLED";
  return {
    arrivalDate:     item.arrivalDate
      ? new Date(item.arrivalDate).toLocaleDateString("en-GB").replace(/\//g, "-")
      : item.purchaseDate
        ? new Date(item.purchaseDate).toLocaleDateString("en-GB").replace(/\//g, "-")
        : "",
    lotNo:           item.lotNo ?? "",
    billNo:          item.billNo ?? "",
    truckNo:         item.truckNo ?? "",
    supplier:        item.supplier?.name ?? item.cashSupplierName ?? item.agentName ?? "",
    vegetable:       item.vegetable?.name ?? "",
    quantityKg:      Number(item.quantity ?? 0),
    bags:            Number(item.bags ?? 0),
    remainingQtyKg:  Number(item.remainingQuantity ?? 0),
    remainingBags:   Number(item.remainingBags ?? 0),
    freight:         Number(item.totalFreight ?? item.freight ?? 0),
    advanceAmount:   Number(item.advanceAmount ?? 0),
    settlementStatus,
  };
}