<?php
// Script para criar tabelas e dados iniciais (PostgreSQL + TimescaleDB)
require_once __DIR__ . '/db.php';

$pdo = db();

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

// USERS
$pdo->exec("
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(60) 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()
)
");

// PLANS
$pdo->exec("
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,
    price                DECIMAL(10,2) NOT NULL DEFAULT 0,
    active               BOOLEAN NOT NULL DEFAULT TRUE
)
");

// SUBSCRIPTIONS
$pdo->exec("
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
)
");

// MONITORED IPS
$pdo->exec("
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,
    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()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_monitored_ips_user ON monitored_ips(user_id)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_monitored_ips_user_status ON monitored_ips(user_id, last_status)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_monitored_ips_status ON monitored_ips(last_status)");

// PING LOGS — hypertable TimescaleDB (particionado por checked_at em chunks de 7 dias)
$pdo->exec("
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)
)
");

// Converte para hypertable
try {
    $pdo->exec("SELECT create_hypertable('ping_logs', 'checked_at', chunk_time_interval => INTERVAL '7 days', if_not_exists => TRUE)");
} catch (PDOException $e) { /* already a hypertable */ }

$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ping_logs_ip_time ON ping_logs(monitored_ip_id, checked_at DESC)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ping_logs_status ON ping_logs(status)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_ping_logs_checked_at ON ping_logs(checked_at DESC)");

// ALERT CHANNELS
$pdo->exec("
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')),
    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
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_alert_channels_ip ON alert_channels(monitored_ip_id)");

// ALERTS SENT
$pdo->exec("
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')),
    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'))
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_alerts_sent_ip ON alerts_sent(monitored_ip_id)");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_alerts_sent_at ON alerts_sent(sent_at DESC)");

// SYSTEM SETTINGS
$pdo->exec("
CREATE TABLE IF NOT EXISTS system_settings (
    id    SERIAL PRIMARY KEY,
    key   VARCHAR(100) NOT NULL UNIQUE,
    value TEXT NOT NULL
)
");

// STATUS PAGES
$pdo->exec("
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()
)
");

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

// RATE LIMITS
$pdo->exec("
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()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_rate_limits_ip_act ON rate_limits(ip, action)");

// PASSWORD RESETS
$pdo->exec("
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
)
");

// USER SESSIONS
$pdo->exec("
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()
)
");
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_user_sessions_user ON user_sessions(user_id, created_at DESC)");

// WEBHOOK RETRIES
$pdo->exec("
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()
)
");

// ─────────────────────────────────────────────────────────────────────────────
// TimescaleDB: Continuous Aggregate para gráficos do dashboard (por hora)
// ─────────────────────────────────────────────────────────────────────────────
try {
    $pdo->exec("
        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
    ");
} catch (PDOException $e) { /* view already exists */ }

// Política de refresh: atualiza a cada hora com delay de 1h
try {
    $pdo->exec("
        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
        )
    ");
} catch (PDOException $e) {}

// Política de compressão: comprime chunks com mais de 7 dias
try {
    $pdo->exec("ALTER TABLE ping_logs SET (
        timescaledb.compress,
        timescaledb.compress_orderby   = 'checked_at DESC',
        timescaledb.compress_segmentby = 'monitored_ip_id'
    )");
    $pdo->exec("SELECT add_compression_policy('ping_logs', INTERVAL '7 days', if_not_exists => TRUE)");
} catch (PDOException $e) {}

// Política de retenção: descarta dados com mais de 30 dias automaticamente
try {
    $pdo->exec("SELECT add_retention_policy('ping_logs', INTERVAL '30 days', if_not_exists => TRUE)");
} catch (PDOException $e) {}

// ─────────────────────────────────────────────────────────────────────────────
// Dados iniciais
// ─────────────────────────────────────────────────────────────────────────────

// Criar planos padrão se não existirem
$countPlans = $pdo->query("SELECT COUNT(*) FROM plans")->fetchColumn();
if ($countPlans == 0) {
    $stmt = $pdo->prepare("
        INSERT INTO plans (name, max_ips, min_interval_seconds, allow_email, allow_webhook, allow_sms, price)
        VALUES
        ('Free',  3,   300, TRUE, FALSE, FALSE, 0.00),
        ('Basic', 20,  60,  TRUE, TRUE,  FALSE, 49.90),
        ('Pro',   100, 30,  TRUE, TRUE,  TRUE,  149.90)
    ");
    $stmt->execute();
}

// Criar admin inicial se não existir
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email = ?");
$stmt->execute([INITIAL_ADMIN_EMAIL]);
if ($stmt->fetchColumn() == 0) {
    $hash = password_hash(INITIAL_ADMIN_PASS, PASSWORD_DEFAULT);
    $stmt = $pdo->prepare("INSERT INTO users (name, email, password_hash, role) VALUES (?, ?, ?, 'admin')");
    $stmt->execute([INITIAL_ADMIN_NAME, INITIAL_ADMIN_EMAIL, $hash]);
    echo "Administrador inicial criado: " . INITIAL_ADMIN_EMAIL . " / " . INITIAL_ADMIN_PASS . "\n";
} else {
    echo "Administrador inicial já existe.\n";
}

echo "Tabelas verificadas e dados iniciais criados.\n";
