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

// ================= FILTERS =================
$start_date    = $_GET['start_date'] ?? date('Y-m-d');
$end_date      = $_GET['end_date'] ?? date('Y-m-d');
$product_id    = intval($_GET['product_id'] ?? 0);
$department_id = intval($_GET['department_id'] ?? 5); // default Front Desk

// ================= DROPDOWNS =================
$products_list = $conn->query("SELECT id, name FROM products ORDER BY name ASC");
$departments_list = $conn->query("SELECT id, name FROM departments ORDER BY name ASC");

// ================= SAFE WHERE =================
$where = "WHERE pr.created_at BETWEEN 
        '".$conn->real_escape_string($start_date)." 00:00:00'
    AND '".$conn->real_escape_string($end_date)." 23:59:59'";

if($product_id > 0){
    $where .= " AND pr.product_id = $product_id";
}

if($department_id > 0){
    $where .= " AND ps.department_id = $department_id";
}

// ================= QUERY =================
$sql = "
    SELECT pr.*, 
           p.name AS product_name,
           p.sku,
           p.buying_price,
           u.username,
           ps.department_id
    FROM purchase_requests_front pr
    JOIN products p ON pr.product_id = p.id
    JOIN users u ON pr.requested_by = u.id
    JOIN product_stock ps ON ps.product_id = p.id
    $where
    ORDER BY pr.created_at DESC
";

$report = $conn->query($sql);

if(!$report){
    die("<div class='alert alert-danger'>SQL Error: ".$conn->error."</div>");
}

// ================= TOTALS =================
$report_data = [];
$total_amount = 0;
$total_qty = 0;
$total_requests = $report->num_rows;

while($r = $report->fetch_assoc()){
    $amount = $r['buying_price'] * $r['quantity'];
    $total_amount += $amount;
    $total_qty += $r['quantity'];
    $report_data[] = $r;
}
?>

<div class="container mt-4">

<h4 class="mb-3">🏢 Front Desk Purchase Request Report</h4>

<!-- ================= FILTER ================= -->
<form method="GET" class="row g-3 mb-4">

    <div class="col-md-3">
        <label>Start Date</label>
        <input type="date" name="start_date" class="form-control" value="<?= $start_date ?>">
    </div>

    <div class="col-md-3">
        <label>End Date</label>
        <input type="date" name="end_date" class="form-control" value="<?= $end_date ?>">
    </div>

    <div class="col-md-3">
        <label>Product</label>
        <select name="product_id" class="form-control">
            <option value="">All</option>
            <?php while($p = $products_list->fetch_assoc()): ?>
                <option value="<?= $p['id'] ?>" <?= $product_id==$p['id']?'selected':'' ?>>
                    <?= htmlspecialchars($p['name']) ?>
                </option>
            <?php endwhile; ?>
        </select>
    </div>

    <div class="col-md-3">
        <label>Department</label>
        <select name="department_id" class="form-control">
            <option value="">All</option>
            <?php while($d = $departments_list->fetch_assoc()): ?>
                <option value="<?= $d['id'] ?>" <?= $department_id==$d['id']?'selected':'' ?>>
                    <?= htmlspecialchars($d['name']) ?>
                </option>
            <?php endwhile; ?>
        </select>
    </div>

    <div class="col-md-12 mt-2">
        <button class="btn btn-primary">Filter Report</button>
        <a href="purchase_request_report_front.php" class="btn btn-secondary">Reset</a>
        <button type="button" onclick="window.print()" class="btn btn-dark">
            🖨 Print
        </button>
    </div>

</form>

<!-- ================= SUMMARY CARDS ================= -->
<div class="row mb-4">

    <div class="col-md-4">
        <div class="card bg-info text-white">
            <div class="card-body">
                <h5>Total Requests</h5>
                <h3><?= $total_requests ?></h3>
            </div>
        </div>
    </div>

    <div class="col-md-4">
        <div class="card bg-success text-white">
            <div class="card-body">
                <h5>Total Quantity</h5>
                <h3><?= $total_qty ?></h3>
            </div>
        </div>
    </div>

    <div class="col-md-4">
        <div class="card bg-warning text-dark">
            <div class="card-body">
                <h5>Total Amount</h5>
                <h3><?= number_format($total_amount,2) ?></h3>
            </div>
        </div>
    </div>

</div>

<!-- ================= TABLE ================= -->
<table class="table table-bordered table-striped">
    <thead class="bg-dark text-white">
        <tr>
            <th>Date</th>
            <th>SKU</th>
            <th>Product</th>
            <th>Price</th>
            <th>Qty</th>
            <th>Amount</th>
            <th>Requested By</th>
        </tr>
    </thead>

    <tbody>

    <?php if(empty($report_data)): ?>
        <tr>
            <td colspan="7" class="text-center">No records found</td>
        </tr>
    <?php endif; ?>

    <?php foreach($report_data as $r): 
        $amount = $r['buying_price'] * $r['quantity'];
    ?>
        <tr>
            <td><?= $r['created_at'] ?></td>
            <td><?= htmlspecialchars($r['sku']) ?></td>
            <td><?= htmlspecialchars($r['product_name']) ?></td>
            <td><?= number_format($r['buying_price'],2) ?></td>
            <td><?= (int)$r['quantity'] ?></td>
            <td><?= number_format($amount,2) ?></td>
            <td><?= htmlspecialchars($r['username']) ?></td>
        </tr>
    <?php endforeach; ?>

    <tr class="table-success">
        <td colspan="5" class="text-end"><strong>Total:</strong></td>
        <td colspan="2"><strong><?= number_format($total_amount,2) ?></strong></td>
    </tr>

    </tbody>
</table>

</div>

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