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

$username = $_SESSION['username'] ?? 'Guest';

/* ================= ADD PERISHABLE ================= */

if(isset($_POST['add_perishable'])){

$product_id = intval($_POST['product_id']);
$product_name = $_POST['product_name'];
$department_id = intval($_POST['department_id']);
$quantity = intval($_POST['quantity']);
$expiry = $_POST['expiry_date'];
$batch = $_POST['batch_number'];

$stmt = $conn->prepare("
INSERT INTO perishable_products
(product_id,product_name,department_id,quantity,expiry_date,batch_number,recorded_by)
VALUES (?,?,?,?,?,?,?)
");

$stmt->bind_param(
"isiisss",
$product_id,
$product_name,
$department_id,
$quantity,
$expiry,
$batch,
$username
);

$stmt->execute();

echo "<script>alert('Perishable product recorded successfully');window.location='perishable_products.php';</script>";
exit;
}


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

$where = "WHERE 1=1";

if(!empty($_GET['department'])){
$dept = intval($_GET['department']);
$where .= " AND pp.department_id=$dept";
}

if(!empty($_GET['product'])){
$product = intval($_GET['product']);
$where .= " AND pp.product_id=$product";
}

if(!empty($_GET['expiry_from'])){
$from = $_GET['expiry_from'];
$where .= " AND pp.expiry_date >= '$from'";
}

if(!empty($_GET['expiry_to'])){
$to = $_GET['expiry_to'];
$where .= " AND pp.expiry_date <= '$to'";
}


/* ================= FETCH PRODUCTS ================= */

$products = $conn->query("
SELECT id,name,unit,min_level
FROM perishables
ORDER BY name
");

/* ================= FETCH DEPARTMENTS ================= */

$departments = $conn->query("
SELECT id,name 
FROM departments 
ORDER BY name
");


/* ================= FETCH PERISHABLES ================= */

$perishables = $conn->query("
SELECT pp.*, d.name AS department
FROM perishable_products pp
LEFT JOIN departments d ON pp.department_id=d.id
$where
ORDER BY pp.expiry_date ASC
");


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

$total_stock = $conn->query("
SELECT SUM(quantity) total FROM perishable_products
")->fetch_assoc()['total'];

$expired = $conn->query("
SELECT COUNT(*) total FROM perishable_products
WHERE expiry_date < CURDATE()
")->fetch_assoc()['total'];

$expiring = $conn->query("
SELECT COUNT(*) total FROM perishable_products
WHERE expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 5 DAY)
")->fetch_assoc()['total'];

?>

<div class="container mt-4">
     <button class="btn btn-secondary" onclick="history.back()">
            <i class="fa fa-arrow-left"></i> Back
        </button>
 <button class="btn btn-secondary" onclick="window.location.href='dispatch_report.php'">
            <i class="fa fa-share"></i>Dispatch Report
        </button>
<h3>🥬 Perishable Inventory Management</h3>

<hr>


<!-- SUMMARY -->

<div class="row mb-3">

<div class="col-md-4">
<div class="alert alert-success">
<b>Total Stock:</b> <?= $total_stock ?>
</div>
</div>

<div class="col-md-4">
<div class="alert alert-warning">
<b>Expiring Soon:</b> <?= $expiring ?>
</div>
</div>

<div class="col-md-4">
<div class="alert alert-danger">
<b>Expired:</b> <?= $expired ?>
</div>
</div>

</div>



<!-- EXPORT BUTTONS -->

<div class="mb-3">

<a href="export_perishablesp_excel.php" class="btn btn-success">
<i class="fa fa-file-excel"></i> Export Excel
</a>

<a href="export_perishablesp_pdf.php" class="btn btn-danger">
<i class="fa fa-file-pdf"></i> Export PDF
</a>

</div>


<hr>


<!-- FILTERS -->

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

<div class="col-md-3">

<select name="product" class="form-control">
<option value="">All Products</option>

<?php
$p2 = $conn->query("SELECT id,name FROM perishables ORDER BY name");
while($p=$p2->fetch_assoc()):
?>

<option value="<?= $p['id'] ?>"><?= $p['name'] ?></option>

<?php endwhile; ?>

</select>

</div>

<div class="col-md-3">

<select name="department" class="form-control">
<option value="">All Departments</option>

<?php
$d2 = $conn->query("SELECT id,name FROM departments");
while($d=$d2->fetch_assoc()):
?>

<option value="<?= $d['id'] ?>"><?= $d['name'] ?></option>

<?php endwhile; ?>

</select>

</div>

<div class="col-md-2">
<input type="date" name="expiry_from" class="form-control">
</div>

<div class="col-md-2">
<input type="date" name="expiry_to" class="form-control">
</div>

<div class="col-md-2">
<button class="btn btn-primary w-100">
<i class="fa fa-search"></i> Filter
</button>
</div>

</form>



<hr>


<!-- ADD FORM -->

<h5>Add Perishable</h5>

<form method="POST" class="row g-2">

<div class="col-md-3">

<select name="product_id" id="productSelect" class="form-control" required>

<option value="">Select Product</option>

<?php
$p3 = $conn->query("SELECT id,name,unit FROM perishables ORDER BY name");

while($p=$p3->fetch_assoc()):
?>

<option 
value="<?= $p['id'] ?>"
data-name="<?= htmlspecialchars($p['name']) ?>"
>

<?= $p['name'] ?> (<?= $p['unit'] ?>)

</option>

<?php endwhile; ?>

</select>

<input type="hidden" name="product_name" id="product_name">

</div>


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

<select name="department_id" class="form-control" required>

<option value="">Dept</option>

<?php
$d3=$conn->query("SELECT * FROM departments");

while($d=$d3->fetch_assoc()):
?>

<option value="<?= $d['id'] ?>">
<?= $d['name'] ?>
</option>

<?php endwhile; ?>

</select>

</div>


<div class="col-md-2">
<input type="number" name="quantity" placeholder="Quantity" class="form-control" required>
</div>


<div class="col-md-2">
<input type="text" name="batch_number" placeholder="Batch" class="form-control">
</div>


<div class="col-md-2">
<input type="date" name="expiry_date" class="form-control" required>
</div>


<div class="col-md-1">

<button type="submit" name="add_perishable" class="btn btn-success">

<i class="fa fa-save"></i>

</button>

</div>

</form>


<hr>



<!-- STOCK TABLE -->

<h4>📋 Perishable Stock</h4>

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

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

<tr>

<th>Product</th>
<th>Department</th>
<th>Qty</th>
<th>Batch</th>
<th>Expiry</th>
<th>Recorded By</th>
<th>Dispatch</th>

</tr>

</thead>

<tbody>

<?php

$today = date('Y-m-d');

while($row=$perishables->fetch_assoc()):

$exp_class='';

if($row['expiry_date'] < $today){
$exp_class='table-danger';
}
elseif($row['expiry_date'] <= date('Y-m-d',strtotime('+5 days'))){
$exp_class='table-warning';
}

?>

<tr class="<?= $exp_class ?>">

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

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

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

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

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

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

<td>

<a href="dispatch_perishable.php?id=<?= $row['id'] ?>" class="btn btn-sm btn-primary">

<i class="fa fa-share"></i> Dispatch

</a>

</td>

</tr>

<?php endwhile; ?>

</tbody>

</table>

</div>


<script>

document.getElementById('productSelect').addEventListener('change',function(){

var name=this.options[this.selectedIndex].getAttribute('data-name');

document.getElementById('product_name').value=name;

});

</script>

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