<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';

$username = $_SESSION['username'] ?? 'Guest';
$today = date('Y-m-d');

// ================= FILTERS =================
$from_date = $_GET['from'] ?? $today;
$to_date   = $_GET['to'] ?? $today;
$status    = $_GET['status'] ?? '';
$department= $_GET['department'] ?? '';
$search    = $_GET['search'] ?? '';

// ================= BUILD WHERE =================
$where = [];
$where[] = "r.date >= '".$conn->real_escape_string($from_date)."'";
$where[] = "r.date <= '".$conn->real_escape_string($to_date)."'";

if($status){
    $where[] = "r.status = '".$conn->real_escape_string($status)."'";
}
if($department){
    $where[] = "r.department = '".$conn->real_escape_string($department)."'";
}
if($search){
    $s = $conn->real_escape_string($search);
    $where[] = "(r.item_name LIKE '%$s%' OR r.requested_by LIKE '%$s%')";
}

$where_sql = "WHERE ".implode(" AND ", $where);

// ================= FETCH REQUISITIONS =================
$query = "
SELECT r.*
FROM requisitions r
$where_sql
ORDER BY r.date DESC
";

$result = $conn->query($query);

// ================= SUMMARY =================
$summary = $conn->query("
SELECT 
    COUNT(*) AS total_requisitions,
    SUM(CASE WHEN department='Bar' THEN 1 ELSE 0 END) AS bar_count,
    SUM(CASE WHEN department='Restaurant' THEN 1 ELSE 0 END) AS restaurant_count,
    SUM(CASE WHEN department='Housekeeping' THEN 1 ELSE 0 END) AS hk_count,
    SUM(CASE WHEN status='Pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN status='Approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(CASE WHEN status='Rejected' THEN 1 ELSE 0 END) AS rejected_count
FROM requisitions r
$where_sql
")->fetch_assoc();

$total_requisitions = $summary['total_requisitions'] ?? 0;
$bar_count          = $summary['bar_count'] ?? 0;
$restaurant_count   = $summary['restaurant_count'] ?? 0;
$hk_count           = $summary['hk_count'] ?? 0;
$pending_count      = $summary['pending_count'] ?? 0;
$approved_count     = $summary['approved_count'] ?? 0;
$rejected_count     = $summary['rejected_count'] ?? 0;
?>

<style>
.report-card{border-radius:12px;}
.table thead th{white-space:nowrap;}
.welcome-msg{background:#f8f9fa;padding:8px 12px;border-radius:8px;margin-bottom:10px;}
@media print{.no-print{display:none!important;}}
</style>

<div class="container-fluid mt-4">
    <button class="btn btn-secondary no-print" onclick="history.back()"><i class="fa fa-arrow-left"></i> Back</button>

    <div class="welcome-msg no-print">
        <i class="fa fa-user-circle"></i>
        Welcome, <strong><?= htmlspecialchars($username) ?></strong>
    </div>

    <h3 class="mb-2"><i class="fa fa-clipboard-list text-primary"></i> Requisitions Report</h3>
    <hr>

    <!-- ================= FILTERS ================= -->
    <form method="GET" class="row g-2 mb-3 no-print">
        <div class="col-md-2"><input type="date" name="from" class="form-control" value="<?= htmlspecialchars($from_date) ?>"></div>
        <div class="col-md-2"><input type="date" name="to" class="form-control" value="<?= htmlspecialchars($to_date) ?>"></div>
        <div class="col-md-2">
            <select name="department" class="form-control">
                <option value="">All Departments</option>
                <option <?= $department=='Bar'?'selected':'' ?>>Bar</option>
                <option <?= $department=='Restaurant'?'selected':'' ?>>Restaurant</option>
                <option <?= $department=='Housekeeping'?'selected':'' ?>>Housekeeping</option>
            </select>
        </div>
        <div class="col-md-2">
            <select name="status" class="form-control">
                <option value="">All Statuses</option>
                <option <?= $status=='Pending'?'selected':'' ?>>Pending</option>
                <option <?= $status=='Approved'?'selected':'' ?>>Approved</option>
                <option <?= $status=='Rejected'?'selected':'' ?>>Rejected</option>
            </select>
        </div>
        <div class="col-md-2"><input type="text" name="search" class="form-control" placeholder="Item / Requested By" value="<?= htmlspecialchars($search) ?>"></div>
        <div class="col-md-2 d-flex gap-1">
            <button class="btn btn-primary w-100"><i class="fa fa-filter"></i> Filter</button>
            <a href="requisitions_report.php" class="btn btn-secondary w-100">Reset</a>
        </div>
    </form>

    <!-- ================= ACTION BUTTONS ================= -->
    <div class="mb-3 no-print d-flex gap-2">
        <a href="export_requisitions_excel.php?from=<?= urlencode($from_date) ?>&to=<?= urlencode($to_date) ?>&department=<?= urlencode($department) ?>&status=<?= urlencode($status) ?>&search=<?= urlencode($search) ?>" class="btn btn-success">
            <i class="fa fa-file-excel"></i> Export Excel
        </a>
        <button onclick="window.print()" class="btn btn-info"><i class="fa fa-print"></i> Print / PDF</button>
    </div>

    <!-- ================= SUMMARY CARDS ================= -->
    <div class="row mb-3 text-center">
        <div class="col-md-2">
            <div class="card report-card shadow-sm">
                <div class="card-body">
                    <h6>Total Requisitions</h6>
                    <h4 class="text-primary"><?= $total_requisitions ?></h4>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card report-card shadow-sm">
                <div class="card-body">
                    <h6>Bar</h6>
                    <h4 class="text-warning"><?= $bar_count ?></h4>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card report-card shadow-sm">
                <div class="card-body">
                    <h6>Restaurant</h6>
                    <h4 class="text-success"><?= $restaurant_count ?></h4>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card report-card shadow-sm">
                <div class="card-body">
                    <h6>Housekeeping</h6>
                    <h4 class="text-info"><?= $hk_count ?></h4>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card report-card shadow-sm">
                <div class="card-body">
                    <h6>Pending</h6>
                    <h4 class="text-warning"><?= $pending_count ?></h4>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card report-card shadow-sm">
                <div class="card-body">
                    <h6>Approved</h6>
                    <h4 class="text-success"><?= $approved_count ?></h4>
                </div>
            </div>
        </div>
    </div>

    <!-- ================= TABLE ================= -->
    <div class="table-responsive">
        <table class="table table-bordered table-striped table-sm">
            <thead class="table-dark">
                <tr>
                    <th>ID</th>
                    <th>Department</th>
                    <th>Item Name</th>
                    <th>Qty</th>
                    <th>Requested By</th>
                    <th>Status</th>
                    <th>Date</th>
                </tr>
            </thead>
            <tbody>
                <?php if($result && $result->num_rows): ?>
                    <?php while($row = $result->fetch_assoc()): ?>
                        <tr>
                            <td><?= $row['id'] ?></td>
                            <td><?= $row['department'] ?></td>
                            <td><?= htmlspecialchars($row['item_name']) ?></td>
                            <td><?= $row['qty'] ?></td>
                            <td><?= htmlspecialchars($row['requested_by']) ?></td>
                            <td><?= $row['status'] ?></td>
                            <td><?= $row['date'] ?></td>
                        </tr>
                    <?php endwhile; ?>
                <?php else: ?>
                    <tr><td colspan="7" class="text-center text-danger">No requisitions found</td></tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

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