<?php
require('config.php'); // Include your database connection file

// Get and sanitize the posted data
$productID = intval($_POST['productID']);
$prodSize = mysqli_real_escape_string($connect, $_POST['prodSize']);
$quantity = intval($_POST['quantity']);
$releaseType = mysqli_real_escape_string($connect, $_POST['releaseType']);
$name = mysqli_real_escape_string($connect, $_POST['name']);
$_timestamp = mysqli_real_escape_string($connect, $_POST['_timestamp']);
$description = mysqli_real_escape_string($connect, $_POST['description']);
$addedBy = intval($_POST['addedBy']); // Ensure addedBy is an integer
$response = array();

if ($productID > 0 && $quantity > 0 && !empty($releaseType)) {
    // Begin transaction to ensure data consistency
    $connect->begin_transaction();

    try {
        // Check if the product exists in the store_stock table
        $checkStoreStockQuery = "SELECT quantity, productPrice, size FROM store_stock WHERE id = ? AND size = ?";
        $stmtStoreStock = $connect->prepare($checkStoreStockQuery);
        $stmtStoreStock->bind_param("is", $productID, $prodSize);
        $stmtStoreStock->execute();
        $storeStockResult = $stmtStoreStock->get_result();

        if ($storeStockResult->num_rows > 0) {
            $storeStockRow = $storeStockResult->fetch_assoc();

            if ($storeStockRow['quantity'] < $quantity) {
                throw new Exception("Insufficient quantity in store stock.");
            }

            if ($releaseType === "FUFA Shop") {
                // Check if the product already exists in the stock table
                $checkStockQuery = "SELECT quantity FROM stock WHERE productID = ?";
                $stmtStock = $connect->prepare($checkStockQuery);
                $stmtStock->bind_param("i", $productID);
                $stmtStock->execute();
                $stockResult = $stmtStock->get_result();

                if ($stockResult->num_rows > 0) {
                    // Update existing stock record
                    $updateStockQuery = "
                        UPDATE stock 
                        SET quantity = quantity + ?, dateAdded = CONVERT_TZ(NOW(), '+00:00', '+03:00'), addedBy = ? 
                        WHERE productID = ?";
                    $stmtUpdateStock = $connect->prepare($updateStockQuery);
                    $stmtUpdateStock->bind_param("iii", $quantity, $addedBy, $productID);
                    $stmtUpdateStock->execute();
                } else {
                    // Insert a new record in the stock table
                    $insertStockQuery = "
                        INSERT INTO stock (productID, quantity, productPrice, size, addedBy, dateAdded) 
                        VALUES (?, ?, ?, ?, ?, CONVERT_TZ(NOW(), '+00:00', '+03:00'))";
                    $stmtInsertStock = $connect->prepare($insertStockQuery);
                    $stmtInsertStock->bind_param(
                        "iiisi",
                        $productID,
                        $quantity,
                        $storeStockRow['productPrice'],
                        $storeStockRow['size'],
                        $addedBy
                    );
                    $stmtInsertStock->execute();
                }
            }

            // Reduce quantity in store_stock table
            $updateStoreStockQuery = "UPDATE store_stock SET quantity = quantity - ? WHERE id = ?";
            $stmtUpdateStoreStock = $connect->prepare($updateStoreStockQuery);
            $stmtUpdateStoreStock->bind_param("ii", $quantity, $productID);
            $stmtUpdateStoreStock->execute();


            // Insert the action into the releaseRecords table
            $insertReleaseRecordQuery = "
                INSERT INTO releaseRecords (stockID, quantity, releaseType, _timestamp, name, description, releaseBy) 
                VALUES (?, ?, ?, ?, ?, ?, ?)";
            $stmtInsertReleaseRecord = $connect->prepare($insertReleaseRecordQuery);
            $stmtInsertReleaseRecord->bind_param("iissssi", $productID, $quantity, $releaseType, $_timestamp, $name, $description, $addedBy);
            $stmtInsertReleaseRecord->execute();

            // Commit the transaction
            $connect->commit();

            // Success response
            $response = [
                "error" => false,
                "message" => "Data successfully processed and release record added."
            ];
        } else {
            throw new Exception("Product not found in store stock.");
        }
    } catch (Exception $e) {
        // Rollback transaction on error
        $response = [
            "error" => true,
            "message" => $e->getMessage()
        ];
    }
} else {
    $response = [
        "error" => true,
        "message" => "Invalid input. Please provide valid productID, quantity, and release type."
    ];
}

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

// Close the database connection
$connect->close();
