<?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): 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));

        $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
              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
        }
    }

    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;

        // 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.
     */
    public static function viewsOverTime(string $from, string $to, ?int $pageId = null): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        $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 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';
        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 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';
        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 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';
        $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 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';
        $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 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 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';
        // 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 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';
        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 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';
        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 AND engaged_seconds >= 2
             GROUP BY path
             ORDER BY views DESC
             LIMIT %d",
            $from, $to, $limit
        ), 'ARRAY_A') ?: [];
    }

    public static function deviceBreakdown(string $from, string $to): array
    {
        $table = DB::prefix() . 'instinctor_pageviews';
        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 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';
        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 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';
        $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 AND engaged_seconds >= 2",
            $from, $to
        ), 'ARRAY_A');
        return $row ?: ['views' => 0, 'visitors' => 0];
    }
}
