<?php
include_once("includes/config.php");

header('Content-Type: application/json');

try {
    if (isset($_REQUEST['product_name'])) {
        $productName = $_REQUEST['product_name'];

        // Set error mode for both connections
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Initialize stock quantities for both databases
        $currentStockQty1 = 0;
        $currentStockQty2 = 0;

        // Fetch product ID from the first database
        $stmt1 = $conn->prepare("SELECT id AS product_id FROM products WHERE name = :product_name AND business_id = 3 LIMIT 1");
        $stmt1->bindParam(':product_name', $productName);
        $stmt1->execute();
        $product1 = $stmt1->fetch(PDO::FETCH_ASSOC);
        $productId1 = $product1 ? $product1['product_id'] : null;

        // Fetch product ID from the second database
        $stmt2 = $conn2->prepare("SELECT id AS product_id FROM products WHERE name = :product_name AND business_id = 3 LIMIT 1");
        $stmt2->bindParam(':product_name', $productName);
        $stmt2->execute();
        $product2 = $stmt2->fetch(PDO::FETCH_ASSOC);
        $productId2 = $product2 ? $product2['product_id'] : null;

        // Fetch stock quantity from the first database if product ID exists
        if ($productId1) {
   $stmtStock1 = $conn->prepare("
SELECT 
    p.id AS product_id, 
    p.name AS product_name, 
    p.business_id, 
    p.image AS product_image, 
    p.type, 
    p.brand_id, 
    b.name AS brand_name, 
    p.category_id, 
    p.sub_category_id, 
    c1.name AS category_name, 
    c2.name AS sub_category_name, 
    p.tax, 
    p.sku, 
    p.alert_quantity, 
    p.product_custom_field1, 
    p.product_custom_field2, 
    p.product_custom_field3, 
    p.product_custom_field4, 
    p.product_description, 
    p.unit_id, 
    p.tax_type, 
    v.profit_percent, 
    u.actual_name, 
    v.default_purchase_price, 
    v.default_sell_price, 
    COALESCE(v.dpp_inc_tax, 0) AS purchase_price, 
    COALESCE(v.sell_price_inc_tax, 0) AS sale_price, 
    COALESCE(SUM(pl.quantity), 0) AS total_quantity, 
    COALESCE(ss.quantity, 0) AS stock_quantity, 
    COALESCE(sale.total_sale_qty, 0) AS total_sale_qty, 
    COALESCE(st_transfer.total_quantity_transferred, 0) AS total_quantity_transferred,
    COALESCE(return_sale.total_return_sale_qty, 0) AS total_return_sale_qty,

    -- Get the latest stock setting date for the product
    (
        SELECT MAX(ss2.created_at) 
        FROM stock_setting ss2 
        WHERE ss2.product_id = p.id
    ) AS last_stock_setting_date,

    -- Calculate new purchase quantity after the last stock setting date
    (
        SELECT COALESCE(SUM(pl2.quantity), 0)
        FROM purchase_lines pl2
        WHERE pl2.product_id = p.id 
        AND (
            (
                SELECT MAX(ss3.created_at) 
                FROM stock_setting ss3 
                WHERE ss3.product_id = p.id
            ) IS NULL OR pl2.created_at > (
                SELECT MAX(ss3.created_at) 
                FROM stock_setting ss3 
                WHERE ss3.product_id = p.id
            )
        )
    ) AS new_purchase_quantity,

    -- ✅ Calculate current stock including return sale stock
    (
        COALESCE(ss.quantity, 0)  
        + (
            SELECT COALESCE(SUM(pl2.quantity), 0)
            FROM purchase_lines pl2
            WHERE pl2.product_id = p.id 
            AND (
                (
                    SELECT MAX(ss3.created_at) 
                    FROM stock_setting ss3 
                    WHERE ss3.product_id = p.id
                ) IS NULL OR pl2.created_at > (
                    SELECT MAX(ss3.created_at) 
                    FROM stock_setting ss3 
                    WHERE ss3.product_id = p.id
                )
            )
        ) 
        + COALESCE(return_sale.total_return_sale_qty, 0)  
        - COALESCE(sale.total_sale_qty, 0)  
        - COALESCE(st_transfer.total_quantity_transferred, 0)  
    ) AS current_stock_qty

FROM 
    products p 

-- Joins
LEFT JOIN 
    (SELECT product_id, SUM(quantity) AS quantity FROM purchase_lines GROUP BY product_id) pl ON p.id = pl.product_id 
LEFT JOIN 
    brands b ON p.brand_id = b.id 
LEFT JOIN 
    categories c1 ON p.category_id = c1.id 
LEFT JOIN 
    categories c2 ON p.sub_category_id = c2.id 
LEFT JOIN 
    units u ON p.unit_id = u.id 
LEFT JOIN 
    variations v ON p.id = v.product_id 
LEFT JOIN 
    stock_setting ss ON p.id = ss.product_id 
LEFT JOIN 
    (SELECT product_id, SUM(sale_qty) AS total_sale_qty FROM stock_sale GROUP BY product_id) sale ON p.id = sale.product_id 
LEFT JOIN 
    (SELECT product_id, SUM(QuantityTransferred) AS total_quantity_transferred FROM stocktransfer GROUP BY product_id) st_transfer ON p.id = st_transfer.product_id 
LEFT JOIN 
    (SELECT product_id, SUM(quantity) AS total_return_sale_qty FROM stock_return_sale GROUP BY product_id) return_sale ON p.id = return_sale.product_id 

WHERE 
    p.id = :product_id 
    AND p.business_id = 3

GROUP BY 
    p.id

ORDER BY 
    p.id DESC;
");


            $stmtStock1->bindParam(':product_id', $productId1);
            $stmtStock1->execute();
            $productData1 = $stmtStock1->fetch(PDO::FETCH_ASSOC);
            if ($productData1) {
                $currentStockQty1 = $productData1['current_stock_qty'];
            }
        }

        // Fetch stock quantity from the second database if product ID exists
        if ($productId2) {
       $stmtStock2 = $conn2->prepare("SELECT 
    p.id AS product_id, 
    p.name AS product_name, 
    p.business_id, 
    p.image AS product_image, 
    p.type, 
    p.brand_id, 
    b.name AS brand_name, 
    p.category_id, 
    c1.name AS category_name, 
    c2.name AS sub_category_name, 
    p.tax, 
    p.sku, 
    p.alert_quantity, 
    p.product_custom_field1, 
    p.product_custom_field2, 
    p.product_custom_field3, 
    p.product_custom_field4, 
    p.product_description, 
    p.unit_id, 
    p.tax_type, 
    v.profit_percent, 
    u.actual_name, 
    v.default_purchase_price, 
    v.default_sell_price, 
    COALESCE(SUM(pl.quantity), 0) AS total_quantity, 
    COALESCE(ss.quantity, 0) AS stock_quantity, 
    (COALESCE(SUM(pl.quantity), 0) - COALESCE(ss.quantity, 0)) AS current_difference, 
    COALESCE(sale.total_sale_qty, 0) AS total_sale_qty, 
    COALESCE(st.transfer_qty, 0) AS total_transfer_qty,
    (COALESCE(SUM(pl.quantity), 0) 
        - COALESCE(sale.total_sale_qty, 0)
        - COALESCE(st.transfer_qty, 0)
        - COALESCE(ss.quantity, 0)
    ) AS current_stock_qty
FROM 
    products p 
LEFT JOIN 
    (SELECT product_id, SUM(quantity) AS quantity FROM purchase_lines GROUP BY product_id) pl ON p.id = pl.product_id 
LEFT JOIN 
    brands b ON p.brand_id = b.id 
LEFT JOIN 
    categories c1 ON p.category_id = c1.id 
LEFT JOIN 
    categories c2 ON p.sub_category_id = c2.id 
LEFT JOIN 
    units u ON p.unit_id = u.id 
LEFT JOIN 
    variations v ON p.id = v.product_id 
LEFT JOIN 
    stock_setting ss ON p.id = ss.product_id 
LEFT JOIN 
    (SELECT product_id, SUM(sale_qty) AS total_sale_qty FROM stock_sale GROUP BY product_id) sale ON p.id = sale.product_id 
LEFT JOIN 
    (SELECT product_id, SUM(QuantityTransferred) AS transfer_qty FROM stocktransfer GROUP BY product_id) st ON p.id = st.product_id 
WHERE 
    p.id = :product_id 
    AND p.business_id = 3
GROUP BY 
    p.id;
");

            $stmtStock2->bindParam(':product_id', $productId2);
            $stmtStock2->execute();
            $productData2 = $stmtStock2->fetch(PDO::FETCH_ASSOC);
            if ($productData2) {
                $currentStockQty2 = $productData2['current_stock_qty'];
            }
        }

        // Prepare the tooltip response
        $tooltip = ":  | : ";

        // Check if there's stock available
        if ($currentStockQty1 > 0 || $currentStockQty2 > 0) {
            echo json_encode([
                'tooltip' => $tooltip,
                'error' => false
            ]);
        } else {
            echo json_encode(['error' => 'No stock available for the specified product name.']);
        }
    } else {
        echo json_encode(['error' => 'No product name provided.']);
    }
} catch (PDOException $e) {
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>
