<?php
include "../db.php";

// Create database connection
$connection = new mysqli($dbhost, $dbusername, $dbpassword, $dbname);
if ($connection->connect_error) {
    die("Database connection error: " . $connection->connect_error);
}

// Only process POST requests
if ($_SERVER["REQUEST_METHOD"] == "POST") {

    // Get POST values and ensure integers
    $category = isset($_POST["category_name"]) ? (int)$_POST["category_name"] : 0;
    $product  = isset($_POST["product_name"]) ? (int)$_POST["product_name"] : 0;

    // Base query
    $query = "
        SELECT 
            m.Invoice,
            s.ID AS orderid,
            m.Date,
            m.OrderType,
            p.Name AS Product_name,
            s.AmountPerPC AS Price,
            s.Quantity,
            s.Amount
        FROM invoice m
        INNER JOIN orders s ON m.Invoice = s.Invoice
        INNER JOIN product p ON s.Product = p.ID
        WHERE m.Order_Status = 'Confirmed'
    ";

    // Append product filter if selected
    if ($product > 0) {
        $query .= " AND p.ID = $product";
    }

    // Append category filter if selected
    if ($category > 0) {
        $query .= " AND p.Category = $category"; // replace 'Category' with your actual column name if different
    }

    // DEBUG: uncomment to see final query
    // echo $query; exit;

    // Execute query
    $result = $connection->query($query);

    $data = [];
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
    }

    // Return JSON response
    header('Content-Type: application/json');
    echo json_encode($data);
    exit;
}
?>
