<?php
/**
 * HelperLink Database Schema Executor Utility
 * 
 * This script runs the helperlink_schema.sql baseline schema against the active database.
 * Supports both CLI (terminal) and Web Browser runtimes.
 */

require_once __DIR__ . '/app/Models/Database.php';

try {
    $db = Database::getInstance();
} catch (Exception $e) {
    die("Database connection failed: " . $e->getMessage());
}

$schemaFile = __DIR__ . '/helperlink.sql';
if (!file_exists($schemaFile)) {
    $schemaFile = __DIR__ . '/sql/helperlink_schema.sql';
}
if (!file_exists($schemaFile)) {
    die("Database SQL file not found at: " . $schemaFile);
}

$sqlContent = file_get_contents($schemaFile);

// Remove SQL comments and empty lines
$sqlContent = preg_replace('/--.*\n/', '', $sqlContent);
$sqlContent = preg_replace('/\/\*.*?\*\//s', '', $sqlContent);

// Split statements by semicolon, ignoring empty entries
$statements = array_filter(array_map('trim', explode(';', $sqlContent)));

$successCount = 0;
$failCount = 0;
$errors = [];

$isCli = (php_sapi_name() === 'cli');

if (!$isCli) {
    echo "<!DOCTYPE html>
    <html lang='en'>
    <head>
        <meta charset='UTF-8'>
        <meta name='viewport' content='width=device-width, initial-scale=1.0'>
        <title>Database Schema Executor | HelperLink</title>
        <link href='https://fonts.googleapis.com/css2?family=Outfit:wght@300;400;600;700&display=swap' rel='stylesheet'>
        <style>
            * { box-sizing: border-box; }
            body { 
                font-family: 'Outfit', sans-serif; 
                background: linear-gradient(135deg, #0f172a 0%, #1e1b4b 100%); 
                color: #f1f5f9; 
                padding: 40px 20px; 
                min-height: 100vh;
                margin: 0;
            }
            .container { 
                max-width: 900px; 
                margin: 0 auto; 
                background: rgba(30, 41, 59, 0.7); 
                backdrop-filter: blur(16px);
                border: 1px solid rgba(255, 255, 255, 0.1);
                padding: 40px; 
                border-radius: 20px; 
                box-shadow: 0 20px 25px -5px rgb(0 0 0 / 0.5); 
            }
            h1 { 
                font-size: 2.5rem;
                font-weight: 700;
                background: linear-gradient(to right, #38bdf8, #818cf8);
                -webkit-background-clip: text;
                -webkit-text-fill-color: transparent;
                margin-top: 0; 
                margin-bottom: 10px;
            }
            p { color: #94a3b8; font-size: 1.1rem; margin-bottom: 30px; }
            .stats { display: flex; gap: 20px; margin: 30px 0; }
            .stat-card { 
                padding: 20px; 
                border-radius: 12px; 
                font-size: 1.2rem;
                font-weight: 600; 
                flex: 1; 
                text-align: center;
                border: 1px solid rgba(255, 255, 255, 0.05);
            }
            .success { background: rgba(16, 185, 129, 0.15); color: #34d399; border-color: rgba(16, 185, 129, 0.2); }
            .failed { background: rgba(239, 68, 68, 0.15); color: #f87171; border-color: rgba(239, 68, 68, 0.2); }
            .log { 
                background: #0b0f19; 
                border: 1px solid rgba(255, 255, 255, 0.05);
                padding: 25px; 
                border-radius: 12px; 
                font-family: 'Consolas', monospace; 
                overflow-y: auto; 
                max-height: 400px; 
                line-height: 1.6; 
                font-size: 0.95rem;
            }
            .log-item { display: flex; justify-content: space-between; margin-bottom: 8px; border-bottom: 1px solid rgba(255, 255, 255, 0.02); padding-bottom: 4px; }
            .log-success { color: #4ade80; }
            .log-fail { color: #f87171; }
            .error-list { margin-top: 30px; background: rgba(239, 68, 68, 0.05); border: 1px solid rgba(239, 68, 68, 0.1); padding: 25px; border-radius: 12px; }
            .error-list h3 { color: #f87171; margin-top: 0; }
            pre { background: #070a13; padding: 15px; border-radius: 6px; overflow-x: auto; font-size: 0.85rem; color: #cbd5e1; border: 1px solid rgba(255,255,255,0.03); }
        </style>
    </head>
    <body>
        <div class='container'>
            <h1>Database Schema Executor</h1>
            <p>Processing: <code>sql/helperlink_schema.sql</code></p>
            <div class='log'>";
} else {
    echo "==================================================\n";
    echo "  HELPERLINK DATABASE SCHEMA EXECUTOR\n";
    echo "==================================================\n";
}

// Temporary disable foreign keys checks to prevent drop/recreate constraint ordering crashes
$db->exec("SET FOREIGN_KEY_CHECKS = 0;");

foreach ($statements as $index => $statement) {
    if (empty($statement)) {
        continue;
    }

    $action = "Executing query " . ($index + 1);
    if (preg_match('/CREATE TABLE\s+(\w+)/i', $statement, $matches)) {
        $action = "CREATE TABLE: " . $matches[1];
    } elseif (preg_match('/ALTER TABLE\s+(\w+)/i', $statement, $matches)) {
        $action = "ALTER TABLE: " . $matches[1];
    } elseif (preg_match('/INSERT INTO\s+(\w+)/i', $statement, $matches)) {
        $action = "INSERT INTO: " . $matches[1];
    }

    try {
        $db->exec($statement);
        $successCount++;
        if (!$isCli) {
            echo "<div class='log-item'><span style='color: #94a3b8;'>{$action}</span><span class='log-success'>✔ SUCCESS</span></div>";
        } else {
            echo "✔ {$action} ... SUCCESS\n";
        }
    } catch (PDOException $e) {
        $failCount++;
        $errors[] = [
            'query' => $statement,
            'error' => $e->getMessage()
        ];
        if (!$isCli) {
            echo "<div class='log-item'><span style='color: #f87171;'>{$action}</span><span class='log-fail'>❌ FAILED</span></div>";
        } else {
            echo "❌ {$action} ... FAILED: " . $e->getMessage() . "\n";
        }
    }
}

// Re-enable foreign keys
$db->exec("SET FOREIGN_KEY_CHECKS = 1;");

if (!$isCli) {
    echo "</div>
          <div class='stats'>
              <div class='stat-card success'>Successful Queries: {$successCount}</div>
              <div class='stat-card failed'>Failed Queries: {$failCount}</div>
          </div>";
    if (!empty($errors)) {
        echo "<div class='error-list'>
                <h3>Execution Errors ({$failCount})</h3>
                <ul>";
        foreach ($errors as $err) {
            echo "<li>
                    <strong>Query:</strong> <pre>" . htmlspecialchars(trim($err['query'])) . "</pre>
                    <strong>Error:</strong> <span style='color: #f87171;'>" . htmlspecialchars($err['error']) . "</span>
                  </li>";
        }
        echo "</ul></div>";
    }
    echo "</div></body></html>";
} else {
    echo "==================================================\n";
    echo "  EXECUTION COMPLETED\n";
    echo "==================================================\n";
    echo "Successful Queries: {$successCount}\n";
    echo "Failed Queries: {$failCount}\n";
    if ($failCount > 0) {
        echo "\nErrors:\n";
        foreach ($errors as $i => $err) {
            echo ($i + 1) . ". Query: " . substr(trim($err['query']), 0, 80) . "...\n";
            echo "   Error: " . $err['error'] . "\n";
        }
    }
}
