import live www.sillylaird.ca webrootHEADmain

1 files changed, 626 insertions, 0 deletions

diff --git a/partials/traffic.php b/partials/traffic.php
new file mode 100644
index 0000000..085b4a7
--- /dev/null
+++ b/partials/traffic.php
@@ -0,0 +1,626 @@
+<?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)),
+    ];
+}