{% extends "base.html" %}

{% block title %}Sales Dashboard - Home{% endblock %}

{% block extra_css %}
<style>
    body {
        background-color: #f8f9fa;
    }

    .dashboard-header {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        color: white;
        padding: 30px 20px;
        margin-bottom: 30px;
        border-radius: 0;
    }

    .dashboard-header h1 {
        font-size: 28px;
        margin: 0 0 5px 0;
        font-weight: 700;
    }

    .dashboard-header .date-range {
        font-size: 14px;
        opacity: 0.9;
    }

    .message-area {
        position: fixed;
        top: 70px;
        right: 20px;
        z-index: 1000;
        max-width: 400px;
    }
    
    .message {
        padding: 15px 20px;
        border-radius: 8px;
        margin-bottom: 10px;
        box-shadow: 0 4px 12px rgba(0,0,0,0.15);
        animation: slideIn 0.3s ease-out;
        display: flex;
        align-items: center;
        gap: 10px;
    }
    
    @keyframes slideIn {
        from {
            transform: translateX(400px);
            opacity: 0;
        }
        to {
            transform: translateX(0);
            opacity: 1;
        }
    }
    
    .message.success {
        background: #10b981;
        color: white;
    }
    
    .message.error {
        background: #ef4444;
        color: white;
    }
    
    .message.info {
        background: #3b82f6;
        color: white;
    }

    .setup-section {
        background: white;
        padding: 20px;
        border-radius: 8px;
        margin-bottom: 20px;
        box-shadow: 0 1px 3px rgba(0,0,0,0.08);
        border: 1px solid #ddd;
    }

    .setup-section h2 {
        color: #333;
        margin-bottom: 15px;
        font-size: 16px;
        font-weight: 600;
    }

    .setup-input {
        display: flex;
        gap: 10px;
        margin-bottom: 15px;
    }

    .setup-input input {
        flex: 1;
        padding: 10px;
        border: 1px solid #ddd;
        border-radius: 4px;
        font-size: 14px;
    }

    .setup-input button {
        padding: 10px 20px;
        background-color: #667eea;
        color: white;
        border: none;
        border-radius: 4px;
        cursor: pointer;
        font-weight: bold;
        transition: background-color 0.3s;
    }

    .setup-input button:hover {
        background-color: #764ba2;
    }

    .stats-grid {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
        gap: 15px;
        margin-bottom: 25px;
    }

    .stat-card {
        background: white;
        padding: 20px;
        border-radius: 8px;
        box-shadow: 0 1px 3px rgba(0,0,0,0.08);
        border: 1px solid #ddd;
    }

    .stat-card h3 {
        color: #888;
        font-size: 12px;
        margin: 0 0 10px 0;
        text-transform: uppercase;
        letter-spacing: 0.5px;
        font-weight: 600;
    }

    .stat-card .value {
        font-size: 28px;
        font-weight: 700;
        color: #667eea;
        margin: 0;
    }

    .filters-section {
        background: white;
        padding: 20px;
        border-radius: 8px;
        margin-bottom: 20px;
        box-shadow: 0 1px 3px rgba(0,0,0,0.08);
        border: 1px solid #ddd;
    }

    .filters-section h3 {
        color: #333;
        margin: 0 0 15px 0;
        font-size: 16px;
        font-weight: 600;
    }

    .filters-grid {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
        gap: 12px;
        margin-bottom: 15px;
    }

    .filter-group {
        display: flex;
        flex-direction: column;
    }

    .filter-group label {
        font-weight: 600;
        margin-bottom: 5px;
        color: #333;
        font-size: 12px;
        text-transform: uppercase;
        letter-spacing: 0.3px;
    }

    .filter-group select {
        padding: 8px;
        border: 1px solid #ddd;
        border-radius: 4px;
        font-size: 13px;
        cursor: pointer;
    }

    .export-btn {
        padding: 10px 20px;
        background: #10b981;
        color: white;
        border: none;
        border-radius: 4px;
        cursor: pointer;
        font-weight: 600;
        font-size: 14px;
        transition: background 0.3s;
    }

    .export-btn:hover {
        background: #059669;
    }

    .data-table {
        background: white;
        border-radius: 8px;
        box-shadow: 0 1px 3px rgba(0,0,0,0.08);
        overflow: hidden;
        border: 1px solid #ddd;
    }

    .data-table table {
        width: 100%;
        border-collapse: collapse;
        font-size: 13px;
    }

    .data-table thead {
        background-color: #f0f1f3;
        border-bottom: 2px solid #ddd;
    }

    .data-table th {
        padding: 12px;
        text-align: left;
        font-weight: 600;
        color: #333;
        font-size: 12px;
        text-transform: uppercase;
        letter-spacing: 0.3px;
    }

    .data-table td {
        padding: 11px 12px;
        border-bottom: 1px solid #eee;
    }

    .data-table tbody tr:hover {
        background-color: #f9f9f9;
    }

    .data-table tbody tr:nth-child(even) {
        background-color: #fafafa;
    }

    .tabs {
        display: flex;
        gap: 0;
        margin-bottom: 0;
        border-bottom: 2px solid #ddd;
        background: white;
        border-radius: 8px 8px 0 0;
    }

    .tab-button {
        padding: 12px 18px;
        background: none;
        border: none;
        cursor: pointer;
        font-size: 13px;
        font-weight: 600;
        color: #666;
        border-bottom: 3px solid transparent;
        transition: all 0.3s;
        margin-bottom: -2px;
        text-transform: uppercase;
        letter-spacing: 0.3px;
    }

    .tab-button:hover {
        color: #667eea;
    }

    .tab-button.active {
        color: #667eea;
        border-bottom-color: #667eea;
    }

    .tab-content {
        display: none;
    }

    .tab-content.active {
        display: block;
    }

    .loading {
        text-align: center;
        padding: 40px;
        color: #666;
    }

    .spinner {
        border: 4px solid #f3f3f3;
        border-top: 4px solid #667eea;
        border-radius: 50%;
        width: 40px;
        height: 40px;
        animation: spin 1s linear infinite;
        margin: 0 auto 15px;
    }

    @keyframes spin {
        0% { transform: rotate(0deg); }
        100% { transform: rotate(360deg); }
    }

    .charts-grid {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(350px, 1fr));
        gap: 15px;
        margin-bottom: 25px;
    }

    .chart-card {
        background: white;
        padding: 20px;
        border-radius: 8px;
        box-shadow: 0 1px 3px rgba(0,0,0,0.08);
        border: 1px solid #ddd;
    }

    .chart-card h3 {
        color: #333;
        font-size: 14px;
        font-weight: 600;
        margin: 0 0 15px 0;
        text-transform: uppercase;
        letter-spacing: 0.3px;
    }

    .chart-card canvas {
        max-height: 300px;
    }

    /* Modal Styles */
    .modal {
        display: none;
        position: fixed;
        z-index: 2000;
        left: 0;
        top: 0;
        width: 100%;
        height: 100%;
        background-color: rgba(0,0,0,0.4);
        animation: fadeIn 0.3s ease;
    }

    @keyframes fadeIn {
        from { opacity: 0; }
        to { opacity: 1; }
    }

    .modal.active {
        display: block;
    }

    .modal-content {
        background-color: white;
        margin: 5% auto;
        padding: 30px;
        border-radius: 8px;
        width: 90%;
        max-width: 1000px;
        box-shadow: 0 4px 20px rgba(0,0,0,0.2);
        max-height: 80vh;
        overflow-y: auto;
    }

    .modal-header {
        display: flex;
        justify-content: space-between;
        align-items: center;
        margin-bottom: 20px;
        padding-bottom: 15px;
        border-bottom: 2px solid #667eea;
    }

    .modal-header h2 {
        color: #333;
        margin: 0;
        font-size: 24px;
    }

    .modal-close {
        font-size: 28px;
        font-weight: bold;
        color: #999;
        cursor: pointer;
        border: none;
        background: none;
    }

    .modal-close:hover {
        color: #333;
    }

    .modal-table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 15px;
    }

    .modal-table thead {
        background-color: #f0f1f3;
        border-bottom: 2px solid #ddd;
    }

    .modal-table th {
        padding: 12px;
        text-align: left;
        font-weight: 600;
        color: #333;
        font-size: 12px;
        text-transform: uppercase;
        letter-spacing: 0.3px;
    }

    .modal-table td {
        padding: 11px 12px;
        border-bottom: 1px solid #eee;
    }

    .modal-table tbody tr:hover {
        background-color: #f9f9f9;
    }

    .clickable-row {
        cursor: pointer;
        transition: background-color 0.2s;
    }

    .clickable-row:hover {
        background-color: #f0f4ff !important;
    }
</style>
{% endblock %}

{% block content %}
<div id="message-area" class="message-area"></div>

<!-- Header -->
<div class="dashboard-header">
    <h1>📊 Sales Dashboard</h1>
    <div class="date-range">01/01/2025 - 31/12/2025</div>
</div>

<!-- Setup Section -->
<div style="padding: 0 20px; margin-bottom: 20px;">
    <div class="setup-section">
        <h2>Configuration</h2>
        <div class="setup-input">
            <input type="text" id="spreadsheetId" placeholder="Enter your Google Sheets ID..." />
            <button onclick="setSpreadsheetsId()">Connect to Sheet</button>
        </div>
    </div>

    <!-- KPI Cards -->
    <div class="stats-grid">
        <div class="stat-card">
            <h3>Total Faturação</h3>
            <div class="value">€ <span id="totalFat">{{ "%.0f"|format(summary.total_faturacao) }}</span></div>
        </div>
        <div class="stat-card">
            <h3>Total Quantity</h3>
            <div class="value"><span id="totalQuant">{{ summary.total_quant }}</span></div>
        </div>
        <div class="stat-card">
            <h3>Unique Clients</h3>
            <div class="value"><span id="totalClientes">{{ summary.clientes_unicos }}</span></div>
        </div>
        <div class="stat-card">
            <h3>Total Records</h3>
            <div class="value"><span id="totalRecords">{{ summary.total_records }}</span></div>
        </div>
    </div>

    <!-- Filters -->
    <div class="filters-section">
        <h3>Filters</h3>
        <div style="margin-bottom: 10px; padding: 10px; background: #f0f4ff; border-radius: 4px; font-size: 12px; display: none;" id="filterStatus">
            <strong>Active Filters:</strong> <span id="activeFiltersList"></span>
        </div>
        <div class="filters-grid">
            <div class="filter-group">
                <label for="yearFilter">Year (Quick)</label>
                <select id="yearFilter" onchange="onFilterChange()">
                    <option value="all">All Years</option>
                    <option value="2025" selected>2025</option>
                    <option value="2024">2024</option>
                    <option value="2023">2023</option>
                    <option value="2022">2022</option>
                    <option value="2021">2021</option>
                    <option value="2020">2020</option>
                    <option value="2019">2019</option>
                </select>
            </div>
            
            <div class="filter-group">
                <label for="dateFromFilter">Date From</label>
                <input type="month" id="dateFromFilter" onchange="onFilterChange()" />
            </div>
            
            <div class="filter-group">
                <label for="dateToFilter">Date To</label>
                <input type="month" id="dateToFilter" onchange="onFilterChange()" />
            </div>
            
            <div class="filter-group">
                <label for="clienteFilter">Cliente</label>
                <select id="clienteFilter" onchange="onFilterChange()">
                    <option value="all">All Clientes</option>
                    {% for cliente in filters.clientes %}
                    <option value="{{ cliente }}">{{ cliente }}</option>
                    {% endfor %}
                </select>
            </div>
            
            <div class="filter-group">
                <label for="zonaFilter">Zona</label>
                <select id="zonaFilter" onchange="onFilterChange()">
                    <option value="all">All Zonas</option>
                    {% for zona in filters.zonas %}
                    <option value="{{ zona }}">{{ zona }}</option>
                    {% endfor %}
                </select>
            </div>
            
            <div class="filter-group">
                <label for="comercialFilter">Comercial</label>
                <select id="comercialFilter" onchange="onFilterChange()">
                    <option value="all">All Comerciais</option>
                    {% for comercial in filters.comerciais %}
                    <option value="{{ comercial }}">{{ comercial }}</option>
                    {% endfor %}
                </select>
            </div>
            
            <div class="filter-group">
                <label for="familiaFilter">Familia</label>
                <select id="familiaFilter" onchange="onFilterChange()">
                    <option value="all">All Familias</option>
                    {% for familia in filters.familias %}
                    {% if familia == 'URNAS' %}
                    <option value="{{ familia }}" selected>{{ familia }}</option>
                    {% else %}
                    <option value="{{ familia }}">{{ familia }}</option>
                    {% endif %}
                    {% endfor %}
                </select>
            </div>
            
            <div class="filter-group">
                <label for="mesFilter">Mês</label>
                <select id="mesFilter" onchange="onFilterChange()">
                    <option value="all">All Meses</option>
                    {% for mes in filters.meses %}
                    <option value="{{ mes }}">{{ mes }}</option>
                    {% endfor %}
                </select>
            </div>
        </div>
        <button onclick="exportData()" class="export-btn">📥 Export to Excel</button>
    </div>

    <!-- Charts -->
    <div class="charts-grid">
        <div class="chart-card">
            <h3>Top 10 Zones by Revenue</h3>
            <canvas id="zoneChart"></canvas>
        </div>
        <div class="chart-card">
            <h3>Top 10 Sales Reps</h3>
            <canvas id="comercialChart"></canvas>
        </div>
    </div>
</div>

<div style="padding: 0 20px;">
    <div class="data-table">
        <div class="tabs">
            <button class="tab-button active" onclick="switchTab('by-client-ref')">🤝 Reference by Client</button>
            <button class="tab-button" onclick="switchTab('by-referencia')">📦 By Reference</button>
            <button class="tab-button" onclick="switchTab('by-codigo')">🏷️ By Code</button>
            <button class="tab-button" onclick="switchTab('all-data')">📋 All Transactions</button>
        </div>
        
        <!-- By Client + Reference Tab (PRIMARY) -->
        <div id="by-client-ref" class="tab-content active">
            <div id="loading-client-ref" class="loading" style="display: none;">
                <div class="spinner"></div>
                <p>Loading data...</p>
            </div>
            <table>
                <thead>
                    <tr>
                        <th>Cliente</th>
                        <th>Referencia</th>
                        <th>Código</th>
                        <th>Familia</th>
                        <th>Quantity</th>
                        <th>Faturação</th>
                    </tr>
                </thead>
                <tbody id="tableBodyClientRef">
                    <tr>
                        <td colspan="6" style="text-align: center; padding: 30px;">
                            <em>Configure your Google Sheets ID above and connect to load data</em>
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>

        <!-- By Reference Tab -->
        <div id="by-referencia" class="tab-content">
            <div id="loading-ref" class="loading" style="display: none;">
                <div class="spinner"></div>
                <p>Loading data...</p>
            </div>
            <table>
                <thead>
                    <tr>
                        <th>Referencia</th>
                        <th>Código</th>
                        <th>Familia</th>
                        <th>Total Quantity</th>
                        <th>Total Faturação</th>
                    </tr>
                </thead>
                <tbody id="tableBodyReferencia">
                    <tr>
                        <td colspan="5" style="text-align: center; padding: 30px;">
                            <em>Load data to see quantities by reference</em>
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>

        <!-- By Code Tab -->
        <div id="by-codigo" class="tab-content">
            <div id="loading-cod" class="loading" style="display: none;">
                <div class="spinner"></div>
                <p>Loading data...</p>
            </div>
            <table>
                <thead>
                    <tr>
                        <th>Código</th>
                        <th>Familia</th>
                        <th>Total Quantity</th>
                        <th>Total Faturação</th>
                    </tr>
                </thead>
                <tbody id="tableBodyCodigo">
                    <tr>
                        <td colspan="4" style="text-align: center; padding: 30px;">
                            <em>Load data to see quantities by code</em>
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>

        <!-- All Transactions Tab -->
        <div id="all-data" class="tab-content">
            <div id="loading-all" class="loading" style="display: none;">
                <div class="spinner"></div>
                <p>Loading data...</p>
            </div>
            <table id="dataTable">
                <thead>
                    <tr>
                        <th>Cliente</th>
                        <th>Zona</th>
                        <th>Comercial</th>
                        <th>Desconto</th>
                        <th>Prazo Pagamento (Dias)</th>
                        <th>Código</th>
                        <th>Referencia</th>
                        <th>Familia</th>
                        <th>Mês</th>
                        <th>Quant</th>
                        <th>Faturação</th>
                    </tr>
                </thead>
                <tbody id="tableBody">
                    <tr>
                        <td colspan="11" style="text-align: center; padding: 30px;">
                            <em>Configure your Google Sheets ID above and connect to load data</em>
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Client Detail Modal -->
<div id="clientModal" class="modal">
    <div class="modal-content">
        <div class="modal-header">
            <h2 id="modalClientName">Client Details</h2>
            <button class="modal-close" onclick="closeClientModal()">&times;</button>
        </div>
        <table class="modal-table">
            <thead>
                <tr>
                    <th>Referencia</th>
                    <th>Código</th>
                    <th>Familia</th>
                    <th>Quantity</th>
                    <th>Faturação</th>
                </tr>
            </thead>
            <tbody id="modalTableBody">
                <tr><td colspan="5" style="text-align: center; padding: 30px;">Loading...</td></tr>
            </tbody>
        </table>
    </div>
</div>

<script>
function showMessage(message, type = 'info') {
    const container = document.getElementById('message-area');
    const messageDiv = document.createElement('div');
    messageDiv.className = `message ${type}`;
    
    // Add icon based on type
    const icon = type === 'success' ? '✓' : type === 'error' ? '✕' : 'ⓘ';
    messageDiv.innerHTML = `<span style="font-size: 1.2em; font-weight: bold;">${icon}</span><span>${message}</span>`;
    
    container.appendChild(messageDiv);
    
    setTimeout(() => {
        messageDiv.style.opacity = '0';
        messageDiv.style.transform = 'translateX(400px)';
        setTimeout(() => messageDiv.remove(), 300);
    }, 5000);
}

function setSpreadsheetsId() {
    const spreadsheetId = document.getElementById('spreadsheetId').value.trim();
    
    if (!spreadsheetId) {
        showMessage('Please enter a Google Sheets ID', 'error');
        return;
    }
    
    fetch('/set-spreadsheet-id', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({ spreadsheet_id: spreadsheetId })
    })
    .then(response => response.json())
    .then(data => {
        showMessage('✅ ' + data.message, 'success');
        loadData();
    })
    .catch(error => {
        showMessage('Error: ' + error.message, 'error');
    });
}

let zoneChartInstance = null;
let comercialChartInstance = null;

let isUpdatingFilters = false;
let filterChangeTimeout = null;  // For debouncing

function applyFilters() {
    loadData();
    updateCharts();
    updateFilterStatus();
    // Update summary after a short delay to ensure data is loaded
    setTimeout(() => {
        updateSummary();
    }, 300);
}

function onFilterChange() {
    if (isUpdatingFilters) return;  // Prevent recursion
    
    // Clear any pending filter change
    if (filterChangeTimeout) {
        clearTimeout(filterChangeTimeout);
    }
    
    // Debounce: wait 400ms after last change before applying
    filterChangeTimeout = setTimeout(() => {
        console.log('Filter changed');
        isUpdatingFilters = true;
        
        updateFilterOptions().then(() => {
            isUpdatingFilters = false;
            applyFilters();
        });
    }, 400);
}

function updateFilterOptions() {
    return new Promise((resolve) => {
        const year = document.getElementById('yearFilter').value;
        const dateFrom = document.getElementById('dateFromFilter').value;
        const dateTo = document.getElementById('dateToFilter').value;
        const cliente = document.getElementById('clienteFilter').value;
        const zona = document.getElementById('zonaFilter').value;
        const comercial = document.getElementById('comercialFilter').value;
        const familia = document.getElementById('familiaFilter').value;
        const mes = document.getElementById('mesFilter').value;
        
        const params = new URLSearchParams();
        if (year !== 'all') params.append('year', year);
        if (dateFrom) params.append('date_from', dateFrom);
        if (dateTo) params.append('date_to', dateTo);
        if (cliente !== 'all') params.append('cliente', cliente);
        if (zona !== 'all') params.append('zona', zona);
        if (comercial !== 'all') params.append('comercial', comercial);
        if (familia !== 'all') params.append('familia', familia);
        if (mes !== 'all') params.append('mes', mes);
        
        console.log('Fetching filter options with:', {year, dateFrom, dateTo, cliente, zona, comercial, familia, mes});
        
        fetch(`/get-filter-options?${params}`)
            .then(response => response.json())
            .then(data => {
                console.log('Filter options received:', data);
                
                // Save current selections
                const currentCliente = document.getElementById('clienteFilter').value;
                const currentZona = document.getElementById('zonaFilter').value;
                const currentComercial = document.getElementById('comercialFilter').value;
                const currentFamilia = document.getElementById('familiaFilter').value;
                const currentMes = document.getElementById('mesFilter').value;
                
                // Update all dropdowns WITHOUT triggering change events
                if (data.clientes) {
                    updateDropdownSilent('clienteFilter', data.clientes, currentCliente);
                }
                if (data.zonas) {
                    updateDropdownSilent('zonaFilter', data.zonas, currentZona);
                }
                if (data.comerciais) {
                    updateDropdownSilent('comercialFilter', data.comerciais, currentComercial);
                }
                if (data.familias) {
                    updateDropdownSilent('familiaFilter', data.familias, currentFamilia);
                }
                if (data.meses) {
                    updateDropdownSilent('mesFilter', data.meses, currentMes);
                }
                
                resolve();
            })
            .catch(error => {
                console.error('Error updating filter options:', error);
                resolve();
            });
    });
}

function updateDropdownSilent(dropdownId, options, currentValue) {
    const dropdown = document.getElementById(dropdownId);
    
    console.log(`[updateDropdownSilent] ${dropdownId}: got ${options.length} options`);
    options.forEach((opt, idx) => {
        if (idx < 5) console.log(`  [${idx}] = "${opt}"`);
    });
    
    // Remove all options except the first one ("All")
    while (dropdown.options.length > 1) {
        dropdown.remove(1);
    }
    
    // Add new options
    options.forEach(option => {
        const opt = document.createElement('option');
        opt.value = option;
        opt.textContent = option;
        dropdown.appendChild(opt);
    });
    
    // Try to restore the value
    if (options.includes(currentValue)) {
        dropdown.value = currentValue;
        console.log(`  ${dropdownId}: restored to ${currentValue}`);
    } else if (currentValue !== 'all') {
        dropdown.value = 'all';
        console.log(`  ${dropdownId}: reset to all (${currentValue} no longer available)`);
    }
}

function updateFilterStatus() {
    const year = document.getElementById('yearFilter').value;
    const dateFrom = document.getElementById('dateFromFilter').value;
    const dateTo = document.getElementById('dateToFilter').value;
    const cliente = document.getElementById('clienteFilter').value;
    const zona = document.getElementById('zonaFilter').value;
    const comercial = document.getElementById('comercialFilter').value;
    const familia = document.getElementById('familiaFilter').value;
    const mes = document.getElementById('mesFilter').value;
    
    const activeFilters = [];
    if (year !== 'all') activeFilters.push(`Year: ${year}`);
    if (dateFrom) activeFilters.push(`From: ${dateFrom}`);
    if (dateTo) activeFilters.push(`To: ${dateTo}`);
    if (cliente !== 'all') activeFilters.push(`Cliente: ${cliente}`);
    if (zona !== 'all') activeFilters.push(`Zona: ${zona}`);
    if (comercial !== 'all') activeFilters.push(`Comercial: ${comercial}`);
    if (familia !== 'all') activeFilters.push(`Familia: ${familia}`);
    if (mes !== 'all') activeFilters.push(`Mês: ${mes}`);
    
    const filterStatus = document.getElementById('filterStatus');
    if (activeFilters.length > 0) {
        document.getElementById('activeFiltersList').textContent = activeFilters.join(' | ');
        filterStatus.style.display = 'block';
    } else {
        filterStatus.style.display = 'none';
    }
}

function onFilterChange() {
    console.log('Filter changed, updating options...');
    updateFilterOptions();  // Update all filter options
    applyFilters();  // Then apply filters and load data
}

function exportData() {
    const year = document.getElementById('yearFilter').value;
    const dateFrom = document.getElementById('dateFromFilter').value;
    const dateTo = document.getElementById('dateToFilter').value;
    const cliente = document.getElementById('clienteFilter').value;
    const zona = document.getElementById('zonaFilter').value;
    const comercial = document.getElementById('comercialFilter').value;
    const familia = document.getElementById('familiaFilter').value;
    const mes = document.getElementById('mesFilter').value;
    
    const params = new URLSearchParams();
    if (year !== 'all') params.append('year', year);
    if (dateFrom) params.append('date_from', dateFrom);
    if (dateTo) params.append('date_to', dateTo);
    if (cliente !== 'all') params.append('cliente', cliente);
    if (zona !== 'all') params.append('zona', zona);
    if (comercial !== 'all') params.append('comercial', comercial);
    if (familia !== 'all') params.append('familia', familia);
    if (mes !== 'all') params.append('mes', mes);
    
    window.location.href = `/export-data?${params}`;
}

function updateCharts() {
    updateChart('zona', 'zoneChart');
    updateChart('comercial', 'comercialChart');
}

function updateChart(type, canvasId) {
    const year = document.getElementById('yearFilter').value;
    const cliente = document.getElementById('clienteFilter').value;
    const zona = document.getElementById('zonaFilter').value;
    const comercial = document.getElementById('comercialFilter').value;
    const familia = document.getElementById('familiaFilter').value;
    const mes = document.getElementById('mesFilter').value;
    
    const params = new URLSearchParams();
    params.append('type', type);
    if (year !== 'all') params.append('year', year);
    if (cliente !== 'all') params.append('cliente', cliente);
    if (zona !== 'all') params.append('zona', zona);
    if (comercial !== 'all') params.append('comercial', comercial);
    if (familia !== 'all') params.append('familia', familia);
    if (mes !== 'all') params.append('mes', mes);
    
    fetch(`/get-breakdowns?${params}`)
        .then(response => response.json())
        .then(data => {
            if (data.error || !Array.isArray(data)) return;
            
            // Show only TOP 10, group rest as "Others"
            const topN = 10;
            let topData = data.slice(0, topN);
            
            if (data.length > topN) {
                const othersSum = data.slice(topN).reduce((sum, item) => {
                    // Find the revenue value (usually the second numeric value)
                    const values = Object.values(item);
                    const revenueValue = values.find(v => typeof v === 'number' && v > 100);
                    return sum + (revenueValue || 0);
                }, 0);
                const othersPercentage = data.slice(topN).reduce((sum, item) => sum + (item.percentage || 0), 0);
                topData.push({
                    category: 'Others',
                    revenue: othersSum,
                    percentage: othersPercentage
                });
            }
            
            // Extract labels and values properly
            const labels = topData.map(item => {
                if (item.category) return item.category;
                // Get first non-numeric value (zone/comercial name)
                const entries = Object.entries(item);
                for (let [key, value] of entries) {
                    if (typeof value === 'string' || (typeof value === 'number' && value < 100)) {
                        return value;
                    }
                }
                return 'Unknown';
            });
            
            const values = topData.map(item => {
                if (item.revenue) return item.revenue;
                // Get the revenue value (largest number)
                const numericValues = Object.values(item).filter(v => typeof v === 'number' && v > 100);
                return numericValues.length > 0 ? Math.max(...numericValues) : 0;
            });
            
            const percentages = topData.map(item => item.percentage || 0);
            
            const canvas = document.getElementById(canvasId);
            const ctx = canvas.getContext('2d');
            
            // Destroy existing chart
            if (canvasId === 'zoneChart' && zoneChartInstance) {
                zoneChartInstance.destroy();
            }
            if (canvasId === 'comercialChart' && comercialChartInstance) {
                comercialChartInstance.destroy();
            }
            
            const chartConfig = {
                type: 'bar',
                data: {
                    labels: labels,
                    datasets: [{
                        label: 'Revenue (€)',
                        data: values,
                        backgroundColor: '#667eea',
                        borderColor: '#667eea',
                        borderWidth: 1
                    }]
                },
                options: {
                    indexAxis: 'y',
                    responsive: true,
                    maintainAspectRatio: true,
                    plugins: {
                        legend: {
                            display: false
                        },
                        tooltip: {
                            callbacks: {
                                label: function(context) {
                                    const value = context.parsed.x;
                                    const percentage = percentages[context.dataIndex];
                                    return '€' + value.toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ',') + ' (' + percentage.toFixed(1) + '%)';
                                }
                            }
                        }
                    },
                    scales: {
                        x: {
                            beginAtZero: true,
                            ticks: {
                                callback: function(value) {
                                    return '€' + (value/1000).toFixed(0) + 'K';
                                }
                            }
                        }
                    }
                }
            };
            
            const newChart = new Chart(ctx, chartConfig);
            
            if (canvasId === 'zoneChart') {
                zoneChartInstance = newChart;
            } else if (canvasId === 'comercialChart') {
                comercialChartInstance = newChart;
            }
        })
        .catch(error => console.error(`Error loading ${type} chart:`, error));
}

function updateSummary() {
    const year = document.getElementById('yearFilter').value;
    const dateFrom = document.getElementById('dateFromFilter').value;
    const dateTo = document.getElementById('dateToFilter').value;
    const cliente = document.getElementById('clienteFilter').value;
    const zona = document.getElementById('zonaFilter').value;
    const comercial = document.getElementById('comercialFilter').value;
    const familia = document.getElementById('familiaFilter').value;
    const mes = document.getElementById('mesFilter').value;
    
    // Use ALL filters including familia (URNAS) for ALL KPIs
    const params = new URLSearchParams();
    if (year !== 'all') params.append('year', year);
    if (dateFrom) params.append('date_from', dateFrom);
    if (dateTo) params.append('date_to', dateTo);
    if (cliente !== 'all') params.append('cliente', cliente);
    if (zona !== 'all') params.append('zona', zona);
    if (comercial !== 'all') params.append('comercial', comercial);
    if (familia !== 'all') params.append('familia', familia);
    if (mes !== 'all') params.append('mes', mes);
    
    console.log('Updating summary with params:', params.toString());
    
    fetch(`/get-summary?${params}`)
        .then(response => response.json())
        .then(data => {
            console.log('Summary response:', data);
            document.getElementById('totalRecords').textContent = data.total_records;
            document.getElementById('totalFat').textContent = parseFloat(data.total_faturacao).toFixed(0);
            document.getElementById('totalClientes').textContent = data.clientes_unicos;
            document.getElementById('totalQuant').textContent = parseFloat(data.total_quant).toFixed(0);
        })
        .catch(error => console.error('Error updating summary:', error));
}

function loadData() {
    const year = document.getElementById('yearFilter').value;
    const dateFrom = document.getElementById('dateFromFilter').value;
    const dateTo = document.getElementById('dateToFilter').value;
    const cliente = document.getElementById('clienteFilter').value;
    const zona = document.getElementById('zonaFilter').value;
    const comercial = document.getElementById('comercialFilter').value;
    const familia = document.getElementById('familiaFilter').value;
    const mes = document.getElementById('mesFilter').value;
    
    const params = new URLSearchParams();
    if (year !== 'all') params.append('year', year);
    if (dateFrom) params.append('date_from', dateFrom);
    if (dateTo) params.append('date_to', dateTo);
    if (cliente !== 'all') params.append('cliente', cliente);
    if (zona !== 'all') params.append('zona', zona);
    if (comercial !== 'all') params.append('comercial', comercial);
    if (familia !== 'all') params.append('familia', familia);
    if (mes !== 'all') params.append('mes', mes);
    
    console.log('Filter values:', { year, cliente, zona, comercial, familia, mes });
    console.log('Query string:', params.toString());
    
    // Load all data
    loadAllData(params);
    // Load aggregated data
    loadDataByReferencia(params);
    loadDataByCodigo(params);
    loadDataByClientRef(params);
}

function loadAllData(params) {
    document.getElementById('loading-all').style.display = 'block';
    
    fetch(`/get-sheets-data?${params}`)
        .then(response => response.json())
        .then(data => {
            populateTable(data);
            document.getElementById('loading-all').style.display = 'none';
        })
        .catch(error => {
            showMessage('Error loading data: ' + error.message, 'error');
            document.getElementById('loading-all').style.display = 'none';
        });
}

function loadDataByReferencia(params) {
    document.getElementById('loading-ref').style.display = 'block';
    
    fetch(`/get-quantities-by-referencia?${params}`)
        .then(response => response.json())
        .then(data => {
            if (data.error) {
                showMessage('Error: ' + data.error, 'error');
            } else if (Array.isArray(data)) {
                populateTableReferencia(data);
            } else {
                showMessage('Error: Invalid data format', 'error');
            }
            document.getElementById('loading-ref').style.display = 'none';
        })
        .catch(error => {
            showMessage('Error loading reference data: ' + error.message, 'error');
            document.getElementById('loading-ref').style.display = 'none';
        });
}

function loadDataByCodigo(params) {
    document.getElementById('loading-cod').style.display = 'block';
    
    fetch(`/get-quantities-by-codigo?${params}`)
        .then(response => response.json())
        .then(data => {
            if (data.error) {
                showMessage('Error: ' + data.error, 'error');
            } else if (Array.isArray(data)) {
                populateTableCodigo(data);
            } else {
                showMessage('Error: Invalid data format', 'error');
            }
            document.getElementById('loading-cod').style.display = 'none';
        })
        .catch(error => {
            showMessage('Error loading code data: ' + error.message, 'error');
            document.getElementById('loading-cod').style.display = 'none';
        });
}

function loadDataByClientRef(params) {
    document.getElementById('loading-client-ref').style.display = 'block';
    console.log('Loading client+ref with params:', params.toString());
    
    fetch(`/get-reference-by-client?${params}`)
        .then(response => {
            console.log('Response status:', response.status);
            return response.json();
        })
        .then(data => {
            console.log('Client+ref data:', data);
            if (data.error) {
                showMessage('Error: ' + data.error, 'error');
            } else if (Array.isArray(data)) {
                populateTableClientRef(data);
            } else {
                showMessage('Error: Invalid data format', 'error');
            }
            document.getElementById('loading-client-ref').style.display = 'none';
        })
        .catch(error => {
            console.error('Error loading reference by client:', error);
            showMessage('Error loading reference by client: ' + error.message, 'error');
            document.getElementById('loading-client-ref').style.display = 'none';
        });
}

function populateTable(data) {
    const tbody = document.getElementById('tableBody');
    
    if (data.length === 0) {
        tbody.innerHTML = '<tr><td colspan="11" style="text-align: center; padding: 30px;"><em>No data found</em></td></tr>';
        return;
    }
    
    tbody.innerHTML = data.map(row => `
        <tr>
            <td>${escapeHtml(row.Cliente || '')}</td>
            <td>${escapeHtml(row.Zona || '')}</td>
            <td>${escapeHtml(row.Comercial || '')}</td>
            <td>${row.Desconto || '-'}</td>
            <td>${row['Prazo Pagamento Dias'] || '-'}</td>
            <td>${escapeHtml(row.Código || '')}</td>
            <td>${escapeHtml(row.Referencia || '')}</td>
            <td>${escapeHtml(row.Familia || '')}</td>
            <td>${escapeHtml(row.Mês || '')}</td>
            <td>${row.Quant || '-'}</td>
            <td>€ ${parseFloat(row.Faturaçao || 0).toFixed(2)}</td>
        </tr>
    `).join('');
}

function escapeHtml(text) {
    const div = document.createElement('div');
    div.textContent = text;
    return div.innerHTML;
}

function switchTab(tabName) {
    // Hide all tabs
    const tabs = document.querySelectorAll('.tab-content');
    tabs.forEach(tab => tab.classList.remove('active'));
    
    // Remove active class from all buttons
    const buttons = document.querySelectorAll('.tab-button');
    buttons.forEach(btn => btn.classList.remove('active'));
    
    // Show selected tab
    document.getElementById(tabName).classList.add('active');
    
    // Mark button as active
    event.target.classList.add('active');
}

function populateTableReferencia(data) {
    const tbody = document.getElementById('tableBodyReferencia');
    
    if (data.length === 0) {
        tbody.innerHTML = '<tr><td colspan="5" style="text-align: center; padding: 30px;"><em>No data found</em></td></tr>';
        return;
    }
    
    tbody.innerHTML = data.map(row => `
        <tr>
            <td><strong>${escapeHtml(row.Referencia || '')}</strong></td>
            <td>${escapeHtml(row.Código || '')}</td>
            <td>${escapeHtml(row.Familia || '')}</td>
            <td style="text-align: right; font-weight: bold; color: #667eea;">${parseInt(row.Quant || 0)}</td>
            <td style="text-align: right;">€ ${parseFloat(row.Faturaçao || 0).toFixed(2)}</td>
        </tr>
    `).join('');
}

function populateTableCodigo(data) {
    const tbody = document.getElementById('tableBodyCodigo');
    
    if (data.length === 0) {
        tbody.innerHTML = '<tr><td colspan="4" style="text-align: center; padding: 30px;"><em>No data found</em></td></tr>';
        return;
    }
    
    tbody.innerHTML = data.map(row => `
        <tr>
            <td><strong>${escapeHtml(row.Código || '')}</strong></td>
            <td>${escapeHtml(row.Familia || '')}</td>
            <td style="text-align: right; font-weight: bold; color: #667eea;">${parseInt(row.Quant || 0)}</td>
            <td style="text-align: right;">€ ${parseFloat(row.Faturaçao || 0).toFixed(2)}</td>
        </tr>
    `).join('');
}

function populateTableClientRef(data) {
    const tbody = document.getElementById('tableBodyClientRef');
    
    if (data.length === 0) {
        tbody.innerHTML = '<tr><td colspan="6" style="text-align: center; padding: 30px;"><em>No data found</em></td></tr>';
        return;
    }
    
    tbody.innerHTML = data.map((row, index) => `
        <tr class="clickable-row" data-client="${escapeHtml(row.Cliente || '')}" data-index="${index}">
            <td>${escapeHtml(row.Cliente || '')}</td>
            <td><strong>${escapeHtml(row.Referencia || '')}</strong></td>
            <td>${escapeHtml(row.Código || '')}</td>
            <td>${escapeHtml(row.Familia || '')}</td>
            <td style="text-align: right; font-weight: bold; color: #667eea;">${parseInt(row.Quant || 0)}</td>
            <td style="text-align: right;">€ ${parseFloat(row.Faturaçao || 0).toFixed(2)}</td>
        </tr>
    `).join('');
    
    // Add click event listeners to rows
    document.querySelectorAll('#tableBodyClientRef .clickable-row').forEach(row => {
        row.addEventListener('click', function() {
            const clientName = this.getAttribute('data-client');
            console.log('Clicked client:', clientName);
            openClientDetail(clientName);
        });
    });
}

// Load data on page load if sheet was previously configured
window.addEventListener('load', () => {
    // First update filter options based on current (default) filters
    updateFilterOptions().then(() => {
        // Then load all data on page load
        applyFilters();
    });
});

// Modal Functions
let currentClientData = [];

function openClientDetail(clientName) {
    console.log('Opening detail for client:', clientName);
    
    // Get current filters
    const year = document.getElementById('yearFilter').value;
    const cliente = document.getElementById('clienteFilter').value;
    const zona = document.getElementById('zonaFilter').value;
    const comercial = document.getElementById('comercialFilter').value;
    const familia = document.getElementById('familiaFilter').value;
    const mes = document.getElementById('mesFilter').value;
    
    // Set modal title
    document.getElementById('modalClientName').textContent = `${clientName} - References & Quantities`;
    
    // Build params for filtered query
    const params = new URLSearchParams();
    params.append('cliente', clientName);  // Filter by this specific client
    if (year !== 'all') params.append('year', year);
    if (zona !== 'all') params.append('zona', zona);
    if (comercial !== 'all') params.append('comercial', comercial);
    if (familia !== 'all') params.append('familia', familia);
    if (mes !== 'all') params.append('mes', mes);
    
    console.log('Fetching with params:', params.toString());
    
    // Fetch client-specific data from get-reference-by-client with client filter
    fetch(`/get-reference-by-client?${params}`)
        .then(response => {
            console.log('Response status:', response.status);
            return response.json();
        })
        .then(data => {
            console.log('Client detail data:', data);
            if (Array.isArray(data)) {
                populateModalTable(data);
            } else if (data.error) {
                document.getElementById('modalTableBody').innerHTML = 
                    `<tr><td colspan="5" style="text-align: center; color: red;">Error: ${data.error}</td></tr>`;
            } else {
                console.error('Invalid data format:', data);
                document.getElementById('modalTableBody').innerHTML = 
                    `<tr><td colspan="5" style="text-align: center; color: red;">Invalid data format</td></tr>`;
            }
        })
        .catch(error => {
            console.error('Error fetching client details:', error);
            document.getElementById('modalTableBody').innerHTML = 
                `<tr><td colspan="5" style="text-align: center; color: red;">Error loading details: ${error.message}</td></tr>`;
        });
    
    // Show modal
    document.getElementById('clientModal').classList.add('active');
}

function populateModalTable(data) {
    const tbody = document.getElementById('modalTableBody');
    
    if (data.length === 0) {
        tbody.innerHTML = '<tr><td colspan="5" style="text-align: center; padding: 30px;"><em>No references found for this client</em></td></tr>';
        return;
    }
    
    tbody.innerHTML = data.map(row => `
        <tr>
            <td><strong>${escapeHtml(row.Referencia || '')}</strong></td>
            <td>${escapeHtml(row.Código || '')}</td>
            <td>${escapeHtml(row.Familia || '')}</td>
            <td style="text-align: right; font-weight: bold; color: #667eea;">${parseInt(row.Quant || 0)}</td>
            <td style="text-align: right;">€ ${parseFloat(row.Faturaçao || 0).toFixed(2)}</td>
        </tr>
    `).join('');
}

function closeClientModal() {
    document.getElementById('clientModal').classList.remove('active');
}

// Close modal when clicking outside
window.addEventListener('click', (event) => {
    const modal = document.getElementById('clientModal');
    if (event.target === modal) {
        closeClientModal();
    }
});
</script>
{% endblock %}