import ExcelJS from "exceljs";

export const generateExpenseExcel = async (expenses: any[]) => {
    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet("Expenses");

    // Define columns
    sheet.columns = [
        { header: "S.No.", key: "serialNo", width: 10 },
        { header: "Date", key: "expenseDate", width: 15 },
        { header: "Category", key: "category", width: 25 },
        { header: "Amount", key: "amount", width: 15 },
        { header: "Payment Mode", key: "paymentMode", width: 20 },
        { header: "Remarks", key: "remarks", width: 30 },
    ];

    // Header styling
    const headerRow = sheet.getRow(1);

    headerRow.font = {
        bold: true,
        color: { argb: "FFFFFFFF" },
    };

    headerRow.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FF4F81BD" },
    };

    headerRow.alignment = {
        horizontal: "center",
        vertical: "middle",
    };

    // Add expense rows
    expenses.forEach((exp, index) => {
        sheet.addRow({
            serialNo: index + 1,
            expenseDate: new Date(exp.expenseDate).toLocaleDateString("en-GB").replace(/\//g, "-"),
            category: exp.category,
            amount: Number(exp.amount),
            paymentMode: exp.paymentMode,
            remarks: exp.remarks ?? "",
        });
    });

    // Add total row
    const totalAmount = expenses.reduce(
        (sum, exp) => sum + Number(exp.amount),
        0
    );

    const totalRow = sheet.addRow({
        category: "Total",
        amount: totalAmount,
    });

    totalRow.font = { bold: true };

    // Optional: Add borders to all cells
    sheet.eachRow((row) => {
        row.eachCell((cell) => {
            cell.border = {
                top: { style: "thin" },
                left: { style: "thin" },
                bottom: { style: "thin" },
                right: { style: "thin" },
            };
        });
    });

    // Format amount column as currency/number
    sheet.getColumn("amount").numFmt = "#,##0.00";

    return workbook;
};