<?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);
$sql = "
    SELECT 
        p.id AS product_id,
        p.name AS product_name,
        v.sell_price_inc_tax AS sale_price, 
        p.category_id,
        p.color_id,
        p.size_id,
        sz.product_size AS size,
        u.actual_name AS actual_name,   -- Added units table
        COALESCE(SUM(pl.quantity), 0) AS total_purchased,
        COALESCE(sl.total_sold_qty, 0) AS total_sold_qty,  
        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(sl.total_sold_qty, 0)  
            + COALESCE(rs.total_return_qty, 0)  -- returned stock added back
            - COALESCE(ds.total_defective_qty, 0)
        ) AS current_stock_qty
    FROM products p
    LEFT JOIN variations v ON p.id = v.product_id  
    LEFT JOIN purchase_lines pl ON p.id = pl.product_id
    LEFT JOIN (
        SELECT product_id, SUM(sale_qty) AS total_sold_qty
        FROM stock_sale 
        GROUP BY product_id
    ) sl ON p.id = sl.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
    LEFT JOIN sizes sz ON p.size_id = sz.id
    LEFT JOIN units u ON p.unit_id = u.id
    WHERE p.id = :product_id
    GROUP BY p.id, p.name, v.sell_price_inc_tax, p.category_id, 
             p.color_id, p.size_id, sz.product_size, u.actual_name,
             sl.total_sold_qty, rs.total_return_qty, ds.total_defective_qty
";


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

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

        if ($productData) {
            echo json_encode($productData);
        } else {
            echo json_encode(['error' => 'No product found']);
        }
    } catch (PDOException $e) {
        echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
    }
} else {
    echo json_encode(['error' => 'No product_id provided']);
}
?>
