<?php
/**
 * admin2/api/stats.php — Statistics page data.
 * Returns DB size, event counts, kind distribution, top pubkeys,
 * time-based stats, and event-rate delta (for the chart).
 */
require_once __DIR__ . '/../lib/helpers.php';

$pdo = db();

// Database size
$db_size = '-';
try {
    $bytes = $pdo->query("SELECT pg_database_size(current_database())")->fetchColumn();
    $db_size = format_bytes(intval($bytes));
} catch (PDOException $e) {}

// Total events
$total_events = intval($pdo->query("SELECT COUNT(*) FROM events")->fetchColumn());

// Event rate: events with first_seen in last 10 seconds
$events_delta = 0;
try {
    $events_delta = intval($pdo->query("SELECT COUNT(*) FROM events WHERE first_seen >= EXTRACT(EPOCH FROM NOW())::BIGINT - 10")->fetchColumn());
} catch (PDOException $e) {}

// Process info
$process_id = '-';
try {
    $pid = $pdo->query("SELECT pg_backend_pid()")->fetchColumn();
    $process_id = strval($pid);
} catch (PDOException $e) {}

// WebSocket connections: tracked in-memory by the relay (g_connection_count).
// The subscriptions table is a 32M-row historical log, not a live state table.
// Use pg_class for an instant estimate (updated by autovacuum).
$ws_connections = 0;
try {
    $est = intval($pdo->query("SELECT reltuples FROM pg_class WHERE relname = 'subscriptions'")->fetchColumn());
    // ~60% are 'created', ~40% are 'closed' — active ≈ created - closed
    $ws_connections = max(0, intval($est * 0.2));
} catch (PDOException $e) {}

// Active subscriptions: same estimate approach.
$active_subscriptions = $ws_connections;

// Memory/CPU (from /proc on Linux)
$memory_usage = '-';
$cpu_usage = '-';
$cpu_core = '-';
$mem_total = 0;
$mem_avail = 0;
$meminfo = @file_get_contents('/proc/meminfo');
if ($meminfo !== false) {
    // /proc/meminfo uses "Key:       Value kB" format (colon, not equals sign)
    foreach (explode("\n", $meminfo) as $line) {
        if (preg_match('/^MemTotal:\s+(\d+)\s+kB/i', $line, $m)) {
            $mem_total = intval($m[1]) * 1024;
        } elseif (preg_match('/^MemAvailable:\s+(\d+)\s+kB/i', $line, $m)) {
            $mem_avail = intval($m[1]) * 1024;
        }
    }
}
if ($mem_total > 0 && $mem_avail > 0) {
    $memory_usage = format_bytes($mem_total - $mem_avail) . ' / ' . format_bytes($mem_total);
} elseif ($mem_total > 0) {
    $memory_usage = format_bytes($mem_total);
}
if (is_readable('/proc/cpuinfo')) {
    $cores = intval(@shell_exec('nproc 2>/dev/null') ?: 1);
    $cpu_core = strval($cores) . ' cores';
}
$cpu_usage = @file_get_contents('/proc/loadavg');
if ($cpu_usage !== false) $cpu_usage = trim(explode(' ', $cpu_usage)[0] ?? '-');

// Oldest / newest event (single query)
$oldest_event = '-';
$newest_event = '-';
try {
    $row = $pdo->query("SELECT MIN(created_at) AS oldest, MAX(created_at) AS newest FROM events")->fetch();
    if ($row && $row['oldest']) {
        $oldest_event = date('Y-m-d H:i:s', intval($row['oldest']));
    }
    if ($row && $row['newest']) {
        $newest_event = date('Y-m-d H:i:s', intval($row['newest']));
    }
} catch (PDOException $e) {}

// Time-based stats
$now = time();
$events_24h = 0; $events_7d = 0; $events_30d = 0;
try {
    $events_24h = intval($pdo->query("SELECT COUNT(*) FROM events WHERE created_at >= $now - 86400")->fetchColumn());
    $events_7d  = intval($pdo->query("SELECT COUNT(*) FROM events WHERE created_at >= $now - 604800")->fetchColumn());
    $events_30d = intval($pdo->query("SELECT COUNT(*) FROM events WHERE created_at >= $now - 2592000")->fetchColumn());
} catch (PDOException $e) {}

// Kind distribution — cached to file, refreshed every 5 minutes
$kinds = [];
$cache_dir = __DIR__ . '/../cache';
if (!is_dir($cache_dir)) @mkdir($cache_dir, 0755, true);
$kinds_cache = $cache_dir . '/stats_kinds.json';
$kinds_ttl = 300;
if (is_readable($kinds_cache) && (time() - filemtime($kinds_cache)) < $kinds_ttl) {
    $kinds = json_decode(file_get_contents($kinds_cache), true) ?: [];
} else {
    try {
        $rows = $pdo->query("SELECT kind, COUNT(*) AS cnt FROM events GROUP BY kind ORDER BY cnt DESC LIMIT 20")->fetchAll();
        foreach ($rows as $r) {
            $kinds[] = ['kind' => intval($r['kind']), 'count' => intval($r['cnt']), 'pct' => $total_events > 0 ? round(intval($r['cnt']) / $total_events * 100, 1) : 0];
        }
        @file_put_contents($kinds_cache, json_encode($kinds));
    } catch (PDOException $e) {}
}

// Top pubkeys — cached to file, refreshed every 5 minutes
$top_pubkeys = [];
$pubkeys_cache = $cache_dir . '/stats_pubkeys.json';
if (is_readable($pubkeys_cache) && (time() - filemtime($pubkeys_cache)) < $kinds_ttl) {
    $top_pubkeys = json_decode(file_get_contents($pubkeys_cache), true) ?: [];
} else {
    try {
        $rows = $pdo->query("
            SELECT pubkey, COUNT(*) AS cnt
            FROM events
            GROUP BY pubkey
            ORDER BY cnt DESC LIMIT 20
        ")->fetchAll();
        $pubkeys = array_column($rows, 'pubkey');
        $pmap = profile_map($pubkeys);
        foreach ($rows as $r) {
            $pk = $r['pubkey'];
            $prof = $pmap[$pk] ?? null;
            $top_pubkeys[] = [
                'pubkey' => $pk,
                'name'   => $prof ? $prof['best_name'] : '',
                'count'  => intval($r['cnt']),
                'pct'    => $total_events > 0 ? round(intval($r['cnt']) / $total_events * 100, 1) : 0,
            ];
        }
        @file_put_contents($pubkeys_cache, json_encode($top_pubkeys));
    } catch (PDOException $e) {}
}

// Name-field usage stats (from profiles cache)
$name_field_usage = ['both' => 0, 'name_only' => 0, 'display_only' => 0, 'neither' => 0, 'both_differ' => 0, 'total' => 0];
try {
    $row = $pdo->query("
        SELECT count(*) FILTER (WHERE name <> '' AND display_name <> '')  AS both,
               count(*) FILTER (WHERE name <> '' AND display_name =  '')  AS name_only,
               count(*) FILTER (WHERE name =  '' AND display_name <> '')  AS display_only,
               count(*) FILTER (WHERE name =  '' AND display_name =  '')  AS neither,
               count(*) FILTER (WHERE name <> '' AND display_name <> '' AND name <> display_name) AS both_differ,
               count(*) AS total
          FROM profiles
    ")->fetch();
    if ($row) {
        $name_field_usage = array_map('intval', $row);
    }
} catch (PDOException $e) {}

json_response([
    'db_size'             => $db_size,
    'total_events'        => $total_events,
    'events_delta'        => $events_delta,
    'process_id'          => $process_id,
    'ws_connections'      => $ws_connections,
    'active_subscriptions' => $active_subscriptions,
    'memory_usage'        => $memory_usage,
    'cpu_usage'           => $cpu_usage,
    'cpu_core'            => $cpu_core,
    'oldest_event'        => $oldest_event,
    'newest_event'        => $newest_event,
    'events_24h'          => $events_24h,
    'events_7d'           => $events_7d,
    'events_30d'          => $events_30d,
    'kinds'               => $kinds,
    'top_pubkeys'         => $top_pubkeys,
    'name_field_usage'    => $name_field_usage,
]);

/** Format bytes as human-readable. */
function format_bytes(int $bytes): string {
    if ($bytes < 1024) return $bytes . ' B';
    if ($bytes < 1048576) return round($bytes / 1024, 1) . ' KB';
    if ($bytes < 1073741824) return round($bytes / 1048576, 1) . ' MB';
    return round($bytes / 1073741824, 2) . ' GB';
}