// Excel Export Functionality using SheetJS (xlsx)
// This script provides Excel export capabilities for tables

// Helper to escape CSV fields
function escapeCsvField(field) {
    if (field === null || field === undefined) {
        return '';
    }
    const stringField = String(field);
    // Escape quotes and wrap in quotes if contains comma, quote or newline
    if (stringField.includes(',') || stringField.includes('"') || stringField.includes('\n')) {
        return `"${stringField.replace(/"/g, '""')}"`;
    }
    return stringField;
}

// Function to export table to Excel
function exportToExcel(tableId, filename = 'export') {
    const table = document.getElementById(tableId);
    if (!table) {
        console.error('Table not found:', tableId);
        return;
    }

    // Get table data
    const data = [];
    const headers = [];

    // Get headers
    const headerCells = table.querySelectorAll('thead th');
    headerCells.forEach(cell => {
        headers.push(cell.textContent.trim());
    });
    data.push(headers);

    // Get rows
    const rows = table.querySelectorAll('tbody tr');
    rows.forEach(row => {
        const rowData = [];
        const cells = row.querySelectorAll('td');
        cells.forEach(cell => {
            rowData.push(cell.textContent.trim());
        });
        if (rowData.length > 0 && !rowData[0].includes('Loading')) {
            data.push(rowData);
        }
    });

    // Create CSV content with BOD and escaped fields
    const csvContent = data.map(row => row.map(escapeCsvField).join(',')).join('\n');

    // Create download link
    const blob = new Blob(['\uFEFF' + csvContent], { type: 'text/csv;charset=utf-8;' });
    const link = document.createElement('a');
    const url = URL.createObjectURL(blob);

    link.setAttribute('href', url);
    link.setAttribute('download', `${filename}_${new Date().toISOString().split('T')[0]}.csv`);
    link.style.visibility = 'hidden';

    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);

    app.showNotification('Exported to CSV successfully', 'success');
}

// Function to export custom data to Excel
function exportDataToExcel(data, headers, filename = 'export') {
    const csvData = [headers, ...data];
    const csvContent = csvData.map(row => row.map(escapeCsvField).join(',')).join('\n');

    const blob = new Blob(['\uFEFF' + csvContent], { type: 'text/csv;charset=utf-8;' });
    const link = document.createElement('a');
    const url = URL.createObjectURL(blob);

    link.setAttribute('href', url);
    link.setAttribute('download', `${filename}_${new Date().toISOString().split('T')[0]}.csv`);
    link.style.visibility = 'hidden';

    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);

    app.showNotification('Exported to CSV successfully', 'success');
}

// Function to export inventory to Excel
async function exportInventoryToExcel() {
    try {
        const skus = await app.apiGet('/skus');

        const headers = ['SKU Code', 'Product', 'Flavour', 'Weight', 'Current Stock', 'Price (PKR)', 'Stock Value (PKR)'];
        const data = skus.map(sku => [
            sku.sku_code,
            sku.product_name,
            sku.flavour,
            sku.weight,
            sku.current_stock,
            sku.price,
            sku.current_stock * sku.price
        ]);

        exportDataToExcel(data, headers, 'inventory');
    } catch (error) {
        console.error('Error exporting inventory:', error);
        app.showNotification('Failed to export inventory', 'error');
    }
}

// Function to export transactions to Excel
async function exportTransactionsToExcel(startDate, endDate) {
    try {
        let url = '/transactions';
        const params = new URLSearchParams();
        if (startDate) params.append('start_date', startDate);
        if (endDate) params.append('end_date', endDate);
        if (params.toString()) url += '?' + params.toString();

        const transactions = await app.apiGet(url);

        const headers = ['Date', 'Customer', 'Payment Type', 'Total Amount (PKR)'];
        const data = transactions.map(t => [
            t.transaction_date,
            t.customer_name,
            t.payment_type,
            t.total_amount
        ]);

        exportDataToExcel(data, headers, 'transactions');
    } catch (error) {
        console.error('Error exporting transactions:', error);
        app.showNotification('Failed to export transactions', 'error');
    }
}

// Function to export expenses to Excel
async function exportExpensesToExcel(startDate, endDate) {
    try {
        let url = '/expenses';
        const params = new URLSearchParams();
        if (startDate) params.append('start_date', startDate);
        if (endDate) params.append('end_date', endDate);
        if (params.toString()) url += '?' + params.toString();

        const expenses = await app.apiGet(url);

        const headers = ['Date', 'Category', 'Description', 'Amount (PKR)'];
        const data = expenses.map(e => [
            e.expense_date,
            e.category,
            e.description,
            e.amount
        ]);

        // Add total row
        const total = expenses.reduce((sum, e) => sum + e.amount, 0);
        data.push(['', '', 'TOTAL', total]);

        exportDataToExcel(data, headers, 'expenses');
    } catch (error) {
        console.error('Error exporting expenses:', error);
        app.showNotification('Failed to export expenses', 'error');
    }
}

// Function to export credit ledger to Excel
async function exportCreditToExcel() {
    try {
        const transactions = await app.apiGet('/transactions?payment_type=Credit');
        const payments = await app.apiGet('/credit-payments');

        const headers = ['Date', 'Customer', 'Total Amount (PKR)', 'Amount Paid (PKR)', 'Balance (PKR)'];
        const data = transactions.map(t => {
            const transactionPayments = payments.filter(p => p.transaction_id === t.id);
            const totalPaid = transactionPayments.reduce((sum, p) => sum + p.amount_paid, 0);
            const balance = t.total_amount - totalPaid;

            return [
                t.transaction_date,
                t.customer_name,
                t.total_amount,
                totalPaid,
                balance
            ];
        });

        // Add total row
        const totalOutstanding = data.reduce((sum, row) => sum + row[4], 0);
        data.push(['', '', '', 'TOTAL OUTSTANDING', totalOutstanding]);

        exportDataToExcel(data, headers, 'credit_ledger');
    } catch (error) {
        console.error('Error exporting credit ledger:', error);
        app.showNotification('Failed to export credit ledger', 'error');
    }
}

// Function to export returns to Excel
async function exportReturnsToExcel(startDate, endDate) {
    try {
        let url = '/returns';
        const params = new URLSearchParams();
        if (startDate) params.append('start_date', startDate);
        if (endDate) params.append('end_date', endDate);
        if (params.toString()) url += '?' + params.toString();

        const returns = await app.apiGet(url);

        const headers = ['Date', 'Customer', 'Reason'];
        const data = returns.map(ret => [
            ret.return_date,
            ret.customer_name,
            ret.reason
        ]);

        exportDataToExcel(data, headers, 'returns');
    } catch (error) {
        console.error('Error exporting returns:', error);
        app.showNotification('Failed to export returns', 'error');
    }
}

// Make functions available globally
window.exportToExcel = exportToExcel;
window.exportDataToExcel = exportDataToExcel;
window.exportInventoryToExcel = exportInventoryToExcel;
window.exportTransactionsToExcel = exportTransactionsToExcel;
window.exportExpensesToExcel = exportExpensesToExcel;
window.exportCreditToExcel = exportCreditToExcel;
window.exportReturnsToExcel = exportReturnsToExcel;
