<?php

declare(strict_types=1);

namespace PpPhp\Core\Analytics;

use PpPhp\Core\DB;
use PpPhp\Core\PlatformSettings;

/**
 * Who visited this installation, from where, on what, and how they moved through
 * it. A privacy-respecting, dependency-free analytics engine.
 *
 * Design decisions worth knowing:
 *
 *  1. **One row per visit, not per request.** A visit is recorded once per
 *     session, then its counters are updated. A compact per-page "hit" is written
 *     to pp_visitor_hits so a visitor's journey can be replayed without bloating
 *     the main table.
 *
 *  2. **Real location, offline.** Country/city/region come from an actual IP
 *     lookup in a local database ({@see GeoIp}) — not a guess from the browser's
 *     locale, which is why a Nepali visitor used to show as American. A CDN header
 *     or an opt-in cached API fill in when there is no local database.
 *
 *  3. **Real device, from Client Hints.** Browser/OS/device come from the
 *     Sec-CH-UA-* headers when present ({@see DeviceDetector}), with a User-Agent
 *     fallback. A small JS beacon adds what only the browser knows — timezone,
 *     screen, connection, engagement time — via {@see enrich()}.
 *
 *  4. **Privacy is a setting, not an afterthought.** IPs can be stored truncated,
 *     Do-Not-Track / Global-Privacy-Control are honoured, bots are flagged not
 *     hidden, and old rows prune themselves.
 *
 *  5. **Never fatal.** Analytics failing must never take a page down, so every
 *     public entry point swallows its own errors.
 */
final class VisitorLog
{
    public const TABLE = 'pp_visitors';
    public const HITS_TABLE = 'pp_visitor_hits';

    public const SETTING = 'platform.visitor_log';
    private const SETTING_SCHEMA = 'platform.visitor_log_schema';
    private const SETTING_SERVER = 'platform.analytics_server_label';
    private const SCHEMA_VERSION = 5;   // v5: composite idx_human_seen + idx_site

    /** Channels that represent human browser traffic (vs API clients). */
    public const HUMAN_CHANNELS = ['console', 'marketing', 'community', 'app', ''];
    /** Channels that represent server-to-server API callers. */
    public const API_CHANNELS = ['license', 'update', 'api'];

    /** Max NEW visits one (site, IP) may create per minute via the public collector
     *  (untrusted browser reports). Bounds pixel spam; server reports are exempt. */
    private const COLLECT_RATE_PER_MIN = 60;

    public const DEFAULT_RETENTION_DAYS = 180;
    private const LIVE_WINDOW_MIN = 5;      // "online now" = seen within N minutes
    private const JOURNEY_CAP = 200;        // most hits kept per visit

    private static ?bool $ready = null;

    // ── Schema ────────────────────────────────────────────────────────

    public static function schemaSql(): string
    {
        return "CREATE TABLE IF NOT EXISTS `" . self::TABLE . "` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `visit_key` CHAR(64) NOT NULL,
            `visitor_id` CHAR(32) NOT NULL DEFAULT '',
            `is_return` TINYINT(1) NOT NULL DEFAULT 0,
            `channel` VARCHAR(16) NOT NULL DEFAULT 'console',
            `server` VARCHAR(60) NOT NULL DEFAULT '',
            `client_type` VARCHAR(10) NOT NULL DEFAULT '',
            `site_id` VARCHAR(40) NOT NULL DEFAULT '',
            `client_label` VARCHAR(190) NOT NULL DEFAULT '',
            `client_key` VARCHAR(64) NOT NULL DEFAULT '',
            `ok_count` INT UNSIGNED NOT NULL DEFAULT 0,
            `err_count` INT UNSIGNED NOT NULL DEFAULT 0,
            `ip` VARCHAR(64) NOT NULL DEFAULT '',
            `asn` INT UNSIGNED NOT NULL DEFAULT 0,
            `org` VARCHAR(160) NOT NULL DEFAULT '',
            `is_hosting` TINYINT(1) NOT NULL DEFAULT 0,
            `country` CHAR(2) NOT NULL DEFAULT '',
            `country_name` VARCHAR(80) NOT NULL DEFAULT '',
            `region` VARCHAR(120) NOT NULL DEFAULT '',
            `city` VARCHAR(120) NOT NULL DEFAULT '',
            `latitude` DOUBLE NULL,
            `longitude` DOUBLE NULL,
            `geo_source` VARCHAR(10) NOT NULL DEFAULT '',
            `geo_accuracy` VARCHAR(10) NOT NULL DEFAULT '',
            `timezone` VARCHAR(64) NOT NULL DEFAULT '',
            `language` VARCHAR(20) NOT NULL DEFAULT '',
            `languages` VARCHAR(120) NOT NULL DEFAULT '',
            `user_agent` VARCHAR(500) NOT NULL DEFAULT '',
            `browser` VARCHAR(60) NOT NULL DEFAULT '',
            `browser_version` VARCHAR(20) NOT NULL DEFAULT '',
            `engine` VARCHAR(20) NOT NULL DEFAULT '',
            `platform` VARCHAR(60) NOT NULL DEFAULT '',
            `platform_version` VARCHAR(20) NOT NULL DEFAULT '',
            `device` VARCHAR(20) NOT NULL DEFAULT '',
            `device_model` VARCHAR(80) NOT NULL DEFAULT '',
            `screen_w` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `screen_h` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `viewport_w` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `viewport_h` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `dpr` DECIMAL(3,1) NOT NULL DEFAULT 0,
            `color_depth` TINYINT UNSIGNED NOT NULL DEFAULT 0,
            `is_touch` TINYINT(1) NOT NULL DEFAULT 0,
            `cpu_cores` TINYINT UNSIGNED NOT NULL DEFAULT 0,
            `device_memory` DECIMAL(4,1) NOT NULL DEFAULT 0,
            `connection` VARCHAR(12) NOT NULL DEFAULT '',
            `prefers_dark` TINYINT NULL,
            `perf_load_ms` INT UNSIGNED NULL,
            `is_bot` TINYINT(1) NOT NULL DEFAULT 0,
            `dnt` TINYINT(1) NOT NULL DEFAULT 0,
            `enriched` TINYINT(1) NOT NULL DEFAULT 0,
            `referrer` VARCHAR(500) NOT NULL DEFAULT '',
            `referrer_domain` VARCHAR(160) NOT NULL DEFAULT '',
            `referrer_type` VARCHAR(12) NOT NULL DEFAULT '',
            `utm_source` VARCHAR(120) NOT NULL DEFAULT '',
            `utm_medium` VARCHAR(120) NOT NULL DEFAULT '',
            `utm_campaign` VARCHAR(160) NOT NULL DEFAULT '',
            `landing_path` VARCHAR(500) NOT NULL DEFAULT '',
            `last_path` VARCHAR(500) NOT NULL DEFAULT '',
            `page_views` INT UNSIGNED NOT NULL DEFAULT 1,
            `active_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
            `user_id` INT DEFAULT NULL,
            `username` VARCHAR(120) NOT NULL DEFAULT '',
            `first_seen` DATETIME NOT NULL,
            `last_seen` DATETIME NOT NULL,
            UNIQUE KEY `uniq_visit` (`visit_key`),
            INDEX `idx_first_seen` (`first_seen`),
            INDEX `idx_last_seen` (`last_seen`),
            INDEX `idx_country` (`country`),
            INDEX `idx_device` (`device`),
            INDEX `idx_bot` (`is_bot`),
            INDEX `idx_channel` (`channel`),
            INDEX `idx_visitor` (`visitor_id`),
            INDEX `idx_user` (`user_id`),
            INDEX `idx_human_seen` (`is_bot`, `channel`, `last_seen`),
            INDEX `idx_site` (`site_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    }

    public static function hitsSchemaSql(): string
    {
        return "CREATE TABLE IF NOT EXISTS `" . self::HITS_TABLE . "` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `visit_key` CHAR(64) NOT NULL,
            `path` VARCHAR(500) NOT NULL DEFAULT '',
            `title` VARCHAR(200) NOT NULL DEFAULT '',
            `status` SMALLINT UNSIGNED NULL,
            `dwell_ms` INT UNSIGNED NULL,
            `seen` DATETIME NOT NULL,
            INDEX `idx_visit` (`visit_key`),
            INDEX `idx_seen` (`seen`),
            INDEX `idx_path` (`path`(160))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    }

    public static function ensureSchema(): void
    {
        try {
            DB::Exec(self::schemaSql());
            DB::Exec(self::hitsSchemaSql());
            self::ensureColumns();
            self::ensureIndexes();
        } catch (\Throwable $e) { /* best effort */ }
    }

    /**
     * Add composite indexes a pre-existing pp_visitors table is missing. MySQL
     * before 8.0 has no "ADD INDEX IF NOT EXISTS", so diff against SHOW INDEX.
     * These turn the human-traffic filter + last_seen ordering (recent, liveNow,
     * pagination) and per-site lookups from full scans into index range scans as
     * the table grows past ~100k rows.
     */
    private static function ensureIndexes(): void
    {
        try {
            $want = [
                'idx_human_seen' => '`is_bot`, `channel`, `last_seen`',
                'idx_site'       => '`site_id`',
            ];
            $have = [];
            foreach ((array) DB::FetchAll('SHOW INDEX FROM `' . self::TABLE . '`') as $r) {
                $have[strtolower((string) ($r['Key_name'] ?? ''))] = true;
            }
            $add = [];
            foreach ($want as $name => $cols) {
                if (!isset($have[strtolower($name)])) $add[] = "ADD INDEX `{$name}` ({$cols})";
            }
            if ($add) DB::Exec('ALTER TABLE `' . self::TABLE . '` ' . implode(', ', $add));
        } catch (\Throwable $e) { /* older MySQL / perms — non-fatal */ }
    }

    /**
     * Add any columns a pre-existing pp_visitors table is missing. MySQL has no
     * "ADD COLUMN IF NOT EXISTS", so diff against SHOW COLUMNS and add the gaps.
     * Gated by a stored schema version so this runs once, not every request.
     */
    private static function ensureColumns(): void
    {
        try {
            $have = [];
            foreach ((array) DB::FetchAll('SHOW COLUMNS FROM `' . self::TABLE . '`') as $c) {
                $have[strtolower((string) ($c['Field'] ?? ''))] = true;
            }
            $add = [];
            foreach (self::columnDefs() as $name => $ddl) {
                if (!isset($have[strtolower($name)])) $add[] = "ADD COLUMN {$ddl}";
            }
            if ($add) DB::Exec('ALTER TABLE `' . self::TABLE . '` ' . implode(', ', $add));

            // The journey table gained a `status` column (HTTP status for API hits).
            $haveHits = [];
            foreach ((array) DB::FetchAll('SHOW COLUMNS FROM `' . self::HITS_TABLE . '`') as $c) {
                $haveHits[strtolower((string) ($c['Field'] ?? ''))] = true;
            }
            if (!isset($haveHits['status'])) {
                DB::Exec('ALTER TABLE `' . self::HITS_TABLE . '` ADD COLUMN `status` SMALLINT UNSIGNED NULL AFTER `title`');
            }
        } catch (\Throwable $e) { /* older MySQL / perms — non-fatal */ }
    }

    /** @return array<string,string> column name => full DDL fragment */
    private static function columnDefs(): array
    {
        return [
            'visitor_id'       => "`visitor_id` CHAR(32) NOT NULL DEFAULT ''",
            'is_return'        => "`is_return` TINYINT(1) NOT NULL DEFAULT 0",
            'channel'          => "`channel` VARCHAR(16) NOT NULL DEFAULT 'console'",
            'server'           => "`server` VARCHAR(60) NOT NULL DEFAULT ''",
            'client_type'      => "`client_type` VARCHAR(10) NOT NULL DEFAULT ''",
            'asn'              => "`asn` INT UNSIGNED NOT NULL DEFAULT 0",
            'org'              => "`org` VARCHAR(160) NOT NULL DEFAULT ''",
            'is_hosting'       => "`is_hosting` TINYINT(1) NOT NULL DEFAULT 0",
            'site_id'          => "`site_id` VARCHAR(40) NOT NULL DEFAULT ''",
            'client_label'     => "`client_label` VARCHAR(190) NOT NULL DEFAULT ''",
            'client_key'       => "`client_key` VARCHAR(64) NOT NULL DEFAULT ''",
            'ok_count'         => "`ok_count` INT UNSIGNED NOT NULL DEFAULT 0",
            'err_count'        => "`err_count` INT UNSIGNED NOT NULL DEFAULT 0",
            'region'           => "`region` VARCHAR(120) NOT NULL DEFAULT ''",
            'latitude'         => "`latitude` DOUBLE NULL",
            'longitude'        => "`longitude` DOUBLE NULL",
            'geo_source'       => "`geo_source` VARCHAR(10) NOT NULL DEFAULT ''",
            'geo_accuracy'     => "`geo_accuracy` VARCHAR(10) NOT NULL DEFAULT ''",
            'timezone'         => "`timezone` VARCHAR(64) NOT NULL DEFAULT ''",
            'languages'        => "`languages` VARCHAR(120) NOT NULL DEFAULT ''",
            'browser_version'  => "`browser_version` VARCHAR(20) NOT NULL DEFAULT ''",
            'engine'           => "`engine` VARCHAR(20) NOT NULL DEFAULT ''",
            'platform_version' => "`platform_version` VARCHAR(20) NOT NULL DEFAULT ''",
            'device_model'     => "`device_model` VARCHAR(80) NOT NULL DEFAULT ''",
            'screen_w'         => "`screen_w` SMALLINT UNSIGNED NOT NULL DEFAULT 0",
            'screen_h'         => "`screen_h` SMALLINT UNSIGNED NOT NULL DEFAULT 0",
            'viewport_w'       => "`viewport_w` SMALLINT UNSIGNED NOT NULL DEFAULT 0",
            'viewport_h'       => "`viewport_h` SMALLINT UNSIGNED NOT NULL DEFAULT 0",
            'dpr'              => "`dpr` DECIMAL(3,1) NOT NULL DEFAULT 0",
            'color_depth'      => "`color_depth` TINYINT UNSIGNED NOT NULL DEFAULT 0",
            'is_touch'         => "`is_touch` TINYINT(1) NOT NULL DEFAULT 0",
            'cpu_cores'        => "`cpu_cores` TINYINT UNSIGNED NOT NULL DEFAULT 0",
            'device_memory'    => "`device_memory` DECIMAL(4,1) NOT NULL DEFAULT 0",
            'connection'       => "`connection` VARCHAR(12) NOT NULL DEFAULT ''",
            'prefers_dark'     => "`prefers_dark` TINYINT NULL",
            'perf_load_ms'     => "`perf_load_ms` INT UNSIGNED NULL",
            'dnt'              => "`dnt` TINYINT(1) NOT NULL DEFAULT 0",
            'enriched'         => "`enriched` TINYINT(1) NOT NULL DEFAULT 0",
            'referrer_domain'  => "`referrer_domain` VARCHAR(160) NOT NULL DEFAULT ''",
            'referrer_type'    => "`referrer_type` VARCHAR(12) NOT NULL DEFAULT ''",
            'utm_source'       => "`utm_source` VARCHAR(120) NOT NULL DEFAULT ''",
            'utm_medium'       => "`utm_medium` VARCHAR(120) NOT NULL DEFAULT ''",
            'utm_campaign'     => "`utm_campaign` VARCHAR(160) NOT NULL DEFAULT ''",
            'active_seconds'   => "`active_seconds` INT UNSIGNED NOT NULL DEFAULT 0",
        ];
    }

    /** Version-gated schema ensure. Cheap after the first call in a request
     *  (static guard), and only touches the DB when the schema version changed —
     *  unlike ensureSchema() which runs 2 CREATE + 2 SHOW COLUMNS every time. */
    public static function ensureReady(): void
    {
        if (self::$ready === true) return;
        $version = 0;
        try {
            $version = (int) (PlatformSettings::get(self::SETTING_SCHEMA)['value'] ?? 0);
        } catch (\Throwable $e) { /* settings store unavailable — assume unmigrated */ }

        if ($version !== self::SCHEMA_VERSION) {
            self::ensureSchema();   // create tables + backfill columns (own try/catch)
            try { PlatformSettings::set(self::SETTING_SCHEMA, ['value' => self::SCHEMA_VERSION]); }
            catch (\Throwable $e) { /* will retry next request */ }
        }
        self::$ready = true;
    }

    // ── Settings ──────────────────────────────────────────────────────

    /** @return array{enabled:bool,retention_days:int,anonymise_ip:bool,log_bots:bool,honor_dnt:bool,track_journey:bool} */
    public static function settings(): array
    {
        $s = [];
        try {
            $raw = PlatformSettings::get(self::SETTING);
            if (is_array($raw)) $s = $raw;
        } catch (\Throwable $e) { /* no DB yet */ }

        return [
            'enabled'        => (bool) ($s['enabled'] ?? true),
            'retention_days' => max(0, (int) ($s['retention_days'] ?? self::DEFAULT_RETENTION_DAYS)),
            'anonymise_ip'   => (bool) ($s['anonymise_ip'] ?? false),
            'log_bots'       => (bool) ($s['log_bots'] ?? true),
            'honor_dnt'      => (bool) ($s['honor_dnt'] ?? true),
            'track_journey'  => (bool) ($s['track_journey'] ?? true),
        ];
    }

    public static function saveSettings(array $in): bool
    {
        try {
            return PlatformSettings::set(self::SETTING, [
                'enabled'        => !empty($in['enabled']),
                'retention_days' => max(0, min(3650, (int) ($in['retention_days'] ?? self::DEFAULT_RETENTION_DAYS))),
                'anonymise_ip'   => !empty($in['anonymise_ip']),
                'log_bots'       => !empty($in['log_bots']),
                'honor_dnt'      => !empty($in['honor_dnt']),
                'track_journey'  => !empty($in['track_journey']),
            ]);
        } catch (\Throwable $e) {
            return false;
        }
    }

    // ── Client Hints opt-in ───────────────────────────────────────────

    /**
     * Ask Chromium browsers to send the high-entropy Client Hints on the next
     * request. Called from the front controller before output. Safe to call even
     * when analytics is off — it costs nothing and helps the whole request.
     */
    public static function sendClientHintHeaders(): void
    {
        if (headers_sent()) return;
        $hints = 'Sec-CH-UA-Platform, Sec-CH-UA-Platform-Version, Sec-CH-UA-Mobile, '
               . 'Sec-CH-UA-Model, Sec-CH-UA-Full-Version-List, Sec-CH-UA-Arch, Sec-CH-UA-Bitness';
        header('Accept-CH: ' . $hints);
        header('Critical-CH: Sec-CH-UA-Platform-Version, Sec-CH-UA-Model');
        // Delegate the hints to self so they are sent on same-origin subrequests.
        header('Permissions-Policy: ch-ua-platform=(self), ch-ua-platform-version=(self), '
             . 'ch-ua-model=(self), ch-ua-mobile=(self), ch-ua-full-version-list=(self)', false);
    }

    // ── Recording ─────────────────────────────────────────────────────

    /**
     * Record the current request. Call once per page load, late enough that the
     * session exists. Silent and non-fatal by design.
     */
    public static function record(?string $path = null): void
    {
        try {
            $cfg = self::settings();
            if (!$cfg['enabled']) return;
            self::ensureReady();

            $ua  = substr((string) ($_SERVER['HTTP_USER_AGENT'] ?? ''), 0, 500);
            $bot = DeviceDetector::isBot($ua);
            if ($bot && !$cfg['log_bots']) return;

            $dnt    = self::dntSignalled();
            $honour = $cfg['honor_dnt'] && $dnt;

            $ip = self::clientIp();
            $storedIp = ($cfg['anonymise_ip'] || $honour) ? self::anonymise($ip) : $ip;

            $sid  = session_id();
            $seed = $sid !== '' ? 'sid:' . $sid : 'ipua:' . $ip . '|' . $ua;
            $key  = hash('sha256', $seed);

            $reqUri = (string) ($_SERVER['REQUEST_URI'] ?? '/');
            $path ??= (string) (parse_url($reqUri, PHP_URL_PATH) ?: '/');
            $path = substr($path, 0, 500);

            $user = null; $uname = '';
            if (!empty($_SESSION['user']['id'])) {
                $user  = (int) $_SESSION['user']['id'];
                $uname = substr((string) ($_SESSION['user']['username'] ?? ''), 0, 120);
            }

            $existing = DB::FetchAll('SELECT `id` FROM `' . self::TABLE . '` WHERE `visit_key` = ? LIMIT 1', [$key]);

            if (!empty($existing)) {
                DB::Query(
                    'UPDATE `' . self::TABLE . '` SET `page_views` = `page_views` + 1, `last_seen` = NOW(),
                         `last_path` = ?, `user_id` = COALESCE(?, `user_id`),
                         `username` = IF(? <> \'\', ?, `username`)
                       WHERE `visit_key` = ?',
                    [$path, $user, $uname, $uname, $key]
                );
                if ($cfg['track_journey'] && !$bot) self::addHit($key, $path);
                return;
            }

            // ── First sight of this visit — do the expensive work once ──
            $visitorId = self::visitorId();
            $isReturn  = self::isReturningVisitor($visitorId);

            $geo = GeoIp::resolve($ip, $_SERVER);
            $dev = DeviceDetector::detect($_SERVER);
            [$refDomain, $refType] = self::classifyReferrer((string) ($_SERVER['HTTP_REFERER'] ?? ''));
            $utm = self::parseUtm($reqUri);

            DB::Query(
                'INSERT INTO `' . self::TABLE . '`
                    (`visit_key`,`visitor_id`,`is_return`,`server`,`ip`,`country`,`country_name`,`region`,`city`,
                     `latitude`,`longitude`,`geo_source`,`geo_accuracy`,`asn`,`org`,`is_hosting`,`language`,`user_agent`,
                     `browser`,`browser_version`,`engine`,`platform`,`platform_version`,`device`,`device_model`,
                     `is_bot`,`dnt`,`referrer`,`referrer_domain`,`referrer_type`,
                     `utm_source`,`utm_medium`,`utm_campaign`,
                     `landing_path`,`last_path`,`page_views`,`user_id`,`username`,`first_seen`,`last_seen`)
                 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,?,?,NOW(),NOW())',
                [
                    $key, $visitorId, $isReturn ? 1 : 0, self::serverLabel(), substr($storedIp, 0, 64),
                    $geo['country'], $geo['country_name'], $geo['region'], $geo['city'],
                    $geo['latitude'], $geo['longitude'], $geo['source'], $geo['accuracy'],
                    (int) ($geo['asn'] ?? 0), (string) ($geo['org'] ?? ''), !empty($geo['hosting']) ? 1 : 0,
                    substr(self::language(), 0, 20), $ua,
                    $dev['browser'], $dev['browser_version'], $dev['engine'],
                    $dev['platform'], $dev['platform_version'], $dev['device'], $dev['device_model'],
                    $bot ? 1 : 0, $dnt ? 1 : 0,
                    substr((string) ($_SERVER['HTTP_REFERER'] ?? ''), 0, 500), $refDomain, $refType,
                    $utm['source'], $utm['medium'], $utm['campaign'],
                    $path, $path, $user, $uname,
                ]
            );

            if ($cfg['track_journey'] && !$bot) self::addHit($key, $path);

            if (random_int(1, 250) === 1) self::prune();
        } catch (\Throwable $e) {
            // analytics must never break a page
        }
    }

    /**
     * Enrich the current visit with what only the browser can measure: timezone,
     * screen, connection, engagement time, etc. Called by the JS beacon. Keyed by
     * the caller's OWN session, so a visitor can only ever touch their own row.
     *
     * @param array<string,mixed> $in decoded beacon payload
     */
    public static function enrich(array $in): bool
    {
        try {
            $cfg = self::settings();
            if (!$cfg['enabled']) return false;
            $sid = session_id();
            if ($sid === '') return false;
            $key = hash('sha256', 'sid:' . $sid);

            $set = [];
            $args = [];
            $put = function (string $col, $val) use (&$set, &$args) { $set[] = "`$col` = ?"; $args[] = $val; };

            if (isset($in['tz']) && is_string($in['tz']))            $put('timezone', substr($in['tz'], 0, 64));
            if (isset($in['langs']) && is_string($in['langs']))      $put('languages', substr($in['langs'], 0, 120));
            if (isset($in['sw']))  $put('screen_w', self::clampInt($in['sw'], 0, 65535));
            if (isset($in['sh']))  $put('screen_h', self::clampInt($in['sh'], 0, 65535));
            if (isset($in['vw']))  $put('viewport_w', self::clampInt($in['vw'], 0, 65535));
            if (isset($in['vh']))  $put('viewport_h', self::clampInt($in['vh'], 0, 65535));
            if (isset($in['dpr'])) $put('dpr', max(0, min(9.9, (float) $in['dpr'])));
            if (isset($in['cd']))  $put('color_depth', self::clampInt($in['cd'], 0, 64));
            if (isset($in['touch'])) $put('is_touch', !empty($in['touch']) ? 1 : 0);
            if (isset($in['cores'])) $put('cpu_cores', self::clampInt($in['cores'], 0, 255));
            if (isset($in['mem']))   $put('device_memory', max(0, min(999.9, (float) $in['mem'])));
            if (isset($in['conn']) && is_string($in['conn'])) $put('connection', substr($in['conn'], 0, 12));
            if (isset($in['dark']))  $put('prefers_dark', !empty($in['dark']) ? 1 : 0);
            if (isset($in['load']))  $put('perf_load_ms', self::clampInt($in['load'], 0, 4000000000));
            if (isset($in['active'])) $set[] = '`active_seconds` = GREATEST(`active_seconds`, ' . self::clampInt($in['active'], 0, 86400) . ')';

            // Automation is a bot even with a real-looking UA.
            if (!empty($in['wd'])) $put('is_bot', 1);

            // Timezone is a strong location signal; use it only to FILL a location
            // we could not resolve from the IP — never to override a real lookup.
            if (isset($in['tz']) && is_string($in['tz'])) {
                $tzCc = self::timezoneCountry($in['tz']);
                if ($tzCc !== '') {
                    $set[] = "`country` = IF(`country` = '', ?, `country`)"; $args[] = $tzCc;
                    $set[] = "`country_name` = IF(`country_name` = '', ?, `country_name`)"; $args[] = self::countryName($tzCc);
                    $set[] = "`geo_source` = IF(`geo_source` IN ('', 'none', 'private'), 'timezone', `geo_source`)";
                }
            }

            // Refine device class with signals the UA can't give: a touch screen
            // that's tablet-sized upgrades a "desktop" guess to "tablet".
            if (!empty($in['touch']) && isset($in['sw'])) {
                $sw = self::clampInt($in['sw'], 0, 65535);
                if ($sw > 0 && $sw <= 1280) {
                    $set[] = "`device` = IF(`device` = 'desktop', IF(? <= 820, 'mobile', 'tablet'), `device`)";
                    $args[] = $sw;
                }
            }

            $set[] = '`enriched` = 1';
            $args[] = $key;
            DB::Query('UPDATE `' . self::TABLE . '` SET ' . implode(', ', $set) . ' WHERE `visit_key` = ? LIMIT 1', $args);

            // Optional per-page dwell time from the beacon.
            if (!empty($in['path']) && isset($in['dwell'])) {
                self::updateDwell($key, (string) $in['path'], self::clampInt($in['dwell'], 0, 4000000000));
            }
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Record a pageview reported by a SEPARATE property (marketing site, community
     * portal…) via the collector. Unlike record() it takes its inputs explicitly
     * — the visitor's real IP and UA reach the collector directly, so geo/device
     * resolve normally — and keys the visit by site + the beacon's own session id
     * (there is no shared PHP session across origins).
     *
     * @param array<string,mixed> $ev   beacon payload
     * @param array<string,mixed> $server $_SERVER at the collector (for UA/Client-Hints)
     */
    public static function recordCollected(array $ev, string $channel, string $siteId, string $ip, array $server, bool $trusted = false): void
    {
        try {
            $cfg = self::settings();
            if (!$cfg['enabled']) return;
            self::ensureReady();

            $ua  = substr((string) ($server['HTTP_USER_AGENT'] ?? ''), 0, 500);
            $bot = DeviceDetector::isBot($ua);
            if ($bot && !$cfg['log_bots']) return;

            // An 'event' beacon is an engagement/heartbeat flush (fired on every
            // visibilitychange/pagehide) — it must NOT be counted as a page view or
            // append a journey hit, or one page with a few tab-switches reads as
            // several views. A genuine page view carries no type / type=pageview.
            $type = strtolower((string) ($ev['type'] ?? 'pageview'));
            $isEvent = $type === 'event';

            // Engagement time a browser may claim: cap hard for untrusted callers
            // (a public site_id is spoofable) — a real session isn't 24h.
            $activeCap = $trusted ? 86400 : 3600;

            $sid = (string) ($ev['sid'] ?? '');
            $vid = preg_match('/^[a-f0-9]{16,32}$/', (string) ($ev['vid'] ?? '')) ? (string) $ev['vid'] : '';
            $seed = $sid !== '' ? ('site:' . $siteId . ':' . $sid) : ('siteip:' . $siteId . ':' . $ip . '|' . $ua);
            $key = hash('sha256', $seed);

            $url  = (string) ($ev['url'] ?? '');
            $path = substr((string) (parse_url($url !== '' ? $url : (string) ($ev['path'] ?? '/'), PHP_URL_PATH) ?: '/'), 0, 500);

            $existing = DB::FetchAll('SELECT `id` FROM `' . self::TABLE . '` WHERE `visit_key` = ? LIMIT 1', [$key]);
            if (!empty($existing)) {
                if ($isEvent) {
                    // Enrichment-only: bump engagement + recency, never the view count.
                    DB::Query(
                        'UPDATE `' . self::TABLE . '` SET `last_seen` = NOW(),
                             `active_seconds` = GREATEST(`active_seconds`, ?) WHERE `visit_key` = ?',
                        [self::clampInt($ev['active'] ?? 0, 0, $activeCap), $key]
                    );
                    return;
                }
                DB::Query(
                    'UPDATE `' . self::TABLE . '` SET `page_views` = `page_views` + 1, `last_seen` = NOW(), `last_path` = ?,
                         `active_seconds` = GREATEST(`active_seconds`, ?) WHERE `visit_key` = ?',
                    [$path, self::clampInt($ev['active'] ?? 0, 0, $activeCap), $key]
                );
                if ($cfg['track_journey'] && !$bot) self::addHit($key, $path);
                return;
            }
            // An engagement event for a session we never saw a page view for is noise
            // (or a probe) — don't materialise a phantom zero-view visit from it.
            if ($isEvent) return;

            // Cheap anti-spam for the PUBLIC collector: bound how many NEW visits one
            // (site, IP) can mint per minute. Only the untrusted new-visit path pays
            // this one COUNT (indexed on first_seen); existing-visit updates and
            // trusted server reports are exempt. Fail-open so it can never break ingest.
            if (!$trusted) {
                try {
                    $rl = DB::FetchAll(
                        'SELECT COUNT(*) AS c FROM `' . self::TABLE . '`
                          WHERE `site_id` = ? AND `ip` = ? AND `first_seen` >= DATE_SUB(NOW(), INTERVAL 1 MINUTE)',
                        [substr($siteId, 0, 40), substr($ip, 0, 64)]
                    );
                    if ((int) ($rl[0]['c'] ?? 0) >= self::COLLECT_RATE_PER_MIN) return;
                } catch (\Throwable $e) { /* fail open */ }
            }

            $geo = GeoIp::resolve($ip, $server);
            $dev = DeviceDetector::detect($server);
            [$refDomain, $refType] = self::classifyReferrer((string) ($ev['ref'] ?? ''));
            $utm = self::parseUtm($url);
            $isReturn = $vid !== '' && self::isReturningVisitor($vid);
            $tz = substr((string) ($ev['tz'] ?? ''), 0, 64);
            if ($geo['country'] === '' && $tz !== '') {                    // timezone fills an unknown geo
                $cc = self::timezoneCountry($tz);
                if ($cc !== '') { $geo['country'] = $cc; $geo['country_name'] = self::countryName($cc); $geo['source'] = 'timezone'; }
            }

            // The "observing server" dimension may only be set by a TRUSTED
            // (secret-verified) report; a browser must never forge it.
            $srv = substr(($trusted && (string) ($ev['server'] ?? '') !== '') ? (string) $ev['server'] : self::serverLabel(), 0, 60);
            DB::Query(
                'INSERT INTO `' . self::TABLE . '`
                    (`visit_key`,`visitor_id`,`is_return`,`channel`,`server`,`site_id`,`ip`,`country`,`country_name`,`region`,`city`,
                     `latitude`,`longitude`,`geo_source`,`geo_accuracy`,`asn`,`org`,`is_hosting`,`timezone`,`languages`,`user_agent`,
                     `browser`,`browser_version`,`engine`,`platform`,`platform_version`,`device`,`device_model`,
                     `screen_w`,`screen_h`,`viewport_w`,`viewport_h`,`dpr`,`color_depth`,`is_touch`,`cpu_cores`,
                     `device_memory`,`connection`,`prefers_dark`,`perf_load_ms`,`is_bot`,`enriched`,
                     `referrer`,`referrer_domain`,`referrer_type`,`utm_source`,`utm_medium`,`utm_campaign`,
                     `landing_path`,`last_path`,`page_views`,`active_seconds`,`first_seen`,`last_seen`)
                 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())',
                [
                    $key, $vid, $isReturn ? 1 : 0, $channel, $srv, substr($siteId, 0, 40), substr($ip, 0, 64),
                    $geo['country'], $geo['country_name'], $geo['region'], $geo['city'],
                    $geo['latitude'], $geo['longitude'], $geo['source'], $geo['accuracy'],
                    (int) ($geo['asn'] ?? 0), (string) ($geo['org'] ?? ''), !empty($geo['hosting']) ? 1 : 0,
                    $tz, substr((string) ($ev['langs'] ?? ''), 0, 120), $ua,
                    $dev['browser'], $dev['browser_version'], $dev['engine'],
                    $dev['platform'], $dev['platform_version'], $dev['device'], $dev['device_model'],
                    self::clampInt($ev['sw'] ?? 0, 0, 65535), self::clampInt($ev['sh'] ?? 0, 0, 65535),
                    self::clampInt($ev['vw'] ?? 0, 0, 65535), self::clampInt($ev['vh'] ?? 0, 0, 65535),
                    max(0, min(9.9, (float) ($ev['dpr'] ?? 0))), self::clampInt($ev['cd'] ?? 0, 0, 64),
                    !empty($ev['touch']) ? 1 : 0, self::clampInt($ev['cores'] ?? 0, 0, 255),
                    max(0, min(999.9, (float) ($ev['mem'] ?? 0))), substr((string) ($ev['conn'] ?? ''), 0, 12),
                    isset($ev['dark']) ? (!empty($ev['dark']) ? 1 : 0) : null,
                    isset($ev['load']) ? self::clampInt($ev['load'], 0, 4000000000) : null,
                    $bot ? 1 : 0,
                    substr((string) ($ev['ref'] ?? ''), 0, 500), $refDomain, $refType,
                    $utm['source'], $utm['medium'], $utm['campaign'],
                    // A browser may only ever open ONE view on its first beacon; a
                    // multi-view `pv` is honoured only for trusted server reports.
                    $path, $path, $trusted ? max(1, self::clampInt($ev['pv'] ?? 1, 1, 10000)) : 1,
                    self::clampInt($ev['active'] ?? 0, 0, $activeCap),
                ]
            );
            if ($cfg['track_journey'] && !$bot) self::addHit($key, $path);
        } catch (\Throwable $e) {
            // never break the collector response
        }
    }

    // ── Journey hits ──────────────────────────────────────────────────

    private static function addHit(string $key, string $path, ?int $status = null): void
    {
        try {
            DB::Query(
                'INSERT INTO `' . self::HITS_TABLE . '` (`visit_key`,`path`,`status`,`seen`) VALUES (?,?,?,NOW())',
                [$key, substr($path, 0, 500), $status]
            );
            // Keep journeys bounded: occasionally trim the oldest beyond the cap.
            if (random_int(1, 40) === 1) {
                $n = (int) (DB::FetchAll('SELECT COUNT(*) AS c FROM `' . self::HITS_TABLE . '` WHERE `visit_key` = ?', [$key])[0]['c'] ?? 0);
                if ($n > self::JOURNEY_CAP) {
                    DB::Query(
                        'DELETE FROM `' . self::HITS_TABLE . '` WHERE `visit_key` = ? ORDER BY `seen` ASC LIMIT ' . ($n - self::JOURNEY_CAP),
                        [$key]
                    );
                }
            }
        } catch (\Throwable $e) { /* best effort */ }
    }

    private static function updateDwell(string $key, string $path, int $ms): void
    {
        try {
            DB::Query(
                'UPDATE `' . self::HITS_TABLE . '` SET `dwell_ms` = ? WHERE `visit_key` = ? AND `path` = ? AND `dwell_ms` IS NULL ORDER BY `seen` DESC LIMIT 1',
                [$ms, $key, substr($path, 0, 500)]
            );
        } catch (\Throwable $e) { /* best effort */ }
    }

    // ── Channels & API-client telemetry ───────────────────────────────

    /**
     * Which surface a request belongs to. The primary console serves the licence
     * API and update host itself, so those are classified by path; browser
     * traffic is 'console'. Marketing/community are separate deployments that
     * report in via the collector with their own channel, so they are never seen
     * here. 'collector' means the ingest endpoint itself — never self-logged.
     */
    public static function classifyChannel(string $path): string
    {
        $p = '/' . ltrim((string) (parse_url($path, PHP_URL_PATH) ?: $path), '/');
        if (str_starts_with($p, '/api/analytics')) return 'collector';
        if (str_starts_with($p, '/api/license'))   return 'license';
        if (str_starts_with($p, '/api/update') || str_starts_with($p, '/updates') || str_starts_with($p, '/update-host')) return 'update';
        if (str_starts_with($p, '/api/'))           return 'api';
        return 'console';
    }

    /** The action name from an API path: /api/license/sync -> 'sync'. */
    public static function apiAction(string $path): string
    {
        $p = trim((string) (parse_url($path, PHP_URL_PATH) ?: $path), '/');
        $seg = explode('/', $p);
        $last = (string) end($seg);
        return $last !== '' ? substr($last, 0, 40) : 'request';
    }

    /**
     * Record one server-to-server API query (a client install calling home). Keyed
     * by the client identity (an explicit key, else the IP) so repeated calls roll
     * up into ONE row per client with success/failure tallies and an action
     * history — "which install, from where, doing what, succeeding or failing".
     */
    public static function recordApiQuery(string $channel, string $action, int $status, string $clientLabel = '', string $clientKey = ''): void
    {
        try {
            $cfg = self::settings();
            if (!$cfg['enabled']) return;
            self::ensureReady();

            $ip  = self::clientIp();
            $idk = $clientKey !== '' ? $clientKey : $ip;
            $key = hash('sha256', 'api:' . $channel . ':' . $idk);
            $ok  = $status < 400 ? 1 : 0;
            $err = $ok ? 0 : 1;

            $existing = DB::FetchAll('SELECT `id` FROM `' . self::TABLE . '` WHERE `visit_key` = ? LIMIT 1', [$key]);
            if (!empty($existing)) {
                DB::Query(
                    'UPDATE `' . self::TABLE . '` SET `page_views` = `page_views` + 1, `ok_count` = `ok_count` + ?,
                         `err_count` = `err_count` + ?, `last_seen` = NOW(), `last_path` = ?,
                         `client_label` = IF(? <> \'\', ?, `client_label`)
                       WHERE `visit_key` = ?',
                    [$ok, $err, $action, $clientLabel, $clientLabel, $key]
                );
            } else {
                $geo = GeoIp::resolve($ip, $_SERVER);
                $label = $clientLabel !== '' ? $clientLabel : ($ip !== '' ? $ip : 'unknown');
                $ctype = self::classifyApiClient($ip, $clientLabel);
                DB::Query(
                    'INSERT INTO `' . self::TABLE . '`
                        (`visit_key`,`channel`,`server`,`client_type`,`client_label`,`client_key`,`ip`,`country`,`country_name`,`region`,`city`,
                         `geo_source`,`geo_accuracy`,`asn`,`org`,`is_hosting`,`browser`,`platform`,`device`,`is_bot`,`ok_count`,`err_count`,
                         `landing_path`,`last_path`,`page_views`,`first_seen`,`last_seen`)
                     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,?,?,?,?,1,NOW(),NOW())',
                    [
                        $key, $channel, self::serverLabel(), $ctype, substr($label, 0, 190), substr($clientKey, 0, 64), substr($ip, 0, 64),
                        $geo['country'], $geo['country_name'], $geo['region'], $geo['city'],
                        $geo['source'], $geo['accuracy'], (int) ($geo['asn'] ?? 0), (string) ($geo['org'] ?? ''), !empty($geo['hosting']) ? 1 : 0,
                        'API client', ucfirst($channel) . ' client', 'server', $ok, $err, $action, $action,
                    ]
                );
            }
            if ($cfg['track_journey']) self::addHit($key, $action, $status);
        } catch (\Throwable $e) {
            // analytics must never break an API response
        }
    }

    /**
     * A short label for the server that OBSERVED a piece of traffic, so the
     * dashboard can group by server (primary vs each backup). Owner-overridable;
     * defaults to the server role + hostname.
     */
    public static function serverLabel(): string
    {
        try {
            $bag = PlatformSettings::get(self::SETTING_SERVER);
            $v = is_array($bag) ? trim((string) ($bag['value'] ?? '')) : '';
            if ($v !== '') return substr($v, 0, 60);
        } catch (\Throwable $e) { /* fall through to a computed default */ }

        $role = 'primary';
        try {
            if (class_exists(\PpPhp\Core\Licensing\ServerRole::class) && \PpPhp\Core\Licensing\ServerRole::isBackup()) $role = 'backup';
        } catch (\Throwable $e) { /* assume primary */ }
        $host = strtolower((string) preg_replace('/:\d+$/', '', (string) ($_SERVER['HTTP_HOST'] ?? $_SERVER['SERVER_NAME'] ?? 'local')));
        return substr($role . ($host !== '' ? ' · ' . $host : ''), 0, 60);
    }

    public static function saveServerLabel(string $label): bool
    {
        try { return PlatformSettings::set(self::SETTING_SERVER, ['value' => substr(trim($label), 0, 60)]); }
        catch (\Throwable $e) { return false; }
    }

    /**
     * Is an API caller a known BACKUP server (in the licence peer mesh), an
     * end-CLIENT install, or this server itself? Lets the console split "licence
     * queries from clients" vs "from backup servers". Peer table exists only on
     * the main; elsewhere everything reads as 'client'.
     */
    private static function classifyApiClient(string $ip, string $label): string
    {
        static $peers = null;
        if ($peers === null) {
            $peers = ['ips' => [], 'hosts' => []];
            try {
                foreach ((array) DB::FetchAll("SELECT `url`,`last_ip` FROM `pp_license_peers` WHERE `status` IN ('active','suspended','pending')") as $p) {
                    $pi = trim((string) ($p['last_ip'] ?? ''));
                    if ($pi !== '') $peers['ips'][$pi] = 1;
                    $ph = strtolower((string) (parse_url((string) ($p['url'] ?? ''), PHP_URL_HOST) ?: ''));
                    if ($ph !== '') $peers['hosts'][$ph] = 1;
                }
            } catch (\Throwable $e) { /* no peer table here */ }
        }
        if ($ip !== '' && isset($peers['ips'][$ip])) return 'backup';
        $lh = $label;
        if (strpos($lh, '://') === false) $lh = 'http://' . $lh;
        $lh = strtolower((string) (parse_url($lh, PHP_URL_HOST) ?: ''));
        if ($lh !== '' && isset($peers['hosts'][$lh])) return 'backup';
        return 'client';
    }

    /** SQL fragment selecting human browser traffic (excludes API clients + bots). */
    private static function humanSql(string $alias = ''): string
    {
        $p = $alias !== '' ? $alias . '.' : '';
        return $p . '`is_bot` = 0 AND ' . self::humanChannelSql($alias);
    }

    /** SQL fragment restricting to human browser CHANNELS (bots not excluded).
     *  Derived from HUMAN_CHANNELS so the taxonomy lives in ONE place (const values
     *  are static, never user input — safe to inline). */
    private static function humanChannelSql(string $alias = ''): string
    {
        $p = $alias !== '' ? $alias . '.' : '';
        $list = implode(',', array_map(static fn(string $c): string => "'" . $c . "'", self::HUMAN_CHANNELS));
        return $p . '`channel` IN (' . $list . ')';
    }

    // ── Derivation helpers ────────────────────────────────────────────

    public static function clientIp(): string
    {
        foreach (['HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_X_REAL_IP', 'REMOTE_ADDR'] as $k) {
            $v = trim((string) ($_SERVER[$k] ?? ''));
            if ($v === '') continue;
            $v = trim(explode(',', $v)[0]);
            if (filter_var($v, FILTER_VALIDATE_IP)) return $v;
        }
        return '';
    }

    private static function dntSignalled(): bool
    {
        return trim((string) ($_SERVER['HTTP_DNT'] ?? '')) === '1'
            || trim((string) ($_SERVER['HTTP_SEC_GPC'] ?? '')) === '1';
    }

    /** A durable, anonymous visitor id in a first-party cookie (new vs returning). */
    private static function visitorId(): string
    {
        $cookie = (string) ($_COOKIE['ppv'] ?? '');
        if (preg_match('/^[a-f0-9]{32}$/', $cookie)) return $cookie;

        $id = bin2hex(random_bytes(16));
        if (!headers_sent()) {
            $secure = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off')
                   || (string) ($_SERVER['SERVER_PORT'] ?? '') === '443';
            @setcookie('ppv', $id, [
                'expires'  => time() + 31536000,
                'path'     => '/',
                'secure'   => $secure,
                'httponly' => true,
                'samesite' => 'Lax',
            ]);
            $_COOKIE['ppv'] = $id;
        }
        return $id;
    }

    private static function isReturningVisitor(string $visitorId): bool
    {
        if ($visitorId === '') return false;
        try {
            $r = DB::FetchAll('SELECT 1 FROM `' . self::TABLE . '` WHERE `visitor_id` = ? LIMIT 1', [$visitorId]);
            return !empty($r);
        } catch (\Throwable $e) {
            return false;
        }
    }

    /** Drop the host part so a stored address identifies a network, not a person. */
    private static function anonymise(string $ip): string
    {
        if ($ip === '') return '';
        if (str_contains($ip, ':')) {
            $p = explode(':', $ip);
            return implode(':', array_slice($p, 0, 3)) . '::';
        }
        $p = explode('.', $ip);
        return count($p) === 4 ? "{$p[0]}.{$p[1]}.{$p[2]}.0" : $ip;
    }

    /** @return array{0:string,1:string} [domain, type] */
    private static function classifyReferrer(string $ref): array
    {
        $ref = trim($ref);
        if ($ref === '') return ['', 'direct'];
        $host = strtolower((string) (parse_url($ref, PHP_URL_HOST) ?: ''));
        if ($host === '') return ['', 'direct'];

        $self = strtolower((string) preg_replace('/:\d+$/', '', (string) ($_SERVER['HTTP_HOST'] ?? '')));
        if ($self !== '' && ($host === $self || str_ends_with($host, '.' . $self))) return [$host, 'internal'];

        $search = 'google.|bing.com|search.yahoo|duckduckgo|yandex.|baidu.com|ecosia.|brave.com|startpage';
        $social = 'facebook.com|fb.com|t.co|twitter.com|x.com|instagram.com|linkedin.com|lnkd.in|reddit.com|'
                . 'youtube.com|youtu.be|pinterest.|t.me|whatsapp.com|tiktok.com|mastodon';
        if (preg_match('#' . $search . '#', $host)) return [$host, 'search'];
        if (preg_match('#' . $social . '#', $host)) return [$host, 'social'];
        return [$host, 'referral'];
    }

    /** @return array{source:string,medium:string,campaign:string} */
    private static function parseUtm(string $reqUri): array
    {
        $q = (string) (parse_url($reqUri, PHP_URL_QUERY) ?: '');
        $out = ['source' => '', 'medium' => '', 'campaign' => ''];
        if ($q === '') return $out;
        parse_str($q, $p);
        $out['source']   = substr((string) ($p['utm_source'] ?? ''), 0, 120);
        $out['medium']   = substr((string) ($p['utm_medium'] ?? ''), 0, 120);
        $out['campaign'] = substr((string) ($p['utm_campaign'] ?? ''), 0, 160);
        return $out;
    }

    private static function language(): string
    {
        $l = (string) ($_SERVER['HTTP_ACCEPT_LANGUAGE'] ?? '');
        return trim(explode(',', $l)[0]);
    }

    private static function clampInt($v, int $min, int $max): int
    {
        $n = (int) $v;
        return max($min, min($max, $n));
    }

    // ── Country / timezone naming ─────────────────────────────────────

    public static function countryName(string $code): string
    {
        static $map = [
            'NP' => 'Nepal', 'IN' => 'India', 'US' => 'United States', 'GB' => 'United Kingdom',
            'AU' => 'Australia', 'CA' => 'Canada', 'DE' => 'Germany', 'FR' => 'France',
            'NL' => 'Netherlands', 'AE' => 'United Arab Emirates', 'SA' => 'Saudi Arabia',
            'QA' => 'Qatar', 'JP' => 'Japan', 'CN' => 'China', 'SG' => 'Singapore',
            'MY' => 'Malaysia', 'BD' => 'Bangladesh', 'PK' => 'Pakistan', 'LK' => 'Sri Lanka',
            'TH' => 'Thailand', 'KR' => 'South Korea', 'ID' => 'Indonesia', 'PH' => 'Philippines',
            'BR' => 'Brazil', 'ZA' => 'South Africa', 'NG' => 'Nigeria', 'KE' => 'Kenya',
            'IT' => 'Italy', 'ES' => 'Spain', 'SE' => 'Sweden', 'NO' => 'Norway',
            'IE' => 'Ireland', 'NZ' => 'New Zealand', 'CH' => 'Switzerland', 'RU' => 'Russia',
            'TR' => 'Turkey', 'PL' => 'Poland', 'MX' => 'Mexico', 'HK' => 'Hong Kong',
            'BT' => 'Bhutan', 'MM' => 'Myanmar', 'VN' => 'Vietnam', 'TW' => 'Taiwan',
            'AT' => 'Austria', 'BE' => 'Belgium', 'DK' => 'Denmark', 'FI' => 'Finland',
            'PT' => 'Portugal', 'GR' => 'Greece', 'IL' => 'Israel', 'EG' => 'Egypt',
        ];
        return $map[strtoupper($code)] ?? strtoupper($code);
    }

    /** Minimal IANA timezone → ISO country, for filling an unknown IP location. */
    public static function timezoneCountry(string $tz): string
    {
        static $map = [
            'Asia/Kathmandu' => 'NP', 'Asia/Katmandu' => 'NP',
            'Asia/Kolkata' => 'IN', 'Asia/Calcutta' => 'IN',
            'Asia/Dhaka' => 'BD', 'Asia/Thimphu' => 'BT', 'Asia/Karachi' => 'PK',
            'Asia/Colombo' => 'LK', 'Asia/Kabul' => 'AF',
            'Asia/Dubai' => 'AE', 'Asia/Qatar' => 'QA', 'Asia/Riyadh' => 'SA',
            'Asia/Singapore' => 'SG', 'Asia/Kuala_Lumpur' => 'MY', 'Asia/Bangkok' => 'TH',
            'Asia/Jakarta' => 'ID', 'Asia/Manila' => 'PH', 'Asia/Tokyo' => 'JP',
            'Asia/Shanghai' => 'CN', 'Asia/Hong_Kong' => 'HK', 'Asia/Seoul' => 'KR',
            'Asia/Taipei' => 'TW', 'Asia/Yangon' => 'MM', 'Asia/Ho_Chi_Minh' => 'VN',
            'Asia/Jerusalem' => 'IL', 'Africa/Cairo' => 'EG',
            'Europe/London' => 'GB', 'Europe/Paris' => 'FR', 'Europe/Berlin' => 'DE',
            'Europe/Amsterdam' => 'NL', 'Europe/Madrid' => 'ES', 'Europe/Rome' => 'IT',
            'Europe/Moscow' => 'RU', 'Europe/Istanbul' => 'TR', 'Europe/Warsaw' => 'PL',
            'Europe/Dublin' => 'IE', 'Europe/Zurich' => 'CH', 'Europe/Stockholm' => 'SE',
            'America/New_York' => 'US', 'America/Chicago' => 'US', 'America/Denver' => 'US',
            'America/Los_Angeles' => 'US', 'America/Toronto' => 'CA', 'America/Vancouver' => 'CA',
            'America/Sao_Paulo' => 'BR', 'America/Mexico_City' => 'MX',
            'Australia/Sydney' => 'AU', 'Australia/Melbourne' => 'AU', 'Pacific/Auckland' => 'NZ',
            'Africa/Lagos' => 'NG', 'Africa/Nairobi' => 'KE', 'Africa/Johannesburg' => 'ZA',
        ];
        return $map[$tz] ?? '';
    }

    /** Flag emoji from an ISO code — pure arithmetic, no image assets. */
    public static function flag(string $code): string
    {
        if (!preg_match('/^[A-Za-z]{2}$/', $code)) return '🌐';
        $c = strtoupper($code);
        return mb_chr(0x1F1E6 + ord($c[0]) - 65, 'UTF-8') . mb_chr(0x1F1E6 + ord($c[1]) - 65, 'UTF-8');
    }

    // ── Reading, for the owner console ────────────────────────────────

    /**
     * Build the shared WHERE clause for recent()/recentCount() from a filter, so
     * the page rows and the total count always agree.
     * @return array{0:string,1:array<int,mixed>}  [" WHERE …" or "", args]
     */
    private static function recentWhere(array $filter): array
    {
        $where = []; $args = [];
        if (!empty($filter['human']))   $where[] = self::humanChannelSql();
        if (!empty($filter['channel'])) { $where[] = '`channel` = ?'; $args[] = (string) $filter['channel']; }
        if (!empty($filter['country'])) { $where[] = '`country` = ?'; $args[] = strtoupper((string) $filter['country']); }
        if (!empty($filter['device']))  { $where[] = '`device` = ?';  $args[] = (string) $filter['device']; }
        if (isset($filter['bots']) && !$filter['bots']) $where[] = '`is_bot` = 0';
        if (!empty($filter['humans_only'])) $where[] = '`is_bot` = 0';
        if (!empty($filter['returning'])) $where[] = '`is_return` = 1';
        if (!empty($filter['since'])) { $where[] = '`last_seen` >= ?'; $args[] = (string) $filter['since']; }
        if (!empty($filter['q'])) {
            $q = '%' . (string) $filter['q'] . '%';
            $where[] = '(`ip` LIKE ? OR `country_name` LIKE ? OR `city` LIKE ? OR `browser` LIKE ? OR `platform` LIKE ? OR `last_path` LIKE ? OR `username` LIKE ? OR `referrer_domain` LIKE ?)';
            array_push($args, $q, $q, $q, $q, $q, $q, $q, $q);
        }
        return [$where ? ' WHERE ' . implode(' AND ', $where) : '', $args];
    }

    /** @return array<int,array<string,mixed>> */
    public static function recent(int $limit = 200, array $filter = []): array
    {
        try {
            [$w, $args] = self::recentWhere($filter);
            $offset = max(0, (int) ($filter['offset'] ?? 0));
            $sql = 'SELECT * FROM `' . self::TABLE . '`' . $w
                 . ' ORDER BY `last_seen` DESC LIMIT ' . max(1, min(1000, $limit))
                 . ($offset > 0 ? ' OFFSET ' . $offset : '');
            return DB::FetchAll($sql, $args) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Total rows matching a recent() filter — for pagination. */
    public static function recentCount(array $filter = []): int
    {
        try {
            [$w, $args] = self::recentWhere($filter);
            $rows = DB::FetchAll('SELECT COUNT(*) AS c FROM `' . self::TABLE . '`' . $w, $args);
            return (int) ($rows[0]['c'] ?? 0);
        } catch (\Throwable $e) {
            return 0;
        }
    }

    /** One visit plus its page journey, for the drill-down. @return array<string,mixed> */
    public static function visitorDetail(int $id): array
    {
        try {
            $rows = DB::FetchAll('SELECT * FROM `' . self::TABLE . '` WHERE `id` = ? LIMIT 1', [$id]);
            if (empty($rows)) return [];
            $v = $rows[0];
            $v['journey'] = DB::FetchAll(
                'SELECT `path`,`title`,`dwell_ms`,`seen` FROM `' . self::HITS_TABLE . '` WHERE `visit_key` = ? ORDER BY `seen` ASC LIMIT ' . self::JOURNEY_CAP,
                [(string) $v['visit_key']]
            ) ?: [];
            return $v;
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Headline numbers for the dashboard. */
    public static function summary(): array
    {
        $out = ['total' => 0, 'humans' => 0, 'bots' => 0, 'today' => 0, 'week' => 0, 'month' => 0,
                'countries' => 0, 'page_views' => 0, 'returning' => 0, 'live' => 0,
                'avg_pages' => 0.0, 'avg_seconds' => 0, 'enriched' => 0,
                'api_clients' => 0, 'api_calls' => 0, 'api_err' => 0];
        $H = '(' . self::humanSql() . ')';   // human browser traffic
        $A = "`channel` IN ('license','update','api')";
        try {
            $r = DB::FetchAll(
                'SELECT COUNT(*) AS total,
                        SUM(' . $H . ') AS humans,
                        SUM(`is_bot` = 1) AS bots,
                        SUM(' . $H . ' AND DATE(`last_seen`) = CURDATE()) AS today,
                        SUM(' . $H . ' AND `last_seen` >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS week,
                        SUM(' . $H . ' AND `last_seen` >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS month,
                        COUNT(DISTINCT CASE WHEN ' . $H . ' THEN NULLIF(`country`, \'\') END) AS countries,
                        COALESCE(SUM(CASE WHEN ' . $H . ' THEN `page_views` END), 0) AS page_views,
                        SUM(' . $H . ' AND `is_return` = 1) AS returning,
                        SUM(`enriched` = 1) AS enriched,
                        SUM(' . $A . ') AS api_clients,
                        COALESCE(SUM(CASE WHEN ' . $A . ' THEN `page_views` END), 0) AS api_calls,
                        COALESCE(SUM(CASE WHEN ' . $A . ' THEN `err_count` END), 0) AS api_err,
                        AVG(CASE WHEN ' . $H . ' THEN `page_views` END) AS avg_pages,
                        AVG(CASE WHEN ' . $H . ' AND `active_seconds` > 0 THEN `active_seconds` END) AS avg_seconds
                   FROM `' . self::TABLE . '`'
            );
            foreach (['total','humans','bots','today','week','month','countries','page_views','returning','enriched','api_clients','api_calls','api_err'] as $k) {
                $out[$k] = (int) ($r[0][$k] ?? 0);
            }
            $out['avg_pages']   = round((float) ($r[0]['avg_pages'] ?? 0), 1);
            $out['avg_seconds'] = (int) round((float) ($r[0]['avg_seconds'] ?? 0));

            $live = DB::FetchAll(
                'SELECT COUNT(*) AS c FROM `' . self::TABLE . '` WHERE ' . $H . ' AND `last_seen` >= DATE_SUB(NOW(), INTERVAL ? MINUTE)',
                [self::LIVE_WINDOW_MIN]
            );
            $out['live'] = (int) ($live[0]['c'] ?? 0);
        } catch (\Throwable $e) { /* table not there yet */ }
        return $out;
    }

    /** Visitors active within the live window, for the "online now" panel. */
    public static function liveNow(int $limit = 30): array
    {
        try {
            return DB::FetchAll(
                'SELECT `id`,`country`,`country_name`,`city`,`browser`,`platform`,`device`,`last_path`,`last_seen`,`username`,`is_return`
                   FROM `' . self::TABLE . '`
                  WHERE ' . self::humanSql() . ' AND `last_seen` >= DATE_SUB(NOW(), INTERVAL ? MINUTE)
                  ORDER BY `last_seen` DESC LIMIT ' . max(1, min(200, $limit)),
                [self::LIVE_WINDOW_MIN]
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Visits grouped by country, most first. */
    public static function byCountry(int $limit = 20, bool $humansOnly = true): array
    {
        return self::groupBy('country', $limit, $humansOnly, ['extra' => '`country_name`', 'nonEmpty' => 'country']);
    }

    public static function byDevice(bool $humansOnly = true): array
    {
        return self::groupBy('device', 10, $humansOnly, ['nonEmpty' => 'device']);
    }

    public static function byBrowser(int $limit = 8, bool $humansOnly = true): array
    {
        return self::groupBy('browser', $limit, $humansOnly, ['nonEmpty' => 'browser']);
    }

    public static function byOS(int $limit = 8, bool $humansOnly = true): array
    {
        return self::groupBy('platform', $limit, $humansOnly, ['nonEmpty' => 'platform']);
    }

    public static function byReferrerType(bool $humansOnly = true): array
    {
        return self::groupBy('referrer_type', 8, $humansOnly, ['nonEmpty' => 'referrer_type']);
    }

    public static function topReferrers(int $limit = 10, bool $humansOnly = true): array
    {
        return self::groupBy('referrer_domain', $limit, $humansOnly, ['nonEmpty' => 'referrer_domain']);
    }

    /**
     * Generic "group by one column, count visits + page views" with an optional
     * extra selected column and a non-empty filter.
     *
     * @param array{extra?:string,nonEmpty?:string} $opt
     * @return array<int,array<string,mixed>>
     */
    private static function groupBy(string $col, int $limit, bool $humansOnly, array $opt = []): array
    {
        try {
            $where = [];
            if (!empty($opt['nonEmpty'])) $where[] = '`' . $opt['nonEmpty'] . "` <> ''";
            if ($humansOnly) $where[] = self::humanSql();
            $extra = !empty($opt['extra']) ? ', ' . $opt['extra'] : '';
            $group = '`' . $col . '`' . (!empty($opt['extra']) ? ', ' . $opt['extra'] : '');
            $sql = 'SELECT `' . $col . '` AS `key`' . $extra . ', COUNT(*) AS visits, COALESCE(SUM(`page_views`),0) AS views
                      FROM `' . self::TABLE . '`'
                 . ($where ? ' WHERE ' . implode(' AND ', $where) : '')
                 . ' GROUP BY ' . $group . ' ORDER BY visits DESC LIMIT ' . max(1, min(100, $limit));
            return DB::FetchAll($sql) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Landing pages (entry points), most first. */
    public static function entryPages(int $limit = 10): array
    {
        return self::pathCounts('landing_path', $limit);
    }

    /** Exit pages, most first. */
    public static function exitPages(int $limit = 10): array
    {
        return self::pathCounts('last_path', $limit);
    }

    private static function pathCounts(string $col, int $limit): array
    {
        try {
            return DB::FetchAll(
                'SELECT `' . $col . '` AS `key`, COUNT(*) AS visits FROM `' . self::TABLE . '`
                  WHERE ' . self::humanSql() . ' AND `' . $col . "` <> ''
                  GROUP BY `" . $col . '` ORDER BY visits DESC LIMIT ' . max(1, min(100, $limit))
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Most-viewed pages across all journeys (from the hits table). */
    public static function topPages(int $limit = 12): array
    {
        try {
            $r = DB::FetchAll(
                'SELECT h.`path` AS `key`, COUNT(*) AS views, COUNT(DISTINCT h.`visit_key`) AS visitors
                   FROM `' . self::HITS_TABLE . '` h JOIN `' . self::TABLE . '` v ON v.`visit_key` = h.`visit_key`
                  WHERE h.`path` <> \'\' AND ' . self::humanSql('v') . '
                  GROUP BY h.`path` ORDER BY views DESC LIMIT ' . max(1, min(100, $limit))
            );
            if (!empty($r)) return $r;
        } catch (\Throwable $e) { /* fall through */ }
        // Fallback when journey tracking is off: use landing pages.
        return self::entryPages($limit);
    }

    /** Active campaigns (utm), most first. */
    public static function campaigns(int $limit = 10): array
    {
        try {
            return DB::FetchAll(
                "SELECT `utm_source` AS source, `utm_medium` AS medium, `utm_campaign` AS campaign,
                        COUNT(*) AS visits
                   FROM `" . self::TABLE . "` WHERE `utm_source` <> '' AND " . self::humanSql() . "
                  GROUP BY `utm_source`, `utm_medium`, `utm_campaign`
                  ORDER BY visits DESC LIMIT " . max(1, min(100, $limit))
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Traffic split by channel (console/marketing/community/license/update/api). */
    public static function byChannel(): array
    {
        try {
            return DB::FetchAll(
                'SELECT `channel` AS `key`, COUNT(*) AS visits, COALESCE(SUM(`page_views`),0) AS views
                   FROM `' . self::TABLE . "` WHERE `is_bot` = 0 AND `channel` <> ''
                  GROUP BY `channel` ORDER BY visits DESC"
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Traffic split by the server that observed it (primary vs each backup). */
    public static function byServer(): array
    {
        try {
            return DB::FetchAll(
                "SELECT `server` AS `key`, COUNT(*) AS visits,
                        SUM(" . self::humanSql() . ") AS humans,
                        SUM(`channel` IN ('license','update','api')) AS api,
                        COALESCE(SUM(`page_views`),0) AS views
                   FROM `" . self::TABLE . "` WHERE `server` <> ''
                  GROUP BY `server` ORDER BY visits DESC"
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Top networks / organisations among human visitors, with a hosting flag. */
    public static function byNetwork(int $limit = 10): array
    {
        try {
            return DB::FetchAll(
                'SELECT `org` AS `key`, MAX(`asn`) AS asn, MAX(`is_hosting`) AS hosting, COUNT(*) AS visits
                   FROM `' . self::TABLE . '` WHERE ' . self::humanSql() . " AND `org` <> ''
                  GROUP BY `org` ORDER BY visits DESC LIMIT " . max(1, min(100, $limit))
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * API clients calling home (licence/update): one row per client install with
     * geo, call volume and success/failure — "which install, from where, doing
     * what, succeeding or failing".
     *
     * @return array<int,array<string,mixed>>
     */
    public static function apiClients(int $limit = 100, string $channel = ''): array
    {
        try {
            $where = ["`channel` IN ('license','update','api')"];
            $args = [];
            if ($channel !== '') { $where = ['`channel` = ?']; $args[] = $channel; }
            return DB::FetchAll(
                'SELECT `id`,`channel`,`server`,`client_type`,`client_label`,`ip`,`country`,`country_name`,`city`,
                        `asn`,`org`,`is_hosting`,`page_views` AS calls, `ok_count`, `err_count`, `last_path` AS last_action,
                        `first_seen`,`last_seen`
                   FROM `' . self::TABLE . '`
                  WHERE ' . implode(' AND ', $where) . '
                  ORDER BY `last_seen` DESC LIMIT ' . max(1, min(500, $limit)),
                $args
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Call volume by API action across all clients (sync/renew/activate/…). */
    public static function apiActions(int $limit = 12): array
    {
        try {
            return DB::FetchAll(
                'SELECT h.`path` AS `key`, COUNT(*) AS calls,
                        SUM(h.`status` >= 400) AS errors, COUNT(DISTINCT h.`visit_key`) AS clients
                   FROM `' . self::HITS_TABLE . '` h JOIN `' . self::TABLE . '` v ON v.`visit_key` = h.`visit_key`
                  WHERE v.`channel` IN (\'license\',\'update\',\'api\')
                  GROUP BY h.`path` ORDER BY calls DESC LIMIT ' . max(1, min(100, $limit))
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Summary for the API/clients panel, split by caller type. */
    public static function apiSummary(): array
    {
        $out = ['clients' => 0, 'calls' => 0, 'errors' => 0, 'countries' => 0, 'live' => 0,
                'from_clients' => 0, 'from_backups' => 0, 'calls_clients' => 0, 'calls_backups' => 0];
        try {
            $r = DB::FetchAll(
                "SELECT COUNT(*) AS clients, COALESCE(SUM(`page_views`),0) AS calls,
                        COALESCE(SUM(`err_count`),0) AS errors,
                        COUNT(DISTINCT NULLIF(`country`,'')) AS countries,
                        SUM(`last_seen` >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)) AS live,
                        SUM(`client_type` <> 'backup') AS from_clients,
                        SUM(`client_type` = 'backup') AS from_backups,
                        COALESCE(SUM(CASE WHEN `client_type` <> 'backup' THEN `page_views` END),0) AS calls_clients,
                        COALESCE(SUM(CASE WHEN `client_type` = 'backup' THEN `page_views` END),0) AS calls_backups
                   FROM `" . self::TABLE . "` WHERE `channel` IN ('license','update','api')"
            );
            foreach ($out as $k => $_) $out[$k] = (int) ($r[0][$k] ?? 0);
        } catch (\Throwable $e) { /* not there yet */ }
        return $out;
    }

    /** Daily visit counts for a trend line. */
    public static function daily(int $days = 30): array
    {
        try {
            return DB::FetchAll(
                'SELECT DATE(`first_seen`) AS day, COUNT(*) AS visits,
                        SUM(`is_return` = 1) AS returning, COALESCE(SUM(`page_views`),0) AS views
                   FROM `' . self::TABLE . '`
                  WHERE ' . self::humanSql() . ' AND `first_seen` >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
                  GROUP BY DATE(`first_seen`) ORDER BY day ASC',
                [max(1, min(365, $days))]
            ) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Range-aware visitor trend for the chart's 24h/7d/30d/90d selector, gaps filled
     * with zeros. 24h uses hourly buckets; the rest use daily buckets. Each point
     * carries visits / page-views / returning so the chart's metric toggle works.
     *
     * @return list<array{t:string,label:string,visits:int,views:int,returning:int}>
     */
    public static function trendSeries(string $range = '30d'): array
    {
        $spec = ['24h' => [1, 'hour'], '7d' => [7, 'day'], '90d' => [90, 'day']];
        [$days, $gran] = $spec[$range] ?? [30, 'day'];

        // Query counts (fail-soft to empty), THEN always build the full bucket set so
        // the chart shows a complete, gap-filled line even when there is no data yet.
        $by = [];
        try {
            if ($gran === 'hour') {
                $rows = DB::FetchAll(
                    "SELECT DATE_FORMAT(`first_seen`,'%Y-%m-%d %H') AS b, COUNT(*) AS visits,
                            SUM(`is_return` = 1) AS returning, COALESCE(SUM(`page_views`),0) AS views
                       FROM `" . self::TABLE . "`
                      WHERE " . self::humanSql() . " AND `first_seen` >= DATE_SUB(NOW(), INTERVAL 23 HOUR)
                      GROUP BY b",
                    []
                ) ?: [];
                foreach ($rows as $r) { $by[(string) $r['b']] = $r; }
            } else {
                $rows = DB::FetchAll(
                    "SELECT DATE(`first_seen`) AS d, COUNT(*) AS visits,
                            SUM(`is_return` = 1) AS returning, COALESCE(SUM(`page_views`),0) AS views
                       FROM `" . self::TABLE . "`
                      WHERE " . self::humanSql() . " AND `first_seen` >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
                      GROUP BY DATE(`first_seen`)",
                    [$days - 1]
                ) ?: [];
                foreach ($rows as $r) { $by[(string) $r['d']] = $r; }
            }
        } catch (\Throwable $e) {
            $by = [];   // no table / query error → a flat zero line, never empty
        }

        $out = [];
        $ts = time();
        if ($gran === 'hour') {
            for ($i = 23; $i >= 0; $i--) {
                $k = date('Y-m-d H', $ts - $i * 3600); $r = $by[$k] ?? [];
                $out[] = ['t' => $k . ':00', 'label' => date('H:00', $ts - $i * 3600),
                          'visits' => (int) ($r['visits'] ?? 0), 'views' => (int) ($r['views'] ?? 0), 'returning' => (int) ($r['returning'] ?? 0)];
            }
        } else {
            for ($i = $days - 1; $i >= 0; $i--) {
                $d = date('Y-m-d', $ts - $i * 86400); $r = $by[$d] ?? [];
                $out[] = ['t' => $d, 'label' => date('j M', $ts - $i * 86400),
                          'visits' => (int) ($r['visits'] ?? 0), 'views' => (int) ($r['views'] ?? 0), 'returning' => (int) ($r['returning'] ?? 0)];
            }
        }
        return $out;
    }

    /**
     * Visits by weekday (0=Mon…6=Sun) × hour-of-day (0-23) over the last N days —
     * the load-pattern heatmap. Returns a 7×24 matrix + the peak cell for scaling.
     * Fail-soft to an empty grid.
     *
     * @return array{grid:array<int,array<int,int>>,max:int}
     */
    public static function heatmap(int $days = 30): array
    {
        $days = max(1, min(180, $days));
        $grid = [];
        for ($d = 0; $d < 7; $d++) $grid[$d] = array_fill(0, 24, 0);
        $max = 0;
        try {
            $rows = DB::FetchAll(
                'SELECT WEEKDAY(`first_seen`) wd, HOUR(`first_seen`) hr, COUNT(*) n
                   FROM `' . self::TABLE . '`
                  WHERE ' . self::humanSql() . ' AND `first_seen` >= DATE_SUB(NOW(), INTERVAL ? DAY)
                  GROUP BY wd, hr',
                [$days]
            ) ?: [];
            foreach ($rows as $r) {
                $wd = (int) $r['wd']; $hr = (int) $r['hr']; $n = (int) $r['n'];
                if ($wd < 0 || $wd > 6 || $hr < 0 || $hr > 23) continue;
                $grid[$wd][$hr] = $n;
                if ($n > $max) $max = $n;
            }
        } catch (\Throwable $e) { /* fail-soft — empty grid */ }
        return ['grid' => $grid, 'max' => $max];
    }

    /** Visits by hour-of-day (0–23) over a recent window — a rhythm chart. */
    public static function hourly(int $days = 30): array
    {
        try {
            $rows = DB::FetchAll(
                'SELECT HOUR(`first_seen`) AS h, COUNT(*) AS visits FROM `' . self::TABLE . '`
                  WHERE ' . self::humanSql() . ' AND `first_seen` >= DATE_SUB(NOW(), INTERVAL ? DAY)
                  GROUP BY HOUR(`first_seen`)',
                [max(1, min(365, $days))]
            ) ?: [];
            $out = array_fill(0, 24, 0);
            foreach ($rows as $r) $out[(int) $r['h']] = (int) $r['visits'];
            return $out;
        } catch (\Throwable $e) {
            return array_fill(0, 24, 0);
        }
    }

    // ── Owner housekeeping ────────────────────────────────────────────

    public static function delete(int $id): bool
    {
        try {
            $vk = DB::FetchAll('SELECT `visit_key` FROM `' . self::TABLE . '` WHERE `id` = ?', [$id]);
            DB::Query('DELETE FROM `' . self::TABLE . '` WHERE `id` = ?', [$id]);
            if (!empty($vk)) DB::Query('DELETE FROM `' . self::HITS_TABLE . '` WHERE `visit_key` = ?', [(string) $vk[0]['visit_key']]);
            return true;
        } catch (\Throwable $e) { return false; }
    }

    public static function deleteBots(): int
    {
        try {
            $n = (int) (DB::FetchAll('SELECT COUNT(*) AS c FROM `' . self::TABLE . '` WHERE `is_bot` = 1')[0]['c'] ?? 0);
            DB::Exec('DELETE h FROM `' . self::HITS_TABLE . '` h JOIN `' . self::TABLE . '` v ON v.`visit_key` = h.`visit_key` WHERE v.`is_bot` = 1');
            DB::Exec('DELETE FROM `' . self::TABLE . '` WHERE `is_bot` = 1');
            return $n;
        } catch (\Throwable $e) { return 0; }
    }

    public static function clearAll(): bool
    {
        try {
            DB::Exec('DELETE FROM `' . self::TABLE . '`');
            DB::Exec('DELETE FROM `' . self::HITS_TABLE . '`');
            return true;
        } catch (\Throwable $e) { return false; }
    }

    /** Drop anything older than the retention window. 0 days = keep forever. */
    /** Token guarding the web-cron prune URL (own secret, minted on first read). */
    public static function cronSecret(): string
    {
        try {
            $bag = PlatformSettings::get('platform.visitor_cron');
            $s = (string) ($bag['secret'] ?? '');
            if ($s === '') { $s = bin2hex(random_bytes(20)); PlatformSettings::set('platform.visitor_cron', ['secret' => $s]); }
            return $s;
        } catch (\Throwable $e) { return ''; }
    }

    public static function prune(): int
    {
        $days = self::settings()['retention_days'];
        if ($days <= 0) return 0;
        try {
            // Expired hits first (via the join), then the expired visits — and take
            // the removed-row count from the visits DELETE's affected rows rather
            // than a preceding full-table COUNT(*) scan whose only job was the tally.
            DB::Query('DELETE h FROM `' . self::HITS_TABLE . '` h JOIN `' . self::TABLE . '` v ON v.`visit_key` = h.`visit_key` WHERE v.`last_seen` < DATE_SUB(NOW(), INTERVAL ? DAY)', [$days]);
            $res = DB::Query('DELETE FROM `' . self::TABLE . '` WHERE `last_seen` < DATE_SUB(NOW(), INTERVAL ? DAY)', [$days]);
            $n = ($res !== false) ? $res->rowCount() : 0;
            // Sweep orphaned hits whose parent visit was removed by other means.
            DB::Query('DELETE h FROM `' . self::HITS_TABLE . '` h LEFT JOIN `' . self::TABLE . '` v ON v.`visit_key` = h.`visit_key` WHERE v.`id` IS NULL');
            return $n;
        } catch (\Throwable $e) { return 0; }
    }
}
