path: root/partials/traffic.php

<?php
/**
 * Traffic log helpers — privacy-friendly hit storage for sillylaird.ca.
 *
 * DB path: /var/lib/sillylaird/traffic.sqlite (outside the document root).
 * Never write client IPs into this database.
 */

declare(strict_types=1);

const TRAFFIC_DB = '/var/lib/sillylaird/traffic.sqlite';
const TRAFFIC_HIT_RETENTION_DAYS = 90;
const TRAFFIC_MAX_RECENT = 5000;

function traffic_host_allowed(string $host): bool {
    $host = strtolower(trim($host));
    if ($host === '' || str_contains($host, '/') || str_contains($host, ' ')) {
        return false;
    }
    // Strip port if present
    if (str_contains($host, ':')) {
        $host = explode(':', $host, 2)[0];
    }
    return $host === 'sillylaird.ca'
        || $host === 'www.sillylaird.ca'
        || str_ends_with($host, '.sillylaird.ca');
}

function traffic_normalize_path(string $path): string {
    $path = trim($path);
    if ($path === '') return '/';
    // Drop query/fragment if a full URL or path?query was sent
    $path = preg_replace('/[?#].*$/', '', $path) ?? $path;
    if ($path === '' || $path[0] !== '/') {
        $path = '/' . ltrim($path, '/');
    }
    // Collapse //
    $path = preg_replace('#/+#', '/', $path) ?? $path;
    $path = rtrim($path, '/') ?: '/';
    // /foo/index.php and lang variants → /foo
    $path = preg_replace('#/index(?:_[a-z]{2})?\.php$#i', '', $path) ?? $path;
    $path = $path === '' ? '/' : $path;
    // /links_jp.php → /links.php
    $path = preg_replace('#_(jp|zh)\.php$#i', '.php', $path) ?? $path;
    // Block path traversal leftovers
    if (str_contains($path, '..')) return '/';
    return $path;
}

function traffic_page_key(string $host, string $path): string {
    $host = strtolower(trim($host));
    if (str_contains($host, ':')) {
        $host = explode(':', $host, 2)[0];
    }
    return $host . traffic_normalize_path($path);
}

/**
 * True when the User-Agent looks like a crawler, scraper, previewer, or monitor.
 * Checked before browser matching so bots that pretend to be Chrome/etc. are not
 * counted as normal visitors.
 */
function traffic_is_bot(string $ua): bool {
    $ua = trim($ua);
    if ($ua === '') {
        // Empty UA is almost never a real browser hit from our JS beacon.
        return true;
    }
    return (bool)preg_match(
        '/(?:'
        . 'bot|crawler|crawl|spider|slurp|fetcher|scraper|scrapy'
        . '|bingpreview|facebookexternalhit|facebot|ia_archiver|twitterbot'
        . '|linkedinbot|discordbot|telegrambot|slackbot|applebot'
        . '|googlebot|adsbot|mediapartners-google|apis-google|feedfetcher'
        . '|yandex|baiduspider|duckduckbot|semrush|ahrefs|mj12bot|dotbot'
        . '|petalbot|bytespider|gptbot|chatgpt|claudebot|anthropic|ccbot'
        . '|amazonbot|meta-externalagent|omgilibot|imagesiftbot'
        . '|wget|curl|python-requests|python-urllib|httpx|libwww|httpclient'
        . '|go-http-client|java\/|okhttp|php\/|node-fetch|axios\/'
        . '|headlesschrome|phantomjs|selenium|puppeteer|playwright'
        . '|uptimerobot|pingdom|statuscake|site24x7|check_http'
        . '|pagespeed|gtmetrix'
        . ')/i',
        $ua
    );
}

/** True if a stored visitor label is a bot (new + legacy rows). */
function traffic_visitor_is_bot(string $visitor, ?int $isBotFlag = null): bool {
    if ($isBotFlag !== null) {
        return $isBotFlag === 1;
    }
    $v = strtolower(trim($visitor));
    if ($v === '' || $v === 'unknown visitor') {
        return false;
    }
    return $v === 'bot' || str_starts_with($v, 'bot ·') || str_starts_with($v, 'bot ');
}

/**
 * Coarse browser · OS label from User-Agent. Intentionally low-resolution —
 * not a fingerprint, not an IP, and not the raw UA string.
 * Bots are labeled separately (never as a normal browser · OS pair).
 */
function traffic_visitor_label(string $ua): string {
    $ua = trim($ua);
    if ($ua === '') return 'Bot · empty-ua';

    // Bots first — do not classify them as Chrome/Firefox/etc.
    if (traffic_is_bot($ua)) {
        if (preg_match('/googlebot|adsbot-google|mediapartners-google/i', $ua)) return 'Bot · Google';
        if (preg_match('/bingbot|bingpreview|msnbot/i', $ua)) return 'Bot · Bing';
        if (preg_match('/yandex/i', $ua)) return 'Bot · Yandex';
        if (preg_match('/baiduspider/i', $ua)) return 'Bot · Baidu';
        if (preg_match('/duckduckbot/i', $ua)) return 'Bot · DuckDuckGo';
        if (preg_match('/applebot/i', $ua)) return 'Bot · Apple';
        if (preg_match('/facebookexternalhit|facebot|meta-externalagent/i', $ua)) return 'Bot · Meta';
        if (preg_match('/twitterbot/i', $ua)) return 'Bot · Twitter';
        if (preg_match('/discordbot/i', $ua)) return 'Bot · Discord';
        if (preg_match('/slackbot/i', $ua)) return 'Bot · Slack';
        if (preg_match('/telegrambot/i', $ua)) return 'Bot · Telegram';
        if (preg_match('/linkedinbot/i', $ua)) return 'Bot · LinkedIn';
        if (preg_match('/gptbot|chatgpt|claudebot|anthropic|ccbot/i', $ua)) return 'Bot · AI';
        if (preg_match('/semrush|ahrefs|mj12bot|dotbot|petalbot|bytespider/i', $ua)) return 'Bot · SEO';
        if (preg_match('/wget|curl|python-|httpx|libwww|go-http-client|java\/|okhttp|php\//i', $ua)) return 'Bot · script';
        if (preg_match('/uptimerobot|pingdom|statuscake|site24x7|check_http|monitoring/i', $ua)) return 'Bot · monitor';
        return 'Bot';
    }

    $browser = 'Browser';
    if (preg_match('/Edg(?:e|A|iOS)?\//i', $ua)) $browser = 'Edge';
    elseif (preg_match('/OPR\/|Opera/i', $ua)) $browser = 'Opera';
    elseif (preg_match('/SamsungBrowser\//i', $ua)) $browser = 'Samsung';
    elseif (preg_match('/Firefox\//i', $ua)) $browser = 'Firefox';
    elseif (preg_match('/CriOS\//i', $ua)) $browser = 'Chrome';
    elseif (preg_match('/Chrome\//i', $ua) && !preg_match('/Chromium/i', $ua)) $browser = 'Chrome';
    elseif (preg_match('/Chromium\//i', $ua)) $browser = 'Chromium';
    elseif (preg_match('/Safari\//i', $ua) && preg_match('/Version\//i', $ua)) $browser = 'Safari';
    elseif (preg_match('/MSIE |Trident\//i', $ua)) $browser = 'IE';

    $os = 'Unknown OS';
    if (preg_match('/Android/i', $ua)) $os = 'Android';
    elseif (preg_match('/iPhone|iPad|iPod/i', $ua)) $os = 'iOS';
    elseif (preg_match('/Windows NT/i', $ua)) $os = 'Windows';
    elseif (preg_match('/Mac OS X|Macintosh/i', $ua)) $os = 'macOS';
    elseif (preg_match('/CrOS/i', $ua)) $os = 'ChromeOS';
    elseif (preg_match('/Linux/i', $ua)) $os = 'Linux';
    elseif (preg_match('/FreeBSD/i', $ua)) $os = 'BSD';

    return $browser . ' · ' . $os;
}

function traffic_lang(string $acceptLang): string {
    $acceptLang = trim($acceptLang);
    if ($acceptLang === '') return '';
    // Primary tag only: "en-CA,en;q=0.9" → "en"
    if (preg_match('/^([a-zA-Z]{2,3})(?:[-_][a-zA-Z0-9]+)?/', $acceptLang, $m)) {
        return strtolower($m[1]);
    }
    return '';
}

function traffic_ref_host(string $ref): string {
    $ref = trim($ref);
    if ($ref === '') return '';
    $host = parse_url($ref, PHP_URL_HOST);
    if (!is_string($host) || $host === '') return '';
    $host = strtolower($host);
    // Don't store long junk
    return strlen($host) > 200 ? '' : $host;
}

function traffic_db(): PDO {
    static $pdo = null;
    if ($pdo instanceof PDO) return $pdo;

    $dir = dirname(TRAFFIC_DB);
    if (!is_dir($dir)) {
        @mkdir($dir, 02770, true);
    }

    $pdo = new PDO('sqlite:' . TRAFFIC_DB, null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    $pdo->exec('PRAGMA journal_mode=WAL');
    $pdo->exec('PRAGMA synchronous=NORMAL');
    $pdo->exec('PRAGMA busy_timeout=3000');

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS hits (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ts INTEGER NOT NULL,
  host TEXT NOT NULL,
  path TEXT NOT NULL,
  page_key TEXT NOT NULL,
  visitor TEXT NOT NULL DEFAULT '',
  lang TEXT NOT NULL DEFAULT '',
  ref_host TEXT NOT NULL DEFAULT '',
  local_count INTEGER NOT NULL DEFAULT 0,
  is_new INTEGER NOT NULL DEFAULT 0,
  is_bot INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_hits_ts ON hits(ts);
CREATE INDEX IF NOT EXISTS idx_hits_page ON hits(page_key);
CREATE INDEX IF NOT EXISTS idx_hits_host ON hits(host);

CREATE TABLE IF NOT EXISTS pages (
  page_key TEXT PRIMARY KEY,
  host TEXT NOT NULL,
  path TEXT NOT NULL,
  hits INTEGER NOT NULL DEFAULT 0,
  unique_sessions INTEGER NOT NULL DEFAULT 0,
  bot_hits INTEGER NOT NULL DEFAULT 0,
  last_hit INTEGER NOT NULL DEFAULT 0,
  last_visitor TEXT NOT NULL DEFAULT '',
  last_local_count INTEGER NOT NULL DEFAULT 0
);
SQL);

    // Migrations for DBs created before bot separation.
    $hitCols = $pdo->query('PRAGMA table_info(hits)')->fetchAll();
    $hitColNames = array_column($hitCols, 'name');
    if (!in_array('is_bot', $hitColNames, true)) {
        $pdo->exec('ALTER TABLE hits ADD COLUMN is_bot INTEGER NOT NULL DEFAULT 0');
        // Backfill: legacy "Bot" labels (and "Bot · …" if any) are bots.
        $pdo->exec(
            "UPDATE hits SET is_bot = 1
             WHERE lower(trim(visitor)) = 'bot'
                OR lower(visitor) LIKE 'bot ·%'
                OR lower(visitor) LIKE 'bot %'"
        );
        $pdo->exec('CREATE INDEX IF NOT EXISTS idx_hits_is_bot ON hits(is_bot)');
    }

    $pageCols = $pdo->query('PRAGMA table_info(pages)')->fetchAll();
    $pageColNames = array_column($pageCols, 'name');
    if (!in_array('bot_hits', $pageColNames, true)) {
        $pdo->exec('ALTER TABLE pages ADD COLUMN bot_hits INTEGER NOT NULL DEFAULT 0');
        // Move retained bot hits out of human page totals where we still have log rows.
        $pdo->exec(
            "UPDATE pages SET
               bot_hits = COALESCE((
                 SELECT COUNT(*) FROM hits h
                 WHERE h.page_key = pages.page_key AND h.is_bot = 1
               ), 0),
               hits = MAX(0, hits - COALESCE((
                 SELECT COUNT(*) FROM hits h
                 WHERE h.page_key = pages.page_key AND h.is_bot = 1
               ), 0)),
               unique_sessions = MAX(0, unique_sessions - COALESCE((
                 SELECT SUM(CASE WHEN h.is_new = 1 THEN 1 ELSE 0 END) FROM hits h
                 WHERE h.page_key = pages.page_key AND h.is_bot = 1
               ), 0))"
        );
        // Pages that only had bots (or were missing) still need a pages row for bot totals.
        $pdo->exec(
            "INSERT OR IGNORE INTO pages (page_key, host, path, hits, unique_sessions, bot_hits, last_hit, last_visitor, last_local_count)
             SELECT h.page_key, h.host, h.path, 0, 0, COUNT(*), MAX(h.ts), 'Bot', 0
             FROM hits h
             WHERE h.is_bot = 1
               AND NOT EXISTS (SELECT 1 FROM pages p WHERE p.page_key = h.page_key)
             GROUP BY h.page_key, h.host, h.path"
        );
    }

    return $pdo;
}

/**
 * @param array{host:string,path:string,visitor:string,lang:string,ref_host:string,local_count:int,is_new:int,is_bot?:int|bool} $row
 * @return array{page_key:string,hits:int,unique_sessions:int,bot_hits:int,is_bot:int}
 */
function traffic_record_hit(array $row): array {
    $host = strtolower(trim($row['host']));
    if (str_contains($host, ':')) {
        $host = explode(':', $host, 2)[0];
    }
    $path = traffic_normalize_path($row['path']);
    $pageKey = $host . $path;
    $now = time();
    $visitor = mb_substr((string)$row['visitor'], 0, 80);
    $lang = mb_substr((string)$row['lang'], 0, 16);
    $refHost = mb_substr((string)$row['ref_host'], 0, 200);
    $local = (int)$row['local_count'];
    $isNew = !empty($row['is_new']) ? 1 : 0;
    // Explicit flag wins; otherwise infer from visitor label.
    if (array_key_exists('is_bot', $row)) {
        $isBot = !empty($row['is_bot']) ? 1 : 0;
    } else {
        $isBot = traffic_visitor_is_bot($visitor) ? 1 : 0;
    }
    // Never treat bots as human uniques.
    if ($isBot) {
        $isNew = 0;
    }

    $db = traffic_db();
    $db->beginTransaction();
    try {
        $ins = $db->prepare(
            'INSERT INTO hits (ts, host, path, page_key, visitor, lang, ref_host, local_count, is_new, is_bot)
             VALUES (:ts, :host, :path, :pk, :vis, :lang, :ref, :lc, :new, :bot)'
        );
        $ins->execute([
            ':ts' => $now,
            ':host' => $host,
            ':path' => $path,
            ':pk' => $pageKey,
            ':vis' => $visitor,
            ':lang' => $lang,
            ':ref' => $refHost,
            ':lc' => $local,
            ':new' => $isNew,
            ':bot' => $isBot,
        ]);

        if ($isBot) {
            // Bots: log the hit, bump bot_hits only — never human hits / uniques.
            $up = $db->prepare(
                'INSERT INTO pages (page_key, host, path, hits, unique_sessions, bot_hits, last_hit, last_visitor, last_local_count)
                 VALUES (:pk, :host, :path, 0, 0, 1, :ts, :vis, :lc)
                 ON CONFLICT(page_key) DO UPDATE SET
                   bot_hits = bot_hits + 1,
                   last_hit = :ts,
                   last_visitor = :vis,
                   last_local_count = :lc'
            );
            $up->execute([
                ':pk' => $pageKey,
                ':host' => $host,
                ':path' => $path,
                ':ts' => $now,
                ':vis' => $visitor,
                ':lc' => $local,
            ]);
        } else {
            $up = $db->prepare(
                'INSERT INTO pages (page_key, host, path, hits, unique_sessions, bot_hits, last_hit, last_visitor, last_local_count)
                 VALUES (:pk, :host, :path, 1, :uniq, 0, :ts, :vis, :lc)
                 ON CONFLICT(page_key) DO UPDATE SET
                   hits = hits + 1,
                   unique_sessions = unique_sessions + :uniq,
                   last_hit = :ts,
                   last_visitor = :vis,
                   last_local_count = :lc'
            );
            $up->execute([
                ':pk' => $pageKey,
                ':host' => $host,
                ':path' => $path,
                ':uniq' => $isNew,
                ':ts' => $now,
                ':vis' => $visitor,
                ':lc' => $local,
            ]);
        }

        // Occasional prune of old hit rows (keep page totals).
        if (random_int(1, 50) === 1) {
            $cutoff = $now - (TRAFFIC_HIT_RETENTION_DAYS * 86400);
            $db->exec('DELETE FROM hits WHERE ts < ' . (int)$cutoff);
            // Cap absolute row count
            $cnt = (int)$db->query('SELECT COUNT(*) FROM hits')->fetchColumn();
            if ($cnt > TRAFFIC_MAX_RECENT) {
                $drop = $cnt - TRAFFIC_MAX_RECENT;
                $db->exec('DELETE FROM hits WHERE id IN (SELECT id FROM hits ORDER BY id ASC LIMIT ' . (int)$drop . ')');
            }
        }

        $stats = $db->prepare('SELECT hits, unique_sessions, bot_hits FROM pages WHERE page_key = :pk');
        $stats->execute([':pk' => $pageKey]);
        $s = $stats->fetch() ?: ['hits' => $isBot ? 0 : 1, 'unique_sessions' => $isBot ? 0 : $isNew, 'bot_hits' => $isBot ? 1 : 0];

        $db->commit();
        return [
            'page_key' => $pageKey,
            'hits' => (int)$s['hits'],
            'unique_sessions' => (int)$s['unique_sessions'],
            'bot_hits' => (int)($s['bot_hits'] ?? 0),
            'is_bot' => $isBot,
        ];
    } catch (Throwable $e) {
        if ($db->inTransaction()) $db->rollBack();
        throw $e;
    }
}

/** SQL fragment: human (non-bot) hits — works with is_bot column after migration. */
function traffic_human_where(string $alias = ''): string {
    $p = $alias !== '' ? $alias . '.' : '';
    return "({$p}is_bot = 0)";
}

/**
 * @return array{total_hits:int,total_pages:int,today:int,week:int,unique_sessions:int,bot_hits:int,bot_today:int,bot_week:int}
 */
function traffic_summary(): array {
    $db = traffic_db();
    $now = time();
    $day = $now - 86400;
    $week = $now - 7 * 86400;
    $human = traffic_human_where();
    return [
        // Human-only page totals (bots never increment pages.hits).
        'total_hits' => (int)$db->query('SELECT COALESCE(SUM(hits),0) FROM pages')->fetchColumn(),
        'total_pages' => (int)$db->query('SELECT COUNT(*) FROM pages')->fetchColumn(),
        'today' => (int)$db->query("SELECT COUNT(*) FROM hits WHERE ts >= {$day} AND {$human}")->fetchColumn(),
        'week' => (int)$db->query("SELECT COUNT(*) FROM hits WHERE ts >= {$week} AND {$human}")->fetchColumn(),
        'unique_sessions' => (int)$db->query('SELECT COALESCE(SUM(unique_sessions),0) FROM pages')->fetchColumn(),
        'bot_hits' => (int)$db->query('SELECT COALESCE(SUM(bot_hits),0) FROM pages')->fetchColumn(),
        'bot_today' => (int)$db->query("SELECT COUNT(*) FROM hits WHERE ts >= {$day} AND is_bot = 1")->fetchColumn(),
        'bot_week' => (int)$db->query("SELECT COUNT(*) FROM hits WHERE ts >= {$week} AND is_bot = 1")->fetchColumn(),
    ];
}

/** @return list<array<string,mixed>> */
function traffic_top_pages(int $limit = 50): array {
    $limit = max(1, min(200, $limit));
    $st = traffic_db()->query(
        'SELECT page_key, host, path, hits, unique_sessions, bot_hits, last_hit, last_visitor, last_local_count
         FROM pages ORDER BY hits DESC LIMIT ' . $limit
    );
    return $st->fetchAll();
}

/** @return list<array<string,mixed>> */
function traffic_recent(int $limit = 100): array {
    $limit = max(1, min(500, $limit));
    $st = traffic_db()->query(
        'SELECT id, ts, host, path, visitor, lang, ref_host, local_count, is_new, is_bot
         FROM hits ORDER BY id DESC LIMIT ' . $limit
    );
    return $st->fetchAll();
}

function traffic_clear_recent(): int {
    $db = traffic_db();
    $n = (int)$db->query('SELECT COUNT(*) FROM hits')->fetchColumn();
    $db->exec('DELETE FROM hits');
    return $n;
}

function traffic_reset_all(): void {
    $db = traffic_db();
    $db->exec('DELETE FROM hits');
    $db->exec('DELETE FROM pages');
}

/**
 * Daily hit counts for the last $days calendar days (UTC), zero-filled.
 * Humans and bots are separate series (bots never fold into hits/unique).
 * @return list<array{day:string,hits:int,unique:int,bots:int}>
 */
function traffic_hits_by_day(int $days = 30): array {
    $days = max(1, min(90, $days));
    $db = traffic_db();
    $now = time();
    $startDay = gmdate('Y-m-d', $now - ($days - 1) * 86400);
    $startTs = strtotime($startDay . ' 00:00:00 UTC') ?: ($now - ($days - 1) * 86400);

    $st = $db->prepare(
        'SELECT strftime(\'%Y-%m-%d\', ts, \'unixepoch\') AS day,
                SUM(CASE WHEN is_bot = 0 THEN 1 ELSE 0 END) AS hits,
                SUM(CASE WHEN is_bot = 0 AND is_new = 1 THEN 1 ELSE 0 END) AS uniq,
                SUM(CASE WHEN is_bot = 1 THEN 1 ELSE 0 END) AS bots
         FROM hits
         WHERE ts >= :start
         GROUP BY day
         ORDER BY day ASC'
    );
    $st->execute([':start' => $startTs]);
    $map = [];
    foreach ($st->fetchAll() as $row) {
        $map[$row['day']] = [
            'hits' => (int)$row['hits'],
            'unique' => (int)$row['uniq'],
            'bots' => (int)$row['bots'],
        ];
    }

    $out = [];
    for ($i = 0; $i < $days; $i++) {
        $day = gmdate('Y-m-d', $startTs + $i * 86400);
        $out[] = [
            'day' => $day,
            'hits' => $map[$day]['hits'] ?? 0,
            'unique' => $map[$day]['unique'] ?? 0,
            'bots' => $map[$day]['bots'] ?? 0,
        ];
    }
    return $out;
}

/**
 * Hourly hit counts for the last 24 hours (UTC), zero-filled.
 * @return list<array{hour:string,hits:int,unique:int,bots:int}>
 */
function traffic_hits_by_hour(int $hours = 24): array {
    $hours = max(1, min(48, $hours));
    $db = traffic_db();
    $now = time();
    // Align to hour start
    $endHour = intdiv($now, 3600) * 3600;
    $startTs = $endHour - ($hours - 1) * 3600;

    $st = $db->prepare(
        'SELECT strftime(\'%Y-%m-%d %H:00\', ts, \'unixepoch\') AS hour,
                SUM(CASE WHEN is_bot = 0 THEN 1 ELSE 0 END) AS hits,
                SUM(CASE WHEN is_bot = 0 AND is_new = 1 THEN 1 ELSE 0 END) AS uniq,
                SUM(CASE WHEN is_bot = 1 THEN 1 ELSE 0 END) AS bots
         FROM hits
         WHERE ts >= :start
         GROUP BY hour
         ORDER BY hour ASC'
    );
    $st->execute([':start' => $startTs]);
    $map = [];
    foreach ($st->fetchAll() as $row) {
        $map[$row['hour']] = [
            'hits' => (int)$row['hits'],
            'unique' => (int)$row['uniq'],
            'bots' => (int)$row['bots'],
        ];
    }

    $out = [];
    for ($i = 0; $i < $hours; $i++) {
        $t = $startTs + $i * 3600;
        $label = gmdate('Y-m-d H:00', $t);
        $out[] = [
            'hour' => $label,
            'hits' => $map[$label]['hits'] ?? 0,
            'unique' => $map[$label]['unique'] ?? 0,
            'bots' => $map[$label]['bots'] ?? 0,
        ];
    }
    return $out;
}

/**
 * Grouped counts from hits table.
 * @param bool $humansOnly When true (default), exclude bot hits from the breakdown.
 * @return list<array{label:string,count:int}>
 */
function traffic_breakdown(string $column, int $limit = 12, int $sinceTs = 0, bool $humansOnly = true): array {
    $allowed = ['visitor' => 'visitor', 'host' => 'host', 'lang' => 'lang', 'ref_host' => 'ref_host'];
    if (!isset($allowed[$column])) return [];
    $col = $allowed[$column];
    $limit = max(1, min(50, $limit));
    $db = traffic_db();

    $conds = [];
    if ($sinceTs > 0) $conds[] = 'ts >= ' . (int)$sinceTs;
    if ($humansOnly) $conds[] = 'is_bot = 0';
    $where = $conds ? ('WHERE ' . implode(' AND ', $conds)) : '';
    // Empty labels → "(direct)" / "(unknown)" for display later
    $sql = "SELECT CASE WHEN TRIM({$col}) = '' THEN '' ELSE {$col} END AS label,
                   COUNT(*) AS cnt
            FROM hits {$where}
            GROUP BY label
            ORDER BY cnt DESC
            LIMIT {$limit}";
    $rows = $db->query($sql)->fetchAll();
    $out = [];
    foreach ($rows as $r) {
        $label = (string)$r['label'];
        if ($label === '') {
            $label = $col === 'ref_host' ? '(direct / none)' : ($col === 'lang' ? '(unknown)' : '(unknown)');
        }
        $out[] = ['label' => $label, 'count' => (int)$r['cnt']];
    }
    return $out;
}

/**
 * Bot visitor breakdown (for admin bot chart / list).
 * @return list<array{label:string,count:int}>
 */
function traffic_bot_breakdown(int $limit = 10, int $sinceTs = 0): array {
    $limit = max(1, min(50, $limit));
    $db = traffic_db();
    $conds = ['is_bot = 1'];
    if ($sinceTs > 0) $conds[] = 'ts >= ' . (int)$sinceTs;
    $where = 'WHERE ' . implode(' AND ', $conds);
    $sql = "SELECT CASE WHEN TRIM(visitor) = '' THEN 'Bot' ELSE visitor END AS label,
                   COUNT(*) AS cnt
            FROM hits {$where}
            GROUP BY label
            ORDER BY cnt DESC
            LIMIT {$limit}";
    $rows = $db->query($sql)->fetchAll();
    $out = [];
    foreach ($rows as $r) {
        $out[] = ['label' => (string)$r['label'], 'count' => (int)$r['cnt']];
    }
    return $out;
}

/**
 * Bundle of series for the admin analytics charts.
 * Human traffic only in main series; bots are a separate series/KPI.
 * @return array<string,mixed>
 */
function traffic_analytics(): array {
    $now = time();
    $weekAgo = $now - 7 * 86400;
    return [
        'generated_at' => $now,
        'daily' => traffic_hits_by_day(30),
        'hourly' => traffic_hits_by_hour(24),
        'visitors' => traffic_breakdown('visitor', 10, $weekAgo, true),
        'bots' => traffic_bot_breakdown(10, $weekAgo),
        'hosts' => traffic_breakdown('host', 10, $weekAgo, true),
        'langs' => traffic_breakdown('lang', 8, $weekAgo, true),
        'refs' => traffic_breakdown('ref_host', 10, $weekAgo, true),
        'top_pages' => array_map(static function ($p) {
            return [
                'label' => $p['host'] . $p['path'],
                'count' => (int)$p['hits'],
                'unique' => (int)$p['unique_sessions'],
                'bots' => (int)($p['bot_hits'] ?? 0),
            ];
        }, traffic_top_pages(10)),
    ];
}