utils/exportToExcel.ts

import XLSX from 'xlsx-js-style';
import { Transaction, Party, BillType } from '../types';

export interface AnalysisExportRow {
    lotNumber: string;
    mirchiName: string;
    purchaseQty: number;
    purchaseValue: number;
    avgPurchaseRate: number;
    soldQty: number;
    salesValue: number;
    avgSaleRate: number;
    realizedProfit: number;
    marginPercent: number;
    remainingQty: number;
    remainingValueAtCost: number;
}

export interface LotOverviewExportRow {
    lotNumber: string;
    mirchiName: string;
    purchaseQty: number;
    purchaseValue: number;
    avgPurchaseRate: number;
    soldQty: number;
    salesValue: number;
    avgSaleRate: number;
    realizedProfit: number;
    marginPercent: number;
    remainingQty: number;
    remainingValueAtCost: number;
}

export interface LotDetailExportRow {
    date: string;
    billNo: string;
    typeLabel: string;
    directionLabel: string;
    qtyKg: number;
    ratePerKg: number;
    grossAmount: number;
    expensesAllocated: number;
    netAmount: number;
    profitImpact: number;
}

// ==========================================
// 2. STYLING CONFIGURATION
// ==========================================

// Common Colors
const CLR_BORDER = { rgb: "D1D5DB" }; // Gray-300
const CLR_TEXT = { rgb: "374151" };   // Gray-700
const CLR_HEAD_BG = { rgb: "F3F4F6" }; // Gray-100
const CLR_GREEN = { rgb: "059669" };
const CLR_RED = { rgb: "DC2626" };
const CLR_TEAL = { rgb: "0D9488" };
const CLR_WHITE = { rgb: "FFFFFF" };

// Border Definitions
const BORDER_ALL = {
    top: { style: 'thin', color: CLR_BORDER },
    bottom: { style: 'thin', color: CLR_BORDER },
    left: { style: 'thin', color: CLR_BORDER },
    right: { style: 'thin', color: CLR_BORDER }
};

const BORDER_TOP_SIDE = {
    top: { style: 'thin', color: { rgb: "9CA3AF" } }, // Darker Gray for Card
    left: { style: 'thin', color: { rgb: "9CA3AF" } },
    right: { style: 'thin', color: { rgb: "9CA3AF" } }
};

const BORDER_BOT_SIDE = {
    bottom: { style: 'thin', color: { rgb: "9CA3AF" } },
    left: { style: 'thin', color: { rgb: "9CA3AF" } },
    right: { style: 'thin', color: { rgb: "9CA3AF" } }
};

// Style Objects
const STYLES = {
    TITLE: {
        font: { bold: true, sz: 14, color: { rgb: "111827" } },
        alignment: { horizontal: 'left', vertical: 'center' }
    },
    
    // Card Styles
    CARD_HEADER: {
        border: BORDER_TOP_SIDE,
        fill: { fgColor: CLR_WHITE },
        font: { sz: 10, color: { rgb: "6B7280" } },
        alignment: { vertical: 'bottom', horizontal: 'left', indent: 1 }
    },
    CARD_VALUE: {
        border: BORDER_BOT_SIDE,
        fill: { fgColor: CLR_WHITE },
        font: { bold: true, sz: 11, color: { rgb: "1F2937" } },
        alignment: { vertical: 'top', horizontal: 'left', indent: 1 }
    },

    // Table Styles
    TABLE_HEAD: {
        fill: { fgColor: CLR_HEAD_BG },
        font: { bold: true, color: CLR_TEXT },
        border: BORDER_ALL,
        alignment: { horizontal: 'center', vertical: 'center' }
    },
    CELL_TEXT: {
        border: BORDER_ALL,
        alignment: { vertical: 'center', horizontal: 'left' },
        font: { color: CLR_TEXT }
    },
    CELL_NUM: {
        border: BORDER_ALL,
        alignment: { vertical: 'center', horizontal: 'right' },
        font: { color: { rgb: "111827" } }
    },
    CELL_MONO: {
        border: BORDER_ALL,
        alignment: { vertical: 'center', horizontal: 'left' },
        font: { name: 'Courier New', sz: 10, color: CLR_TEXT }
    },

    // Text Colors
    TXT_TEAL: { font: { bold: true, sz: 12, color: CLR_TEAL } },
    TXT_GRAY: { font: { color: { rgb: "6B7280" } } },
    TXT_GREEN: { font: { bold: true, color: CLR_GREEN } },
    TXT_RED: { font: { bold: true, color: CLR_RED } }
};

// Formats
const FMT_CURRENCY = "₹#,##0.00"; // 2 decimal places for clean alignment
const FMT_QTY = "0.00 \"kg\"";

// ==========================================
// 3. HELPER FUNCTIONS
// ==========================================

// Helper to create cell. 
// Uses empty string "" if value is null to avoid "null" text in Excel.
const c = (val: any, s: any = {}, fmt?: string) => {
    return {
        v: val === null || val === undefined ? "" : val,
        s: { ...s, numFmt: fmt }, // Inject format into style for xlsx-js-style
        z: fmt // Inject format into standard key for compatibility
    };
};

const formatIndCurrency = (val: number) => {
    return val.toLocaleString('en-IN', { 
        maximumFractionDigits: 0, // Keep summary cards clean (no decimals)
        style: 'currency', 
        currency: 'INR' 
    }).replace('₹', '₹'); // Ensure symbol consistency
};

// ==========================================
// 4. EXPORT FUNCTIONS
// ==========================================

export const exportLotDetailAnalysis = (
    lot: LotOverviewExportRow,
    rows: LotDetailExportRow[],
) => {
    const data: any[][] = [];

    // --- ROW 1: Main Title ---
    data.push([
        c(`${lot.lotNumber} - ${lot.mirchiName} Analysis`, STYLES.TITLE),
        c(""), c(""), c(""), c(""), c(""), c(""), c("")
    ]);

    // --- ROW 2: Spacer ---
    data.push([c("")]);

    // --- ROW 3: Lot Info (Left) & Remaining (Right) ---
    // Note: We used c("") for spacers to ensure no "null" text
    data.push([
        c(lot.lotNumber, { ...STYLES.TXT_TEAL, alignment: { vertical: 'bottom' } }), 
        c(""), c(""), c(""), c(""), c(""), 
        c("Remaining", { ...STYLES.TXT_GRAY, alignment: { horizontal: 'right', vertical: 'bottom' } }), 
        c(Number(lot.remainingQty.toFixed(2)), { font: { bold: true }, alignment: { horizontal: 'right', vertical: 'bottom' } }, FMT_QTY)
    ]);

    // --- ROW 4: Mirchi Name ---
    data.push([
        c(lot.mirchiName, STYLES.TXT_GRAY),
        c(""), c(""), c(""), c(""), c(""), c(""), c("")
    ]);

    // --- ROW 5: Spacer ---
    data.push([c("")]);

    // --- ROW 6: CARD HEADERS ---
    data.push([
        c("Purchase", STYLES.CARD_HEADER), c(""), c(""), c(""), 
        c("Sales", STYLES.CARD_HEADER), c(""), c(""), c("")
    ]);

    // --- ROW 7: CARD VALUES ---
    // Format text manually for the card to ensure it looks perfect
    const buyTxt = `${lot.purchaseQty.toFixed(2)} kg  •  ${formatIndCurrency(lot.purchaseValue)}`;
    const sellTxt = `${lot.soldQty.toFixed(2)} kg  •  ${formatIndCurrency(lot.salesValue)}`;

    data.push([
        c(buyTxt, STYLES.CARD_VALUE), c(""), c(""), c(""), 
        c(sellTxt, STYLES.CARD_VALUE), c(""), c(""), c("")
    ]);

    // --- ROW 8: Spacer ---
    data.push([c("")]);

    // --- ROW 9: Avg & Profit Summary ---
    const avgText = `Avg Buy ₹${lot.avgPurchaseRate.toFixed(0)} /kg   •   Avg Sell ₹${lot.avgSaleRate.toFixed(0)} /kg`;
    
    // Profit Logic
    const pVal = Number(lot.realizedProfit.toFixed(2)); // Round to avoid .774
    const pLabel = pVal > 0 ? "Profit" : pVal < 0 ? "Loss" : "Break-even";
    const pStyle = pVal > 0 ? STYLES.TXT_GREEN : pVal < 0 ? STYLES.TXT_RED : STYLES.TXT_GRAY;

    data.push([
        c(avgText, { font: { color: { rgb: "4B5563" }, italic: true }, alignment: { vertical: 'center' } }), 
        c(""), c(""), c(""), c(""), c(""), 
        c(pLabel, { ...STYLES.TXT_GRAY, alignment: { horizontal: 'right', vertical: 'center' } }), 
        c(pVal, { ...pStyle, alignment: { horizontal: 'right', vertical: 'center' } }, FMT_CURRENCY)
    ]);

    // --- ROW 10: Spacer ---
    data.push([c("")]);

    // --- ROW 11: TABLE HEADERS ---
    data.push([
        c('Date', STYLES.TABLE_HEAD),
        c('Bill No', STYLES.TABLE_HEAD),
        c('Type', STYLES.TABLE_HEAD),
        c('Import / Export', STYLES.TABLE_HEAD),
        c('Qty (kg)', STYLES.TABLE_HEAD),
        c('Rate', STYLES.TABLE_HEAD),
        c('Net Amount', STYLES.TABLE_HEAD),
        c('Profit Impact', STYLES.TABLE_HEAD)
    ]);

    // --- ROW 12+: DATA ---
    if (rows.length === 0) {
        data.push([c('No transactions found', STYLES.CELL_TEXT)]);
    } else {
        rows.forEach(row => {
            // Round numbers to 2 decimals to prevent floating point garbage
            const safeProfit = Number(row.profitImpact.toFixed(2));
            const safeNet = Number(row.netAmount.toFixed(2));
            const safeQty = Number(row.qtyKg.toFixed(2));
            const safeRate = Number(row.ratePerKg.toFixed(2));

            const impactStyle = safeProfit > 0 
                ? { ...STYLES.CELL_NUM, font: { color: CLR_GREEN } }
                : safeProfit < 0 
                    ? { ...STYLES.CELL_NUM, font: { color: CLR_RED } } 
                    : STYLES.CELL_NUM;

            data.push([
                c(row.date, STYLES.CELL_TEXT),
                c(row.billNo, STYLES.CELL_MONO),
                c(row.typeLabel, STYLES.CELL_TEXT),
                c(row.directionLabel, STYLES.CELL_TEXT),
                c(safeQty, STYLES.CELL_NUM, "0.00"),
                c(safeRate, STYLES.CELL_NUM, "0.00"),
                c(safeNet, STYLES.CELL_NUM, FMT_CURRENCY),
                c(safeProfit, impactStyle, FMT_CURRENCY)
            ]);
        });
    }

    // --- GENERATE ---
    const ws = XLSX.utils.aoa_to_sheet(data);

    // --- MERGES (0-based indices) ---
    ws['!merges'] = [
        { s: { r: 0, c: 0 }, e: { r: 0, c: 7 } }, // Title
        { s: { r: 2, c: 0 }, e: { r: 2, c: 5 } }, // Lot Number
        { s: { r: 3, c: 0 }, e: { r: 3, c: 5 } }, // Mirchi Name
        // Purchase Card
        { s: { r: 5, c: 0 }, e: { r: 5, c: 3 } }, 
        { s: { r: 6, c: 0 }, e: { r: 6, c: 3 } }, 
        // Sales Card
        { s: { r: 5, c: 4 }, e: { r: 5, c: 7 } },
        { s: { r: 6, c: 4 }, e: { r: 6, c: 7 } },
        // Avg Line
        { s: { r: 8, c: 0 }, e: { r: 8, c: 5 } },
    ];

    // --- COL WIDTHS ---
    ws['!cols'] = [
        { wch: 14 }, // Date
        { wch: 22 }, // Bill No
        { wch: 12 }, // Type
        { wch: 14 }, // Imp/Exp
        { wch: 12 }, // Qty
        { wch: 10 }, // Rate
        { wch: 16 }, // Net Amt
        { wch: 16 }, // Profit
    ];

    const wb = XLSX.utils.book_new();
    const safeName = lot.lotNumber.replace(/[^a-zA-Z0-9]/g, '_').substring(0, 30);
    XLSX.utils.book_append_sheet(wb, ws, safeName);
    XLSX.writeFile(wb, `Lot_${safeName}_Analysis.xlsx`);
};

// 5. OTHER EXPORT FUNCTIONS (Parties/Ledger)
// ==========================================

export const exportPartyLedger = (party: Party, transactions: Transaction[], dateRange?: { from: string; to: string }) => {
    const parseDate = (value?: string): Date | undefined => {
        if (!value) return undefined;
        const trimmed = String(value).trim();
        if (!trimmed) return undefined;

        const iso = trimmed.match(/^\d{4}-\d{2}-\d{2}(?:[T\s].*)?$/);
        if (iso) {
            const d = new Date(trimmed);
            return isNaN(d.getTime()) ? undefined : d;
        }
        const slash = trimmed.match(/^\d{1,2}\/\d{1,2}\/(\d{2}|\d{4})$/);
        if (slash) {
            const [aStr, bStr, yyStr] = trimmed.split('/');
            const a = Number(aStr);
            const b = Number(bStr);
            const yy = Number(yyStr);
            const year = yyStr.length === 2 ? 2000 + yy : yy;

            const day = a > 12 ? a : b > 12 ? b : a;
            const month = a > 12 ? b : b > 12 ? a : b;

            const d = new Date(year, month - 1, day);
            return isNaN(d.getTime()) ? undefined : d;
        }

        const d = new Date(trimmed);
        return isNaN(d.getTime()) ? undefined : d;
    };

    const extractDateFromText = (text?: string): Date | undefined => {
        if (!text) return undefined;
        const iso = text.match(/\b\d{4}-\d{2}-\d{2}\b/);
        if (iso?.[0]) return parseDate(iso[0]);
        const dmy = text.match(/\b\d{1,2}\/\d{1,2}\/\d{4}\b/);
        if (dmy?.[0]) return parseDate(dmy[0]);
        return undefined;
    };

    const isAwaakTx = (tx: Transaction) => {
        const typeStr = String(tx.bill_type).toUpperCase();
        const numStr = tx.bill_number ? tx.bill_number.toUpperCase() : "";
        const isJawaakFromNumber = numStr.startsWith('JAWAAK');
        const isAwaakFromNumber = numStr.startsWith('AWAAK');
        return isAwaakFromNumber ? true : isJawaakFromNumber ? false : typeStr === 'AWAAK';
    };

    const inRange = (d: Date) => {
        if (!dateRange) return true;
        const from = new Date(dateRange.from);
        const to = new Date(dateRange.to);
        return d >= from && d <= to;
    };

    type LedgerEvent =
        | { kind: 'bill'; date: Date; tx: Transaction }
        | { kind: 'payment'; date: Date; tx: Transaction; payment: any };

    const events: LedgerEvent[] = [];

    transactions.forEach(tx => {
        const billDate = parseDate(tx.bill_date) || new Date(tx.bill_date);
        if (!inRange(billDate)) return;

        events.push({ kind: 'bill', date: billDate, tx });

        const payments = tx.payments || [];
        const validPayments = payments.filter((p: any) => String(p.mode).toLowerCase() !== 'due');
        validPayments.forEach((p: any) => {
            const paymentDate =
                parseDate(p.payment_date) ||
                parseDate(p.created_at) ||
                parseDate(p.date) ||
                extractDateFromText(p.reference) ||
                billDate;

            const safePaymentDate = paymentDate < billDate ? billDate : paymentDate;
            if (!inRange(safePaymentDate)) return;
            events.push({ kind: 'payment', date: safePaymentDate, tx, payment: p });
        });
    });

    events.sort((a, b) => {
        const ta = a.date.getTime();
        const tb = b.date.getTime();
        if (ta !== tb) return ta - tb;
        if (a.kind !== b.kind) return a.kind === 'bill' ? -1 : 1;
        return (a.tx.bill_number || '').localeCompare(b.tx.bill_number || '');
    });

    const data: any[][] = [];
    data.push([c(`Ledger: ${party.name}`, STYLES.TITLE)]);
    data.push([c("")]);
    data.push(['Date', 'Particulars', 'Credit', 'Debit', 'Balance', 'Dr/Cr'].map(h => c(h, STYLES.TABLE_HEAD)));

    let running = 0, tCr = 0, tDr = 0;
    events.forEach(ev => {
        const tx = ev.tx;
        const isAwaak = isAwaakTx(tx);

        let cr = 0, dr = 0;

        if (ev.kind === 'bill') {
            if (isAwaak) {
                if (tx.is_return) dr = tx.total_amount;
                else cr = tx.total_amount;
            } else {
                if (tx.is_return) cr = tx.total_amount;
                else dr = tx.total_amount;
            }

            const desc = `${tx.bill_number}` + (tx.is_return ? ' (RETURN)' : '') + (tx.items.length ? ` - ${tx.items[0].mirchi_name}` : "");

            tCr += cr; tDr += dr; running += (dr - cr);

            data.push([
                c(ev.date.toLocaleDateString('en-IN'), STYLES.CELL_TEXT),
                c(desc, STYLES.CELL_TEXT),
                c(cr || "", STYLES.CELL_NUM, FMT_CURRENCY),
                c(dr || "", STYLES.CELL_NUM, FMT_CURRENCY),
                c(Math.abs(running), STYLES.CELL_NUM, FMT_CURRENCY),
                c(running >= 0 ? 'Dr' : 'Cr', STYLES.CELL_TEXT)
            ]);

            return;
        }

        const p = ev.payment;
        const amount = Number(p.amount) || 0;

        if (isAwaak) {
            if (tx.is_return) cr = amount;
            else dr = amount;
        } else {
            if (tx.is_return) dr = amount;
            else cr = amount;
        }

        const modeStr = String(p.mode || '').charAt(0).toUpperCase() + String(p.mode || '').slice(1);
        let desc = `   [${modeStr}]`;
        if (p.reference) desc += ` ${p.reference}`;

        tCr += cr; tDr += dr; running += (dr - cr);

        data.push([
            c(ev.date.toLocaleDateString('en-IN'), STYLES.CELL_TEXT),
            c(desc, STYLES.CELL_TEXT),
            c(cr || "", STYLES.CELL_NUM, FMT_CURRENCY),
            c(dr || "", STYLES.CELL_NUM, FMT_CURRENCY),
            c(Math.abs(running), STYLES.CELL_NUM, FMT_CURRENCY),
            c(running >= 0 ? 'Dr' : 'Cr', STYLES.CELL_TEXT)
        ]);
    });

    const totStyle = { ...STYLES.CELL_NUM, font: { bold: true } };
    data.push([
        c('TOTAL', { ...STYLES.CELL_TEXT, font: { bold: true } }), c(""),
        c(tCr, totStyle, FMT_CURRENCY), c(tDr, totStyle, FMT_CURRENCY),
        c(Math.abs(running), totStyle, FMT_CURRENCY),
        c(running > 0 ? 'Dr' : 'Cr', { ...STYLES.CELL_TEXT, font: { bold: true } })
    ]);

    const ws = XLSX.utils.aoa_to_sheet(data);
    ws['!merges'] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 5 } }];
    ws['!cols'] = [{ wch: 12 }, { wch: 40 }, { wch: 15 }, { wch: 15 }, { wch: 15 }, { wch: 8 }];
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Ledger');
    XLSX.writeFile(wb, `${party.name.replace(/\s/g,'_')}_Ledger.xlsx`);
};

export const exportAllParties = (parties: Party[]) => {
    const data: any[][] = [];
    data.push(['Party Name', 'Phone', 'City', 'Type', 'Balance', 'Status'].map(h => c(h, STYLES.TABLE_HEAD)));
    let tRec = 0, tPay = 0;

    parties.forEach(p => {
        if(p.current_balance > 0) tRec += p.current_balance;
        if(p.current_balance < 0) tPay += Math.abs(p.current_balance);
        const color = p.current_balance > 0 ? CLR_GREEN : p.current_balance < 0 ? CLR_RED : undefined;
        
        data.push([
            c(p.name, STYLES.CELL_TEXT), c(p.phone||'-', STYLES.CELL_TEXT), c(p.city||'-', STYLES.CELL_TEXT),
            c(p.party_type, STYLES.CELL_TEXT), 
            c(p.current_balance, { ...STYLES.CELL_NUM, font: { color } }, FMT_CURRENCY),
            c(p.current_balance > 0 ? 'Rec' : p.current_balance < 0 ? 'Pay' : '-', STYLES.CELL_TEXT)
        ]);
    });

    data.push([c("")]); data.push([c('SUMMARY', { font: { bold: true } })]);
    data.push([c('Total Receivable'), c(""), c(""), c(""), c(tRec, STYLES.CELL_NUM, FMT_CURRENCY)]);
    data.push([c('Total Payable'), c(""), c(""), c(""), c(tPay, STYLES.CELL_NUM, FMT_CURRENCY)]);

    const ws = XLSX.utils.aoa_to_sheet(data);
    ws['!cols'] = [{ wch: 30 }, { wch: 15 }, { wch: 20 }, { wch: 10 }, { wch: 18 }, { wch: 10 }];
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Parties');
    XLSX.writeFile(wb, `All_Parties.xlsx`);
};

export const exportAllTransactions = (transactions: Transaction[]) => {
    const data: any[][] = [];
    data.push(['Date', 'Bill No', 'Party', 'Type', 'Amount', 'Paid', 'Balance'].map(h => c(h, STYLES.TABLE_HEAD)));
    transactions.forEach(tx => {
        data.push([
            c(new Date(tx.bill_date).toLocaleDateString('en-IN'), STYLES.CELL_TEXT),
            c(tx.bill_number, STYLES.CELL_MONO), c(tx.party_name, STYLES.CELL_TEXT), c(tx.bill_type, STYLES.CELL_TEXT),
            c(tx.total_amount, STYLES.CELL_NUM, FMT_CURRENCY),
            c(tx.paid_amount, STYLES.CELL_NUM, FMT_CURRENCY),
            c(tx.balance_amount, STYLES.CELL_NUM, FMT_CURRENCY)
        ]);
    });
    const ws = XLSX.utils.aoa_to_sheet(data);
    ws['!cols'] = [{ wch: 12 }, { wch: 20 }, { wch: 25 }, { wch: 10 }, { wch: 15 }, { wch: 15 }, { wch: 15 }];
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Transactions');
    XLSX.writeFile(wb, `All_Transactions.xlsx`);
};

export const exportAnalysisReport = (rows: AnalysisExportRow[]) => {
    if (!rows.length) return;
    const data: any[][] = [];
    const headers = ['LOT','Mirchi','Buy Qty','Buy Val','Avg Buy','Sell Qty','Sell Val','Avg Sell','Rem Qty','Rem Val','Profit','Margin %'];
    data.push(headers.map(h => c(h, STYLES.TABLE_HEAD)));

    rows.forEach(r => {
        const pStyle = r.realizedProfit > 0 ? { ...STYLES.CELL_NUM, font: { color: CLR_GREEN } } : r.realizedProfit < 0 ? { ...STYLES.CELL_NUM, font: { color: CLR_RED } } : STYLES.CELL_NUM;
        data.push([
            c(r.lotNumber, STYLES.CELL_TEXT), c(r.mirchiName, STYLES.CELL_TEXT),
            c(r.purchaseQty, STYLES.CELL_NUM,"0.00"), c(r.purchaseValue, STYLES.CELL_NUM,FMT_CURRENCY), c(r.avgPurchaseRate, STYLES.CELL_NUM,FMT_CURRENCY),
            c(r.soldQty, STYLES.CELL_NUM,"0.00"), c(r.salesValue, STYLES.CELL_NUM,FMT_CURRENCY), c(r.avgSaleRate, STYLES.CELL_NUM,FMT_CURRENCY),
            c(r.remainingQty, STYLES.CELL_NUM,"0.00"), c(r.remainingValueAtCost, STYLES.CELL_NUM,FMT_CURRENCY),
            c(r.realizedProfit, pStyle,FMT_CURRENCY), c(r.marginPercent/100, STYLES.CELL_NUM,"0.00%")
        ]);
    });
    const ws = XLSX.utils.aoa_to_sheet(data);
    ws['!cols'] = headers.map(() => ({ wch: 14 }));
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Summary');
    XLSX.writeFile(wb, `Analysis_Summary.xlsx`);
};