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

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

// Step 1: Get release records and their corresponding stockID
$query = "
    SELECT 
        releaseRecords.id, 
        releaseRecords.stockID, 
        releaseRecords.quantity, 
        releaseRecords.releaseType, 
        releaseRecords._timestamp, 
        releaseRecords.name AS releaseName, 
        releaseRecords.description AS releaseDescription, 
        releaseRecords.releaseBy 
    FROM 
        releaseRecords ORDER BY releaseRecords.id DESC
";

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

// Check if there are results
if ($releaseRecordsResult->num_rows > 0) {
    while ($releaseRow = $releaseRecordsResult->fetch_assoc()) {
        $stockID = $releaseRow['stockID'];

        // Step 2: Get productID and size from store_stock based on stockID
        $storeStockQuery = "SELECT productID, size, store_price FROM store_stock WHERE id = ?";
        $stmtStoreStock = $connect->prepare($storeStockQuery);
        $stmtStoreStock->bind_param("i", $stockID);
        $stmtStoreStock->execute();
        $storeStockResult = $stmtStoreStock->get_result();

        if ($storeStockResult->num_rows > 0) {
            $storeStockRow = $storeStockResult->fetch_assoc();
            $productID = $storeStockRow['productID'];
            $size = $storeStockRow['size'];
            $productPrice = $storeStockRow['store_price'];

            // Step 3: Get productName from products table based on productID
            $productsQuery = "SELECT productCode FROM products WHERE id = ?";
            $stmtProducts = $connect->prepare($productsQuery);
            $stmtProducts->bind_param("i", $productID);
            $stmtProducts->execute();
            $productsResult = $stmtProducts->get_result();

            if ($productsResult->num_rows > 0) {
                $productsRow = $productsResult->fetch_assoc();
                $productCode = $productsRow['productCode'];

                // Combine productName and size into a single string
                $combinedProductName = $releaseRow["id"].'-'.$productCode;

                // Step 4: Fetch user details for releaseBy
                $usersQuery = "SELECT name, email, phone FROM users WHERE id = ?";
                $stmtUsers = $connect->prepare($usersQuery);
                $stmtUsers->bind_param("i", $releaseRow['releaseBy']);
                $stmtUsers->execute();
                $usersResult = $stmtUsers->get_result();

                if ($usersResult->num_rows > 0) {
                    $usersRow = $usersResult->fetch_assoc();

                    // Build the response for this record
                    $response[] = [
                        "id" => $releaseRow["id"],
                        "productName" => $combinedProductName,
                        "productPrice" => $productPrice,
                        "quantity" => $releaseRow["quantity"],
                        "amount" => $releaseRow["quantity"] * $productPrice,
                        "releaseType" => $releaseRow["releaseType"],
                        "_timestamp" => $releaseRow["_timestamp"],
                        "releaseName" => $releaseRow["releaseName"],
                        "releaseDescription" => $releaseRow["releaseDescription"],
                        "releasedBy" => [
                            "name" => $usersRow["name"],
                            "email" => $usersRow["email"],
                            "phone" => $usersRow["phone"]
                        ]
                    ];
                }
            }
        }
    }

    // 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 release records found.";
}

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

?>
