<?php
/**
 * Script de migração de banco de dados.
 * Pode ser executado múltiplas vezes sem risco (idempotente).
 * docker compose exec app php migrate_db.php
 */
require_once __DIR__ . '/db.php';
$pdo = db();

$migrations = [];

// ── Índices de performance ──────────────────────────────────────────────────

$migrations[] = ["Índice ping_logs.checked_at",
    "CREATE INDEX IF NOT EXISTS idx_ping_logs_checked_at ON ping_logs(checked_at)"];

$migrations[] = ["Índice ping_logs.status",
    "CREATE INDEX IF NOT EXISTS idx_ping_logs_status ON ping_logs(status)"];

$migrations[] = ["Índice ping_logs.(monitored_ip_id, checked_at)",
    "CREATE INDEX IF NOT EXISTS idx_ping_logs_ip_at ON ping_logs(monitored_ip_id, checked_at)"];

$migrations[] = ["Índice alerts_sent.sent_at",
    "CREATE INDEX IF NOT EXISTS idx_alerts_sent_at ON alerts_sent(sent_at)"];

$migrations[] = ["Índice monitored_ips.last_status",
    "CREATE INDEX IF NOT EXISTS idx_monitored_ips_last_status ON monitored_ips(last_status)"];

$migrations[] = ["Índice monitored_ips.(user_id, last_status)",
    "CREATE INDEX IF NOT EXISTS idx_monitored_ips_user_status ON monitored_ips(user_id, last_status)"];

// ── Coluna timezone em users ────────────────────────────────────────────────

$migrations[] = ["Coluna users.timezone",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS timezone VARCHAR(64) NOT NULL DEFAULT 'America/Sao_Paulo'"];

// ── Tabela password_resets ──────────────────────────────────────────────────

$migrations[] = ["Tabela password_resets", "
    CREATE TABLE IF NOT EXISTS password_resets (
        id         SERIAL PRIMARY KEY,
        email      VARCHAR(150) NOT NULL,
        token      VARCHAR(64) NOT NULL UNIQUE,
        expires_at TIMESTAMPTZ NOT NULL,
        used       BOOLEAN NOT NULL DEFAULT FALSE
    )"];

// ── Novas colunas e tabelas (melhorias #39–#50) ──────────────────────────────

$migrations[] = ["Coluna monitored_ips.latency_alert_ms (#45)",
    "ALTER TABLE monitored_ips ADD COLUMN IF NOT EXISTS latency_alert_ms INT DEFAULT NULL"];

$migrations[] = ["Coluna alert_channels.notify_recovery (#35)",
    "ALTER TABLE alert_channels ADD COLUMN IF NOT EXISTS notify_recovery BOOLEAN NOT NULL DEFAULT TRUE"];

$migrations[] = ["Coluna users.totp_secret (#33)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS totp_secret VARCHAR(64) NULL"];

$migrations[] = ["Coluna users.totp_enabled (#33)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS totp_enabled BOOLEAN NOT NULL DEFAULT FALSE"];

$migrations[] = ["Coluna users.email_verified (#46)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN NOT NULL DEFAULT TRUE"];

$migrations[] = ["Coluna users.email_verify_token (#46)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verify_token VARCHAR(64) NULL"];

$migrations[] = ["Coluna status_pages.access_password (#41)",
    "ALTER TABLE status_pages ADD COLUMN IF NOT EXISTS access_password VARCHAR(255) NULL"];

$migrations[] = ["Tabela user_sessions (#48)", "
    CREATE TABLE IF NOT EXISTS user_sessions (
        id         BIGSERIAL PRIMARY KEY,
        user_id    INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        ip_address VARCHAR(64) NOT NULL,
        user_agent VARCHAR(255) NOT NULL DEFAULT '',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )"];

$migrations[] = ["Índice user_sessions.user_id (#48)",
    "CREATE INDEX IF NOT EXISTS idx_user_sessions_user ON user_sessions(user_id, created_at DESC)"];

$migrations[] = ["Tabela webhook_retries (#39)", "
    CREATE TABLE IF NOT EXISTS webhook_retries (
        id            SERIAL PRIMARY KEY,
        channel_id    INT NULL REFERENCES alert_channels(id) ON DELETE SET NULL,
        ip_id         INT NOT NULL,
        target        VARCHAR(255) NOT NULL,
        payload       TEXT NOT NULL,
        attempts      INT NOT NULL DEFAULT 0,
        next_retry_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )"];

// ── Executar ────────────────────────────────────────────────────────────────

$ok = 0; $fail = 0;
foreach ($migrations as [$label, $sql]) {
    try {
        $pdo->exec(trim($sql));
        echo "[OK]   {$label}\n";
        $ok++;
    } catch (Throwable $e) {
        echo "[ERRO] {$label}: " . $e->getMessage() . "\n";
        $fail++;
    }
}

echo "\n{$ok} OK, {$fail} com erro.\n";
