<?php

$host = '127.0.0.1';
$db   = 'ludikevent';
$user = 'ludikevent';
$pass = 'ludikevent';
$port = "5432";
$dsn = "pgsql:host=$host;port=$port;dbname=$db";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (	PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

echo "Connected to database.\n";

function migrateTable($pdo, $tableName, $columnName, $idColumn = 'id') {
    echo "Checking table: $tableName, column: $columnName\n";
    
    // Check if table exists
    $stmt = $pdo->prepare("SELECT to_regclass(:tablename)");
    $stmt->execute(['tablename' => $tableName]);
    if (!$stmt->fetchColumn()) {
        echo "Table $tableName does not exist. Skipping.\n";
        return;
    }

    // Select only rows that are NOT NULL. 
    // We also want to avoid reprocessing already JSON data if possible, but reliable detection is tricky.
    // The script below checks if data is serialized first. If not, it checks if it's JSON.
    $stmt = $pdo->query("SELECT $idColumn, $columnName FROM $tableName WHERE $columnName IS NOT NULL");
    
    while ($row = $stmt->fetch()) {
        $id = $row[$idColumn];
        $rawData = $row[$columnName];
        
        // Try to unserialize
        $data = @unserialize($rawData);
        
        // Check for unserialization success or specific serialized values
        // unserialize returns false on error AND for serialized boolean false (b:0;)
        $isSerialized = ($data !== false) || ($rawData === 'b:0;') || ($rawData === 'N;');

        if ($isSerialized) {
            // It was a valid serialized string
            if ($rawData === 'N;') {
                 // Serialized null -> SQL NULL
                 $updateStmt = $pdo->prepare("UPDATE $tableName SET $columnName = NULL WHERE $idColumn = :id");
                 $updateStmt->execute(['id' => $id]);
                 echo "Updated ID $id: Serialized NULL -> SQL NULL\n";
            } else {
                 // Convert to JSON
                 $jsonData = json_encode($data);
                 $updateStmt = $pdo->prepare("UPDATE $tableName SET $columnName = :json WHERE $idColumn = :id");
                 $updateStmt->execute(['json' => $jsonData, 'id' => $id]);
                 echo "Updated ID $id: Serialized -> JSON\n";
            }
        } else {
             // Check if it is already valid JSON
             json_decode($rawData);
             if (json_last_error() === JSON_ERROR_NONE) {
                 // It is already JSON, do nothing
                 // echo "ID $id is already JSON. Skipping.\n";
             } else {
                 // It might be a plain string or corrupted data.
                 // For Types::ARRAY columns, data MUST be serialized. 
                 // If we migrated to Types::JSON, data MUST be JSON.
                 // If neither, it's an issue.
                 echo "Warning: ID $id in $tableName is neither valid serialized data nor valid JSON. Raw: " . substr($rawData, 0, 50) . "...\n";
             }
        }
    }
}

migrateTable($pdo, 'contrats_payments', 'card');
migrateTable($pdo, 'formules_product_inclus', 'config');
migrateTable($pdo, 'formules_restriction', 'restriction_config');

echo "Migration complete.\n";