<?php
require('config.php'); // Ensure database connection is included

// Define an array to hold the response
$response = [];

// SQL Query to retrieve required details
$query = "
    SELECT 
        sr.requestID, 
        sr.storeStockID, 
        sr.requestQTY, 
        sr.status, 
        sr.requestFrom, 
        sr.timeStamp, 
        ss.productID, 
        ss.size AS productSize, 
        p.productName, 
        p.productCode, 
        u.id AS userID, 
        u.name AS requestedName, 
        u.email AS requestedEmail, 
        u.phone AS requestedPhone
    FROM 
        stock_requests sr
    JOIN 
        store_stock ss ON sr.storeStockID = ss.id
    JOIN 
        products p ON ss.productID = p.id
    JOIN 
        users u ON sr.userID = u.id
";

// Execute the query
$result = $connect->query($query);

// Check if there are results
if ($result->num_rows > 0) {
    // Fetch each row and format the response
    $data = [];
    while ($row = $result->fetch_assoc()) {
        $data[] = [
            "requestID" => $row["requestID"],
            "storeStockID" => $row["storeStockID"],
            "productCode" => $row["storeStockID"].'-'.$row["productCode"],
            "productName" => $row["productName"],
            "requestQTY" => $row["requestQTY"],
            "productSize" => $row["productSize"],
            "status" => $row["status"],
            "requestFrom" => $row["requestFrom"],
            "requested" => [
                "userID" => $row["userID"],
                "name" => $row["requestedName"],
                "email" => $row["requestedEmail"],
                "phone" => $row["requestedPhone"]
            ],            "timeStamp" => $row["timeStamp"],

        ];
    }

    // Prepare the final response
    $response = [
        "error" => false,
        "data" => $data
    ];
} else {
    // No records found
    $response = [
        "error" => true,
        "message" => "No stock requests found."
    ];
}

// Output the response as JSON
echo json_encode($response);

?>
