<?php
/**
 * SQLite to MySQL Migration Script
 * 
 * This script migrates data from SQLite database to MySQL database.
 * It handles schema conversion, data migration, and verification.
 * 
 * Usage:
 *   php migrate_to_mysql.php [options]
 * 
 * Options:
 *   --dry-run    Perform migration without actually writing to MySQL
 *   --verbose    Show detailed progress information
 *   --force      Skip confirmation prompts
 *   --help       Show this help message
 */

// Prevent running from web browser
if (php_sapi_name() !== 'cli') {
    die("This script must be run from command line.\n");
}

// Parse command line arguments
$options = getopt('', ['dry-run', 'verbose', 'force', 'help']);
$dryRun = isset($options['dry-run']);
$verbose = isset($options['verbose']);
$force = isset($options['force']);
$showHelp = isset($options['help']);

if ($showHelp) {
    echo "SQLite to MySQL Migration Script\n";
    echo "=================================\n\n";
    echo "Usage: php migrate_to_mysql.php [options]\n\n";
    echo "Options:\n";
    echo "  --dry-run    Perform migration without actually writing to MySQL\n";
    echo "  --verbose    Show detailed progress information\n";
    echo "  --force      Skip confirmation prompts\n";
    echo "  --help       Show this help message\n\n";
    exit(0);
}

// Configuration
$logFile = __DIR__ . '/migration_log_' . date('Y-m-d_H-i-s') . '.txt';
$startTime = microtime(true);

// Initialize logging
function log_message($message, $level = 'INFO') {
    global $logFile, $verbose;
    $timestamp = date('Y-m-d H:i:s');
    $logLine = "[$timestamp] [$level] $message\n";
    file_put_contents($logFile, $logLine, FILE_APPEND);
    
    if ($verbose || $level === 'ERROR' || $level === 'WARNING') {
        echo $logLine;
    } elseif ($level === 'INFO') {
        echo $message . "\n";
    }
}

function log_error($message) {
    log_message($message, 'ERROR');
}

function log_warning($message) {
    log_message($message, 'WARNING');
}

function log_verbose($message) {
    global $verbose;
    if ($verbose) {
        log_message($message, 'DEBUG');
    }
}

// Progress display
function show_progress($current, $total, $label = '') {
    $percentage = $total > 0 ? ($current / $total) * 100 : 0;
    $bar = str_repeat('=', (int)($percentage / 2));
    $spaces = str_repeat(' ', 50 - strlen($bar));
    printf("\r[%s%s] %3d%% %s", $bar, $spaces, (int)$percentage, $label);
    if ($current >= $total) {
        echo "\n";
    }
}

echo "\n";
echo "╔════════════════════════════════════════════════════════════╗\n";
echo "║     SQLite to MySQL Migration Script                      ║\n";
echo "║     MyGB School Management System                          ║\n";
echo "╚════════════════════════════════════════════════════════════╝\n";
echo "\n";

if ($dryRun) {
    echo "🔍 DRY RUN MODE - No changes will be made to MySQL\n\n";
}

log_message("Migration started");
log_message("Options: dry-run=" . ($dryRun ? 'yes' : 'no') . ", verbose=" . ($verbose ? 'yes' : 'no') . ", force=" . ($force ? 'yes' : 'no'));

// Step 1: Pre-flight checks
echo "Step 1: Pre-flight Checks\n";
echo "─────────────────────────\n";

// Check if config file exists
$configFile = __DIR__ . '/config/database.php';
if (!file_exists($configFile)) {
    log_error("Configuration file not found: $configFile");
    echo "❌ Configuration file missing!\n";
    echo "   Please copy config/database.php.example to config/database.php\n";
    echo "   and configure your MySQL credentials.\n";
    exit(1);
}

require_once $configFile;

// Check if MySQL configuration is present
if (!defined('DB_HOST') || !defined('DB_USER') || !defined('DB_NAME')) {
    log_error("MySQL configuration incomplete in config/database.php");
    echo "❌ MySQL configuration incomplete!\n";
    echo "   Please ensure DB_HOST, DB_USER, DB_NAME are defined.\n";
    exit(1);
}

echo "✓ Configuration file found\n";
log_verbose("Config file: $configFile");

// Check if SQLite database exists
$sqliteDb = defined('SQLITE_DB_PATH') ? SQLITE_DB_PATH : __DIR__ . '/data/school.db';
if (!file_exists($sqliteDb)) {
    log_error("SQLite database not found: $sqliteDb");
    echo "❌ SQLite database not found: $sqliteDb\n";
    exit(1);
}

echo "✓ SQLite database found: $sqliteDb\n";
log_verbose("SQLite database size: " . filesize($sqliteDb) . " bytes");

// Try to connect to MySQL
echo "  Connecting to MySQL server...\n";
$mysqli = @new mysqli(DB_HOST, DB_USER, defined('DB_PASS') ? DB_PASS : '', '', defined('DB_PORT') ? DB_PORT : 3306);

if ($mysqli->connect_error) {
    log_error("MySQL connection failed: " . $mysqli->connect_error);
    echo "❌ Cannot connect to MySQL server!\n";
    echo "   Error: " . $mysqli->connect_error . "\n";
    echo "\n";
    echo "Troubleshooting:\n";
    echo "  • Verify MySQL server is running (check XAMPP Control Panel)\n";
    echo "  • Check credentials in config/database.php\n";
    echo "  • Verify host: " . DB_HOST . "\n";
    echo "  • Verify user: " . DB_USER . "\n";
    exit(1);
}

echo "✓ MySQL connection successful\n";
log_verbose("MySQL server version: " . $mysqli->server_info);

// Check if database exists (we'll create it if not)
$dbName = DB_NAME;
$result = $mysqli->query("SHOW DATABASES LIKE '$dbName'");
if ($result->num_rows > 0) {
    echo "⚠ Database '$dbName' already exists\n";
    if (!$force && !$dryRun) {
        echo "\n";
        echo "WARNING: The target database already exists!\n";
        echo "This migration will DROP all existing tables and recreate them.\n";
        echo "All data in the MySQL database will be LOST!\n";
        echo "\n";
        echo "Do you want to continue? (yes/no): ";
        $handle = fopen("php://stdin", "r");
        $line = trim(fgets($handle));
        fclose($handle);
        if (strtolower($line) !== 'yes') {
            echo "Migration cancelled.\n";
            log_message("Migration cancelled by user");
            exit(0);
        }
    }
} else {
    echo "✓ Database '$dbName' will be created\n";
}

// Connect to SQLite
try {
    $sqlite = new PDO('sqlite:' . $sqliteDb);
    $sqlite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "✓ SQLite connection successful\n";
    log_verbose("SQLite connection established");
} catch (PDOException $e) {
    log_error("SQLite connection failed: " . $e->getMessage());
    echo "❌ Cannot connect to SQLite database!\n";
    echo "   Error: " . $e->getMessage() . "\n";
    exit(1);
}

echo "\n✅ All pre-flight checks passed!\n\n";

// Confirmation prompt
if (!$force && !$dryRun) {
    echo "Ready to migrate from SQLite to MySQL.\n";
    echo "Source: $sqliteDb\n";
    echo "Target: " . DB_HOST . "/" . DB_NAME . "\n";
    echo "\n";
    echo "Continue with migration? (yes/no): ";
    $handle = fopen("php://stdin", "r");
    $line = trim(fgets($handle));
    fclose($handle);
    if (strtolower($line) !== 'yes') {
        echo "Migration cancelled.\n";
        log_message("Migration cancelled by user");
        exit(0);
    }
    echo "\n";
}

// Include migration functions
require_once __DIR__ . '/includes/db.php';

// Migration statistics
$stats = [
    'tables_migrated' => 0,
    'records_migrated' => 0,
    'errors' => 0,
    'warnings' => 0
];

// Rollback mechanism: Each table migration uses transactions
// If any error occurs during data migration, the transaction is rolled back
// The SQLite database is preserved and backed up before migration starts
// In case of failure, you can:
//   1. Fix the issue and re-run the migration
//   2. Restore from the SQLite backup if needed
//   3. Keep using SQLite by not changing DB_TYPE in config

// Set up error handler for critical failures
set_error_handler(function($errno, $errstr, $errfile, $errline) {
    log_error("PHP Error [$errno]: $errstr in $errfile on line $errline");
    return false; // Let PHP handle the error normally
});

// Set up exception handler
set_exception_handler(function($exception) {
    log_error("Uncaught exception: " . $exception->getMessage());
    log_error("Stack trace: " . $exception->getTraceAsString());
    echo "\n❌ CRITICAL ERROR: " . $exception->getMessage() . "\n";
    echo "Migration aborted. Check log file for details.\n";
    exit(1);
});

echo "Starting migration process...\n\n";


// Step 2: Backup SQLite database
echo "Step 2: Backup SQLite Database\n";
echo "───────────────────────────────\n";

$backupDir = __DIR__ . '/backups/database';
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0755, true);
    log_verbose("Created backup directory: $backupDir");
}

$backupFile = $backupDir . '/school_backup_' . date('Y-m-d_H-i-s') . '.db';

if (!$dryRun) {
    echo "  Creating backup...\n";
    if (copy($sqliteDb, $backupFile)) {
        echo "✓ Backup created: $backupFile\n";
        log_message("SQLite backup created: $backupFile");
        
        // Verify backup integrity
        $originalSize = filesize($sqliteDb);
        $backupSize = filesize($backupFile);
        
        if ($originalSize === $backupSize) {
            echo "✓ Backup integrity verified (size: " . number_format($backupSize) . " bytes)\n";
            log_verbose("Backup size matches original: $backupSize bytes");
        } else {
            log_warning("Backup size mismatch! Original: $originalSize, Backup: $backupSize");
            echo "⚠ Warning: Backup size differs from original\n";
            $stats['warnings']++;
        }
        
        // Try to open backup to verify it's a valid SQLite database
        try {
            $testDb = new PDO('sqlite:' . $backupFile);
            $testDb->query("SELECT 1");
            $testDb = null;
            echo "✓ Backup file is valid SQLite database\n";
            log_verbose("Backup file validated successfully");
        } catch (PDOException $e) {
            log_error("Backup file validation failed: " . $e->getMessage());
            echo "❌ Backup file is corrupted!\n";
            echo "   Migration aborted for safety.\n";
            exit(1);
        }
    } else {
        log_error("Failed to create backup");
        echo "❌ Failed to create backup!\n";
        echo "   Migration aborted for safety.\n";
        exit(1);
    }
} else {
    echo "  [DRY RUN] Would create backup: $backupFile\n";
    log_verbose("[DRY RUN] Backup would be created at: $backupFile");
}

echo "\n";


// Step 3: Create MySQL database
echo "Step 3: Create MySQL Database\n";
echo "──────────────────────────────\n";

if (!$dryRun) {
    // Create database with utf8mb4 charset
    $charset = defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4';
    $collation = 'utf8mb4_unicode_ci';
    
    $createDbSql = "CREATE DATABASE IF NOT EXISTS `$dbName` 
                    CHARACTER SET $charset 
                    COLLATE $collation";
    
    if ($mysqli->query($createDbSql)) {
        echo "✓ Database '$dbName' created/verified\n";
        log_message("Database created: $dbName");
    } else {
        log_error("Failed to create database: " . $mysqli->error);
        echo "❌ Failed to create database!\n";
        echo "   Error: " . $mysqli->error . "\n";
        exit(1);
    }
    
    // Select the database
    if (!$mysqli->select_db($dbName)) {
        log_error("Failed to select database: " . $mysqli->error);
        echo "❌ Failed to select database!\n";
        exit(1);
    }
    
    // Set charset for connection
    if (!$mysqli->set_charset($charset)) {
        log_warning("Failed to set charset: " . $mysqli->error);
        echo "⚠ Warning: Could not set charset to $charset\n";
        $stats['warnings']++;
    } else {
        echo "✓ Character set configured: $charset\n";
        log_verbose("Connection charset set to: $charset");
    }
    
    // Set collation
    $mysqli->query("SET NAMES '$charset' COLLATE '$collation'");
    echo "✓ Collation configured: $collation\n";
    log_verbose("Collation set to: $collation");
    
} else {
    echo "  [DRY RUN] Would create database: $dbName\n";
    echo "  [DRY RUN] Character set: utf8mb4\n";
    echo "  [DRY RUN] Collation: utf8mb4_unicode_ci\n";
    log_verbose("[DRY RUN] Database creation skipped");
}

echo "\n";


// Step 4: Migrate Schema
echo "Step 4: Migrate Database Schema\n";
echo "────────────────────────────────\n";

// Get list of tables from SQLite
$tablesResult = $sqlite->query("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name");
$tables = $tablesResult->fetchAll(PDO::FETCH_COLUMN);

if (empty($tables)) {
    log_warning("No tables found in SQLite database");
    echo "⚠ No tables found in SQLite database\n";
    echo "\n";
} else {
    echo "  Found " . count($tables) . " tables to migrate\n";
    log_message("Tables found: " . implode(', ', $tables));
    
    foreach ($tables as $table) {
        echo "  • Migrating table: $table\n";
        log_verbose("Processing table: $table");
        
        // Get SQLite table schema
        $schemaResult = $sqlite->query("SELECT sql FROM sqlite_master WHERE type='table' AND name='$table'");
        $sqliteSchema = $schemaResult->fetchColumn();
        
        if (!$sqliteSchema) {
            log_warning("Could not retrieve schema for table: $table");
            echo "    ⚠ Warning: Could not retrieve schema\n";
            $stats['warnings']++;
            continue;
        }
        
        log_verbose("Original SQLite schema for $table:\n$sqliteSchema");
        
        // Convert SQLite schema to MySQL
        $mysqlSchema = convert_sqlite_to_mysql_schema($sqliteSchema);
        log_verbose("Converted MySQL schema for $table:\n$mysqlSchema");
        
        if (!$dryRun) {
            // Drop table if exists
            $dropSql = "DROP TABLE IF EXISTS `$table`";
            if (!$mysqli->query($dropSql)) {
                log_error("Failed to drop table $table: " . $mysqli->error);
                echo "    ❌ Failed to drop existing table\n";
                $stats['errors']++;
                continue;
            }
            
            // Create table with MySQL schema
            if ($mysqli->query($mysqlSchema)) {
                echo "    ✓ Schema migrated\n";
                log_verbose("Table $table created successfully");
                
                // Get and migrate indexes
                $indexResult = $sqlite->query("SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name='$table' AND sql IS NOT NULL");
                $indexes = $indexResult->fetchAll(PDO::FETCH_COLUMN);
                
                foreach ($indexes as $indexSql) {
                    if (empty($indexSql)) continue;
                    
                    // Convert SQLite index to MySQL
                    $mysqlIndexSql = str_replace('CREATE INDEX', 'CREATE INDEX', $indexSql);
                    $mysqlIndexSql = str_replace('CREATE UNIQUE INDEX', 'CREATE UNIQUE INDEX', $mysqlIndexSql);
                    
                    // Add IF NOT EXISTS for MySQL 5.7+
                    if (strpos($mysqlIndexSql, 'IF NOT EXISTS') === false) {
                        $mysqlIndexSql = preg_replace('/CREATE (UNIQUE )?INDEX/', 'CREATE $1INDEX IF NOT EXISTS', $mysqlIndexSql);
                    }
                    
                    log_verbose("Creating index: $mysqlIndexSql");
                    
                    if (!$mysqli->query($mysqlIndexSql)) {
                        // Index creation failure is not critical, log as warning
                        log_warning("Failed to create index on $table: " . $mysqli->error);
                        $stats['warnings']++;
                    } else {
                        log_verbose("Index created successfully");
                    }
                }
                
            } else {
                log_error("Failed to create table $table: " . $mysqli->error);
                echo "    ❌ Failed to create table\n";
                echo "       Error: " . $mysqli->error . "\n";
                $stats['errors']++;
                continue;
            }
        } else {
            echo "    [DRY RUN] Would create table with MySQL schema\n";
            if ($verbose) {
                echo "    Schema:\n";
                echo "    " . str_replace("\n", "\n    ", $mysqlSchema) . "\n";
            }
        }
    }
    
    echo "\n✓ Schema migration completed\n";
    log_message("Schema migration completed for " . count($tables) . " tables");
}

echo "\n";


// Step 5: Migrate Data
echo "Step 5: Migrate Data\n";
echo "────────────────────\n";

$batchSize = 100; // Records per batch for performance

foreach ($tables as $table) {
    echo "  • Migrating data for table: $table\n";
    log_verbose("Starting data migration for table: $table");
    
    // Get row count
    $countResult = $sqlite->query("SELECT COUNT(*) FROM `$table`");
    $totalRows = $countResult->fetchColumn();
    
    if ($totalRows == 0) {
        echo "    ℹ No data to migrate\n";
        log_verbose("Table $table is empty");
        continue;
    }
    
    echo "    Found $totalRows records\n";
    log_verbose("Table $table has $totalRows records");
    
    if (!$dryRun) {
        // Get column names
        $columnsResult = $sqlite->query("PRAGMA table_info(`$table`)");
        $columns = $columnsResult->fetchAll(PDO::FETCH_ASSOC);
        $columnNames = array_map(function($col) { return $col['name']; }, $columns);
        
        log_verbose("Columns in $table: " . implode(', ', $columnNames));
        
        // Fetch all data
        $dataResult = $sqlite->query("SELECT * FROM `$table`");
        $rows = $dataResult->fetchAll(PDO::FETCH_ASSOC);
        
        // Begin transaction for atomic migration
        $mysqli->begin_transaction();
        
        try {
            $migratedCount = 0;
            $batch = [];
            
            foreach ($rows as $index => $row) {
                $batch[] = $row;
                
                // Process batch when it reaches batch size or is the last row
                if (count($batch) >= $batchSize || $index == count($rows) - 1) {
                    // Build batch insert query
                    $columnList = '`' . implode('`, `', $columnNames) . '`';
                    $valuePlaceholders = [];
                    $values = [];
                    
                    foreach ($batch as $batchRow) {
                        $rowPlaceholders = [];
                        foreach ($columnNames as $col) {
                            $rowPlaceholders[] = '?';
                            $value = $batchRow[$col] ?? null;
                            
                            // Handle BLOB data
                            if (is_resource($value)) {
                                $value = stream_get_contents($value);
                            }
                            
                            $values[] = $value;
                        }
                        $valuePlaceholders[] = '(' . implode(', ', $rowPlaceholders) . ')';
                    }
                    
                    $insertSql = "INSERT INTO `$table` ($columnList) VALUES " . implode(', ', $valuePlaceholders);
                    
                    // Prepare and execute
                    $stmt = $mysqli->prepare($insertSql);
                    if (!$stmt) {
                        throw new Exception("Failed to prepare statement: " . $mysqli->error);
                    }
                    
                    // Bind parameters
                    $types = str_repeat('s', count($values)); // Use string type for all values
                    $bindParams = array_merge([$types], $values);
                    $refs = [];
                    foreach ($bindParams as $key => $value) {
                        $refs[$key] = &$bindParams[$key];
                    }
                    call_user_func_array([$stmt, 'bind_param'], $refs);
                    
                    if (!$stmt->execute()) {
                        throw new Exception("Failed to insert batch: " . $stmt->error);
                    }
                    
                    $migratedCount += count($batch);
                    $stmt->close();
                    
                    // Show progress
                    show_progress($migratedCount, $totalRows, "($migratedCount/$totalRows records)");
                    
                    // Clear batch
                    $batch = [];
                }
            }
            
            // Commit transaction
            $mysqli->commit();
            
            echo "    ✓ Migrated $migratedCount records\n";
            log_message("Successfully migrated $migratedCount records from table: $table");
            $stats['records_migrated'] += $migratedCount;
            
        } catch (Exception $e) {
            // Rollback on error
            $mysqli->rollback();
            log_error("Data migration failed for table $table: " . $e->getMessage());
            echo "    ❌ Migration failed: " . $e->getMessage() . "\n";
            $stats['errors']++;
        }
        
    } else {
        echo "    [DRY RUN] Would migrate $totalRows records\n";
        log_verbose("[DRY RUN] Data migration skipped for table: $table");
    }
    
    $stats['tables_migrated']++;
}

echo "\n✓ Data migration completed\n";
log_message("Data migration completed. Total records migrated: " . $stats['records_migrated']);

echo "\n";


// Step 6: Verify Data
echo "Step 6: Verify Data Integrity\n";
echo "──────────────────────────────\n";

$verificationResults = [];
$verificationPassed = true;

foreach ($tables as $table) {
    echo "  • Verifying table: $table\n";
    log_verbose("Verifying data integrity for table: $table");
    
    // Get SQLite count
    $sqliteCountResult = $sqlite->query("SELECT COUNT(*) FROM `$table`");
    $sqliteCount = $sqliteCountResult->fetchColumn();
    
    if (!$dryRun) {
        // Get MySQL count
        $mysqlCountResult = $mysqli->query("SELECT COUNT(*) FROM `$table`");
        if (!$mysqlCountResult) {
            log_error("Failed to count records in MySQL table $table: " . $mysqli->error);
            echo "    ❌ Failed to verify (query error)\n";
            $verificationPassed = false;
            $stats['errors']++;
            continue;
        }
        
        $mysqlRow = $mysqlCountResult->fetch_row();
        $mysqlCount = $mysqlRow[0];
        
        // Compare counts
        if ($sqliteCount == $mysqlCount) {
            echo "    ✓ Record count matches: $mysqlCount records\n";
            log_verbose("Table $table: SQLite=$sqliteCount, MySQL=$mysqlCount (MATCH)");
            $verificationResults[$table] = ['status' => 'pass', 'count' => $mysqlCount];
        } else {
            echo "    ❌ Record count mismatch!\n";
            echo "       SQLite: $sqliteCount records\n";
            echo "       MySQL:  $mysqlCount records\n";
            log_error("Table $table: Record count mismatch! SQLite=$sqliteCount, MySQL=$mysqlCount");
            $verificationResults[$table] = ['status' => 'fail', 'sqlite' => $sqliteCount, 'mysql' => $mysqlCount];
            $verificationPassed = false;
            $stats['errors']++;
        }
        
        // Sample record verification (check first 5 records)
        if ($sqliteCount > 0 && $mysqlCount > 0) {
            $sampleSize = min(5, $sqliteCount);
            $sqliteSample = $sqlite->query("SELECT * FROM `$table` LIMIT $sampleSize")->fetchAll(PDO::FETCH_ASSOC);
            $mysqlSample = $mysqli->query("SELECT * FROM `$table` LIMIT $sampleSize");
            
            if ($mysqlSample) {
                $mysqlSampleData = [];
                while ($row = $mysqlSample->fetch_assoc()) {
                    $mysqlSampleData[] = $row;
                }
                
                // Compare sample records
                $sampleMatch = true;
                for ($i = 0; $i < $sampleSize; $i++) {
                    if (isset($sqliteSample[$i]) && isset($mysqlSampleData[$i])) {
                        // Compare key fields (not all fields as some may have type differences)
                        $sqliteKeys = array_keys($sqliteSample[$i]);
                        foreach ($sqliteKeys as $key) {
                            $sqliteVal = $sqliteSample[$i][$key];
                            $mysqlVal = $mysqlSampleData[$i][$key] ?? null;
                            
                            // Normalize for comparison (handle NULL, empty strings, type differences)
                            if ($sqliteVal != $mysqlVal && !($sqliteVal === null && $mysqlVal === null)) {
                                // Allow for minor type differences (e.g., "0" vs 0)
                                if (strval($sqliteVal) !== strval($mysqlVal)) {
                                    log_warning("Sample data mismatch in $table, record $i, column $key: SQLite='$sqliteVal', MySQL='$mysqlVal'");
                                    $sampleMatch = false;
                                }
                            }
                        }
                    }
                }
                
                if ($sampleMatch) {
                    echo "    ✓ Sample records verified\n";
                    log_verbose("Sample records match for table: $table");
                } else {
                    echo "    ⚠ Sample records have minor differences (check log)\n";
                    log_warning("Sample records have differences in table: $table");
                    $stats['warnings']++;
                }
            }
        }
        
    } else {
        echo "    [DRY RUN] Would verify $sqliteCount records\n";
        log_verbose("[DRY RUN] Verification skipped for table: $table");
    }
}

if (!$dryRun) {
    if ($verificationPassed) {
        echo "\n✅ All verification checks passed!\n";
        log_message("Data verification completed successfully");
    } else {
        echo "\n⚠ Some verification checks failed!\n";
        echo "   Please review the errors above and check the log file.\n";
        log_warning("Data verification completed with errors");
    }
} else {
    echo "\n  [DRY RUN] Verification skipped\n";
}

echo "\n";


// Step 7: Generate Migration Report
echo "Step 7: Migration Report\n";
echo "────────────────────────\n";

$endTime = microtime(true);
$duration = $endTime - $startTime;
$durationFormatted = gmdate("H:i:s", (int)$duration);

// Generate summary report
$report = "\n";
$report .= "╔════════════════════════════════════════════════════════════╗\n";
$report .= "║              MIGRATION SUMMARY REPORT                      ║\n";
$report .= "╚════════════════════════════════════════════════════════════╝\n";
$report .= "\n";
$report .= "Migration Date: " . date('Y-m-d H:i:s') . "\n";
$report .= "Duration: $durationFormatted\n";
$report .= "Mode: " . ($dryRun ? "DRY RUN" : "LIVE MIGRATION") . "\n";
$report .= "\n";
$report .= "Source Database:\n";
$report .= "  Type: SQLite\n";
$report .= "  Path: $sqliteDb\n";
$report .= "  Size: " . number_format(filesize($sqliteDb)) . " bytes\n";
$report .= "\n";
$report .= "Target Database:\n";
$report .= "  Type: MySQL\n";
$report .= "  Host: " . DB_HOST . "\n";
$report .= "  Database: " . DB_NAME . "\n";
$report .= "  Charset: " . (defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4') . "\n";
$report .= "\n";
$report .= "Migration Statistics:\n";
$report .= "  Tables Processed: " . $stats['tables_migrated'] . "\n";
$report .= "  Records Migrated: " . number_format($stats['records_migrated']) . "\n";
$report .= "  Errors: " . $stats['errors'] . "\n";
$report .= "  Warnings: " . $stats['warnings'] . "\n";
$report .= "\n";

if (!empty($verificationResults)) {
    $report .= "Verification Results:\n";
    foreach ($verificationResults as $table => $result) {
        if ($result['status'] === 'pass') {
            $report .= "  ✓ $table: " . number_format($result['count']) . " records\n";
        } else {
            $report .= "  ❌ $table: SQLite=" . $result['sqlite'] . ", MySQL=" . $result['mysql'] . "\n";
        }
    }
    $report .= "\n";
}

if (!$dryRun) {
    if ($stats['errors'] == 0) {
        $report .= "Status: ✅ MIGRATION SUCCESSFUL\n";
        $report .= "\n";
        $report .= "Next Steps:\n";
        $report .= "  1. Update config/database.php to set DB_TYPE='mysql'\n";
        $report .= "  2. Test the application thoroughly\n";
        $report .= "  3. Keep SQLite backup for at least 30 days\n";
        $report .= "  4. Monitor application for any issues\n";
        $report .= "\n";
        $report .= "Rollback Information:\n";
        $report .= "  If you need to rollback to SQLite:\n";
        $report .= "  1. Change DB_TYPE='sqlite' in config/database.php\n";
        $report .= "  2. Restore backup if needed: $backupFile\n";
        $report .= "  3. The SQLite database was preserved during migration\n";
    } else {
        $report .= "Status: ⚠ MIGRATION COMPLETED WITH ERRORS\n";
        $report .= "\n";
        $report .= "Action Required:\n";
        $report .= "  1. Review error messages above\n";
        $report .= "  2. Check log file: $logFile\n";
        $report .= "  3. Fix issues and re-run migration if needed\n";
        $report .= "  4. SQLite backup available at: $backupFile\n";
        $report .= "\n";
        $report .= "Rollback Information:\n";
        $report .= "  The migration encountered errors. Your SQLite database is safe.\n";
        $report .= "  - Original SQLite database: $sqliteDb (unchanged)\n";
        $report .= "  - Backup copy: $backupFile\n";
        $report .= "  - Failed table migrations were rolled back automatically\n";
        $report .= "  - You can continue using SQLite or fix errors and retry\n";
    }
} else {
    $report .= "Status: ℹ DRY RUN COMPLETED\n";
    $report .= "\n";
    $report .= "This was a dry run. No changes were made to MySQL.\n";
    $report .= "Review the output above and run without --dry-run to perform actual migration.\n";
}

$report .= "\n";
$report .= "Log File: $logFile\n";
if (!$dryRun && isset($backupFile)) {
    $report .= "Backup File: $backupFile\n";
}
$report .= "\n";
$report .= "╚════════════════════════════════════════════════════════════╝\n";

echo $report;
log_message($report);

// Save report to separate file
$reportFile = __DIR__ . '/migration_report_' . date('Y-m-d_H-i-s') . '.txt';
file_put_contents($reportFile, $report);
echo "\nReport saved to: $reportFile\n";

log_message("Migration completed");

// Exit with appropriate code
if (!$dryRun && $stats['errors'] > 0) {
    exit(1);
} else {
    exit(0);
}
