-- Customer Sales Analysis
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_value
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
WHERE 
    o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    c.customer_id, c.customer_name
HAVING 
    SUM(o.total_amount) > 1000
ORDER BY 
    total_spent DESC;

-- Inventory Management
SELECT 
    p.product_name,
    p.category,
    i.quantity_in_stock,
    i.reorder_level,
    CASE 
        WHEN i.quantity_in_stock < i.reorder_level THEN 'Need Restock'
        ELSE 'In Stock'
    END as stock_status
FROM 
    products p
JOIN 
    inventory i ON p.product_id = i.product_id
ORDER BY 
    stock_status, i.quantity_in_stock ASC;

class BusinessDashboard {
    constructor(apiUrl) {
        this.apiUrl = apiUrl;
        this.charts = {};
        this.data = {};
    }

    async loadData() {
        try {
            const response = await fetch(`${this.apiUrl}/business-data`);
            this.data = await response.json();
            this.renderDashboard();
        } catch (error) {
            console.error('Error loading data:', error);
        }
    }

    renderDashboard() {
        this.renderSalesChart();
        this.renderInventoryTable();
        this.updateKPIs();
    }

    renderSalesChart() {
        const ctx = document.getElementById('salesChart').getContext('2d');
        this.charts.sales = new Chart(ctx, {
            type: 'line',
            data: {
                labels: this.data.months,
                datasets: [{
                    label: 'Monthly Sales',
                    data: this.data.sales,
                    borderColor: '#1a4789',
                    backgroundColor: 'rgba(26, 71, 137, 0.1)',
                    tension: 0.4
                }]
            }
        });
    }
}

const dashboard = new BusinessDashboard('/api');
dashboard.loadData();