<?php
// hps_stock_report.php
require "db.php";

/* -----------------------------
   FILTERS
------------------------------*/
$name = mysqli_real_escape_string($conn, $_GET['name'] ?? '');
$min  = (float)($_GET['min'] ?? 0);
$max  = (float)($_GET['max'] ?? 0);

$where = [];

if ($name !== '') {
    $where[] = "p.name LIKE '%$name%'";
}
if ($min > 0) {
    $where[] = "s.qty_on_hand >= $min";
}
if ($max > 0) {
    $where[] = "s.qty_on_hand <= $max";
}

$whereSql = count($where) ? "WHERE ".implode(" AND ", $where) : "";

/* -----------------------------
   FETCH STOCK
------------------------------*/
$sql = "
SELECT 
    p.id,
    p.name,
    p.price,
    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
$whereSql
ORDER BY p.name
";

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

/* Totals */
$total_qty = 0;
$total_value = 0;
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Stock Report</title>

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

<style>
body{
    background:#f2f4f7;
}

/* Card */
.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);
}

/* Hover rows */
.table tbody tr:hover{
    background:#eaf5ea;
}

/* Print */
@media print{
    body{background:#fff}
    .no-print{display:none!important}
    .main-card{
        box-shadow:none;
        margin:0;
        padding:0;
    }
    table{font-size:12px}
}
</style>
</head>
<body>

<div class="main-card">

<h4 class="mb-3">📦 Stock Report</h4>

<!-- FILTERS -->
<form method="get" class="row g-2 mb-3 no-print">

<div class="col-md-4">
<input type="text" name="name" value="<?=htmlspecialchars($name)?>"
class="form-control form-control-sm"
placeholder="Search product">
</div>

<!-- <div class="col-md-2">
<input type="number" name="min" value="<?=$min?>"
class="form-control form-control-sm"
placeholder="Min stock">
</div>

<div class="col-md-2">
<input type="number" name="max" value="<?=$max?>"
class="form-control form-control-sm"
placeholder="Max stock">
</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-end">Price</th>
<th class="text-end">Avg Cost</th>
<th class="text-end">Stock</th>
<th class="text-end">Stock Value</th>
</tr>
</thead>
<tbody>

<?php
$i=1;
while($r=mysqli_fetch_assoc($res)):
    $total_qty   += $r['qty_on_hand'];
    $total_value += $r['stock_value'];
?>
<tr>
<td><?=$i++?></td>
<td><?=htmlspecialchars($r['name'])?></td>
<td class="text-end"><?=number_format($r['price'],2)?></td>
<td class="text-end"><?=number_format($r['avg_cost'],2)?></td>
<td class="text-end"><?=$r['qty_on_hand']?></td>
<td class="text-end"><?=number_format($r['stock_value'],2)?></td>
</tr>
<?php endwhile; ?>

<?php if($i==1): ?>
<tr>
<td colspan="6" class="text-center text-muted">No records</td>
</tr>
<?php endif; ?>

</tbody>

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

</table>

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