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

$where = "WHERE 1=1";

/* ================= FILTERS ================= */

$date_from = $_GET['date_from'] ?? '';
$date_to = $_GET['date_to'] ?? '';
$department = $_GET['department'] ?? '';
$product = $_GET['product'] ?? '';

if($date_from != ''){
$where .= " AND DATE(pd.dispatched_at) >= '$date_from'";
}

if($date_to != ''){
$where .= " AND DATE(pd.dispatched_at) <= '$date_to'";
}

if($department != ''){
$where .= " AND pd.department_id = '$department'";
}

if($product != ''){
$where .= " AND pp.product_name LIKE '%$product%'";
}

/* ================= FETCH DATA ================= */

$sql = "
SELECT 
pd.*,
pp.product_name,
pp.batch_number,
d.name AS department
FROM perishable_dispatch pd
LEFT JOIN perishable_products pp ON pd.perishable_id = pp.id
LEFT JOIN departments d ON pd.department_id = d.id
$where
ORDER BY pd.dispatched_at DESC
";

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

/* ================= SUMMARY ================= */

$summary = $conn->query("
SELECT SUM(quantity) as total_qty
FROM perishable_dispatch pd
$where
")->fetch_assoc();

?>

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

<h3>📦 Perishable Dispatch Report</h3>
<hr>

<!-- FILTERS -->

<form method="GET" class="row g-2 mb-3">

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

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

<div class="col-md-3">
<label>Department</label>

<select name="department" class="form-control">

<option value="">All Departments</option>

<?php
$dept = $conn->query("SELECT * FROM departments");
while($d=$dept->fetch_assoc()):
?>

<option value="<?= $d['id'] ?>" <?= ($department==$d['id'])?'selected':'' ?>>

<?= $d['name'] ?>

</option>

<?php endwhile; ?>

</select>

</div>

<div class="col-md-3">
<label>Product</label>
<input type="text" name="product" value="<?= $product ?>" placeholder="Search product" class="form-control">
</div>

<div class="col-md-12 mt-2">

<button class="btn btn-primary">
<i class="fa fa-search"></i> Filter
</button>

<a href="dispatch_report.php" class="btn btn-secondary">
Reset
</a>

<a href="export_dispatch_excel.php?date_from=<?= $date_from ?>&date_to=<?= $date_to ?>&department=<?= $department ?>&product=<?= $product ?>" class="btn btn-success">
<i class="fa fa-file-excel"></i> Export Excel
</a>

<a href="export_dispatch_pdf.php?date_from=<?= $date_from ?>&date_to=<?= $date_to ?>&department=<?= $department ?>&product=<?= $product ?>" class="btn btn-danger">
<i class="fa fa-file-pdf"></i> Export PDF
</a>

</div>

</form>

<!-- SUMMARY -->

<div class="alert alert-info">

<b>Total Quantity Dispatched:</b>

<?= $summary['total_qty'] ?? 0 ?>

</div>

<!-- TABLE -->

<table class="table table-bordered table-striped">

<thead class="bg-dark text-white">

<tr>

<th>Date</th>
<th>Product</th>
<th>Batch</th>
<th>Department</th>
<th>Quantity</th>
<th>Collected By</th>
<th>Issued By</th>

</tr>

</thead>

<tbody>

<?php while($row=$data->fetch_assoc()): ?>

<tr>

<td><?= date('Y-m-d',strtotime($row['dispatched_at'])) ?></td>

<td><?= htmlspecialchars($row['product_name']) ?></td>

<td><?= $row['batch_number'] ?></td>

<td><?= $row['department'] ?></td>

<td><?= $row['quantity'] ?></td>

<td><?= $row['collected_by'] ?></td>

<td><?= $row['dispatched_by'] ?></td>

</tr>

<?php endwhile; ?>

</tbody>

</table>

</div>

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