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

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

// ================== FILTER INPUTS ==================
$search_name = $_GET['search_name'] ?? '';
$search_sku  = $_GET['search_sku'] ?? '';
$category_id = $_GET['category_id'] ?? '';
$threshold   = $_GET['threshold'] ?? 10;

// ================== FETCH CATEGORIES ==================
$cat_res = $conn->query("SELECT id,name FROM categories ORDER BY name ASC");

// ================== BUILD WHERE ==================
$where = [];
$where[] = "COALESCE(ps.quantity,0) < " . intval($threshold);

if($search_name){
    $safe = $conn->real_escape_string($search_name);
    $where[] = "p.name LIKE '%$safe%'";
}

if($search_sku){
    $safe = $conn->real_escape_string($search_sku);
    $where[] = "p.sku LIKE '%$safe%'";
}

if($category_id){
    $where[] = "p.category_id=" . intval($category_id);
}

$where_sql = !empty($where) ? "WHERE " . implode(" AND ", $where) : "";

// ================== FETCH LOW STOCK ITEMS ==================
$low_stock = $conn->query("
    SELECT p.*, COALESCE(ps.quantity,0) AS stock_qty
    FROM products p
    LEFT JOIN product_stock ps 
        ON p.id = ps.product_id AND ps.store_id = 1
    $where_sql
    ORDER BY stock_qty ASC
");
?>

<div class="container mt-4">
      <button class="btn btn-secondary mb-3" onclick="history.back()"><i class="fa fa-arrow-left"></i> Back</button>
    <h3 class="text-center mb-3">⚠️ Low Stock Requisition</h3>
    <hr>
    <p>Prepared by: <strong><?= htmlspecialchars($username) ?></strong></p>
    <p>Date: <strong><?= date('d-m-Y') ?></strong></p>

    <!-- ================= FILTER FORM (NEW) ================= -->
    <form method="GET" class="row g-2 mb-3">
        <div class="col-md-3">
            <input type="text" name="search_name" class="form-control"
                   placeholder="Search Product Name"
                   value="<?= htmlspecialchars($search_name) ?>">
        </div>

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

        <div class="col-md-3">
            <select name="category_id" class="form-control">
                <option value="">-- All Categories --</option>
                <?php while($c = $cat_res->fetch_assoc()): ?>
                    <option value="<?= $c['id'] ?>"
                        <?= ($category_id==$c['id'])?'selected':'' ?>>
                        <?= htmlspecialchars($c['name']) ?>
                    </option>
                <?php endwhile; ?>
            </select>
        </div>

        <div class="col-md-2">
            <input type="number" name="threshold" class="form-control"
                   placeholder="Low Stock Level"
                   value="<?= htmlspecialchars($threshold) ?>">
        </div>

        <div class="col-md-2">
            <button class="btn btn-primary w-100">
                <i class="fa fa-filter"></i> Filter
            </button>
        </div>
    </form>
    <!-- ================= END FILTER FORM ================= -->

    <form method="POST" action="print_r.php">
        <table class="table table-bordered table-striped">
            <thead class="bg-dark text-white">
                <tr>
                    <th>#</th>
                    <th>SKU</th>
                    <th>Product</th>
                    <th>Current Qty</th>
                    <th>Buying Price</th>
                    <th>Qty to Replenish</th>
                    <th>Select</th>
                </tr>
            </thead>
            <tbody>
                <?php $i=1; while($p = $low_stock->fetch_assoc()): ?>
                <tr>
                    <td><?= $i++ ?></td>
                    <td><?= $p['sku'] ?></td>
                    <td><?= $p['name'] ?></td>
                   <td><?= intval($p['stock_qty']) ?></td>

<td>
    MWK <?= number_format($p['buying_price'], 2) ?>
</td>

<td>
    <input type="number"
           name="replenish[<?= $p['id'] ?>]"
           min="1"
           value="1"
           class="form-control">
</td>
                    <td class="text-center">
                        <input type="checkbox" name="tick[<?= $p['id'] ?>]">
                    </td>
                </tr>
                <?php endwhile; ?>
            </tbody>
        </table>

        <div class="text-center mt-3">
            <button type="submit" class="btn btn-primary">
                <i class="fa fa-print"></i> Generate Print
            </button>
        </div>
    </form>
</div>

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