<?php
require_once "../assets/db_connect.php";

$q = $_GET['q'] ?? '';

$sql = "
SELECT p.id, p.product_name,
       b.selling_price
FROM products p
JOIN product_batches b ON b.product_id = p.id
WHERE p.product_name LIKE ?
AND b.quantity > 0
ORDER BY b.expiry_date ASC
GROUP BY p.id
LIMIT 20
";

$stmt = $pdo->prepare($sql);
$stmt->execute(["%$q%"]);

$data = [];
while ($r = $stmt->fetch()) {
    $data[] = [
        'id' => $r['id'],
        'text' => $r['product_name'],
        'price' => $r['selling_price']
    ];
}

echo json_encode($data);
