<?php
/**
 * migrate_db2.php — Migrações para as novas funcionalidades do MonitorPing
 * Itens: Telegram, SSL, Janela de manutenção, Tags, API REST, Duração de queda
 */
require_once __DIR__ . '/db.php';
$pdo = db();

echo "Iniciando migrações...\n";

// ── 1. Coluna last_offline_at em monitored_ips (rastrear início de queda) ─────
try {
    $pdo->exec("ALTER TABLE monitored_ips ADD COLUMN IF NOT EXISTS last_offline_at TIMESTAMPTZ NULL");
    echo "✓ monitored_ips.last_offline_at\n";
} catch (PDOException $e) { echo "  (já existe ou erro: {$e->getMessage()})\n"; }

// ── 2. Coluna ssl_expires_at e ssl_check_enabled em monitored_ips ─────────────
try {
    $pdo->exec("ALTER TABLE monitored_ips ADD COLUMN IF NOT EXISTS ssl_expires_at DATE NULL");
    $pdo->exec("ALTER TABLE monitored_ips ADD COLUMN IF NOT EXISTS ssl_check_enabled BOOLEAN NOT NULL DEFAULT FALSE");
    $pdo->exec("ALTER TABLE monitored_ips ADD COLUMN IF NOT EXISTS ssl_last_checked_at TIMESTAMPTZ NULL");
    echo "✓ monitored_ips: colunas SSL\n";
} catch (PDOException $e) { echo "  (já existe ou erro: {$e->getMessage()})\n"; }

// ── 3. Adiciona 'telegram' ao tipo de canal (constraint update) ───────────────
try {
    $row = $pdo->query("
        SELECT pg_get_constraintdef(oid) AS def
        FROM pg_constraint
        WHERE conrelid = 'alert_channels'::regclass
          AND conname = 'alert_channels_type_check'
    ")->fetch();
    if ($row && strpos($row['def'], "'telegram'") === false) {
        $pdo->exec("
            ALTER TABLE alert_channels
              DROP CONSTRAINT IF EXISTS alert_channels_type_check,
              ADD CONSTRAINT alert_channels_type_check
                CHECK (type IN ('email','webhook','sms','whatsapp','telegram'))
        ");
        echo "✓ alert_channels: telegram adicionado\n";
    } else {
        echo "  (telegram já existe no constraint)\n";
    }
} catch (PDOException $e) { echo "  (erro constraint telegram: {$e->getMessage()})\n"; }

// ── 4. Tabela de tags ──────────────────────────────────────────────────────────
$pdo->exec("
CREATE TABLE IF NOT EXISTS tags (
    id         SERIAL PRIMARY KEY,
    user_id    INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name       VARCHAR(50) NOT NULL,
    color      VARCHAR(7) NOT NULL DEFAULT '#6366f1',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, name)
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_tags_user ON tags(user_id)");
echo "✓ tabela tags\n";

// ── 5. Tabela ip_tags (relação N:N entre IPs e tags) ──────────────────────────
$pdo->exec("
CREATE TABLE IF NOT EXISTS ip_tags (
    monitored_ip_id INT NOT NULL REFERENCES monitored_ips(id) ON DELETE CASCADE,
    tag_id          INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (monitored_ip_id, tag_id)
)
");
echo "✓ tabela ip_tags\n";

// ── 6. Tabela de janelas de manutenção ────────────────────────────────────────
$pdo->exec("
CREATE TABLE IF NOT EXISTS maintenance_windows (
    id              SERIAL PRIMARY KEY,
    user_id         INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    monitored_ip_id INT NULL REFERENCES monitored_ips(id) ON DELETE CASCADE,
    title           VARCHAR(150) NOT NULL DEFAULT 'Manutenção',
    starts_at       TIMESTAMPTZ NOT NULL,
    ends_at         TIMESTAMPTZ NOT NULL,
    all_ips         BOOLEAN NOT NULL DEFAULT FALSE,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_maint_user ON maintenance_windows(user_id)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_maint_ip ON maintenance_windows(monitored_ip_id)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_maint_active ON maintenance_windows(active, starts_at, ends_at)");
echo "✓ tabela maintenance_windows\n";

// ── 7. Tabela de tokens de API ────────────────────────────────────────────────
$pdo->exec("
CREATE TABLE IF NOT EXISTS api_tokens (
    id         SERIAL PRIMARY KEY,
    user_id    INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name       VARCHAR(100) NOT NULL,
    token      VARCHAR(64) NOT NULL UNIQUE,
    last_used_at TIMESTAMPTZ NULL,
    active     BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_api_tokens_user ON api_tokens(user_id)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_api_tokens_token ON api_tokens(token)");
echo "✓ tabela api_tokens\n";

// ── 8. Coluna telegram_bot_token nas system_settings ─────────────────────────
// (armazenado via set_system_setting(), sem DDL extra)
echo "✓ Telegram armazenado via system_settings (sem DDL)\n";

// ── 9. Colunas de alerta por interface (nms_interfaces) ──────────────────────
$ifCols = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='nms_interfaces'")->fetchAll(PDO::FETCH_COLUMN);
foreach ([
    'alert_on_down'      => "ALTER TABLE nms_interfaces ADD COLUMN alert_on_down BOOLEAN NOT NULL DEFAULT FALSE",
    'alert_email'        => "ALTER TABLE nms_interfaces ADD COLUMN alert_email VARCHAR(150) NULL",
    'last_alert_status'  => "ALTER TABLE nms_interfaces ADD COLUMN last_alert_status VARCHAR(10) NULL",
    'last_alert_sent_at' => "ALTER TABLE nms_interfaces ADD COLUMN last_alert_sent_at TIMESTAMPTZ NULL",
] as $col => $ddl) {
    if (!in_array($col, $ifCols)) {
        $pdo->exec($ddl);
        echo "✓ nms_interfaces.{$col} adicionada\n";
    } else {
        echo "✓ nms_interfaces.{$col} já existe\n";
    }
}

// ── 10. Colunas allow_telegram e allow_whatsapp na tabela plans ───────────────
$cols = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='plans'")->fetchAll(PDO::FETCH_COLUMN);
if (!in_array('allow_telegram', $cols)) {
    $pdo->exec("ALTER TABLE plans ADD COLUMN allow_telegram BOOLEAN NOT NULL DEFAULT FALSE");
    echo "✓ plans.allow_telegram adicionada\n";
} else {
    echo "✓ plans.allow_telegram já existe\n";
}
if (!in_array('allow_whatsapp', $cols)) {
    $pdo->exec("ALTER TABLE plans ADD COLUMN allow_whatsapp BOOLEAN NOT NULL DEFAULT FALSE");
    echo "✓ plans.allow_whatsapp adicionada\n";
} else {
    echo "✓ plans.allow_whatsapp já existe\n";
}

echo "\nMigrações concluídas com sucesso!\n";
