<?php
/**
 * Command Line Database Migration Script
 * 
 * Usage: php migrate_cli.php [config_file]
 * Example: php migrate_cli.php migration_config.php
 */

// Check if running from command line
if (php_sapi_name() !== 'cli') {
    die("This script can only be run from command line.\n");
}

// Get configuration file
$configFile = $argv[1] ?? 'migration_config.php';

if (!file_exists($configFile)) {
    die("Configuration file not found: $configFile\n");
}

$config = include $configFile;

echo "🔄 Database Migration Tool (CLI)\n";
echo "================================\n\n";

echo "Source: {$config['source']['database']} @ {$config['source']['host']}\n";
echo "Destination: {$config['destination']['database']} @ {$config['destination']['host']}\n";
echo "Tables: " . implode(', ', $config['tables']) . "\n\n";

// Confirm before proceeding
echo "Do you want to proceed with the migration? (y/N): ";
$handle = fopen("php://stdin", "r");
$line = fgets($handle);
fclose($handle);

if (trim(strtolower($line)) !== 'y') {
    echo "Migration cancelled.\n";
    exit(0);
}

try {
    // Connect to databases
    echo "Connecting to databases...\n";
    
    $sourcePdo = new PDO(
        "mysql:host={$config['source']['host']};dbname={$config['source']['database']};charset={$config['source']['charset']}",
        $config['source']['username'],
        $config['source']['password'],
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
    
    $destPdo = new PDO(
        "mysql:host={$config['destination']['host']};dbname={$config['destination']['database']};charset={$config['destination']['charset']}",
        $config['destination']['username'],
        $config['destination']['password'],
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
    
    echo "✅ Database connections established.\n\n";
    
    $totalRecords = 0;
    $totalTables = count($config['tables']);
    $currentTable = 0;
    
    foreach ($config['tables'] as $table) {
        $currentTable++;
        echo "[$currentTable/$totalTables] Processing table: $table\n";
        
        // Check if table exists in source
        $checkTable = $sourcePdo->query("SHOW TABLES LIKE '$table'");
        if ($checkTable->rowCount() == 0) {
            echo "  ⚠️  Table $table not found in source database. Skipping.\n";
            continue;
        }
        
        // Check if table exists in destination
        $checkDestTable = $destPdo->query("SHOW TABLES LIKE '$table'");
        if ($checkDestTable->rowCount() == 0) {
            echo "  ⚠️  Table $table not found in destination database. Skipping.\n";
            continue;
        }
        
        // Get record count
        $countStmt = $sourcePdo->query("SELECT COUNT(*) FROM `$table`");
        $recordCount = $countStmt->fetchColumn();
        
        if ($recordCount == 0) {
            echo "  📭 Table $table is empty. Skipping.\n";
            continue;
        }
        
        echo "  📊 Found $recordCount records\n";
        
        // Truncate if requested
        if ($config['options']['truncate_destination']) {
            $destPdo->exec("TRUNCATE TABLE `$table`");
            echo "  🗑️  Destination table truncated\n";
        }
        
        // Get primary key
        $primaryKey = getPrimaryKey($destPdo, $table);
        
        // Migrate in batches
        $batchSize = $config['options']['batch_size'];
        $offset = 0;
        $recordsMigrated = 0;
        
        while ($offset < $recordCount) {
            $stmt = $sourcePdo->query("SELECT * FROM `$table` LIMIT $batchSize OFFSET $offset");
            $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            if (empty($records)) {
                break;
            }
            
            foreach ($records as $record) {
                try {
                    // Apply transformations if any
                    if (isset($config['transformations'][$table])) {
                        foreach ($config['transformations'][$table] as $column => $transformation) {
                            if (isset($record[$column])) {
                                $record[$column] = $transformation($record[$column]);
                            }
                        }
                    }
                    
                    // Apply field mappings if any
                    if (isset($config['field_mappings'][$table])) {
                        foreach ($config['field_mappings'][$table] as $oldField => $newField) {
                            if (isset($record[$oldField])) {
                                $record[$newField] = $record[$oldField];
                                unset($record[$oldField]);
                            }
                        }
                    }
                    
                    $inserted = insertRecord($destPdo, $table, $record, $primaryKey, $config['options']['ignore_duplicates']);
                    $recordsMigrated += $inserted;
                    
                } catch (Exception $e) {
                    echo "  ❌ Error inserting record: " . $e->getMessage() . "\n";
                }
            }
            
            $offset += $batchSize;
            
            // Show progress
            $progress = min(100, ($offset / $recordCount) * 100);
            echo "  📈 Progress: " . number_format($progress, 1) . "% ($recordsMigrated records migrated)\n";
        }
        
        echo "  ✅ Completed: $recordsMigrated records migrated\n\n";
        $totalRecords += $recordsMigrated;
    }
    
    echo "🎉 Migration completed successfully!\n";
    echo "Total records migrated: $totalRecords\n";
    echo "Tables processed: $currentTable\n";
    
} catch (Exception $e) {
    echo "❌ Migration failed: " . $e->getMessage() . "\n";
    exit(1);
}

/**
 * Get primary key column
 */
function getPrimaryKey($pdo, $table) {
    $stmt = $pdo->query("SHOW KEYS FROM `$table` WHERE Key_name = 'PRIMARY'");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result ? $result['Column_name'] : null;
}

/**
 * Insert a single record
 */
function insertRecord($pdo, $table, $record, $primaryKey, $ignoreDuplicates) {
    try {
        $columns = array_keys($record);
        $placeholders = ':' . implode(', :', $columns);
        $columnsList = '`' . implode('`, `', $columns) . '`';
        
        $sql = "INSERT INTO `$table` ($columnsList) VALUES ($placeholders)";
        
        if ($ignoreDuplicates && $primaryKey) {
            $sql .= " ON DUPLICATE KEY UPDATE `$primaryKey` = `$primaryKey`";
        }
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($record);
        
        return 1;
        
    } catch (PDOException $e) {
        if ($ignoreDuplicates && $e->getCode() == 23000) {
            return 0; // Duplicate ignored
        }
        throw $e;
    }
}
?>