<?php
require('config.php');

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

$query = "
    SELECT 
    store_stock.id, 
    store_stock.productID, 
    products.productName, 
    products.productCode, 
    store_stock.quantity AS QuantityInStock, 
    COALESCE(stock.quantity, 0) AS ShopQuantity, 
    store_stock.productPrice, 
    store_stock.store_price, 
    store_stock.size AS productSize, 
    users.name AS addedByName,
    users.email AS addedByEmail,
    users.phone AS addedByPhone
FROM 
    store_stock
JOIN 
    products ON store_stock.productID = products.id
JOIN 
    users ON store_stock.addedBy = users.id
LEFT JOIN 
    stock ON store_stock.productID = stock.productID;
";

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

// Check if there are results
if ($result->num_rows > 0) {
    // Fetch each row as an associative array
    while ($row = $result->fetch_assoc()) {
        // Format each row to include product and user details
        $response[] = [
            "id" => $row["id"],
            "stockCode" => $row["id"] . " - " . $row["productCode"], // Updated stockCode format
            "productID" => $row["productID"],
            "productName" => $row["productName"],
            "QuantityInStock" => $row["QuantityInStock"],
            "ShopQuantity" => $row["ShopQuantity"],
            "productPrice" => $row["productPrice"],
            "storePrice" => $row["store_price"],
            "productSize" => $row["productSize"],
            "addedBy" => [
                "name" => $row["addedByName"],
                "email" => $row["addedByEmail"],
                "phone" => $row["addedByPhone"]
            ]
        ];
    }

    // Set the error flag and message for success
    $responseData['error'] = false;
    $responseData['data'] = $response;
} else {
    // Set the error flag and message for no results
    $responseData['error'] = true;
    $responseData['message'] = "No stock records found.";
}

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

?>
