<?php
/**
 * Database Migration Runner
 * Executes patrol system migration
 */

// Prevent timeout on large migrations
set_time_limit(300);

require_once __DIR__ . '/../config/bootstrap.php';

$db = $GLOBALS['db'];

// Read migration file
$migrationFile = SSCI_ROOT . '/database/migrations/010_create_patrol_system.sql';

if (!file_exists($migrationFile)) {
    die('Migration file not found: ' . $migrationFile);
}

echo "<pre style='background: #f5f5f5; padding: 20px; border-radius: 5px; font-family: monospace;'>";
echo "<strong>🔄 Running Patrol System Migration...</strong>\n\n";

try {
    // Read SQL file
    $sql = file_get_contents($migrationFile);
    
    // Split by semicolon and execute each statement
    $statements = array_filter(array_map('trim', explode(';', $sql)));
    
    $executed = 0;
    $errors = [];
    
    foreach ($statements as $statement) {
        if (empty($statement)) continue;
        
        try {
            $db->query($statement);
            echo "✅ Executed: " . substr($statement, 0, 60) . "...\n";
            $executed++;
        } catch (\Exception $e) {
            echo "⚠️ Error: " . $e->getMessage() . "\n";
            $errors[] = $e->getMessage();
        }
    }
    
    echo "\n" . str_repeat("=", 70) . "\n";
    echo "Migration Complete!\n";
    echo "Executed: $executed statements\n";
    
    if (!empty($errors)) {
        echo "Errors: " . count($errors) . "\n";
        foreach ($errors as $error) {
            echo "  • $error\n";
        }
    }
    
    // Verify tables exist
    echo "\n<strong>📊 Table Verification:</strong>\n";
    $tables = ['patrol_points', 'patrol_scans', 'patrols', 'patrol_point_status'];
    
    foreach ($tables as $table) {
        $result = $db->fetch("SHOW TABLES LIKE ?", [$table]);
        echo ($result ? "✅" : "❌") . " Table: $table\n";
    }
    
    echo "\n<strong style='color: green;'>✅ Migration completed successfully!</strong>\n";
    echo "You can now use the patrol system.\n";
    
} catch (\Exception $e) {
    echo "\n<strong style='color: red;'>❌ Migration failed:</strong>\n";
    echo $e->getMessage() . "\n";
    echo $e->getTraceAsString();
}

echo "</pre>";

// Output HTML for browser
?>
<!DOCTYPE html>
<html>
<head>
    <title>Database Migration</title>
    <style>
        body {
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .container {
            background: white;
            border-radius: 10px;
            box-shadow: 0 10px 40px rgba(0,0,0,0.2);
            padding: 40px;
            max-width: 800px;
            width: 100%;
        }
        h1 {
            color: #333;
            margin-bottom: 20px;
        }
        .success {
            background: #d4edda;
            border: 1px solid #c3e6cb;
            color: #155724;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .info {
            background: #d1ecf1;
            border: 1px solid #bee5eb;
            color: #0c5460;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .button {
            display: inline-block;
            padding: 10px 20px;
            background: #667eea;
            color: white;
            text-decoration: none;
            border-radius: 5px;
            margin-top: 20px;
        }
        .button:hover {
            background: #764ba2;
        }
        pre {
            background: #f8f9fa;
            padding: 20px;
            border-radius: 5px;
            overflow-x: auto;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>🚀 Database Migration Runner</h1>
        <div class="success">
            ✅ Database migration has been executed!
        </div>
        <div class="info">
            The patrol system tables (patrol_points, patrol_scans, patrols, patrol_point_status) have been created.
        </div>
        <p>You can now:</p>
        <ul>
            <li>Navigate to <strong>Patrols</strong> in the admin menu</li>
            <li>Create patrol points for your security posts</li>
            <li>Configure guards to perform patrols</li>
            <li>Track QR code scans in real-time</li>
        </ul>
        <a href="?page=patrols" class="button">Go to Patrols Dashboard →</a>
    </div>
</body>
</html>
