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

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

// Query all productCode and id from the products table
$productsQuery = "SELECT id, productCode, productName FROM products";
$productsResult = $connect->query($productsQuery);

$productsMap = [];
if ($productsResult->num_rows > 0) {
    while ($row = $productsResult->fetch_assoc()) {
        $productsMap[$row['id']] = [
            'productCode' => $row['productCode'],
            'productName' => $row['productName']
        ];
    }
}

// Main query with productCode derived from store_stock.productID matching products.id
$query = "
    SELECT 
        stock.productID, 
        stock.id, 
        store_stock.productID AS storeProductID, 
        stock.productPrice, 
        stock.quantity AS QuantityInStock, 
        stock.size AS productSize, 
        users.name AS addedByName,
        users.email AS addedByEmail,
        users.phone AS addedByPhone,
        store_stock.quantity AS StoreQuantity
    FROM 
        stock
    JOIN 
        users ON stock.addedBy = users.id
    LEFT JOIN 
        store_stock ON stock.productID = store_stock.id AND stock.size = store_stock.size
";

// 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()) {
        $productCode = isset($productsMap[$row['storeProductID']]) ? $productsMap[$row['storeProductID']]['productCode'] : "Unknown";
        $productName = isset($productsMap[$row['storeProductID']]) ? $productsMap[$row['storeProductID']]['productName'] : "Unknown";
        
        // Format each row to include product and user details
        $response[] = [
            "stockID" => $row["id"],
            "productID" => $row["productID"],
            "productCode" => $row["productID"]. " - " .$productCode,
            "productName" => $productName,
            "QuantityInStock" => $row["QuantityInStock"],
            "StoreQuantity" => $row["StoreQuantity"], // Store stock quantity
            "productPrice" => $row["productPrice"],
            "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);

?>
