<?php

use SlimStat\Utils\Query;
use SlimStat\Components\DateRangeHelper;

// Let's define the main class with all the methods that we need
class wp_slimstat_db
{
    // Filters
    public static $columns_names = [];

    public static $operator_names = [];

    // Operators that take no value by design (is_empty/is_not_empty). Shared with
    // parse_filters(), fs_url(), and the JS layer (via SlimStatAdminParams) so the
    // list never drifts between callsites. See #305.
    public static $valueless_operators = ['is_empty', 'is_not_empty'];

    // parse_filters() switch keys that map to the date/misc buckets rather than a data
    // column. A value-less operator (is_empty/is_not_empty) is meaningless for these and
    // must not reach the switch without a value — those branches dereference $a_filter[3]
    // and would emit an "Undefined array key 3" warning on a crafted request. See #305.
    // Public + shared with the JS layer (via SlimStatAdminParams) so SlimStatGetFiltersForAjax()
    // strips the same date/misc keys when harvesting column filters for a sub-report. (#22)
    public const NON_COLUMN_FILTER_KEYS = [
        'strtotime', 'minute', 'hour', 'day', 'month', 'year',
        'interval', 'interval_hours', 'interval_minutes', 'limit_results', 'start_from',
    ];

    public static $filters_normalized = [];

    // Structure that maps filters to SQL information (table names, clauses, lookup tables, etc)
    public static $sql_where = ['columns' => '', 'time_range' => ''];

    // Filters that are not visible in the dropdown
    public static $all_columns_names = [];

    // Debug message
    public static $debug_message = '';

    // Useful data for the reports
    public static $pageviews = 0;

    /*
     * Sets the filters and other structures needed to store the data retrieved from the DB
     */
    public static function init($_filters = '')
    {
        // List of supported filters and their user-friendly names
        self::$columns_names = [
            'browser'              => [__('Browser', 'wp-slimstat'), 'varchar'],
            'country'              => [__('Country Code', 'wp-slimstat'), 'varchar'],
            'ip'                   => [__('IP Address', 'wp-slimstat'), 'varchar'],
            'searchterms'          => [__('Search Terms', 'wp-slimstat'), 'varchar'],
            'language'             => [__('Language', 'wp-slimstat'), 'varchar'],
            'platform'             => [__('Operating System', 'wp-slimstat'), 'varchar'],
            'resource'             => [__('Permalink', 'wp-slimstat'), 'varchar'],
            'referer'              => [__('Referer', 'wp-slimstat'), 'varchar'],
            'username'             => [__("Visitor's Username", 'wp-slimstat'), 'varchar'],
            'email'                => [__("Visitor's Email", 'wp-slimstat'), 'varchar'],
            'outbound_resource'    => [__('Outbound Link', 'wp-slimstat'), 'varchar'],
            'tz_offset'            => [__('Timezone Offset', 'wp-slimstat'), 'int'],
            'fingerprint'          => [__('Fingerprint', 'wp-slimstat'), 'varchar'],
            'page_performance'     => [__('Page Speed', 'wp-slimstat'), 'int'],
            'no_filter_selected_2' => ['', 'none'],
            'no_filter_selected_3' => [__('-- Advanced filters --', 'wp-slimstat'), 'none'],
            'browser_version'      => [__('Browser Version', 'wp-slimstat'), 'varchar'],
            'browser_type'         => [__('Browser Type', 'wp-slimstat'), 'int'],
            'user_agent'           => [__('User Agent', 'wp-slimstat'), 'varchar'],
            'city'                 => [__('City', 'wp-slimstat'), 'varchar'],
            'location'             => [__('Coordinates', 'wp-slimstat'), 'varchar'],
            'notes'                => [__('Annotations', 'wp-slimstat'), 'varchar'],
            'server_latency'       => [__('Server Latency', 'wp-slimstat'), 'int'],
            'author'               => [__('Post Author', 'wp-slimstat'), 'varchar'],
            'category'             => [__('Post Category ID', 'wp-slimstat'), 'varchar'],
            'other_ip'             => [__('Originating IP', 'wp-slimstat'), 'varchar'],
            'content_type'         => [__('Resource Content Type', 'wp-slimstat'), 'varchar'],
            'content_id'           => [__('Resource ID', 'wp-slimstat'), 'int'],
            'screen_width'         => [__('Screen Width', 'wp-slimstat'), 'int'],
            'screen_height'        => [__('Screen Height', 'wp-slimstat'), 'int'],
            'resolution'           => [__('Viewport Size', 'wp-slimstat'), 'varchar'],
            'visit_id'             => [__('Visit ID', 'wp-slimstat'), 'int'],
        ];

        if ('on' == wp_slimstat::$settings['geolocation_country']) {
            unset(self::$columns_names['city']);
            unset(self::$columns_names['location']);
        }

        // List of supported filters and their friendly names
        self::$operator_names = [
            'equals'           => __('equals', 'wp-slimstat'),
            'is_not_equal_to'  => __('is not equal to', 'wp-slimstat'),
            'contains'         => __('contains', 'wp-slimstat'),
            'includes_in_set'  => __('is included in', 'wp-slimstat'),
            'does_not_contain' => __('does not contain', 'wp-slimstat'),
            'starts_with'      => __('starts with', 'wp-slimstat'),
            'ends_with'        => __('ends with', 'wp-slimstat'),
            'sounds_like'      => __('sounds like', 'wp-slimstat'),
            'is_greater_than'  => __('is greater than', 'wp-slimstat'),
            'is_less_than'     => __('is less than', 'wp-slimstat'),
            'between'          => __('is between (x,y)', 'wp-slimstat'),
            'matches'          => __('matches', 'wp-slimstat'),
            'does_not_match'   => __('does not match', 'wp-slimstat'),
            'is_empty'         => __('is empty', 'wp-slimstat'),
            'is_not_empty'     => __('is not empty', 'wp-slimstat'),
        ];

        // The following filters will not be displayed in the dropdown
        self::$all_columns_names = array_merge([
            // Date and Time
            'minute'           => [__('Minute', 'wp-slimstat'), 'int'],
            'hour'             => [__('Hour', 'wp-slimstat'), 'int'],
            'day'              => [__('Day', 'wp-slimstat'), 'int'],
            'month'            => [__('Month', 'wp-slimstat'), 'int'],
            'year'             => [__('Year', 'wp-slimstat'), 'int'],
            'interval'         => [__('days', 'wp-slimstat'), 'int'],
            'interval_hours'   => [__('hours', 'wp-slimstat'), 'int'],
            'interval_minutes' => [__('minutes', 'wp-slimstat'), 'int'],
            'dt'               => [__('Timestamp', 'wp-slimstat'), 'int'],
            'dt_out'           => [__('Exit Timestamp', 'wp-slimstat'), 'int'],

            // Other columns
            'metric'       => [__('Metric', 'wp-slimstat'), 'varchar'],
            'value'        => [__('Value', 'wp-slimstat'), 'varchar'],
            'counthits'    => [__('Hits', 'wp-slimstat'), 'int'],
            'column_group' => [__('Grouped Value', 'wp-slimstat'), 'varchar'],
            'percentage'   => [__('Percentage', 'wp-slimstat'), 'int'],
            'tooltip'      => [__('Notes', 'wp-slimstat'), 'varchar'],
            'details'      => [__('Notes', 'wp-slimstat'), 'varchar'],

            // Events
            'event_id'          => [__('Event ID', 'wp-slimstat'), 'int'],
            'type'              => [__('Type', 'wp-slimstat'), 'int'],
            'event_description' => [__('Event Description', 'wp-slimstat'), 'varchar'],
            'position'          => [__('Event Coordinates', 'wp-slimstat'), 'int'],

            'limit_results' => [__('Max Results', 'wp-slimstat'), 'int'],
            'start_from'    => [__('Offset', 'wp-slimstat'), 'int'],

            // Misc Filters
            'strtotime' => [0, 'int'],
        ], self::$columns_names);

        // Allow third party plugins to add even more column names to the array
        self::$all_columns_names = apply_filters('slimstat_column_names', self::$all_columns_names);

        // Filters use the following format: browser equals Firefox&&&country contains gb
        $filters_array = [];

        // Handle type parameter for date presets and custom ranges
        if (isset($_GET['type'])) {
            // Sanitize the type parameter to prevent XSS
            $type = sanitize_key($_GET['type']);

            if ($type !== 'custom') {
                // Handle preset types
                // Validate that the type is a valid preset before using it
                $valid_presets = ['today', 'yesterday', 'this_week', 'last_week', 'this_month', 'last_month',
                                  'last_7_days', 'last_28_days', 'last_30_days', 'last_90_days',
                                  'last_6_months', 'this_year'];

                if (in_array($type, $valid_presets, true)) {
                    $preset_range = DateRangeHelper::get_range_by_preset($type);
                    if ($preset_range) {
                        $filters_array['strtotime'] = 'strtotime equals ' . sanitize_text_field(wp_date('Y-m-d', $preset_range['end']));
                        // Calculate days by normalizing to midnight to avoid DST issues
                        $start_day = strtotime(wp_date('Y-m-d', $preset_range['start']));
                        $end_day = strtotime(wp_date('Y-m-d', $preset_range['end']));
                        $interval_days = (($end_day - $start_day) / 86400) + 1;
                        $filters_array['interval'] = 'interval equals -' . absint($interval_days);
                    }
                }
            } elseif (isset($_GET['from']) && isset($_GET['to'])) {
                // Sanitize date inputs to prevent XSS
                $from_date = sanitize_text_field($_GET['from']);
                $to_date = sanitize_text_field($_GET['to']);

                // Validate date format (YYYY-MM-DD)
                if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $from_date) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $to_date)) {
                    // Calculate interval days directly from the date strings
                    $start_day = strtotime($from_date);
                    $end_day = strtotime($to_date);

                    // Basic validation
                    if ($start_day && $end_day && $start_day <= $end_day) {
                        $interval_days = (($end_day - $start_day) / 86400) + 1;

                        // Use the date strings directly without converting back and forth
                        $filters_array['strtotime'] = 'strtotime equals ' . $to_date;
                        $filters_array['interval'] = 'interval equals -' . absint($interval_days);
                    }
                }
            }
        }

        // Filters are set via javascript as hidden fields and submitted as a POST request. They override anything passed through the regular input fields
        if (!empty($_REQUEST['fs']) && is_array($_REQUEST['fs'])) {
            foreach ($_REQUEST['fs'] as $a_request_filter_name => $a_request_filter_value) {
                $safe_name  = sanitize_text_field(wp_unslash($a_request_filter_name));
                $safe_value = str_replace('&&&', '', sanitize_text_field(wp_unslash($a_request_filter_value)));
                $filters_array[$safe_name] = sprintf('%s %s', $safe_name, $safe_value);
            }
        }

        // Date filters (input fields) - Please note: interval_minutes is not exposed via the web interface, that's why it's not listed here below
        foreach (['hour', 'day', 'month', 'year', 'interval', 'interval_hours'] as $a_date_time_filter_name) {
            if (isset($_POST[$a_date_time_filter_name]) && strlen($_POST[$a_date_time_filter_name]) > 0) { // here we use isset instead of !empty to handle ZERO as a valid input value
                $filters_array[$a_date_time_filter_name] = $a_date_time_filter_name . ' equals ' . intval($_POST[$a_date_time_filter_name]);
            }
        }

        // Fields and drop downs
        if (!empty($_POST['f']) && !empty($_POST['o'])) {
            $filters_array[sanitize_text_field($_POST['f'])] = sprintf('%s %s ', sanitize_text_field($_POST[ 'f' ]), sanitize_text_field($_POST[ 'o' ])) . (isset($_POST['v']) ? sanitize_text_field($_POST['v']) : '');
        }

        // Filters set via the plugin options
        if ('on' == wp_slimstat::$settings['restrict_authors_view'] && !current_user_can('manage_options') && !empty($GLOBALS['current_user']->user_login)) {
            $filters_array['author'] = 'author equals ' . $GLOBALS['current_user']->user_login;
        }

        if ([] !== $filters_array) {
            $filters_raw = implode('&&&', $filters_array);
        }

        // Filters are defined as: browser equals Chrome&&&country starts_with en
        if (!isset($filters_raw) || !is_string($filters_raw)) {
            $filters_raw = '';
        }

        if (!empty($_filters) && is_string($_filters)) {
            if ('' !== $filters_raw && '0' !== $filters_raw) {
                $filters_raw = '' === $filters_raw || '0' === $filters_raw ? $_filters : $_filters . '&&&' . $filters_raw;
            } else {
                $filters_raw = $_filters;
            }
        }

        // Hook for the... filters
        $filters_raw = apply_filters('slimstat_db_pre_filters', $filters_raw);

        // Normalize the filters
        self::$filters_normalized = self::init_filters($filters_raw);

        // Retrieve data that will be used by multiple reports
        if (empty($_REQUEST['page']) || false !== strpos($_REQUEST['page'], 'slimview')) {
            self::$pageviews = wp_slimstat_db::count_records();
        }
    }

    // end init

    /**
     * Builds the array of WHERE clauses to be used later in our SQL queries
     */
    protected static function _get_sql_where($_filters_normalized = [], $_slim_stats_table_alias = '')
    {
        $sql_array = [];

        foreach ($_filters_normalized as $a_filter_column => $a_filter_data) {
            // Add-ons can set their own custom filters, which are ignored here
            if (false !== strpos($a_filter_column, 'addon_')) {
                continue;
            }

            $sql_array[] = self::get_single_where_clause($a_filter_column, $a_filter_data[0], $a_filter_data[1], $_slim_stats_table_alias);
        }

        // Flatten array
        if ([] !== $sql_array) {
            return implode(' AND ', $sql_array);
        }

        return '';
    }

    public static function get_combined_where($_where = '', $_column = '*', $_use_date_filters = true, $_slim_stats_table_alias = '', $where_params = null)
    {
        global $wpdb;

        $dt_with_alias = 'dt';
        if (!empty($_slim_stats_table_alias)) {
            $dt_with_alias = $_slim_stats_table_alias . '.' . $dt_with_alias;
        }

        $time_range_condition = '';
        if (empty($_where)) {
            if (!empty(self::$filters_normalized['columns'])) {
                $_where = self::_get_sql_where(self::$filters_normalized['columns'], $_slim_stats_table_alias);

                if ($_use_date_filters) {
                    // Use $wpdb->prepare() for all dynamic SQL values
                    $time_range_condition = $wpdb->prepare(
                        $dt_with_alias . ' BETWEEN %d AND %d',
                        intval(self::$filters_normalized['utime']['start']),
                        intval(self::$filters_normalized['utime']['end'])
                    );
                }
            } elseif ($_use_date_filters) {
                // Use $wpdb->prepare() for all dynamic SQL values
                $time_range_condition = $wpdb->prepare(
                    $dt_with_alias . ' BETWEEN %d AND %d',
                    intval(self::$filters_normalized['utime']['start']),
                    intval(self::$filters_normalized['utime']['end'])
                );
            }

            // This could happen if we have custom filters (add-ons, third party tools)
            if (empty($_where)) {
                $_where = '1=1';
            }
        } else {
            if ('1=1' != $_where && !empty(self::$filters_normalized['columns'])) {
                $new_clause = self::_get_sql_where(self::$filters_normalized['columns'], $_slim_stats_table_alias);

                // This condition could be empty if it's related to a custom column
                if (!empty($new_clause)) {
                    $_where .= ' AND ' . $new_clause;
                }
            }

            if ($_use_date_filters) {
                // Use $wpdb->prepare() for all dynamic SQL values
                $time_range_condition = $wpdb->prepare(
                    $dt_with_alias . ' BETWEEN %d AND %d',
                    intval(self::$filters_normalized['utime']['start']),
                    intval(self::$filters_normalized['utime']['end'])
                );
            }
        }

        if (!empty($_where) && ('' !== $time_range_condition && '0' !== $time_range_condition)) {
            $_where = sprintf('%s AND %s', $_where, $time_range_condition);
        } else {
            $_where = trim(sprintf('%s %s', $_where, $time_range_condition));
        }

        if (!empty($_column) && !empty(self::$columns_names[$_column])) {
            $column_with_alias = $_column;
            if (!empty($_slim_stats_table_alias)) {
                $column_with_alias = $_slim_stats_table_alias . '.' . $column_with_alias;
            }

            $filter_empty     = $column_with_alias . ' ' . (('varchar' == self::$columns_names[$_column][1]) ? 'IS NULL' : '= 0');
            $filter_not_empty = $column_with_alias . ' ' . (('varchar' == self::$columns_names[$_column][1]) ? 'IS NOT NULL' : '<> 0');

            if (false === strpos($_where, $filter_empty) && false === strpos($_where, $filter_not_empty)) {
                $_where = sprintf('%s AND %s', $filter_not_empty, $_where);
            }
        }

        // If where_param is provided and where contains %s or %d, use prepare
        if (null !== $where_params && (false !== strpos($_where, '%s') || false !== strpos($_where, '%d'))) {
            global $wpdb;
            $_where = is_array($where_params) ? $wpdb->prepare($_where, ...$where_params) : $wpdb->prepare($_where, $where_params);
        }

        return $_where;
    }

    /**
     * Translates user-friendly operators into SQL conditions
     */
    public static function get_single_where_clause($_dimension = 'id', $_operator = 'equals', $_value = '', $_slim_stats_table_alias = '')
    {
        // Auto-upgrade operators for multi-value columns where exact match
        // never works (values stored as concatenated strings in a single field).
        $multi_value_like_columns = ['outbound_resource', 'notes'];
        if ($_operator === 'equals' && in_array($_dimension, $multi_value_like_columns, true)) {
            $_operator = 'contains';
        }
        if ($_operator === 'is_not_equal_to' && in_array($_dimension, $multi_value_like_columns, true)) {
            $_operator = 'does_not_contain';
        }
        // Category uses comma-separated IDs — use LIKE for substring matching.
        if ($_operator === 'equals' && $_dimension === 'category') {
            $_operator = 'contains';
        }
        if ($_operator === 'is_not_equal_to' && $_dimension === 'category') {
            $_operator = 'does_not_contain';
        }

        $filter_empty     = (!empty(self::$columns_names[$_dimension]) && 'varchar' == self::$columns_names[$_dimension][1]) ? 'IS NULL' : '= 0';
        $filter_not_empty = (!empty(self::$columns_names[$_dimension]) && 'varchar' == self::$columns_names[$_dimension][1]) ? 'IS NOT NULL' : '<> 0';

        $column_with_alias = $_dimension;
        if (!empty($_slim_stats_table_alias)) {
            $column_with_alias = $_slim_stats_table_alias . '.' . $_dimension;
        }

        switch ($_dimension) {
            case 'ip':
            case 'other_ip':
                $filter_empty = '= "0.0.0.0"';
                break;
            default:
                break;
        }

        if ('resource' == $_dimension) {
            $_value = implode('/', array_map('urlencode', explode('/', $_value)));
        }

        $where = ['', htmlentities($_value, ENT_QUOTES, 'UTF-8')];

        switch ($_operator) {
            case 'is_not_equal_to':
                $where[0] = sprintf('%s <> %%s', $column_with_alias);
                break;

            case 'contains':
                $where = [sprintf('%s LIKE %%s', $column_with_alias), '%' . $_value . '%'];
                break;

            case 'includes_in_set':
            case 'included_in_set':
                $where[0] = sprintf('FIND_IN_SET( %s, %%s ) > 0', $column_with_alias);
                break;

            case 'does_not_contain':
                $where = [sprintf('%s NOT LIKE %%s', $column_with_alias), '%' . $_value . '%'];
                break;

            case 'starts_with':
                $where = [sprintf('%s LIKE %%s', $column_with_alias), $_value . '%'];
                break;

            case 'ends_with':
                $where = [sprintf('%s LIKE %%s', $column_with_alias), '%' . $_value];
                break;

            case 'sounds_like':
                $where[0] = sprintf('SOUNDEX( %s ) = SOUNDEX( %%s )', $column_with_alias);
                break;

            case 'is_empty':
                $where = [sprintf('%s %s', $column_with_alias, $filter_empty), ''];
                break;

            case 'is_not_empty':
                $where = [sprintf('%s %s', $column_with_alias, $filter_not_empty), ''];
                break;

            case 'is_greater_than':
                $where[0] = sprintf('%s > %%s', $column_with_alias);
                break;

            case 'is_less_than':
                $where[0] = sprintf('%s < %%s', $column_with_alias);
                break;

            case 'between':
                $range = explode(',', $_value);
                $where[0] = sprintf('%s BETWEEN %%d AND %%d', $column_with_alias);
                $where[1] = [intval($range[0]), intval($range[1])];
                break;

            case 'matches':
                $where[0] = sprintf('%s REGEXP %%s', $column_with_alias);
                break;

            case 'does_not_match':
                $where[0] = sprintf('%s NOT REGEXP %%s', $column_with_alias);
                break;

            default:
                $where[0] = sprintf('%s = %%s', $column_with_alias);
                break;
        }

        if (isset($where[1]) && '' != $where[1]) {
            // Handle array of values for operators like 'between'
            if (is_array($where[1])) {
                return $GLOBALS['wpdb']->prepare($where[0], ...$where[1]);
            }
            return $GLOBALS['wpdb']->prepare($where[0], $where[1]);
        } else {
            return $where[0];
        }
    }

    /**
     * Helper to enable caching on a Query object if the date range does not include today.
     *
     * @param Query $query
     */
    protected static function maybe_enable_query_cache($query)
    {
        // Use the end date from normalized filters (if available)
        if (!empty(self::$filters_normalized['utime']['end'])) {
            // Convert to Y-m-d for comparison (Query expects string date)
            $to = wp_date('Y-m-d', self::$filters_normalized['utime']['end']);
            if (method_exists($query, 'canUseCacheForDateRange')) {
                $query->canUseCacheForDateRange($to);
            }
        }
    }

    public static function get_results($_sql = '', $_select_no_aggregate_values = '', $_order_by = '', $_group_by = '', $_aggregate_values_add = '')
    {
        $_sql = apply_filters('slimstat_get_results_sql', $_sql, $_select_no_aggregate_values, $_order_by, $_group_by, $_aggregate_values_add);

        if ('on' == wp_slimstat::$settings['show_sql_debug']) {
            self::$debug_message .= sprintf("<p class='debug'>%s</p>", $_sql);
        }

        $table    = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $sql_trim = ltrim($_sql);

        // Try to convert SQL to Query class for better caching and performance
        if (0 === stripos($sql_trim, 'select') && false !== stripos($sql_trim, $table)) {
            // Add caching for SELECT queries
            $cache_key      = 'slimstat_query_' . md5($_sql);
            $cached_results = get_transient($cache_key);
            if (false !== $cached_results) {
                return $cached_results;
            }

            // Try to parse and convert to Query class
            if (preg_match('/SELECT (.+) FROM [^ ]+ WHERE (.+?)( GROUP BY (.+?))?( ORDER BY (.+?))?( LIMIT (\d+), (\d+))?/is', $_sql, $m)) {
                $columns      = trim($m[1]);
                $where        = trim($m[2]);
                $group_by     = isset($m[4]) ? trim($m[4]) : '';
                $order_by     = isset($m[6]) ? trim($m[6]) : '';
                $limit_offset = isset($m[8]) ? intval($m[8]) : 0;
                $limit_count  = isset($m[9]) ? intval($m[9]) : 100;

                $q = Query::select($columns)->from($table);

                if ($where && '1=1' !== $where) {
                    $q->whereRaw($where);
                }

                if ('' !== $group_by && '0' !== $group_by) {
                    $q->groupBy($group_by);
                }

                if ('' !== $order_by && '0' !== $order_by) {
                    $q->orderBy($order_by);
                }

                $page = ($limit_offset / $limit_count) + 1;
                $q->perPage($page, $limit_count);
                $q->allowCaching(true);
                $result = $q->getAll();
                set_transient($cache_key, $result, 10 * MINUTE_IN_SECONDS);
                return $result;
            }
        }

        // Fallback to wpdb for complex queries — use wp_slimstat::$wpdb
        // so External DB addon queries the correct database.
        return wp_slimstat::$wpdb->get_results($_sql, ARRAY_A);
    }

    protected static function is_simple_count_query($sql)
    {
        $sql_trim = ltrim($sql);
        if (preg_match('/^select\s+count\s*\(.*\)\s+as\s+[a-z_][a-z0-9_]*\s+from\s+[`\w]+/i', $sql_trim) && (false === stripos($sql_trim, ' join ') && false === stripos($sql_trim, ' group by ') && false === stripos($sql_trim, ' having ') && false === stripos($sql_trim, ' union ') && false === stripos($sql_trim, ' as sub') && stripos($sql_trim, '(') === stripos($sql_trim, 'count('))) {
            // no subquery before count
            return true;
        }

        return preg_match('/^select\s+count\s*\(\s*distinct\s+.*\)\s+as\s+[a-z_][a-z0-9_]*\s+from\s+[`\w]+/i', $sql_trim) && (false === stripos($sql_trim, ' join ') && false === stripos($sql_trim, ' group by ') && false === stripos($sql_trim, ' having ') && false === stripos($sql_trim, ' union ') && false === stripos($sql_trim, ' as sub'));
    }

    public static function get_var($_sql = '', $_aggregate_value = '')
    {
        $_sql = apply_filters('slimstat_get_var_sql', $_sql, $_aggregate_value);

        if ('on' == wp_slimstat::$settings['show_sql_debug']) {
            self::$debug_message .= sprintf("<p class='debug'>%s</p>", $_sql);
        }

        $table = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $sql_trim = ltrim($_sql);

        // Try to convert to Query class for better performance
        if (0 === stripos($sql_trim, 'select') && false !== stripos($sql_trim, $table)) {
            // Parse simple count queries
            if (preg_match('/^SELECT\s+COUNT\s*\(\s*(\*|DISTINCT\s+(\w+))\s*\)\s+AS\s+(\w+)\s+FROM\s+[`\w]+(?:\s+WHERE\s+(.+?))?$/i', $sql_trim, $matches)) {
                $count_field = $matches[1];
                $alias = $matches[3];
                $where_clause = isset($matches[4]) ? trim($matches[4]) : '';

                $query = Query::select($count_field)->from($table);

                if ($where_clause !== '' && $where_clause !== '0') {
                    $query->whereRaw($where_clause);
                }

                $query->allowCaching(true);
                return $query->getVar();
            }

            // Parse other aggregate queries
            if (preg_match('/^SELECT\s+(\w+\([^)]+\))\s+AS\s+(\w+)\s+FROM\s+[`\w]+(?:\s+WHERE\s+(.+?))?$/i', $sql_trim, $matches)) {
                $aggregate = $matches[1];
                $alias = $matches[2];
                $where_clause = isset($matches[3]) ? trim($matches[3]) : '';

                $query = Query::select($aggregate)->from($table);

                if ($where_clause !== '' && $where_clause !== '0') {
                    $query->whereRaw($where_clause);
                }

                $query->allowCaching(true);
                return $query->getVar();
            }
        }

        // Fallback to wpdb for complex queries
        if (0 === stripos(trim($_sql), 'select')) {
            $query = Query::select('*')->from('(' . $_sql . ') as sub');
            self::maybe_enable_query_cache($query);
            return $query->getVar();
        } else {
            return wp_slimstat::$wpdb->get_var($_sql);
        }
    }

    public static function parse_filters($_filters_raw)
    {
        $filters_parsed = [
            'columns' => [],
            'date'    => [],
        ];

        if (!empty($_filters_raw)) {
            $matches = explode('&&&', $_filters_raw);

            foreach ($matches as $a_match) {
                // Third group AND its leading separator are optional so value-less
                // operators survive a URL round-trip (sanitize_text_field() trims the
                // trailing space the form-builder appends). See #305.
                preg_match('/([^\s]+)\s([^\s]+)(?:\s(.+))?/', urldecode($a_match), $a_filter);

                if ([] === $a_filter || ((!array_key_exists($a_filter[1], self::$all_columns_names) || false !== strpos($a_filter[1], 'no_filter')) && false === strpos($a_filter[1], 'addon_'))) {
                    continue;
                }

                // Preserve "malformed (no value) → drop" semantics for value-bearing
                // operators now that the regex no longer requires a value. Value-less
                // operators (is_empty/is_not_empty) are explicitly allowed through — but
                // only for real data columns: a value-less op aimed at a date/misc switch
                // key (strtotime, minute, …) would dereference the absent $a_filter[3]
                // below, so drop it here too. See #305.
                if (!isset($a_filter[3])
                    && (!in_array($a_filter[2], self::$valueless_operators, true)
                        || in_array($a_filter[1], self::NON_COLUMN_FILTER_KEYS, true))) {
                    continue;
                }

                switch ($a_filter[1]) {
                    case 'strtotime':
                        $custom_date = strtotime($a_filter[3], wp_slimstat::date_i18n('U'));

                        $filters_parsed['date']['minute'] = intval(date('i', $custom_date));
                        $filters_parsed['date']['hour']   = intval(date('H', $custom_date));
                        $filters_parsed['date']['day']    = intval(date('j', $custom_date));
                        $filters_parsed['date']['month']  = intval(date('n', $custom_date));
                        $filters_parsed['date']['year']   = intval(date('Y', $custom_date));
                        break;

                    case 'minute':
                    case 'hour':
                    case 'day':
                    case 'month':
                    case 'year':
                        if (is_numeric($a_filter[3])) {
                            $filters_parsed['date'][$a_filter[1]] = intval($a_filter[3]);
                        } else {
                            // Try to apply strtotime to value
                            self::toggle_date_i18n_filters(false);
                            switch ($a_filter[1]) {
                                case 'minute':
                                    $filters_parsed['date']['minute'] = intval(wp_date('i', strtotime($a_filter[3], date_i18n('U'))));
                                    break;

                                case 'hour':
                                    $filters_parsed['date']['hour'] = intval(wp_date('H', strtotime($a_filter[3], date_i18n('U'))));
                                    break;

                                case 'day':
                                    $filters_parsed['date']['day'] = intval(wp_date('j', strtotime($a_filter[3], date_i18n('U'))));
                                    break;

                                case 'month':
                                    $filters_parsed['date']['month'] = intval(wp_date('n', strtotime($a_filter[3], date_i18n('U'))));
                                    break;

                                case 'year':
                                    $filters_parsed['date']['year'] = intval(wp_date('Y', strtotime($a_filter[3], date_i18n('U'))));
                                    break;

                                default:
                                    break;
                            }

                            self::toggle_date_i18n_filters(true);

                            if (false === $filters_parsed['date'][$a_filter[1]]) {
                                unset($filters_parsed['date'][$a_filter[1]]);
                            }
                        }

                        break;

                    case 'interval':
                    case 'interval_hours':
                    case 'interval_minutes':
                        $filters_parsed['date'][$a_filter[1]] = intval($a_filter[3]);
                        break;

                    case 'limit_results':
                    case 'start_from':
                        $filters_parsed['misc'][$a_filter[1]] = str_replace('\\', '', htmlspecialchars_decode($a_filter[3]));
                        break;

                    case 'content_id':
                        if (isset($a_filter[3]) && ('' !== $a_filter[3] && '0' !== $a_filter[3])) {
                            $content_id                              = ('current' == $a_filter[3] && !empty($GLOBALS['post']->ID)) ? $GLOBALS['post']->ID : $a_filter[3];
                            $filters_parsed['columns'][$a_filter[1]] = [$a_filter[2], $content_id];
                            break;
                        }
                        // no break here: if value IS numeric, go to the default parser here below

                    default:
                        $filter_op    = $a_filter[2];
                        $filter_value = isset($a_filter[3]) ? str_replace('\\', '', htmlspecialchars_decode($a_filter[3])) : '';
                        if (in_array($filter_op, self::$valueless_operators, true)) {
                            // Value-less by design — store an empty value, scrubbing any stale
                            // UI value the SQL builder would ignore anyway. See #305.
                            $filters_parsed['columns'][$a_filter[1]] = [$filter_op, ''];
                        } elseif (trim($filter_value) !== '') {
                            // Ignore value-bearing filters submitted without a value.
                            $filters_parsed['columns'][$a_filter[1]] = [$filter_op, $filter_value];
                        }
                        break;
                }
            }
        }

        return $filters_parsed;
    }

    public static function init_filters($_filters_raw = '')
    {
        $fn = self::parse_filters($_filters_raw);

        // Initialize default values
        if (empty($fn['misc']['limit_results'])) {
            $fn['misc']['limit_results'] = wp_slimstat::$settings['limit_results'];
        }

        if (empty($fn['misc']['start_from'])) {
            $fn['misc']['start_from'] = 0;
        }

        $fn['utime'] = [
            'start' => 0,
            'end'   => 0,
        ];

        // Normalize the various date values
        wp_slimstat::toggle_date_i18n_filters(false);

        // Intervals
        // If neither an interval nor interval_hours were specified...
        if (!isset($fn['date']['interval_minutes']) && !isset($fn['date']['interval_hours']) && !isset($fn['date']['interval'])) {
            $fn['date']['interval_minutes'] = 0;
            $fn['date']['interval_hours']   = 0;

            // If a day has been specified, then interval = 1 (show only that day)
            if (!empty($fn['date']['day'])) {
                $fn['date']['interval'] = -1;
            } elseif (empty(wp_slimstat::$settings['use_current_month_timespan']) || 'on' != wp_slimstat::$settings['use_current_month_timespan']) {
                $fn['date']['interval'] = -abs(wp_slimstat::$settings['posts_column_day_interval']);
            } else {
                $fn['date']['interval'] = -intval(date_i18n('j'));
            }
        } else {
            if (empty($fn['date']['interval_minutes'])) {
                // interval was set, but not interval_hours
                $fn['date']['interval_minutes'] = 0;
            }

            if (empty($fn['date']['interval_hours'])) {
                // interval_hours was set, but not interval
                $fn['date']['interval_hours'] = 0;
            }

            if (empty($fn['date']['interval'])) {
                // interval_hours was set, but not interval
                $fn['date']['interval'] = 0;
            }
        }

        $fn['utime']['range'] = $fn['date']['interval'] * 86400 + $fn['date']['interval_hours'] * 3600 + $fn['date']['interval_minutes'] * 60;

        // Day
        if (empty($fn['date']['day'])) {
            $fn['date']['day'] = intval(date_i18n('j'));
        }

        // Month
        if (empty($fn['date']['month'])) {
            $fn['date']['month'] = intval(date_i18n('n'));
        }

        // Year
        if (empty($fn['date']['year'])) {
            $fn['date']['year'] = intval(date_i18n('Y'));
        }

        if ($fn['utime']['range'] < 0) {
            $fn['utime']['end'] = mktime(
                empty($fn['date']['hour']) ? 23 : $fn['date']['hour'],
                empty($fn['date']['minute']) ? 59 : $fn['date']['minute'],
                59,
                $fn['date']['month'],
                $fn['date']['day'],
                $fn['date']['year']
            );

            // If end is in the future and the level of granularity is hours, set it to now
            if (!empty($fn['date']['interval_hours']) && $fn['utime']['end'] > date_i18n('U')) {
                $fn['utime']['end'] = intval(date_i18n('U'));
            }

            // Add 1 second to account for the time difference between midnight and 23:59:59
            $fn['utime']['range'] += 1;
            $fn['utime']['start'] = $fn['utime']['end'] + $fn['utime']['range'];

            // Store the absolute value for later (chart)
            $fn['utime']['range'] = -$fn['utime']['range'];
        } else {
            $fn['utime']['start'] = mktime(
                empty($fn['date']['hour']) ? 0 : $fn['date']['hour'],
                empty($fn['date']['minute']) ? 0 : $fn['date']['minute'],
                0,
                $fn['date']['month'],
                $fn['date']['day'],
                $fn['date']['year']
            );

            $fn['utime']['range'] -= 1;
            $fn['utime']['end'] = $fn['utime']['start'] + $fn['utime']['range'];
        }

        // If end is in the future, set it to now
        if ($fn['utime']['end'] > date_i18n('U')) {
            $fn['utime']['end'] = intval(date_i18n('U'));
        }

        // Turn the date_i18n filters back on
        wp_slimstat::toggle_date_i18n_filters(true);

        // Apply third-party filters
        $fn = apply_filters('slimstat_db_filters_normalized', $fn, $_filters_raw);

        return $fn;
    }

    // The following methods retrieve the information from the database

    public static function count_bouncing_pages()
    {
        $where = self::get_combined_where('visit_id > 0 AND content_type <> "404"', 'resource');

        return intval(self::get_var(
            "
			SELECT COUNT(*) counthits
				FROM (
					SELECT resource, visit_id
					FROM {$GLOBALS['wpdb']->prefix}slim_stats
					WHERE {$where}
					GROUP BY resource
					HAVING COUNT(visit_id) = 1
				) as ts1",
            'SUM(counthits) AS counthits'
        ));
    }

    public static function count_exit_pages()
    {
        $where = self::get_combined_where('visit_id > 0', 'resource');

        return intval(self::get_var(
            "
			SELECT COUNT(*) counthits
				FROM (
					SELECT resource, dt
					FROM {$GLOBALS['wpdb']->prefix}slim_stats
					WHERE {$where}
					GROUP BY resource
					HAVING dt = MAX(dt)
				) AS ts1",
            'SUM(counthits) AS counthits'
        ));
    }

    public static function count_records($_column = 'id', $_where = '', $_use_date_filters = true, $where_params = [])
    {
        // Validating the column
        if (false === in_array($_column, ['id', 'ip', 'other_ip', 'username', 'email', 'country', 'location', 'city', 'referer', 'resource', 'searchterms', 'notes', 'visit_id', 'server_latency', 'page_performance', 'browser', 'browser_version', 'browser_type', 'platform', 'language', 'fingerprint', 'user_agent', 'resolution', 'screen_width', 'screen_height', 'content_type', 'category', 'author', 'content_id', 'outbound_resource', 'tz_offset', 'dt_out', 'dt'])) {
            return null;
        }

        $table = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $distinct_column = ('id' != $_column) ? 'DISTINCT ' . $_column : $_column;

        $query = Query::select(sprintf('COUNT(%s) as counthits', $distinct_column))->from($table);

        // Add date filters if needed
        if ($_use_date_filters && !empty(self::$filters_normalized['utime']['start']) && !empty(self::$filters_normalized['utime']['end'])) {
            $query->where('dt', 'BETWEEN', [intval(self::$filters_normalized['utime']['start']), intval(self::$filters_normalized['utime']['end'])]);
        }

		if (
			!empty($_where)
			&& !empty($where_params)
			&& (false !== strpos($_where, '%s') || false !== strpos($_where, '%d') || false !== strpos($_where, '%f'))
		) {
			$_where = is_array($where_params) ? $GLOBALS['wpdb']->prepare($_where, ...$where_params) : $GLOBALS['wpdb']->prepare($_where, $where_params);
		}

        // Add custom where clause
        if (!empty($_where)) {
            $query->whereRaw($_where);
        }

        // Add other filters
        if (!empty(self::$filters_normalized['columns'])) {
            $where_clause = self::_get_sql_where(self::$filters_normalized['columns']);
            if (!empty($where_clause)) {
                $query->whereRaw($where_clause);
            }
        }

        $query->allowCaching(true);
        return intval($query->getVar());
    }

    public static function count_records_having($_column = 'id', $_where = '', $_having = '')
    {
        // Allowlist: only known schema columns are allowed as identifiers
        $allowed_columns = array_keys(self::$columns_names);
        if (!in_array($_column, $allowed_columns, true)) {
            return 0;
        }

        $table = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $distinct_column = ('id' !== $_column) ? 'DISTINCT ' . esc_sql($_column) : esc_sql($_column);

        $query = Query::select("COUNT(*) as counthits")
            ->from("(
                SELECT {$distinct_column}
                FROM {$table}
                WHERE " . self::get_combined_where($_where, $_column) . "
                GROUP BY " . esc_sql($_column) . "
                HAVING {$_having}
            ) AS ts1");

        $query->allowCaching(true);
        return intval($query->getVar());
    }

    public static function get_data_size()
    {
        $suffix = 'KB';

        $sql           = 'SHOW TABLE STATUS LIKE "' . $GLOBALS['wpdb']->prefix . 'slim_stats"';
        $table_details = wp_slimstat::$wpdb->get_row($sql, 'ARRAY_A', 0);

        $table_size = ($table_details['Data_length'] / 1024) + ($table_details['Index_length'] / 1024);

        if ($table_size > 1024) {
            $table_size /= 1024;
            $suffix = 'MB';
        }

        return number_format_i18n($table_size, 2) . ' ' . $suffix;
    }

    public static function get_group_by($_args = [])
    {
        if (empty($_args['column_group'])) {
            $_args['column_group'] = 'id';
        }

        if (empty($_args['group_by'])) {
            $_args['group_by'] = 'id';
        }

        $table = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $query = Query::select([
            $_args['group_by'],
            'COUNT(*) AS counthits',
            sprintf("GROUP_CONCAT( DISTINCT %s SEPARATOR ';;;' ) as column_group", $_args['column_group'])
        ])->from($table);

        // Add date filters if needed
        if (!empty(self::$filters_normalized['utime']['start']) && !empty(self::$filters_normalized['utime']['end'])) {
            $query->where('dt', 'BETWEEN', [intval(self::$filters_normalized['utime']['start']), intval(self::$filters_normalized['utime']['end'])]);
        }

        // Add other filters
        if (!empty(self::$filters_normalized['columns'])) {
            $where_clause = self::_get_sql_where(self::$filters_normalized['columns']);
            if (!empty($where_clause)) {
                $query->whereRaw($where_clause);
            }
        }

        // Add IS NOT NULL condition
        $query->where($_args['group_by'], 'IS NOT', null);

        // GROUP BY
        $query->groupBy($_args['group_by']);

        // ORDER BY — tie-breaker on group key for deterministic pagination
        $query->orderBy('counthits DESC, ' . $_args['group_by'] . ' ASC');

        // LIMIT — no SQL OFFSET; PHP-side pagination in show_group_by()
        $limit = max(1, intval(self::$filters_normalized['misc']['limit_results']));
        $query->limit($limit);

        $query->allowCaching(true);
        return $query->getAll();
    }

    public static function get_max_and_average_pages_per_visit()
    {
        $where = self::get_combined_where('visit_id > 0');
        $table = $GLOBALS['wpdb']->prefix . 'slim_stats';

        $subQuery = sprintf('SELECT count(ip) counthits, visit_id FROM %s WHERE %s GROUP BY visit_id', $table, $where);

        $query = Query::select('AVG(ts1.counthits) AS avghits, MAX(ts1.counthits) AS maxhits')
            ->from(sprintf('(%s) AS ts1', $subQuery));

        self::maybe_enable_query_cache($query);
        return $query->getAll();
    }

    public static function get_oldest_visit()
    {
        $table = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $query = Query::select('dt')->from($table)->orderBy('dt', 'ASC')->limit(1);
        $query->allowCaching(true, DAY_IN_SECONDS);
        return $query->getVar();
    }

    public static function get_overview_summary()
    {
        $days_in_range = ceil((wp_slimstat_db::$filters_normalized['utime']['end'] - wp_slimstat_db::$filters_normalized['utime']['start']) / 86400);
        $days_in_range = ($days_in_range < 1) ? 1 : $days_in_range;

        $results = [];

        // Turn date_i18n filters off
        wp_slimstat::toggle_date_i18n_filters(false);

        // Ensure pageviews is initialized for Dashboard widgets
        if (0 === self::$pageviews) {
            self::$pageviews = wp_slimstat_db::count_records();
        }

        $results[0]['metric']  = __('Pageviews', 'wp-slimstat');
        $results[0]['value']   = number_format_i18n(self::$pageviews, 0);
        $results[0]['tooltip'] = __('A pageview is a request to load a single HTML page on your website.', 'wp-slimstat');

        $results[1]['metric'] = __('Days in Range', 'wp-slimstat');
        $results[1]['value']  = $days_in_range;

        $results[2]['metric']  = __('Average Daily Pageviews', 'wp-slimstat');
        $results[2]['value']   = number_format_i18n(round(self::$pageviews / $days_in_range, 0));
        $results[2]['tooltip'] = __('How many daily pageviews have been generated on average.', 'wp-slimstat');

        $results[3]['metric']  = __('From Any SERP', 'wp-slimstat');
        $results[3]['value']   = number_format_i18n(wp_slimstat_db::count_records('id', 'searchterms IS NOT NULL'));
        $results[3]['tooltip'] = __('Visitors who landed on your site after searching for a keyword on a search engine and clicking on the corresponding search result link. This value includes both internal and external search result pages.', 'wp-slimstat');

        $results[4]['metric']  = __('Unique IPs', 'wp-slimstat');
        $results[4]['value']   = number_format_i18n(wp_slimstat_db::count_records('ip'));
        $results[4]['tooltip'] = __('Used to differentiate between multiple requests to download a file from one internet address (IP) and requests originating from many distinct addresses.', 'wp-slimstat');

        $results[5]['metric'] = __('Last 30 minutes', 'wp-slimstat');
        $results[5]['value']  = number_format_i18n(wp_slimstat_db::count_records('id', 'dt > ' . (wp_slimstat::now() - 1800), false));

        $results[6]['metric'] = __('Today', 'wp-slimstat');
        $results[6]['value']  = number_format_i18n(wp_slimstat_db::count_records('id', 'dt > ' . (wp_slimstat::date_i18n('U', mktime(0, 0, 0, wp_slimstat::date_i18n('m'), wp_slimstat::date_i18n('d'), wp_slimstat::date_i18n('Y')))), false));

        $results[7]['metric'] = __('Yesterday', 'wp-slimstat');
        $results[7]['value']  = number_format_i18n(wp_slimstat_db::count_records('id', 'dt BETWEEN ' . (wp_slimstat::date_i18n('U', mktime(0, 0, 0, wp_slimstat::date_i18n('m'), wp_slimstat::date_i18n('d') - 1, wp_slimstat::date_i18n('Y')))) . ' AND ' . (wp_slimstat::date_i18n('U', mktime(23, 59, 59, wp_slimstat::date_i18n('m'), wp_slimstat::date_i18n('d') - 1, wp_slimstat::date_i18n('Y')))), false));

        // Turn date_i18n filters back on
        wp_slimstat::toggle_date_i18n_filters(true);

        return $results;
    }

    public static function get_recent($_column = 'id', $_where = '', $_having = '', $_use_date_filters = true, $_as_column = '', $_more_columns = '', $_order_by = 'dt DESC')
    {
        if (is_array($_column)) {
            $_where            = empty($_column['where']) ? '' : $_column['where'];
            $_having           = empty($_column['having']) ? '' : $_column['having'];
            $_use_date_filters = $_column['use_date_filters'] ?? true;
            $_as_column        = empty($_column['as_column']) ? '' : $_column['as_column'];
            $_more_columns     = empty($_column['more_columns']) ? '' : $_column['more_columns'];
            $_order_by         = empty($_column['order_by']) ? 'dt DESC' : $_column['order_by'];
            $_column           = $_column['columns'];
        }

        $columns = ('*' === $_column)
            ? ['id', 'ip', 'dt', 'username', 'referer', 'resource', 'browser', 'platform', 'country', 'city', 'content_type', 'notes', 'visit_id', 'server_latency', 'page_performance', 'browser_version', 'browser_type', 'language', 'fingerprint', 'user_agent', 'resolution', 'screen_width', 'screen_height', 'category', 'author', 'content_id', 'outbound_resource', 'tz_offset', 'dt_out']
            : array_map('trim', explode(',', $_column));
        if (!empty($_as_column)) {
            $columns[0] = $columns[0] . ' AS ' . $_as_column;
        }

        if (!empty($_more_columns)) {
            $more_cols = array_map('trim', explode(',', $_more_columns));
            $columns   = array_merge($columns, $more_cols);
        }

        if (!in_array('dt', $columns)) {
            $columns[] = 'dt';
        }

        if (!in_array('ip', $columns)) {
            $columns[] = 'ip';
        }

        $table = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $query = Query::select(implode(', ', $columns))->from($table);

        // Always add date filter as a proper where() clause so placeholders are replaced
        if ($_use_date_filters && !empty(self::$filters_normalized['utime']['start']) && !empty(self::$filters_normalized['utime']['end']) && !$query->hasWhereClause('dt', 'BETWEEN')) {
            $query->where('dt', 'BETWEEN', [intval(self::$filters_normalized['utime']['start']), intval(self::$filters_normalized['utime']['end'])]);
        }

        // Apply active column filters (e.g., browser equals Chrome) using the existing normalization logic
        if (!empty(self::$filters_normalized['columns'])) {
            $normalized_where = self::_get_sql_where(self::$filters_normalized['columns']);
            if (!empty($normalized_where)) {
                $query->whereRaw($normalized_where);
            }
        }

        // Only add additional non-parameterized conditions passed via $_where
        if (!empty($_where)) {
            $query->whereRaw($_where);
        }

        // HAVING
        if (!empty($_having)) {
			$query->havingRaw($_having);
        }

        // ORDER BY
        if (!empty($_order_by)) {
            $query->orderBy($_order_by);
        }

        // LIMIT
        $start = max(0, intval(self::$filters_normalized['misc']['start_from']));
        $limit = max(1, intval(self::$filters_normalized['misc']['limit_results']));
        $query->limit($limit, $start);

        $query->allowCaching(false);


        return $query->getAll();
    }

    public static function get_recent_events()
    {
        return self::get_results(
            "
			SELECT te.*, t1.ip, t1.resource
			FROM {$GLOBALS[ 'wpdb' ]->prefix}slim_events te INNER JOIN {$GLOBALS[ 'wpdb' ]->prefix}slim_stats t1 ON te.id = t1.id
			WHERE " . wp_slimstat_db::get_combined_where('te.notes NOT LIKE "_ype:click%"', 'te.notes', true, 't1') . '
			ORDER BY te.dt DESC',
            'te.*, t1.resource',
            'dt DESC'
        );
    }

    public static function get_recent_outbound()
    {
        $mixed_outbound_resources = self::get_recent('outbound_resource', "outbound_resource IS NOT NULL AND outbound_resource != ''", '', true, '', 'dt, dt_out');
        $clean_outbound_resources = [];

        foreach ($mixed_outbound_resources as $a_mixed_resource) {
            // Prefer dt_out (actual outbound click time) over dt (pageview creation time)
            $row_dt = isset($a_mixed_resource['dt_out']) && intval($a_mixed_resource['dt_out']) > 0
                ? intval($a_mixed_resource['dt_out'])
                : (isset($a_mixed_resource['dt']) ? intval($a_mixed_resource['dt']) : 0);
            $exploded_resources = explode(';;;', $a_mixed_resource['outbound_resource'] ?? '');
            foreach ($exploded_resources as $a_exploded_resource) {
                if ($a_exploded_resource !== '') {
                    $clean_outbound_resources[] = ['url' => $a_exploded_resource, 'dt' => $row_dt];
                }
            }
        }

        return $clean_outbound_resources;
    }

    public static function get_top($_column = 'id', $_where = '', $_having = '', $_use_date_filters = true, $_as_column = '')
    {
        $_order_by    = 'counthits DESC';
        $_more_select = '';

        // This function can be passed individual arguments, or an array of arguments
        if (is_array($_column)) {
            $where_params = !empty($_column['where_params']) ? $_column['where_params'] : [];
            $_where       = !empty($_column['where']) ? $_column['where'] : '';

			if (
				!empty($_where)
				&& !empty($where_params)
				&& (false !== strpos($_where, '%s') || false !== strpos($_where, '%d') || false !== strpos($_where, '%f'))
			) {
				$_where = is_array($where_params) ? $GLOBALS['wpdb']->prepare($_where, ...$where_params) : $GLOBALS['wpdb']->prepare($_where, $where_params);
			}

            $_having           = empty($_column['having']) ? '' : $_column['having'];
            $_use_date_filters = isset($_column['use_date_filters']) ? (bool)$_column['use_date_filters'] : true;
            $_as_column        = empty($_column['as_column']) ? '' : $_column['as_column'];
            $_order_by         = empty($_column['order_by']) ? 'counthits DESC' : $_column['order_by'];
            $_more_select      = empty($_column['more_select']) ? '' : $_column['more_select'];
            $_column           = $_column['columns'];
        }

        $group_by_column = $_column;

        if (!empty($_as_column)) {
            $_column = sprintf('%s AS %s', $_column, $_as_column);
        } else {
            $_as_column = $_column;
        }

        $table = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $select_cols = [$_column, 'COUNT(*) AS counthits'];
        if (!empty($_more_select)) {
            $select_cols[] = $_more_select;
        }
        $query = Query::select($select_cols)->from($table);

        // Add date filters if needed
        if ($_use_date_filters && !empty(self::$filters_normalized['utime']['start']) && !empty(self::$filters_normalized['utime']['end'])) {
            $query->where('dt', 'BETWEEN', [intval(self::$filters_normalized['utime']['start']), intval(self::$filters_normalized['utime']['end'])]);
        }

        // Add custom where clause
        if (!empty($_where)) {
            $query->whereRaw($_where);
        }

        // Add other filters
        if (!empty(self::$filters_normalized['columns'])) {
            $where_clause = self::_get_sql_where(self::$filters_normalized['columns']);
            if (!empty($where_clause)) {
                $query->whereRaw($where_clause);
            }
        }

        // GROUP BY
        $query->groupBy($group_by_column);

        // HAVING
		if (!empty($_having)) {
			$query->havingRaw($_having);
		}

        // ORDER BY — append group key as tie-breaker for deterministic
        // pagination when many rows share the primary sort value.
        $order_with_tiebreak = $_order_by;
        if (false === stripos($_order_by, $group_by_column)) {
            $order_with_tiebreak .= ', ' . $group_by_column . ' ASC';
        }
        $query->orderBy($order_with_tiebreak);

        // LIMIT — no SQL OFFSET for aggregated reports; PHP-side pagination
        // handles page slicing via array_slice in the rendering callbacks.
        $limit = max(1, intval(self::$filters_normalized['misc']['limit_results']));
        $query->limit($limit);

        $query->allowCaching(true);
        return $query->getAll();
    }

    public static function get_top_aggr($_column = 'id', $_where = '', $_outer_select_column = '', $_aggr_function = 'MAX')
    {
        if (is_array($_column)) {
            $_where               = empty($_column['where']) ? '' : $_column['where'];
            $_having              = empty($_column['having']) ? '' : $_column['having'];
            $_use_date_filters    = empty($_column['use_date_filters']) ? true : $_column['use_date_filters'];
            $_as_column           = empty($_column['as_column']) ? '' : $_column['as_column'];
            $_outer_select_column = empty($_column['outer_select_column']) ? '' : $_column['outer_select_column'];
            $_aggr_function       = empty($_column['aggr_function']) ? '' : $_column['aggr_function'];
            $_column              = $_column['columns'];
        }

        if (!empty($_as_column)) {
            $_column = sprintf('%s AS %s', $_column, $_as_column);
        } else {
            $_as_column = $_column;
        }

        $_where = self::get_combined_where($_where, $_column);
        $table  = $GLOBALS['wpdb']->prefix . 'slim_stats';

		$subQuerySql = sprintf('SELECT %s, %s(id) as aggrid FROM %s WHERE %s GROUP BY %s', $_column, $_aggr_function, $table, $_where, $_column);

		$query = Query::select(sprintf('%s, ts1.aggrid as %s, COUNT(*) as counthits', $_outer_select_column, $_column))
			->from(sprintf('(%s) AS ts1', $subQuerySql))
            ->join($table . ' t1', 'ts1.aggrid', 't1.id')
            ->groupBy($_outer_select_column)
            ->orderBy('counthits DESC')
            ->limit(max(1, intval(self::$filters_normalized['misc']['limit_results'])));

        self::maybe_enable_query_cache($query);
        return $query->getAll();
    }

    public static function get_top_events()
    {
        $table_events = $GLOBALS['wpdb']->prefix . 'slim_events';
        $table_stats  = $GLOBALS['wpdb']->prefix . 'slim_stats';

        if (empty(self::$filters_normalized['columns'])) {
            $query = Query::select('te.notes, COUNT(*) as counthits')
                ->from($table_events . ' te')
                ->whereRaw(wp_slimstat_db::get_combined_where('notes NOT LIKE "type:click%"', 'notes'));
        } else {
            $query = Query::select('te.notes, COUNT(*) as counthits')
                ->from($table_events . ' te')
                ->join($table_stats . ' t1', 'te.id', 't1.id')
                ->whereRaw(wp_slimstat_db::get_combined_where('te.notes NOT LIKE "_ype:click%"', 'te.notes', true, 't1'));
        }

        $query->groupBy('te.notes')->orderBy('counthits DESC');

        $limit = max(1, intval(self::$filters_normalized['misc']['limit_results']));
        $query->limit($limit);

        self::maybe_enable_query_cache($query);
        return $query->getAll();
    }

    public static function get_top_outbound($_args = [])
    {
        $sort_by = 'counthits';
        if (is_array($_args) && !empty($_args['sort_outbound'])) {
            $sort_by = $_args['sort_outbound'];
        }

        // Zero out start_from before fetching raw data — get_recent_outbound()
        // calls get_recent() which applies SQL OFFSET. We need the full
        // (un-offset) result set for correct aggregation; PHP-side pagination
        // in the rendering callback handles page slicing.
        $saved_start = self::$filters_normalized['misc']['start_from'];
        self::$filters_normalized['misc']['start_from'] = 0;
        try {
            $raw_outbound = self::get_recent_outbound();
        } finally {
            self::$filters_normalized['misc']['start_from'] = $saved_start;
        }

        // Aggregate: count hits and track max dt per unique URL
        $aggregated = [];
        foreach ($raw_outbound as $item) {
            $url = $item['url'];
            if (!isset($aggregated[$url])) {
                $aggregated[$url] = ['counthits' => 0, 'dt' => 0];
            }
            $aggregated[$url]['counthits']++;
            if ($item['dt'] > $aggregated[$url]['dt']) {
                $aggregated[$url]['dt'] = $item['dt'];
            }
        }

        // Sort: 'dt' for Recent panel, 'counthits' (default) for Top panel
        if ($sort_by === 'dt') {
            uasort($aggregated, static function ($a, $b) {
                return $b['dt'] <=> $a['dt'];
            });
        } else {
            uasort($aggregated, static function ($a, $b) {
                return $b['counthits'] <=> $a['counthits'] ?: $b['dt'] <=> $a['dt'];
            });
        }

        $sorted_outbound_resources = [];
        foreach ($aggregated as $url => $data) {
            $sorted_outbound_resources[] = [
                'outbound_resource' => $url,
                'counthits'         => $data['counthits'],
                'dt'                => $data['dt'],
            ];
        }

        return $sorted_outbound_resources;
    }

    public static function get_traffic_sources_summary()
    {
        $results           = [];
        $total_human_hits  = wp_slimstat_db::count_records('id', 'visit_id > 0 AND browser_type <> 1');
        $new_visitors      = wp_slimstat_db::count_records_having('ip', 'visit_id > 0', 'COUNT(visit_id) = 1');
        $new_visitors_rate = ($total_human_hits > 0) ? sprintf('%01.2f', (100 * $new_visitors / $total_human_hits)) : 0;
        $server_name       = sanitize_text_field(wp_unslash($_SERVER['SERVER_NAME']));

        if (intval($new_visitors_rate) > 99) {
            $new_visitors_rate = '100';
        }

        $results[0]['metric']  = __('Pageviews', 'wp-slimstat');
        $results[0]['value']   = number_format_i18n(self::$pageviews);
        $results[0]['tooltip'] = __('A pageview is a request to load a single HTML page on your website.', 'wp-slimstat');

        $results[1]['metric']  = __('Unique Referrers', 'wp-slimstat');
        $results[1]['value']   = number_format_i18n(wp_slimstat_db::count_records('referer', 'referer NOT LIKE %s', true, ['%' . $GLOBALS['wpdb']->esc_like($server_name) . '%']));
        $results[1]['tooltip'] = __('A referrer (or referring site) is a site that a visitor previously visited before following a link to your site.', 'wp-slimstat');

        $results[2]['metric']  = __('Direct Pageviews', 'wp-slimstat');
        $results[2]['value']   = number_format_i18n(wp_slimstat_db::count_records('id', 'resource IS NULL'));
        $results[2]['tooltip'] = __("Visitors who typed your website URL directly into their browser address bar. It can also refer to visitors who clicked on one of their bookmarked links, untagged links within emails, or links in documents that don't include tracking variables.", 'wp-slimstat');

        $results[3]['metric']  = __('From External SERP', 'wp-slimstat');
        $results[3]['value']   = number_format_i18n(wp_slimstat_db::count_records('id', 'searchterms IS NOT NULL AND referer IS NOT NULL AND referer NOT LIKE %s', true, ['%' . $GLOBALS['wpdb']->esc_like(home_url()) . '%']));
        $results[3]['tooltip'] = __('Visitors who clicked on a link to your website listed on a search engine result page (SERP). This metric only counts visits coming from EXTERNAL search pages.', 'wp-slimstat');

        $results[4]['metric']  = __('Unique Landing Pages', 'wp-slimstat');
        $results[4]['value']   = number_format_i18n(wp_slimstat_db::count_records('resource'));
        $results[4]['tooltip'] = __("A landing page is the first page on your website that a visitors opens, also known as <em>entrance page</em>. For example, if they search for 'Brooklyn Office Space,' and they land on a page on your website, this page gets counted (for that visit) as a landing page.", 'wp-slimstat');

        $results[5]['metric']  = __('Bounce Pages', 'wp-slimstat');
        $results[5]['value']   = number_format_i18n(wp_slimstat_db::count_bouncing_pages());
        $results[5]['tooltip'] = __('Number of single-page visits tracked over the selected period of time.', 'wp-slimstat');

        $results[6]['metric']  = __('New Visitors Rate', 'wp-slimstat');
        $results[6]['value']   = number_format_i18n($new_visitors_rate, 2);
        $results[6]['tooltip'] = __('Percentage of single-page visits, i.e. visits in which the person left your site from the entrance page.', 'wp-slimstat');

        $results[7]['metric']  = __('Currently from search engines', 'wp-slimstat');
        $results[7]['value']   = number_format_i18n(wp_slimstat_db::count_records('id', 'searchterms IS NOT NULL  AND referer IS NOT NULL AND referer NOT LIKE %s AND dt > UNIX_TIMESTAMP() - 300', false, ['%' . $GLOBALS['wpdb']->esc_like(home_url()) . '%']));
        $results[7]['tooltip'] = __('Visitors who clicked on a link to your website listed on a search engine result page (SERP), tracked in the last 5 minutes.', 'wp-slimstat');

        return $results;
    }

    public static function get_visits_duration()
    {
        $total_human_visits = wp_slimstat_db::count_records('visit_id', 'visit_id > 0 AND browser_type <> 1');
        $results            = [];

        $count_results             = wp_slimstat_db::count_records_having('visit_id', 'visit_id > 0 AND browser_type <> 1', '	GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) >= 0 AND GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) <= 30');
        $average_time              = 30 * $count_results;
        $results[0]['metric']      = __('0 - 30 seconds', 'wp-slimstat');
        $results[0]['value']       = (($total_human_visits > 0) ? number_format_i18n((100 * $count_results / $total_human_visits), 2) : 0) . '%';
        $results[0]['details']     = __('Hits', 'wp-slimstat') . (': ' . $count_results);
        $results[0]['counthits']   = $count_results;

        $count_results             = wp_slimstat_db::count_records_having('visit_id', 'visit_id > 0 AND browser_type <> 1', 'GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) > 30 AND GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) <= 60');
        $average_time             += 60 * $count_results;
        $results[1]['metric']      = __('31 - 60 seconds', 'wp-slimstat');
        $results[1]['value']       = (($total_human_visits > 0) ? number_format_i18n((100 * $count_results / $total_human_visits), 2) : 0) . '%';
        $results[1]['details']     = __('Hits', 'wp-slimstat') . (': ' . $count_results);
        $results[1]['counthits']   = $count_results;

        $count_results             = wp_slimstat_db::count_records_having('visit_id', 'visit_id > 0 AND browser_type <> 1', 'GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) > 60 AND GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) <= 180');
        $average_time             += 180 * $count_results;
        $results[2]['metric']      = __('1 - 3 minutes', 'wp-slimstat');
        $results[2]['value']       = (($total_human_visits > 0) ? number_format_i18n((100 * $count_results / $total_human_visits), 2) : 0) . '%';
        $results[2]['details']     = __('Hits', 'wp-slimstat') . (': ' . $count_results);
        $results[2]['counthits']   = $count_results;

        $count_results             = wp_slimstat_db::count_records_having('visit_id', 'visit_id > 0 AND browser_type <> 1', 'GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) > 180 AND GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) <= 300');
        $average_time             += 300 * $count_results;
        $results[3]['metric']      = __('3 - 5 minutes', 'wp-slimstat');
        $results[3]['value']       = (($total_human_visits > 0) ? number_format_i18n((100 * $count_results / $total_human_visits), 2) : 0) . '%';
        $results[3]['details']     = __('Hits', 'wp-slimstat') . (': ' . $count_results);
        $results[3]['counthits']   = $count_results;

        $count_results             = wp_slimstat_db::count_records_having('visit_id', 'visit_id > 0 AND browser_type <> 1', 'GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) > 300 AND GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) <= 420');
        $average_time             += 420 * $count_results;
        $results[4]['metric']      = __('5 - 7 minutes', 'wp-slimstat');
        $results[4]['value']       = (($total_human_visits > 0) ? number_format_i18n((100 * $count_results / $total_human_visits), 2) : 0) . '%';
        $results[4]['details']     = __('Hits', 'wp-slimstat') . (': ' . $count_results);
        $results[4]['counthits']   = $count_results;

        $count_results             = wp_slimstat_db::count_records_having('visit_id', 'visit_id > 0 AND browser_type <> 1', 'GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) > 420 AND GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) <= 600');
        $average_time             += 600 * $count_results;
        $results[5]['metric']      = __('7 - 10 minutes', 'wp-slimstat');
        $results[5]['value']       = (($total_human_visits > 0) ? number_format_i18n((100 * $count_results / $total_human_visits), 2) : 0) . '%';
        $results[5]['details']     = __('Hits', 'wp-slimstat') . (': ' . $count_results);
        $results[5]['counthits']   = $count_results;

        $count_results             = wp_slimstat_db::count_records_having('visit_id', 'visit_id > 0 AND browser_type <> 1', 'GREATEST( MAX( dt ), MAX( dt_out ) ) - MIN( dt ) > 600');
        $average_time             += 900 * $count_results;
        $results[6]['metric']      = __('More than 10 minutes', 'wp-slimstat');
        $results[6]['value']       = (($total_human_visits > 0) ? number_format_i18n((100 * $count_results / $total_human_visits), 2) : 0) . '%';
        $results[6]['details']     = __('Hits', 'wp-slimstat') . (': ' . $count_results);
        $results[6]['counthits']   = $count_results;

        // Sort time buckets by most hits first
        usort($results, static function ($a, $b) {
            return ($b['counthits'] ?? 0) <=> ($a['counthits'] ?? 0);
        });

        if ($total_human_visits > 0) {
            $average_time = intval($average_time / $total_human_visits);
            // gmdate (not date) so the elapsed-seconds value isn't shifted by the
            // site timezone; 'i:s' = minutes:seconds ('m' was the month token).
            $average_time = gmdate($average_time >= 3600 ? 'H:i:s' : 'i:s', $average_time);
        } else {
            $average_time = '0:00';
        }

        // Average row always at the bottom
        $results[]  = [
            'metric'  => __('Average Visit Duration', 'wp-slimstat'),
            'value'   => $average_time,
            'details' => '',
        ];

        return $results;
    }

    public static function get_visitors_summary()
    {
        $results            = [];
        $total_visits       = wp_slimstat_db::count_records('visit_id', 'browser_type <> 1');
        $single_page_visits = wp_slimstat_db::count_records_having('visit_id', 'browser_type <> 1', 'COUNT(id) = 1');

        $bounce_rate       = ($total_visits > 0) ? (100 * $single_page_visits / $total_visits) : 0;
        $metrics_per_visit = wp_slimstat_db::get_max_and_average_pages_per_visit();
        if (empty($metrics_per_visit[0])) {
            $metrics_per_visit[0] = ['avghits' => 0, 'maxhits' => 0];
        }

        if (intval($bounce_rate) > 99) {
            $bounce_rate = '100';
        }

        $results[0]['metric']  = __('Visits', 'wp-slimstat');
        $results[0]['value']   = number_format_i18n(wp_slimstat_db::count_records('visit_id', 'visit_id > 0 AND browser_type <> 1'));
        $results[0]['tooltip'] = __('A visit is a group of pageviews within a 30-minute time span. Returning visitors are counted multiple times if they start a new visit.', 'wp-slimstat');

        $results[1]['metric']  = __('Unique IPs', 'wp-slimstat');
        $results[1]['value']   = number_format_i18n(wp_slimstat_db::count_records('ip', 'visit_id > 0 AND browser_type <> 1'));
        $results[1]['tooltip'] = __('It includes only traffic generated by human visitors.', 'wp-slimstat');

        $results[2]['metric']  = __('Bounce rate', 'wp-slimstat');
        $results[2]['value']   = number_format_i18n($bounce_rate, 2);
        $results[2]['tooltip'] = __('Total number of one-page visits divided by the total number of entries to a website. Please see the <a href="https://support.google.com/analytics/answer/1009409?hl=en" target="_blank">official Google docs</a> for more information.', 'wp-slimstat');

        $results[3]['metric']  = __('Known visitors', 'wp-slimstat');
        $results[3]['value']   = number_format_i18n(wp_slimstat_db::count_records('username'));
        $results[3]['tooltip'] = __('Visitors who have previously left a comment on your blog.', 'wp-slimstat');

        $results[4]['metric']  = __('Single-page Visits', 'wp-slimstat');
        $results[4]['value']   = number_format_i18n($single_page_visits);
        $results[4]['tooltip'] = __('Human users that generated one single page view on your website.', 'wp-slimstat');

        $results[5]['metric'] = __('Bots', 'wp-slimstat');
        $results[5]['value']  = number_format_i18n(wp_slimstat_db::count_records('id', 'browser_type = 1'));

        $results[6]['metric'] = __('Pageviews per visit', 'wp-slimstat');
        $results[6]['value']  = number_format_i18n($metrics_per_visit[0]['avghits'], 2);

        $results[7]['metric'] = __('Longest visit', 'wp-slimstat');
        $results[7]['value']  = number_format_i18n($metrics_per_visit[0]['maxhits']) . ' ' . __('hits', 'wp-slimstat');

        return $results;
    }

    public static function get_your_blog()
    {
        if (false === ($results = get_transient('slimstat_your_content'))) {
            $results      = [];
            $posts_table  = $GLOBALS['wpdb']->posts;
            $comments_table = $GLOBALS['wpdb']->comments;
            $slim_stats_table = $GLOBALS['wpdb']->prefix . 'slim_stats';

            $results[0]['metric']  = __('Content Items', 'wp-slimstat');
            $results[0]['value']   = number_format_i18n(Query::select('COUNT(*)')->from($posts_table)->where('post_type', '!=', 'revision')->where('post_status', '!=', 'auto-draft')->getVar());
            $results[0]['tooltip'] = __('This value includes not only posts and pages, but any custom post type, regardless of their status.', 'wp-slimstat');

            $results[1]['metric'] = __('Posts', 'wp-slimstat');
            $results[1]['value']  = Query::select('COUNT(*)')->from($posts_table)->where('post_type', '=', 'post')->getVar();

            $results[2]['metric'] = __('Pages', 'wp-slimstat');
            $results[2]['value']  = number_format_i18n(Query::select('COUNT(*)')->from($posts_table)->where('post_type', '=', 'page')->getVar());

            $results[3]['metric'] = __('Attachments', 'wp-slimstat');
            $results[3]['value']  = number_format_i18n(Query::select('COUNT(*)')->from($posts_table)->where('post_type', '=', 'attachment')->getVar());

            $results[4]['metric'] = __('Revisions', 'wp-slimstat');
            $results[4]['value']  = number_format_i18n(Query::select('COUNT(*)')->from($posts_table)->where('post_type', '=', 'revision')->getVar());

            $results[5]['metric'] = __('Comments', 'wp-slimstat');
            $results[5]['value']  = Query::select('COUNT(*)')->from($comments_table)->getVar();

            $results[6]['metric'] = __('Avg Comments per Post', 'wp-slimstat');
            $results[6]['value']  = empty($results[1]['value']) ? 0 : number_format_i18n($results[5]['value'] / $results[1]['value']);

            $results[7]['metric']  = __('Avg Server Latency', 'wp-slimstat');
            $results[7]['value']   = number_format_i18n(Query::select('AVG(server_latency)')->from($slim_stats_table)->where('server_latency', '!=', 0)->getVar());
            $results[7]['tooltip'] = __('Latency is the amount of time it takes for the host server to receive and process a request for a page object. The amount of latency depends largely on how far away the user is from the server.', 'wp-slimstat');

            $results[1]['value'] = number_format_i18n($results[1]['value']);
            $results[5]['value'] = number_format_i18n($results[5]['value']);

            // Store values as transients for 30 minutes
            set_transient('slimstat_your_content', $results, 1800);
        }

        return $results;
    }

    // ---- Goals & Funnels Query Methods ---- //

    /**
     * Returns a prepared SQL WHERE fragment for a single goal/step condition.
     * Uses the existing get_single_where_clause() which returns an already-prepared string.
     *
     * @param array  $goal   Goal definition with dimension, operator, value keys.
     * @param string $alias  Table alias (e.g., 't1' or 'te').
     * @return string Prepared SQL WHERE fragment (e.g., "t1.resource = '/shop/'").
     */
    private static function build_goal_where($goal, $alias = '')
    {
        // Read keys defensively: legacy/malformed stored goals or funnel steps
        // may be missing a field, and the report render path must not emit
        // undefined-array-key notices. A missing dimension/operator yields no
        // clause (preserving the empty-where -> 0-results contract). (#6)
        $dimension = (string) ($goal['dimension'] ?? '');
        $operator  = (string) ($goal['operator'] ?? '');
        $value     = (string) ($goal['value'] ?? '');

        if ('' === $dimension || '' === $operator) {
            return '';
        }

        // Defense-in-depth: a value-bearing operator with an empty value makes
        // get_single_where_clause() return an unprepared fragment that still
        // contains a literal "%s" placeholder (it skips prepare() when the value
        // is empty). sanitize_goal() already rejects this at save time, but guard
        // the query layer too so such a clause can never reach $wpdb->query().
        // Only the valueless operators (is_empty / is_not_empty) may run without a value.
        if ('' === $value && !in_array($operator, self::$valueless_operators, true)) {
            return '';
        }

        // Event-based goals query the events table notes column
        if ($dimension === 'event_notes') {
            $dimension = 'notes';
            if (empty($alias)) {
                $alias = 'te';
            }
        }

        return self::get_single_where_clause($dimension, $operator, $value, $alias);
    }

    /**
     * Visitor identifier expression that handles NULL fingerprints:
     * COALESCE(fingerprint, 'v_'+visit_id, 'ip_'+ip). Used both to populate the
     * funnel temp tables (SELECT/INSERT) and, via count_unique_visitors(), to
     * count distinct goal visitors — so goals and funnels share one identity and
     * neither silently drops visitors that lack a fingerprint. The expression is
     * only ever used in SELECT output, never in a WHERE clause.
     */
    private static function visitor_id_expr($alias = '')
    {
        $prefix = !empty($alias) ? $alias . '.' : '';
        return sprintf(
            "COALESCE(%sfingerprint, CONCAT('v_', %svisit_id), CONCAT('ip_', %sip))",
            $prefix,
            $prefix,
            $prefix
        );
    }

    /**
     * Counts distinct visitors using the NULL-safe visitor identity
     * (COALESCE(fingerprint, visit_id, ip)) that funnels already use, so a
     * segment dominated by NULL-fingerprint rows — bots/crawlers,
     * consent-limited sessions, or rows recorded before the fingerprint feature
     * shipped — is no longer silently dropped (the symptom: "Country" goals
     * showing a correct Total but 0 Uniques). Goal uniques now agree with funnel
     * step-1 counts for the same rule.
     *
     * No "fingerprint IS NOT NULL" filter is needed because the COALESCE
     * expression is never NULL. Keeps the subquery-decomposition form (SELECT
     * COUNT(*) FROM (SELECT DISTINCT ...)) for the documented speedup over
     * COUNT(DISTINCT). (#3)
     *
     * @param string $from_clause  SQL FROM + JOIN.
     * @param string $where_clause SQL WHERE conditions (already prepared).
     * @param string $alias        Table alias the visitor columns live on.
     * @return int
     */
    private static function count_unique_visitors($from_clause, $where_clause, $alias = 't1')
    {
        return intval(wp_slimstat::$wpdb->get_var(sprintf(
            "SELECT COUNT(*) FROM (SELECT DISTINCT %s AS vid FROM %s WHERE %s) AS uv",
            self::visitor_id_expr($alias),
            $from_clause,
            $where_clause
        )));
    }

    /**
     * Get results for a single goal: total hits, unique visitors, conversion rate.
     *
     * @param array $goal Goal definition.
     * @return array ['total' => int, 'uniques' => int, 'cr' => float]
     */
    public static function get_goal_results($goal)
    {
        $table_stats  = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $table_events = $GLOBALS['wpdb']->prefix . 'slim_events';
        $is_event     = ($goal['dimension'] === 'event_notes');

        $goal_where = self::build_goal_where($goal, $is_event ? 'te' : 't1');
        if (empty($goal_where)) {
            return ['total' => 0, 'uniques' => 0, 'cr' => 0.0, 'total_visitors' => 0];
        }

        $filters_where = self::get_combined_where('', '*', true, 't1');
        $cache_ver     = get_option('slimstat_goals_cache_ver', '0');
        // NOTE: keyed per goal id, so it embeds the live range end (second precision)
        // and shares funnels' SSR/AJAX drift. Left as-is — a goal shows one number per
        // id, so it never produces the "two identical things disagree" symptom #1 fixed
        // for funnels. If a goal-number flicker is ever reported, route this through a
        // shared range-bucket helper (see funnel_cache_key).
        $cache_key     = 'slimstat_goal_' . $goal['id'] . '_' . md5($filters_where . $cache_ver);

        // Per-request memo keyed by the result-determining signature (criteria +
        // filters + cache version), NOT the goal id — so several goals with the
        // same criteria run the COUNT/unique queries once per request instead of
        // once each, and a re-render reuses the result. Removes the duplicate
        // COUNT(*)/unique queries Query Monitor reported. (#12)
        static $request_memo = [];
        $memo_key = md5($goal_where . '|' . $filters_where . '|' . $cache_ver);
        if (array_key_exists($memo_key, $request_memo)) {
            return $request_memo[$memo_key];
        }

        $result = get_transient($cache_key);

        if (false === $result) {
            $where_combined = $goal_where . ' AND ' . $filters_where;

            if ($is_event) {
                $from = sprintf('%s te INNER JOIN %s t1 ON te.id = t1.id', $table_events, $table_stats);
            } else {
                $from = sprintf('%s t1', $table_stats);
            }

            $total   = intval(wp_slimstat::$wpdb->get_var("SELECT COUNT(*) FROM $from WHERE $where_combined"));
            // NULL-safe distinct-visitor count (COALESCE id) so segments full of
            // NULL-fingerprint rows aren't reported as 0 uniques, and goal uniques
            // match funnel step-1 counts for the same rule. (#3)
            $uniques = self::count_unique_visitors($from, $where_combined);

            $total_visitors = self::get_total_unique_visitors();
            $cr = ($total_visitors > 0) ? round(($uniques / $total_visitors) * 100, 2) : 0.0;

            // total_visitors is the CR denominator — returned so the card can show
            // "N of M uniques" and make the percentage legible without re-querying. (#13)
            $result = ['total' => $total, 'uniques' => $uniques, 'cr' => $cr, 'total_visitors' => $total_visitors];
            set_transient($cache_key, $result, 5 * MINUTE_IN_SECONDS);
        }

        $request_memo[$memo_key] = $result;
        return $result;
    }

    /**
     * Get total unique visitors in the current date range.
     * Cached as transient (15 min TTL) + in-request static var.
     *
     * @return int
     */
    private static function get_total_unique_visitors()
    {
        static $request_cache = null;
        if ($request_cache !== null) {
            return $request_cache;
        }

        $table_stats = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $date_where  = self::get_combined_where('', '*', true, 't1');
        // Version-key like the goal/funnel transients so a CRUD cache bump (which
        // also runs the GC in clear_goals_cache()) rotates this denominator too.
        $cache_ver   = get_option('slimstat_goals_cache_ver', '0');
        // NOTE: like the goal key, this embeds the live range end and shares funnels'
        // SSR/AJAX drift; left funnel-scoped on purpose (#1). Route through a shared
        // range-bucket helper if this denominator ever needs the same fix.
        $cache_key   = 'slimstat_uv_' . md5($date_where . $cache_ver);
        $cached      = get_transient($cache_key);

        if (false !== $cached) {
            $request_cache = intval($cached);
            return $request_cache;
        }

        // Same NULL-safe visitor identity as the goal numerator (count_unique_visitors)
        // so the conversion-rate denominator and numerator stay consistent. (#3)
        $request_cache = self::count_unique_visitors(
            sprintf('%s t1', $table_stats),
            $date_where
        );

        set_transient($cache_key, $request_cache, 15 * MINUTE_IN_SECONDS);
        return $request_cache;
    }

    /**
     * Get raw goal results as flat array (for Export CSV / Email Reports).
     * Accepts standard $_args array like get_top().
     *
     * @param array $_args Callback args from report definition.
     * @return array Array of associative arrays with goal_name, uniques, total, cr keys.
     */
    public static function get_goals_raw($_args = [])
    {
        $goals   = get_option('slimstat_goals', []);
        $results = [];

        foreach ($goals as $goal) {
            if (empty($goal['active']) || empty($goal['name']) || empty($goal['dimension'])) {
                continue;
            }
            $data      = self::get_goal_results($goal);
            $results[] = [
                'goal_name' => $goal['name'],
                'uniques'   => $data['uniques'],
                'total'     => $data['total'],
                'cr'        => $data['cr'] . '%',
            ];
        }

        return $results;
    }

    /**
     * Reduce a funnel's steps to the fields that actually determine the query
     * result — dimension, operator, value, in order — so two funnels with the
     * same rules (ignoring id, name and per-step labels) hash to the same cache
     * signature and therefore return identical numbers. Order is significant:
     * A->B->C is a different journey than C->B->A, so steps are NOT sorted. The
     * fields mirror exactly what build_goal_where() reads, so a shared signature
     * guarantees a shared WHERE clause — never a wrong-result collision. (#19)
     *
     * @param array $steps
     * @return array<int,array{dimension:string,operator:string,value:string}>
     */
    private static function normalize_funnel_steps($steps)
    {
        return array_map(
            static fn($step) => [
                'dimension' => (string) ($step['dimension'] ?? ''),
                'operator'  => (string) ($step['operator'] ?? ''),
                'value'     => (string) ($step['value'] ?? ''),
            ],
            array_values((array) $steps)
        );
    }

    /**
     * Signature of the active global column filters, for the funnel cache key.
     *
     * Derived from the NORMALIZED filter array ([col => [operator, value]]), NOT
     * from get_combined_where()'s SQL: that SQL is run through $wpdb->prepare(),
     * whose per-request placeholder salt would make the signature unstable and
     * re-split a server-rendered funnel from its AJAX-loaded twin (the very #1
     * symptom). Serializing the normalized array is request-stable by construction
     * and mirrors normalize_funnel_steps(). An empty/absent filter set hashes to a
     * fixed baseline so an unfiltered render is a stable key. (#22)
     *
     * @return string
     */
    private static function funnel_filters_signature()
    {
        return md5(serialize(self::$filters_normalized['columns'] ?? []));
    }

    /**
     * Build the funnel-result cache key: normalized step signature + the date
     * window bucketed to the hour + the active column-filter signature + cache
     * version. Bucketing the end (which is "now" for a live range, set with second
     * precision) to the hour absorbs the sub-hour drift between a server-rendered
     * funnel and its AJAX-loaded twin, so two identical funnels share one transient
     * and return identical numbers. (#1)
     * (A render pair straddling an hour boundary can still miss — a rare, ~few-second
     * window that self-heals on the next render; acceptable given the 5-min TTL.)
     *
     * $filters_sig folds the active global filters into the key so toggling a report
     * filter (e.g. "browser equals X") yields a new key instead of serving the stale
     * unfiltered transient — the funnel equivalent of how Goals key on the filter
     * WHERE. The date is intentionally NOT in $filters_sig (it lives in $range,
     * hour-bucketed); only the COLUMN filters are, so the SSR/AJAX drift fix stands.
     * The sig folds into the trailing md5 so the "slimstat_funnel_" prefix that
     * clear_goals_cache() sweeps with a LIKE is preserved. (#22)
     *
     * @param array      $steps
     * @param int        $range_start utime range start (seconds)
     * @param int        $range_end   utime range end (seconds)
     * @param string     $filters_sig column-filter signature (funnel_filters_signature())
     * @param int|string $cache_ver
     * @return string
     */
    private static function funnel_cache_key($steps, $range_start, $range_end, $filters_sig, $cache_ver)
    {
        // 3600 = bucket the window end to the hour (mirrors the hour-bucketing in
        // wp_slimstat_admin::build_filter_options_cache_key()).
        $range = (int) $range_start . ':' . (int) floor((int) $range_end / 3600);

        return 'slimstat_funnel_' . md5(serialize(self::normalize_funnel_steps($steps)))
            . '_' . md5($range . '|' . $filters_sig . '|' . $cache_ver);
    }

    /**
     * Get funnel results: visitors at each step with drop-off.
     * Uses iterative PHP approach for MySQL 5.6 compatibility.
     *
     * Cached for 5 minutes via a version-keyed transient, invalidated by
     * wp_slimstat_admin::clear_goals_cache() on any goal/funnel CRUD.
     *
     * @param array $funnel Funnel definition with steps array.
     * @return array Array of step results: name, visitors, pct, dropoff, unreachable.
     */
    public static function get_funnel_results($funnel)
    {
        if (empty($funnel['steps']) || count($funnel['steps']) < 2) {
            return [];
        }

        $cache_ver = get_option('slimstat_goals_cache_ver', '0');
        // Deterministic key: normalized step signature + hour-bucketed date window +
        // active column-filter signature (NOT the funnel id, NOT the raw $date_where
        // SQL) — so two identical funnels, and a server-rendered funnel + its AJAX
        // twin, share one transient, while a change to the global report filters
        // rotates the key instead of serving a stale unfiltered result. See
        // funnel_cache_key() / funnel_filters_signature(). (#1, #22, builds on #19)
        $cache_key = self::funnel_cache_key(
            $funnel['steps'],
            (int) (self::$filters_normalized['utime']['start'] ?? 0),
            (int) (self::$filters_normalized['utime']['end'] ?? 0),
            self::funnel_filters_signature(),
            $cache_ver
        );

        // Per-request memo: a funnel rendered (or re-rendered) twice in one
        // request reuses its result instead of rebuilding temp tables again. (#12)
        static $request_memo = [];
        if (array_key_exists($cache_key, $request_memo)) {
            return $request_memo[$cache_key];
        }

        $cached = get_transient($cache_key);
        if (false !== $cached) {
            $request_memo[$cache_key] = $cached;
            return $cached;
        }

        // Built only after the cache miss — it drives the step queries below, not the
        // cache key, so a memo/transient hit skips this get_combined_where() work.
        $date_where = self::get_combined_where('', '*', true, 't1');

        $table_stats  = $GLOBALS['wpdb']->prefix . 'slim_stats';
        $table_events = $GLOBALS['wpdb']->prefix . 'slim_events';
        $visitor_id   = self::visitor_id_expr('t1');

        // Two-table swap pattern: READ holds previous step's visitors,
        // WRITE receives current step's results. After each step, WRITE
        // is renamed to READ. This avoids the self-referencing temp table
        // bug where DROP + CREATE AS SELECT ... IN (SELECT vid FROM same_table) fails.
        // Fixed names are safe: TEMPORARY tables are session-scoped, a connection
        // serves one request at a time, and the preflight DROP IF EXISTS clears
        // any stale leftovers — so no cross-call/connection collision can occur.
        $temp_read  = $GLOBALS['wpdb']->prefix . 'slim_funnel_read';
        $temp_write = $GLOBALS['wpdb']->prefix . 'slim_funnel_write';

        $results     = [];
        $step1_count = 0;
        $use_temp    = false;
        $preflight   = false;
        $had_error   = false;

        // Each temp table row carries (vid, t) — the visitor identifier and the
        // MIN(dt) at which they qualified for the preceding step. The JOIN on
        // step N+ enforces `new_row.dt >= r.t` so out-of-order matches (visitor
        // hit step N before step N-1) don't count as converted. We use `>=`
        // (not `>`) because dt has one-second granularity: two genuinely ordered
        // steps that land in the same second (fast SPA navigation, a pageview
        // immediately followed by an event row) must still count. Distinct step
        // rules keep the same physical row from satisfying two steps at once.
        foreach ($funnel['steps'] as $step_index => $step) {
            $is_event   = ($step['dimension'] === 'event_notes');
            $step_where = self::build_goal_where($step, $is_event ? 'te' : 't1');

            if (empty($step_where)) {
                $results[] = ['name' => $step['name'], 'visitors' => 0, 'pct' => 0, 'dropoff' => 0, 'unreachable' => false];
                $use_temp = false;
                wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_read");
                continue;
            }

            if ($step_index > 0 && !$use_temp) {
                // Previous step already returned zero — every downstream step
                // is unreachable, no point hitting SQL.
                $results[] = ['name' => $step['name'], 'visitors' => 0, 'pct' => 0, 'dropoff' => 0, 'unreachable' => false];
                continue;
            }

            // Event steps use te.dt (the event's own timestamp) as the ordering
            // time. Pageview steps use t1.dt.
            $dt_expr = $is_event ? 'te.dt' : 't1.dt';

            // Base FROM clause — joined for event steps, plain for pageview steps.
            $from_sql = $is_event
                ? sprintf('%s te INNER JOIN %s t1 ON te.id = t1.id', $table_events, $table_stats)
                : sprintf('%s t1', $table_stats);

            if ($step_index === 0) {
                // Step 1: per-visitor MIN(dt) within the date window.
                $select_sql = sprintf(
                    "SELECT %s AS vid, MIN(%s) AS t FROM %s WHERE %s AND %s GROUP BY vid",
                    $visitor_id, $dt_expr, $from_sql, $step_where, $date_where
                );
            } else {
                // Step N>1: JOIN temp_read and require the new row's dt at or
                // after the stored timestamp for the same visitor (see ordering
                // note above for why `>=` rather than `>`).
                $select_sql = sprintf(
                    "SELECT %s AS vid, MIN(%s) AS t FROM %s INNER JOIN %s r ON r.vid = %s WHERE %s AND %s AND %s >= r.t GROUP BY vid",
                    $visitor_id, $dt_expr, $from_sql, $temp_read, $visitor_id, $step_where, $date_where, $dt_expr
                );
            }

            // Lazy preflight: only drop stale temps on the first step that actually runs SQL.
            if (!$preflight) {
                wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_read");
                wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_write");
                $preflight = true;
            }

            // Create the per-step temp table once, then count from it — avoids
            // running the grouped subquery twice for the same step.
            wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_write");
            $created = wp_slimstat::$wpdb->query("CREATE TEMPORARY TABLE $temp_write (vid VARCHAR(64) NOT NULL, t INT UNSIGNED NOT NULL, KEY(vid)) AS $select_sql");

            // If CREATE … AS SELECT failed (malformed step rule, STRICT-mode
            // truncation, missing CREATE TEMPORARY privilege, deadlock), the
            // write table does not exist. Proceeding would COUNT a missing table
            // (→ 0), then DROP the previous valid READ and RENAME a missing WRITE,
            // silently zeroing this and every downstream step and presenting a
            // corrupt funnel as real data. Bail with the steps gathered so far,
            // flag this step as errored, and don't cache the partial result so a
            // transient failure self-heals on the next request.
            if (false === $created) {
                if ('on' == wp_slimstat::$settings['show_sql_debug'] && !empty(wp_slimstat::$wpdb->last_error)) {
                    self::$debug_message .= sprintf("<p class='debug'>Funnel step query failed: %s</p>", esc_html(wp_slimstat::$wpdb->last_error));
                }
                wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_read");
                wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_write");
                $results[] = ['name' => $step['name'], 'visitors' => 0, 'pct' => 0, 'dropoff' => 0, 'unreachable' => false];
                $had_error = true;
                break;
            }

            $visitor_count = intval(wp_slimstat::$wpdb->get_var("SELECT COUNT(*) FROM $temp_write"));

            if ($step_index === 0) {
                $step1_count = $visitor_count;
            }

            // Swap: drop old READ, rename WRITE → READ for next iteration.
            wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_read");
            wp_slimstat::$wpdb->query("ALTER TABLE $temp_write RENAME TO $temp_read");
            $use_temp = ($visitor_count > 0);

            $prev_count = ($step_index > 0 && !empty($results[$step_index - 1])) ? $results[$step_index - 1]['visitors'] : $visitor_count;
            $dropoff    = $prev_count - $visitor_count;

            // A step is "unreachable" when the previous step had visitors but none
            // carried through — usually a rule typo or an impossible ordering.
            $unreachable = ($step_index > 0 && $visitor_count === 0 && $prev_count > 0);

            $results[] = [
                'name'        => $step['name'],
                'visitors'    => $visitor_count,
                'pct'         => ($step1_count > 0) ? round(($visitor_count / $step1_count) * 100, 1) : 0,
                'dropoff'     => max(0, $dropoff),
                'unreachable' => $unreachable,
            ];
        }

        if ($preflight) {
            wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_read");
            wp_slimstat::$wpdb->query("DROP TEMPORARY TABLE IF EXISTS $temp_write");
        }

        // Don't cache a funnel whose query errored — let it recompute next time
        // in case the failure was transient (deadlock) or the rule was fixed.
        if (!$had_error) {
            set_transient($cache_key, $results, 5 * MINUTE_IN_SECONDS);
        }

        // Memo for the rest of THIS request even on error (the transient is
        // skipped above, so the next request still recomputes and self-heals).
        $request_memo[$cache_key] = $results;
        return $results;
    }

    /**
     * Get raw funnel results as flat array (for Export CSV / Email Reports).
     *
     * @param array $_args Callback args from report definition.
     * @return array Flat rows with funnel_name, step_name, step_order, visitors, pct, dropoff.
     */
    public static function get_funnels_raw($_args = [])
    {
        $funnels = get_option('slimstat_funnels', []);
        $results = [];

        foreach ($funnels as $funnel) {
            if (empty($funnel['name']) || empty($funnel['steps'])) {
                continue;
            }
            $step_results = self::get_funnel_results($funnel);
            foreach ($step_results as $i => $step) {
                $results[] = [
                    'funnel_name' => $funnel['name'],
                    'step_name'   => $step['name'],
                    'step_order'  => $i + 1,
                    'visitors'    => $step['visitors'],
                    'pct'         => $step['pct'] . '%',
                    'dropoff'     => $step['dropoff'],
                ];
            }
        }

        return $results;
    }
}