utils/exportToExcel.ts

import * as 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 }, jamaEntries?: { entry_date: string; amount: number; note?: string | null }[]) => {
    // Filter & Sort transactions
    const filtered = (dateRange ? transactions.filter(t => {
        const d = new Date(t.bill_date);
        return d >= new Date(dateRange.from) && d <= new Date(dateRange.to);
    }) : [...transactions]).sort((a, b) => new Date(a.bill_date).getTime() - new Date(b.bill_date).getTime());

    // Filter & Sort jama entries
    const filteredJama = (jamaEntries || []).filter(j => {
        if (!dateRange) return true;
        const d = new Date(j.entry_date);
        return d >= new Date(dateRange.from) && d <= new Date(dateRange.to);
    }).sort((a, b) => new Date(a.entry_date).getTime() - new Date(b.entry_date).getTime());

    // Build combined ledger rows sorted by date
    type LedgerExportRow = { date: Date; type: 'tx' | 'jama'; tx?: (typeof filtered)[0]; jama?: (typeof filteredJama)[0] };
    const combined: LedgerExportRow[] = [];
    filtered.forEach(tx => combined.push({ date: new Date(tx.bill_date), type: 'tx', tx }));
    filteredJama.forEach(j => combined.push({ date: new Date(j.entry_date), type: 'jama', jama: j }));
    combined.sort((a, b) => a.date.getTime() - b.date.getTime());

    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;

    // Add past_due as opening balance if exists
    const pastDue = party.past_due || 0;
    if (Math.abs(pastDue) > 0) {
        running = pastDue;
        const dr = pastDue > 0 ? pastDue : 0;
        const cr = pastDue < 0 ? Math.abs(pastDue) : 0;
        tCr += cr; tDr += dr;
        data.push([
            c('', STYLES.CELL_TEXT),
            c('मागील (Opening Balance)', 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)
        ]);
    }

    combined.forEach(row => {
        if (row.type === 'tx' && row.tx) {
            const tx = row.tx;
            let cr = 0, dr = 0;
            if (tx.bill_type === BillType.AWAAK) tx.is_return ? (cr = tx.total_amount) : (dr = tx.total_amount);
            else tx.is_return ? (dr = tx.total_amount) : (cr = tx.total_amount);
            
            tCr += cr; tDr += dr; running += (dr - cr);
            const desc = `${tx.bill_number} (${tx.bill_type})` + (tx.items.length ? ` - ${tx.items[0].mirchi_name}` : "");

            data.push([
                c(new Date(tx.bill_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)
            ]);
        } else if (row.type === 'jama' && row.jama) {
            const j = row.jama;
            const cr = j.amount;
            tCr += cr; running -= cr;
            const desc = `जमा (Jama)${j.note ? ` - ${j.note}` : ''}`;

            data.push([
                c(new Date(j.entry_date).toLocaleDateString('en-IN'), STYLES.CELL_TEXT),
                c(desc, STYLES.CELL_TEXT),
                c(cr, STYLES.CELL_NUM, FMT_CURRENCY),
                c("", 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 } })
    ]);

    // Add Total Due row
    data.push([c("")]);
    const dueLabel = running > 0 ? 'एकूण बाकी (Total Due)' : 'एकूण शिल्लक (Total Surplus)';
    data.push([
        c(dueLabel, { ...STYLES.CELL_TEXT, font: { bold: true, sz: 12 } }), c(""), c(""), c(""),
        c(Math.abs(running), { ...STYLES.CELL_NUM, font: { bold: true, sz: 12, color: running > 0 ? CLR_RED : CLR_GREEN } }, 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`);
};