<?php
session_start();
require_once '../../../db.php';

$pid = isset($_POST['pid']) ? intval($_POST['pid']) : 0;
$variations = isset($_POST['variations']) ? json_decode($_POST['variations'], true) : [];

// Arrays to keep track of variations
$existingVariations = [];
$variationsToDelete = [];
$variationsToUpdate = [];
$variationsToInsert = [];

// Fetch existing variations
$query = "SELECT PVID, VARIATION_CHAR, VARIATION FROM pro_variation WHERE PID = ?";
$stmt = $con->prepare($query);
$stmt->bind_param('i', $pid);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    $existingVariations[$row['VARIATION_CHAR']] = [
        'PVID' => $row['PVID'],
        'VARIATION' => $row['VARIATION']
    ];
}
$stmt->close();

// Process incoming variations
foreach ($variations as $variation) {
    $variation3 = mysqli_real_escape_string($con, $variation['variation3']);
    $variation4 = mysqli_real_escape_string($con, $variation['variation4']);

    if (!empty($variation3)) {
        if (isset($existingVariations[$variation3])) {
            // Variation exists, prepare for update if changed
            if ($existingVariations[$variation3]['VARIATION'] !== $variation4) {
                $variationsToUpdate[] = [
                    'PVID' => $existingVariations[$variation3]['PVID'],
                    'VARIATION' => $variation4
                ];
            }
            // Remove from deletion list
            unset($existingVariations[$variation3]);
        } else {
            // Variation does not exist, prepare for insertion
            $variationsToInsert[] = [
                'PID' => $pid,
                'VARIATION_CHAR' => $variation3,
                'VARIATION' => $variation4
            ];
        }
    }
}

// Remaining existing variations are to be deleted
foreach ($existingVariations as $variation) {
    $variationsToDelete[] = $variation['PVID'];
}

// Perform database operations
$con->autocommit(false);

try {
    // Delete variations
    foreach ($variationsToDelete as $pvid) {
        $stmtDelete = $con->prepare("DELETE FROM pro_variation WHERE PVID = ?");
        $stmtDelete->bind_param('i', $pvid);
        $stmtDelete->execute();
        $stmtDelete->close();
    }

    // Insert new variations
    foreach ($variationsToInsert as $variation) {
        $stmtInsert = $con->prepare("INSERT INTO pro_variation (PID, VARIATION_CHAR, VARIATION) VALUES (?, ?, ?)");
        $stmtInsert->bind_param('iss', $variation['PID'], $variation['VARIATION_CHAR'], $variation['VARIATION']);
        $stmtInsert->execute();
        $stmtInsert->close();
    }

    // Update existing variations
    foreach ($variationsToUpdate as $variation) {
        $stmtUpdate = $con->prepare("UPDATE pro_variation SET VARIATION = ? WHERE PVID = ?");
        $stmtUpdate->bind_param('si', $variation['VARIATION'], $variation['PVID']);
        $stmtUpdate->execute();
        $stmtUpdate->close();
    }

    $con->commit();
    echo json_encode(['status' => 'success']);
} catch (Exception $e) {
    $con->rollback();
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
}

$con->autocommit(true);
?>