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

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Initialize the response
$response = [];

try {
    // Check if flag and file data are provided
    if (isset($_POST['flag']) && isset($_POST['file'])) {
        $flag = $_POST['flag'];
        $base64File = $_POST['file'];

        // Validate the flag
        if (!in_array($flag, ['storestock', 'releaseRecord'])) {
            throw new Exception("Invalid flag value. Must be 'storestock' or 'releaseRecord'.");
        }

        // Decode the base64-encoded file data
        $fileData = base64_decode($base64File);
        if ($fileData === false) {
            throw new Exception("Failed to decode the base64 file.");
        }

        // Create a temporary file to store the decoded file content
        $fileTmpPath = tempnam(sys_get_temp_dir(), 'csv_');
        if (file_put_contents($fileTmpPath, $fileData) === false) {
            throw new Exception("Failed to write the decoded file to a temporary location.");
        }

        // Open the .csv file for reading
        if (($handle = fopen($fileTmpPath, 'r')) === false) {
            throw new Exception("Failed to open the uploaded file.");
        }

        // Start a database transaction
        $connect->begin_transaction();

        try {
            $rowIndex = 0; // Row index to track the first row

            // Process each row in the .csv file
            while (($data = fgetcsv($handle, 1000, ",")) !== false) {
                $rowIndex++;

                // Skip the first row
                if ($rowIndex === 1) {
                    continue;
                }

                if ($flag === 'storestock') {
                    // Validate the row for store_stock
                    if (count($data) < 6) {
                        throw new Exception("Invalid row format for store_stock at line $rowIndex.");
                    }

                    // Map .csv values to variables for store_stock
                    $productID = $data[1];
                    $quantity = $data[2];
                    $productPrice = $data[3];
                    $store_price = $data[4];
                    $size = $data[5];
                    $addedBy = $data[6];
                    $dateAdded = $data[7];

                    $insertStmt = $connect->prepare("INSERT INTO store_stock (productID, quantity, productPrice, store_price, size, addedBy, dateAdded) VALUES (?, ?, ?, ?, ?, ?, ?)");

                    if (!$insertStmt) {
                        throw new Exception("Failed to prepare statement: " . $connect->error);
                    }

                    $insertStmt->bind_param("iiiisis", $productID, $quantity, $productPrice, $store_price, $size, $addedBy, $dateAdded);
                } elseif ($flag === 'releaseRecord') {
                    // Validate the row for releaseRecords
                    if (count($data) < 5) {
                        throw new Exception("Invalid row format for releaseRecords at line $rowIndex.");
                    }

                    // Map .csv values to variables for releaseRecords
                    $productID = $data[1];
                    $quantity = $data[2];
                    $releaseType = $data[3];
                    $timestamp = $data[4];
                    $name = $data[5];
                    $description = $data[6];
                    $releaseBy = $data[7];

                    $insertStmt = $connect->prepare("INSERT INTO releaseRecords (productID, quantity, releaseType, _timestamp, name, description, releaseBy) VALUES (?, ?, ?, ?, ?, ?, ?)");

                    if (!$insertStmt) {
                        throw new Exception("Failed to prepare statement: " . $connect->error);
                    }

                    $insertStmt->bind_param("iissssi", $productID, $quantity, $releaseType, $timestamp, $name, $description, $releaseBy);
                }

                // Execute the prepared statement
                if (!$insertStmt->execute()) {
                    throw new Exception("Failed to execute insert statement: " . $insertStmt->error);
                }
            }

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

            $response['error'] = false;
            $response['message'] = "Data successfully inserted into $flag table.";
        } catch (Exception $e) {
            // Rollback the transaction in case of error
            if ($connect->in_transaction()) {
                $connect->rollback();
            }
            throw $e;
        } finally {
            fclose($handle);
            unlink($fileTmpPath); // Remove the temporary file
        }
    } else {
        throw new Exception("Missing file or flag in the request.");
    }
} catch (Exception $e) {
    $response['error'] = true;
    $response['message'] = $e->getMessage();
}

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