<?php
/**
 * CAE Migration Runner
 * Access: http://localhost/Content/migrate.php
 * Run once after updating the app. Safe to re-run.
 */
require __DIR__ . '/bootstrap.php';
use CAE\Lib\Database;

$pdo = Database::getInstance()->getPdo();
$results = [];

$migrations = glob(__DIR__ . '/database/migrations/*.sql');
sort($migrations);

foreach ($migrations as $file) {
    $name = basename($file);
    $sql  = file_get_contents($file);
    $sql  = ltrim($sql, "\xEF\xBB\xBF"); // strip BOM

    // Split on ; but only when not inside a string — simple approach: split on ";\n"
    $statements = array_filter(array_map('trim', preg_split('/;\s*\n/', $sql)));

    $fileErrors = [];
    $fileOk     = 0;
    foreach ($statements as $stmt) {
        // Strip leading comment lines, then skip if nothing remains
        $stripped = trim(preg_replace('/^[ \t]*--[^\n]*(\n|$)/m', '', $stmt));
        if (empty($stripped)) continue;
        try {
            $pdo->exec($stripped);
            $fileOk++;
        } catch (PDOException $e) {
            $code = (int)$e->errorInfo[1];
            // Ignore harmless codes: duplicate column (1060), already exists (1050/1826/1061)
            if (in_array($code, [1060, 1050, 1826, 1061])) {
                $fileOk++;
            } else {
                $fileErrors[] = $e->getMessage();
            }
        }
    }
    $results[] = ['file' => $name, 'ok' => $fileOk, 'errors' => $fileErrors];
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>CAE Migrate</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
</head>
<body class="bg-light">
<div class="container py-5" style="max-width:700px;">
  <h2 class="fw-bold mb-4"><i class="bi bi-arrow-repeat me-2"></i>CAE Migrations</h2>
  <?php foreach ($results as $r): ?>
  <div class="card border-0 shadow-sm mb-3">
    <div class="card-body">
      <div class="d-flex align-items-center gap-2 mb-2">
        <i class="bi bi-<?= empty($r['errors']) ? 'check-circle-fill text-success' : 'x-circle-fill text-danger' ?>"></i>
        <strong><?= htmlspecialchars($r['file']) ?></strong>
        <span class="text-muted small"><?= $r['ok'] ?> statements OK</span>
      </div>
      <?php if (!empty($r['errors'])): ?>
      <ul class="mb-0 small text-danger">
        <?php foreach ($r['errors'] as $err): ?>
        <li><?= htmlspecialchars($err) ?></li>
        <?php endforeach; ?>
      </ul>
      <?php endif; ?>
    </div>
  </div>
  <?php endforeach; ?>
  <a href="/" class="btn btn-primary">Back to App →</a>
</div>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css">
</body>
</html>
