<?php
/**
 * OTIS — Safe Migration Runner
 * Executes final_safe_migration.sql and logs every statement result.
 *
 * Usage (CLI):   php database/run_safe_migration.php
 * Usage (Web):   http://localhost/otis/database/run_safe_migration.php?token=YOUR_TOKEN
 *
 * Security: Only runs if a secret token matches OR running from CLI.
 */

// ── Security Gate ────────────────────────────────────────────────
define('MIGRATION_SECRET', 'otis-migrate-' . sha1('otis-secure-2026'));

$isCli = (PHP_SAPI === 'cli');
$webToken = $_GET['token'] ?? '';

if (!$isCli && $webToken !== MIGRATION_SECRET) {
    http_response_code(403);
    exit("403 Forbidden — provide valid ?token=\n");
}

// ── Bootstrap ────────────────────────────────────────────────────
define('ROOT_PATH', realpath(__DIR__ . '/..'));
// Parse .env manually (parse_ini_file fails on comment lines with colons)
$dbConf = [];
foreach (file(ROOT_PATH . '/config/.env', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
    if (str_starts_with(trim($line), '#') || !str_contains($line, '=')) continue;
    [$k, $v] = explode('=', $line, 2);
    $dbConf[trim($k)] = trim($v);
}

$host   = $dbConf['DB_HOST'] ?? 'localhost';
$dbname = $dbConf['DB_NAME'] ?? 'otis';
$dbuser = $dbConf['DB_USER'] ?? 'root';
$dbpass = $dbConf['DB_PASS'] ?? '';

// ── Header ────────────────────────────────────────────────────────
$startTime = microtime(true);
$results   = [];
$errors    = [];
$warnings  = [];

function log_line(string $msg, string $type = 'ok'): void {
    global $results, $errors, $warnings;
    $ts = date('H:i:s');
    $line = "[{$ts}] [{$type}] {$msg}";
    if ($type === 'error') { $errors[] = $line; }
    elseif ($type === 'warn') { $warnings[] = $line; }
    else { $results[] = $line; }
}

if (!$isCli) {
    header('Content-Type: text/plain; charset=utf-8');
}

echo "╔══════════════════════════════════════════════╗\n";
echo "║  OTIS Safe Migration Runner                  ║\n";
echo "║  " . date('Y-m-d H:i:s') . "                       ║\n";
echo "╚══════════════════════════════════════════════╝\n\n";

// ── PDO Connection ────────────────────────────────────────────────
try {
    $pdo = new PDO(
        "mysql:host={$host};dbname={$dbname};charset=utf8mb4",
        $dbuser,
        $dbpass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_MULTI_STATEMENTS => true,
        ]
    );
    echo "✅ Connected to database: {$dbname}\n\n";
} catch (PDOException $e) {
    echo "❌ CONNECTION FAILED: " . $e->getMessage() . "\n";
    exit(1);
}

// ── Load SQL File ─────────────────────────────────────────────────
$sqlFile = __DIR__ . '/final_safe_migration.sql';
if (!file_exists($sqlFile)) {
    echo "❌ Migration file not found: {$sqlFile}\n";
    exit(1);
}

$rawSql = file_get_contents($sqlFile);
echo "📄 Loaded: " . basename($sqlFile) . " (" . number_format(strlen($rawSql)) . " bytes)\n\n";

// ── Split into individual statements ─────────────────────────────
// Handle DELIMITER ;; blocks specially
$statements  = [];
$buffer      = '';
$inDelimiter = false;
$delimiter   = ';';

foreach(explode("\n", $rawSql) as $line) {
    $trimmed = trim($line);

    // Skip comments
    if (str_starts_with($trimmed, '--') || str_starts_with($trimmed, '#')) continue;

    if (preg_match('/^DELIMITER\s+(.+)$/i', $trimmed, $m)) {
        if ($buffer !== '') {
            $statements[] = trim($buffer);
            $buffer = '';
        }
        $delimiter = trim($m[1]);
        continue;
    }

    $buffer .= $line . "\n";

    if ($delimiter === ';;' && str_ends_with(rtrim($line), ';;')) {
        $stmt = rtrim(trim($buffer), ';');
        $statements[] = $stmt;
        $buffer = '';
    } elseif ($delimiter === ';' && str_ends_with(rtrim($line), ';') &&
              !preg_match('/^(CREATE|ALTER|DROP)\s+PROCEDURE/i', trim($buffer))) {
        $stmt = trim($buffer);
        if ($stmt !== ';') $statements[] = $stmt;
        $buffer = '';
    }
}
if (trim($buffer) !== '') $statements[] = trim($buffer);

echo "📊 Statements to execute: " . count($statements) . "\n\n";

// ── Execute Each Statement ────────────────────────────────────────
$created  = 0;
$altered  = 0;
$inserted = 0;
$skipped  = 0;
$failed   = 0;

foreach ($statements as $i => $stmt) {
    $stmt = trim($stmt);
    if (empty($stmt) || $stmt === ';' || $stmt === ';;') { $skipped++; continue; }

    // Detect type for display
    $upper = strtoupper(substr($stmt, 0, 30));
    if (str_starts_with($upper, 'SET ') || str_starts_with($upper, 'USE ')) {
        $skipped++;
        continue; // skip session-level statements silently
    }

    $type = 'EXEC';
    if (str_contains($upper, 'CREATE TABLE')) $type = 'CREATE TABLE';
    elseif (str_contains($upper, 'ALTER TABLE')) $type = 'ALTER TABLE';
    elseif (str_contains($upper, 'INSERT')) $type = 'INSERT';
    elseif (str_contains($upper, 'CREATE PROCEDURE')) $type = 'CREATE PROC';
    elseif (str_contains($upper, 'CALL ')) $type = 'CALL';
    elseif (str_contains($upper, 'DROP PROCEDURE')) $type = 'DROP PROC';

    $preview = substr(preg_replace('/\s+/', ' ', $stmt), 0, 80);

    try {
        $pdo->exec($stmt);

        if ($type === 'CREATE TABLE') { $created++; $status = '✅ OK'; }
        elseif ($type === 'ALTER TABLE') { $altered++; $status = '🔧 OK'; }
        elseif ($type === 'INSERT') { $inserted++; $status = '📥 OK'; }
        else { $status = '✔  OK'; }

        log_line("{$type}: {$preview}...", 'ok');
        echo "{$status} | {$type} | {$preview}\n";

    } catch (PDOException $e) {
        $errMsg = $e->getMessage();

        // These are harmless — already exists
        $harmless = [
            'already exists',
            'Duplicate entry',
            'Duplicate column name',
            "doesn't exist" // DROP PROCEDURE IF NOT EXISTS on empty db
        ];

        $isHarmless = false;
        foreach ($harmless as $h) {
            if (stripos($errMsg, $h) !== false) { $isHarmless = true; break; }
        }

        if ($isHarmless) {
            $skipped++;
            echo "⏭  SKIP | {$type}: {$preview}\n";
            log_line("SKIPPED (already exists): {$preview}", 'warn');
        } else {
            $failed++;
            echo "❌ FAIL | {$type}: {$preview}\n   → {$errMsg}\n";
            log_line("ERROR: {$errMsg} | SQL: {$preview}", 'error');
        }
    }
}

// ── INTEGRITY CHECK ───────────────────────────────────────────────
echo "\n══════════════════════════════════════════════\n";
echo "  INTEGRITY CHECKS\n";
echo "══════════════════════════════════════════════\n";

$criticalTables = [
    'users', 'todo_tasks', 'habits', 'habit_logs',
    'finance_expenses', 'health_fasts', 'health_profile',
    'spiritual_daily_logs', 'notifications',
    'user_xp', 'badges', 'user_badges',
    'automation_log', 'intelligence_cache',
    'vault_items', 'vault_security',
];

$missingTables = [];
foreach ($criticalTables as $tbl) {
    $exists = $pdo->query("SHOW TABLES LIKE '{$tbl}'")->rowCount() > 0;
    if ($exists) {
        echo "✅ Table exists: {$tbl}\n";
    } else {
        echo "❌ MISSING TABLE: {$tbl}\n";
        $missingTables[] = $tbl;
    }
}

// Orphan check
echo "\n── Orphan Records Check ──\n";
$orphanChecks = [
    ['habit_logs', 'habit_id', 'habits', 'id'],
    ['user_xp', 'user_id', 'users', 'id'],
    ['user_badges', 'user_id', 'users', 'id'],
];
foreach ($orphanChecks as [$childTbl, $childCol, $parentTbl, $parentCol]) {
    try {
        $count = $pdo->query(
            "SELECT COUNT(*) FROM `{$childTbl}` c
             LEFT JOIN `{$parentTbl}` p ON c.`{$childCol}` = p.`{$parentCol}`
             WHERE p.`{$parentCol}` IS NULL"
        )->fetchColumn();
        if ($count > 0) {
            echo "⚠️  Orphans in {$childTbl}: {$count} records\n";
        } else {
            echo "✅ No orphans: {$childTbl}\n";
        }
    } catch (PDOException $e) {
        echo "⏭  Skip orphan check {$childTbl}: " . $e->getMessage() . "\n";
    }
}

// ── Summary ───────────────────────────────────────────────────────
$elapsed = round(microtime(true) - $startTime, 2);
echo "\n╔══════════════════════════════════════════════╗\n";
echo "║  MIGRATION SUMMARY                           ║\n";
echo "╠══════════════════════════════════════════════╣\n";
echo "║  Tables created/verified : {$created}\n";
echo "║  Columns altered         : {$altered}\n";
echo "║  Seed rows inserted      : {$inserted}\n";
echo "║  Skipped (already exist) : {$skipped}\n";
echo "║  ERRORS                  : {$failed}\n";
echo "║  Missing tables          : " . count($missingTables) . "\n";
echo "║  Time elapsed            : {$elapsed}s\n";
echo "╚══════════════════════════════════════════════╝\n";

if ($failed === 0 && count($missingTables) === 0) {
    echo "\n🚀 MIGRATION COMPLETE — System is production-ready!\n";
} else {
    echo "\n⚠️  MIGRATION FINISHED WITH ISSUES — Review errors above.\n";
}

// ── Write log ─────────────────────────────────────────────────────
$logFile = ROOT_PATH . '/storage/logs/migration_' . date('Ymd_His') . '.log';
if (is_dir(dirname($logFile))) {
    file_put_contents($logFile, implode("\n", array_merge($results, $warnings, $errors)));
    echo "\n📝 Full log: {$logFile}\n";
}
