let workbook = null;
let processedSheets = {};

document.addEventListener('DOMContentLoaded', () => {
    const fileInput = document.getElementById('fileInput');
    const loadBtn = document.getElementById('loadBtn');
    const updateBtn = document.getElementById('updateBtn');
    const generateReportBtn = document.getElementById('generateReportBtn');
    const exportExcelBtn = document.getElementById('exportExcelBtn');
    const exportCSVBtn = document.getElementById('exportCSVBtn');
    const inputFields = document.getElementById('inputFields');
    const reportContainer = document.getElementById('reportContainer');

    // Load Excel file
    fileInput.addEventListener('change', (e) => {
        const file = e.target.files[0];
        if (!file) return;

        const reader = new FileReader();
        reader.onload = (e) => {
            const data = new Uint8Array(e.target.result);
            workbook = XLSX.read(data, { type: 'array' });
            loadBtn.disabled = false;
        };
        reader.readAsArrayBuffer(file);
    });

    // Process Excel file
    loadBtn.addEventListener('click', () => {
        if (!workbook) return;

        try {
            // Process each sheet
            workbook.SheetNames.forEach(sheetName => {
                const worksheet = workbook.Sheets[sheetName];
                const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

                processedSheets[sheetName] = {
                    data: jsonData,
                    formulas: detectFormulas(worksheet),
                    editableCells: detectEditableCells(jsonData, worksheet)
                };
            });

            // Generate input fields for editable cells
            generateInputFields();
            
            // Enable buttons
            updateBtn.disabled = false;
            generateReportBtn.disabled = false;
            exportExcelBtn.disabled = false;
            exportCSVBtn.disabled = false;

            // Show success message
            showToast('Planilha processada com sucesso!', 'success');
        } catch (error) {
            console.error('Error processing workbook:', error);
            showToast('Erro ao processar a planilha', 'error');
        }
    });

    // Update values
    updateBtn.addEventListener('click', () => {
        if (!workbook) return;

        try {
            // Update values in processedSheets
            updateWorkbookValues();
            
            // Recalculate formulas
            recalculateFormulas();
            
            // Generate reports
            generateReports();

            showToast('Valores atualizados com sucesso!', 'success');
        } catch (error) {
            console.error('Error updating values:', error);
            showToast('Erro ao atualizar valores', 'error');
        }
    });

    // Generate reports
    generateReportBtn.addEventListener('click', () => {
        generateReports();
        showToast('Relatório gerado com sucesso!', 'success');
    });

    // Export to Excel
    exportExcelBtn.addEventListener('click', () => {
        exportToExcel();
    });

    // Export to CSV
    exportCSVBtn.addEventListener('click', () => {
        exportToCSV();
    });

    // Helper functions
    function detectFormulas(worksheet) {
        const formulas = {};
        for (const cell in worksheet) {
            if (cell[0] === '!') continue;
            if (worksheet[cell].f) {
                formulas[cell] = worksheet[cell].f;
            }
        }
        return formulas;
    }
    function detectEditableCells(jsonData, worksheet) {
        const editableCells = [];
        
        // Find cells without formulas and not empty
        for (let row = 0; row < jsonData.length; row++) {
            for (let col = 0; col < (jsonData[row]?.length || 0); col++) {
                const cellAddress = XLSX.utils.encode_cell({ r: row, c: col });
                
                // Skip if cell has formula
                if (worksheet[cellAddress]?.f) continue;
                
                // Check if cell is a header (first row)
                if (row === 0) continue;
                
                // Consider cells with values (including empty strings)
                editableCells.push({
                    address: cellAddress,
                    value: jsonData[row][col] || '',
                    row,
                    col,
                    sheet: worksheet.name,
                    header: jsonData[0][col] || `Coluna ${col+1}`
                });
            }
        }
        
        return editableCells;
    }
function generateInputFields() {
        inputFields.innerHTML = '';
        
        // Group editable cells by sheet
        const sheetsWithEditableCells = {};
        for (const sheetName in processedSheets) {
            if (processedSheets[sheetName].editableCells.length > 0) {
                sheetsWithEditableCells[sheetName] = processedSheets[sheetName].editableCells;
            }
        }
        
        // Create input fields
        for (const sheetName in sheetsWithEditableCells) {
            const sheetDiv = document.createElement('div');
            sheetDiv.className = 'mb-6';
            
            const sheetTitle = document.createElement('h3');
            sheetTitle.className = 'text-lg font-medium text-gray-800 mb-2';
            sheetTitle.textContent = `Planilha: ${sheetName}`;
            sheetDiv.appendChild(sheetTitle);
            
            const fieldsContainer = document.createElement('div');
            fieldsContainer.className = 'space-y-3';
            
            sheetsWithEditableCells[sheetName].forEach((cell, index) => {
                const groupDiv = document.createElement('div');
                groupDiv.className = 'input-group';
                const label = document.createElement('label');
                label.htmlFor = `input-${sheetName}-${index}`;
                label.className = 'text-sm font-medium text-gray-700';
                label.textContent = `${cell.header}:`;
                label.title = `Célula ${cell.address}`;
let input;
                if (typeof cell.value === 'string' && cell.value.includes('|')) {
                    // CSV-like input
                    input = document.createElement('textarea');
                    input.id = `input-${sheetName}-${index}`;
                    input.className = 'w-full';
                    input.rows = 3;
                    input.dataset.type = 'csv';
                    input.value = cell.value;
                } else {
                    // Regular input
                    input = document.createElement('input');
                    input.id = `input-${sheetName}-${index}`;
                    input.type = typeof cell.value === 'number' ? 'number' : 'text';
                    input.value = cell.value;
                }
                
                input.dataset.sheet = sheetName;
                input.dataset.address = cell.address;
                
                groupDiv.appendChild(label);
                groupDiv.appendChild(input);
                fieldsContainer.appendChild(groupDiv);
            });
            
            sheetDiv.appendChild(fieldsContainer);
            inputFields.appendChild(sheetDiv);
        }
        
        if (inputFields.children.length === 0) {
            inputFields.innerHTML = '<p class="text-sm text-gray-500">Nenhum campo editável encontrado na planilha.</p>';
        }
    }

    function updateWorkbookValues() {
        const inputs = inputFields.querySelectorAll('input, textarea');
        
        inputs.forEach(input => {
            const sheetName = input.dataset.sheet;
            const cellAddress = input.dataset.address;
            const value = input.dataset.type === 'csv' ? input.value : input.type === 'number' ? Number(input.value) : input.value;
            
            // Update in processedSheets
            const sheet = processedSheets[sheetName];
            const cellPos = XLSX.utils.decode_cell(cellAddress);
            
            // Ensure row exists
            while (sheet.data.length <= cellPos.r) {
                sheet.data.push([]);
            }
            
            // Ensure column exists in row
            while (sheet.data[cellPos.r].length <= cellPos.c) {
                sheet.data[cellPos.r].push('');
            }
            
            sheet.data[cellPos.r][cellPos.c] = value;
        });
    }
    function showDataInputModal(sheetName) {
        const modal = document.createElement('div');
        modal.className = 'fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center z-50';
        modal.innerHTML = `
            <div class="bg-white rounded-lg p-6 w-full max-w-md">
                <h3 class="text-xl font-bold mb-4">Inserir Dados para ${sheetName}</h3>
                <p class="mb-3 text-sm text-gray-600">
                    Insira os dados no formato CSV, separados por | (pipe).<br>
                    Exemplo: Valor1|Valor2|Valor3
                </p>
                <textarea id="dataInputText" class="w-full h-40 p-3 border rounded mb-4" placeholder="Cole ou digite os dados aqui..."></textarea>
                <div class="flex justify-end space-x-3">
                    <button id="cancelInput" class="px-4 py-2 border rounded text-gray-700 hover:bg-gray-100">Cancelar</button>
                    <button id="confirmInput" class="px-4 py-2 bg-blue-600 text-white rounded hover:bg-blue-700">Confirmar</button>
                </div>
            </div>
        `;
        
        document.body.appendChild(modal);
        
        modal.querySelector('#cancelInput').addEventListener('click', () => {
            modal.remove();
        });
        
        modal.querySelector('#confirmInput').addEventListener('click', () => {
            const inputText = modal.querySelector('#dataInputText').value;
            if (inputText) {
                processInputData(sheetName, inputText);
            }
            modal.remove();
        });
    }
    
    function processInputData(sheetName, inputText) {
        try {
            const sheet = processedSheets[sheetName];
            const rows = inputText.split('\n');
            
            // Clear existing data (keep header row)
            sheet.data = [sheet.data[0]];
            
            // Parse each row
            rows.forEach(row => {
                if (row.trim() === '') return;
                const values = row.split('|').map(v => v.trim());
                sheet.data.push(values);
            });
            
            // Recalculate all formulas
            recalculateFormulas();
            
            // Regenerate reports
            generateReports();
            
            showToast('Dados processados com sucesso!', 'success');
        } catch (error) {
            console.error('Error processing input data:', error);
            showToast('Erro ao processar dados. Verifique o formato.', 'error');
        }
    }

    function recalculateFormulas() {
        for (const sheetName in processedSheets) {
            const sheet = processedSheets[sheetName];
            
            // Simple formula calculation (would need a proper formula parser in production)
            for (const cellAddress in sheet.formulas) {
                const formula = sheet.formulas[cellAddress];
                const pos = XLSX.utils.decode_cell(cellAddress);
                
                // Basic SUM formula example
                if (formula.startsWith('SUM(')) {
                    const range = formula.match(/SUM\((.*?)\)/)[1];
                    const [start, end] = range.split(':').map(XLSX.utils.decode_cell);
                    
                    let sum = 0;
                    for (let r = start.r; r <= end.r; r++) {
                        for (let c = start.c; c <= end.c; c++) {
                            const val = sheet.data[r]?.[c];
                            if (typeof val === 'number') {
                                sum += val;
                            }
                        }
                    }
                    
                    // Ensure row exists
                    while (sheet.data.length <= pos.r) {
                        sheet.data.push([]);
                    }
                    
                    // Ensure column exists in row
                    while (sheet.data[pos.r].length <= pos.c) {
                        sheet.data[pos.r].push('');
                    }
                    
                    sheet.data[pos.r][pos.c] = sum;
                }
                // TODO: Add more formula types as needed
            }
        }
    }
function generateReports() {
        reportContainer.innerHTML = '';
        
        for (const sheetName in processedSheets) {
            const sheet = processedSheets[sheetName];
            if (sheet.data.length === 0) continue;
            
            const reportDiv = document.createElement('div');
            reportDiv.className = 'p-4 bg-white rounded-lg shadow mb-4';
            
            const title = document.createElement('h3');
            title.className = 'text-lg font-semibold text-gray-800 mb-3';
            title.textContent = `Relatório: ${sheetName}`;
            reportDiv.appendChild(title);

            // Add data input button for reports 2-5
            if (['Relatório 2', 'Relatório 3', 'Relatório 4', 'Relatório 5'].includes(sheetName)) {
                const inputBtn = document.createElement('button');
                inputBtn.className = 'mb-4 bg-blue-500 hover:bg-blue-600 text-white py-2 px-4 rounded';
                inputBtn.textContent = 'Inserir Dados para ' + sheetName;
                inputBtn.onclick = () => showDataInputModal(sheetName);
                reportDiv.appendChild(inputBtn);
            }
// Create table
            const table = document.createElement('table');
            table.className = 'min-w-full divide-y divide-gray-200';
            
            // Create header
            const thead = document.createElement('thead');
            const headerRow = document.createElement('tr');
            
            sheet.data[0].forEach((header, colIndex) => {
                const th = document.createElement('th');
                th.className = 'px-4 py-2 bg-gray-50 text-left text-xs font-medium text-gray-500 uppercase tracking-wider';
                th.textContent = header || `Coluna ${colIndex+1}`;
                headerRow.appendChild(th);
            });
            
            thead.appendChild(headerRow);
            table.appendChild(thead);
            
            // Create body with first 5 data rows
            const tbody = document.createElement('tbody');
            tbody.className = 'bg-white divide-y divide-gray-200';
            
            for (let row = 1; row < Math.min(6, sheet.data.length); row++) {
                const tr = document.createElement('tr');
                tr.className = row % 2 === 0 ? 'bg-gray-50' : 'bg-white';
                
                sheet.data[row].forEach((cell, colIndex) => {
                    const td = document.createElement('td');
                    td.className = 'px-4 py-2 whitespace-nowrap text-sm text-gray-700';
                    td.textContent = cell || '-';
                    tr.appendChild(td);
                });
                
                tbody.appendChild(tr);
            }
            
            table.appendChild(tbody);
            
            // Add table to report
            const tableContainer = document.createElement('div');
            tableContainer.className = 'overflow-x-auto';
            tableContainer.appendChild(table);
            
            // Add stats
            const stats = document.createElement('div');
            stats.className = 'mt-3 text-xs text-gray-500';
            stats.innerHTML = `
                <p>Total de linhas: ${sheet.data.length-1}</p>
                <p>Colunas: ${sheet.data[0].length}</p>
                <p>Fórmulas: ${Object.keys(sheet.formulas).length}</p>
                <p>Campos editáveis: ${sheet.editableCells.length}</p>
            `;
            
            reportDiv.appendChild(tableContainer);
            reportDiv.appendChild(stats);
            reportContainer.appendChild(reportDiv);
        }
    }
function exportToExcel() {
        try {
            // Create a new workbook with updated data
            const newWorkbook = XLSX.utils.book_new();
            
            for (const sheetName in processedSheets) {
                const sheet = processedSheets[sheetName];
                const worksheet = XLSX.utils.aoa_to_sheet(sheet.data);
                
                // Reapply formulas (simplified - in real app would need proper handling)
                for (const cell in sheet.formulas) {
                    if (worksheet[cell]) {
                        worksheet[cell].f = sheet.formulas[cell];
                    }
                }
                
                XLSX.utils.book_append_sheet(newWorkbook, worksheet, sheetName);
            }
            
            // Export
            XLSX.writeFile(newWorkbook, 'athina_processed.xlsx');
            showToast('Exportado para Excel com sucesso!', 'success');
        } catch (error) {
            console.error('Error exporting to Excel:', error);
            showToast('Erro ao exportar para Excel', 'error');
        }
    }

    function exportToCSV() {
        try {
            // For simplicity, we'll export the first sheet
            const firstSheetName = workbook.SheetNames[0];
            const sheet = processedSheets[firstSheetName];
            
            // Convert to CSV
            const csv = XLSX.utils.sheet_to_csv(XLSX.utils.aoa_to_sheet(sheet.data), {
                FS: '|'  // Use | as delimiter
            });
            
            // Download
            const blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
            saveAs(blob, 'athina_data.csv');
            showToast('Exportado para CSV com sucesso!', 'success');
        } catch (error) {
            console.error('Error exporting to CSV:', error);
            showToast('Erro ao exportar para CSV', 'error');
        }
    }

    function showToast(message, type = 'info') {
        const toast = document.createElement('div');
        toast.className = `fixed bottom-4 right-4 px-4 py-2 rounded-md shadow-lg text-white ${
            type === 'success' ? 'bg-green-500' : 
            type === 'error' ? 'bg-red-500' : 'bg-blue-500'
        }`;
        toast.textContent = message;
        
        document.body.appendChild(toast);
        
        setTimeout(() => {
            toast.classList.add('opacity-0', 'transition', 'duration-300');
            setTimeout(() => toast.remove(), 300);
        }, 3000);
    }
});