<?php
/**
 * setup_db.php — Script único e idempotente de inicialização do banco
 * Cria todas as tabelas, colunas e índices do MonitorPing.
 * Pode ser executado quantas vezes quiser: nunca destrói dados.
 *
 * Executado automaticamente pelo docker-entrypoint.sh na inicialização.
 */
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/config.php';

$pdo = db();
$ok = 0; $warn = 0;

function run(PDO $pdo, string $label, string $sql): void {
    global $ok, $warn;
    try {
        $pdo->exec(trim($sql));
        echo "[OK]  $label\n";
        $ok++;
    } catch (Throwable $e) {
        echo "[AVS] $label: " . $e->getMessage() . "\n";
        $warn++;
    }
}

// ════════════════════════════════════════════════════════════════════════════
// 0. EXTENSÕES
// ════════════════════════════════════════════════════════════════════════════
run($pdo, "Extensão timescaledb",
    "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE");

// ════════════════════════════════════════════════════════════════════════════
// 1. TABELAS PRINCIPAIS
// ════════════════════════════════════════════════════════════════════════════
run($pdo, "Tabela users", "
CREATE TABLE IF NOT EXISTS users (
    id                 SERIAL PRIMARY KEY,
    name               VARCHAR(100) NOT NULL,
    email              VARCHAR(150) NOT NULL UNIQUE,
    password_hash      VARCHAR(255) NOT NULL,
    role               VARCHAR(10) NOT NULL DEFAULT 'user' CHECK (role IN ('admin','user')),
    active             BOOLEAN NOT NULL DEFAULT TRUE,
    timezone           VARCHAR(64) NOT NULL DEFAULT 'America/Sao_Paulo',
    totp_secret        VARCHAR(64) NULL,
    totp_enabled       BOOLEAN NOT NULL DEFAULT FALSE,
    email_verified     BOOLEAN NOT NULL DEFAULT TRUE,
    email_verify_token VARCHAR(64) NULL,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");

run($pdo, "Tabela plans", "
CREATE TABLE IF NOT EXISTS plans (
    id                   SERIAL PRIMARY KEY,
    name                 VARCHAR(100) NOT NULL,
    max_ips              INT NOT NULL,
    min_interval_seconds INT NOT NULL,
    allow_email          BOOLEAN NOT NULL DEFAULT TRUE,
    allow_webhook        BOOLEAN NOT NULL DEFAULT FALSE,
    allow_sms            BOOLEAN NOT NULL DEFAULT FALSE,
    allow_telegram       BOOLEAN NOT NULL DEFAULT FALSE,
    allow_whatsapp       BOOLEAN NOT NULL DEFAULT FALSE,
    price                DECIMAL(10,2) NOT NULL DEFAULT 0,
    active               BOOLEAN NOT NULL DEFAULT TRUE
)");

run($pdo, "Tabela subscriptions", "
CREATE TABLE IF NOT EXISTS subscriptions (
    id         SERIAL PRIMARY KEY,
    user_id    INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    plan_id    INT NOT NULL REFERENCES plans(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NULL
)");

run($pdo, "Tabela monitored_ips", "
CREATE TABLE IF NOT EXISTS monitored_ips (
    id                  SERIAL PRIMARY KEY,
    user_id             INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    label               VARCHAR(150) NOT NULL,
    host                VARCHAR(255) NOT NULL,
    check_method        VARCHAR(10) NOT NULL DEFAULT 'auto' CHECK (check_method IN ('auto','icmp','tcp')),
    interval_seconds    INT NOT NULL,
    timeout_ms          INT NOT NULL DEFAULT 1000,
    alert_email         VARCHAR(150) DEFAULT NULL,
    latency_alert_ms    INT DEFAULT NULL,
    last_offline_at     TIMESTAMPTZ NULL,
    ssl_expires_at      DATE NULL,
    ssl_check_enabled   BOOLEAN NOT NULL DEFAULT FALSE,
    ssl_last_checked_at TIMESTAMPTZ NULL,
    active              BOOLEAN NOT NULL DEFAULT TRUE,
    last_status         VARCHAR(10) NOT NULL DEFAULT 'unknown' CHECK (last_status IN ('online','offline','unknown')),
    last_latency_ms     INT DEFAULT NULL,
    last_check_at       TIMESTAMPTZ NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");
run($pdo, "Índice monitored_ips.user_id",
    "CREATE INDEX IF NOT EXISTS idx_monitored_ips_user ON monitored_ips(user_id)");
run($pdo, "Índice monitored_ips.(user_id, last_status)",
    "CREATE INDEX IF NOT EXISTS idx_monitored_ips_user_status ON monitored_ips(user_id, last_status)");
run($pdo, "Índice monitored_ips.last_status",
    "CREATE INDEX IF NOT EXISTS idx_monitored_ips_last_status ON monitored_ips(last_status)");

run($pdo, "Tabela ping_logs", "
CREATE TABLE IF NOT EXISTS ping_logs (
    id              BIGSERIAL,
    monitored_ip_id INT NOT NULL REFERENCES monitored_ips(id) ON DELETE CASCADE,
    status          VARCHAR(10) NOT NULL CHECK (status IN ('online','offline')),
    latency_ms      INT DEFAULT NULL,
    checked_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, checked_at)
)");
run($pdo, "Hypertable ping_logs",
    "SELECT create_hypertable('ping_logs','checked_at',chunk_time_interval => INTERVAL '7 days',if_not_exists => TRUE)");
run($pdo, "Índice ping_logs.(ip, checked_at)",
    "CREATE INDEX IF NOT EXISTS idx_ping_logs_ip_time ON ping_logs(monitored_ip_id, checked_at DESC)");
run($pdo, "Índice ping_logs.status",
    "CREATE INDEX IF NOT EXISTS idx_ping_logs_status ON ping_logs(status)");
run($pdo, "Índice ping_logs.checked_at",
    "CREATE INDEX IF NOT EXISTS idx_ping_logs_checked_at ON ping_logs(checked_at DESC)");
run($pdo, "Compressão ping_logs",
    "ALTER TABLE ping_logs SET (timescaledb.compress, timescaledb.compress_orderby='checked_at DESC', timescaledb.compress_segmentby='monitored_ip_id')");
run($pdo, "Política compressão ping_logs",
    "SELECT add_compression_policy('ping_logs', INTERVAL '7 days', if_not_exists => TRUE)");
run($pdo, "Política retenção ping_logs",
    "SELECT add_retention_policy('ping_logs', INTERVAL '30 days', if_not_exists => TRUE)");

run($pdo, "Tabela alert_channels", "
CREATE TABLE IF NOT EXISTS alert_channels (
    id                   SERIAL PRIMARY KEY,
    monitored_ip_id      INT NOT NULL REFERENCES monitored_ips(id) ON DELETE CASCADE,
    type                 VARCHAR(10) NOT NULL CHECK (type IN ('email','webhook','sms','whatsapp','telegram')),
    target               VARCHAR(255) NOT NULL,
    min_failures         INT NOT NULL DEFAULT 1,
    cooldown_minutes     INT NOT NULL DEFAULT 0,
    silence_start        TIME NULL,
    silence_end          TIME NULL,
    active               BOOLEAN NOT NULL DEFAULT TRUE,
    notify_recovery      BOOLEAN NOT NULL DEFAULT TRUE,
    consecutive_failures INT NOT NULL DEFAULT 0,
    last_sent_at         TIMESTAMPTZ NULL
)");
run($pdo, "Índice alert_channels.ip",
    "CREATE INDEX IF NOT EXISTS idx_alert_channels_ip ON alert_channels(monitored_ip_id)");

run($pdo, "Tabela alerts_sent", "
CREATE TABLE IF NOT EXISTS alerts_sent (
    id              BIGSERIAL PRIMARY KEY,
    monitored_ip_id INT NOT NULL REFERENCES monitored_ips(id) ON DELETE CASCADE,
    channel_id      INT NULL REFERENCES alert_channels(id) ON DELETE SET NULL,
    type            VARCHAR(10) NOT NULL CHECK (type IN ('email','webhook','sms','whatsapp','telegram')),
    status          VARCHAR(10) NOT NULL DEFAULT 'success' CHECK (status IN ('success','failed')),
    error_message   TEXT NULL,
    message         TEXT NOT NULL,
    sent_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    severity        VARCHAR(10) NOT NULL DEFAULT 'info' CHECK (severity IN ('info','warning','critical'))
)");
run($pdo, "Índice alerts_sent.ip",
    "CREATE INDEX IF NOT EXISTS idx_alerts_sent_ip ON alerts_sent(monitored_ip_id)");
run($pdo, "Índice alerts_sent.sent_at",
    "CREATE INDEX IF NOT EXISTS idx_alerts_sent_at ON alerts_sent(sent_at DESC)");

run($pdo, "Tabela system_settings", "
CREATE TABLE IF NOT EXISTS system_settings (
    id    SERIAL PRIMARY KEY,
    key   VARCHAR(100) NOT NULL UNIQUE,
    value TEXT NOT NULL
)");

run($pdo, "Tabela status_pages", "
CREATE TABLE IF NOT EXISTS status_pages (
    id              SERIAL PRIMARY KEY,
    user_id         INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    slug            VARCHAR(64) NOT NULL UNIQUE,
    title           VARCHAR(150) NOT NULL,
    description     VARCHAR(255) DEFAULT NULL,
    access_password VARCHAR(255) NULL,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");

run($pdo, "Tabela status_pages_ips", "
CREATE TABLE IF NOT EXISTS status_pages_ips (
    status_page_id  INT NOT NULL REFERENCES status_pages(id) ON DELETE CASCADE,
    monitored_ip_id INT NOT NULL REFERENCES monitored_ips(id) ON DELETE CASCADE,
    PRIMARY KEY (status_page_id, monitored_ip_id)
)");

run($pdo, "Tabela rate_limits", "
CREATE TABLE IF NOT EXISTS rate_limits (
    id           SERIAL PRIMARY KEY,
    ip           VARCHAR(64) NOT NULL,
    action       VARCHAR(50) NOT NULL,
    attempts     INT NOT NULL DEFAULT 1,
    last_attempt TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");
run($pdo, "Índice rate_limits.(ip,action)",
    "CREATE INDEX IF NOT EXISTS idx_rate_limits_ip_act ON rate_limits(ip, action)");

run($pdo, "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
)");

run($pdo, "Tabela user_sessions", "
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()
)");
run($pdo, "Índice user_sessions.user_id",
    "CREATE INDEX IF NOT EXISTS idx_user_sessions_user ON user_sessions(user_id, created_at DESC)");

run($pdo, "Tabela webhook_retries", "
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()
)");

run($pdo, "Tabela tags", "
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)
)");
run($pdo, "Índice tags.user_id",
    "CREATE INDEX IF NOT EXISTS idx_tags_user ON tags(user_id)");

run($pdo, "Tabela ip_tags", "
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)
)");

run($pdo, "Tabela maintenance_windows", "
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()
)");
run($pdo, "Índice maintenance_windows.user_id",
    "CREATE INDEX IF NOT EXISTS idx_maint_user ON maintenance_windows(user_id)");
run($pdo, "Índice maintenance_windows.ip_id",
    "CREATE INDEX IF NOT EXISTS idx_maint_ip ON maintenance_windows(monitored_ip_id)");
run($pdo, "Índice maintenance_windows.active",
    "CREATE INDEX IF NOT EXISTS idx_maint_active ON maintenance_windows(active, starts_at, ends_at)");

run($pdo, "Tabela api_tokens", "
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()
)");
run($pdo, "Índice api_tokens.user_id",
    "CREATE INDEX IF NOT EXISTS idx_api_tokens_user ON api_tokens(user_id)");
run($pdo, "Índice api_tokens.token",
    "CREATE INDEX IF NOT EXISTS idx_api_tokens_token ON api_tokens(token)");

run($pdo, "Tabela latency_incidents", "
CREATE TABLE IF NOT EXISTS latency_incidents (
    id                     BIGSERIAL PRIMARY KEY,
    monitored_ip_id        INT NOT NULL REFERENCES monitored_ips(id) ON DELETE CASCADE,
    threshold_ms           INT NOT NULL,
    started_at             TIMESTAMPTZ NOT NULL,
    recovered_at           TIMESTAMPTZ NULL,
    duration_seconds       INT NULL,
    peak_latency_ms        INT NOT NULL,
    checks_above_threshold INT NOT NULL DEFAULT 1,
    alert_sent             BOOLEAN NOT NULL DEFAULT FALSE
)");
run($pdo, "Índice latency_incidents.(ip, started_at)",
    "CREATE INDEX IF NOT EXISTS idx_latency_incidents_ip_at ON latency_incidents(monitored_ip_id, started_at DESC)");
run($pdo, "Índice latency_incidents.open",
    "CREATE INDEX IF NOT EXISTS idx_latency_incidents_open ON latency_incidents(monitored_ip_id) WHERE recovered_at IS NULL");

run($pdo, "Tabela latency_events", "
CREATE TABLE IF NOT EXISTS latency_events (
    id              BIGSERIAL PRIMARY KEY,
    monitored_ip_id INT NOT NULL REFERENCES monitored_ips(id) ON DELETE CASCADE,
    latency_ms      INT NOT NULL,
    threshold_ms    INT NOT NULL,
    occurred_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");
run($pdo, "Índice latency_events.(ip, occurred_at)",
    "CREATE INDEX IF NOT EXISTS idx_latency_events_ip_at ON latency_events(monitored_ip_id, occurred_at DESC)");

// ════════════════════════════════════════════════════════════════════════════
// 2. TIMESCALEDB — continuous aggregate
// ════════════════════════════════════════════════════════════════════════════
run($pdo, "View ping_stats_hourly", "
CREATE MATERIALIZED VIEW IF NOT EXISTS ping_stats_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', checked_at) AS bucket,
    monitored_ip_id,
    COUNT(*) AS total_checks,
    SUM(CASE WHEN status = 'online' THEN 1 ELSE 0 END) AS online_count,
    AVG(latency_ms) AS avg_latency_ms
FROM ping_logs
GROUP BY bucket, monitored_ip_id
WITH NO DATA");
run($pdo, "Política refresh ping_stats_hourly",
    "SELECT add_continuous_aggregate_policy('ping_stats_hourly', start_offset => INTERVAL '3 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour', if_not_exists => TRUE)");

// ════════════════════════════════════════════════════════════════════════════
// 3. NMS MIKROTIK
// ════════════════════════════════════════════════════════════════════════════
run($pdo, "Tabela nms_devices", "
CREATE TABLE IF NOT EXISTS nms_devices (
    id              SERIAL PRIMARY KEY,
    user_id         INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name            VARCHAR(100) NOT NULL,
    hostname        VARCHAR(100) NOT NULL,
    type            VARCHAR(10) NOT NULL DEFAULT 'mikrotik' CHECK (type IN ('mikrotik','olt','switch','server','other')),
    location        VARCHAR(200) NULL,
    snmp_enabled    BOOLEAN NOT NULL DEFAULT TRUE,
    snmp_version    VARCHAR(5) NOT NULL DEFAULT '2c' CHECK (snmp_version IN ('2c','3')),
    snmp_community  VARCHAR(100) NULL DEFAULT 'public',
    snmp_user       VARCHAR(100) NULL,
    snmp_auth_proto VARCHAR(5) NOT NULL DEFAULT 'MD5' CHECK (snmp_auth_proto IN ('MD5','SHA')),
    snmp_priv_proto VARCHAR(5) NOT NULL DEFAULT 'DES' CHECK (snmp_priv_proto IN ('DES','AES')),
    snmp_auth_pass  VARCHAR(255) NULL,
    snmp_priv_pass  VARCHAR(255) NULL,
    snmp_port       SMALLINT NOT NULL DEFAULT 161,
    api_enabled     BOOLEAN NOT NULL DEFAULT FALSE,
    api_user        VARCHAR(100) NULL,
    api_password    VARCHAR(255) NULL,
    api_port        SMALLINT NOT NULL DEFAULT 80,
    api_use_ssl     BOOLEAN NOT NULL DEFAULT FALSE,
    map_lat         DECIMAL(10,7) NULL,
    map_lng         DECIMAL(10,7) NULL,
    last_status     VARCHAR(10) NOT NULL DEFAULT 'unknown' CHECK (last_status IN ('online','offline','unknown')),
    last_check_at   TIMESTAMPTZ NULL,
    last_cpu        SMALLINT NULL,
    last_memory_pct SMALLINT NULL,
    uptime_seconds  BIGINT NULL,
    poll_interval   SMALLINT NOT NULL DEFAULT 60,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");
run($pdo, "Índice nms_devices.user_id",
    "CREATE INDEX IF NOT EXISTS idx_nms_user ON nms_devices(user_id)");
run($pdo, "Índice nms_devices.last_status",
    "CREATE INDEX IF NOT EXISTS idx_nms_status ON nms_devices(last_status)");

run($pdo, "Tabela nms_interfaces", "
CREATE TABLE IF NOT EXISTS nms_interfaces (
    id              SERIAL PRIMARY KEY,
    device_id       INT NOT NULL REFERENCES nms_devices(id) ON DELETE CASCADE,
    if_index        INT NOT NULL,
    if_name         VARCHAR(100) NOT NULL,
    if_alias        VARCHAR(200) NULL,
    if_type         VARCHAR(50) NULL,
    if_speed_bps    BIGINT NULL,
    alert_on_down   BOOLEAN NOT NULL DEFAULT FALSE,
    alert_email     VARCHAR(150) NULL,
    last_status     VARCHAR(10) NOT NULL DEFAULT 'unknown' CHECK (last_status IN ('up','down','unknown')),
    last_alert_status  VARCHAR(10) NULL,
    last_alert_sent_at TIMESTAMPTZ NULL,
    last_in_bps     BIGINT NULL,
    last_out_bps    BIGINT NULL,
    last_in_octets  BIGINT NULL,
    last_out_octets BIGINT NULL,
    last_polled_at  TIMESTAMPTZ NULL,
    monitored       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (device_id, if_index)
)");
run($pdo, "Índice nms_interfaces.device_id",
    "CREATE INDEX IF NOT EXISTS idx_nms_iface_device ON nms_interfaces(device_id)");

run($pdo, "Tabela nms_interface_events", "
CREATE TABLE IF NOT EXISTS nms_interface_events (
    id               BIGSERIAL PRIMARY KEY,
    interface_id     INT NOT NULL REFERENCES nms_interfaces(id) ON DELETE CASCADE,
    started_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    recovered_at     TIMESTAMPTZ NULL,
    duration_seconds INT NULL,
    alert_sent       BOOLEAN NOT NULL DEFAULT FALSE
)");
run($pdo, "Índice nms_interface_events.(iface, started_at)",
    "CREATE INDEX IF NOT EXISTS idx_nie_iface ON nms_interface_events(interface_id, started_at DESC)");
run($pdo, "Índice nms_interface_events.open",
    "CREATE INDEX IF NOT EXISTS idx_nie_open ON nms_interface_events(interface_id, recovered_at) WHERE recovered_at IS NULL");

run($pdo, "Tabela nms_bandwidth_history", "
CREATE TABLE IF NOT EXISTS nms_bandwidth_history (
    id           BIGSERIAL,
    interface_id INT NOT NULL REFERENCES nms_interfaces(id) ON DELETE CASCADE,
    in_bps       BIGINT NOT NULL DEFAULT 0,
    out_bps      BIGINT NOT NULL DEFAULT 0,
    recorded_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, recorded_at)
)");
run($pdo, "Hypertable nms_bandwidth_history",
    "SELECT create_hypertable('nms_bandwidth_history','recorded_at',chunk_time_interval => INTERVAL '1 day',if_not_exists => TRUE)");
run($pdo, "Índice nms_bandwidth_history.(iface, time)",
    "CREATE INDEX IF NOT EXISTS idx_nms_bw_iface_time ON nms_bandwidth_history(interface_id, recorded_at DESC)");
run($pdo, "Compressão nms_bandwidth_history",
    "ALTER TABLE nms_bandwidth_history SET (timescaledb.compress, timescaledb.compress_orderby='recorded_at DESC', timescaledb.compress_segmentby='interface_id')");
run($pdo, "Política compressão nms_bandwidth_history",
    "SELECT add_compression_policy('nms_bandwidth_history', INTERVAL '7 days', if_not_exists => TRUE)");
run($pdo, "Política retenção nms_bandwidth_history",
    "SELECT add_retention_policy('nms_bandwidth_history', INTERVAL '90 days', if_not_exists => TRUE)");

run($pdo, "Tabela nms_device_metrics", "
CREATE TABLE IF NOT EXISTS nms_device_metrics (
    id             BIGSERIAL,
    device_id      INT NOT NULL REFERENCES nms_devices(id) ON DELETE CASCADE,
    cpu_pct        SMALLINT NULL,
    memory_pct     SMALLINT NULL,
    uptime_seconds BIGINT NULL,
    recorded_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, recorded_at)
)");
run($pdo, "Hypertable nms_device_metrics",
    "SELECT create_hypertable('nms_device_metrics','recorded_at',chunk_time_interval => INTERVAL '1 day',if_not_exists => TRUE)");
run($pdo, "Índice nms_device_metrics.(device, time)",
    "CREATE INDEX IF NOT EXISTS idx_nms_metrics_time ON nms_device_metrics(device_id, recorded_at DESC)");
run($pdo, "Compressão nms_device_metrics",
    "ALTER TABLE nms_device_metrics SET (timescaledb.compress, timescaledb.compress_orderby='recorded_at DESC', timescaledb.compress_segmentby='device_id')");
run($pdo, "Política compressão nms_device_metrics",
    "SELECT add_compression_policy('nms_device_metrics', INTERVAL '7 days', if_not_exists => TRUE)");
run($pdo, "Política retenção nms_device_metrics",
    "SELECT add_retention_policy('nms_device_metrics', INTERVAL '90 days', if_not_exists => TRUE)");

run($pdo, "Tabela nms_pppoe_clients", "
CREATE TABLE IF NOT EXISTS nms_pppoe_clients (
    id             BIGSERIAL PRIMARY KEY,
    device_id      INT NOT NULL REFERENCES nms_devices(id) ON DELETE CASCADE,
    username       VARCHAR(100) NOT NULL,
    caller_id      VARCHAR(100) NULL,
    address        VARCHAR(45) NULL,
    uptime_seconds BIGINT NULL,
    interface      VARCHAR(100) NULL,
    service        VARCHAR(100) NULL,
    rx_bytes       BIGINT NULL,
    tx_bytes       BIGINT NULL,
    snapshot_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");
run($pdo, "Índice nms_pppoe_clients.(device, snapshot)",
    "CREATE INDEX IF NOT EXISTS idx_nms_pppoe_snap ON nms_pppoe_clients(device_id, snapshot_at DESC)");
run($pdo, "Índice nms_pppoe_clients.username",
    "CREATE INDEX IF NOT EXISTS idx_nms_pppoe_user ON nms_pppoe_clients(username)");

// ════════════════════════════════════════════════════════════════════════════
// 4. UBIQUITI
// ════════════════════════════════════════════════════════════════════════════
run($pdo, "Tabela ubiquiti_panels", "
CREATE TABLE IF NOT EXISTS ubiquiti_panels (
    id                      SERIAL PRIMARY KEY,
    user_id                 INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name                    VARCHAR(100) NOT NULL,
    hostname                VARCHAR(100) NOT NULL,
    location                VARCHAR(200) NULL,
    snmp_community          VARCHAR(100) NOT NULL DEFAULT 'public',
    snmp_port               SMALLINT NOT NULL DEFAULT 161,
    alert_iface_speed       BOOLEAN NOT NULL DEFAULT TRUE,
    alert_iface_down        BOOLEAN NOT NULL DEFAULT TRUE,
    alert_clients           BOOLEAN NOT NULL DEFAULT TRUE,
    alert_client_mode       VARCHAR(10) NOT NULL DEFAULT 'absolute' CHECK (alert_client_mode IN ('absolute','percent')),
    alert_client_threshold  SMALLINT NOT NULL DEFAULT 3,
    alert_client_window     SMALLINT NOT NULL DEFAULT 2,
    alert_phone             VARCHAR(20) NULL,
    last_status             VARCHAR(10) NOT NULL DEFAULT 'unknown' CHECK (last_status IN ('online','offline','unknown')),
    last_client_count       SMALLINT NULL,
    last_iface_speed        SMALLINT NULL,
    last_iface_status       VARCHAR(5) NULL,
    last_ccq                SMALLINT NULL,
    last_signal             SMALLINT NULL,
    last_check_at           TIMESTAMPTZ NULL,
    prev_client_count       SMALLINT NULL,
    prev_iface_speed        SMALLINT NULL,
    alert_pending_since     TIMESTAMPTZ NULL,
    poll_interval           SMALLINT NOT NULL DEFAULT 60,
    active                  BOOLEAN NOT NULL DEFAULT TRUE,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");
run($pdo, "Índice ubiquiti_panels.user_id",
    "CREATE INDEX IF NOT EXISTS idx_ubnt_panels_user ON ubiquiti_panels(user_id)");
run($pdo, "Índice ubiquiti_panels.last_status",
    "CREATE INDEX IF NOT EXISTS idx_ubnt_panels_status ON ubiquiti_panels(last_status)");

run($pdo, "Tabela ubiquiti_client_logs", "
CREATE TABLE IF NOT EXISTS ubiquiti_client_logs (
    id           BIGSERIAL,
    panel_id     INT NOT NULL REFERENCES ubiquiti_panels(id) ON DELETE CASCADE,
    client_count SMALLINT NOT NULL DEFAULT 0,
    ccq          SMALLINT NULL,
    signal_dbm   SMALLINT NULL,
    recorded_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, recorded_at)
)");
run($pdo, "Hypertable ubiquiti_client_logs",
    "SELECT create_hypertable('ubiquiti_client_logs','recorded_at',chunk_time_interval => INTERVAL '1 day',if_not_exists => TRUE)");
run($pdo, "Índice ubiquiti_client_logs.(panel, time)",
    "CREATE INDEX IF NOT EXISTS idx_ubnt_clients_time ON ubiquiti_client_logs(panel_id, recorded_at DESC)");
run($pdo, "Compressão ubiquiti_client_logs",
    "ALTER TABLE ubiquiti_client_logs SET (timescaledb.compress, timescaledb.compress_orderby='recorded_at DESC', timescaledb.compress_segmentby='panel_id')");
run($pdo, "Política compressão ubiquiti_client_logs",
    "SELECT add_compression_policy('ubiquiti_client_logs', INTERVAL '7 days', if_not_exists => TRUE)");
run($pdo, "Política retenção ubiquiti_client_logs",
    "SELECT add_retention_policy('ubiquiti_client_logs', INTERVAL '90 days', if_not_exists => TRUE)");

run($pdo, "Tabela ubiquiti_link_logs", "
CREATE TABLE IF NOT EXISTS ubiquiti_link_logs (
    id           BIGSERIAL,
    panel_id     INT NOT NULL REFERENCES ubiquiti_panels(id) ON DELETE CASCADE,
    iface_speed  SMALLINT NULL,
    iface_status VARCHAR(5) NULL,
    recorded_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, recorded_at)
)");
run($pdo, "Hypertable ubiquiti_link_logs",
    "SELECT create_hypertable('ubiquiti_link_logs','recorded_at',chunk_time_interval => INTERVAL '1 day',if_not_exists => TRUE)");
run($pdo, "Índice ubiquiti_link_logs.(panel, time)",
    "CREATE INDEX IF NOT EXISTS idx_ubnt_link_time ON ubiquiti_link_logs(panel_id, recorded_at DESC)");
run($pdo, "Compressão ubiquiti_link_logs",
    "ALTER TABLE ubiquiti_link_logs SET (timescaledb.compress, timescaledb.compress_orderby='recorded_at DESC', timescaledb.compress_segmentby='panel_id')");
run($pdo, "Política compressão ubiquiti_link_logs",
    "SELECT add_compression_policy('ubiquiti_link_logs', INTERVAL '7 days', if_not_exists => TRUE)");
run($pdo, "Política retenção ubiquiti_link_logs",
    "SELECT add_retention_policy('ubiquiti_link_logs', INTERVAL '90 days', if_not_exists => TRUE)");

run($pdo, "Tabela ubiquiti_alerts", "
CREATE TABLE IF NOT EXISTS ubiquiti_alerts (
    id         SERIAL PRIMARY KEY,
    panel_id   INT NOT NULL REFERENCES ubiquiti_panels(id) ON DELETE CASCADE,
    user_id    INT NOT NULL,
    type       VARCHAR(20) NOT NULL CHECK (type IN ('iface_speed','iface_down','iface_up','client_drop','client_recover')),
    severity   VARCHAR(10) NOT NULL DEFAULT 'warning' CHECK (severity IN ('warning','critical')),
    details    JSONB NULL,
    notified   BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)");
run($pdo, "Índice ubiquiti_alerts.(panel, time)",
    "CREATE INDEX IF NOT EXISTS idx_ubnt_alerts_panel ON ubiquiti_alerts(panel_id, created_at DESC)");
run($pdo, "Índice ubiquiti_alerts.(user, time)",
    "CREATE INDEX IF NOT EXISTS idx_ubnt_alerts_user ON ubiquiti_alerts(user_id, created_at DESC)");

// ════════════════════════════════════════════════════════════════════════════
// 5. COLUNAS EXTRAS — retrocompatibilidade (instalações antigas)
// ════════════════════════════════════════════════════════════════════════════
$backfill = [
    "plans.allow_telegram"         => "ALTER TABLE plans ADD COLUMN IF NOT EXISTS allow_telegram BOOLEAN NOT NULL DEFAULT FALSE",
    "plans.allow_whatsapp"         => "ALTER TABLE plans ADD COLUMN IF NOT EXISTS allow_whatsapp BOOLEAN NOT NULL DEFAULT FALSE",
    "alert_channels.telegram check"=> "ALTER TABLE alert_channels DROP CONSTRAINT IF EXISTS alert_channels_type_check",
    "alert_channels.type check fix" => "ALTER TABLE alert_channels ADD CONSTRAINT alert_channels_type_check CHECK (type IN ('email','webhook','sms','whatsapp','telegram'))",
    "nms_devices.map_lat"          => "ALTER TABLE nms_devices ADD COLUMN IF NOT EXISTS map_lat DECIMAL(10,7) NULL",
    "nms_devices.map_lng"          => "ALTER TABLE nms_devices ADD COLUMN IF NOT EXISTS map_lng DECIMAL(10,7) NULL",
    "nms_interfaces.alert_on_down" => "ALTER TABLE nms_interfaces ADD COLUMN IF NOT EXISTS alert_on_down BOOLEAN NOT NULL DEFAULT FALSE",
    "nms_interfaces.alert_email"   => "ALTER TABLE nms_interfaces ADD COLUMN IF NOT EXISTS alert_email VARCHAR(150) NULL",
    "nms_interfaces.last_alert_status"  => "ALTER TABLE nms_interfaces ADD COLUMN IF NOT EXISTS last_alert_status VARCHAR(10) NULL",
    "nms_interfaces.last_alert_sent_at" => "ALTER TABLE nms_interfaces ADD COLUMN IF NOT EXISTS last_alert_sent_at TIMESTAMPTZ NULL",
];
foreach ($backfill as $label => $sql) {
    run($pdo, "Compat: $label", $sql);
}

// ════════════════════════════════════════════════════════════════════════════
// 6. DADOS INICIAIS
// ════════════════════════════════════════════════════════════════════════════

// Planos padrão
$countPlans = (int)$pdo->query("SELECT COUNT(*) FROM plans")->fetchColumn();
if ($countPlans === 0) {
    $pdo->exec("
        INSERT INTO plans (name, max_ips, min_interval_seconds, allow_email, allow_webhook, allow_sms, allow_telegram, allow_whatsapp, price)
        VALUES
        ('Padrão', 9999, 10, TRUE, TRUE, TRUE, TRUE, TRUE, 0.00)
    ");
    echo "[OK]  Perfil de acesso padrão criado\n";
    $ok++;
}

// Admin inicial
if (defined('INITIAL_ADMIN_EMAIL') && INITIAL_ADMIN_EMAIL) {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email = ?");
    $stmt->execute([INITIAL_ADMIN_EMAIL]);
    if ((int)$stmt->fetchColumn() === 0) {
        $hash = password_hash(INITIAL_ADMIN_PASS, PASSWORD_DEFAULT);
        $name = defined('INITIAL_ADMIN_NAME') && INITIAL_ADMIN_NAME ? INITIAL_ADMIN_NAME : 'Administrador';
        $pdo->prepare("INSERT INTO users (name, email, password_hash, role) VALUES (?, ?, ?, 'admin')")
            ->execute([$name, INITIAL_ADMIN_EMAIL, $hash]);

        // Vincula ao plano padrão
        $planId = (int)$pdo->query("SELECT id FROM plans LIMIT 1")->fetchColumn();
        $userId = (int)$pdo->lastInsertId();
        if ($planId && $userId) {
            $pdo->prepare("INSERT INTO subscriptions (user_id, plan_id) VALUES (?, ?)")
                ->execute([$userId, $planId]);
        }

        echo "[OK]  Admin inicial criado: " . INITIAL_ADMIN_EMAIL . "\n";
        $ok++;
    } else {
        echo "[OK]  Admin já existe.\n";
        $ok++;
    }
}

// ════════════════════════════════════════════════════════════════════════════
echo "\n✓ setup_db concluído — {$ok} OK, {$warn} avisos (colunas/tabelas já existentes)\n";
