<?php
session_start();
if (!isset($_SESSION['role_id']) || !in_array($_SESSION['role_id'], [1, 4])) {
    header("Location: ../../index.php");
    exit();
}
include('../../includes/config.php');
include('../../includes/header.php');

// Filters
$store_id = $_GET['store_id'] ?? '';
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');

// Validate dates
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_from)) $date_from = date('Y-m-01');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_to)) $date_to = date('Y-m-d');

// Fetch stores for filter dropdown
$stores_result = $conn->query("SELECT id, store_name FROM stores ORDER BY store_name ASC");
$stores = [];
while ($row = $stores_result->fetch_assoc()) {
    $stores[] = $row;
}

// Prepare query with filters - only approved expenses
$params = [];
$whereClauses = ["e.status = 'approved'", "DATE(e.expense_date) BETWEEN ? AND ?"];
$params[] = $date_from;
$params[] = $date_to;
$types = "ss";

if ($store_id && is_numeric($store_id)) {
    $whereClauses[] = "e.store_id = ?";
    $params[] = $store_id;
    $types .= "i";
}

$where = implode(" AND ", $whereClauses);

$sql = "
    SELECT e.id, e.expense_date, e.amount, e.description, s.store_name
    FROM expenses e
    LEFT JOIN stores s ON e.store_id = s.id
    WHERE $where
    ORDER BY e.expense_date DESC
";

$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();

$expenses = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Calculate total approved expenses
$total = 0;
foreach ($expenses as $e) {
    $total += $e['amount'];
}

$page_title = "Approved Expenses Report";
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title><?= htmlspecialchars($page_title) ?></title>
    <link rel="icon" type="image/png" href="../../images/logo.png" />
    <style>
    body {
        margin: 0;
        background: url('../../images/profile.jpg') no-repeat center center fixed;
        background-size: cover;
        font-family: 'Segoe UI', sans-serif;
        color: #eee;
    }
    .page-header {
        max-width: 1850px;
        margin: 60px auto 20px auto;
        padding: 40 40px;
        display: flex;
        justify-content: space-between;
        align-items: center;
    }
    h1.page-title {
        color: #ffa500;
        margin: 0;
        display: flex;
        align-items: center;
        gap: 12px;
        font-weight: 700;
        font-size: 2rem;
    }
    h1.page-title img {
        height: 40px;
        width: auto;
        display: block;
    }
    a.back-link {
        color: #ffa500;
        text-decoration: none;
        font-weight: 600;
        border: 1.5px solid #ffa500;
        padding: 6px 14px;
        border-radius: 8px;
        transition: background-color 0.3s ease, color 0.3s ease;
        white-space: nowrap;
    }
    a.back-link:hover {
        background-color: #ffa500;
        color: #222;
    }
    .content {
        margin: 0 auto 60px auto;
        padding: 40px;
        max-width: 1850px;
        background: rgba(255, 255, 255, 0.08);
        border-radius: 18px;
        backdrop-filter: blur(14px);
        box-shadow: 0 0 20px rgba(255, 165, 0, 0.3);
    }
    .filter-form {
        margin: 20px 0 30px 0;
        display: flex;
        gap: 15px;
        flex-wrap: wrap;
        justify-content: center;
        align-items: center;
    }
    .filter-form label {
        color: #ffa500;
        font-weight: 600;
        user-select: none;
        align-self: center;
    }
    .filter-form input[type="date"],
    .filter-form select {
        padding: 10px 14px;
        border-radius: 10px;
        border: none;
        background: rgba(255, 255, 255, 0.15);
        color: #fff;
        font-size: 15px;
        min-width: 160px;
        transition: background 0.3s;
    }
    .filter-form input[type="date"]:focus,
    .filter-form select:focus {
        outline: none;
        background: rgba(255, 165, 0, 0.25);
        color: #222;
    }
    .filter-form button {
        background: #ffa500;
        border: none;
        padding: 12px 25px;
        font-weight: 700;
        border-radius: 12px;
        cursor: pointer;
        color: #222;
        font-size: 15px;
        transition: background 0.3s;
        white-space: nowrap;
    }
    .filter-form button:hover {
        background: #ffb833;
    }
    table {
        width: 100%;
        border-collapse: collapse;
        background: rgba(255,255,255,0.05);
        border-radius: 12px;
        overflow: hidden;
        font-size: 15px;
    }
    th, td {
        padding: 14px 18px;
        border-bottom: 1px solid rgba(255,255,255,0.1);
        color: #eee;
        text-align: left;
        vertical-align: middle;
    }
    th {
        background-color: rgba(0,0,0,0.4);
        color: #ffa500;
    }
    td:last-child,
    td[style*="text-align:right"] {
        text-align: right;
    }
    .total-row {
        font-weight: 700;
        background: rgba(255,255,255,0.07);
        color: #ffd700;
        font-size: 16px;
        text-align: right;
    }
    @media(max-width: 700px) {
        .filter-form {
            flex-direction: column;
            align-items: center;
        }
        .filter-form label,
        .filter-form input[type="date"],
        .filter-form select,
        .filter-form button {
            min-width: 100%;
        }
    }
    </style>

    <!-- Font Awesome for icons -->
    <script src="https://kit.fontawesome.com/a2e0c6d5c5.js" crossorigin="anonymous"></script>
</head>
<body>

    <div class="page-header">
        <h1 class="page-title">
            <img src="../../images/logo.png" alt="Logo" />
            <?= htmlspecialchars($page_title) ?>
        </h1>
        <a href="reports_dashboard.php" class="back-link">&laquo; Back to Reports Dashboard</a>
    </div>

    <div class="content">

        <form method="get" class="filter-form" aria-label="Filter expenses report">
            <label for="date_from">From:</label>
            <input type="date" id="date_from" name="date_from" value="<?= htmlspecialchars($date_from) ?>" required>

            <label for="date_to">To:</label>
            <input type="date" id="date_to" name="date_to" value="<?= htmlspecialchars($date_to) ?>" required>

            <label for="store_id">Store:</label>
            <select id="store_id" name="store_id" aria-label="Select Store">
                <option value="">All Stores</option>
                <?php foreach ($stores as $store): ?>
                    <option value="<?= $store['id'] ?>" <?= ($store_id == $store['id']) ? 'selected' : '' ?>>
                        <?= htmlspecialchars($store['store_name']) ?>
                    </option>
                <?php endforeach; ?>
            </select>

            <button type="submit">Filter</button>
        </form>

        <table aria-label="Expenses report table">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Date</th>
                    <th>Store</th>
                    <th>Description</th>
                    <th style="text-align:right;">Amount (MWK)</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($expenses)): ?>
                    <tr><td colspan="5" style="text-align:center; color: #ffa500;">No approved expenses found.</td></tr>
                <?php else: ?>
                    <?php foreach ($expenses as $e): ?>
                        <tr>
                            <td><?= htmlspecialchars($e['id']) ?></td>
                            <td><?= htmlspecialchars($e['expense_date']) ?></td>
                            <td><?= htmlspecialchars($e['store_name'] ?? 'N/A') ?></td>
                            <td><?= htmlspecialchars($e['description']) ?></td>
                            <td style="text-align:right;"><?= number_format($e['amount'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
            <?php if (!empty($expenses)): ?>
            <tfoot>
                <tr class="total-row">
                    <td colspan="4">Total Approved Expenses:</td>
                    <td style="text-align:right;"><?= number_format($total, 2) ?></td>
                </tr>
            </tfoot>
            <?php endif; ?>
        </table>

    </div>

<?php include('../../includes/footer.php'); ?>

</body>
</html>
