<?php
/**
 * Stats facade — read-only queries used by Dashboard / reports.
 *
 * All queries respect the current blog when in Multisite (or aggregate the
 * network when called from network admin).
 *
 * @package Brainwerk SEO Suite
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class Seoforge_Stats {

    const CACHE_TTL = 300; // 5 minutes — invalidates on settings save / cleanup

    /** Helper: short-lived transient cache wrapper. */
    private static function cached( $key, callable $fn ) {
        $blog = Seoforge_Utils::current_blog_id();
        $full = 'seoforge_' . $blog . '_' . $key;
        $hit  = get_transient( $full );
        if ( false !== $hit ) {
            return $hit;
        }
        $val = $fn();
        set_transient( $full, $val, self::CACHE_TTL );
        return $val;
    }

    public static function flush_cache() {
        // Best effort — we don't track keys, but the wp_options cleanup tool
        // and the cleanup cron flush via this entry point.
        global $wpdb;
        $wpdb->query( "DELETE FROM {$wpdb->options} WHERE option_name LIKE '_transient_seoforge_%' OR option_name LIKE '_transient_timeout_seoforge_%'" );
    }

    /** @return array{visits:int,unique_visitors:int,sessions:int,bounce_rate:float,avg_duration:float} */
    public static function summary( $hours = 24, $blog_id = null ) {
        return self::cached( 'summary_' . (int) $hours . '_' . ( $blog_id === null ? 'all' : (int) $blog_id ), function () use ( $hours, $blog_id ) {
            global $wpdb;

            // Use the daily aggregate when the range fits whole days (>=24h).
            // Falls back to live visits table for sub-24h ranges.
            if ( $hours >= 24 ) {
                $days = (int) ceil( $hours / 24 );
                $daily_t = Seoforge_Utils::table( 'daily' );
                $cutoff  = gmdate( 'Y-m-d', time() - DAY_IN_SECONDS * ( $days - 1 ) );

                if ( null !== $blog_id ) {
                    $row = $wpdb->get_row( $wpdb->prepare(
                        "SELECT COALESCE(SUM(visits),0) AS visits,
                                COALESCE(SUM(uniques),0) AS uniques,
                                COALESCE(SUM(sessions),0) AS sessions,
                                COALESCE(SUM(bounces),0) AS bounces,
                                COALESCE(SUM(duration_sum),0) AS duration_sum
                         FROM %i WHERE day >= %s AND blog_id = %d",
                        $daily_t, $cutoff, (int) $blog_id
                    ), ARRAY_A );
                } else {
                    $row = $wpdb->get_row( $wpdb->prepare(
                        "SELECT COALESCE(SUM(visits),0) AS visits,
                                COALESCE(SUM(uniques),0) AS uniques,
                                COALESCE(SUM(sessions),0) AS sessions,
                                COALESCE(SUM(bounces),0) AS bounces,
                                COALESCE(SUM(duration_sum),0) AS duration_sum
                         FROM %i WHERE day >= %s",
                        $daily_t, $cutoff
                    ), ARRAY_A );
                }
                $visits   = (int) ( $row['visits'] ?? 0 );
                $uniques  = (int) ( $row['uniques'] ?? 0 );
                $sessions = (int) ( $row['sessions'] ?? 0 );
                $bounces  = (int) ( $row['bounces'] ?? 0 );
                $avg      = $sessions > 0 ? (int) ( ( $row['duration_sum'] ?? 0 ) / $sessions ) : 0;
                return array(
                    'visits'           => $visits,
                    'unique_visitors'  => $uniques,
                    'sessions'         => $sessions,
                    'bounce_rate'      => $sessions > 0 ? round( ( $bounces / $sessions ) * 100, 1 ) : 0.0,
                    'avg_duration'     => $avg,
                );
            }

            // Sub-24h: hit the live tables.
            $visits_t   = Seoforge_Utils::table( 'visits' );
            $sessions_t = Seoforge_Utils::table( 'sessions' );
            $since      = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );

            if ( null !== $blog_id ) {
                $visits = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d", $visits_t, $since, (int) $blog_id ) );
                $unique = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d", $visits_t, $since, (int) $blog_id ) );
            } else {
                $visits = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE is_bot = 0 AND created_at >= %s", $visits_t, $since ) );
                $unique = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE is_bot = 0 AND created_at >= %s", $visits_t, $since ) );
            }

            if ( null !== $blog_id ) {
                $sess_count   = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE started_at >= %s AND blog_id = %d", $sessions_t, $since, (int) $blog_id ) );
                $bounces      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE started_at >= %s AND blog_id = %d AND is_bounce = 1", $sessions_t, $since, (int) $blog_id ) );
                $avg_duration = (float) $wpdb->get_var( $wpdb->prepare( "SELECT AVG(duration_sec) FROM %i WHERE started_at >= %s AND blog_id = %d", $sessions_t, $since, (int) $blog_id ) );
            } else {
                $sess_count   = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE started_at >= %s", $sessions_t, $since ) );
                $bounces      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE started_at >= %s AND is_bounce = 1", $sessions_t, $since ) );
                $avg_duration = (float) $wpdb->get_var( $wpdb->prepare( "SELECT AVG(duration_sec) FROM %i WHERE started_at >= %s", $sessions_t, $since ) );
            }

            return array(
                'visits'          => $visits,
                'unique_visitors' => $unique,
                'sessions'        => $sess_count,
                'bounce_rate'     => $sess_count > 0 ? round( ( $bounces / $sess_count ) * 100, 1 ) : 0.0,
                'avg_duration'    => round( $avg_duration, 1 ),
            );
        } );
    }

    /**
     * Per-site visitor breakdown for the network view: one row per blog_id
     * that recorded traffic in range, busiest first. Callers gate this on
     * is_multisite() + network scope; the site name is resolved in the view.
     *
     * @return array<int,array{blog_id:int,visits:int,unique_visitors:int,sessions:int}>
     */
    public static function summary_by_blog( $hours = 168 ) {
        global $wpdb;
        $visits_t   = Seoforge_Utils::table( 'visits' );
        $sessions_t = Seoforge_Utils::table( 'sessions' );
        $since      = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );

        $visit_rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT blog_id, COUNT(*) AS visits, COUNT(DISTINCT visitor_hash) AS uniques
             FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY blog_id",
            $visits_t, $since
        ), ARRAY_A );
        $sess_rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT blog_id, COUNT(*) AS sessions
             FROM %i WHERE started_at >= %s GROUP BY blog_id",
            $sessions_t, $since
        ), ARRAY_A );

        $sess = array();
        foreach ( (array) $sess_rows as $r ) {
            $sess[ (int) $r['blog_id'] ] = (int) $r['sessions'];
        }

        $out = array();
        foreach ( (array) $visit_rows as $r ) {
            $bid   = (int) $r['blog_id'];
            $out[] = array(
                'blog_id'         => $bid,
                'visits'          => (int) $r['visits'],
                'unique_visitors' => (int) $r['uniques'],
                'sessions'        => isset( $sess[ $bid ] ) ? $sess[ $bid ] : 0,
            );
        }
        usort( $out, function ( $a, $b ) { return $b['visits'] - $a['visits']; } );
        return $out;
    }

    /**
     * Live visitors in the last 5 minutes — small, frequently called.
     * Cached for 30s only because it powers a polling widget.
     */
    public static function live_visitors( $blog_id = null ) {
        $key  = 'live_' . ( $blog_id === null ? 'all' : (int) $blog_id );
        $blog = Seoforge_Utils::current_blog_id();
        $full = 'seoforge_' . $blog . '_' . $key;
        $hit  = get_transient( $full );
        if ( false !== $hit ) {
            return $hit;
        }
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - 5 * MINUTE_IN_SECONDS );
        $now      = gmdate( 'Y-m-d H:i:s', time() );
        // Belt-and-braces: also bound by NOW() so that bad seed data with
        // future timestamps cannot inflate the live counter.
        if ( null !== $blog_id ) {
            $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE is_bot = 0 AND created_at >= %s AND created_at <= %s AND blog_id = %d", $visits_t, $since, $now, (int) $blog_id ) );
            $rows  = $wpdb->get_results( $wpdb->prepare(
                "SELECT url_path, MIN(title) AS title, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND created_at >= %s AND created_at <= %s AND blog_id = %d
                 GROUP BY url_path ORDER BY hits DESC LIMIT 5",
                $visits_t, $since, $now, (int) $blog_id
            ), ARRAY_A );
        } else {
            $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE is_bot = 0 AND created_at >= %s AND created_at <= %s", $visits_t, $since, $now ) );
            $rows  = $wpdb->get_results( $wpdb->prepare(
                "SELECT url_path, MIN(title) AS title, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND created_at >= %s AND created_at <= %s
                 GROUP BY url_path ORDER BY hits DESC LIMIT 5",
                $visits_t, $since, $now
            ), ARRAY_A );
        }

        $out = array( 'visitors' => $count, 'pages' => $rows ?: array() );
        set_transient( $full, $out, 30 );
        return $out;
    }

    /** Hourly buckets for the last N hours. Returns array of [bucket=>'YYYY-MM-DD HH', visits=>int]. */
    public static function visits_hourly( $hours = 24, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );

        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H') AS bucket, COUNT(*) AS visits
                 FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY bucket ORDER BY bucket ASC",
                $visits_t, $since, (int) $blog_id
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H') AS bucket, COUNT(*) AS visits
             FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY bucket ORDER BY bucket ASC",
            $visits_t, $since
        ), ARRAY_A );
    }

    /** Hourly hit counts for an explicit [start,end] datetime window (not "last N hours"). Used by the intel snapshot's day-scoped hour profile. */
    public static function hour_profile( $start_sql, $end_sql, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT HOUR(created_at) AS hour, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND created_at BETWEEN %s AND %s AND blog_id = %d GROUP BY hour",
                $visits_t, $start_sql, $end_sql, (int) $blog_id
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT HOUR(created_at) AS hour, COUNT(*) AS hits
             FROM %i WHERE is_bot = 0 AND created_at BETWEEN %s AND %s GROUP BY hour",
            $visits_t, $start_sql, $end_sql
        ), ARRAY_A );
    }

    /** Weekday hit counts (WEEKDAY(): 0=Monday..6=Sunday) over the trailing N days. */
    public static function weekday_profile( $days = 28, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS * (int) $days );
        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT WEEKDAY(created_at) AS wd, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY wd",
                $visits_t, $since, (int) $blog_id
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT WEEKDAY(created_at) AS wd, COUNT(*) AS hits
             FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY wd",
            $visits_t, $since
        ), ARRAY_A );
    }

    /**
     * Daily buckets broken down by source_type. Fills missing days with 0.
     * Returns array of:
     *   ['day' => 'YYYY-MM-DD', 'total' => int, 'sources' => ['search' => N, ...]]
     */
    public static function visits_daily_by_source( $days = 7, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d 00:00:00', time() - DAY_IN_SECONDS * ( $days - 1 ) );

        if ( null !== $blog_id ) {
            $rows = $wpdb->get_results( $wpdb->prepare(
                "SELECT DATE(created_at) AS day, source_type, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d
                 GROUP BY day, source_type ORDER BY day ASC, source_type",
                $visits_t, $since, (int) $blog_id
            ), ARRAY_A );
        } else {
            $rows = $wpdb->get_results( $wpdb->prepare(
                "SELECT DATE(created_at) AS day, source_type, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND created_at >= %s
                 GROUP BY day, source_type ORDER BY day ASC, source_type",
                $visits_t, $since
            ), ARRAY_A );
        }

        $matrix = array();
        foreach ( (array) $rows as $r ) {
            $d = $r['day'];
            if ( ! isset( $matrix[ $d ] ) ) {
                $matrix[ $d ] = array( 'day' => $d, 'total' => 0, 'sources' => array() );
            }
            $st = $r['source_type'] ?: 'direct';
            $matrix[ $d ]['sources'][ $st ] = (int) $r['hits'];
            $matrix[ $d ]['total']         += (int) $r['hits'];
        }
        // Fill missing days with zeros so the chart never has gaps.
        $out = array();
        for ( $i = $days - 1; $i >= 0; $i-- ) {
            $d = gmdate( 'Y-m-d', time() - DAY_IN_SECONDS * $i );
            $out[] = isset( $matrix[ $d ] ) ? $matrix[ $d ] : array(
                'day'     => $d,
                'total'   => 0,
                'sources' => array(),
            );
        }
        return $out;
    }

    /** Daily buckets. */
    public static function visits_daily( $days = 30, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS * (int) $days );

        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT DATE(created_at) AS bucket, COUNT(*) AS visits, COUNT(DISTINCT visitor_hash) AS uniques
                 FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY bucket ORDER BY bucket ASC",
                $visits_t, $since, (int) $blog_id
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT DATE(created_at) AS bucket, COUNT(*) AS visits, COUNT(DISTINCT visitor_hash) AS uniques
             FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY bucket ORDER BY bucket ASC",
            $visits_t, $since
        ), ARRAY_A );
    }

    public static function top_pages( $hours = 168, $limit = 20, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );

        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT url_path, MIN(title) AS title, COUNT(*) AS hits, COUNT(DISTINCT visitor_hash) AS uniques
                 FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d
                 GROUP BY url_path ORDER BY hits DESC LIMIT %d",
                $visits_t, $since, (int) $blog_id, (int) $limit
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT url_path, MIN(title) AS title, COUNT(*) AS hits, COUNT(DISTINCT visitor_hash) AS uniques
             FROM %i WHERE is_bot = 0 AND created_at >= %s
             GROUP BY url_path ORDER BY hits DESC LIMIT %d",
            $visits_t, $since, (int) $limit
        ), ARRAY_A );
    }

    public static function top_sources( $hours = 168, $limit = 20, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );

        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT source_type, source_name, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d
                 GROUP BY source_type, source_name ORDER BY hits DESC LIMIT %d",
                $visits_t, $since, (int) $blog_id, (int) $limit
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT source_type, source_name, COUNT(*) AS hits
             FROM %i WHERE is_bot = 0 AND created_at >= %s
             GROUP BY source_type, source_name ORDER BY hits DESC LIMIT %d",
            $visits_t, $since, (int) $limit
        ), ARRAY_A );
    }

    public static function top_referrers( $hours = 168, $limit = 20, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );

        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT referrer_host, source_type, source_name, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND source_type IN ('referral','social','search') AND created_at >= %s AND blog_id = %d
                 GROUP BY referrer_host, source_type, source_name ORDER BY hits DESC LIMIT %d",
                $visits_t, $since, (int) $blog_id, (int) $limit
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT referrer_host, source_type, source_name, COUNT(*) AS hits
             FROM %i WHERE is_bot = 0 AND source_type IN ('referral','social','search') AND created_at >= %s
             GROUP BY referrer_host, source_type, source_name ORDER BY hits DESC LIMIT %d",
            $visits_t, $since, (int) $limit
        ), ARRAY_A );
    }

    public static function top_search_terms( $hours = 720, $limit = 20, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );

        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT search_term, source_name, COUNT(*) AS hits
                 FROM %i WHERE is_bot = 0 AND search_term <> '' AND created_at >= %s AND blog_id = %d
                 GROUP BY search_term, source_name ORDER BY hits DESC LIMIT %d",
                $visits_t, $since, (int) $blog_id, (int) $limit
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT search_term, source_name, COUNT(*) AS hits
             FROM %i WHERE is_bot = 0 AND search_term <> '' AND created_at >= %s
             GROUP BY search_term, source_name ORDER BY hits DESC LIMIT %d",
            $visits_t, $since, (int) $limit
        ), ARRAY_A );
    }

    public static function device_breakdown( $hours = 168, $blog_id = null ) {
        return self::group_count( 'device_type', $hours, $blog_id );
    }
    public static function browser_breakdown( $hours = 168, $blog_id = null ) {
        return self::group_count( 'browser', $hours, $blog_id );
    }
    public static function os_breakdown( $hours = 168, $blog_id = null ) {
        return self::group_count( 'os', $hours, $blog_id );
    }

    /**
     * Top visitor countries (excludes rows with no resolved country).
     *
     * @return array<int,array{code:string,hits:int}>
     */
    public static function geo_countries( $hours = 168, $limit = 250, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );
        $limit    = max( 1, min( 250, (int) $limit ) );
        if ( null !== $blog_id ) {
            return (array) $wpdb->get_results( $wpdb->prepare(
                "SELECT country AS code, COUNT(*) AS hits FROM %i
                 WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d AND country IS NOT NULL AND country <> ''
                 GROUP BY country ORDER BY hits DESC LIMIT %d",
                $visits_t, $since, (int) $blog_id, $limit ), ARRAY_A );
        }
        return (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT country AS code, COUNT(*) AS hits FROM %i
             WHERE is_bot = 0 AND created_at >= %s AND country IS NOT NULL AND country <> ''
             GROUP BY country ORDER BY hits DESC LIMIT %d",
            $visits_t, $since, $limit ), ARRAY_A );
    }

    /**
     * Top regions within one country (Pro / MaxMind City data).
     *
     * @return array<int,array{label:string,hits:int}>
     */
    public static function geo_regions( $country, $hours = 168, $limit = 15, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );
        $country  = strtoupper( substr( (string) $country, 0, 2 ) );
        $limit    = max( 1, min( 100, (int) $limit ) );
        if ( '' === $country ) {
            return array();
        }
        if ( null !== $blog_id ) {
            return (array) $wpdb->get_results( $wpdb->prepare(
                "SELECT region AS label, COUNT(*) AS hits FROM %i
                 WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d AND country = %s AND region IS NOT NULL AND region <> ''
                 GROUP BY region ORDER BY hits DESC LIMIT %d",
                $visits_t, $since, (int) $blog_id, $country, $limit ), ARRAY_A );
        }
        return (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT region AS label, COUNT(*) AS hits FROM %i
             WHERE is_bot = 0 AND created_at >= %s AND country = %s AND region IS NOT NULL AND region <> ''
             GROUP BY region ORDER BY hits DESC LIMIT %d",
            $visits_t, $since, $country, $limit ), ARRAY_A );
    }

    /**
     * Top cities within one country (Pro / MaxMind City data).
     *
     * @return array<int,array{label:string,hits:int}>
     */
    public static function geo_cities( $country, $hours = 168, $limit = 15, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );
        $country  = strtoupper( substr( (string) $country, 0, 2 ) );
        $limit    = max( 1, min( 100, (int) $limit ) );
        if ( '' === $country ) {
            return array();
        }
        if ( null !== $blog_id ) {
            return (array) $wpdb->get_results( $wpdb->prepare(
                "SELECT city AS label, COUNT(*) AS hits FROM %i
                 WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d AND country = %s AND city IS NOT NULL AND city <> ''
                 GROUP BY city ORDER BY hits DESC LIMIT %d",
                $visits_t, $since, (int) $blog_id, $country, $limit ), ARRAY_A );
        }
        return (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT city AS label, COUNT(*) AS hits FROM %i
             WHERE is_bot = 0 AND created_at >= %s AND country = %s AND city IS NOT NULL AND city <> ''
             GROUP BY city ORDER BY hits DESC LIMIT %d",
            $visits_t, $since, $country, $limit ), ARRAY_A );
    }

    private static function group_count( $column, $hours, $blog_id ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        $since    = gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS * (int) $hours );

        $has_blog = ( null !== $blog_id );
        // The grouped column is NEVER taken from a variable: each branch is a
        // separate query string with the identifier hard-coded as a literal.
        // Unknown columns return empty. Values are bound through prepare().
        switch ( (string) $column ) {
            case 'device_type':
                if ( $has_blog ) {
                    return $wpdb->get_results( $wpdb->prepare( "SELECT device_type AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY device_type ORDER BY hits DESC", $visits_t, $since, (int) $blog_id ), ARRAY_A );
                }
                return $wpdb->get_results( $wpdb->prepare( "SELECT device_type AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY device_type ORDER BY hits DESC", $visits_t, $since ), ARRAY_A );
            case 'browser':
                if ( $has_blog ) {
                    return $wpdb->get_results( $wpdb->prepare( "SELECT browser AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY browser ORDER BY hits DESC", $visits_t, $since, (int) $blog_id ), ARRAY_A );
                }
                return $wpdb->get_results( $wpdb->prepare( "SELECT browser AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY browser ORDER BY hits DESC", $visits_t, $since ), ARRAY_A );
            case 'os':
                if ( $has_blog ) {
                    return $wpdb->get_results( $wpdb->prepare( "SELECT os AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY os ORDER BY hits DESC", $visits_t, $since, (int) $blog_id ), ARRAY_A );
                }
                return $wpdb->get_results( $wpdb->prepare( "SELECT os AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY os ORDER BY hits DESC", $visits_t, $since ), ARRAY_A );
            case 'source_type':
                if ( $has_blog ) {
                    return $wpdb->get_results( $wpdb->prepare( "SELECT source_type AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY source_type ORDER BY hits DESC", $visits_t, $since, (int) $blog_id ), ARRAY_A );
                }
                return $wpdb->get_results( $wpdb->prepare( "SELECT source_type AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY source_type ORDER BY hits DESC", $visits_t, $since ), ARRAY_A );
            case 'source_name':
                if ( $has_blog ) {
                    return $wpdb->get_results( $wpdb->prepare( "SELECT source_name AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY source_name ORDER BY hits DESC", $visits_t, $since, (int) $blog_id ), ARRAY_A );
                }
                return $wpdb->get_results( $wpdb->prepare( "SELECT source_name AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY source_name ORDER BY hits DESC", $visits_t, $since ), ARRAY_A );
            case 'country':
                if ( $has_blog ) {
                    return $wpdb->get_results( $wpdb->prepare( "SELECT country AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s AND blog_id = %d GROUP BY country ORDER BY hits DESC", $visits_t, $since, (int) $blog_id ), ARRAY_A );
                }
                return $wpdb->get_results( $wpdb->prepare( "SELECT country AS label, COUNT(*) AS hits FROM %i WHERE is_bot = 0 AND created_at >= %s GROUP BY country ORDER BY hits DESC", $visits_t, $since ), ARRAY_A );
            default:
                return array();
        }
    }

    public static function recent_visits( $limit = 50, $blog_id = null ) {
        global $wpdb;
        $visits_t = Seoforge_Utils::table( 'visits' );
        if ( null !== $blog_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT id, created_at, url_path, title, source_type, source_name, referrer_host, device_type, browser, country
                 FROM %i WHERE is_bot = 0 AND blog_id = %d
                 ORDER BY id DESC LIMIT %d",
                $visits_t, (int) $blog_id, (int) $limit
            ), ARRAY_A );
        }
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT id, created_at, url_path, title, source_type, source_name, referrer_host, device_type, browser, country
             FROM %i WHERE is_bot = 0
             ORDER BY id DESC LIMIT %d",
            $visits_t, (int) $limit
        ), ARRAY_A );
    }
}
