<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Restaurant Analytics Dashboard</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/chart.js@3.7.1/dist/chart.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    <style>
        :root {
            --primary-color: #2c3e50;
            --secondary-color: #34495e;
            --accent-color: #3498db;
            --success-color: #27ae60;
            --warning-color: #f39c12;
            --danger-color: #e74c3c;
            --light-color: #ecf0f1;
            --dark-color: #2c3e50;
        }
        
        body {
            background-color: #f5f7fa;
            color: #333;
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            min-height: 100vh;
        }
        
        .dashboard-header {
            background-color: var(--primary-color);
            color: white;
            padding: 1.5rem 0;
            margin-bottom: 2rem;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        
        .card {
            border: none;
            border-radius: 8px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.05);
            margin-bottom: 20px;
            transition: all 0.3s ease;
            background-color: white;
        }
        
        .card:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 20px rgba(0,0,0,0.1);
        }
        
        .card-header {
            background-color: var(--primary-color);
            color: white;
            border-radius: 8px 8px 0 0 !important;
            padding: 1rem 1.25rem;
            font-weight: 600;
        }
        
        .nav-tabs {
            border-bottom: 1px solid #dee2e6;
        }
        
        .nav-tabs .nav-link {
            color: var(--secondary-color);
            border: none;
            margin-right: 5px;
            border-radius: 5px 5px 0 0;
            padding: 0.75rem 1.25rem;
            font-weight: 500;
        }
        
        .nav-tabs .nav-link:hover {
            border-color: transparent;
            color: var(--accent-color);
        }
        
        .nav-tabs .nav-link.active {
            color: var(--accent-color);
            background-color: transparent;
            border-bottom: 3px solid var(--accent-color);
            font-weight: 600;
        }
        
        .table-responsive {
            border-radius: 8px;
            overflow: hidden;
        }
        
        .table {
            margin-bottom: 0;
        }
        
        .table th {
            background-color: #f8f9fa;
            color: var(--secondary-color);
            font-weight: 600;
            border-bottom: 2px solid #dee2e6;
        }
        
        .table td {
            border-top: 1px solid #e9ecef;
            vertical-align: middle;
        }
        
        .form-control, .form-select {
            border: 1px solid #ced4da;
            border-radius: 4px;
            padding: 0.5rem 0.75rem;
        }
        
        .form-control:focus, .form-select:focus {
            border-color: var(--accent-color);
            box-shadow: 0 0 0 0.2rem rgba(52, 152, 219, 0.25);
        }
        
        .btn-primary {
            background-color: var(--accent-color);
            border-color: var(--accent-color);
        }
        
        .btn-primary:hover {
            background-color: #2980b9;
            border-color: #2980b9;
        }
        
        .stat-card {
            text-align: center;
            padding: 1.5rem;
            border-radius: 8px;
            margin-bottom: 1.5rem;
            color: white;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        
        .stat-card .value {
            font-size: 2.5rem;
            font-weight: bold;
            margin-bottom: 0.5rem;
        }
        
        .stat-card .label {
            font-size: 1rem;
            opacity: 0.9;
        }
        
        .primary-bg { background-color: var(--primary-color); }
        .secondary-bg { background-color: var(--secondary-color); }
        .accent-bg { background-color: var(--accent-color); }
        .success-bg { background-color: var(--success-color); }
        .warning-bg { background-color: var(--warning-color); }
        .danger-bg { background-color: var(--danger-color); }
        
        .filter-section {
            background-color: white;
            padding: 1.5rem;
            border-radius: 8px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.05);
            margin-bottom: 2rem;
        }
        
        .loading-overlay {
            position: fixed;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            background-color: rgba(255,255,255,0.8);
            display: flex;
            justify-content: center;
            align-items: center;
            z-index: 9999;
            display: none;
        }
        
        .spinner {
            width: 3rem;
            height: 3rem;
        }
    </style>
</head>
<body>
    <!-- Loading Overlay -->
    <div class="loading-overlay">
        <div class="spinner-border text-primary spinner" role="status">
            <span class="visually-hidden">Loading...</span>
        </div>
    </div>

    <!-- Header -->
    <div class="dashboard-header">
        <div class="container">
            <div class="row align-items-center">
                <div class="col-md-8">
                    <h1><i class="fas fa-chart-line me-2"></i> Restaurant Analytics Dashboard</h1>
                    <p class="mb-0">Comprehensive insights for your restaurant business</p>
                </div>
                <div class="col-md-4 text-md-end">
                    <span class="badge bg-light text-dark me-2"><i class="fas fa-calendar-alt me-1"></i> <span id="currentDate"></span></span>
                    <span class="badge bg-light text-dark"><i class="fas fa-clock me-1"></i> <span id="currentTime"></span></span>
                </div>
            </div>
        </div>
    </div>

    <div class="container">
        <!-- Filters -->
        <div class="filter-section">
            <div class="row g-3">
                <div class="col-md-3">
                    <label class="form-label">Start Date</label>
                    <input type="date" id="startDate" class="form-control">
                </div>
                <div class="col-md-3">
                    <label class="form-label">End Date</label>
                    <input type="date" id="endDate" class="form-control">
                </div>
                <div class="col-md-2">
                    <label class="form-label">Report Type</label>
                    <select id="reportType" class="form-select">
                        <option value="daily">Daily</option>
                        <option value="weekly">Weekly</option>
                        <option value="monthly">Monthly</option>
                        <option value="annual">Annual</option>
                    </select>
                </div>
                <div class="col-md-2">
                    <label class="form-label">Category</label>
                    <select id="categoryFilter" class="form-select">
                        <option value="all">All Categories</option>
                        <option value="beverages">Beverages</option>
                        <option value="food">Food</option>
                        <option value="other">Other</option>
                    </select>
                </div>
                <div class="col-md-2 d-flex align-items-end">
                    <button id="applyFilters" class="btn btn-primary w-100">
                        <i class="fas fa-filter me-2"></i> Apply Filters
                    </button>
                </div>
            </div>
        </div>
        
        <!-- Summary Stats -->
        <div class="row mb-4">
            <div class="col-md-3">
                <div class="stat-card accent-bg">
                    <div class="value" id="totalSales">$0</div>
                    <div class="label">Total Sales</div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card success-bg">
                    <div class="value" id="totalProfit">$0</div>
                    <div class="label">Total Profit</div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card warning-bg">
                    <div class="value" id="avgTransaction">$0</div>
                    <div class="label">Avg. Transaction</div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card danger-bg">
                    <div class="value" id="wasteCost">$0</div>
                    <div class="label">Waste Cost</div>
                </div>
            </div>
        </div>
        
        <!-- Main Content -->
        <div class="row">
            <div class="col-12">
                <ul class="nav nav-tabs" id="dashboardTabs" role="tablist">
                    <li class="nav-item" role="presentation">
                        <button class="nav-link active" id="profit-tab" data-bs-toggle="tab" data-bs-target="#profit" type="button" role="tab">Profit Analysis</button>
                    </li>
                    <li class="nav-item" role="presentation">
                        <button class="nav-link" id="sales-tab" data-bs-toggle="tab" data-bs-target="#sales" type="button" role="tab">Sales Reports</button>
                    </li>
                    <li class="nav-item" role="presentation">
                        <button class="nav-link" id="employees-tab" data-bs-toggle="tab" data-bs-target="#employees" type="button" role="tab">Employee Performance</button>
                    </li>
                    <li class="nav-item" role="presentation">
                        <button class="nav-link" id="inventory-tab" data-bs-toggle="tab" data-bs-target="#inventory" type="button" role="tab">Inventory Analysis</button>
                    </li>
                </ul>
                
                <div class="tab-content p-0 border-0" id="dashboardTabsContent">
                    <!-- Profit Analysis Tab -->
                    <div class="tab-pane fade show active" id="profit" role="tabpanel">
                        <div class="card mt-0">
                            <div class="card-header">
                                <i class="fas fa-chart-pie me-2"></i> Profit Analysis
                            </div>
                            <div class="card-body">
                                <div class="row mb-4">
                                    <div class="col-md-8">
                                        <canvas id="profitTrendChart"></canvas>
                                    </div>
                                    <div class="col-md-4">
                                        <canvas id="profitByCategoryChart"></canvas>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-12">
                                        <div class="d-flex justify-content-between mb-3">
                                            <h4>Detailed Profit Analysis</h4>
                                            <button class="btn btn-primary btn-sm" onclick="exportData('profit')">
                                                <i class="fas fa-download me-2"></i>Export
                                            </button>
                                        </div>
                                        <div class="table-responsive">
                                            <table class="table table-hover" id="profitTable">
                                                <thead>
                                                    <tr>
                                                        <th>Category</th>
                                                        <th>Period</th>
                                                        <th>Sales</th>
                                                        <th>Cost</th>
                                                        <th>Profit</th>
                                                        <th>Margin</th>
                                                    </tr>
                                                </thead>
                                                <tbody></tbody>
                                            </table>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Sales Reports Tab -->
                    <div class="tab-pane fade" id="sales" role="tabpanel">
                        <div class="card mt-0">
                            <div class="card-header">
                                <i class="fas fa-shopping-cart me-2"></i> Sales Reports
                            </div>
                            <div class="card-body">
                                <div class="row mb-4">
                                    <div class="col-md-6">
                                        <canvas id="salesTrendChart"></canvas>
                                    </div>
                                    <div class="col-md-6">
                                        <canvas id="paymentMethodChart"></canvas>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-12">
                                        <div class="d-flex justify-content-between mb-3">
                                            <h4>Sales Transaction Details</h4>
                                            <button class="btn btn-primary btn-sm" onclick="exportData('sales')">
                                                <i class="fas fa-download me-2"></i>Export
                                            </button>
                                        </div>
                                        <div class="table-responsive">
                                            <table class="table table-hover" id="salesTable">
                                                <thead>
                                                    <tr>
                                                        <th>Period</th>
                                                        <th>Transactions</th>
                                                        <th>Total Sales</th>
                                                        <th>Cash Received</th>
                                                        <th>Employees</th>
                                                    </tr>
                                                </thead>
                                                <tbody></tbody>
                                            </table>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Employee Performance Tab -->
                    <div class="tab-pane fade" id="employees" role="tabpanel">
                        <div class="card mt-0">
                            <div class="card-header">
                                <i class="fas fa-users me-2"></i> Employee Performance
                            </div>
                            <div class="card-body">
                                <div class="row mb-4">
                                    <div class="col-md-6">
                                        <canvas id="employeeSalesChart"></canvas>
                                    </div>
                                    <div class="col-md-6">
                                        <canvas id="employeeAvgChart"></canvas>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-12">
                                        <div class="d-flex justify-content-between mb-3">
                                            <h4>Employee Performance Details</h4>
                                            <button class="btn btn-primary btn-sm" onclick="exportData('employees')">
                                                <i class="fas fa-download me-2"></i>Export
                                            </button>
                                        </div>
                                        <div class="table-responsive">
                                            <table class="table table-hover" id="employeesTable">
                                                <thead>
                                                    <tr>
                                                        <th>Employee</th>
                                                        <th>Role</th>
                                                        <th>Transactions</th>
                                                        <th>Total Sales</th>
                                                        <th>Avg. Sale</th>
                                                    </tr>
                                                </thead>
                                                <tbody></tbody>
                                            </table>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Inventory Analysis Tab -->
                    <div class="tab-pane fade" id="inventory" role="tabpanel">
                        <div class="card mt-0">
                            <div class="card-header">
                                <i class="fas fa-boxes me-2"></i> Inventory Analysis
                            </div>
                            <div class="card-body">
                                <div class="row mb-4">
                                    <div class="col-md-6">
                                        <canvas id="inventoryTurnoverChart"></canvas>
                                    </div>
                                    <div class="col-md-6">
                                        <canvas id="wasteAnalysisChart"></canvas>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-12">
                                        <div class="d-flex justify-content-between mb-3">
                                            <h4>Inventory Turnover Details</h4>
                                            <button class="btn btn-primary btn-sm" onclick="exportData('inventory')">
                                                <i class="fas fa-download me-2"></i>Export
                                            </button>
                                        </div>
                                        <div class="table-responsive">
                                            <table class="table table-hover" id="inventoryTable">
                                                <thead>
                                                    <tr>
                                                        <th>Ingredient</th>
                                                        <th>Current Stock</th>
                                                        <th>Purchased</th>
                                                        <th>Disposed</th>
                                                        <th>Turnover Rate</th>
                                                    </tr>
                                                </thead>
                                                <tbody></tbody>
                                            </table>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@3.7.1/dist/chart.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2.0.0"></script>
    <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>
    <script>
        // Global variables
        let profitTrendChart, profitByCategoryChart, salesTrendChart, paymentMethodChart;
        let employeeSalesChart, employeeAvgChart, inventoryTurnoverChart, wasteAnalysisChart;
        
        // Initialize dashboard
        document.addEventListener('DOMContentLoaded', function() {
            // Set current date and time
            updateDateTime();
            setInterval(updateDateTime, 60000);
            
            // Set default dates (current month)
            const today = new Date();
            const firstDay = new Date(today.getFullYear(), today.getMonth(), 1);
            const lastDay = new Date(today.getFullYear(), today.getMonth() + 1, 0);
            
            document.getElementById('startDate').valueAsDate = firstDay;
            document.getElementById('endDate').valueAsDate = lastDay;
            
            // Set report type to monthly by default
            document.getElementById('reportType').value = 'monthly';
            
            // Load initial data
            loadDashboardData();
            
            // Apply filters event
            document.getElementById('applyFilters').addEventListener('click', function() {
                showLoading();
                loadDashboardData();
            });
        });
        
        function updateDateTime() {
            const now = new Date();
            document.getElementById('currentDate').textContent = now.toLocaleDateString();
            document.getElementById('currentTime').textContent = now.toLocaleTimeString();
        }
        
        function showLoading() {
            document.querySelector('.loading-overlay').style.display = 'flex';
        }
        
        function hideLoading() {
            document.querySelector('.loading-overlay').style.display = 'none';
        }
        
        // Load all dashboard data
        function loadDashboardData() {
            const startDate = document.getElementById('startDate').value;
            const endDate = document.getElementById('endDate').value;
            const reportType = document.getElementById('reportType').value;
            const category = document.getElementById('categoryFilter').value;
            
            // Show loading indicator
            showLoading();
            
            // Array to track all API calls
            const apiCalls = [];
            
            // Load profit analysis
            apiCalls.push(
                fetchData('get_profit_analysis', {start_date: startDate, end_date: endDate, report_type: reportType, category: category})
                    .then(data => {
                        updateProfitAnalysis(data);
                    })
            );
                
            // Load employee performance
            apiCalls.push(
                fetchData('get_employee_performance', {start_date: startDate, end_date: endDate})
                    .then(data => {
                        updateEmployeePerformance(data);
                    })
            );
                
            // Load inventory turnover
            apiCalls.push(
                fetchData('get_inventory_turnover', {})
                    .then(data => {
                        updateInventoryTurnover(data);
                    })
            );
                
            // Load sales reports
            apiCalls.push(
                fetchData('get_sales_reports', {start_date: startDate, end_date: endDate, report_type: reportType})
                    .then(data => {
                        updateSalesReports(data);
                    })
            );
                
            // Load summary stats
            apiCalls.push(
                calculateSummaryStats(startDate, endDate)
            );
            
            // Hide loading when all API calls complete
            Promise.all(apiCalls).finally(() => {
                hideLoading();
            });
        }
        
        // Generic function to fetch data from API
        function fetchData(action, params) {
            const queryString = new URLSearchParams({
                action: action,
                ...params
            }).toString();
            
            return fetch(`./database/dashboard_api.kts.php?${queryString}`)
                .then(response => {
                    if (!response.ok) {
                        throw new Error('Network response was not ok');
                    }
                    return response.json();
                })
                .catch(error => {
                    console.error('Error fetching data:', error);
                    showErrorToast('Failed to load data. Please try again.');
                    return [];
                });
        }
        
        function showErrorToast(message) {
            // You could implement a toast notification here
            console.error(message);
        }
        
        // Update profit analysis section
        function updateProfitAnalysis(data) {
            if (!data || data.length === 0) {
                console.warn('No profit analysis data received');
                return;
            }
            
            // Update table
            const tableBody = document.querySelector('#profitTable tbody');
            tableBody.innerHTML = '';
            
            // Initialize totals
            let totalSales = 0;
            let totalCost = 0;
            let totalProfit = 0;
            
            data.forEach(item => {
                const row = document.createElement('tr');
                row.innerHTML = `
                    <td>${item.category || 'N/A'}</td>
                    <td>${item.period || 'N/A'}</td>
                    <td>$${parseFloat(item.total_sales || 0).toFixed(2)}</td>
                    <td>$${parseFloat(item.total_cost || 0).toFixed(2)}</td>
                    <td>$${parseFloat(item.gross_profit || 0).toFixed(2)}</td>
                    <td>${parseFloat(item.profit_margin || 0).toFixed(2)}%</td>
                `;
                tableBody.appendChild(row);
                
                // Add to totals
                totalSales += parseFloat(item.total_sales || 0);
                totalCost += parseFloat(item.total_cost || 0);
                totalProfit += parseFloat(item.gross_profit || 0);
            });
            
            // Add totals row
            const totalsRow = document.createElement('tr');
            totalsRow.style.fontWeight = 'bold';
            totalsRow.style.backgroundColor = '#f8f9fa';
            totalsRow.innerHTML = `
                <td colspan="2">Total</td>
                <td>$${totalSales.toFixed(2)}</td>
                <td>$${totalCost.toFixed(2)}</td>
                <td>$${totalProfit.toFixed(2)}</td>
                <td>${(totalProfit / totalSales * 100).toFixed(2)}%</td>
            `;
            tableBody.appendChild(totalsRow);
            
            // Prepare data for charts
            const categories = [...new Set(data.map(item => item.category))];
            const periods = [...new Set(data.map(item => item.period))];
            
            // Profit Trend Chart
            const trendData = {
                labels: periods,
                datasets: categories.map(category => {
                    const categoryData = data.filter(item => item.category === category);
                    return {
                        label: category,
                        data: periods.map(period => {
                            const item = categoryData.find(d => d.period === period);
                            return item ? parseFloat(item.gross_profit) : 0;
                        }),
                        borderWidth: 2,
                        tension: 0.4,
                        backgroundColor: getCategoryColor(category, 0.2),
                        borderColor: getCategoryColor(category, 1),
                        fill: true
                    };
                })
            };
            
            if (profitTrendChart) {
                profitTrendChart.data = trendData;
                profitTrendChart.update();
            } else {
                profitTrendChart = new Chart(
                    document.getElementById('profitTrendChart'),
                    {
                        type: 'line',
                        data: trendData,
                        options: {
                            responsive: true,
                            plugins: {
                                title: {
                                    display: true,
                                    text: 'Profit Trend Over Time',
                                    font: {
                                        size: 16
                                    }
                                },
                                legend: {
                                    position: 'bottom',
                                    labels: {
                                        padding: 20,
                                        usePointStyle: true
                                    }
                                }
                            },
                            scales: {
                                y: {
                                    beginAtZero: true,
                                    title: {
                                        display: true,
                                        text: 'Amount ($)'
                                    }
                                }
                            }
                        }
                    }
                );
            }
            
            // Profit by Category Chart
            const categoryProfit = categories.map(category => {
                const categoryItems = data.filter(item => item.category === category);
                return categoryItems.reduce((sum, item) => sum + parseFloat(item.gross_profit || 0), 0);
            });
            
            const categoryColors = categories.map(category => getCategoryColor(category, 0.7));
            
            if (profitByCategoryChart) {
                profitByCategoryChart.data.labels = categories;
                profitByCategoryChart.data.datasets[0].data = categoryProfit;
                profitByCategoryChart.data.datasets[0].backgroundColor = categoryColors;
                profitByCategoryChart.update();
            } else {
                profitByCategoryChart = new Chart(
                    document.getElementById('profitByCategoryChart'),
                    {
                        type: 'doughnut',
                        data: {
                            labels: categories,
                            datasets: [{
                                data: categoryProfit,
                                backgroundColor: categoryColors,
                                borderWidth: 1
                            }]
                        },
                        options: {
                            responsive: true,
                            plugins: {
                                title: {
                                    display: true,
                                    text: 'Profit by Category',
                                    font: {
                                        size: 16
                                    }
                                },
                                legend: {
                                    position: 'bottom',
                                    labels: {
                                        padding: 20,
                                        usePointStyle: true
                                    }
                                },
                                tooltip: {
                                    callbacks: {
                                        label: function(context) {
                                            const label = context.label || '';
                                            const value = context.raw || 0;
                                            const total = context.dataset.data.reduce((a, b) => a + b, 0);
                                            const percentage = Math.round((value / total) * 100);
                                            return `${label}: $${value.toFixed(2)} (${percentage}%)`;
                                        }
                                    }
                                }
                            }
                        }
                    }
                );
            }
        }
        
        // Helper function to get consistent colors for categories
        function getCategoryColor(category, opacity = 1) {
            const colors = {
                'beverages': `rgba(52, 152, 219, ${opacity})`,
                'food': `rgba(46, 204, 113, ${opacity})`,
                'other': `rgba(155, 89, 182, ${opacity})`,
                'default': `rgba(241, 196, 15, ${opacity})`
            };
            
            return colors[category.toLowerCase()] || colors['default'];
        }
        
        // Update employee performance section
        function updateEmployeePerformance(data) {
            if (!data || data.length === 0) {
                console.warn('No employee performance data received');
                return;
            }
            
            // Update table
            const tableBody = document.querySelector('#employeesTable tbody');
            tableBody.innerHTML = '';
            
            data.forEach(item => {
                const row = document.createElement('tr');
                row.innerHTML = `
                    <td>${item.name || 'N/A'}</td>
                    <td>${item.role || 'N/A'}</td>
                    <td>${item.transactions || 0}</td>
                    <td>$${parseFloat(item.total_sales || 0).toFixed(2)}</td>
                    <td>$${parseFloat(item.avg_sale || 0).toFixed(2)}</td>
                `;
                tableBody.appendChild(row);
            });
            
            // Prepare data for charts
            const labels = data.map(item => item.name);
            const salesData = data.map(item => parseFloat(item.total_sales || 0));
            const avgData = data.map(item => parseFloat(item.avg_sale || 0));
            
            // Employee Sales Chart
            if (employeeSalesChart) {
                employeeSalesChart.data.labels = labels;
                employeeSalesChart.data.datasets[0].data = salesData;
                employeeSalesChart.update();
            } else {
                employeeSalesChart = new Chart(
                    document.getElementById('employeeSalesChart'),
                    {
                        type: 'bar',
                        data: {
                            labels: labels,
                            datasets: [{
                                label: 'Total Sales',
                                data: salesData,
                                backgroundColor: 'rgba(52, 152, 219, 0.7)',
                                borderWidth: 1
                            }]
                        },
                        options: {
                            responsive: true,
                            plugins: {
                                title: {
                                    display: true,
                                    text: 'Employee Sales Performance',
                                    font: {
                                        size: 16
                                    }
                                }
                            },
                            scales: {
                                y: {
                                    beginAtZero: true,
                                    title: {
                                        display: true,
                                        text: 'Amount ($)'
                                    }
                                }
                            }
                        }
                    }
                );
            }
            
            // Employee Average Sale Chart
            if (employeeAvgChart) {
                employeeAvgChart.data.labels = labels;
                employeeAvgChart.data.datasets[0].data = avgData;
                employeeAvgChart.update();
            } else {
                employeeAvgChart = new Chart(
                    document.getElementById('employeeAvgChart'),
                    {
                        type: 'bar',
                        data: {
                            labels: labels,
                            datasets: [{
                                label: 'Average Sale',
                                data: avgData,
                                backgroundColor: 'rgba(155, 89, 182, 0.7)',
                                borderWidth: 1
                            }]
                        },
                        options: {
                            responsive: true,
                            plugins: {
                                title: {
                                    display: true,
                                    text: 'Average Transaction Value',
                                    font: {
                                        size: 16
                                    }
                                }
                            },
                            scales: {
                                y: {
                                    beginAtZero: true,
                                    title: {
                                        display: true,
                                        text: 'Amount ($)'
                                    }
                                }
                            }
                        }
                    }
                );
            }
        }
        
        // Update inventory turnover section
        function updateInventoryTurnover(data) {
            if (!data || data.length === 0) {
                console.warn('No inventory turnover data received');
                return;
            }
            
            // Update table
            const tableBody = document.querySelector('#inventoryTable tbody');
            tableBody.innerHTML = '';
            
            data.forEach(item => {
                const row = document.createElement('tr');
                row.innerHTML = `
                    <td>${item.ingredient || 'N/A'}</td>
                    <td>${parseFloat(item.current_stock || 0).toFixed(2)} ${item.unit || ''}</td>
                    <td>${parseFloat(item.purchased_quantity || 0).toFixed(2)}</td>
                    <td>${parseFloat(item.disposed_quantity || 0).toFixed(2)}</td>
                    <td>${parseFloat(item.turnover_rate || 0).toFixed(2)}</td>
                `;
                tableBody.appendChild(row);
            });
            
            // Prepare data for charts
            const labels = data.map(item => item.ingredient);
            const turnoverData = data.map(item => parseFloat(item.turnover_rate || 0));
            const wasteData = data.map(item => parseFloat(item.disposed_quantity || 0));
            
            // Sort data to show top 10 items
            const sortedIndices = [...Array(data.length).keys()]
                .sort((a, b) => turnoverData[b] - turnoverData[a])
                .slice(0, 10);
            
            const topLabels = sortedIndices.map(i => labels[i]);
            const topTurnoverData = sortedIndices.map(i => turnoverData[i]);
            const topWasteData = sortedIndices.map(i => wasteData[i]);
            
            // Inventory Turnover Chart
            if (inventoryTurnoverChart) {
                inventoryTurnoverChart.data.labels = topLabels;
                inventoryTurnoverChart.data.datasets[0].data = topTurnoverData;
                inventoryTurnoverChart.update();
            } else {
                inventoryTurnoverChart = new Chart(
                    document.getElementById('inventoryTurnoverChart'),
                    {
                        type: 'bar',
                        data: {
                            labels: topLabels,
                            datasets: [{
                                label: 'Turnover Rate',
                                data: topTurnoverData,
                                backgroundColor: 'rgba(46, 204, 113, 0.7)',
                                borderWidth: 1
                            }]
                        },
                        options: {
                            responsive: true,
                            plugins: {
                                title: {
                                    display: true,
                                    text: 'Top 10 Inventory Turnover Rates',
                                    font: {
                                        size: 16
                                    }
                                }
                            },
                            scales: {
                                y: {
                                    beginAtZero: true,
                                    title: {
                                        display: true,
                                        text: 'Turnover Rate'
                                    }
                                }
                            }
                        }
                    }
                );
            }
            
            // Waste Analysis Chart
            if (wasteAnalysisChart) {
                wasteAnalysisChart.data.labels = topLabels;
                wasteAnalysisChart.data.datasets[0].data = topWasteData;
                wasteAnalysisChart.update();
            } else {
                wasteAnalysisChart = new Chart(
                    document.getElementById('wasteAnalysisChart'),
                    {
                        type: 'bar',
                        data: {
                            labels: topLabels,
                            datasets: [{
                                label: 'Waste Quantity',
                                data: topWasteData,
                                backgroundColor: 'rgba(231, 76, 60, 0.7)',
                                borderWidth: 1
                            }]
                        },
                        options: {
                            responsive: true,
                            plugins: {
                                title: {
                                    display: true,
                                    text: 'Top 10 Wasted Ingredients',
                                    font: {
                                        size: 16
                                    }
                                }
                            },
                            scales: {
                                y: {
                                    beginAtZero: true,
                                    title: {
                                        display: true,
                                        text: 'Quantity'
                                    }
                                }
                            }
                        }
                    }
                );
            }
        }
        
        // Update sales reports section
        function updateSalesReports(data) {
            if (!data || data.length === 0) {
                console.warn('No sales reports data received');
                return;
            }
            
            // Update table
            const tableBody = document.querySelector('#salesTable tbody');
            tableBody.innerHTML = '';
            
            data.forEach(item => {
                const row = document.createElement('tr');
                row.innerHTML = `
                    <td>${item.period || 'N/A'}</td>
                    <td>${item.transactions || 0}</td>
                    <td>$${parseFloat(item.total_sales || 0).toFixed(2)}</td>
                    <td>$${parseFloat(item.cash_received || 0).toFixed(2)}</td>
                    <td>${item.employees || 0}</td>
                `;
                tableBody.appendChild(row);
            });
            
            // Prepare data for charts
            const labels = data.map(item => item.period);
            const salesData = data.map(item => parseFloat(item.total_sales || 0));
            const transactionData = data.map(item => parseInt(item.transactions || 0));
            
            // Sales Trend Chart
            if (salesTrendChart) {
                salesTrendChart.data.labels = labels;
                salesTrendChart.data.datasets[0].data = salesData;
                salesTrendChart.update();
            } else {
                salesTrendChart = new Chart(
                    document.getElementById('salesTrendChart'),
                    {
                        type: 'line',
                        data: {
                            labels: labels,
                            datasets: [{
                                label: 'Total Sales',
                                data: salesData,
                                borderColor: 'rgba(52, 152, 219, 1)',
                                backgroundColor: 'rgba(52, 152, 219, 0.2)',
                                borderWidth: 2,
                                tension: 0.4,
                                fill: true
                            }]
                        },
                        options: {
                            responsive: true,
                            plugins: {
                                title: {
                                    display: true,
                                    text: 'Sales Trend Over Time',
                                    font: {
                                        size: 16
                                    }
                                }
                            },
                            scales: {
                                y: {
                                    beginAtZero: true,
                                    title: {
                                        display: true,
                                        text: 'Amount ($)'
                                    }
                                }
                            }
                        }
                    }
                );
            }
            
            // Payment Method Chart (mock data - you would need to implement this in your API)
            const paymentMethods = ['Cash', 'Credit Card', 'Mobile Payment', 'Other'];
            const paymentData = [45, 30, 20, 5]; // Example percentages
            
            if (paymentMethodChart) {
                paymentMethodChart.data.labels = paymentMethods;
                paymentMethodChart.data.datasets[0].data = paymentData;
                paymentMethodChart.update();
            } else {
                paymentMethodChart = new Chart(
                    document.getElementById('paymentMethodChart'),
                    {
                        type: 'pie',
                        data: {
                            labels: paymentMethods,
                            datasets: [{
                                data: paymentData,
                                backgroundColor: [
                                    'rgba(52, 152, 219, 0.7)',
                                    'rgba(46, 204, 113, 0.7)',
                                    'rgba(155, 89, 182, 0.7)',
                                    'rgba(241, 196, 15, 0.7)'
                                ],
                                borderWidth: 1
                            }]
                        },
                        options: {
                            responsive: true,
                            plugins: {
                                title: {
                                    display: true,
                                    text: 'Payment Method Distribution',
                                    font: {
                                        size: 16
                                    }
                                },
                                legend: {
                                    position: 'bottom',
                                    labels: {
                                        padding: 20,
                                        usePointStyle: true
                                    }
                                }
                            }
                        }
                    }
                );
            }
        }
        
        // Calculate and display summary statistics
        function calculateSummaryStats(startDate, endDate) {
            // In a real implementation, you would fetch these from your API
            // For demo purposes, we'll use mock calculations
            
            // Mock API call - replace with actual API call
            return fetchData('get_summary_stats', {start_date: startDate, end_date: endDate})
                .then(data => {
                    if (!data) {
                        console.warn('No summary stats data received');
                        return;
                    }
                    
                    // Update the UI
                    document.getElementById('totalSales').textContent = `$${parseFloat(data.total_sales || 0).toFixed(2)}`;
                    document.getElementById('totalProfit').textContent = `$${parseFloat(data.total_profit || 0).toFixed(2)}`;
                    document.getElementById('avgTransaction').textContent = `$${parseFloat(data.avg_transaction || 0).toFixed(2)}`;
                    document.getElementById('wasteCost').textContent = `$${parseFloat(data.waste_cost || 0).toFixed(2)}`;
                })
                .catch(error => {
                    console.error('Error loading summary stats:', error);
                    
                    // Fallback to mock data if API fails
                    const totalSales = 12543.67;
                    const totalProfit = 5432.18;
                    const avgTransaction = 45.32;
                    const wasteCost = 876.54;
                    
                    document.getElementById('totalSales').textContent = `$${totalSales.toFixed(2)}`;
                    document.getElementById('totalProfit').textContent = `$${totalProfit.toFixed(2)}`;
                    document.getElementById('avgTransaction').textContent = `$${avgTransaction.toFixed(2)}`;
                    document.getElementById('wasteCost').textContent = `$${wasteCost.toFixed(2)}`;
                });
        }
        
        // Export data to Excel
        function exportData(type) {
            let table, filename;
            
            switch(type) {
                case 'profit':
                    table = document.getElementById('profitTable');
                    filename = 'profit_analysis.xlsx';
                    break;
                case 'sales':
                    table = document.getElementById('salesTable');
                    filename = 'sales_reports.xlsx';
                    break;
                case 'employees':
                    table = document.getElementById('employeesTable');
                    filename = 'employee_performance.xlsx';
                    break;
                case 'inventory':
                    table = document.getElementById('inventoryTable');
                    filename = 'inventory_analysis.xlsx';
                    break;
                default:
                    return;
            }
            
            const ws = XLSX.utils.table_to_sheet(table);
            const wb = XLSX.utils.book_new();
            XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
            XLSX.writeFile(wb, filename);
        }
    </script>
</body>
</html>