<?php
include 'includes/config.php';

$searchTerm = isset($_GET['search']) ? trim($_GET['search']) : '';
$businessId = 3;  
$limit = 10;     
$offset = 0;     

$sql = "
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    p.business_id,
    COALESCE(ss.quantity, 0) AS stock_quantity,
    (COALESCE(SUM(pl.quantity), 0) - COALESCE(ss.quantity, 0)) AS current_stock
FROM 
    products p
LEFT JOIN 
    purchase_lines pl ON p.id = pl.product_id
LEFT JOIN 
    stock_setting ss ON p.id = ss.product_id
WHERE 
    p.business_id = :business_id
";

if ($searchTerm !== '') {
    $sql .= " AND (p.name LIKE :search OR p.sku LIKE :search)";
}

$sql .= "
GROUP BY 
    p.id
HAVING 
    current_stock = 0  
ORDER BY 
    p.id ASC
LIMIT :limit OFFSET :offset
";

$stmt = $conn->prepare($sql);

$stmt->bindValue(':business_id', $businessId, PDO::PARAM_INT);

if ($searchTerm !== '') {
    $stmt->bindValue(':search', '%' . $searchTerm . '%', PDO::PARAM_STR);
}

$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

try {
    $stmt->execute();
} catch (PDOException $e) {
    echo "Error executing query: " . $e->getMessage();
    exit();
}

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (count($results) > 0) {
    foreach ($results as $row) {
        echo '<tr>';
        echo '<td>' . htmlspecialchars($row['product_name']) . '</td>';
        echo '<td>Pen Grid</td>';
        echo '<td>' . htmlspecialchars($row['current_stock']) . ' Than</td>';
        echo '</tr>';
    }
} else {
    echo '<tr><td colspan="3">No products found.</td></tr>';
}
?>
