<?php
/**
 * Inicialização do banco de dados — módulo Ubiquiti
 * Execute: php ubiquiti/init_ubiquiti_db.php
 */
require_once __DIR__ . '/../db.php';
require_once __DIR__ . '/../config.php';

$pdo = db();
$pdo->exec("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE");

// ── Tabela principal de painéis ───────────────────────────────────────────────
$pdo->exec("
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,

    -- Alertas de interface (velocidade e link down)
    alert_iface_speed   BOOLEAN NOT NULL DEFAULT TRUE,
    alert_iface_down    BOOLEAN NOT NULL DEFAULT TRUE,

    -- Alertas de queda de clientes
    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,

    -- Destino WhatsApp para notificações
    alert_phone     VARCHAR(20) NULL,

    -- Estado atual (atualizado pelo poller)
    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 CHECK (last_iface_status IN ('up','down', NULL)),
    last_ccq            SMALLINT NULL,
    last_signal         SMALLINT NULL,
    last_check_at       TIMESTAMPTZ NULL,

    -- Estado anterior para detecção de mudança
    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()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ubnt_panels_user   ON ubiquiti_panels(user_id)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ubnt_panels_status ON ubiquiti_panels(last_status)");

// ── Histórico de clientes (hypertable) ───────────────────────────────────────
$pdo->exec("
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)
)
");
try {
    $pdo->exec("SELECT create_hypertable('ubiquiti_client_logs','recorded_at',
        chunk_time_interval => INTERVAL '1 day', if_not_exists => TRUE)");
} catch (PDOException $e) {}
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ubnt_clients_time ON ubiquiti_client_logs(panel_id, recorded_at DESC)");

try {
    $pdo->exec("ALTER TABLE ubiquiti_client_logs SET (
        timescaledb.compress,
        timescaledb.compress_orderby   = 'recorded_at DESC',
        timescaledb.compress_segmentby = 'panel_id'
    )");
    $pdo->exec("SELECT add_compression_policy('ubiquiti_client_logs', INTERVAL '7 days', if_not_exists => TRUE)");
    $pdo->exec("SELECT add_retention_policy('ubiquiti_client_logs', INTERVAL '90 days', if_not_exists => TRUE)");
} catch (PDOException $e) {}

// ── Histórico de link/velocidade (hypertable) ────────────────────────────────
$pdo->exec("
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)
)
");
try {
    $pdo->exec("SELECT create_hypertable('ubiquiti_link_logs','recorded_at',
        chunk_time_interval => INTERVAL '1 day', if_not_exists => TRUE)");
} catch (PDOException $e) {}
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ubnt_link_time ON ubiquiti_link_logs(panel_id, recorded_at DESC)");

try {
    $pdo->exec("ALTER TABLE ubiquiti_link_logs SET (
        timescaledb.compress,
        timescaledb.compress_orderby   = 'recorded_at DESC',
        timescaledb.compress_segmentby = 'panel_id'
    )");
    $pdo->exec("SELECT add_compression_policy('ubiquiti_link_logs', INTERVAL '7 days', if_not_exists => TRUE)");
    $pdo->exec("SELECT add_retention_policy('ubiquiti_link_logs', INTERVAL '90 days', if_not_exists => TRUE)");
} catch (PDOException $e) {}

// ── Tabela de alertas gerados ────────────────────────────────────────────────
$pdo->exec("
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()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ubnt_alerts_panel ON ubiquiti_alerts(panel_id, created_at DESC)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ubnt_alerts_user  ON ubiquiti_alerts(user_id, created_at DESC)");

echo "Tabelas do módulo Ubiquiti criadas com sucesso!\n";
