<?php
/**
 * Inicialização do banco de dados para o módulo MikroTik NMS (PostgreSQL + TimescaleDB)
 * Execute: php mikrotik/init_mikrotik_db.php  (ou acesse via browser)
 */
require_once __DIR__ . '/../db.php';
require_once __DIR__ . '/../config.php';

$pdo = db();

// Habilita TimescaleDB (idempotente)
$pdo->exec("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE");

$pdo->exec("
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,

    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()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_nms_user   ON nms_devices(user_id)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_nms_status ON nms_devices(last_status)");

$pdo->exec("
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,
    last_status     VARCHAR(10) NOT NULL DEFAULT 'unknown' CHECK (last_status IN ('up','down','unknown')),
    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)
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_nms_iface_device ON nms_interfaces(device_id)");

// nms_bandwidth_history — hypertable TimescaleDB (particionado por recorded_at)
$pdo->exec("
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)
)
");
try {
    $pdo->exec("SELECT create_hypertable('nms_bandwidth_history', 'recorded_at', chunk_time_interval => INTERVAL '1 day', if_not_exists => TRUE)");
} catch (PDOException $e) {}
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_nms_bw_iface_time ON nms_bandwidth_history(interface_id, recorded_at DESC)");

// Compressão e retenção para bandwidth history (7 dias raw, manter 90 dias)
try {
    $pdo->exec("ALTER TABLE nms_bandwidth_history SET (
        timescaledb.compress,
        timescaledb.compress_orderby   = 'recorded_at DESC',
        timescaledb.compress_segmentby = 'interface_id'
    )");
    $pdo->exec("SELECT add_compression_policy('nms_bandwidth_history', INTERVAL '7 days', if_not_exists => TRUE)");
    $pdo->exec("SELECT add_retention_policy('nms_bandwidth_history', INTERVAL '90 days', if_not_exists => TRUE)");
} catch (PDOException $e) {}

// nms_device_metrics — hypertable TimescaleDB
$pdo->exec("
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)
)
");
try {
    $pdo->exec("SELECT create_hypertable('nms_device_metrics', 'recorded_at', chunk_time_interval => INTERVAL '1 day', if_not_exists => TRUE)");
} catch (PDOException $e) {}
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_nms_metrics_time ON nms_device_metrics(device_id, recorded_at DESC)");

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

// nms_pppoe_clients — snapshot por dispositivo (não é time-series puro, sem hypertable)
$pdo->exec("
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()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_nms_pppoe_snap ON nms_pppoe_clients(device_id, snapshot_at DESC)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_nms_pppoe_user ON nms_pppoe_clients(username)");

// Migração segura: adiciona colunas novas em instalações existentes
foreach ([
    "ALTER TABLE nms_devices ADD COLUMN IF NOT EXISTS snmp_auth_proto VARCHAR(5) NOT NULL DEFAULT 'MD5'",
    "ALTER TABLE nms_devices ADD COLUMN IF NOT EXISTS snmp_priv_proto VARCHAR(5) NOT NULL DEFAULT 'DES'",
    "ALTER TABLE nms_devices ADD COLUMN IF NOT EXISTS map_lat DECIMAL(10,7) NULL",
    "ALTER TABLE nms_devices ADD COLUMN IF NOT EXISTS map_lng DECIMAL(10,7) NULL",
] as $sql) {
    try { $pdo->exec($sql); } catch (PDOException $e) { /* coluna já existe */ }
}

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