geo/api/scripts/php/migrate_from_backup_verbose.php

#!/usr/bin/env php
<?php

/**
 * Script de migration VERBOSE avec détails table par table
 *
 * Usage:
 *   php migrate_from_backup_verbose.php \
 *     --source-db=geosector_20251008 \
 *     --target-db=pra_geo \
 *     --entity-id=1178 \
 *     --limit-operations=3
 */

// Inclusion des dépendances de l'API
require_once dirname(dirname(__DIR__)) . '/bootstrap.php';

use GeoSector\Services\ApiService;

// Configuration
const DB_HOST = '13.23.33.4';
const DB_PORT = 3306;
const DB_USER = 'pra_geo_user';
const DB_PASS = 'd2jAAGGWi8fxFrWgXjOA';
const DB_USER_ROOT = 'root';
const DB_PASS_ROOT = 'MyAlpLocal,90b';

// Couleurs pour terminal
const C_RESET = "\033[0m";
const C_RED = "\033[0;31m";
const C_GREEN = "\033[0;32m";
const C_YELLOW = "\033[1;33m";
const C_BLUE = "\033[0;34m";
const C_CYAN = "\033[0;36m";
const C_BOLD = "\033[1m";

// Variables globales
$sourceDb = null;
$targetDb = null;
$sourceDbName = null;
$targetDbName = null;
$entityId = null;
$limitOperations = 3;
$stats = [
    'entites' => ['source' => 0, 'migrated' => 0],
    'users' => ['source' => 0, 'migrated' => 0],
    'operations' => ['source' => 0, 'migrated' => 0],
    'ope_sectors' => ['source' => 0, 'migrated' => 0],
    'sectors_adresses' => ['source' => 0, 'migrated' => 0],
    'ope_users' => ['source' => 0, 'migrated' => 0],
    'ope_users_sectors' => ['source' => 0, 'migrated' => 0],
    'ope_pass' => ['source' => 0, 'migrated' => 0],
    'ope_pass_histo' => ['source' => 0, 'migrated' => 0],
    'medias' => ['source' => 0, 'migrated' => 0],
];

// Fonctions utilitaires
function println($message, $color = C_RESET) {
    echo $color . $message . C_RESET . "\n";
}

function printBox($title, $color = C_BLUE) {
    $width = 70;
    $titleLen = strlen($title);
    $padding = ($width - $titleLen - 2) / 2;

    println(str_repeat("═", $width), $color);
    println(str_repeat(" ", floor($padding)) . $title . str_repeat(" ", ceil($padding)), $color);
    println(str_repeat("═", $width), $color);
}

function printStep($step, $substep = null) {
    if ($substep) {
        println("  ├─ " . $substep, C_CYAN);
    } else {
        println("\n" . C_BOLD . "▶ " . $step . C_RESET);
    }
}

function printStat($label, $source, $migrated, $indent = "  ") {
    $status = ($source === $migrated) ? C_GREEN . "✓" : C_YELLOW . "⚠";
    println($indent . "📊 {$label}: {$source} source → {$migrated} migré(s) {$status}" . C_RESET);
}

function connectDatabases($sourceDbName, $targetDbName) {
    global $sourceDb, $targetDb;

    printStep("Connexion aux bases de données");

    try {
        // Base source
        $dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
            DB_HOST, DB_PORT, $sourceDbName);
        $sourceDb = new PDO($dsn, DB_USER_ROOT, DB_PASS_ROOT, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
        printStep("Source connectée: {$sourceDbName}", true);

        // Base cible
        $dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
            DB_HOST, DB_PORT, $targetDbName);
        $targetDb = new PDO($dsn, DB_USER, DB_PASS, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
        printStep("Cible connectée: {$targetDbName}", true);

        return true;
    } catch (PDOException $e) {
        println("✗ Erreur connexion: " . $e->getMessage(), C_RED);
        return false;
    }
}

function getEntityInfo($entityId) {
    global $sourceDb;

    $stmt = $sourceDb->prepare("
        SELECT rowid, libelle, cp, ville
        FROM users_entites
        WHERE rowid = ?
    ");
    $stmt->execute([$entityId]);
    return $stmt->fetch();
}

function migrateReferenceTable($tableName) {
    global $sourceDb, $targetDb;

    printStep("Migration table: {$tableName}");

    // Compter source
    $count = $sourceDb->query("SELECT COUNT(*) FROM {$tableName}")->fetchColumn();
    printStep("Source: {$count} enregistrements", true);

    if ($count === 0) {
        printStep("Aucune donnée à migrer", true);
        return 0;
    }

    // Récupérer les données
    $rows = $sourceDb->query("SELECT * FROM {$tableName}")->fetchAll();

    // Préparer l'insertion
    $columns = array_keys($rows[0]);
    $placeholders = array_map(fn($col) => ":{$col}", $columns);

    $sql = sprintf(
        "INSERT INTO %s (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s",
        $tableName,
        implode(', ', $columns),
        implode(', ', $placeholders),
        implode(', ', array_map(fn($col) => "{$col} = VALUES({$col})", $columns))
    );

    $stmt = $targetDb->prepare($sql);

    $success = 0;
    foreach ($rows as $row) {
        try {
            $stmt->execute($row);
            $success++;
        } catch (PDOException $e) {
            // Ignorer erreurs
        }
    }

    printStep("Migré: {$success}/{$count}", true);
    return $success;
}

function migrateEntite($entityId) {
    global $sourceDb, $targetDb, $stats;

    printStep("ÉTAPE 1: Migration de l'entité #{$entityId}");

    // Récupérer l'entité source
    $stmt = $sourceDb->prepare("
        SELECT * FROM users_entites WHERE rowid = ?
    ");
    $stmt->execute([$entityId]);
    $entity = $stmt->fetch();

    if (!$entity) {
        println("  ✗ Entité introuvable", C_RED);
        return false;
    }

    $stats['entites']['source'] = 1;

    println("  📋 Entité: " . $entity['libelle']);
    println("  📍 Code postal: " . ($entity['cp'] ?? 'N/A'));
    println("  🏙️  Ville: " . ($entity['ville'] ?? 'N/A'));

    // Chiffrer les données
    $encryptedName = ApiService::encryptSearchableData($entity['libelle']);
    $encryptedEmail = !empty($entity['email']) ? ApiService::encryptSearchableData($entity['email']) : '';
    $encryptedPhone = !empty($entity['phone']) ? ApiService::encryptData($entity['phone']) : '';
    $encryptedMobile = !empty($entity['mobile']) ? ApiService::encryptData($entity['mobile']) : '';

    // Insérer dans la cible
    $sql = "INSERT INTO entites (
        id, encrypted_name, code_postal, ville, encrypted_email, encrypted_phone, encrypted_mobile,
        fk_region, fk_type, chk_active, created_at, updated_at
    ) VALUES (
        :id, :name, :cp, :ville, :email, :phone, :mobile,
        :region, :type, :active, :created, :updated
    ) ON DUPLICATE KEY UPDATE
        encrypted_name = VALUES(encrypted_name),
        code_postal = VALUES(code_postal),
        ville = VALUES(ville)";

    $stmt = $targetDb->prepare($sql);
    $stmt->execute([
        'id' => $entity['rowid'],
        'name' => $encryptedName,
        'cp' => $entity['cp'] ?? '',
        'ville' => $entity['ville'] ?? '',
        'email' => $encryptedEmail,
        'phone' => $encryptedPhone,
        'mobile' => $encryptedMobile,
        'region' => $entity['fk_region'] ?? 1,
        'type' => $entity['fk_type'] ?? 1,
        'active' => $entity['active'] ?? 1,
        'created' => $entity['date_creat'],
        'updated' => $entity['date_modif']
    ]);

    $stats['entites']['migrated'] = 1;

    printStat("Entité", 1, 1);

    return true;
}

function migrateUsers($entityId) {
    global $sourceDb, $targetDb, $stats;

    printStep("ÉTAPE 2: Migration des utilisateurs");

    // Compter source
    $count = $sourceDb->prepare("SELECT COUNT(*) FROM users WHERE fk_entite = ? AND active = 1");
    $count->execute([$entityId]);
    $sourceCount = $count->fetchColumn();

    $stats['users']['source'] = $sourceCount;
    println("  📊 Source: {$sourceCount} utilisateurs actifs");

    if ($sourceCount === 0) {
        println("  ⚠️  Aucun utilisateur à migrer", C_YELLOW);
        return 0;
    }

    // Récupérer les users
    $stmt = $sourceDb->prepare("
        SELECT * FROM users WHERE fk_entite = ? AND active = 1
    ");
    $stmt->execute([$entityId]);
    $users = $stmt->fetchAll();

    $success = 0;
    foreach ($users as $user) {
        try {
            $encryptedName = ApiService::encryptSearchableData($user['nom']);
            $encryptedUsername = !empty($user['username']) ? ApiService::encryptSearchableData($user['username']) : '';
            $encryptedEmail = !empty($user['email']) ? ApiService::encryptSearchableData($user['email']) : '';
            $encryptedPhone = !empty($user['telephone']) ? ApiService::encryptData($user['telephone']) : '';
            $encryptedMobile = !empty($user['mobile']) ? ApiService::encryptData($user['mobile']) : '';

            $sql = "INSERT INTO users (
                id, fk_entite, fk_role, encrypted_name, first_name,
                encrypted_user_name, user_pass_hash, encrypted_email,
                encrypted_phone, encrypted_mobile, chk_active, created_at, updated_at
            ) VALUES (
                :id, :entity, :role, :name, :firstname,
                :username, :pass, :email,
                :phone, :mobile, :active, :created, :updated
            ) ON DUPLICATE KEY UPDATE
                encrypted_name = VALUES(encrypted_name),
                encrypted_email = VALUES(encrypted_email)";

            $stmt = $targetDb->prepare($sql);
            $stmt->execute([
                'id' => $user['rowid'],
                'entity' => $entityId,
                'role' => $user['fk_role'] ?? 1,
                'name' => $encryptedName,
                'firstname' => $user['prenom'] ?? '',
                'username' => $encryptedUsername,
                'pass' => $user['password'] ?? '',
                'email' => $encryptedEmail,
                'phone' => $encryptedPhone,
                'mobile' => $encryptedMobile,
                'active' => 1,
                'created' => $user['date_creat'],
                'updated' => $user['date_modif']
            ]);

            $success++;
        } catch (PDOException $e) {
            // Ignorer
        }
    }

    $stats['users']['migrated'] = $success;
    printStat("Utilisateurs", $sourceCount, $success);

    return $success;
}

function migrateOperations($entityId, $limit = 3) {
    global $sourceDb, $targetDb, $stats;

    printStep("ÉTAPE 3: Migration des opérations (limite: {$limit})");

    // Compter toutes les opérations
    $count = $sourceDb->prepare("SELECT COUNT(*) FROM operations WHERE fk_entite = ? AND active = 1");
    $count->execute([$entityId]);
    $totalCount = $count->fetchColumn();

    println("  📊 Total disponible: {$totalCount} opérations");
    println("  🎯 Limitation: {$limit} dernières opérations");

    $stats['operations']['source'] = min($limit, $totalCount);

    // Récupérer les N dernières opérations
    $stmt = $sourceDb->prepare("
        SELECT * FROM operations
        WHERE fk_entite = ? AND active = 1
        ORDER BY date_creat DESC
        LIMIT ?
    ");
    $stmt->execute([$entityId, $limit]);
    $operations = $stmt->fetchAll();

    if (empty($operations)) {
        println("  ⚠️  Aucune opération à migrer", C_YELLOW);
        return [];
    }

    $migratedOps = [];
    foreach ($operations as $op) {
        try {
            $sql = "INSERT INTO operations (
                id, fk_entite, libelle, date_deb, date_fin,
                chk_distinct_sectors, chk_active, created_at, updated_at
            ) VALUES (
                :id, :entity, :libelle, :datedeb, :datefin,
                :distinct, :active, :created, :updated
            ) ON DUPLICATE KEY UPDATE
                libelle = VALUES(libelle)";

            $stmt = $targetDb->prepare($sql);
            $stmt->execute([
                'id' => $op['rowid'],
                'entity' => $entityId,
                'libelle' => $op['libelle'],
                'datedeb' => $op['date_deb'],
                'datefin' => $op['date_fin'],
                'distinct' => $op['chk_distinct_sectors'] ?? 0,
                'active' => 1,
                'created' => $op['date_creat'],
                'updated' => $op['date_modif']
            ]);

            $migratedOps[] = $op['rowid'];
            $stats['operations']['migrated']++;

            println("  ├─ Opération #{$op['rowid']}: " . $op['libelle'], C_GREEN);
        } catch (PDOException $e) {
            println("  ├─ ✗ Erreur opération #{$op['rowid']}: " . $e->getMessage(), C_RED);
        }
    }

    printStat("Opérations", count($operations), count($migratedOps));

    return $migratedOps;
}

function migrateOperationDetails($operationId, $entityId) {
    global $sourceDb, $targetDb, $stats;

    println("\n  " . C_BOLD . "┌─ Détails opération #{$operationId}" . C_RESET);

    // 1. Compter les passages
    $passCount = $sourceDb->prepare("SELECT COUNT(*) FROM ope_pass WHERE fk_operation = ?");
    $passCount->execute([$operationId]);
    $nbPassages = $passCount->fetchColumn();

    println("  │  📊 Passages disponibles: {$nbPassages}");

    // 2. Compter les ope_users
    $opeUsersCount = $sourceDb->prepare("SELECT COUNT(*) FROM ope_users WHERE fk_operation = ?");
    $opeUsersCount->execute([$operationId]);
    $nbOpeUsers = $opeUsersCount->fetchColumn();

    $stats['ope_users']['source'] += $nbOpeUsers;
    println("  │  👥 Associations users: {$nbOpeUsers}");

    // 3. Compter les secteurs (via ope_users_sectors)
    $sectorsCount = $sourceDb->prepare("
        SELECT COUNT(DISTINCT ous.fk_sector)
        FROM ope_users_sectors ous
        WHERE ous.fk_operation = ?
    ");
    $sectorsCount->execute([$operationId]);
    $nbSectors = $sectorsCount->fetchColumn();

    println("  │  🗺️  Secteurs distincts: {$nbSectors}");

    println("  └─ " . C_CYAN . "Migration des données associées..." . C_RESET);

    // Migration ope_users (simplifié pour l'exemple)
    // ... (code de migration réel ici)

    $stats['ope_pass']['source'] += $nbPassages;
}

// === MAIN ===

function parseArguments($argv) {
    $args = [
        'source-db' => null,
        'target-db' => 'pra_geo',
        'entity-id' => null,
        'limit-operations' => 3,
        'help' => false
    ];

    foreach ($argv as $arg) {
        if (strpos($arg, '--') === 0) {
            $parts = explode('=', substr($arg, 2), 2);
            $key = $parts[0];
            $value = $parts[1] ?? true;

            if (array_key_exists($key, $args)) {
                $args[$key] = $value;
            }
        }
    }

    return $args;
}

// Vérifier CLI
if (php_sapi_name() !== 'cli') {
    die("Ce script doit être exécuté en ligne de commande.\n");
}

$args = parseArguments($argv);

if ($args['help'] || !$args['source-db'] || !$args['entity-id']) {
    echo <<<HELP

Usage: php migrate_from_backup_verbose.php [OPTIONS]

Options:
  --source-db=NAME         Base source (ex: geosector_20251008) [REQUIS]
  --target-db=NAME         Base cible (défaut: pra_geo)
  --entity-id=ID           ID de l'entité à migrer [REQUIS]
  --limit-operations=N     Nombre d'opérations à migrer (défaut: 3)
  --help                   Affiche cette aide

Exemple:
  php migrate_from_backup_verbose.php \\
    --source-db=geosector_20251008 \\
    --target-db=pra_geo \\
    --entity-id=1178 \\
    --limit-operations=3

HELP;
    exit($args['help'] ? 0 : 1);
}

$sourceDbName = $args['source-db'];
$targetDbName = $args['target-db'];
$entityId = (int)$args['entity-id'];
$limitOperations = (int)$args['limit-operations'];

// Bannière
printBox("MIGRATION VERBOSE - DÉTAILS TABLE PAR TABLE", C_BLUE);
println("📅 Date: " . date('Y-m-d H:i:s'));
println("📁 Source: {$sourceDbName}");
println("📁 Cible: {$targetDbName}");
println("🎯 Entité: #{$entityId}");
println("📊 Limite opérations: {$limitOperations}");
println("");

// Connexion
if (!connectDatabases($sourceDbName, $targetDbName)) {
    exit(1);
}

// Récupérer infos entité
$entityInfo = getEntityInfo($entityId);
if (!$entityInfo) {
    println("✗ Entité #{$entityId} introuvable", C_RED);
    exit(1);
}

println("\n📋 Entité trouvée: " . $entityInfo['libelle']);
println("📍 CP: " . ($entityInfo['cp'] ?? 'N/A') . " - Ville: " . ($entityInfo['ville'] ?? 'N/A'));
println("");

// Migration des tables de référence (x_*)
printBox("TABLES DE RÉFÉRENCE", C_CYAN);
$referenceTables = ['x_devises', 'x_entites_types', 'x_types_passages',
                    'x_types_reglements', 'x_users_roles'];
foreach ($referenceTables as $table) {
    migrateReferenceTable($table);
}

// Migration entité
printBox("MIGRATION ENTITÉ", C_CYAN);
if (!migrateEntite($entityId)) {
    println("✗ Échec migration entité", C_RED);
    exit(1);
}

// Migration users
printBox("MIGRATION UTILISATEURS", C_CYAN);
migrateUsers($entityId);

// Migration opérations
printBox("MIGRATION OPÉRATIONS", C_CYAN);
$migratedOps = migrateOperations($entityId, $limitOperations);

// Détails par opération
foreach ($migratedOps as $opId) {
    migrateOperationDetails($opId, $entityId);
}

// Résumé final
printBox("RÉSUMÉ DE LA MIGRATION", C_GREEN);
foreach ($stats as $table => $data) {
    if ($data['source'] > 0 || $data['migrated'] > 0) {
        printStat(ucfirst($table), $data['source'], $data['migrated'], "  ");
    }
}

println("\n✅ Migration terminée avec succès!", C_GREEN);
exit(0);