<?php
date_default_timezone_set('Asia/Colombo');
include '../config.php';
include '../auth.php';
checkAuth();

// Check if user has permission to view reports (manager or admin only)
checkRole('manager');

// Get date range from query parameters
$start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
$end_date = $_GET['end_date'] ?? date('Y-m-d');
$report_type = $_GET['report_type'] ?? 'overview';

// Sales summary
$sales_query = "
    SELECT 
        DATE(sale_date) as sale_day,
        COUNT(*) as transaction_count,
        SUM(total_amount) as daily_total,
        SUM(paid_amount) as daily_paid,
        AVG(total_amount) as avg_transaction
    FROM sales 
    WHERE DATE(sale_date) BETWEEN ? AND ?
    GROUP BY DATE(sale_date)
    ORDER BY sale_day DESC
";
$stmt = $conn->prepare($sales_query);
$stmt->bind_param('ss', $start_date, $end_date);
$stmt->execute();
$daily_sales = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Top products with profit analysis
$top_products_query = "
    SELECT 
        p.name,
        p.category,
        p.price as current_price,
        p.wholesale_price,
        SUM(si.quantity) as total_sold,
        SUM(si.total_price) as revenue,
        SUM(si.quantity * COALESCE(p.wholesale_price, 0)) as total_cost,
        SUM(si.total_price) - SUM(si.quantity * COALESCE(p.wholesale_price, 0)) as net_profit,
        AVG(si.unit_price) as avg_selling_price
    FROM sale_items si
    JOIN products p ON si.product_id = p.id
    JOIN sales s ON si.sale_id = s.id
    WHERE DATE(s.sale_date) BETWEEN ? AND ?
    GROUP BY p.id, p.name, p.category, p.price, p.wholesale_price
    ORDER BY net_profit DESC
    LIMIT 15
";
$stmt = $conn->prepare($top_products_query);
$stmt->bind_param('ss', $start_date, $end_date);
$stmt->execute();
$top_products = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Total Inventory Cost Analysis
$inventory_cost_query = "
    SELECT 
        SUM(COALESCE(wholesale_price, 0) * COALESCE(stock, 0)) as total_inventory_cost,
        SUM(COALESCE(price, 0) * COALESCE(stock, 0)) as total_inventory_value,
        COUNT(*) as total_products,
        SUM(COALESCE(stock, 0)) as total_stock_units,
        AVG(CASE WHEN wholesale_price > 0 THEN ((price - wholesale_price) / wholesale_price) * 100 ELSE 0 END) as avg_profit_margin
    FROM products
    WHERE stock > 0
";
$inventory_cost_result = $conn->query($inventory_cost_query);
$inventory_summary = $inventory_cost_result->fetch_assoc();

// Inventory by Category
$inventory_by_category_query = "
    SELECT 
        category,
        COUNT(*) as product_count,
        SUM(COALESCE(stock, 0)) as total_units,
        SUM(COALESCE(wholesale_price, 0) * COALESCE(stock, 0)) as category_cost,
        SUM(COALESCE(price, 0) * COALESCE(stock, 0)) as category_value,
        SUM((COALESCE(price, 0) - COALESCE(wholesale_price, 0)) * COALESCE(stock, 0)) as potential_profit
    FROM products
    WHERE stock > 0 AND category IS NOT NULL
    GROUP BY category
    ORDER BY category_cost DESC
";
$inventory_by_category_result = $conn->query($inventory_by_category_query);
$inventory_by_category = $inventory_by_category_result->fetch_all(MYSQLI_ASSOC);

// Category analysis
$category_query = "
    SELECT 
        p.category,
        COUNT(DISTINCT p.id) as product_count,
        SUM(si.quantity) as total_sold,
        SUM(si.total_price) as category_revenue
    FROM sale_items si
    JOIN products p ON si.product_id = p.id
    JOIN sales s ON si.sale_id = s.id
    WHERE DATE(s.sale_date) BETWEEN ? AND ? AND p.category IS NOT NULL
    GROUP BY p.category
    ORDER BY category_revenue DESC
";
$stmt = $conn->prepare($category_query);
$stmt->bind_param('ss', $start_date, $end_date);
$stmt->execute();
$category_sales = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Payment method analysis
$payment_query = "
    SELECT 
        payment_method,
        COUNT(*) as transaction_count,
        SUM(total_amount) as total_amount,
        AVG(total_amount) as avg_amount
    FROM sales 
    WHERE DATE(sale_date) BETWEEN ? AND ?
    GROUP BY payment_method
    ORDER BY total_amount DESC
";
$stmt = $conn->prepare($payment_query);
$stmt->bind_param('ss', $start_date, $end_date);
$stmt->execute();
$payment_methods = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Hourly sales pattern
$hourly_query = "
    SELECT 
        HOUR(sale_date) as hour,
        COUNT(*) as transaction_count,
        SUM(total_amount) as hourly_total
    FROM sales 
    WHERE DATE(sale_date) BETWEEN ? AND ?
    GROUP BY HOUR(sale_date)
    ORDER BY hour
";
$stmt = $conn->prepare($hourly_query);
$stmt->bind_param('ss', $start_date, $end_date);
$stmt->execute();
$hourly_sales = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Customer analysis (if customers table exists)
$customer_query = "
    SELECT 
        c.name as customer_name,
        c.phone,
        COUNT(s.id) as total_orders,
        SUM(s.total_amount) as total_spent,
        AVG(s.total_amount) as avg_order_value,
        MAX(s.sale_date) as last_purchase
    FROM customers c
    JOIN sales s ON c.id = s.customer_id
    WHERE DATE(s.sale_date) BETWEEN ? AND ?
    GROUP BY c.id, c.name, c.phone
    ORDER BY total_spent DESC
    LIMIT 10
";
$stmt = $conn->prepare($customer_query);
$stmt->bind_param('ss', $start_date, $end_date);
$stmt->execute();
$top_customers = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Summary statistics
$summary_query = "
    SELECT 
        COUNT(*) as total_transactions,
        SUM(total_amount) as total_revenue,
        SUM(paid_amount) as total_paid,
        AVG(total_amount) as avg_transaction,
        SUM(discount) as total_discount,
        COUNT(DISTINCT customer_id) as unique_customers
    FROM sales 
    WHERE DATE(sale_date) BETWEEN ? AND ?
";
$stmt = $conn->prepare($summary_query);
$stmt->bind_param('ss', $start_date, $end_date);
$stmt->execute();
$summary = $stmt->get_result()->fetch_assoc();

// Profit Analysis Summary
$profit_summary_query = "
    SELECT 
        SUM(si.total_price) as total_revenue,
        SUM(si.quantity * COALESCE(p.wholesale_price, 0)) as total_cost,
        SUM(si.total_price) - SUM(si.quantity * COALESCE(p.wholesale_price, 0)) as net_profit,
        COUNT(DISTINCT si.product_id) as products_sold,
        AVG((si.unit_price - COALESCE(p.wholesale_price, 0)) / NULLIF(COALESCE(p.wholesale_price, 1), 0) * 100) as avg_profit_margin
    FROM sale_items si
    JOIN products p ON si.product_id = p.id
    JOIN sales s ON si.sale_id = s.id
    WHERE DATE(s.sale_date) BETWEEN ? AND ?
";
$stmt = $conn->prepare($profit_summary_query);
$stmt->bind_param('ss', $start_date, $end_date);
$stmt->execute();
$profit_summary = $stmt->get_result()->fetch_assoc();

// Low stock alerts
$low_stock_query = "SELECT name, stock, category FROM products WHERE stock <= 5 ORDER BY stock ASC LIMIT 10";
$low_stock_products = $conn->query($low_stock_query)->fetch_all(MYSQLI_ASSOC);

// Expenses data (if expenses table exists and report type is expenses)
$expenses_data = [];
$expenses_summary = [];
$expenses_categories = [];
if ($report_type === 'expenses') {
    $table_check = $conn->query("SHOW TABLES LIKE 'expenses'");
    if ($table_check->num_rows > 0) {
        // Get expenses summary
        $expenses_summary_query = "
            SELECT 
                COUNT(*) as total_expenses,
                SUM(amount) as total_amount,
                AVG(amount) as avg_expense
            FROM expenses 
            WHERE DATE(expense_date) BETWEEN ? AND ?
        ";
        $stmt = $conn->prepare($expenses_summary_query);
        $stmt->bind_param('ss', $start_date, $end_date);
        $stmt->execute();
        $expenses_summary = $stmt->get_result()->fetch_assoc();
        
        // Get expenses by category
        $expenses_category_query = "
            SELECT 
                category,
                COUNT(*) as expense_count,
                SUM(amount) as category_total,
                AVG(amount) as avg_amount
            FROM expenses 
            WHERE DATE(expense_date) BETWEEN ? AND ?
            GROUP BY category
            ORDER BY category_total DESC
        ";
        $stmt = $conn->prepare($expenses_category_query);
        $stmt->bind_param('ss', $start_date, $end_date);
        $stmt->execute();
        $expenses_categories = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        
        // Get recent expenses
        $expenses_query = "
            SELECT 
                expense_date,
                category,
                description,
                amount,
                payment_method,
                notes
            FROM expenses 
            WHERE DATE(expense_date) BETWEEN ? AND ?
            ORDER BY expense_date DESC, created_at DESC
            LIMIT 50
        ";
        $stmt = $conn->prepare($expenses_query);
        $stmt->bind_param('ss', $start_date, $end_date);
        $stmt->execute();
        $expenses_data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Advanced Reports - Textile POS</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        body { background: #f8f9fa; }
        .container-fluid { margin-top: 20px; }
        .stat-card { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; border-radius: 15px; }
        .stat-card-green { background: linear-gradient(135deg, #56ab2f 0%, #a8e6cf 100%); color: white; }
        .stat-card-orange { background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%); color: white; }
        .stat-card-blue { background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%); color: white; }
        .chart-container { background: white; border-radius: 15px; padding: 20px; box-shadow: 0 4px 6px rgba(0,0,0,0.1); }
        .nav-pills .nav-link.active { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); }
        .table-responsive { max-height: 400px; overflow-y: auto; }
    </style>
</head>
<body>
    <div class="container-fluid">
        <div class="d-flex justify-content-between align-items-center mb-4">
            <h1 class="fw-bold"><i class="fas fa-chart-line"></i> Advanced Sales Analytics</h1>
            <div>
                <a href="daily_summary.php" class="btn btn-info me-2"><i class="fas fa-calendar-day"></i> Daily Summary</a>
                <a href="../pos/" class="btn btn-success me-2"><i class="fas fa-cash-register"></i> POS</a>
                <a href="../" class="btn btn-secondary"><i class="fas fa-home"></i> Dashboard</a>
            </div>
        </div>

        <!-- Date Range Filter & Report Types -->
        <div class="card mb-4">
            <div class="card-body">
                <form method="GET" class="row g-3 align-items-end">
                    <div class="col-md-2">
                        <label class="form-label">Report Type</label>
                        <select name="report_type" class="form-select">
                            <option value="overview" <?= $report_type == 'overview' ? 'selected' : '' ?>>Overview</option>
                            <option value="profit" <?= $report_type == 'profit' ? 'selected' : '' ?>>Profit Analysis</option>
                            <option value="products" <?= $report_type == 'products' ? 'selected' : '' ?>>Product Analysis</option>
                            <option value="customers" <?= $report_type == 'customers' ? 'selected' : '' ?>>Customer Analytics</option>
                            <option value="categories" <?= $report_type == 'categories' ? 'selected' : '' ?>>Category Performance</option>
                            <option value="expenses" <?= $report_type == 'expenses' ? 'selected' : '' ?>>Expenses Report</option>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">Start Date</label>
                        <input type="date" name="start_date" class="form-control" value="<?= $start_date ?>">
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">End Date</label>
                        <input type="date" name="end_date" class="form-control" value="<?= $end_date ?>">
                    </div>
                    <div class="col-md-2">
                        <button type="submit" class="btn btn-primary w-100">
                            <i class="fas fa-search"></i> Generate Report
                        </button>
                    </div>
                    <div class="col-md-2">
                        <button type="button" class="btn btn-success w-100" onclick="exportReport()">
                            <i class="fas fa-download"></i> Export
                        </button>
                    </div>
                </form>
            </div>
        </div>

        <!-- Enhanced Summary Cards -->
        <?php if ($report_type !== 'expenses'): ?>
        <div class="row mb-4">
            <div class="col-md-2">
                <div class="card stat-card">
                    <div class="card-body text-center">
                        <i class="fas fa-rupee-sign fa-2x mb-2"></i>
                        <h4>Rs <?= number_format($summary['total_revenue'] ?? 0, 2) ?></h4>
                        <p class="mb-1">Total Revenue</p>
                        <small><i class="fas fa-arrow-up"></i> Period Total</small>
                    </div>
                </div>
            </div>
            <div class="col-md-2">
                <div class="card stat-card-green">
                    <div class="card-body text-center">
                        <i class="fas fa-money-check-alt fa-2x mb-2"></i>
                        <h4>Rs <?= number_format($summary['total_paid'] ?? 0, 2) ?></h4>
                        <p class="mb-1">Total Paid</p>
                        <small><i class="fas fa-check"></i> Collected</small>
                    </div>
                </div>
            </div>
            <div class="col-md-2">
                <div class="card stat-card-orange">
                    <div class="card-body text-center">
                        <i class="fas fa-receipt fa-2x mb-2"></i>
                        <h4><?= $summary['total_transactions'] ?? 0 ?></h4>
                        <p class="mb-1">Transactions</p>
                        <small><i class="fas fa-shopping-cart"></i> Total Sales</small>
                    </div>
                </div>
            </div>
            <div class="col-md-2">
                <div class="card stat-card-blue">
                    <div class="card-body text-center">
                        <i class="fas fa-chart-line fa-2x mb-2"></i>
                        <h4>Rs <?= number_format($summary['avg_transaction'] ?? 0, 2) ?></h4>
                        <p class="mb-1">Avg Transaction</p>
                        <small><i class="fas fa-calculator"></i> Per Sale</small>
                    </div>
                </div>
            </div>
            <div class="col-md-2">
                <div class="card stat-card">
                    <div class="card-body text-center">
                        <i class="fas fa-users fa-2x mb-2"></i>
                        <h4><?= $summary['unique_customers'] ?? 0 ?></h4>
                        <p class="mb-1">Customers</p>
                        <small><i class="fas fa-user-friends"></i> Unique</small>
                    </div>
                </div>
            </div>
            <div class="col-md-2">
                <div class="card stat-card-green">
                    <div class="card-body text-center">
                        <i class="fas fa-percentage fa-2x mb-2"></i>
                        <h4>Rs <?= number_format($summary['total_discount'] ?? 0, 2) ?></h4>
                        <p class="mb-1">Discounts</p>
                        <small><i class="fas fa-tags"></i> Given</small>
                    </div>
                </div>
            </div>
        </div>
        
        <!-- Profit Analysis Section -->
        <div class="row mb-4">
            <div class="col-md-3">
                <div class="card" style="background: linear-gradient(135deg, #2ecc71 0%, #27ae60 100%); color: white; border-radius: 15px;">
                    <div class="card-body text-center">
                        <i class="fas fa-chart-profit fa-2x mb-2"></i>
                        <h4>Rs <?= number_format($profit_summary['net_profit'] ?? 0, 2) ?></h4>
                        <p class="mb-1">Net Profit</p>
                        <small><i class="fas fa-arrow-up"></i> Period Total</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card" style="background: linear-gradient(135deg, #e74c3c 0%, #c0392b 100%); color: white; border-radius: 15px;">
                    <div class="card-body text-center">
                        <i class="fas fa-warehouse fa-2x mb-2"></i>
                        <h4>Rs <?= number_format($inventory_summary['total_inventory_cost'] ?? 0, 2) ?></h4>
                        <p class="mb-1">Total Inventory Cost</p>
                        <small><i class="fas fa-boxes"></i> All Stock @ Wholesale</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card" style="background: linear-gradient(135deg, #f39c12 0%, #e67e22 100%); color: white; border-radius: 15px;">
                    <div class="card-body text-center">
                        <i class="fas fa-percentage fa-2x mb-2"></i>
                        <h4><?= number_format($profit_summary['avg_profit_margin'] ?? 0, 1) ?>%</h4>
                        <p class="mb-1">Avg Profit Margin</p>
                        <small><i class="fas fa-calculator"></i> Average</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card" style="background: linear-gradient(135deg, #9b59b6 0%, #8e44ad 100%); color: white; border-radius: 15px;">
                    <div class="card-body text-center">
                        <i class="fas fa-boxes fa-2x mb-2"></i>
                        <h4><?= $profit_summary['products_sold'] ?? 0 ?></h4>
                        <p class="mb-1">Products Sold</p>
                        <small><i class="fas fa-cube"></i> Unique Items</small>
                    </div>
                </div>
            </div>
        </div>

        <!-- Inventory Cost Breakdown -->
        <div class="row mb-4">
            <div class="col-md-6">
                <div class="card">
                    <div class="card-header bg-primary text-white">
                        <h5><i class="fas fa-warehouse"></i> Inventory Summary</h5>
                    </div>
                    <div class="card-body">
                        <div class="row text-center">
                            <div class="col-6">
                                <h4 class="text-primary">Rs <?= number_format($inventory_summary['total_inventory_cost'] ?? 0, 2) ?></h4>
                                <small class="text-muted">Total Wholesale Cost<br><?= number_format($inventory_summary['total_stock_units'] ?? 0) ?> units</small>
                            </div>
                            <div class="col-6">
                                <h4 class="text-success">Rs <?= number_format($inventory_summary['total_inventory_value'] ?? 0, 2) ?></h4>
                                <small class="text-muted">Total Retail Value<br><?= number_format($inventory_summary['total_products'] ?? 0) ?> products</small>
                            </div>
                        </div>
                        <hr>
                        <div class="row text-center">
                            <div class="col-6">
                                <h5 class="text-info">Rs <?= number_format(($inventory_summary['total_inventory_value'] ?? 0) - ($inventory_summary['total_inventory_cost'] ?? 0), 2) ?></h5>
                                <small class="text-muted">Potential Profit</small>
                            </div>
                            <div class="col-6">
                                <h5 class="text-warning"><?= number_format($inventory_summary['avg_profit_margin'] ?? 0, 1) ?>%</h5>
                                <small class="text-muted">Avg Margin</small>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-md-6">
                <div class="card">
                    <div class="card-header bg-info text-white">
                        <h5><i class="fas fa-chart-pie"></i> Inventory by Category</h5>
                    </div>
                    <div class="card-body" style="max-height: 250px; overflow-y: auto;">
                        <?php foreach ($inventory_by_category as $category): ?>
                        <div class="d-flex justify-content-between align-items-center mb-2 p-2 border-bottom">
                            <div>
                                <strong><?= htmlspecialchars($category['category']) ?></strong><br>
                                <small class="text-muted"><?= $category['product_count'] ?> products, <?= $category['total_units'] ?> units</small>
                            </div>
                            <div class="text-end">
                                <strong class="text-primary">Rs <?= number_format($category['category_cost'], 2) ?></strong><br>
                                <small class="text-success">Value: Rs <?= number_format($category['category_value'], 2) ?></small>
                            </div>
                        </div>
                        <?php endforeach; ?>
                    </div>
                </div>
            </div>
        </div>

        <!-- Cost Analysis Comparison -->
        <div class="row mb-4">
            <div class="col-md-12">
                <div class="card">
                    <div class="card-header bg-warning text-dark">
                        <h5><i class="fas fa-balance-scale"></i> Cost Analysis: Sold vs Total Inventory</h5>
                    </div>
                    <div class="card-body">
                        <div class="row text-center">
                            <div class="col-md-3">
                                <div class="p-3 border rounded bg-light">
                                    <h4 class="text-danger">Rs <?= number_format($profit_summary['total_cost'] ?? 0, 2) ?></h4>
                                    <p class="mb-1"><strong>Cost of Sold Items</strong></p>
                                    <small class="text-muted">Wholesale cost × quantity sold</small>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="p-3 border rounded bg-light">
                                    <h4 class="text-primary">Rs <?= number_format($inventory_summary['total_inventory_cost'] ?? 0, 2) ?></h4>
                                    <p class="mb-1"><strong>Total Inventory Investment</strong></p>
                                    <small class="text-muted">Wholesale cost × current stock</small>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="p-3 border rounded bg-light">
                                    <h4 class="text-info">Rs <?= number_format($inventory_summary['total_inventory_cost'] ?? 0, 2) ?></h4>
                                    <p class="mb-1"><strong>Current Stock Investment</strong></p>
                                    <small class="text-muted">Money tied up in current stock</small>
                                </div>
                            </div>
                            <div class="col-md-3">
                                <div class="p-3 border rounded bg-light">
                                    <?php 
                                    $inventory_turnover = ($inventory_summary['total_inventory_cost'] ?? 0) > 0 ? 
                                        (($profit_summary['total_cost'] ?? 0) / ($inventory_summary['total_inventory_cost'] ?? 1)) * 100 : 0;
                                    ?>
                                    <h4 class="text-success"><?= number_format($inventory_turnover, 1) ?>%</h4>
                                    <p class="mb-1"><strong>Inventory Turnover</strong></p>
                                    <small class="text-muted">% of inventory sold (period)</small>
                                </div>
                            </div>
                        </div>
                        <hr>
                        <div class="row">
                            <div class="col-md-12">
                                <div class="alert alert-info mb-0">
                                    <i class="fas fa-info-circle"></i> <strong>Understanding the metrics:</strong>
                                    <ul class="mb-0 mt-2">
                                        <li><strong>Cost of Sold Items:</strong> Wholesale cost of products sold in this period (profit calculation)</li>
                                        <li><strong>Total Inventory Investment:</strong> Total wholesale value of all current stock (cash tied up)</li>
                                        <li><strong>Current Stock Investment:</strong> Same as above - money invested in existing inventory</li>
                                        <li><strong>Inventory Turnover:</strong> Percentage showing how much of total inventory was sold in this period</li>
                                    </ul>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <?php endif; ?>

        <?php if ($report_type !== 'expenses'): ?>
        <div class="row mb-4">
            <!-- Daily Sales Trend Chart -->
            <div class="col-lg-8 mb-4">
                <div class="card chart-container">
                    <div class="card-header bg-transparent">
                        <h5><i class="fas fa-chart-area"></i> Daily Sales Trend</h5>
                    </div>
                    <div class="card-body">
                        <canvas id="salesChart" height="80"></canvas>
                    </div>
                </div>
            </div>

            <!-- Payment Methods Pie Chart -->
            <div class="col-lg-4 mb-4">
                <div class="card chart-container">
                    <div class="card-header bg-transparent">
                        <h5><i class="fas fa-credit-card"></i> Payment Methods</h5>
                    </div>
                    <div class="card-body">
                        <canvas id="paymentChart" height="120"></canvas>
                    </div>
                </div>
            </div>
        </div>

        <div class="row mb-4">
            <!-- Category Performance -->
            <div class="col-lg-6 mb-4">
                <div class="card chart-container">
                    <div class="card-header bg-transparent">
                        <h5><i class="fas fa-tags"></i> Category Performance</h5>
                    </div>
                    <div class="card-body">
                        <canvas id="categoryChart" height="100"></canvas>
                    </div>
                </div>
            </div>

            <!-- Hourly Sales Pattern -->
            <div class="col-lg-6 mb-4">
                <div class="card chart-container">
                    <div class="card-header bg-transparent">
                        <h5><i class="fas fa-clock"></i> Hourly Sales Pattern</h5>
                    </div>
                    <div class="card-body">
                        <canvas id="hourlyChart" height="100"></canvas>
                    </div>
                </div>
            </div>
        </div>

        <div class="row">
            <!-- Top Products by Profit -->
            <div class="col-lg-4 mb-4">
                <div class="card">
                    <div class="card-header bg-success text-white">
                        <h5><i class="fas fa-chart-line"></i> Most Profitable Products</h5>
                    </div>
                    <div class="card-body table-responsive">
                        <table class="table table-sm">
                            <thead>
                                <tr>
                                    <th>Product</th>
                                    <th>Sold</th>
                                    <th>Profit</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach (array_slice($top_products, 0, 10) as $index => $product): ?>
                                <?php 
                                    $profit_margin = $product['wholesale_price'] > 0 ? 
                                        (($product['current_price'] - $product['wholesale_price']) / $product['wholesale_price']) * 100 : 0;
                                ?>
                                <tr>
                                    <td>
                                        <strong><?= htmlspecialchars($product['name']) ?></strong>
                                        <br><small class="text-muted"><?= htmlspecialchars($product['category']) ?></small>
                                    </td>
                                    <td><span class="badge bg-primary"><?= $product['total_sold'] ?></span></td>
                                    <td>
                                        <span class="text-success fw-bold">Rs <?= number_format($product['net_profit'], 2) ?></span>
                                        <br><small class="text-muted"><?= number_format($profit_margin, 1) ?>% margin</small>
                                    </td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>

            <!-- Top Customers -->
            <div class="col-lg-4 mb-4">
                <div class="card">
                    <div class="card-header bg-info text-white">
                        <h5><i class="fas fa-users"></i> Top Customers</h5>
                    </div>
                    <div class="card-body table-responsive">
                        <table class="table table-sm">
                            <thead>
                                <tr>
                                    <th>Customer</th>
                                    <th>Orders</th>
                                    <th>Total Spent</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($top_customers as $customer): ?>
                                <tr>
                                    <td>
                                        <strong><?= htmlspecialchars($customer['customer_name']) ?></strong>
                                        <br><small class="text-muted"><?= htmlspecialchars($customer['phone']) ?></small>
                                    </td>
                                    <td><span class="badge bg-info"><?= $customer['total_orders'] ?></span></td>
                                    <td class="text-success fw-bold">Rs <?= number_format($customer['total_spent'], 2) ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>

            <!-- Low Stock Alert -->
            <div class="col-lg-4 mb-4">
                <div class="card">
                    <div class="card-header bg-warning text-dark">
                        <h5><i class="fas fa-exclamation-triangle"></i> Low Stock Alert</h5>
                    </div>
                    <div class="card-body table-responsive">
                        <table class="table table-sm">
                            <thead>
                                <tr>
                                    <th>Product</th>
                                    <th>Stock</th>
                                    <th>Category</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($low_stock_products as $product): ?>
                                <tr class="<?= $product['stock'] == 0 ? 'table-danger' : 'table-warning' ?>">
                                    <td><strong><?= htmlspecialchars($product['name']) ?></strong></td>
                                    <td>
                                        <span class="badge <?= $product['stock'] == 0 ? 'bg-danger' : 'bg-warning text-dark' ?>">
                                            <?= $product['stock'] ?>
                                        </span>
                                    </td>
                                    <td><small><?= htmlspecialchars($product['category']) ?></small></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
        <?php endif; ?>

        <?php if ($report_type === 'expenses'): ?>
        <!-- Debug: Show that we're in expenses mode -->
        <!-- Expenses Report Section -->
        <?php if (empty($expenses_data)): ?>
        <div class="row mb-4">
            <div class="col-12">
                <div class="card">
                    <div class="card-header bg-warning text-dark">
                        <h5><i class="fas fa-exclamation-triangle"></i> Expenses Module - No Data Found</h5>
                    </div>
                    <div class="card-body text-center">
                        <i class="fas fa-receipt fa-3x text-muted mb-3"></i>
                        <h4>No Expenses Data Found</h4>
                        <p class="text-muted">Either no expenses table exists or no expenses recorded for this period.</p>
                        <p><strong>Period:</strong> <?= $start_date ?> to <?= $end_date ?></p>
                        <a href="../expenses/" class="btn btn-warning">
                            <i class="fas fa-plus"></i> Manage Expenses
                        </a>
                    </div>
                </div>
            </div>
        </div>
        <?php else: ?>
        <!-- Expenses Summary Cards -->
        <div class="row mb-4">
            <div class="col-md-4">
                <div class="card stat-card" style="background: linear-gradient(135deg, #ff6b6b, #ee5a52);">
                    <div class="card-body text-center text-white">
                        <i class="fas fa-money-bill-wave fa-2x mb-2"></i>
                        <h4>Rs <?= number_format($expenses_summary['total_amount'] ?? 0, 2) ?></h4>
                        <p class="mb-1">Total Expenses</p>
                        <small><i class="fas fa-arrow-down"></i> Period Total</small>
                    </div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="card stat-card" style="background: linear-gradient(135deg, #ffa726, #fb8c00);">
                    <div class="card-body text-center text-white">
                        <i class="fas fa-receipt fa-2x mb-2"></i>
                        <h4><?= $expenses_summary['total_expenses'] ?? 0 ?></h4>
                        <p class="mb-1">Total Transactions</p>
                        <small><i class="fas fa-list"></i> Expense Count</small>
                    </div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="card stat-card" style="background: linear-gradient(135deg, #42a5f5, #1e88e5);">
                    <div class="card-body text-center text-white">
                        <i class="fas fa-calculator fa-2x mb-2"></i>
                        <h4>Rs <?= number_format($expenses_summary['avg_expense'] ?? 0, 2) ?></h4>
                        <p class="mb-1">Average Expense</p>
                        <small><i class="fas fa-chart-line"></i> Per Transaction</small>
                    </div>
                </div>
            </div>
        </div>

        <div class="row mb-4">
            <!-- Expenses by Category -->
            <div class="col-lg-6">
                <div class="card">
                    <div class="card-header bg-warning text-dark">
                        <h5><i class="fas fa-chart-pie"></i> Expenses by Category</h5>
                    </div>
                    <div class="card-body">
                        <div class="table-responsive">
                            <table class="table table-sm">
                                <thead>
                                    <tr>
                                        <th>Category</th>
                                        <th>Count</th>
                                        <th>Total Amount</th>
                                        <th>Avg Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($expenses_categories as $category): ?>
                                    <tr>
                                        <td><strong><?= htmlspecialchars($category['category']) ?></strong></td>
                                        <td><span class="badge bg-secondary"><?= $category['expense_count'] ?></span></td>
                                        <td class="text-danger fw-bold">Rs <?= number_format($category['category_total'], 2) ?></td>
                                        <td>Rs <?= number_format($category['avg_amount'], 2) ?></td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Recent Expenses -->
            <div class="col-lg-6">
                <div class="card">
                    <div class="card-header bg-danger text-white">
                        <h5><i class="fas fa-list"></i> Recent Expenses</h5>
                    </div>
                    <div class="card-body">
                        <div class="table-responsive" style="max-height: 400px; overflow-y: auto;">
                            <table class="table table-sm">
                                <thead class="sticky-top bg-light">
                                    <tr>
                                        <th>Date</th>
                                        <th>Category</th>
                                        <th>Description</th>
                                        <th>Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach (array_slice($expenses_data, 0, 20) as $expense): ?>
                                    <tr>
                                        <td><small><?= date('M d', strtotime($expense['expense_date'])) ?></small></td>
                                        <td>
                                            <span class="badge bg-secondary" style="font-size: 0.7em;">
                                                <?= htmlspecialchars($expense['category']) ?>
                                            </span>
                                        </td>
                                        <td>
                                            <small><?= htmlspecialchars(substr($expense['description'], 0, 30)) ?><?= strlen($expense['description']) > 30 ? '...' : '' ?></small>
                                        </td>
                                        <td class="text-danger fw-bold">Rs <?= number_format($expense['amount'], 2) ?></td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <?php endif; ?>
        <?php endif; ?>

        <?php if ($report_type === 'profit'): ?>
        <!-- Dedicated Profit Analysis Section -->
        <div class="row mb-4">
            <div class="col-12">
                <div class="card">
                    <div class="card-header bg-success text-white">
                        <h5><i class="fas fa-chart-line"></i> Detailed Profit Analysis</h5>
                    </div>
                    <div class="card-body">
                        <div class="table-responsive">
                            <table class="table table-striped">
                                <thead>
                                    <tr>
                                        <th>Product</th>
                                        <th>Category</th>
                                        <th>Units Sold</th>
                                        <th>Wholesale Price</th>
                                        <th>Selling Price</th>
                                        <th>Total Cost</th>
                                        <th>Revenue</th>
                                        <th>Net Profit</th>
                                        <th>Profit Margin</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($top_products as $product): ?>
                                    <?php 
                                        $wholesale = $product['wholesale_price'] ?? 0;
                                        $selling = $product['current_price'] ?? 0;
                                        $profit_margin = $wholesale > 0 ? (($selling - $wholesale) / $wholesale) * 100 : 0;
                                        $profit_per_unit = $selling - $wholesale;
                                    ?>
                                    <tr>
                                        <td>
                                            <strong><?= htmlspecialchars($product['name']) ?></strong>
                                        </td>
                                        <td>
                                            <span class="badge bg-secondary"><?= htmlspecialchars($product['category']) ?></span>
                                        </td>
                                        <td>
                                            <span class="badge bg-primary"><?= $product['total_sold'] ?></span>
                                        </td>
                                        <td>Rs <?= number_format($wholesale, 2) ?></td>
                                        <td>Rs <?= number_format($selling, 2) ?></td>
                                        <td>Rs <?= number_format($product['total_cost'], 2) ?></td>
                                        <td class="text-primary fw-bold">Rs <?= number_format($product['revenue'], 2) ?></td>
                                        <td class="text-success fw-bold">Rs <?= number_format($product['net_profit'], 2) ?></td>
                                        <td>
                                            <span class="badge bg-<?= $profit_margin > 30 ? 'success' : ($profit_margin > 15 ? 'warning' : 'danger') ?>">
                                                <?= number_format($profit_margin, 1) ?>%
                                            </span>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        
        <!-- Profit Summary Charts for Profit Report -->
        <div class="row mb-4">
            <div class="col-lg-6">
                <div class="card chart-container">
                    <div class="card-header bg-transparent">
                        <h5><i class="fas fa-chart-pie"></i> Profit Distribution by Category</h5>
                    </div>
                    <div class="card-body">
                        <canvas id="profitCategoryChart" height="100"></canvas>
                    </div>
                </div>
            </div>
            <div class="col-lg-6">
                <div class="card chart-container">
                    <div class="card-header bg-transparent">
                        <h5><i class="fas fa-chart-bar"></i> Profit vs Revenue Comparison</h5>
                    </div>
                    <div class="card-body">
                        <canvas id="profitComparisonChart" height="100"></canvas>
                    </div>
                </div>
            </div>
        </div>
        <?php endif; ?>

        <!-- Detailed Sales Table -->
        <div class="card">
            <div class="card-header">
                <h5><i class="fas fa-table"></i> Daily Sales Details</h5>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-striped">
                        <thead>
                            <tr>
                                <th>Date</th>
                                <th>Transactions</th>
                                <th>Total Sales</th>
                                <th>Avg per Transaction</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($daily_sales as $day): ?>
                            <tr>
                                <td><?= date('M d, Y', strtotime($day['sale_day'])) ?></td>
                                <td><?= $day['transaction_count'] ?></td>
                                <td class="fw-bold text-success">$<?= number_format($day['daily_total'], 2) ?></td>
                                <td>$<?= number_format($day['daily_total'] / $day['transaction_count'], 2) ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Sales Trend Chart
        const salesCtx = document.getElementById('salesChart').getContext('2d');
        const salesData = <?= json_encode(array_reverse($daily_sales)) ?>;
        
        new Chart(salesCtx, {
            type: 'line',
            data: {
                labels: salesData.map(day => new Date(day.sale_day).toLocaleDateString()),
                datasets: [{
                    label: 'Daily Sales (Rs)',
                    data: salesData.map(day => day.daily_total),
                    borderColor: '#667eea',
                    backgroundColor: 'rgba(102, 126, 234, 0.1)',
                    tension: 0.4,
                    fill: true,
                    pointBackgroundColor: '#667eea',
                    pointBorderColor: '#fff',
                    pointBorderWidth: 2
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: { display: false }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'Rs ' + value.toLocaleString();
                            }
                        }
                    }
                }
            }
        });

        // Payment Methods Pie Chart
        const paymentCtx = document.getElementById('paymentChart').getContext('2d');
        const paymentData = <?= json_encode($payment_methods) ?>;
        
        new Chart(paymentCtx, {
            type: 'doughnut',
            data: {
                labels: paymentData.map(p => p.payment_method.toUpperCase()),
                datasets: [{
                    data: paymentData.map(p => p.total_amount),
                    backgroundColor: ['#667eea', '#56ab2f', '#f093fb', '#4facfe', '#ff9a56'],
                    borderWidth: 2,
                    borderColor: '#fff'
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: { position: 'bottom' }
                }
            }
        });

        // Category Performance Chart
        const categoryCtx = document.getElementById('categoryChart').getContext('2d');
        const categoryData = <?= json_encode($category_sales) ?>;
        
        new Chart(categoryCtx, {
            type: 'bar',
            data: {
                labels: categoryData.map(c => c.category),
                datasets: [{
                    label: 'Revenue (Rs)',
                    data: categoryData.map(c => c.category_revenue),
                    backgroundColor: 'rgba(86, 171, 47, 0.8)',
                    borderColor: '#56ab2f',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: { display: false }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'Rs ' + value.toLocaleString();
                            }
                        }
                    }
                }
            }
        });

        // Hourly Sales Pattern Chart
        const hourlyCtx = document.getElementById('hourlyChart').getContext('2d');
        const hourlyData = <?= json_encode($hourly_sales) ?>;
        
        // Create 24-hour array with zeros
        const hourlyArray = new Array(24).fill(0);
        hourlyData.forEach(h => {
            hourlyArray[h.hour] = parseFloat(h.hourly_total);
        });
        
        new Chart(hourlyCtx, {
            type: 'bar',
            data: {
                labels: Array.from({length: 24}, (_, i) => i + ':00'),
                datasets: [{
                    label: 'Sales by Hour (Rs)',
                    data: hourlyArray,
                    backgroundColor: 'rgba(79, 172, 254, 0.8)',
                    borderColor: '#4facfe',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: { display: false }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'Rs ' + value.toLocaleString();
                            }
                        }
                    }
                }
            }
        });

        // Profit Analysis Charts (only show if profit report is selected)
        <?php if ($report_type === 'profit'): ?>
        // Category Profit Distribution Chart
        const profitCategoryCtx = document.getElementById('profitCategoryChart').getContext('2d');
        new Chart(profitCategoryCtx, {
            type: 'pie',
            data: {
                labels: <?= json_encode(array_column(array_slice($top_products, 0, 8), 'category')) ?>,
                datasets: [{
                    data: <?= json_encode(array_column(array_slice($top_products, 0, 8), 'net_profit')) ?>,
                    backgroundColor: [
                        '#FF6384', '#36A2EB', '#FFCE56', '#4BC0C0', 
                        '#9966FF', '#FF9F40', '#FF6384', '#C9CBCF'
                    ]
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'bottom'
                    },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.label + ': Rs ' + context.parsed.toLocaleString();
                            }
                        }
                    }
                }
            }
        });

        // Profit vs Revenue Comparison Chart
        const profitComparisonCtx = document.getElementById('profitComparisonChart').getContext('2d');
        new Chart(profitComparisonCtx, {
            type: 'bar',
            data: {
                labels: <?= json_encode(array_column(array_slice($top_products, 0, 10), 'name')) ?>,
                datasets: [
                    {
                        label: 'Revenue',
                        data: <?= json_encode(array_column(array_slice($top_products, 0, 10), 'revenue')) ?>,
                        backgroundColor: 'rgba(54, 162, 235, 0.8)'
                    },
                    {
                        label: 'Profit',
                        data: <?= json_encode(array_column(array_slice($top_products, 0, 10), 'net_profit')) ?>,
                        backgroundColor: 'rgba(75, 192, 192, 0.8)'
                    },
                    {
                        label: 'Cost',
                        data: <?= json_encode(array_column(array_slice($top_products, 0, 10), 'total_cost')) ?>,
                        backgroundColor: 'rgba(255, 99, 132, 0.8)'
                    }
                ]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'Rs ' + value.toLocaleString();
                            }
                        }
                    }
                },
                plugins: {
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.dataset.label + ': Rs ' + context.parsed.y.toLocaleString();
                            }
                        }
                    }
                }
            }
        });
        <?php endif; ?>

        function exportReport() {
            const startDate = document.querySelector('input[name="start_date"]').value;
            const endDate = document.querySelector('input[name="end_date"]').value;
            const reportType = document.querySelector('select[name="report_type"]').value;
            window.open(`export.php?start_date=${startDate}&end_date=${endDate}&report_type=${reportType}`, '_blank');
        }

        // Auto-refresh every 5 minutes
        setTimeout(() => {
            location.reload();
        }, 300000);
    </script>
</body>
</html>
