<?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
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$store_id = $_GET['store_id'] ?? '';
$category_id = $_GET['category_id'] ?? '';

// Fetch expenses
$where = "e.status = 'approved' AND e.reconciliation_status != 'reconciled' AND e.expense_date BETWEEN '$date_from' AND '$date_to'";
if ($store_id !== '') $where .= " AND e.store_id = " . intval($store_id);
if ($category_id !== '') $where .= " AND e.expense_category_id = " . intval($category_id);

$expenses = $conn->query("
    SELECT e.*, s.store_name, ec.category_name 
    FROM expenses e
    LEFT JOIN stores s ON e.store_id = s.id
    LEFT JOIN expense_categories ec ON e.expense_category_id = ec.id
    WHERE $where
    ORDER BY e.expense_date DESC
");

// Fetch matching bank transactions
$bank_txns = $conn->query("
    SELECT * FROM bank_transactions 
    WHERE type = 'expense_payment' AND reconciled = 0 AND status = 'approved'
    ORDER BY transaction_date DESC
");

// Stores & categories for filters
$stores = $conn->query("SELECT id, store_name FROM stores");
$categories = $conn->query("SELECT id, category_name FROM expense_categories");
?>

<style>
body {
    background: url('../../images/profile.jpg') center center fixed;
    background-size: cover;
    font-family: 'Segoe UI', sans-serif;
    color: #eee;
}
.container {
    max-width: 1850px;
    margin: 40px auto;
    background: rgba(255,255,255,0.08);
    padding: 25px;
    border-radius: 12px;
    backdrop-filter: blur(12px);
}
h2 { color: #ffa500; text-align: center; }
label, select, input { margin-right: 10px; color: #000; }
table { width: 100%; margin-top: 20px; border-collapse: collapse; }
th, td {
    padding: 10px; text-align: left;
    border-bottom: 1px solid rgba(255,255,255,0.1);
}
th { color: #ffa500; }
select, input[type="date"] {
    padding: 4px 6px;
    border-radius: 4px;
    margin-top: 5px;
}
button {
    padding: 6px 12px;
    border-radius: 6px;
    font-weight: bold;
    border: none;
    cursor: pointer;
}
.reconcile-btn {
    background: #4CAF50;
    color: white;
}
</style>

<div class="container">
    <h2>Expense Reconciliation</h2>

    <form method="GET" style="margin-bottom: 15px;">
        <label>From: <input type="date" name="date_from" value="<?= $date_from ?>"></label>
        <label>To: <input type="date" name="date_to" value="<?= $date_to ?>"></label>
        <label>Store:
            <select name="store_id">
                <option value="">All</option>
                <?php while ($s = $stores->fetch_assoc()): ?>
                    <option value="<?= $s['id'] ?>" <?= $store_id == $s['id'] ? 'selected' : '' ?>>
                        <?= htmlspecialchars($s['store_name']) ?>
                    </option>
                <?php endwhile; ?>
            </select>
        </label>
        <label>Category:
            <select name="category_id">
                <option value="">All</option>
                <?php while ($c = $categories->fetch_assoc()): ?>
                    <option value="<?= $c['id'] ?>" <?= $category_id == $c['id'] ? 'selected' : '' ?>>
                        <?= htmlspecialchars($c['category_name']) ?>
                    </option>
                <?php endwhile; ?>
            </select>
        </label>
        <button type="submit">Filter</button>
    </form>

    <form method="POST" action="reconcile_expense_action.php" onsubmit="return confirm('Are you sure you want to reconcile this expense?')">
        <table>
            <thead>
                <tr>
                    <th>Ref</th>
                    <th>Date</th>
                    <th>Store</th>
                    <th>Category</th>
                    <th>Description</th>
                    <th>Amount</th>
                    <th>Bank Transaction Match</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody>
            <?php while ($row = $expenses->fetch_assoc()): ?>
                <tr>
                    <td><?= $row['reference'] ?></td>
                    <td><?= $row['expense_date'] ?></td>
                    <td><?= $row['store_name'] ?></td>
                    <td><?= $row['category_name'] ?></td>
                    <td><?= $row['description'] ?></td>
                    <td><?= number_format($row['amount'], 2) ?></td>
                    <td>
                        <select name="bank_txn_id[<?= $row['id'] ?>]" required>
                            <option value="">-- Select --</option>
                            <?php mysqli_data_seek($bank_txns, 0); ?>
                            <?php while ($txn = $bank_txns->fetch_assoc()): ?>
                                <?php if (abs($txn['amount'] - $row['amount']) < 0.01): ?>
                                    <option value="<?= $txn['id'] ?>">
                                        <?= $txn['reference'] ?> | <?= $txn['transaction_date'] ?> | <?= number_format($txn['amount'], 2) ?>
                                    </option>
                                <?php endif; ?>
                            <?php endwhile; ?>
                        </select>
                    </td>
                    <td>
                        <button type="submit" name="reconcile[<?= $row['id'] ?>]" class="reconcile-btn">Reconcile</button>
                    </td>
                </tr>
            <?php endwhile; ?>
            <?php if ($expenses->num_rows === 0): ?>
                <tr><td colspan="8" style="text-align:center;">No matching expenses found.</td></tr>
            <?php endif; ?>
            </tbody>
        </table>
    </form>
</div>

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