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

include('../../includes/config.php');
include('../../includes/header.php');

$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$store_id = intval($_GET['store_id'] ?? 0);
$category_id = intval($_GET['category_id'] ?? 0);

// Fetch stores
$stores = [];
$res = $conn->query("SELECT id, store_name FROM stores ORDER BY store_name");
while ($row = $res->fetch_assoc()) $stores[] = $row;

// Fetch categories
$categories = [];
$res = $conn->query("SELECT id, category_name FROM expense_categories ORDER BY category_name");
while ($row = $res->fetch_assoc()) $categories[] = $row;

// Build WHERE
$where = "WHERE e.expense_date BETWEEN ? AND ?";
$params = [$date_from, $date_to];
$types = "ss";

if ($store_id > 0) {
    $where .= " AND e.store_id = ?";
    $params[] = $store_id;
    $types .= "i";
}

if ($category_id > 0) {
    $where .= " AND e.expense_category_id = ?";
    $params[] = $category_id;
    $types .= "i";
}

$sql = "SELECT e.*, s.store_name, c.category_name, u.first_name, u.last_name
        FROM expenses e
        JOIN stores s ON e.store_id = s.id
        JOIN expense_categories c ON e.expense_category_id = c.id
        LEFT JOIN users u ON e.added_by = u.id
        $where
        ORDER BY e.expense_date DESC, e.id DESC";

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

$total_expenses = 0;
?>

<style>
body, html {
    margin: 0;
    padding: 50px 20px 80px;
    font-family: 'Segoe UI', sans-serif;
    background: url('../../images/profile.jpg') no-repeat center center fixed;
    background-size: cover;
    color: #eee;
    box-sizing: border-box;
    overflow-x: hidden;
}

.wrap {
    max-width: 1850px;
    margin: 0 auto;
    padding: 30px 40px;
    background: rgba(255, 255, 255, 0.08);
    border-radius: 16px;
    backdrop-filter: blur(8px);
    box-shadow: 0 4px 30px rgba(0,0,0,0.1);
}

h2 {
    color: #ffa500;
    font-size: 2rem;
    font-weight: 700;
    margin-bottom: 30px;
    text-align: center;
}

.filters {
    display: flex;
    flex-wrap: wrap;
    justify-content: center;
    gap: 15px;
    margin-bottom: 30px;
}

.filters input,
.filters select {
    padding: 10px 14px;
    border-radius: 8px;
    border: none;
    background: rgba(255, 255, 255, 0.15);
    color: #eee;
    font-size: 14px;
    min-width: 160px;
}

.filters button {
    background: #ffa500;
    color: #222;
    border: none;
    padding: 10px 18px;
    border-radius: 8px;
    font-weight: 700;
    cursor: pointer;
    transition: background 0.3s;
}

.filters button:hover {
    background: #ffb733;
}

.actions {
    display: flex;
    justify-content: flex-end;
    gap: 15px;
    margin-bottom: 15px;
}

.actions button {
    background: rgba(255, 165, 0, 0.85);
    color: #222;
    border: none;
    padding: 10px 18px;
    border-radius: 8px;
    font-weight: bold;
    cursor: pointer;
    transition: background 0.3s;
}

.actions button:hover {
    background: #ffb733;
}

table {
    width: 100%;
    border-collapse: collapse;
    font-size: 14px;
}

table th,
table td {
    padding: 10px 14px;
    border-bottom: 1px solid rgba(255,255,255,0.15);
}

table th {
    background: rgba(255, 165, 0, 0.2);
    color: #fff;
    font-weight: 600;
    text-align: left;
}

table td.amount,
table th.amount {
    text-align: right;
}

.no-data {
    text-align: center;
    font-weight: bold;
    margin: 30px 0;
    font-size: 18px;
}

.modal {
    display: none;
    position: fixed;
    inset: 0;
    background: rgba(0,0,0,0.7);
    justify-content: center;
    align-items: center;
    z-index: 9999;
}

.modal-content {
    background: rgba(255,255,255,0.1);
    color: #eee;
    padding: 25px 30px;
    border-radius: 12px;
    max-width: 480px;
    width: 100%;
    backdrop-filter: blur(8px);
    box-shadow: 0 0 25px rgba(255,165,0,0.5);
}

.modal-content h3 {
    margin-top: 0;
    color: #ffa500;
    font-weight: 700;
    font-size: 1.5rem;
    margin-bottom: 20px;
}

.modal-content input,
.modal-content textarea,
.modal-content select {
    width: 100%;
    margin-bottom: 15px;
    padding: 10px;
    border-radius: 6px;
    border: none;
    font-size: 1rem;
    color: #333;
}

.modal-content button {
    padding: 10px 16px;
    font-weight: bold;
    border: none;
    border-radius: 6px;
    cursor: pointer;
}

.close-btn {
    background: #ef4444;
    color: #fff;
    margin-right: 10px;
}

.save-btn {
    background: #22c55e;
    color: #fff;
}
</style>

<div class="wrap">
    <h2>📋 Expense Report</h2>

    <form method="get" class="filters">
        <input type="date" name="date_from" value="<?= htmlspecialchars($date_from) ?>" required>
        <input type="date" name="date_to" value="<?= htmlspecialchars($date_to) ?>" required>
        <select name="store_id">
            <option value="0">All Stores</option>
            <?php foreach ($stores as $store): ?>
                <option value="<?= $store['id'] ?>" <?= ($store_id === intval($store['id'])) ? 'selected' : '' ?>>
                    <?= htmlspecialchars($store['store_name']) ?>
                </option>
            <?php endforeach; ?>
        </select>
        <select name="category_id">
            <option value="0">All Categories</option>
            <?php foreach ($categories as $cat): ?>
                <option value="<?= $cat['id'] ?>" <?= ($category_id === intval($cat['id'])) ? 'selected' : '' ?>>
                    <?= htmlspecialchars($cat['category_name']) ?>
                </option>
            <?php endforeach; ?>
        </select>
        <button type="submit">Apply Filters</button>
    </form>

    <div class="actions">
        <button onclick="window.print()">🖨 Print</button>
        <button onclick="exportTableToExcel('expenseTable', 'Expense_Report')">⬇ Export to Excel</button>
    </div>

    <?php if ($result->num_rows === 0): ?>
        <div class="no-data">No expenses found for the selected criteria.</div>
    <?php else: ?>
        <table id="expenseTable">
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Store</th>
                    <th>Category</th>
                    <th>Description</th>
                    <th class="amount">Amount (MWK)</th>
                    <th>Supporting Document</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = $result->fetch_assoc()):
                    $total_expenses += $row['amount'];
                    $doc = $row['supporting_document'] && file_exists('../../uploads/expenses/' . $row['supporting_document'])
                            ? '../../uploads/expenses/' . rawurlencode($row['supporting_document'])
                            : null;
                    $fullName = trim($row['first_name'] . ' ' . $row['last_name']);
                ?>
                <tr onclick="showExpenseModal(
                    <?= $row['id'] ?>,
                    '<?= htmlspecialchars($row['expense_date']) ?>',
                    '<?= htmlspecialchars($row['store_name']) ?>',
                    <?= $row['expense_category_id'] ?>,
                    `<?= htmlspecialchars($row['description']) ?>`,
                    <?= $row['amount'] ?>,
                    '<?= $doc ?>',
                    '<?= htmlspecialchars($fullName) ?>',
                    this
                )">
                    <td><?= htmlspecialchars($row['expense_date']) ?></td>
                    <td><?= htmlspecialchars($row['store_name']) ?></td>
                    <td><?= htmlspecialchars($row['category_name']) ?></td>
                    <td><?= htmlspecialchars($row['description'] ?: '-') ?></td>
                    <td class="amount"><?= number_format($row['amount'], 2) ?></td>
                    <td><?= $doc ? '<a href="'.$doc.'" target="_blank">View</a>' : '-' ?></td>
                </tr>
                <?php endwhile; ?>
                <tr>
                    <td colspan="4" style="text-align:right;font-weight:bold;">Total Expenses:</td>
                    <td class="amount" style="font-weight:bold;"><?= number_format($total_expenses, 2) ?></td>
                    <td></td>
                </tr>
            </tbody>
        </table>
    <?php endif; ?>
</div>

<div class="modal" id="expenseModal">
    <div class="modal-content">
        <h3>Edit Expense</h3>
        <input type="hidden" id="modalId">
        <p><strong>Date:</strong> <span id="mDate"></span></p>
        <p><strong>Store:</strong> <span id="mStore"></span></p>
        <p><strong>Category:</strong>
            <select id="mCategory">
                <?php foreach ($categories as $cat): ?>
                    <option value="<?= $cat['id'] ?>"><?= htmlspecialchars($cat['category_name']) ?></option>
                <?php endforeach; ?>
            </select>
        </p>
        <p><strong>Description:</strong> <textarea id="mDesc" rows="2"></textarea></p>
        <p><strong>Amount:</strong> <input type="number" id="mAmount" step="0.01"></p>
        <p><strong>Document:</strong> <span id="mDoc"></span></p>
        <p><strong>Added By:</strong> <span id="mAddedBy"></span></p>
        <button class="close-btn" onclick="closeModal()">Close</button>
        <button class="save-btn" onclick="saveChanges()">Save</button>
    </div>
</div>

<script>
let currentRow = null;

function exportTableToExcel(tableID, filename = '') {
    const tableHTML = encodeURIComponent(document.getElementById(tableID).outerHTML);
    const link = document.createElement("a");
    link.href = 'data:application/vnd.ms-excel,' + tableHTML;
    link.download = filename ? filename + '.xls' : 'expenses.xls';
    link.click();
}

function showExpenseModal(id, date, store, categoryId, description, amount, doc, addedBy, row) {
    document.getElementById('modalId').value = id;
    document.getElementById('mDate').innerText = date;
    document.getElementById('mStore').innerText = store;
    document.getElementById('mCategory').value = categoryId;
    document.getElementById('mDesc').value = description;
    document.getElementById('mAmount').value = amount;
    document.getElementById('mDoc').innerHTML = doc ? `<a href="${doc}" target="_blank">View</a>` : '-';
    document.getElementById('mAddedBy').innerText = addedBy;
    currentRow = row;
    document.getElementById('expenseModal').style.display = 'flex';
}

function closeModal() {
    document.getElementById('expenseModal').style.display = 'none';
}

function saveChanges() {
    const id = document.getElementById('modalId').value;
    const amount = parseFloat(document.getElementById('mAmount').value);
    const desc = document.getElementById('mDesc').value;
    const catId = document.getElementById('mCategory').value;

    if (!amount || amount <= 0) {
        alert('Invalid amount');
        return;
    }

    const formData = new FormData();
    formData.append('id', id);
    formData.append('amount', amount);
    formData.append('description', desc);
    formData.append('category_id', catId);

    fetch('update_expense.php', {
        method: 'POST',
        body: formData
    }).then(res => res.text()).then(response => {
        if (response.trim() === 'success') {
            alert('Updated!');
            const cells = currentRow.getElementsByTagName('td');
            cells[2].innerText = document.getElementById('mCategory').selectedOptions[0].text;
            cells[3].innerText = desc || '-';
            cells[4].innerText = amount.toFixed(2);
            closeModal();
        } else {
            alert('Failed: ' + response);
        }
    }).catch(err => alert('Error: ' + err));
}
</script>

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