<?php
declare(strict_types=1);

namespace Instinctor\Services;

use Instinctor\Core\DB;

/**
 * Built-in privacy-first analytics — no JS, no cookies, no third-party.
 *
 * Hooked once from the top of templates/impulse-page.php. record() inserts
 * a single row into instinctor_pageviews per genuine public page view:
 *
 *   - Editor previews (?impulse_preview / ?impulse_builder), admin paths,
 *     XHR/REST/AJAX requests, bot UAs, the server's own IP / loopback, and any
 *     IP/CIDR in the `instinctor_analytics_exclude_ips` option (owner machine,
 *     office, automation sessions) are skipped at record time.
 *   - The dashboard counts JS-VERIFIED views only: a row is surfaced once the
 *     visitor's browser actually runs the tracker and fires a beacon
 *     (beaconed = 1). Datacenter bots that fetch the HTML with a spoofed
 *     browser UA but never execute JS are stored for forensics but excluded
 *     from every visitor-facing aggregate.
 *   - visitor_hash = sha256(UA + IP/24 + daily salt). Salt rotates at
 *     midnight UTC; cross-day hashes are non-correlatable.
 *   - Country comes from CF-IPCountry when present (Cloudflare proxy),
 *     '' otherwise — no MaxMind dependency.
 *   - Device parsed from UA into desktop / tablet / mobile.
 *   - UTM source/medium/campaign captured from the query string so tagged
 *     campaign links are attributable even when the referrer is stripped.
 *
 * Query helpers (viewsOverTime / topReferrers / topSources / directTraffic /
 * deviceBreakdown / countryBreakdown) are consumed by the admin Stats page.
 *
 * @since 2.1.954
 */
class AnalyticsService
{
    /**
     * Record one pageview. No-op when the request isn't a real public view.
     * Called from templates/impulse-page.php near the top of render.
     */
    /**
     * Record a public page view. Returns a random `view_token` the frontend
     * passes to the behavior tracker so its exit beacon can update THIS row
     * (engaged time / scroll / exit screen). Returns '' when the view is
     * skipped (preview/admin/bot/XHR) — the caller then emits no tracker.
     */
    public static function record(?int $pageId = null): string
    {
        try {
            if (self::shouldSkip()) return '';

            $ua  = sanitize_text_field(wp_unslash($_SERVER['HTTP_USER_AGENT'] ?? ''));
            $ip  = self::clientIp();
            $day = gmdate('Y-m-d');
            $ts  = gmdate('Y-m-d H:i:s');

            // Visitor hash uses the daily salt so the same UA+IP can't be
            // correlated across days. /24 truncation drops the last byte
            // of IPv4 (and the last 80 bits of IPv6) — enough to deflect
            // single-household fingerprinting while still letting same-
            // session views collapse for dedupe.
            $salt        = self::dailySalt($day);
            $visitor     = hash('sha256', $ua . '|' . self::truncateIp($ip) . '|' . $salt);

            $path     = sanitize_text_field(wp_unslash($_SERVER['REQUEST_URI'] ?? ''));
            // Strip query string + trim to column width.
            $qPos     = strpos($path, '?');
            if ($qPos !== false) $path = substr($path, 0, $qPos);
            $path     = substr($path, 0, 512);

            $referrer = esc_url_raw(wp_unslash($_SERVER['HTTP_REFERER'] ?? ''));
            $refHost  = '';
            if ($referrer !== '') {
                $parts = wp_parse_url($referrer);
                $h     = isset($parts['host']) ? strtolower($parts['host']) : '';
                // Treat same-site referrers as direct so the top-referrers
                // table reflects external traffic.
                $selfHost = strtolower(sanitize_text_field(wp_unslash($_SERVER['HTTP_HOST'] ?? '')));
                if ($h !== '' && $h !== $selfHost) {
                    $refHost = substr($h, 0, 255);
                }
            }

            // UTM attribution — pulled from the query string (which `path`
            // above already dropped). Referrer-host can't separate campaign
            // channels and app/email clicks strip the referrer entirely, so
            // tagged links (?utm_source=…&utm_medium=…&utm_campaign=…) are the
            // reliable signal. source/medium lowercased (GA convention) so
            // "Instagram"/"instagram" don't fragment; campaign kept verbatim.
            $utmSource = $utmMedium = $utmCampaign = '';
            $rawUri = sanitize_text_field(wp_unslash($_SERVER['REQUEST_URI'] ?? ''));
            $qs = strpos($rawUri, '?');
            if ($qs !== false) {
                parse_str(substr($rawUri, $qs + 1), $q);
                $utmSource   = strtolower(substr(trim((string) ($q['utm_source']   ?? '')), 0, 100));
                $utmMedium   = strtolower(substr(trim((string) ($q['utm_medium']   ?? '')), 0, 100));
                $utmCampaign =          substr(trim((string) ($q['utm_campaign'] ?? '')), 0, 150);
            }

            $country = '';
            $cfCountry = sanitize_text_field(wp_unslash($_SERVER['HTTP_CF_IPCOUNTRY'] ?? ''));
            if (strlen($cfCountry) === 2) {
                $country = strtoupper(substr($cfCountry, 0, 2));
            }
            /* Fallback: when CF-IPCountry isn't present (site not behind
               Cloudflare), look the visitor IP up against the bundled
               DB-IP Country Lite binary (Sergei 2026-06-05, v2.1.1184).
               Cheap O(log n) binary search over a sorted fixed-row file.
               Honors X-Forwarded-For if nginx is in front and forwarding,
               otherwise REMOTE_ADDR. Lookup misses (private/reserved IPs,
               IPv6 outside published ranges) leave country = '' which is
               the same as before — no regression. */
            if ($country === '') {
                $clientIp = '';
                $fwd = sanitize_text_field(wp_unslash($_SERVER['HTTP_X_FORWARDED_FOR'] ?? ''));
                if ($fwd !== '') {
                    /* First entry in the comma list is the original client. */
                    $first = trim(explode(',', $fwd)[0]);
                    if ($first !== '') $clientIp = $first;
                }
                if ($clientIp === '') {
                    $clientIp = sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR'] ?? ''));
                }
                if ($clientIp !== '') {
                    try { $country = \Instinctor\Core\IpToCountry::lookup($clientIp); }
                    catch (\Throwable $_e) { /* swallow — analytics is best-effort */ }
                }
            }

            $device = self::detectDevice($ua);
            $isBot  = self::isBotUa($ua) ? 1 : 0;

            // Per-visitor dedupe within the same day: if this visitor_hash
            // already has a row for this page+day, skip the insert. Keeps
            // "unique visitors" honest and trims storage. The KEY
            // idx_visitor_day makes this a single-row index probe.
            $table = DB::prefix() . 'instinctor_pageviews';
            $existing = DB::get_row(DB::prepare(
                "SELECT id, view_token FROM {$table}
                 WHERE visitor_hash = %s AND day = %s AND " .
                ($pageId ? "page_id = %d" : "path = %s") .
                " LIMIT 1",
                $visitor,
                $day,
                $pageId ? $pageId : $path
            ), 'ARRAY_A');
            // Repeat same-day view of the same page → reuse the existing row's
            // token so the tracker keeps accumulating engagement onto it
            // (ingestBeacon takes GREATEST), instead of inserting a duplicate.
            if ($existing) {
                return (string) ($existing['view_token'] ?? '');
            }

            // Random per-view correlation id handed to the frontend tracker.
            $token = bin2hex(random_bytes(8)); // 16 hex chars

            DB::query(DB::prepare(
                "INSERT INTO {$table}
                 (page_id, path, ts, day, visitor_hash, referrer_host, country, device, is_bot, utm_source, utm_medium, utm_campaign, view_token)
                 VALUES (%d, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s, %s, %s)",
                $pageId ?: 0,
                $path,
                $ts,
                $day,
                $visitor,
                $refHost,
                $country,
                $device,
                $isBot,
                $utmSource,
                $utmMedium,
                $utmCampaign,
                $token
            ));
            return $token;
        } catch (\Throwable $e) {
            // Analytics must never break the page. Errors are silent in
            // production; uncomment for debugging.
            // error_log('[analytics] ' . $e->getMessage());
        }
        return '';
    }

    /**
     * Behavior-tracker beacon ingest. Public, unauthenticated (frontend
     * visitors call it via navigator.sendBeacon on exit). Updates the
     * pageview row identified by its view_token with active engagement time,
     * deepest scroll, and exit screen. GREATEST() so repeat beacons
     * (visibilitychange + pagehide) and repeat views never shrink the figures.
     * Restricted to the last 2 days so a leaked token can't rewrite history.
     */
    public static function ingestBeacon(string $token, int $seconds, int $scroll, int $exitScreen, int $humanInput = 1): bool
    {
        $token = preg_replace('~[^a-f0-9]~', '', strtolower(substr($token, 0, 16)));
        if (strlen($token) !== 16) return false;

        $seconds    = max(0, min(7200, $seconds));   // cap 2h of "active" time
        $scroll     = max(0, min(100,  $scroll));
        $exitScreen = max(-1, min(999, $exitScreen));

        // Behavioural bot flag. isBotUa() only reads the User-Agent, so a
        // headless Chrome with a clean UA counted as a real visitor. The
        // tracker now reports whether a pointer/tap/keypress ever happened
        // (hu); scroll deliberately does NOT count, because that is the one
        // thing preview/screenshot bots reliably fake. No human input at all
        // over a whole visit means a machine, so flip is_bot and let every
        // existing report (all of which already filter is_bot = 0) drop it.
        //
        // Only ever sets the flag, never clears it: a UA-detected bot stays a
        // bot even if it forges a click. Default 1 so an older cached tracker
        // that omits `hu` keeps being counted rather than being wiped out.
        //
        // DWELL ESCAPE HATCH (2026-08-03). Pointer input alone brands mobile
        // readers as machines. `mousemove` does not exist on a phone, so the
        // only human tells left are a tap or a keypress, and a page that fits
        // the viewport (/make-a-site does) offers nothing to scroll or touch:
        // a visitor who arrives, reads for half a minute and leaves produces
        // hu=0 and was filed as a bot. That is precisely the bounce population
        // a landing page has to be judged on, and 92% of paid traffic is
        // mobile, so the flag was deleting the channel we were measuring
        // (25 of 31 visits on 2026-08-03; 16 of them engaged over 10s).
        // Real bots sit at 0s engaged with an empty country because they
        // screenshot and exit, so dwell separates the two cleanly: this
        // recovers 23 rows out of 24,634 flagged and leaves 24,602 caught.
        // Deliberately the LAST of four gates: _isHeadlessBrowser() bails
        // before the beacon, isBotUa() reads the UA, and every report already
        // demands engaged_seconds >= 2 on top of is_bot = 0. Note the ceiling
        // this cannot see past: `lastAct` starts at load, so a visible tab
        // accrues dwell without input for IDLE_MS: a bot that idles 5s before
        // exiting now passes here and is only held by the >= 2s report filter.
        $botFlag = ($humanInput === 0 && $seconds < 5) ? ', is_bot = 1' : '';

        $table = DB::prefix() . 'instinctor_pageviews';
        DB::query(DB::prepare(
            "UPDATE {$table}
                SET engaged_seconds = GREATEST(engaged_seconds, %d),
                    max_scroll      = GREATEST(max_scroll, %d),
                    exit_screen     = %d,
                    beaconed        = 1{$botFlag}
              WHERE view_token = %s AND day >= %s
              LIMIT 1",
            $seconds,
            $scroll,
            $exitScreen,
            $token,
            gmdate('Y-m-d', time() - 86400)
        ));
        return DB::last_error() === '';
    }

    /** True when the request should NOT be counted (preview, admin, bot, XHR). */
    /**
     * Remember the site owner's current IP so their own public (logged-out)
     * page views stop inflating the Traffic tab. Only the owner/admin reaches
     * the admin shell, which is where this is called, so the current client IP
     * is theirs. Appended to `instinctor_analytics_exclude_ips` (deduped, and
     * capped so a roaming owner can't grow it without bound). Self-healing: a
     * changed IP is re-added on the next admin load. Best-effort — any failure
     * is swallowed so it can never break the admin. v2.1.2164.
     */
    public static function rememberOwnerIp(): void
    {
        try {
            if (!class_exists('\\Instinctor\\Core\\Auth')) return;
            if (!\Instinctor\Core\Auth::can('instinctor_access')) return;
            $ip = self::clientIp();
            if ($ip === '' || !filter_var($ip, FILTER_VALIDATE_IP)) return;
            if (self::isExcludedIp($ip)) return; // already covered (list / loopback / server)
            $raw  = (string) get_option('instinctor_analytics_exclude_ips', '');
            $list = preg_split('~[\s,]+~', $raw, -1, PREG_SPLIT_NO_EMPTY) ?: [];
            $list[] = $ip;
            if (count($list) > 12) $list = array_slice($list, -12); // keep the 12 most recent
            update_option('instinctor_analytics_exclude_ips', implode("\n", $list));
        } catch (\Throwable $e) {
            // best-effort; analytics exclusion must never break the admin shell
        }
    }

    /**
     * Plant the owner opt-out cookie honored by shouldSkip(). Fully automatic:
     * planted on every admin-shell load, no UI and nothing for the owner to do.
     * It catches THIS browser on the public site even when logged out, which
     * neither the login skip nor the IP list reliably does (a residential IP
     * drifts). One year, path=/, Lax. Best-effort, a no-op after headers sent.
     *
     * Deliberately browser-scoped. A manual per-device opt-out link existed and
     * was removed 2026-07-16 (automatic only, Sergei's call), so a device that
     * never loads the admin (the owner's phone on cellular) is NOT excluded.
     * That is the accepted trade, not an oversight. v2.1.2256.
     */
    public static function setOwnerCookie(): void
    {
        if (headers_sent()) return;
        $secure = (!empty($_SERVER['HTTPS']) && strtolower((string) $_SERVER['HTTPS']) !== 'off')
            || ((int) ($_SERVER['SERVER_PORT'] ?? 0) === 443)
            || (strtolower((string) ($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '')) === 'https');
        setcookie('instinctor_no_track', '1', [
            'expires'  => time() + 31536000,
            'path'     => '/',
            'secure'   => $secure,
            'httponly' => false,
            'samesite' => 'Lax',
        ]);
        $_COOKIE['instinctor_no_track'] = '1'; // effective on this request too
    }

    private static function shouldSkip(): bool
    {
        // Editor preview iframe — INSTINCTOR_PREVIEW_CONTEXT is set on the
        // request line above the record() call.
        if (defined('INSTINCTOR_PREVIEW_CONTEXT') && INSTINCTOR_PREVIEW_CONTEXT) return true;

        // Owner opt-out cookie, planted automatically on every admin-shell load.
        // Catches this browser previewing the public site logged out, which the
        // login skip below misses and a drifting residential IP misses. v2.1.2256.
        if (!empty($_COOKIE['instinctor_no_track'])) return true;

        // Authenticated staff (owner/admin/manager/agent). Their own browsing —
        // and any QA/automation session riding the same admin cookie — is not a
        // real visitor and would skew the numbers. The frontend Auth driver
        // auto-resolves the logged-in user from the session cookie that rides
        // along on every same-domain request, so this catches owner self-views
        // without any per-page flag. Logged-in customers are NOT excluded
        // (they lack instinctor_access) — they're genuine traffic.
        if (class_exists('\\Instinctor\\Core\\Auth')) {
            try {
                if (\Instinctor\Core\Auth::can('instinctor_access')) return true;
            } catch (\Throwable $e) {
                // Auth unavailable on this request — fall through and record.
            }
        }

        // Excluded IPs — the site owner's own machine (and any QA box / office
        // network), plus Claude / automation sessions that ride the same IP.
        // Login-based skipping above can't catch these: public pages don't
        // require auth, so the owner's own home-page reloads would otherwise be
        // counted as visits. The server's own address and loopback are always
        // excluded so server-side loopback / preview fetches never self-inflate.
        $ip = self::clientIp();
        if ($ip !== '' && self::isExcludedIp($ip)) return true;

        // Admin area.
        $uri = sanitize_text_field(wp_unslash($_SERVER['REQUEST_URI'] ?? ''));
        if (stripos($uri, '/admin') !== false || stripos($uri, '/wp-admin') !== false) return true;
        if (stripos($uri, '/api/instinctor') !== false || stripos($uri, '/wp-json') !== false) return true;
        if (stripos($uri, '/admin-ajax') !== false) return true;

        // XHR / fetch.
        $xhr = strtolower(sanitize_text_field(wp_unslash($_SERVER['HTTP_X_REQUESTED_WITH'] ?? '')));
        if ($xhr === 'xmlhttprequest') return true;

        // Prefetch / prerender.
        $purpose = strtolower(sanitize_text_field(wp_unslash($_SERVER['HTTP_PURPOSE'] ?? $_SERVER['HTTP_SEC_PURPOSE'] ?? '')));
        if ($purpose === 'prefetch' || strpos($purpose, 'prefetch') !== false) return true;

        // Only GET (POST/PUT/DELETE/etc are API calls).
        $method = strtoupper(sanitize_text_field(wp_unslash($_SERVER['REQUEST_METHOD'] ?? 'GET')));
        if ($method !== 'GET') return true;

        return false;
    }

    /**
     * True when $ip is loopback, the server's own address, or matches an entry
     * in the `instinctor_analytics_exclude_ips` option — a comma/newline list
     * of exact IPs or CIDR ranges (IPv4 and IPv6). Owner / office / automation
     * IPs go in that option; it ships EMPTY so no personal IP is baked into the
     * distributed plugin (the live install seeds its own).
     */
    private static function isExcludedIp(string $ip): bool
    {
        if ($ip === '127.0.0.1' || $ip === '::1') return true;
        $self = sanitize_text_field(wp_unslash($_SERVER['SERVER_ADDR'] ?? ''));
        if ($self !== '' && $ip === $self) return true;

        $raw = (string) get_option('instinctor_analytics_exclude_ips', '');
        if ($raw === '') return false;
        foreach (preg_split('~[\s,]+~', $raw, -1, PREG_SPLIT_NO_EMPTY) as $entry) {
            if (strpos($entry, '/') !== false) {
                if (self::ipInCidr($ip, $entry)) return true;
            } elseif ($ip === $entry) {
                return true;
            }
        }
        return false;
    }

    /** CIDR membership test for IPv4 and IPv6 (binary mask compare). */
    private static function ipInCidr(string $ip, string $cidr): bool
    {
        $parts  = explode('/', $cidr, 2);
        $subnet = $parts[0];
        $bits   = isset($parts[1]) ? (int) $parts[1] : -1;
        if ($bits < 0) return false;
        $ipBin     = @inet_pton($ip);
        $subnetBin = @inet_pton($subnet);
        if ($ipBin === false || $subnetBin === false) return false;
        if (strlen($ipBin) !== strlen($subnetBin)) return false; // v4 vs v6 mismatch
        $bytes = intdiv($bits, 8);
        $rem   = $bits % 8;
        if ($bytes > 0 && strncmp($ipBin, $subnetBin, $bytes) !== 0) return false;
        if ($rem === 0) return true;
        $mask = chr((0xff << (8 - $rem)) & 0xff);
        return (ord($ipBin[$bytes]) & ord($mask)) === (ord($subnetBin[$bytes]) & ord($mask));
    }

    /** True when the UA looks like a search-engine bot / crawler / scraper. */
    private static function isBotUa(string $ua): bool
    {
        if ($ua === '') return true;
        // Coverage list is intentionally short — catches the headline 90%
        // of bot traffic. Anything more aggressive risks false positives
        // (mobile browsers with "App" or "Mobile" in their UA strings).
        return (bool) preg_match('~bot|crawl|spider|slurp|baidu|yandex|duckduck|facebookexternalhit|preview|pingdom|uptimerobot|monitor|wget|curl|python-requests|axios|headless|phantom~i', $ua);
    }

    private static function detectDevice(string $ua): string
    {
        if ($ua === '') return '';
        if (preg_match('~tablet|ipad|kindle|playbook~i', $ua)) return 'tablet';
        if (preg_match('~mobi|android|iphone|ipod|blackberry|iemobile|opera mini~i', $ua)) return 'mobile';
        return 'desktop';
    }

    /** Client IP, preferring CF-Connecting-IP then X-Forwarded-For then REMOTE_ADDR. */
    private static function clientIp(): string
    {
        foreach (['HTTP_CF_CONNECTING_IP', 'HTTP_X_REAL_IP'] as $h) {
            if (!empty($_SERVER[$h])) return sanitize_text_field(wp_unslash($_SERVER[$h]));
        }
        if (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
            $parts = explode(',', sanitize_text_field(wp_unslash($_SERVER['HTTP_X_FORWARDED_FOR'])));
            return trim($parts[0]);
        }
        return sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR'] ?? ''));
    }

    /** Drop the last octet of IPv4 / last 80 bits of IPv6. Keeps geo coarse. */
    private static function truncateIp(string $ip): string
    {
        if (strpos($ip, '.') !== false) {
            $p = explode('.', $ip);
            if (count($p) === 4) return $p[0] . '.' . $p[1] . '.' . $p[2] . '.0';
        }
        if (strpos($ip, ':') !== false) {
            // Keep first 3 hex groups, zero the rest.
            $p = explode(':', $ip);
            $p = array_slice($p, 0, 3);
            return implode(':', $p) . '::';
        }
        return $ip;
    }

    /** Read-or-insert the daily salt. 32-char hex (16 bytes). */
    private static function dailySalt(string $day): string
    {
        $table = DB::prefix() . 'instinctor_analytics_salt';
        $cached = DB::get_var(DB::prepare(
            "SELECT salt FROM {$table} WHERE day = %s LIMIT 1",
            $day
        ));
        if ($cached) return (string) $cached;
        $salt = bin2hex(random_bytes(16));
        // INSERT IGNORE so concurrent requests on day rollover don't error.
        DB::query(DB::prepare(
            "INSERT IGNORE INTO {$table} (day, salt) VALUES (%s, %s)",
            $day,
            $salt
        ));
        // If another process inserted first, read back the winning value.
        $winner = DB::get_var(DB::prepare(
            "SELECT salt FROM {$table} WHERE day = %s LIMIT 1",
            $day
        ));
        return $winner ? (string) $winner : $salt;
    }

    // ── Query helpers (consumed by admin Stats page) ─────────────────────

    /**
     * Daily pageview counts between $from..$to (Y-m-d inclusive).
     * Returns array of ['day' => 'YYYY-MM-DD', 'views' => N, 'visitors' => N].
     * Excludes bots. If $pageId is set, filters to that page.
     */
    /**
     * Referrer hosts the owner has chosen to leave out of the Traffic tab.
     *
     * Stored as one host per line (or comma-separated) in the
     * `analytics_excluded_referrers` config key. Matching is on the host as
     * recorded, plus its www. variant, so entering "awwwards.com" also drops
     * "www.awwwards.com".
     *
     * This is a REPORTING filter, not a deletion: the rows stay in the table
     * with their bot flags untouched, so removing a host from the list brings
     * its history straight back. Sergei 2026-08-03: gallery referrers (awwwards,
     * betalist) send real-looking sessions that never convert and drown out the
     * channels that do.
     *
     * @return string[]
     */
    public static function excludedReferrers(): array
    {
        $raw = (string) \Instinctor\Core\Config::get('analytics_excluded_referrers', '');
        if (trim($raw) === '') {
            return [];
        }
        $out = [];
        foreach (preg_split('/[
,]+/', $raw) as $h) {
            $h = strtolower(trim((string) $h));
            $h = preg_replace('~^https?://~', '', $h);
            $h = trim((string) $h, '/ ');
            if ($h === '') continue;
            $out[] = $h;
            $out[] = (strpos($h, 'www.') === 0) ? substr($h, 4) : ('www.' . $h);
        }
        return array_values(array_unique($out));
    }

    /**
     * SQL fragment appended to every report's WHERE clause. Empty string when
     * nothing is excluded, so the queries are byte-identical to before for an
     * install that never sets the list.
     */
    private static function excludeSql(): string
    {
        $hosts = self::excludedReferrers();
        if (!$hosts) {
            return '';
        }
        $quoted = array_map(function ($h) { return "'" . str_replace("'", "''", $h) . "'"; }, $hosts);
        return ' AND referrer_host NOT IN (' . implode(',', $quoted) . ')';
    }

    /**
     * What the exclusion list is hiding in a range, so the panel can say so out
     * loud instead of quietly reporting smaller numbers.
     */
    public static function excludedStats(string $from, string $to): array
    {
        $hosts = self::excludedReferrers();
        if (!$hosts) {
            return ['hosts' => [], 'views' => 0];
        }
        $table  = DB::prefix() . 'instinctor_pageviews';
        $quoted = array_map(function ($h) { return "'" . str_replace("'", "''", $h) . "'"; }, $hosts);
        $views  = (int) DB::get_var(DB::prepare(
            "SELECT COUNT(*) FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1 AND engaged_seconds >= 2
               AND referrer_host IN (" . implode(',', $quoted) . ")",
            $from, $to
        ));
        return ['hosts' => array_values(array_filter($hosts, function ($h) { return strpos($h, 'www.') !== 0; })), 'views' => $views];
    }

    public static function viewsOverTime(string $from, string $to, ?int $pageId = null): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        $sql   = "SELECT day, COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
                  FROM {$table}
                  WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2";
        $args  = [$from, $to];
        if ($pageId) {
            $sql    .= " AND page_id = %d";
            $args[] = $pageId;
        }
        $sql .= " GROUP BY day ORDER BY day ASC";
        return DB::get_results(DB::prepare($sql, ...$args), 'ARRAY_A') ?: [];
    }

    public static function topReferrers(string $from, string $to, int $limit = 20): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        return DB::get_results(DB::prepare(
            "SELECT referrer_host, COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2 AND referrer_host != ''
             GROUP BY referrer_host
             ORDER BY views DESC
             LIMIT %d",
            $from, $to, $limit
        ), 'ARRAY_A') ?: [];
    }

    /**
     * Top campaign sources from UTM-tagged links, grouped by
     * source / medium / campaign. Only rows with a utm_source are counted —
     * this is campaign attribution, distinct from the referrer table.
     */
    public static function topSources(string $from, string $to, int $limit = 20): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        return DB::get_results(DB::prepare(
            "SELECT utm_source, utm_medium, utm_campaign,
                    COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2 AND utm_source != ''
             GROUP BY utm_source, utm_medium, utm_campaign
             ORDER BY views DESC
             LIMIT %d",
            $from, $to, $limit
        ), 'ARRAY_A') ?: [];
    }

    /**
     * Direct / unknown traffic: views with neither an external referrer nor a
     * UTM source. Surfaced as a "Direct" row above the referrers list so the
     * no-referrer bucket is visible instead of silently dropped.
     */
    public static function directTraffic(string $from, string $to): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        $row = DB::get_row(DB::prepare(
            "SELECT COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2
               AND referrer_host = '' AND utm_source = ''",
            $from, $to
        ), 'ARRAY_A');
        return $row ?: ['views' => 0, 'visitors' => 0];
    }

    /**
     * Engagement KPIs over beaconed views: average active time on page,
     * average scroll depth, count of engaged views, and bounce rate
     * (sessions = visitor+day groups; a bounce is a single-pageview session).
     */
    public static function engagementSummary(string $from, string $to): array
    {
        $t = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        $row = DB::get_row(DB::prepare(
            "SELECT AVG(engaged_seconds) AS avg_seconds,
                    AVG(max_scroll)      AS avg_scroll,
                    COUNT(*)             AS engaged_views
             FROM {$t}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2",
            $from, $to
        ), 'ARRAY_A') ?: [];

        $bounce = DB::get_row(DB::prepare(
            "SELECT SUM(CASE WHEN c = 1 THEN 1 ELSE 0 END) AS bounced, COUNT(*) AS sessions
             FROM (
                SELECT COUNT(*) AS c
                FROM {$t}
                WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2
                GROUP BY visitor_hash, day
             ) s",
            $from, $to
        ), 'ARRAY_A') ?: [];

        $sessions = (int) ($bounce['sessions'] ?? 0);
        return [
            'avg_seconds'   => (int) round((float) ($row['avg_seconds'] ?? 0)),
            'avg_scroll'    => (int) round((float) ($row['avg_scroll'] ?? 0)),
            'engaged_views' => (int) ($row['engaged_views'] ?? 0),
            'bounce_rate'   => $sessions ? (int) round(100 * (int) ($bounce['bounced'] ?? 0) / $sessions) : 0,
        ];
    }

    /**
     * Exit-screen histogram for screen-mode pages: how many sessions left at
     * each screen index. The cinematic "where do they drop off" signal.
     */
    public static function exitScreenHistogram(string $from, string $to, int $limit = 16, ?int $pageId = null): array
    {
        $t = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        // Per-page scope: a screen index only means something within ONE page
        // (and its current layout). Without page_id this conflated every page
        // AND every historical layout into one meaningless histogram.
        $sql  = "SELECT exit_screen, COUNT(*) AS sessions
                 FROM {$t}
                 WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2 AND exit_screen >= 0";
        $args = [$from, $to];
        if ($pageId) { $sql .= " AND page_id = %d"; $args[] = $pageId; }
        $sql .= " GROUP BY exit_screen ORDER BY exit_screen ASC LIMIT %d";
        $args[] = $limit;
        return DB::get_results(DB::prepare($sql, ...$args), 'ARRAY_A') ?: [];
    }

    /**
     * Pages that have exit-screen data in range, busiest first. Powers the
     * per-page selector on the Traffic "Where visitors leave" panel.
     */
    public static function exitPages(string $from, string $to, int $limit = 20): array
    {
        $t = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        return DB::get_results(DB::prepare(
            "SELECT page_id, COUNT(*) AS sessions
             FROM {$t}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2
               AND exit_screen >= 0 AND page_id IS NOT NULL AND page_id > 0
             GROUP BY page_id
             ORDER BY sessions DESC
             LIMIT %d",
            $from, $to, $limit
        ), 'ARRAY_A') ?: [];
    }

    /**
     * Most-visited pages in range — path, views, unique visitors. Powers the
     * Traffic right-column "Top pages" list.
     */
    public static function topPages(string $from, string $to, int $limit = 20): array
    {
        $t = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        return DB::get_results(DB::prepare(
            "SELECT path, COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$t}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2
             GROUP BY path
             ORDER BY views DESC
             LIMIT %d",
            $from, $to, $limit
        ), 'ARRAY_A') ?: [];
    }

    // ── Stage-2: lead-source attribution ────────────────────────────
    // The frontend tracker stores a FIRST-TOUCH record in the visitor's
    // localStorage ({r: referrer host, us/um/uc: utm, lp: landing path,
    // t: epoch}) and every capture flow (chat, forms, bookings, quotes)
    // posts it as `attribution`. The server sanitizes and stores it on
    // the lead row, so the Traffic dashboard can answer "which channel
    // produces CUSTOMERS", not just views. Privacy posture unchanged:
    // the record lives client-side; no cookies, no cross-site anything.

    /** PHP mirror of channelCase() - keep the buckets in lockstep. */
    public static function classifyChannel(string $referrerHost, string $utmSource): string
    {
        $r = strtolower($referrerHost);
        if ($utmSource !== '') return 'campaign';
        if ($r === '') return 'direct';
        if (preg_match('~google\.|bing\.|duckduckgo|yandex|baidu|ecosia|qwant|brave~', $r)) return 'search';
        if (preg_match('~youtube|youtu\.be|vimeo|rutube|twitch~', $r)) return 'video';
        if (preg_match('~facebook|fb\.|instagram|twitter|t\.co|x\.com|linkedin|reddit|pinterest|tiktok|threads|ycombinator|producthunt|telegram|whatsapp~', $r)) return 'social';
        return 'referral';
    }

    /**
     * Sanitize the client-posted first-touch JSON. Returns a compact,
     * whitelisted re-encoding (max ~500 chars) or '' when unusable.
     */
    public static function sanitizeAttribution(string $raw): string
    {
        if ($raw === '' || strlen($raw) > 2000) return '';
        $d = json_decode(wp_unslash($raw), true);
        if (!is_array($d)) return '';
        $out = [
            'r'  => substr(sanitize_text_field((string) ($d['r']  ?? '')), 0, 120),
            'us' => substr(sanitize_text_field((string) ($d['us'] ?? '')), 0, 100),
            'um' => substr(sanitize_text_field((string) ($d['um'] ?? '')), 0, 100),
            'uc' => substr(sanitize_text_field((string) ($d['uc'] ?? '')), 0, 150),
            // Paid-search fields. This is an ALLOW-LIST: any key not named here is
            // dropped, so the tracker growing new fields is not enough on its own.
            'ct' => substr(sanitize_text_field((string) ($d['ct'] ?? '')), 0, 100),  // utm_content
            'tm' => substr(sanitize_text_field((string) ($d['tm'] ?? '')), 0, 100),  // utm_term
            'gc' => substr(sanitize_text_field((string) ($d['gc'] ?? '')), 0, 120),  // gclid
            'gb' => substr(sanitize_text_field((string) ($d['gb'] ?? '')), 0, 120),  // gbraid
            'wb' => substr(sanitize_text_field((string) ($d['wb'] ?? '')), 0, 120),  // wbraid
            'lp' => substr(sanitize_text_field((string) ($d['lp'] ?? '')), 0, 200),
            't'  => (int) ($d['t'] ?? 0),
        ];
        // A paid click carrying only a click id and no utm tags is still a lead
        // worth attributing, so the click ids count as presence too.
        if ($out['r'] === '' && $out['us'] === '' && $out['lp'] === ''
            && $out['gc'] === '' && $out['gb'] === '' && $out['wb'] === '') {
            return '';
        }
        return (string) wp_json_encode($out);
    }

    /** Read + sanitize the attribution field from the current request. */
    public static function attributionFromRequest(): string
    {
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- read-only side data on already-nonce-checked capture endpoints; sanitized fully above.
        return self::sanitizeAttribution((string) ($_POST['attribution'] ?? ''));
    }

    /**
     * Ensure a lead table carries the attribution column. SHOW COLUMNS +
     * ALTER once per request per table (same pattern as TemplateService's
     * column upgrades) - avoids a migration-ordering dependency across
     * four separately-created tables.
     */
    public static function ensureAttributionColumn(string $table): void
    {
        static $done = [];
        if (!empty($done[$table])) return;
        $done[$table] = true;
        try {
            $col = DB::get_results("SHOW COLUMNS FROM {$table} LIKE 'attribution'");
            if (empty($col)) {
                DB::query("ALTER TABLE {$table} ADD COLUMN attribution VARCHAR(1500) NOT NULL DEFAULT ''");
                return;
            }
            // Widen in place on installs created before the paid-search fields.
            // 600 was already tight for the original five fields at their limits;
            // gclid/gbraid/wbraid push the worst case past 1200, and MySQL
            // truncates silently, which would corrupt the JSON rather than lose
            // one field. Only ever grows, never shrinks.
            $type = strtolower((string) ($col[0]->Type ?? ''));
            if (preg_match('/varchar\((\d+)\)/', $type, $m) && (int) $m[1] < 1500) {
                DB::query("ALTER TABLE {$table} MODIFY COLUMN attribution VARCHAR(1500) NOT NULL DEFAULT ''");
            }
        } catch (\Throwable $e) { /* table may not exist yet - the caller creates it */ }
    }

    /**
     * Leads grouped by first-touch channel across every capture surface.
     * Rows without attribution count as 'unknown' so pre-Stage-2 leads
     * stay visible instead of silently vanishing from the totals.
     */
    public static function leadsBySource(string $from, string $to): array
    {
        $p = DB::prefix();
        $surfaces = [
            'chat'    => [$p . 'instinctor_chat_conversations', 'created_at'],
            'form'    => [$p . 'instinctor_form_submissions',   'created_at'],
            'booking' => [$p . 'instinctor_bookings',           'created_at'],
            'quote'   => [$p . 'instinctor_estimates',          'created_at'],
        ];
        $agg = [];
        foreach ($surfaces as $kind => [$table, $dateCol]) {
            try {
                $rows = DB::get_results(DB::prepare(
                    "SELECT attribution FROM {$table} WHERE {$dateCol} BETWEEN %s AND %s",
                    $from . ' 00:00:00', $to . ' 23:59:59'
                ), 'ARRAY_A');
            } catch (\Throwable $e) { continue; }
            if (!$rows) continue;
            foreach ($rows as $row) {
                $a = json_decode((string) ($row['attribution'] ?? ''), true);
                $ch = is_array($a)
                    ? self::classifyChannel((string) ($a['r'] ?? ''), (string) ($a['us'] ?? ''))
                    : 'unknown';
                $key = $ch;
                if (!isset($agg[$key])) $agg[$key] = ['channel' => $ch, 'total' => 0, 'chat' => 0, 'form' => 0, 'booking' => 0, 'quote' => 0, 'landing' => []];
                $agg[$key]['total']++;
                $agg[$key][$kind]++;
                if (is_array($a) && !empty($a['lp'])) {
                    $lp = (string) $a['lp'];
                    $agg[$key]['landing'][$lp] = ($agg[$key]['landing'][$lp] ?? 0) + 1;
                }
            }
        }
        foreach ($agg as &$g) {
            arsort($g['landing']);
            $g['landing'] = array_slice($g['landing'], 0, 3, true);
        }
        unset($g);
        usort($agg, function ($a, $b) { return $b['total'] - $a['total']; });
        return array_values($agg);
    }

    // ── Stage-3: conversion (visitors -> leads) ─────────────────────
    // Joins real-visitor traffic (pageviews) with attributed leads to
    // answer "which channel and which page actually CONVERT", not just
    // which get looked at. A lead is any chat, form, booking, or quote.

    /**
     * Conversion funnel for the range.
     * - channels: visitors + leads + rate per first-touch channel. The
     *   'unknown' lead bucket (pre-attribution rows) carries no rate
     *   because it has no matching visitor denominator.
     * - pages: per landing path - visitors who STARTED there vs leads
     *   whose first touch landed there. Ranked by leads, then rate.
     */
    public static function conversionSummary(string $from, string $to): array
    {
        $t    = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        $case = self::channelCase();

        // Visitor denominators per channel (unique real visitors).
        $chanVisitors = DB::get_results(DB::prepare(
            "SELECT {$case} AS channel, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$t}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl}
             GROUP BY channel",
            $from, $to
        ), 'ARRAY_A') ?: [];
        $visByChan = [];
        foreach ($chanVisitors as $r) $visByChan[$r['channel']] = (int) $r['visitors'];

        // Visitors whose FIRST pageview of a day was this path (landing
        // visitors) - the honest denominator for a landing page's rate.
        $landVisitors = DB::get_results(DB::prepare(
            "SELECT path, COUNT(*) AS visitors FROM (
                 SELECT visitor_hash, day,
                        SUBSTRING_INDEX(GROUP_CONCAT(path ORDER BY ts SEPARATOR '\n'), '\n', 1) AS path
                 FROM {$t}
                 WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl}
                 GROUP BY visitor_hash, day
             ) f GROUP BY path",
            $from, $to
        ), 'ARRAY_A') ?: [];
        $visByPath = [];
        foreach ($landVisitors as $r) $visByPath[$r['path']] = (int) $r['visitors'];

        // Lead numerators per channel and per landing path.
        $leadsByChan = [];
        $leadsByPath = [];
        foreach (self::leadsBySource($from, $to) as $g) {
            $leadsByChan[$g['channel']] = (int) $g['total'];
        }
        $p = DB::prefix();
        foreach ([
            $p . 'instinctor_chat_conversations',
            $p . 'instinctor_form_submissions',
            $p . 'instinctor_bookings',
            $p . 'instinctor_estimates',
        ] as $table) {
            try {
                $rows = DB::get_results(DB::prepare(
                    "SELECT attribution FROM {$table} WHERE created_at BETWEEN %s AND %s AND attribution != ''",
                    $from . ' 00:00:00', $to . ' 23:59:59'
                ), 'ARRAY_A');
            } catch (\Throwable $e) { continue; }
            foreach ((array) $rows as $row) {
                $a = json_decode((string) ($row['attribution'] ?? ''), true);
                if (is_array($a) && !empty($a['lp'])) {
                    $lp = (string) $a['lp'];
                    $leadsByPath[$lp] = ($leadsByPath[$lp] ?? 0) + 1;
                }
            }
        }

        // Union of both maps: a channel with leads but no recorded visitors
        // in the range (first-touch on an earlier day, filtered IP) must
        // still show its leads instead of vanishing.
        $channels = [];
        foreach (array_unique(array_merge(array_keys($visByChan), array_keys($leadsByChan))) as $ch) {
            $vis   = $visByChan[$ch] ?? 0;
            $leads = $leadsByChan[$ch] ?? 0;
            $channels[] = [
                'channel'  => $ch,
                'visitors' => $vis,
                'leads'    => $leads,
                'rate'     => $vis > 0 ? round($leads / $vis * 100, 1) : null,
            ];
        }
        usort($channels, function ($a, $b) {
            return ($b['leads'] - $a['leads']) ?: ($b['visitors'] - $a['visitors']);
        });

        $pages = [];
        foreach (array_unique(array_merge(array_keys($visByPath), array_keys($leadsByPath))) as $path) {
            $vis   = $visByPath[$path] ?? 0;
            $leads = $leadsByPath[$path] ?? 0;
            $pages[] = [
                'path'     => $path,
                'visitors' => $vis,
                'leads'    => $leads,
                'rate'     => $vis > 0 ? round($leads / $vis * 100, 1) : null,
            ];
        }
        usort($pages, function ($a, $b) {
            return ($b['leads'] - $a['leads']) ?: ($b['visitors'] - $a['visitors']);
        });
        return ['channels' => $channels, 'pages' => array_slice($pages, 0, 15)];
    }

    /**
     * Channel classification CASE - shared by channelBreakdown and
     * channelLandingPages so the buckets can never drift apart.
     * Campaign (any utm_source) > Direct (no referrer) > Search / Video /
     * Social by referrer host > Referral (everything else external).
     */
    private static function channelCase(): string
    {
        return "CASE
            WHEN utm_source != '' THEN 'campaign'
            WHEN referrer_host = '' THEN 'direct'
            WHEN referrer_host REGEXP 'google\\.|bing\\.|duckduckgo|yandex|baidu|ecosia|qwant|brave' THEN 'search'
            WHEN referrer_host REGEXP 'youtube|youtu\\.be|vimeo|rutube|twitch' THEN 'video'
            WHEN referrer_host REGEXP 'facebook|fb\\.|instagram|twitter|t\\.co|x\\.com|linkedin|reddit|pinterest|tiktok|threads|ycombinator|producthunt|telegram|whatsapp' THEN 'social'
            ELSE 'referral'
        END";
    }

    /**
     * Stage-1 analytics (2026-07-23): traffic bucketed into channels
     * (Search / Video / Social / Campaign / Referral / Direct) with a
     * QUALITY column per channel - average engaged seconds and scroll
     * depth - so a channel that sends readers is distinguishable from
     * one that sends bouncers.
     */
    public static function channelBreakdown(string $from, string $to): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        $case  = self::channelCase();
        return DB::get_results(DB::prepare(
            "SELECT {$case} AS channel,
                    COUNT(*) AS views,
                    COUNT(DISTINCT visitor_hash) AS visitors,
                    ROUND(AVG(engaged_seconds)) AS avg_engaged,
                    ROUND(AVG(max_scroll)) AS avg_scroll
             FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2
             GROUP BY channel
             ORDER BY views DESC",
            $from, $to
        ), 'ARRAY_A') ?: [];
    }

    /**
     * Landing pages per channel: which page each channel actually lands on.
     * Flat rows (channel, path, views) - the dashboard groups client-side.
     */
    public static function channelLandingPages(string $from, string $to, int $limit = 40): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        $case  = self::channelCase();
        return DB::get_results(DB::prepare(
            "SELECT {$case} AS channel, path,
                    COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2
             GROUP BY channel, path
             ORDER BY views DESC
             LIMIT %d",
            $from, $to, $limit
        ), 'ARRAY_A') ?: [];
    }

    /**
     * Most common visitor-day journeys: the ordered page paths a visitor
     * took within one day (the daily hash rotation bounds a "session" to a
     * day by design). Single-page journeys are included - a popular
     * land-and-leave path is a signal, not noise.
     */
    public static function topJourneys(string $from, string $to, int $limit = 12): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        return DB::get_results(DB::prepare(
            "SELECT journey, steps, COUNT(*) AS sessions FROM (
                 SELECT visitor_hash, day,
                        SUBSTRING(GROUP_CONCAT(path ORDER BY ts ASC SEPARATOR ' > '), 1, 400) AS journey,
                        COUNT(*) AS steps
                 FROM {$table}
                 WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2
                 GROUP BY visitor_hash, day
             ) j
             GROUP BY journey, steps
             ORDER BY sessions DESC, steps DESC
             LIMIT %d",
            $from, $to, $limit
        ), 'ARRAY_A') ?: [];
    }

    public static function deviceBreakdown(string $from, string $to): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        return DB::get_results(DB::prepare(
            "SELECT device, COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2 AND device != ''
             GROUP BY device
             ORDER BY views DESC",
            $from, $to
        ), 'ARRAY_A') ?: [];
    }

    public static function countryBreakdown(string $from, string $to, int $limit = 30): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        return DB::get_results(DB::prepare(
            "SELECT country, COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2 AND country != ''
             GROUP BY country
             ORDER BY views DESC
             LIMIT %d",
            $from, $to, $limit
        ), 'ARRAY_A') ?: [];
    }

    /** Totals card: views + visitors over the date range. Single row. */
    public static function totals(string $from, string $to): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $excl = self::excludeSql();
        $row = DB::get_row(DB::prepare(
            "SELECT COUNT(*) AS views, COUNT(DISTINCT visitor_hash) AS visitors
             FROM {$table}
             WHERE day BETWEEN %s AND %s AND is_bot = 0 AND beaconed = 1{$excl} AND engaged_seconds >= 2",
            $from, $to
        ), 'ARRAY_A');
        return $row ?: ['views' => 0, 'visitors' => 0];
    }
}
