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

try {
    $sql = "
        SELECT 
            p.id AS product_id,
            p.sku,
            p.barcode, 
            p.name AS product_name,
            u.actual_name AS unit_name,
            COALESCE(purch.total_purchased, 0) AS total_purchased,
            COALESCE(purch.unit_purchase_price, 0) AS unit_purchase_price,
            COALESCE(sale.total_unit_sold, 0) AS total_unit_sold,
            COALESCE(ret.total_unit_returned, 0) AS total_unit_returned,
            COALESCE(dmg.total_defective, 0) AS total_defective
        FROM products p
        LEFT JOIN units u ON p.unit_id = u.id
        LEFT JOIN (
            SELECT 
                product_id, 
                SUM(quantity) AS total_purchased, 
                AVG(purchase_price) AS unit_purchase_price
            FROM purchase_lines
            GROUP BY product_id
        ) purch ON p.id = purch.product_id
        LEFT JOIN (
            SELECT 
                product_id, 
                SUM(sale_qty) AS total_unit_sold
            FROM stock_sale
            GROUP BY product_id
        ) sale ON p.id = sale.product_id
        LEFT JOIN (
            SELECT 
                product_id, 
                SUM(sale_qty) AS total_unit_returned
            FROM return_sale
            GROUP BY product_id
        ) ret ON p.id = ret.product_id
        LEFT JOIN (
            SELECT 
                product_id, 
                SUM(sale_qty) AS total_defective
            FROM defective_sale
            GROUP BY product_id
        ) dmg ON p.id = dmg.product_id
        ORDER BY p.name ASC
    ";

    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $data = [];

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $total_purchased = (float) $row['total_purchased'];
        $total_sold = (float) $row['total_unit_sold'];
        $total_returned = (float) $row['total_unit_returned'];
        $total_defective = (float) $row['total_defective'];
        $unit_price = (float) $row['unit_purchase_price'];

        $current_stock = $total_purchased - $total_sold + $total_returned - $total_defective;
        if ($current_stock < 0) $current_stock = 0;

        $current_stock_value = $current_stock * $unit_price;

        $data[] = [
            'product_id' => $row['product_id'],
            'sku' => $row['sku'],
            'barcode' => $row['barcode'],
            'product' => $row['product_name'],
            'unit' => $row['unit_name'], 
            'unit_purchase_price' => number_format($unit_price, 2, '.', ''),
            'current_stock' => $current_stock,
            'current_stock_value' => number_format($current_stock_value, 2, '.', ''),
            'total_unit_sold' => $total_sold,
            'total_unit_returned' => $total_returned,
            'total_defective' => $total_defective
        ];
    }

    echo json_encode($data);
} catch (PDOException $e) {
    echo json_encode(['error' => $e->getMessage()]);
}
