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

try {
    // Get filters
    $fromDate = !empty($_GET['fromDate']) ? $_GET['fromDate'] : null;
    $toDate   = !empty($_GET['toDate']) ? $_GET['toDate'] : null;
    $brand_id = !empty($_GET['brand_id']) ? (int)$_GET['brand_id'] : null;

    // Base SQL
    $query = "
        SELECT 
            p.ref_no,
            p.purchase_date AS Date,
            c.name AS Supplier,
            pr.name AS Product,
            p.quantity AS QuantityValue,
            p.purchase_price AS UnitPurchasePrice,
            p.fair_price AS FairPrice,
            (p.purchase_price * p.quantity) AS SubTotal,
            COALESCE(s.TotalSold, 0) AS TotalSold,
            COALESCE(s.TotalRevenue, 0) AS TotalRevenue,
            COALESCE(s.TotalRevenue - (p.purchase_price * s.TotalSold), 0) AS Profit
        FROM purchase_lines p
        JOIN contacts c 
            ON p.supplier_id = c.id 
            AND c.type = 'supplier'
        JOIN products pr 
            ON p.product_id = pr.id
        LEFT JOIN (
            SELECT 
                product_id, 
                SUM(sale_qty) AS TotalSold, 
                SUM(price * sale_qty) AS TotalRevenue
            FROM stock_sale
            GROUP BY product_id
        ) s ON p.product_id = s.product_id
        WHERE LOWER(c.name) NOT LIKE '%audit purchase%' 
          AND LOWER(c.name) NOT LIKE '%audit%' 
          AND (p.ref_no IS NULL OR LOWER(p.ref_no) NOT LIKE '%audit%')
    ";


    if ($fromDate && $toDate) {
        $query .= " AND DATE(p.purchase_date) BETWEEN :fromDate AND :toDate";
    }

    if ($brand_id) {
        $query .= " AND pr.brand_id = :brand_id";
    }

    $stmt = $conn->prepare($query);

    if ($fromDate && $toDate) {
        $stmt->bindParam(':fromDate', $fromDate);
        $stmt->bindParam(':toDate', $toDate);
    }
    if ($brand_id) {
        $stmt->bindParam(':brand_id', $brand_id, PDO::PARAM_INT);
    }

    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($results)) {
        echo json_encode(["error" => "No purchase records found for the selected criteria."]);
        exit;
    }

    foreach ($results as &$r) {
        $r['UnitPurchasePrice'] = number_format((float)$r['UnitPurchasePrice'], 2);
        $r['FairPrice']         = number_format((float)$r['FairPrice'], 2);
        $r['SubTotal']          = number_format((float)$r['SubTotal'], 2);
        $r['TotalSold']         = number_format((float)$r['TotalSold'], 2);
        $r['TotalRevenue']      = number_format((float)$r['TotalRevenue'], 2);
        $r['Profit']            = number_format((float)$r['Profit'], 2);
    }

    echo json_encode($results);

} catch (PDOException $e) {
    echo json_encode(["error" => "Database error: " . $e->getMessage()]);
}
?>
