<?php
require_once '../../../auth/check_session.php';
require_once '../../../config/database.php';

$query = $_POST['query'] ?? '';

try {
    $db = getDatabaseConnection();
    
    $sql = "SELECT 
                p.id,
                p.sku,
                p.name,
                p.generic_name,
                p.selling_price,
                p.category,
                p.unit,
                COUNT(DISTINCT pb.id) as batch_count,
                COALESCE(SUM(
                    CASE 
                        WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                        WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                        ELSE 0
                    END
                ), 0) as total_stock
            FROM products p
            LEFT JOIN product_batches pb ON p.id = pb.product_id AND pb.is_active = 1
            LEFT JOIN inventory_movements im ON pb.id = im.batch_id
            WHERE p.is_active = 1 
            AND p.shop_id = ?
            AND (p.name LIKE ? OR p.sku LIKE ? OR p.generic_name LIKE ?)
            GROUP BY p.id, p.sku, p.name, p.generic_name, p.selling_price, p.category, p.unit
            HAVING total_stock > 0
            ORDER BY p.name
            LIMIT 20";
    
    $stmt = $db->prepare($sql);
    $searchTerm = "%{$query}%";
    $stmt->execute([$_SESSION['user']['shop_id'], $searchTerm, $searchTerm, $searchTerm]);
    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo json_encode($products);
    
} catch (PDOException $e) {
    echo json_encode([]);
}
?>