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

$pdo = db();

$pdo->exec("
CREATE TABLE IF NOT EXISTS nms_devices (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    name            VARCHAR(100) NOT NULL,
    hostname        VARCHAR(100) NOT NULL,
    type            ENUM('mikrotik','olt','switch','server','other') NOT NULL DEFAULT 'mikrotik',
    location        VARCHAR(200) NULL,

    snmp_enabled    TINYINT(1) NOT NULL DEFAULT 1,
    snmp_version    ENUM('2c','3') NOT NULL DEFAULT '2c',
    snmp_community  VARCHAR(100) NULL DEFAULT 'public',
    snmp_user       VARCHAR(100) NULL,
    snmp_auth_proto ENUM('MD5','SHA') NOT NULL DEFAULT 'MD5',
    snmp_priv_proto ENUM('DES','AES') NOT NULL DEFAULT 'DES',
    snmp_auth_pass  VARCHAR(255) NULL,
    snmp_priv_pass  VARCHAR(255) NULL,
    snmp_port       SMALLINT UNSIGNED NOT NULL DEFAULT 161,

    api_enabled     TINYINT(1) NOT NULL DEFAULT 0,
    api_user        VARCHAR(100) NULL,
    api_password    VARCHAR(255) NULL,
    api_port        SMALLINT UNSIGNED NOT NULL DEFAULT 80,
    api_use_ssl     TINYINT(1) NOT NULL DEFAULT 0,

    last_status     ENUM('online','offline','unknown') NOT NULL DEFAULT 'unknown',
    last_check_at   DATETIME NULL,
    last_cpu        TINYINT UNSIGNED NULL,
    last_memory_pct TINYINT UNSIGNED NULL,
    uptime_seconds  BIGINT UNSIGNED NULL,

    poll_interval   SMALLINT UNSIGNED NOT NULL DEFAULT 60,
    active          TINYINT(1) NOT NULL DEFAULT 1,
    created_at      DATETIME NOT NULL DEFAULT NOW(),

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_nms_user (user_id),
    INDEX idx_nms_status (last_status)
)
");

$pdo->exec("
CREATE TABLE IF NOT EXISTS nms_interfaces (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    device_id       INT NOT NULL,
    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 UNSIGNED NULL,
    last_status     ENUM('up','down','unknown') NOT NULL DEFAULT 'unknown',
    last_in_bps     BIGINT UNSIGNED NULL,
    last_out_bps    BIGINT UNSIGNED NULL,
    last_in_octets  BIGINT UNSIGNED NULL,
    last_out_octets BIGINT UNSIGNED NULL,
    last_polled_at  DATETIME NULL,
    monitored       TINYINT(1) NOT NULL DEFAULT 1,
    created_at      DATETIME NOT NULL DEFAULT NOW(),
    FOREIGN KEY (device_id) REFERENCES nms_devices(id) ON DELETE CASCADE,
    UNIQUE KEY uq_device_if (device_id, if_index),
    INDEX idx_nms_iface_device (device_id)
)
");

$pdo->exec("
CREATE TABLE IF NOT EXISTS nms_bandwidth_history (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    interface_id    INT NOT NULL,
    in_bps          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    out_bps         BIGINT UNSIGNED NOT NULL DEFAULT 0,
    recorded_at     DATETIME NOT NULL,
    FOREIGN KEY (interface_id) REFERENCES nms_interfaces(id) ON DELETE CASCADE,
    INDEX idx_nms_bw_iface_time (interface_id, recorded_at)
)
");

$pdo->exec("
CREATE TABLE IF NOT EXISTS nms_device_metrics (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    device_id       INT NOT NULL,
    cpu_pct         TINYINT UNSIGNED NULL,
    memory_pct      TINYINT UNSIGNED NULL,
    uptime_seconds  BIGINT UNSIGNED NULL,
    recorded_at     DATETIME NOT NULL,
    FOREIGN KEY (device_id) REFERENCES nms_devices(id) ON DELETE CASCADE,
    INDEX idx_nms_metrics_time (device_id, recorded_at)
)
");

$pdo->exec("
CREATE TABLE IF NOT EXISTS nms_pppoe_clients (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    device_id       INT NOT NULL,
    username        VARCHAR(100) NOT NULL,
    caller_id       VARCHAR(100) NULL,
    address         VARCHAR(45) NULL,
    uptime_seconds  BIGINT UNSIGNED NULL,
    interface       VARCHAR(100) NULL,
    service         VARCHAR(100) NULL,
    rx_bytes        BIGINT UNSIGNED NULL,
    tx_bytes        BIGINT UNSIGNED NULL,
    snapshot_at     DATETIME NOT NULL,
    FOREIGN KEY (device_id) REFERENCES nms_devices(id) ON DELETE CASCADE,
    INDEX idx_nms_pppoe_snap (device_id, snapshot_at),
    INDEX idx_nms_pppoe_user (username)
)
");

// Migração: adiciona colunas de protocolo SNMPv3 em instalações existentes
foreach ([
    "ALTER TABLE nms_devices ADD COLUMN snmp_auth_proto ENUM('MD5','SHA') NOT NULL DEFAULT 'MD5' AFTER snmp_user",
    "ALTER TABLE nms_devices ADD COLUMN snmp_priv_proto ENUM('DES','AES') NOT NULL DEFAULT 'DES' AFTER snmp_auth_proto",
] as $sql) {
    try { $pdo->exec($sql); } catch (PDOException $e) { /* coluna já existe */ }
}

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