<?php
require "db.php";

/* -----------------------------
   SEARCH
------------------------------*/
$search = mysqli_real_escape_string($conn, $_GET['search'] ?? '');

/* -----------------------------
   FETCH LOW STOCK
------------------------------*/
$sql = "
SELECT
    p.id,
    p.name,
    p.price,
    p.reorder_level,
    s.qty_on_hand,
    s.avg_cost,
    (s.qty_on_hand * s.avg_cost) AS stock_value
FROM hps_products p
JOIN hps_stock s ON s.product_id = p.id
WHERE s.qty_on_hand <= p.reorder_level
" . ($search ? " AND p.name LIKE '%$search%'" : "") . "
ORDER BY s.qty_on_hand ASC
";

$res = mysqli_query($conn,$sql);

$total_items = 0;
$total_value = 0;
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Low Stock Alert</title>

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">

<style>
body{background:#f2f4f7}

.main-card{
    background:#fff;
    border-radius:20px;
    padding:25px;
    margin:30px auto;
    max-width:1200px;
    box-shadow:0 10px 25px rgba(0,0,0,.12);
}

.table tbody tr:hover{
    background:#eaf5ea;
}

.out{background:#ffe6e6}
.critical{background:#fff1cc}
.low{background:#e7f3ff}

@media print{
    .no-print{display:none}
    body{background:#fff}
    .main-card{box-shadow:none;margin:0}
}
</style>
</head>
<body>

<div class="main-card">

<h4 class="mb-3">🚨 Low Stock Alert (Reorder Level)</h4>

<!-- FILTER -->
<form method="get" class="row g-2 mb-3 no-print">
<div class="col-md-4">
<input type="text" name="search"
value="<?=htmlspecialchars($search)?>"
class="form-control form-control-sm"
placeholder="Search product">
</div>

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

<div class="col-md-2">
<button type="button" onclick="window.print()"
class="btn btn-dark btn-sm w-100">Print</button>
</div>
</form>

<!-- TABLE -->
<table class="table table-bordered table-sm">
<thead class="table-dark">
<tr>
<th>#</th>
<th>Product</th>
<th class="text-center">Reorder</th>
<th class="text-end">Stock</th>
<th class="text-end">Avg Cost</th>
<th class="text-end">Value</th>
<th>Status</th>
</tr>
</thead>
<tbody>

<?php
$i=1;
while($r=mysqli_fetch_assoc($res)):
$total_items++;
$total_value += $r['stock_value'];

if($r['qty_on_hand']==0) $cls='out';
elseif($r['qty_on_hand'] <= ($r['reorder_level']/2)) $cls='critical';
else $cls='low';
?>
<tr class="<?=$cls?>">
<td><?=$i++?></td>
<td><?=$r['name']?></td>
<td class="text-center"><?=$r['reorder_level']?></td>
<td class="text-end fw-bold"><?=$r['qty_on_hand']?></td>
<td class="text-end"><?=number_format($r['avg_cost'],2)?></td>
<td class="text-end"><?=number_format($r['stock_value'],2)?></td>
<td>
<?php
if($r['qty_on_hand']==0) echo "<span class='badge bg-danger'>OUT</span>";
elseif($r['qty_on_hand'] <= ($r['reorder_level']/2))
echo "<span class='badge bg-warning text-dark'>CRITICAL</span>";
else echo "<span class='badge bg-info'>LOW</span>";
?>
</td>
</tr>
<?php endwhile; ?>

<?php if($i==1): ?>
<tr>
<td colspan="7" class="text-center text-muted">
All products are sufficiently stocked ✅
</td>
</tr>
<?php endif; ?>

</tbody>

<tfoot class="table-secondary fw-bold">
<tr>
<td colspan="3" class="text-end">TOTAL LOW ITEMS</td>
<td><?=$total_items?></td>
<td></td>
<td><?=number_format($total_value,2)?></td>
<td></td>
</tr>
</tfoot>
</table>

</div>
</body>
</html>
