<?php
// modules/sales/ajax_get_product_batches.php
header('Content-Type: text/html; charset=utf-8');
require_once '../assets/db_connect.php'; // <- adjust path if needed

if (!isset($_POST['product_id']) || empty($_POST['product_id'])) {
    echo '<option value="">Select product first</option>';
    exit;
}

$product_id = intval($_POST['product_id']);

try {
    // Only return batches with qty > 0 (available stock) and order by expiry soonest
    $stmt = $pdo->prepare("
        SELECT id, batch_no, qty, expiry_date
        FROM product_batches
        WHERE product_id = :pid AND qty > 0
        ORDER BY expiry_date IS NULL, expiry_date ASC, id ASC
    ");
    $stmt->execute([':pid' => $product_id]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$rows) {
        echo '<option value="">No available batches</option>';
        exit;
    }

    echo '<option value="">Select Batch</option>';
    foreach ($rows as $r) {
        $label = htmlspecialchars($r['batch_no'] ?: "Batch #{$r['id']}");
        $label .= " — {$r['qty']} avail";
        if (!empty($r['expiry_date'])) {
            $label .= " — exp: " . date('d-M-Y', strtotime($r['expiry_date']));
        }
        echo '<option value="' . $r['id'] . '">' . $label . '</option>';
    }

} catch (Exception $e) {
    echo '<option value="">Error fetching batches</option>';
}
