<?php
include 'db.php';

// Grouped monthly totals
$sql_summary = "
    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 DESC
";
$summary = $conn->query($sql_summary);

// All expenses with details
$sql_expenses = "
    SELECT e.id, e.expense_date, e.details, e.amount, 
           et.type_name AS expense_type, p.project_name AS project
    FROM expenses e
    JOIN expense_types et ON e.type_id = et.id
    JOIN projects p ON e.project_id = p.id
    ORDER BY e.expense_date DESC
";
$expenses = $conn->query($sql_expenses);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Expenses List</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>.negative {color: red;}</style>
</head>
<body class="container py-4">
    <h2 class="mb-4">📊 Expenses Summary & List</h2>

    <!-- Monthly Summary -->
    <div class="card mb-4 shadow-sm">
        <div class="card-header bg-dark text-white">Monthly Summary</div>
        <div class="card-body">
            <table class="table table-bordered table-sm">
                <thead class="table-light">
                    <tr>
                        <th>Month</th>
                        <th>Total Amount Spent (RM)</th>
                    </tr>
                </thead>
                <tbody>
                <?php if ($summary->num_rows > 0): ?>
                    <?php while($row = $summary->fetch_assoc()): ?>
                        <tr>
                            <td><?= $row['month'] ?></td>
                            <td><strong>RM <?= number_format($row['total'],2) ?></strong></td>
                        </tr>
                    <?php endwhile; ?>
                <?php else: ?>
                    <tr><td colspan="2" class="text-center">No records found</td></tr>
                <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- All Expenses -->
    <div class="card shadow-sm">
        <div class="card-header bg-primary text-white">All Expenses</div>
        <div class="card-body">
            <table class="table table-striped table-hover table-bordered align-middle">
                <thead class="table-dark">
                    <tr>
                        <th>ID</th>
                        <th>Date</th>
                        <th>Project</th>
                        <th>Type</th>
                        <th>Details</th>
                        <th>Amount (RM)</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                <?php if ($expenses->num_rows > 0): ?>
                    <?php while($exp = $expenses->fetch_assoc()): ?>
                        <tr>
                            <td><?= $exp['id'] ?></td>
                            <td><?= $exp['expense_date'] ?></td>
                            <td><?= $exp['project'] ?></td>
                            <td><?= $exp['expense_type'] ?></td>
                            <td><?= $exp['details'] ?></td>
                            <td>RM <?= number_format($exp['amount'],2) ?></td>
                            <td>
                                <a href="view_expense.php?id=<?= $exp['id'] ?>" class="btn btn-sm btn-info">View</a>
                                <a href="edit_expense.php?id=<?= $exp['id'] ?>" class="btn btn-sm btn-warning">Edit</a>
                                <a href="delete_expense.php?id=<?= $exp['id'] ?>" class="btn btn-sm btn-danger"
                                   onclick="return confirm('Are you sure to delete this expense?')">Delete</a>
                            </td>
                        </tr>
                    <?php endwhile; ?>
                <?php else: ?>
                    <tr><td colspan="7" class="text-center">No expense records found</td></tr>
                <?php endif; ?>
                </tbody>
            </table>
            <a href="expenses_dashboard.php" class="btn btn-info mb-3">📊 View Expense Dashboard</a>

        </div>
    </div>
</body>
</html>
