<?php
namespace Crawl\Controllers;

use Crawl\Lib\Database;

class MigrationController
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function run(): void
    {
        $pdo = $this->db->pdo();

        // Track applied migrations in a lightweight table
        $pdo->exec("CREATE TABLE IF NOT EXISTS schema_migrations (
            migration   VARCHAR(255) NOT NULL PRIMARY KEY,
            applied_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $applied = array_flip(array_column(
            $this->db->fetchAll("SELECT migration FROM schema_migrations"),
            'migration'
        ));

        $dir   = ROOT_PATH . '/database/migrations';
        $files = glob($dir . '/*.sql') ?: [];
        sort($files);

        $results = [];

        foreach ($files as $file) {
            $name = basename($file);

            if (isset($applied[$name])) {
                $results[] = ['file' => $name, 'status' => 'skipped', 'msg' => 'Already applied — skipped.'];
                continue;
            }

            $sql        = file_get_contents($file);
            $statements = $this->splitStatements($sql);
            $errors     = [];
            $warnings   = [];

            foreach ($statements as $stmt) {
                try {
                    $pdo->exec($stmt);
                } catch (\PDOException $e) {
                    $msg  = $e->getMessage();
                    $code = (int)($e->errorInfo[1] ?? 0);

                    // 1060 = Duplicate column name  (MySQL/MariaDB)
                    // 1061 = Duplicate key name
                    // 1050 = Table already exists
                    if (in_array($code, [1050, 1060, 1061], true)) {
                        $warnings[] = 'Already exists (skipped): ' . $this->excerpt($msg);
                    } else {
                        $errors[] = $this->excerpt($msg);
                    }
                }
            }

            if (empty($errors)) {
                $pdo->exec("INSERT IGNORE INTO schema_migrations (migration) VALUES ('" . addslashes($name) . "')");
                $status = empty($warnings) ? 'applied' : 'applied_warn';
                $msg    = empty($warnings)
                    ? 'Applied successfully (' . count($statements) . ' statement' . (count($statements) !== 1 ? 's' : '') . ')'
                    : 'Applied with warnings: ' . implode('; ', $warnings);
            } else {
                $status = 'error';
                $msg    = implode('<br>', array_map('htmlspecialchars', $errors));
                if (!empty($warnings)) {
                    $msg .= '<br><span class="text-warning">Warnings: ' . implode('; ', $warnings) . '</span>';
                }
            }

            $results[] = ['file' => $name, 'status' => $status, 'msg' => $msg];
        }

        $this->render($results);
    }

    // ── Helpers ──────────────────────────────────────────────────────────

    private function splitStatements(string $sql): array
    {
        // Strip single-line comments, then split on ;
        $lines = array_map('rtrim', explode("\n", $sql));
        $clean = implode("\n", array_filter($lines, fn($l) => !str_starts_with(ltrim($l), '--')));

        return array_filter(
            array_map('trim', explode(';', $clean)),
            fn($s) => $s !== ''
        );
    }

    private function excerpt(string $msg, int $len = 200): string
    {
        return mb_strlen($msg) > $len ? mb_substr($msg, 0, $len) . '…' : $msg;
    }

    private function render(array $results): void
    {
        $applied = count(array_filter($results, fn($r) => in_array($r['status'], ['applied', 'applied_warn'])));
        $skipped = count(array_filter($results, fn($r) => $r['status'] === 'skipped'));
        $errors  = count(array_filter($results, fn($r) => $r['status'] === 'error'));
        $total   = count($results);

        $bannerColor = $errors > 0 ? '#dc2626' : ($applied > 0 ? '#16a34a' : '#2563eb');
        $bannerText  = $errors > 0
            ? "{$errors} migration(s) failed"
            : ($applied > 0 ? "{$applied} migration(s) applied" : 'All migrations already applied');

        $rows = '';
        foreach ($results as $r) {
            [$icon, $badge, $rowClass] = match($r['status']) {
                'applied'      => ['✓', 'bg-success', ''],
                'applied_warn' => ['⚠', 'bg-warning text-dark', ''],
                'skipped'      => ['–', 'bg-secondary', 'opacity-60'],
                default        => ['✗', 'bg-danger', 'table-danger'],
            };
            $rows .= "
              <tr class=\"{$rowClass}\">
                <td class=\"text-center\"><span class=\"badge {$badge}\">{$icon}</span></td>
                <td class=\"font-monospace small\">" . htmlspecialchars($r['file']) . "</td>
                <td class=\"small\">{$r['msg']}</td>
              </tr>";
        }

        $bannerEmoji = $errors > 0 ? '❌' : ($applied > 0 ? '✅' : '✔️');

        // phpcs:disable
        http_response_code(200);
        header('Content-Type: text/html; charset=utf-8');
        echo <<<HTML
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Savit — Database Migrations</title>
  <link rel="preconnect" href="https://fonts.googleapis.com">
  <link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700&display=swap" rel="stylesheet">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    body          { font-family: 'Inter', sans-serif; background: #f1f5f9; }
    .savit-header { background: #0f172a; color: #fff; padding: 18px 32px; }
    .savit-logo   { font-weight: 700; font-size: 1.3rem; letter-spacing: -.02em; }
    .savit-logo span { background: linear-gradient(135deg,#6366f1,#2563eb); -webkit-background-clip:text; -webkit-text-fill-color:transparent; }
    .banner       { border-left: 4px solid {$bannerColor}; background: #fff; }
    .opacity-60   { opacity: .6; }
  </style>
</head>
<body>
  <div class="savit-header d-flex align-items-center justify-content-between">
    <div class="savit-logo"><span>Savit</span> <span style="font-weight:400;font-size:.9rem;opacity:.6;">Audit Engine</span></div>
    <span class="small opacity-75">Database Migration Runner</span>
  </div>

  <div class="container-lg py-4">
    <div class="card border-0 shadow-sm mb-4 banner p-4">
      <div class="d-flex align-items-center gap-3">
        <div style="font-size:2rem;">{$bannerEmoji}</div>
        <div>
          <div class="fw-semibold fs-5">{$bannerText}</div>
          <div class="text-muted small">{$total} migration file(s) checked &nbsp;·&nbsp; {$applied} applied &nbsp;·&nbsp; {$skipped} skipped &nbsp;·&nbsp; {$errors} error(s)</div>
        </div>
      </div>
    </div>

    <div class="card border-0 shadow-sm">
      <div class="card-header fw-semibold d-flex justify-content-between align-items-center">
        Migration Results
        <a href="?" class="btn btn-sm btn-outline-primary">↺ Re-run</a>
      </div>
      <div class="table-responsive">
        <table class="table table-hover align-middle mb-0">
          <thead class="table-light">
            <tr>
              <th style="width:50px;">Status</th>
              <th style="width:280px;">File</th>
              <th>Details</th>
            </tr>
          </thead>
          <tbody>
            {$rows}
          </tbody>
        </table>
      </div>
    </div>

    <p class="text-muted small text-center mt-4">
      ⚠ Remove or protect <code>/migrate</code> before deploying to production.
    </p>
  </div>
</body>
</html>
HTML;
        // phpcs:enable
    }
}
