<?php
include 'db.php';

// Get total per month
$sql = "
    SELECT DATE_FORMAT(expense_date, '%Y-%m') AS month, SUM(amount) as total
    FROM expenses
    GROUP BY DATE_FORMAT(expense_date, '%Y-%m')
    ORDER BY month ASC
";
$res = $conn->query($sql);

$months = [];
$totals = [];
while($row = $res->fetch_assoc()){
    $months[] = $row['month'];
    $totals[] = $row['total'];
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Expense Dashboard</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body class="container py-4">
    <h2 class="mb-4">📊 Expense Dashboard</h2>

    <!-- Chart Container -->
    <div class="card p-4 shadow-sm">
        <h5 class="mb-3">Monthly Expenses</h5>
        <canvas id="expenseChart" height="100"></canvas>
    </div>

    <!-- Back to List -->
    <div class="mt-4">
        <a href="expenses_list.php" class="btn btn-secondary">⬅ Back to Expenses List</a>
    </div>

    <script>
        const ctx = document.getElementById('expenseChart').getContext('2d');
        const expenseChart = new Chart(ctx, {
            type: 'bar', // can change to 'line' or 'pie'
            data: {
                labels: <?= json_encode($months) ?>,
                datasets: [{
                    label: 'Total Spent (RM)',
                    data: <?= json_encode($totals) ?>,
                    backgroundColor: 'rgba(54, 162, 235, 0.6)',
                    borderColor: 'rgba(54, 162, 235, 1)',
                    borderWidth: 1,
                    borderRadius: 6
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: { beginAtZero: true }
                }
            }
        });
    </script>
</body>
</html>
