<?php
// Establish a database connection 
ini_set('max_execution_time', 0); // Increase execution time if necessary
$db = mysqli_connect("localhost", "root", "", "shangrilaperfumes");

if (!$db) {
    die("Connection failed: " . mysqli_connect_error());
}

try {
    // Retrieve data sent from frontend
    $data = json_decode($_POST['data'], true); // Mapped fields data
    $vendorId = intval($_POST['vendor']); // Selected vendor ID

    $insertedCount = 0;
    $updatedCount = 0;

    // Begin transaction
    mysqli_autocommit($db, false);

    // Loop through the data
    foreach ($data as $row) {
        $upc = isset($row['UPC']) ? intval($row['UPC']) : null; 
        $sku = isset($row['SKU']) ? $row['SKU'] : "";
        $product = isset($row['Product']) ? $row['Product'] : null;
        $priceString = isset($row['Price']) ? $row['Price'] : null;
        $price = null;

        if ($priceString !== null) {
            $priceString = preg_replace('/\s+/', '', $priceString); // Remove spaces
            $priceString = filter_var($priceString, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
            $price = number_format((float) $priceString, 4, '.', ''); // Ensure four decimal places
        }

        $brand = isset($row['Brand']) ? $row['Brand'] : "";
        $stock = isset($row['Stock']) ? intval($row['Stock']) : 0;

        // Check if UPC and Vendor combination already exists in the table
        $checkQuery = "SELECT UPC FROM test_table WHERE UPC = ? AND Vendor = ?";
        $checkStmt = mysqli_prepare($db, $checkQuery);
        mysqli_stmt_bind_param($checkStmt, "ii", $upc, $vendorId);
        mysqli_stmt_execute($checkStmt);
        mysqli_stmt_store_result($checkStmt);

        // If a row with the same UPC and Vendor exists, update the existing row
        if (mysqli_stmt_num_rows($checkStmt) > 0) {
            $updateQuery = "UPDATE test_table 
                            SET SKU = ?, Product = ?, Price = ?, Brand = ?, Stock = ? 
                            WHERE UPC = ? AND Vendor = ?";
            $updateStmt = mysqli_prepare($db, $updateQuery);
            mysqli_stmt_bind_param($updateStmt, "ssdssii", $sku, $product, $price, $brand, $stock, $upc, $vendorId);
            mysqli_stmt_execute($updateStmt);
            mysqli_stmt_close($updateStmt);
            $updatedCount++; 
        } else {
            // Insert the data if it doesn't already exist
            $insertQuery = "INSERT INTO test_table (UPC, SKU, Product, Price, Brand, Stock, Vendor) 
                            VALUES (?, ?, ?, ?, ?, ?, ?)";
            $insertStmt = mysqli_prepare($db, $insertQuery);
            mysqli_stmt_bind_param($insertStmt, "issdssi", $upc, $sku, $product, $price, $brand, $stock, $vendorId);
            mysqli_stmt_execute($insertStmt);
            mysqli_stmt_close($insertStmt);
            $insertedCount++;
        }
    }

    // Commit the transaction
    mysqli_commit($db);

    echo "Data imported successfully! 
    $insertedCount rows inserted, 
    $updatedCount rows updated.";

    // Close the database connection
    mysqli_close($db);
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
    // Rollback the transaction in case of error
    mysqli_rollback($db);
    // Close the database connection
    mysqli_close($db);
}
?>
