<?php
include_once("includes/config.php");
header('Content-Type: application/json');

if (isset($_GET['product_id'])) {
    $productId = (int)$_GET['product_id'];

    try {
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $query = "
        SELECT 
            p.id AS product_id,
            p.name AS product_name,
            COALESCE(SUM(pl.quantity), 0) AS total_purchased,
            COALESCE(rs.total_return_qty, 0) AS total_return_qty,
            COALESCE(ds.total_defective_qty, 0) AS total_defective_qty,
            (COALESCE(SUM(pl.quantity), 0) 
             - COALESCE(rs.total_return_qty, 0) 
             - COALESCE(ds.total_defective_qty, 0)) AS current_stock_qty
        FROM products p
        LEFT JOIN purchase_lines pl ON p.id = pl.product_id
        LEFT JOIN (
            SELECT product_id, SUM(sale_qty) AS total_return_qty
            FROM return_sale GROUP BY product_id
        ) rs ON p.id = rs.product_id
        LEFT JOIN (
            SELECT product_id, SUM(sale_qty) AS total_defective_qty
            FROM defective_sale GROUP BY product_id
        ) ds ON p.id = ds.product_id
        WHERE p.id = :product_id
        GROUP BY p.id, p.name, rs.total_return_qty, ds.total_defective_qty
        ";

        $stmt = $conn->prepare($query);
        $stmt->bindParam(':product_id', $productId, PDO::PARAM_INT);
        $stmt->execute();

        $product = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($product) {
            echo json_encode([
                "product_id" => $product['product_id'],
                "name" => $product['product_name'],
                "tooltip" => "Stock: " . $product['current_stock_qty']
            ]);
        } else {
            echo json_encode(["error" => "Product not found"]);
        }
    } catch (PDOException $e) {
        echo json_encode(["error" => "Database error: " . $e->getMessage()]);
    }
} else {
    echo json_encode(["error" => "No product_id provided"]);
}
?>
