<?php
// Include your database connection
require_once 'db.php';

// Function to sanitize input data
function sanitize($con, $data) {
    return mysqli_real_escape_string($con, trim($data));
}

// Initialize response array for AJAX
$response = array(
    'status' => 'error',
    'message' => 'An error occurred while processing the request.'
);

// Ensure clean output - no whitespace or PHP notices before JSON
ob_clean(); // Clear any previous output buffer

try {
    // Start transaction
    mysqli_begin_transaction($con);

    $productId = isset($_POST['id']) ? intval($_POST['id']) : 0;
    
    if ($productId <= 0) {
        throw new Exception("Invalid product ID");
    }
    
    // Get existing variations for this product
    $existingVariations = array();
    $result = mysqli_query($con, "SELECT pvid, vname FROM variation WHERE pid = $productId");
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $existingVariations[$row['vname']] = $row['pvid'];
        }
        mysqli_free_result($result);
    }
    
    // Track processed variations to identify which need to be deleted
    $processedNames = array();
    
    // Process variation data from POST
    if (isset($_POST['kt_ecommerce_add_product_options']) && is_array($_POST['kt_ecommerce_add_product_options'])) {
        foreach ($_POST['kt_ecommerce_add_product_options'] as $variation) {
            // Make sure we have both the variation name and value(s)
            if (!empty($variation['variation3']) && isset($variation['variation4'])) {
                $variationName = sanitize($con, $variation['variation3']);
                $processedNames[] = $variationName;
                
                // Handle both array and string values for variation4
                if (is_array($variation['variation4'])) {
                    $variationValue = implode(',', array_map(function($v) use ($con) {
                        return sanitize($con, $v);
                    }, $variation['variation4']));
                } else {
                    $variationValue = sanitize($con, $variation['variation4']);
                }
                
                // Only process if we have a value
                if (!empty($variationValue)) {
                    // Check if this variation name already exists for this product
                    if (array_key_exists($variationName, $existingVariations)) {
                        // Update the existing variation
                        $varId = $existingVariations[$variationName];
                        $updateSql = "UPDATE variation SET vvalue = '$variationValue' WHERE pvid = $varId";
                        if (!mysqli_query($con, $updateSql)) {
                            throw new Exception("Failed to update variation: " . mysqli_error($con));
                        }
                    } else {
                        // This is a new variation name, insert it
                        $insertSql = "INSERT INTO variation (pid, vname, vvalue) VALUES ($productId, '$variationName', '$variationValue')";
                        if (!mysqli_query($con, $insertSql)) {
                            throw new Exception("Failed to insert variation: " . mysqli_error($con));
                        }
                    }
                }
            }
        }
    }
    
    // Delete variations that weren't in the current submission
    foreach ($existingVariations as $name => $id) {
        if (!in_array($name, $processedNames)) {
            $deleteSql = "DELETE FROM variation WHERE pvid = $id";
            if (!mysqli_query($con, $deleteSql)) {
                throw new Exception("Failed to remove obsolete variation: " . mysqli_error($con));
            }
        }
    }
    
    // If we got here, everything succeeded
    mysqli_commit($con);
    
    $response['status'] = 'success';
    $response['message'] = 'Product variations updated successfully.';
    
} catch (Exception $e) {
    // Roll back the transaction if something failed
    mysqli_rollback($con);
    $response['message'] = $e->getMessage();
    error_log("Variation update error: " . $e->getMessage());
} finally {
    // Close database connection
    if (isset($con) && $con) {
        mysqli_close($con);
    }
}

// Set proper JSON header
header('Content-Type: application/json');

// Encode and output the response
echo json_encode($response);
exit;
?>