<?php

/**
 * Envia mensagem via Telegram Bot API.
 * @param string $botToken  Token do bot (obtido via @BotFather)
 * @param string $chatId    Chat ID ou username do destino
 * @param string $text      Texto da mensagem (suporta Markdown)
 */
function send_telegram_message(string $botToken, string $chatId, string $text): bool {
    if (empty($botToken) || empty($chatId)) return false;
    $url = "https://api.telegram.org/bot" . urlencode($botToken) . "/sendMessage";
    $payload = json_encode([
        'chat_id'    => $chatId,
        'text'       => $text,
        'parse_mode' => 'Markdown',
    ]);
    if (!function_exists('curl_init')) {
        $ctx = stream_context_create(['http' => [
            'method'  => 'POST',
            'header'  => "Content-Type: application/json\r\n",
            'content' => $payload,
            'timeout' => 8,
        ]]);
        $result = @file_get_contents($url, false, $ctx);
        return $result !== false;
    }
    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_POST           => true,
        CURLOPT_POSTFIELDS     => $payload,
        CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT        => 8,
        CURLOPT_SSL_VERIFYPEER => true,
    ]);
    $resp = curl_exec($ch);
    $ok   = curl_getinfo($ch, CURLINFO_HTTP_CODE) === 200;
    curl_close($ch);
    return $ok;
}

/**
 * Verifica se um IP está em janela de manutenção ativa.
 */
function ip_in_maintenance(PDO $pdo, int $ipId, int $userId): bool {
    try {
        $stmt = $pdo->prepare("
            SELECT COUNT(*) FROM maintenance_windows
            WHERE active = TRUE
              AND starts_at <= NOW()
              AND ends_at >= NOW()
              AND user_id = ?
              AND (all_ips = TRUE OR monitored_ip_id = ?)
        ");
        $stmt->execute([$userId, $ipId]);
        return (int)$stmt->fetchColumn() > 0;
    } catch (Throwable $e) {
        return false;
    }
}

/**
 * Retorna as tags de um IP monitorado como array de strings.
 */
function get_ip_tags(PDO $pdo, int $ipId): array {
    try {
        $stmt = $pdo->prepare("
            SELECT t.name, t.color FROM tags t
            JOIN ip_tags it ON it.tag_id = t.id
            WHERE it.monitored_ip_id = ?
            ORDER BY t.name
        ");
        $stmt->execute([$ipId]);
        return $stmt->fetchAll();
    } catch (Throwable $e) {
        return [];
    }
}

/**
 * Sincroniza tags de um IP monitorado.
 * @param array $tagIds IDs das tags a associar
 */
function sync_ip_tags(PDO $pdo, int $ipId, array $tagIds): void {
    try {
        $pdo->prepare("DELETE FROM ip_tags WHERE monitored_ip_id = ?")->execute([$ipId]);
        if (empty($tagIds)) return;
        $stmt = $pdo->prepare("INSERT INTO ip_tags (monitored_ip_id, tag_id) VALUES (?, ?)");
        foreach ($tagIds as $tagId) {
            $tagId = (int)$tagId;
            if ($tagId > 0) $stmt->execute([$ipId, $tagId]);
        }
    } catch (Throwable $e) {
        mp_log('warning', 'sync_ip_tags falhou: ' . $e->getMessage());
    }
}

/**
 * Formata duração de queda em texto legível.
 * Ex: 23 minutos, 1h 5min, 3 horas
 */
function format_outage_duration(?string $offlineSince): string {
    if (!$offlineSince) return '';
    try {
        $start = new DateTimeImmutable($offlineSince);
        $diff  = $start->diff(new DateTimeImmutable());
        $total_minutes = $diff->days * 1440 + $diff->h * 60 + $diff->i;
        if ($total_minutes < 1) return 'menos de 1 minuto';
        if ($total_minutes < 60) return $total_minutes . ' minuto' . ($total_minutes != 1 ? 's' : '');
        $hours = (int)floor($total_minutes / 60);
        $mins  = $total_minutes % 60;
        $str   = $hours . 'h';
        if ($mins > 0) $str .= ' ' . $mins . 'min';
        return $str;
    } catch (Throwable $e) {
        return '';
    }
}

function h($str) {
    return htmlspecialchars((string)($str ?? ''), ENT_QUOTES, 'UTF-8');
}

function redirect($path) {
    $url = BASE_URL . $path;
    if (!headers_sent()) {
        header("Location: " . $url);
    } else {
        echo '<script>window.location.href = ' . json_encode($url) . ';</script>';
        echo '<noscript><meta http-equiv="refresh" content="0;url=' . htmlspecialchars($url, ENT_QUOTES, 'UTF-8') . '"></noscript>';
    }
    exit;
}

/**
 * Retorna a URL base absoluta da aplicação sem usar HTTP_HOST (evita Host Header Injection).
 * Usa SERVER_NAME (definido pelo servidor web) em vez do cabeçalho HTTP Host.
 */
function app_base_url(): string {
    $base = rtrim(BASE_URL, '/');
    // Se BASE_URL já for absoluta, usa diretamente
    if (preg_match('#^https?://#i', $base)) {
        return $base . '/';
    }
    $scheme = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? 'https' : 'http';
    $host   = $_SERVER['SERVER_NAME'] ?? 'localhost';
    $port   = (int)($_SERVER['SERVER_PORT'] ?? 80);
    $portSuffix = (($scheme === 'http' && $port === 80) || ($scheme === 'https' && $port === 443))
        ? '' : ':' . $port;
    return $scheme . '://' . $host . $portSuffix . $base . '/';
}

// Busca plano atual do usuário (simples: último registro)
function get_user_plan($user_id) {
    $pdo = db();
    $stmt = $pdo->prepare("
        SELECT p.*
        FROM subscriptions s
        JOIN plans p ON p.id = s.plan_id
        WHERE s.user_id = ?
        ORDER BY s.created_at DESC
        LIMIT 1
    ");
    $stmt->execute([$user_id]);
    $plan = $stmt->fetch();
    if (!$plan) {
        // Plano Free padrão
        $stmt = $pdo->prepare("SELECT * FROM plans WHERE name = 'Free' LIMIT 1");
        $stmt->execute();
        $plan = $stmt->fetch();
        if ($plan) {
            $stmt2 = $pdo->prepare("INSERT INTO subscriptions (user_id, plan_id) VALUES (?, ?)");
            $stmt2->execute([$user_id, $plan['id']]);
        }
    }
    return $plan;
}

function count_user_ips($user_id) {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM monitored_ips WHERE user_id = ?");
    $stmt->execute([$user_id]);
    return (int)$stmt->fetchColumn();
}

function get_user_name(PDO $pdo, int $userId): ?string {
    static $cache = [];
    if (array_key_exists($userId, $cache)) {
        return $cache[$userId];
    }
    $stmt = $pdo->prepare("SELECT name FROM users WHERE id = ? LIMIT 1");
    $stmt->execute([$userId]);
    $name = $stmt->fetchColumn() ?: null;
    $cache[$userId] = $name;
    return $name;
}

function get_user_timezone(PDO $pdo, int $userId): string {
    static $cache = [];
    if (array_key_exists($userId, $cache)) {
        return $cache[$userId];
    }
    $stmt = $pdo->prepare("SELECT timezone FROM users WHERE id = ? LIMIT 1");
    $stmt->execute([$userId]);
    $tz = $stmt->fetchColumn() ?: 'America/Sao_Paulo';
    $cache[$userId] = $tz;
    return $tz;
}

function system_settings_cache(bool $reset = false): array {
    static $cache = null;
    if ($reset) {
        $cache = null;
    }
    if ($cache !== null) {
        return $cache;
    }
    $pdo = db();
    $stmt = $pdo->query("SELECT key, value FROM system_settings");
    $cache = [];
    foreach ($stmt->fetchAll() as $row) {
        $cache[$row['key']] = $row['value'];
    }
    return $cache;
}

function get_system_setting(string $key, $default = null) {
    $settings = system_settings_cache();
    return array_key_exists($key, $settings) ? $settings[$key] : $default;
}

function set_system_setting(string $key, $value): void {
    $pdo = db();
    $stmt = $pdo->prepare("
        INSERT INTO system_settings (key, value)
        VALUES (?, ?)
        ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value
    ");
    $stmt->execute([$key, $value]);
    system_settings_cache(true);
}

function get_smtp_settings(): array {
    static $config = null;
    if ($config !== null) {
        return $config;
    }
    $config = [
        'host' => get_system_setting('smtp_host', SMTP_HOST),
        'port' => (int)get_system_setting('smtp_port', SMTP_PORT),
        'user' => get_system_setting('smtp_user', SMTP_USER),
        'pass' => get_system_setting('smtp_pass', SMTP_PASS),
        'secure' => strtolower(get_system_setting('smtp_secure', SMTP_SECURE ?: 'tls')),
        'from' => get_system_setting('smtp_from', SMTP_FROM),
        'from_name' => get_system_setting('smtp_from_name', SMTP_FROM_NAME),
        'timeout' => (int)get_system_setting('smtp_timeout', SMTP_TIMEOUT ?: 10),
    ];
    return $config;
}

function get_whatsapp_settings(): array {
    static $config = null;
    if ($config !== null) {
        return $config;
    }
    $config = [
        'webhook_url' => get_system_setting('whatsapp_webhook_url', WHATSAPP_WEBHOOK_URL),
        'token' => get_system_setting('whatsapp_webhook_token', WHATSAPP_WEBHOOK_TOKEN),
    ];
    return $config;
}

function db_column_exists(PDO $pdo, string $table, string $column): bool {
    static $cache = [];
    $key = $table . ':' . $column;
    if (array_key_exists($key, $cache)) {
        return $cache[$key];
    }
    try {
        $stmt = $pdo->prepare("
            SELECT column_name FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = ? AND column_name = ?
        ");
        $stmt->execute([$table, $column]);
        $exists = (bool)$stmt->fetch();
    } catch (Throwable $e) {
        $exists = false;
    }
    $cache[$key] = $exists;
    return $exists;
}

function ensure_monitored_ips_check_method(PDO $pdo): bool {
    if (!db_column_exists($pdo, 'monitored_ips', 'check_method')) {
        try {
            $pdo->exec("ALTER TABLE monitored_ips ADD COLUMN IF NOT EXISTS check_method VARCHAR(10) NOT NULL DEFAULT 'auto'");
        } catch (Throwable $e) {
            return false;
        }
    }
    // Garante que a constraint inclui http e https (migração automática)
    try {
        $row = $pdo->query("
            SELECT pg_get_constraintdef(oid) AS def
            FROM pg_constraint
            WHERE conrelid = 'monitored_ips'::regclass
              AND conname = 'monitored_ips_check_method_check'
        ")->fetch();
        if ($row && strpos($row['def'], "'https'") === false) {
            $pdo->exec("
                ALTER TABLE monitored_ips
                  DROP CONSTRAINT IF EXISTS monitored_ips_check_method_check,
                  ADD CONSTRAINT monitored_ips_check_method_check
                    CHECK (check_method IN ('auto','icmp','tcp','http','https'))
            ");
        }
    } catch (Throwable) {}
    return db_column_exists($pdo, 'monitored_ips', 'check_method');
}

function split_host_and_port(string $host): array {
    $host = trim($host);
    $port = null;
    if ($host !== '' && preg_match('/^(.+):(\d+)$/', $host, $matches) && strpos($host, ']') === false) {
        $host = $matches[1];
        $port = (int)$matches[2];
    }
    return ['host' => $host, 'port' => $port];
}

function ping_host($host, $timeout_ms = 1000, string $method = 'auto'): array {
    $host = trim($host);
    if ($host === '') {
        return ['status' => 'offline', 'latency_ms' => null];
    }

    $method = strtolower(trim($method));
    if (!in_array($method, ['auto', 'icmp', 'tcp', 'http', 'https'], true)) {
        $method = 'auto';
    }

    // HTTP/HTTPS: passa a URL diretamente, sem split_host_and_port (evita duplo prefixo)
    // Usa no mínimo 8s para HTTP — DNS + TLS + resposta levam muito mais que ICMP
    if ($method === 'http' || $method === 'https') {
        if (!preg_match('#^https?://#i', $host)) {
            $host = $method . '://' . $host;
        }
        return ping_host_http($host, max($timeout_ms, 8000));
    }

    $parts = split_host_and_port($host);
    $host = $parts['host'];
    $port = $parts['port'];

    if ($method === 'tcp') {
        return tcp_ping_fallback($host, $timeout_ms, $port);
    }

    $execAvailable = function_exists('exec') && !in_array('exec', array_map('trim', explode(',', (string)ini_get('disable_functions'))));
    $isWindows = stripos(PHP_OS, 'WIN') === 0;

    if ($execAvailable) {
        $timeout_s = max(1, (int)ceil($timeout_ms / 1000));
        if ($isWindows) {
            $cmd = sprintf('ping -n 1 -w %d %s', $timeout_ms, escapeshellarg($host));
        } else {
            $cmd = sprintf('ping -c 1 -W %d %s', $timeout_s, escapeshellarg($host));
        }
        $output = [];
        $return_var = 0;
        @exec($cmd, $output, $return_var);

        if ($return_var === 0) {
            $text = implode("\n", $output);
            if (preg_match('/time[=<]\s*([\d\.]+)\s*ms/i', $text, $m)) {
                $latency = (int)round((float)$m[1]);
                return ['status' => 'online', 'latency_ms' => $latency];
            }
            return ['status' => 'online', 'latency_ms' => null];
        }
        if ($method === 'icmp') {
            return ['status' => 'offline', 'latency_ms' => null];
        }
    } elseif ($method === 'icmp') {
        return ['status' => 'offline', 'latency_ms' => null];
    }

    return tcp_ping_fallback($host, $timeout_ms, $port);
}

function ping_host_http(string $url, int $timeout_ms = 3000): array {
    if (!preg_match('#^https?://#i', $url)) {
        $url = 'http://' . $url;
    }
    if (!function_exists('curl_init')) {
        return ['status' => 'offline', 'latency_ms' => null];
    }
    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER    => true,
        CURLOPT_NOBODY            => true,
        CURLOPT_TIMEOUT_MS        => $timeout_ms,
        CURLOPT_CONNECTTIMEOUT_MS => $timeout_ms,
        CURLOPT_FOLLOWLOCATION    => false, // desabilitado para evitar SSRF via redirect
        CURLOPT_SSL_VERIFYPEER    => true,
        CURLOPT_SSL_VERIFYHOST    => 2,
        CURLOPT_USERAGENT         => 'MonitorPing/1.0',
    ]);
    curl_exec($ch);
    $code      = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $totalTime = curl_getinfo($ch, CURLINFO_TOTAL_TIME);
    curl_close($ch);
    $online = ($code >= 200 && $code < 400);
    return [
        'status'     => $online ? 'online' : 'offline',
        'latency_ms' => $online ? (int)($totalTime * 1000) : null,
    ];
}

function tcp_ping_fallback(string $host, int $timeout_ms = 1000, ?int $forcePort = null): array {
    $parts = split_host_and_port($host);
    $host = $parts['host'];
    $port = $forcePort;
    if ($port === null) {
        $port = $parts['port'];
    }

    $portsToTry = $port ? [$port] : [80, 443, 22, 53];
    $timeout_s = max(0.2, $timeout_ms / 1000);

    foreach ($portsToTry as $currentPort) {
        $start = microtime(true);
        $connection = @fsockopen($host, $currentPort, $errno, $errstr, $timeout_s);
        if ($connection) {
            $latency = (int)round((microtime(true) - $start) * 1000);
            fclose($connection);
            return ['status' => 'online', 'latency_ms' => $latency];
        }
    }

    return ['status' => 'offline', 'latency_ms' => null];
}

function get_alert_channels(int $ipId): array {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT * FROM alert_channels WHERE monitored_ip_id = ? ORDER BY id ASC");
    $stmt->execute([$ipId]);
    return $stmt->fetchAll() ?: [];
}

function generate_slug(int $length = 12): string {
    $alphabet = 'abcdefghijklmnopqrstuvwxyz0123456789';
    $max = strlen($alphabet) - 1;
    $slug = '';
    for ($i = 0; $i < $length; $i++) {
        $slug .= $alphabet[random_int(0, $max)];
    }
    return $slug;
}

function csrf_token(): string {
    if (session_status() !== PHP_SESSION_ACTIVE) {
        session_start();
    }
    if (empty($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(16));
    }
    return $_SESSION['csrf_token'];
}

function csrf_check(?string $token): bool {
    if (!$token || session_status() !== PHP_SESSION_ACTIVE || empty($_SESSION['csrf_token'])) {
        return false;
    }
    return hash_equals($_SESSION['csrf_token'], $token);
}

function rate_limit_check(string $key, int $seconds): bool {
    if (session_status() !== PHP_SESSION_ACTIVE) {
        session_start();
    }
    $now = time();
    $last = $_SESSION['rate_limit'][$key] ?? 0;
    if ($now - $last < $seconds) {
        return false;
    }
    $_SESSION['rate_limit'][$key] = $now;
    return true;
}

function validate_monitor_host(string $host, ?string &$error = null, string $method = 'auto'): bool {
    $error = null;
    // Para métodos HTTP/HTTPS aceita URL completa
    if (in_array($method, ['http', 'https'], true)) {
        if ($host === '') { $error = 'URL vazia.'; return false; }
        if (!preg_match('#^https?://#i', $host)) {
            $host = $method . '://' . $host;
        }
        $parsed = parse_url($host);
        $h = $parsed['host'] ?? '';
        if ($h === '') { $error = 'URL inválida.'; return false; }
        $isIp = filter_var($h, FILTER_VALIDATE_IP);
        if ($isIp !== false && filter_var($h, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE) === false) {
            $error = 'Endereco IP privado nao permitido.'; return false;
        }
        return true;
    }
    $parts = split_host_and_port($host);
    $host = trim($parts['host']);
    $port = $parts['port'];
    if ($host === '') {
        $error = 'Host vazio.';
        return false;
    }
    if (stripos($host, '://') !== false) {
        $error = 'Informe apenas host ou host:porta (sem http://).';
        return false;
    }
    if ($port !== null && ($port < 1 || $port > 65535)) {
        $error = 'Porta invalida.';
        return false;
    }
    $isIp = filter_var($host, FILTER_VALIDATE_IP);
    if ($isIp !== false) {
        if (filter_var($host, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE) === false) {
            $error = 'Endereco IP privado nao permitido.';
            return false;
        }
        return true;
    }
    if (!preg_match('/^(?=.{3,255}$)([a-zA-Z0-9-]+\.)+[a-zA-Z]{2,}$/', $host)) {
        $error = 'Dominio invalido.';
        return false;
    }
    return true;
}

function send_email_message(string $to, string $subject, string $message, ?string &$error = null, ?string $htmlMessage = null): bool {
    $smtp = get_smtp_settings();
    $to = trim($to);
    if ($to === '') {
        $error = 'Destinatário vazio.';
        return false;
    }
    // Valida o endereço de e-mail para prevenir SMTP header injection
    if (filter_var($to, FILTER_VALIDATE_EMAIL) === false) {
        $error = 'Endereço de e-mail inválido.';
        return false;
    }
    // Remove qualquer quebra de linha do endereço (proteção extra contra injeção)
    if (strpbrk($to, "\r\n") !== false) {
        $error = 'Endereço de e-mail contém caracteres inválidos.';
        return false;
    }

    if (empty($smtp['host'])) {
        $headers = "From: " . $smtp['from_name'] . " <" . $smtp['from'] . ">\r\n";
        if ($htmlMessage !== null) {
            $headers .= "MIME-Version: 1.0\r\n";
            $headers .= "Content-Type: text/html; charset=UTF-8\r\n";
            $headers .= "Content-Transfer-Encoding: 8bit\r\n";
            $body = $htmlMessage;
        } else {
            $headers .= "MIME-Version: 1.0\r\n";
            $headers .= "Content-Type: text/plain; charset=UTF-8\r\n";
            $headers .= "Content-Transfer-Encoding: 8bit\r\n";
            $body = $message;
        }
        if (@mail($to, $subject, $body, $headers)) {
            return true;
        }
        $error = 'mail() retornou false';
        return false;
    }

    try {
        smtp_send_mail($to, $subject, $message, $smtp, $htmlMessage);
        return true;
    } catch (Exception $e) {
        $error = $e->getMessage();
        return false;
    }
}

function smtp_send_mail(string $to, string $subject, string $message, array $smtpConfig, ?string $htmlMessage = null): void {
    $host = $smtpConfig['host'];
    $port = (int)($smtpConfig['port'] ?: 587);
    $secure = in_array($smtpConfig['secure'], ['tls', 'ssl', 'none'], true) ? $smtpConfig['secure'] : 'tls';
    $timeout = max(5, (int)($smtpConfig['timeout'] ?: 10));
    $remote = ($secure === 'ssl' ? 'ssl://' : '') . $host . ':' . $port;
    $context = stream_context_create([
        'ssl' => [
            'verify_peer'      => false,
            'verify_peer_name' => false,
        ],
    ]);
    $socket = @stream_socket_client($remote, $errno, $errstr, $timeout, STREAM_CLIENT_CONNECT, $context);
    if (!$socket) {
        throw new Exception("Falha ao conectar ao SMTP: {$errstr} ({$errno})");
    }
    stream_set_timeout($socket, $timeout);

    $read = function () use ($socket) {
        $data = '';
        while ($line = fgets($socket, 515)) {
            $data .= $line;
            if (isset($line[3]) && $line[3] === ' ') {
                break;
            }
        }
        return $data;
    };

    $expect = function (array $codes) use ($read) {
        $response = $read();
        $code = substr($response, 0, 3);
        if (!in_array($code, $codes, true)) {
            throw new Exception("Resposta SMTP inesperada: " . trim($response));
        }
        return $response;
    };

    $write = function (string $cmd) use ($socket) {
        fwrite($socket, $cmd . "\r\n");
    };

    $expect(['220']);
    $write('EHLO ' . gethostname());
    $expect(['250']);

    if ($secure === 'tls') {
        $write('STARTTLS');
        $expect(['220']);
        if (!stream_socket_enable_crypto($socket, true, STREAM_CRYPTO_METHOD_TLS_CLIENT)) {
            throw new Exception('Falha ao iniciar TLS no SMTP.');
        }
        $write('EHLO ' . gethostname());
        $expect(['250']);
    }

    if (!empty($smtpConfig['user'])) {
        $write('AUTH LOGIN');
        $expect(['334']);
        $write(base64_encode($smtpConfig['user']));
        $expect(['334']);
        $write(base64_encode($smtpConfig['pass']));
        $expect(['235']);
    }

    $from = $smtpConfig['from'] ?: $smtpConfig['user'];
    $write('MAIL FROM:<' . $from . '>');
    $expect(['250']);

    $recipients = preg_split('/[,;]/', $to);
    $validRcpt = false;
    foreach ($recipients as $recipient) {
        $recipient = trim($recipient);
        if ($recipient === '') continue;
        $write('RCPT TO:<' . $recipient . '>');
        $expect(['250', '251']);
        $validRcpt = true;
    }

    if (!$validRcpt) {
        throw new Exception('Nenhum destinatário válido informado.');
    }

    $write('DATA');
    $expect(['354']);

    $headers = [
        "From: " . $smtpConfig['from_name'] . " <" . $from . ">",
        "To: " . $to,
        "Subject: " . $subject,
        "MIME-Version: 1.0"
    ];

    if ($htmlMessage !== null) {
        $headers[] = "Content-Type: text/html; charset=UTF-8";
        $headers[] = "Content-Transfer-Encoding: 8bit";
        $body = $htmlMessage;
    } else {
        $headers[] = "Content-Type: text/plain; charset=UTF-8";
        $headers[] = "Content-Transfer-Encoding: 8bit";
        $body = $message;
    }

    $normalizedBody = preg_replace('/^\./m', '..', str_replace(["\r\n", "\n"], "\r\n", $body));
    $write(implode("\r\n", $headers) . "\r\n\r\n" . $normalizedBody . "\r\n.");
    $expect(['250']);

    $write('QUIT');
    $expect(['221']);
    fclose($socket);
}

function sync_alert_channels(int $ipId, array $channels, ?string $fallbackEmail = null): void {
    $pdo = db();
    ensure_whatsapp_enum($pdo);
    $inserted = 0;
    $stmt = $pdo->prepare("
        INSERT INTO alert_channels
        (monitored_ip_id, type, target, min_failures, cooldown_minutes, silence_start, silence_end, active, notify_recovery)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    $pdo->prepare("DELETE FROM alert_channels WHERE monitored_ip_id = ?")->execute([$ipId]);

    foreach ($channels as $channel) {
        $type = isset($channel['type']) && in_array($channel['type'], ['email','webhook','sms','whatsapp'], true)
            ? $channel['type']
            : null;
        $target = trim($channel['target'] ?? '');
        if (!$type || $target === '') {
            continue;
        }

        $minFailures     = max(1, (int)($channel['min_failures'] ?? 1));
        $cooldown        = max(0, (int)($channel['cooldown_minutes'] ?? 0));
        $silenceStart    = sanitize_channel_time($channel['silence_start'] ?? null);
        $silenceEnd      = sanitize_channel_time($channel['silence_end'] ?? null);
        $active          = isset($channel['active']) ? true : false;
        $notifyRecovery  = isset($channel['notify_recovery']) ? true : false;

        $stmt->execute([
            $ipId,
            $type,
            $target,
            $minFailures,
            $cooldown,
            $silenceStart,
            $silenceEnd,
            $active,
            $notifyRecovery
        ]);
        $inserted++;
    }

    if ($inserted === 0 && $fallbackEmail) {
        $stmt->execute([$ipId, 'email', trim($fallbackEmail), 1, 0, null, null, true, true]);
    }
}

function sanitize_channel_time(?string $value): ?string {
    if (!$value) return null;
    $value = trim($value);
    if (preg_match('/^\d{2}:\d{2}$/', $value)) {
        return $value . ':00';
    }
    if (preg_match('/^\d{2}:\d{2}:\d{2}$/', $value)) {
        return $value;
    }
    return null;
}

function channel_in_silence_window(array $channel, DateTimeImmutable $now): bool {
    if (empty($channel['silence_start']) || empty($channel['silence_end'])) {
        return false;
    }
    $start = DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $now->format('Y-m-d') . ' ' . $channel['silence_start']);
    $end = DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $now->format('Y-m-d') . ' ' . $channel['silence_end']);
    if (!$start || !$end) {
        return false;
    }

    if ($end > $start) {
        return $now >= $start && $now <= $end;
    }

    // Silence wraps past midnight
    return $now >= $start || $now <= $end->modify('+1 day');
}

function channel_in_cooldown(array $channel, DateTimeImmutable $now): bool {
    if (empty($channel['last_sent_at']) || empty($channel['cooldown_minutes'])) {
        return false;
    }
    $cooldown = (int)$channel['cooldown_minutes'];
    if ($cooldown <= 0) {
        return false;
    }
    try {
        $last = new DateTimeImmutable($channel['last_sent_at']);
    } catch (Exception $e) {
        return false;
    }
    return $last->modify("+{$cooldown} minutes") > $now;
}

function build_alert_email_content(
    array   $ip,
    string  $status,
    ?int    $latency,
    ?string $userName       = null,
    ?string $userTimezone   = null,
    ?string $outageDuration = null,
    ?int    $newFailures    = null
): array {
    $label       = $ip['label'] ?? 'Canal monitorado';
    $host        = $ip['host'] ?? '';
    $statusKey   = strtolower(trim($status));
    $latencyText = $latency !== null ? $latency . ' ms' : 'N/A';
    $userName    = $userName ?: 'MonitorPing';
    $timestamp   = format_datetime_tz(gmdate('Y-m-d H:i:s'), $userTimezone, 'd/m/Y H:i:s');

    $statusColor = match($statusKey) {
        'online'       => '#22c55e',
        'offline'      => '#ef4444',
        'latency_high' => '#f59e0b',
        default        => '#64748b',
    };
    $statusBg = match($statusKey) {
        'online'       => '#f0fdf4',
        'offline'      => '#fef2f2',
        'latency_high' => '#fffbeb',
        default        => '#f8fafc',
    };
    $icon = match($statusKey) {
        'online'       => '✅',
        'offline'      => '❌',
        'latency_high' => '⚠️',
        default        => '📡',
    };
    $cardTitle = match($statusKey) {
        'online'       => 'Dispositivo Recuperado!',
        'offline'      => 'Alerta: Dispositivo Offline!',
        'latency_high' => 'Latência Alta Detectada',
        default        => 'Alerta de Canal',
    };
    $statusPretty = match($statusKey) {
        'online'       => 'Online',
        'offline'      => 'Offline',
        'latency_high' => 'Latência Alta',
        default        => ucfirst($statusKey),
    };
    $subject = match($statusKey) {
        'online'       => "[MonitorPing] ✅ {$label} — Canal RECUPERADO",
        'offline'      => "[MonitorPing] ❌ {$label} — Canal OFFLINE",
        'latency_high' => "[MonitorPing] ⚠️ {$label} — Latência Alta: {$latencyText}",
        default        => "[MonitorPing] {$label} está " . strtoupper($statusKey),
    };

    $contextText = '';
    if ($statusKey === 'online' && $outageDuration) {
        $contextText = "Offline por {$outageDuration}.";
    } elseif ($statusKey === 'offline' && $newFailures !== null) {
        $contextText = "{$newFailures} falha(s) consecutiva(s) detectada(s).";
    } elseif ($statusKey === 'latency_high') {
        $contextText = "Latência acima do limiar configurado.";
    }

    $textMessage = "Host: {$label} ({$host})\nStatus: {$statusPretty}\nLatência: {$latencyText}\nHorário: {$timestamp}"
        . ($contextText ? "\nInfo: {$contextText}" : '')
        . "\nDashboard: " . APP_URL . '?page=user_dashboard';

    $htmlHost      = h($host);
    $htmlLabel     = h($label);
    $htmlStatus    = h($statusPretty);
    $htmlLatency   = h($latencyText);
    $htmlTimestamp = h($timestamp);
    $htmlUser      = h($userName);
    $htmlContext   = $contextText ? '<p style="margin:8px 0 0;font-size:14px;color:#64748b;">' . h($contextText) . '</p>' : '';
    $dashboardUrl  = h(APP_URL . '?page=user_dashboard');

    $htmlBody = <<<HTML
<!DOCTYPE html>
<html lang="pt-BR">
<body style="margin:0;padding:0;background:#f1f5f9;">
  <table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="background:#f1f5f9;padding:32px 0;">
    <tr>
      <td align="center">
        <table role="presentation" width="500" cellpadding="0" cellspacing="0" style="background:#ffffff;border-radius:20px;font-family:'Inter',Arial,sans-serif;color:#0f172a;border:1px solid #e2e8f0;overflow:hidden;">

          <!-- Header colorido por status -->
          <tr>
            <td align="center" style="background:{$statusBg};padding:28px 32px 20px;border-bottom:3px solid {$statusColor};">
              <div style="font-size:40px;line-height:1;margin-bottom:10px;">{$icon}</div>
              <h2 style="margin:0;font-size:20px;font-weight:800;color:#0f172a;">{$cardTitle}</h2>
              <div style="margin-top:10px;">
                <span style="display:inline-block;padding:5px 16px;border-radius:999px;font-weight:700;font-size:13px;background:{$statusColor};color:#ffffff;">{$htmlStatus}</span>
              </div>
            </td>
          </tr>

          <!-- Corpo -->
          <tr>
            <td style="padding:24px 32px 8px;">
              <p style="margin:0 0 6px;font-size:15px;color:#334155;">Olá, <strong>{$htmlUser}</strong></p>
              <p style="margin:0;font-size:15px;color:#334155;">O canal <strong>{$htmlLabel}</strong> agora está <strong style="color:{$statusColor};">{$htmlStatus}</strong>.</p>
              {$htmlContext}
            </td>
          </tr>

          <!-- Tabela de dados -->
          <tr>
            <td style="padding:16px 32px;">
              <table width="100%" cellspacing="0" cellpadding="0" style="border:1px solid #e2e8f0;border-radius:12px;border-collapse:collapse;font-size:14px;">
                <tr>
                  <td style="padding:10px 14px;border-bottom:1px solid #e2e8f0;background:#f8fafc;font-weight:600;width:38%;color:#475569;">Host</td>
                  <td style="padding:10px 14px;border-bottom:1px solid #e2e8f0;color:#0f172a;">{$htmlHost}</td>
                </tr>
                <tr>
                  <td style="padding:10px 14px;border-bottom:1px solid #e2e8f0;background:#f8fafc;font-weight:600;color:#475569;">Latência</td>
                  <td style="padding:10px 14px;border-bottom:1px solid #e2e8f0;color:#0f172a;">{$htmlLatency}</td>
                </tr>
                <tr>
                  <td style="padding:10px 14px;background:#f8fafc;font-weight:600;color:#475569;">Horário</td>
                  <td style="padding:10px 14px;color:#0f172a;">{$htmlTimestamp}</td>
                </tr>
              </table>
            </td>
          </tr>

          <!-- Botão Dashboard -->
          <tr>
            <td align="center" style="padding:8px 32px 28px;">
              <a href="{$dashboardUrl}" style="display:inline-block;padding:11px 28px;background:#4f46e5;color:#ffffff;border-radius:10px;text-decoration:none;font-weight:700;font-size:14px;">Ver no Dashboard →</a>
            </td>
          </tr>

          <!-- Footer -->
          <tr>
            <td style="padding:16px 32px;border-top:1px solid #e2e8f0;background:#f8fafc;border-radius:0 0 20px 20px;">
              <p style="margin:0;font-size:13px;color:#94a3b8;text-align:center;">MonitorPing · Monitoramento de Rede</p>
            </td>
          </tr>

        </table>
      </td>
    </tr>
  </table>
</body>
</html>
HTML;

    return [
        'subject' => $subject,
        'text' => $textMessage,
        'html' => $htmlBody,
        'timestamp' => $timestamp
    ];
}

function send_channel_alert(PDO $pdo, array $channel, array $ip, string $status, ?int $latency, ?int $newFailures = null, ?string $outageDuration = null): void {
    $target = $channel['target'] ?? '';
    $type = $channel['type'] ?? 'email';
    ensure_whatsapp_enum($pdo);
    $userName     = isset($ip['user_id']) ? get_user_name($pdo, (int)$ip['user_id']) : null;
    $userTimezone = isset($ip['user_id']) ? get_user_timezone($pdo, (int)$ip['user_id']) : null;
    $emailContent = build_alert_email_content($ip, $status, $latency, $userName, $userTimezone, $outageDuration, $newFailures);
    $subject = $emailContent['subject'];
    $message = $emailContent['text'];
    $htmlMessage = $emailContent['html'];

    $statusFlag = 'success';
    $errorMessage = null;

    try {
        if ($type === 'email') {
            if (!send_email_message($target, $subject, $message, $errorMessage, $htmlMessage)) {
                $statusFlag = 'failed';
            }
        } elseif ($type === 'webhook') {
            $payload = json_encode([
                'host' => $ip['host'],
                'label' => $ip['label'],
                'status' => $status,
                'latency' => $latency,
                'timestamp' => date('c')
            ]);
            $context = stream_context_create([
                'http' => [
                    'method' => 'POST',
                    'header' => "Content-Type: application/json\r\n",
                    'content' => $payload,
                    'timeout' => 5
                ]
            ]);
            $result = @file_get_contents($target, false, $context);
            $httpCode = 0;
            if (!empty($http_response_header)) {
                foreach ($http_response_header as $headerLine) {
                    if (preg_match('#HTTP/\d\.\d\s+(\d+)#', $headerLine, $match)) {
                        $httpCode = (int)$match[1];
                        break;
                    }
                }
            }
            if ($result === false || $httpCode >= 400 || $httpCode === 0) {
                $statusFlag = 'failed';
                $errorMessage = 'Webhook erro HTTP ' . ($httpCode ?: 'timeout/sem resposta');
                queue_webhook_retry($pdo, $channel, (int)$ip['id'], $target, $payload);
            }
        } elseif ($type === 'telegram') {
            $botToken = get_system_setting('telegram_bot_token', '');
            if (empty($botToken)) {
                $statusFlag = 'failed';
                $errorMessage = 'Telegram Bot Token não configurado em Configurações.';
            } else {
                // Monta mensagem Markdown para o Telegram
                $statusEmoji = $status === 'online' ? '✅' : ($status === 'latency_high' ? '⚠️' : '🔴');
                $latencyText = $latency !== null ? "{$latency} ms" : 'N/A';
                $tgDuration  = $outageDuration ? "\n⏱️ *Duração da queda:* {$outageDuration}" : '';
                $tgText = "{$statusEmoji} *MonitorPing Alert*\n"
                    . "*Host:* " . ($ip['label'] ?? $ip['host']) . "\n"
                    . "*Endereço:* `" . $ip['host'] . "`\n"
                    . "*Status:* " . strtoupper($status) . "\n"
                    . "*Latência:* {$latencyText}"
                    . $tgDuration . "\n"
                    . "🕐 " . date('d/m/Y H:i:s');
                if (!send_telegram_message($botToken, $target, $tgText)) {
                    $statusFlag = 'failed';
                    $errorMessage = 'Falha ao enviar mensagem Telegram.';
                }
            }
        } elseif ($type === 'sms') {
            error_log("[MonitorPing] SMS para {$target}: {$message}");
        } elseif ($type === 'whatsapp') {
            $whats = get_whatsapp_settings();
            if (empty($whats['webhook_url'])) {
                $statusFlag = 'failed';
                $errorMessage = 'Webhook WhatsApp não configurado.';
            } else {
                $payload = json_encode([
                    'phone' => $target,
                    'host' => $ip['host'],
                    'label' => $ip['label'],
                    'status' => $status,
                    'latency' => $latency,
                    'timestamp' => date('c'),
                    'token' => $whats['token'],
                ]);
                $context = stream_context_create([
                    'http' => [
                        'method' => 'POST',
                        'header' => "Content-Type: application/json\r\n",
                        'content' => $payload,
                        'timeout' => 5
                    ]
                ]);
                $result = @file_get_contents($whats['webhook_url'], false, $context);
                $httpCode = 0;
                if (!empty($http_response_header)) {
                    foreach ($http_response_header as $headerLine) {
                        if (preg_match('#HTTP/\d\.\d\s+(\d+)#', $headerLine, $match)) {
                            $httpCode = (int)$match[1];
                            break;
                        }
                    }
                }
                if ($result === false || $httpCode >= 400 || $httpCode === 0) {
                    $statusFlag = 'failed';
                    $errorMessage = 'WhatsApp webhook erro HTTP ' . ($httpCode ?: 'timeout/sem resposta');
                    queue_webhook_retry($pdo, $channel, (int)$ip['id'], $whats['webhook_url'], $payload);
                }
            }
        }
    } catch (Throwable $e) {
        $statusFlag = 'failed';
        $errorMessage = $e->getMessage();
    }

    // Atribui severidade básica para observabilidade
    $severity = 'info';
    if ($status === 'offline') {
        $severity = 'critical';
    } elseif ($status === 'online') {
        $severity = 'info';
    } elseif ($latency !== null && $latency >= 200) {
        $severity = 'warning';
    }

    $stmtAlert = $pdo->prepare("
        INSERT INTO alerts_sent (monitored_ip_id, channel_id, type, status, error_message, message, sent_at, severity)
        VALUES (?, ?, ?, ?, ?, ?, NOW(), ?)
    ");
    $stmtAlert->execute([$ip['id'], $channel['id'] ?? null, $type, $statusFlag, $errorMessage, $message, $severity]);

    if (!empty($channel['id']) && $statusFlag === 'success') {
        $pdo->prepare("
            UPDATE alert_channels
            SET last_sent_at = NOW(), consecutive_failures = ?
            WHERE id = ?
        ")->execute([$status === 'online' ? 0 : ($newFailures ?? ($channel['consecutive_failures'] ?? 0)), $channel['id']]);
    }
}

/** #39 Enfileira webhook/whatsapp para retry automático (3 tentativas: 5min, 30min, 2h) */
function queue_webhook_retry(PDO $pdo, array $channel, int $ipId, string $target, string $payload): void {
    try {
        $pdo->prepare("
            INSERT INTO webhook_retries (channel_id, ip_id, target, payload, attempts, next_retry_at)
            VALUES (?, ?, ?, ?, 0, NOW() + INTERVAL '5 minutes')
        ")->execute([$channel['id'] ?? null, $ipId, $target, $payload]);
    } catch (Throwable $e) {
        mp_log('warning', 'queue_webhook_retry falhou: ' . $e->getMessage());
    }
}

function ensure_whatsapp_enum(PDO $pdo): void {}

function get_client_ip(): string {
    // REMOTE_ADDR é o único cabeçalho que não pode ser forjado pelo cliente.
    // X-Forwarded-For e CF-Connecting-IP podem ser injetados por qualquer um,
    // por isso usamos apenas REMOTE_ADDR para decisões de segurança (rate limiting).
    // Se o sistema estiver atrás de um proxy reverso confiável (ex: Cloudflare, nginx),
    // configure o proxy para substituir REMOTE_ADDR pelo IP real do cliente antes
    // que a requisição chegue ao PHP (ex: ngx_http_realip_module).
    return $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1';
}

function check_rate_limit_ip(PDO $pdo, string $action, int $max_attempts, int $window_seconds): bool {
    $ip = get_client_ip();
    
    // Garante que a tabela existe sem precisar refazer scripts
    static $tableChecked = false;
    if (!$tableChecked) {
        $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)");
        $tableChecked = true;
    }
    
    // Limpeza de lixo antigo de 10% das vezes
    if (random_int(1, 10) === 1) {
        $pdo->exec("DELETE FROM rate_limits WHERE last_attempt < (NOW() - INTERVAL '1 day')");
    }

    $stmt = $pdo->prepare("SELECT attempts, last_attempt FROM rate_limits WHERE ip = ? AND action = ?");
    $stmt->execute([$ip, $action]);
    $row = $stmt->fetch();
    
    if ($row) {
        $last = strtotime($row['last_attempt']);
        if (time() - $last > $window_seconds) {
            // Período de bloqueio passou, reseta a contagem
            $pdo->prepare("UPDATE rate_limits SET attempts = 1, last_attempt = NOW() WHERE ip = ? AND action = ?")->execute([$ip, $action]);
            return true;
        }
        
        if ($row['attempts'] >= $max_attempts) {
            // Estourou o limite! Atualiza só a data pra manter a punição de tempo
            $pdo->prepare("UPDATE rate_limits SET last_attempt = NOW() WHERE ip = ? AND action = ?")->execute([$ip, $action]);
            return false;
        }
        
        $pdo->prepare("UPDATE rate_limits SET attempts = attempts + 1, last_attempt = NOW() WHERE ip = ? AND action = ?")->execute([$ip, $action]);
        return true;
    } else {
        $pdo->prepare("INSERT INTO rate_limits (ip, action, attempts, last_attempt) VALUES (?, ?, 1, NOW())")->execute([$ip, $action]);
        return true;
    }
}

// ── Cache de dashboard (arquivo em /tmp, TTL configurável) ───────────────────

/** #30 Log estruturado — escreve no error_log do PHP com timestamp e contexto */
function mp_log(string $level, string $message, array $context = []): void {
    $ts  = date('Y-m-d H:i:s');
    $ctx = $context ? ' ' . json_encode($context, JSON_UNESCAPED_UNICODE) : '';
    error_log("[MonitorPing][{$level}] {$ts} {$message}{$ctx}");
}

function dash_cache_get(string $key, int $ttl = 30): ?array {
    $file = sys_get_temp_dir() . '/mp_' . md5($key) . '.cache';
    if (!is_file($file)) return null;
    $raw = @file_get_contents($file);
    if (!$raw) return null;
    $data = json_decode($raw, true);
    if (!is_array($data) || (time() - ($data['ts'] ?? 0)) > $ttl) return null;
    return $data['v'];
}

function dash_cache_set(string $key, array $value): void {
    $file = sys_get_temp_dir() . '/mp_' . md5($key) . '.cache';
    @file_put_contents($file, json_encode(['ts' => time(), 'v' => $value]), LOCK_EX);
}

/**
 * Retorna estatísticas pesadas de ping_logs para o dashboard (com cache 30s).
 * Combina uptime 24h, latência média 24h e uptime por IP em uma única chamada.
 */
function get_dash_log_stats(PDO $pdo, int $userId, array $ipIds = []): array {
    $cacheKey = 'dash_logs_' . $userId;
    $cached   = dash_cache_get($cacheKey);
    if ($cached !== null) return $cached;

    // Uptime + latência 24h em uma única query
    $stmt = $pdo->prepare("
        SELECT COUNT(*) AS total,
               SUM(CASE WHEN pl.status='online' THEN 1 ELSE 0 END) AS on_count,
               AVG(pl.latency_ms) AS avg_lat
        FROM ping_logs pl
        JOIN monitored_ips mi ON mi.id = pl.monitored_ip_id
        WHERE mi.user_id = ? AND pl.checked_at >= (NOW() - INTERVAL '1 day')
    ");
    $stmt->execute([$userId]);
    $row = $stmt->fetch();

    $uptime = ($row['total'] ?? 0) > 0
        ? round(($row['on_count'] / $row['total']) * 100, 2)
        : null;
    $avgLat = $row['avg_lat'] !== null ? round((float)$row['avg_lat'], 1) : null;

    // Uptime por IP (para os cards individuais)
    $uptimeByIp = [];
    if (!empty($ipIds)) {
        $placeholders = implode(',', array_fill(0, count($ipIds), '?'));
        $uipStmt = $pdo->prepare("
            SELECT monitored_ip_id,
                   SUM(CASE WHEN status='online' THEN 1 ELSE 0 END) AS on_count,
                   COUNT(*) AS total
            FROM ping_logs
            WHERE monitored_ip_id IN ($placeholders)
              AND checked_at >= (NOW() - INTERVAL '1 day')
            GROUP BY monitored_ip_id
        ");
        $uipStmt->execute($ipIds);
        while ($r = $uipStmt->fetch()) {
            $uptimeByIp[(int)$r['monitored_ip_id']] = [
                'on_count' => (int)$r['on_count'],
                'total'    => (int)$r['total'],
            ];
        }
    }

    $stats = ['uptime' => $uptime, 'avg_lat' => $avgLat, 'uptime_by_ip' => $uptimeByIp];
    dash_cache_set($cacheKey, $stats);
    return $stats;
}

/**
 * Retorna dados do gráfico de latência por hora (com cache 30s, por range).
 */
function get_dash_chart_cached(PDO $pdo, int $userId, int $rangeHours): array {
    $cacheKey = 'dash_chart_' . $userId . '_' . $rangeHours;
    $cached   = dash_cache_get($cacheKey);
    if ($cached !== null) return $cached;

    // Intervalo de bucket em minutos por range — ~24 pontos no gráfico
    $bucketMinutes = match($rangeHours) {
        3  => 8,
        6  => 15,
        12 => 30,
        default => 60,
    };
    $totalSlots = (int)round(($rangeHours * 60) / $bucketMinutes);

    $utc       = new DateTimeZone('UTC');
    $now       = new DateTimeImmutable('now', $utc);
    $startTime = $now->modify("-{$rangeHours} hour");

    // Alinha ao mesmo origin que date_bin usa (2000-01-01 00:00:00 UTC)
    $originTs    = (new DateTimeImmutable('2000-01-01 00:00:00', $utc))->getTimestamp();
    $bucketSecs  = $bucketMinutes * 60;
    $startTs     = $startTime->getTimestamp();
    $nowTs       = $now->getTimestamp();
    $firstOffset = (int)floor(($startTs - $originTs) / $bucketSecs) * $bucketSecs;
    $firstSlotTs = $originTs + $firstOffset;

    // Gera todos os slots vazios alinhados com date_bin
    $labels  = [];
    $buckets = [];
    for ($ts = $firstSlotTs; $ts <= $nowTs; $ts += $bucketSecs) {
        $slotDt = new DateTimeImmutable('@' . $ts);
        $slotDt = $slotDt->setTimezone($utc);
        $key    = $slotDt->format('Y-m-d H:i:s');
        $buckets[$key] = null;
        $labels[]      = $slotDt->format('H:i');
    }

    // Lê direto do ping_logs usando date_bin para granularidade real
    $stmt = $pdo->prepare("
        SELECT to_char(
                   date_bin(
                       :bkt * INTERVAL '1 minute',
                       pl.checked_at AT TIME ZONE 'UTC',
                       TIMESTAMP '2000-01-01'
                   ),
                   'YYYY-MM-DD HH24:MI:SS'
               ) AS slot,
               AVG(pl.latency_ms) FILTER (WHERE pl.status = 'online') AS avg_latency
        FROM ping_logs pl
        JOIN monitored_ips mi ON mi.id = pl.monitored_ip_id
        WHERE mi.user_id = :uid
          AND pl.checked_at >= NOW() - :hrs * INTERVAL '1 hour'
        GROUP BY 1
        ORDER BY 1
    ");
    $stmt->execute([':bkt' => $bucketMinutes, ':uid' => $userId, ':hrs' => $rangeHours]);
    while ($row = $stmt->fetch()) {
        $key = $row['slot']; // já no formato 'YYYY-MM-DD HH24:MI:SS'
        if (array_key_exists($key, $buckets)) {
            $buckets[$key] = $row['avg_latency'] !== null
                ? round((float)$row['avg_latency'], 2)
                : null;
        }
    }

    $result = ['labels' => $labels, 'values' => array_values($buckets)];
    dash_cache_set($cacheKey, $result);
    return $result;
}

// ── 2FA TOTP (RFC 6238) — sem dependências externas ─────────────────────────

function totp_base32_decode(string $s): string {
    $alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
    $s = strtoupper(str_replace(['=', ' '], '', $s));
    $bits = '';
    for ($i = 0; $i < strlen($s); $i++) {
        $val = strpos($alphabet, $s[$i]);
        if ($val === false) continue;
        $bits .= str_pad(decbin($val), 5, '0', STR_PAD_LEFT);
    }
    $bytes = '';
    for ($i = 0; $i + 8 <= strlen($bits); $i += 8) {
        $bytes .= chr(bindec(substr($bits, $i, 8)));
    }
    return $bytes;
}

function totp_generate_secret(int $length = 16): string {
    $chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
    $secret = '';
    for ($i = 0; $i < $length; $i++) {
        $secret .= $chars[random_int(0, 31)];
    }
    return $secret;
}

function totp_get_code(string $secret, int $offset = 0): string {
    $key     = totp_base32_decode($secret);
    $counter = floor(time() / 30) + $offset;
    $time    = pack('N*', 0) . pack('N*', $counter);
    $hash    = hash_hmac('sha1', $time, $key, true);
    $ofs     = ord($hash[19]) & 0x0f;
    $code    = (
        ((ord($hash[$ofs])   & 0x7f) << 24) |
        ((ord($hash[$ofs+1]) & 0xff) << 16) |
        ((ord($hash[$ofs+2]) & 0xff) <<  8) |
        ( ord($hash[$ofs+3]) & 0xff)
    ) % 1000000;
    return str_pad($code, 6, '0', STR_PAD_LEFT);
}

function totp_verify(string $secret, string $code): bool {
    $code = preg_replace('/\D/', '', $code);
    if (strlen($code) !== 6) return false;
    for ($offset = -1; $offset <= 1; $offset++) {
        if (hash_equals(totp_get_code($secret, $offset), $code)) return true;
    }
    return false;
}

// ────────────────────────────────────────────────────────────────────────────

function clear_rate_limit_ip(PDO $pdo, string $action): void {
    $ip = get_client_ip();
    $pdo->prepare("DELETE FROM rate_limits WHERE ip = ? AND action = ?")->execute([$ip, $action]);
}

/**
 * Formata uma datetime string aplicando o fuso horário do usuário logado.
 * $format: formato PHP date(), padrão 'd/m/Y H:i:s'
 */
function format_datetime_tz(?string $datetime, ?string $timezone = null, string $format = 'd/m/Y H:i:s'): string {
    if (!$datetime) return '—';
    if ($timezone === null) {
        $u = function_exists('current_user') ? current_user() : null;
        $timezone = $u['timezone'] ?? 'America/Sao_Paulo';
    }
    $tz = $timezone;
    if (!in_array($tz, timezone_identifiers_list(), true)) {
        $tz = 'America/Sao_Paulo';
    }
    try {
        $dt = new DateTimeImmutable($datetime, new DateTimeZone('UTC'));
        return $dt->setTimezone(new DateTimeZone($tz))->format($format);
    } catch (Throwable $e) {
        return $datetime;
    }
}
