<?php /*

 Composr
 Copyright (c) Christopher Graham, 2004-2024

 See docs/LICENSE.md for full licensing information.


 NOTE TO PROGRAMMERS:
   Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
   **** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****

*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  Christopher Graham
 * @package    stats
 */

/**
 * Hook class.
 */
class Hook_admin_stats_views extends CMSStatsProvider
{
    protected const USER_AGENT_TYPE__UNKNOWN = 0;
    protected const USER_AGENT_TYPE__DESKTOP = 1;
    protected const USER_AGENT_TYPE__MOBILE = 2;
    protected const USER_AGENT_TYPE__BOT = 3;

    protected const REFERRER_TYPE__DIRECT = 0;
    protected const REFERRER_TYPE__UNKNOWN = 1;
    protected const REFERRER_TYPE__INTERNAL = 2;
    protected const REFERRER_TYPE__EXTERNAL_SEARCH_ENGINE = 3;
    protected const REFERRER_TYPE__EXTERNAL_SOCIAL_MEDIA = 4;
    protected const REFERRER_TYPE__EXTERNAL_MISC = 5;

    protected $speed_brackets = [];
    protected $session_duration_brackets = [];

    /**
     * Constructor.
     */
    public function __construct()
    {
        $this->speed_brackets = [ // in ms
            '<100',
            '100-249',
            '250-499',
            '500-999',
            '1000-2499',
            '2500-4999',
            '5000-9999',
            '10000+',
        ];

        $this->session_duration_brackets = [ // in s
            '<10',
            '10-29',
            '30-59',
            '60-119', // 1 minute+
            '120-299', // 2 minutes+
            '300-899', // 5 minutes+
            '900-1799', // 15 minutes+
            '1800-3599', // 30 minutes+
            '3600-7199', // 1 hour+
            '7200-17999', // 2 hours+
            '18000+', // 5 hours+
        ];
    }

    /**
     * Find metadata about stats categories that are defined by this stats hook.
     *
     * @return ?array Map of metadata (null: hook is disabled)
     */
    public function category_info() : ?array
    {
        return [
            'content_growth' => [
                'label_lang_string' => 'STATS_CATEGORY_content_growth',
                'icon' => 'menu/adminzone/audit/statistics/submits',
            ],
            'economic_activity' => [
                'label_lang_string' => 'STATS_CATEGORY_economic_activity',
                'icon' => 'spare/sponsored',
            ],
            'inter_member_engagement' => [
                'label_lang_string' => 'STATS_CATEGORY_inter_member_engagement',
                'icon' => 'spare/social',
            ],
            'feedback_and_engagement' => [
                'label_lang_string' => 'STATS_CATEGORY_feedback_and_engagement',
                'icon' => 'spare/feedback_features',
            ],
            'conversions' => [
                'label_lang_string' => 'STATS_CATEGORY_conversions',
                'icon' => 'spare/activity',
            ],
            'audience_demographics' => [
                'label_lang_string' => 'STATS_CATEGORY_audience_demographics',
                'icon' => 'menu/adminzone/audit/statistics/demographics',
            ],
            'audience_technical' => [
                'label_lang_string' => 'STATS_CATEGORY_audience_technical',
                'icon' => 'spare/computer',
            ],
            'server_performance' => [
                'label_lang_string' => 'STATS_CATEGORY_server_performance',
                'icon' => 'menu/adminzone/audit/statistics/load_times',
            ],
            'search_traffic' => [
                'label_lang_string' => 'STATS_CATEGORY_search_traffic',
                'icon' => 'menu/adminzone/audit/statistics/search',
            ],
            'referrers_and_referrals' => [
                'label_lang_string' => 'STATS_CATEGORY_referrers_and_referrals',
                'icon' => 'menu/adminzone/audit/statistics/top_referrers',
            ],
            'hits' => [
                'label_lang_string' => 'STATS_CATEGORY_hits',
                'icon' => 'menu/adminzone/audit/statistics/page_views',
            ],
            'session_behaviours' => [
                'label_lang_string' => 'STATS_CATEGORY_session_behaviours',
                'icon' => 'menu/adminzone/audit/statistics/users_online',
            ],
        ];
    }

    /**
     * Find metadata about stats graphs that are provided by this stats hook.
     *
     * @param  boolean $for_kpi Whether this is for setting up a KPI
     * @return ?array Map of metadata (null: hook is disabled)
     */
    public function info(bool $for_kpi = false) : ?array
    {
        require_lang('zones');
        require_code('locations');

        $ret = [
            'total_views' => [
                'label' => do_lang_tempcode('VIEWS'),
                'category' => 'hits',
                'filters' => [
                    'total_views__month_range' => new CMSStatsDateMonthRangeFilter('total_views__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'total_views__page_link' => new CMSStatsTextFilter('total_views__page_link', do_lang_tempcode('PAGE_LINK')),
                    'total_views__country' => has_geolocation_data() ? new CMSStatsListFilter('total_views__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => new CMSStatsDatePivot('total_views__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
            'total_unique_views' => [
                'label' => do_lang_tempcode('UNIQUE_VIEWS'),
                'category' => 'hits',
                'filters' => [
                    'total_unique_views__month_range' => new CMSStatsDateMonthRangeFilter('total_unique_views__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'total_unique_views__country' => has_geolocation_data() ? new CMSStatsListFilter('total_unique_views__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => new CMSStatsDatePivot('total_unique_views__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
            'popular_pages' => [
                'label' => do_lang_tempcode('POPULAR_PAGES'),
                'category' => 'hits',
                'filters' => [
                    'popular_pages__month_range' => new CMSStatsDateMonthRangeFilter('popular_pages__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'popular_pages__country' => has_geolocation_data() ? new CMSStatsListFilter('popular_pages__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'operating_systems' => [
                'label' => do_lang_tempcode('WITH_VERSION_NUMBERS', do_lang_tempcode('OPERATING_SYSTEMS')),
                'category' => 'audience_technical',
                'filters' => [
                    'operating_systems__month_range' => new CMSStatsDateMonthRangeFilter('operating_systems__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'operating_systems__country' => has_geolocation_data() ? new CMSStatsListFilter('operating_systems__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'operating_systems__stripped' => [
                'label' => do_lang_tempcode('WITHOUT_VERSION_NUMBERS', do_lang_tempcode('OPERATING_SYSTEMS')),
                'category' => 'audience_technical',
                'filters' => [
                    'operating_systems__stripped__month_range' => new CMSStatsDateMonthRangeFilter('operating_systems__stripped__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'operating_systems__stripped__country' => has_geolocation_data() ? new CMSStatsListFilter('operating_systems__stripped__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'web_browsers' => [
                'label' => do_lang_tempcode('WITH_VERSION_NUMBERS', do_lang_tempcode('WEB_BROWSERS')),
                'category' => 'audience_technical',
                'filters' => [
                    'web_browsers__month_range' => new CMSStatsDateMonthRangeFilter('web_browsers__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'web_browsers__country' => has_geolocation_data() ? new CMSStatsListFilter('web_browsers__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'web_browsers__stripped' => [
                'label' => do_lang_tempcode('WITHOUT_VERSION_NUMBERS', do_lang_tempcode('WEB_BROWSERS')),
                'category' => 'audience_technical',
                'filters' => [
                    'web_browsers__stripped__month_range' => new CMSStatsDateMonthRangeFilter('web_browsers__stripped__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'web_browsers__stripped__country' => has_geolocation_data() ? new CMSStatsListFilter('web_browsers__stripped__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'user_agent_types' => [
                'label' => do_lang_tempcode('USER_AGENT_TYPES'),
                'category' => 'audience_technical',
                'filters' => [
                    'user_agent_types__month_range' => new CMSStatsDateMonthRangeFilter('user_agent_types__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'user_agent_types__country' => has_geolocation_data() ? new CMSStatsListFilter('user_agent_types__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'referrer_urls' => [
                'label' => do_lang_tempcode('REFERRER_URLS'),
                'category' => 'referrers_and_referrals',
                'filters' => [
                    'referrer_urls__month_range' => new CMSStatsDateMonthRangeFilter('referrer_urls__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'referrer_urls__country' => has_geolocation_data() ? new CMSStatsListFilter('referrer_urls__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'referrer_domains' => [
                'label' => do_lang_tempcode('REFERRER_DOMAINS'),
                'category' => 'referrers_and_referrals',
                'filters' => [
                    'referrer_domains__month_range' => new CMSStatsDateMonthRangeFilter('referrer_domains__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'referrer_domains__country' => has_geolocation_data() ? new CMSStatsListFilter('referrer_domains__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'total_referrals' => [
                'label' => do_lang_tempcode('TOTAL_REFERRALS'),
                'category' => 'referrers_and_referrals',
                'filters' => [
                    'total_referrals__month_range' => new CMSStatsDateMonthRangeFilter('total_referrals__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'total_referrals__country' => has_geolocation_data() ? new CMSStatsListFilter('total_referrals__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => new CMSStatsDatePivot('total_referrals__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
            'referrer_type' => [
                'label' => do_lang_tempcode('REFERRER_TYPES'),
                'category' => 'referrers_and_referrals',
                'filters' => [
                    'referrer_type__month_range' => new CMSStatsDateMonthRangeFilter('referrer_type__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'referrer_type__country' => has_geolocation_data() ? new CMSStatsListFilter('referrer_type__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'load_times_spread' => [
                'label' => do_lang_tempcode('LOAD_TIMES'),
                'category' => 'server_performance',
                'filters' => [
                    'load_times_spread__month_range' => new CMSStatsDateMonthRangeFilter('load_times_spread__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                ],
                'pivot' => null,
            ],
            'page_average_speeds' => [
                'label' => do_lang_tempcode('PAGE_AVERAGE_SPEEDS'),
                'category' => 'server_performance',
                'filters' => [
                    'page_average_speeds__month_range' => new CMSStatsDateMonthRangeFilter('page_average_speeds__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                ],
                'pivot' => null,
                'support_kpis' => self::KPI_LOW_IS_GOOD,
            ],
            'average_page_speed' => [
                'label' => do_lang_tempcode('AVERAGE_PAGE_SPEED'),
                'category' => 'server_performance',
                'filters' => [
                    'average_page_speed__month_range' => new CMSStatsDateMonthRangeFilter('average_page_speed__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'average_page_speed__page_link' => new CMSStatsTextFilter('average_page_speed__page_link', do_lang_tempcode('PAGE_LINK')),
                ],
                'pivot' => new CMSStatsDatePivot('average_page_speed__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_LOW_IS_GOOD,
            ],
            'requested_languages' => [
                'label' => do_lang_tempcode('REQUESTED_LANGUAGES'),
                'category' => 'audience_demographics',
                'filters' => [
                    'requested_languages__month_range' => new CMSStatsDateMonthRangeFilter('requested_languages__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                ],
                'pivot' => null,
            ],
            'session_bounce_rates' => [
                'label' => do_lang_tempcode('SESSION_BOUNCE_RATES'),
                'category' => 'session_behaviours',
                'filters' => [
                    'session_bounce_rates__month_range' => new CMSStatsDateMonthRangeFilter('session_bounce_rates__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'session_bounce_rates__page_link' => new CMSStatsTextFilter('session_bounce_rates__page_link', do_lang_tempcode('PAGE_LINK')),
                    'session_bounce_rates__country' => has_geolocation_data() ? new CMSStatsListFilter('session_bounce_rates__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => new CMSStatsDatePivot('session_bounce_rates__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_LOW_IS_GOOD,
            ],
            'session_entry_pages' => [
                'label' => do_lang_tempcode('SESSION_ENTRY_PAGES'),
                'category' => 'session_behaviours',
                'filters' => [
                    'session_entry_pages__month_range' => new CMSStatsDateMonthRangeFilter('session_entry_pages__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'session_entry_pages__country' => has_geolocation_data() ? new CMSStatsListFilter('session_entry_pages__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'session_exit_pages' => [
                'label' => do_lang_tempcode('SESSION_EXIT_PAGES'),
                'category' => 'session_behaviours',
                'filters' => [
                    'session_exit_pages__month_range' => new CMSStatsDateMonthRangeFilter('session_exit_pages__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'session_exit_pages__country' => has_geolocation_data() ? new CMSStatsListFilter('session_exit_pages__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'session_durations' => [
                'label' => do_lang_tempcode('SESSION_DURATIONS'),
                'category' => 'session_behaviours',
                'filters' => [
                    'session_durations__month_range' => new CMSStatsDateMonthRangeFilter('session_durations__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'session_durations__country' => has_geolocation_data() ? new CMSStatsListFilter('session_durations__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'average_session_duration' => [
                'label' => do_lang_tempcode('AVERAGE_SESSION_DURATION'),
                'category' => 'session_behaviours',
                'filters' => [
                    'average_session_duration__month_range' => new CMSStatsDateMonthRangeFilter('average_session_duration__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'average_session_duration__country' => has_geolocation_data() ? new CMSStatsListFilter('average_session_duration__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => new CMSStatsDatePivot('average_session_duration__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
            'session_total_views' => [
                'label' => do_lang_tempcode('SESSION_TOTAL_VIEWS'),
                'category' => 'session_behaviours',
                'filters' => [
                    'session_total_views__month_range' => new CMSStatsDateMonthRangeFilter('session_total_views__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'session_total_views__country' => has_geolocation_data() ? new CMSStatsListFilter('session_total_views__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => null,
            ],
            'average_session_total_views' => [
                'label' => do_lang_tempcode('AVERAGE_SESSION_TOTAL_VIEWS'),
                'category' => 'session_behaviours',
                'filters' => [
                    'average_session_total_views__month_range' => new CMSStatsDateMonthRangeFilter('average_session_total_views__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'average_session_total_views__country' => has_geolocation_data() ? new CMSStatsListFilter('average_session_total_views__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => new CMSStatsDatePivot('average_session_total_views__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
        ];

        if (has_geolocation_data()) {
            $ret['countries'] = [
                'label' => do_lang_tempcode('VISITOR_COUNTRIES'),
                'category' => 'audience_demographics',
                'filters' => [
                    'countries__month_range' => new CMSStatsDateMonthRangeFilter('countries__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                ],
                'pivot' => null,
            ];
        }

        return $ret;
    }

    /**
     * Preprocess raw data in the database into something we can efficiently draw graphs/conclusions from.
     *
     * @param  TIME $start_time Start timestamp
     * @param  TIME $end_time End timestamp
     * @param  array $data_buckets Map of data buckets; a map of bucket name to nested maps with the following maps in sequence: 'month', 'pivot', 'value' (then further map data) ; extended and returned by reference
     */
    public function preprocess_raw_data(int $start_time, int $end_time, array &$data_buckets)
    {
        cms_profile_start_for('Hook_admin_stats_views->preprocess_raw_data');

        $old = cms_extend_time_limit(TIME_LIMIT_EXTEND__SLUGGISH);

        require_code('temporal');
        require_code('locations');

        $server_timezone = get_server_timezone();

        $max = 1000;
        $start = 0;

        $date_pivots = $this->get_date_pivots();

        $guest_id = $GLOBALS['FORUM_DRIVER']->get_guest_id();

        require_code('textfiles');
        $all_search_engine_domains = explode("\n", read_text_file('search_engine_domains'));
        $all_social_media_domains = explode("\n", read_text_file('social_media_domains'));

        $found_sessions = [];

        // Main processing loop...

        $query = 'SELECT * FROM ' . get_table_prefix() . 'stats WHERE ';
        $query .= 'date_and_time>=' . strval($start_time) . ' AND ';
        $query .= 'date_and_time<=' . strval($end_time);
        $query .= ' ORDER BY date_and_time';
        cms_profile_start_for('Hook_admin_stats_views->preprocess_raw_data (main processing loop)');
        do {
            cms_profile_start_for('Hook_admin_stats_views->preprocess_raw_data (group ' . integer_format($start) . ')');
            $rows = $GLOBALS['SITE_DB']->query($query, $max, $start);
            foreach ($rows as $row) {
                $timestamp = $row['date_and_time'];
                $timestamp = tz_time($timestamp, $server_timezone);

                $month = to_epoch_interval_index($timestamp, 'months');

                if ($row['member_id'] == $guest_id) {
                    $unique_identifier = $row['ip'];
                } else {
                    $unique_identifier = strval($row['member_id']);
                }

                $ip_address = $row['ip'];
                $country = geolocate_ip($ip_address);
                if ($country === null) {
                    $country = '';
                }

                list($zone, $attributes) = page_link_decode($row['page_link']);
                $page = isset($attributes['page']) ? $attributes['page'] : DEFAULT_ZONE_PAGE_NAME;
                $page_link = $zone . ':' . $page;

                $os = $this->remove_minor_versioning($row['operating_system']);
                if (stripos($os, 'Android') !== false) {
                    $os_stripped = 'Android';
                } elseif ((stripos($os, 'iPhone') !== false) || (stripos($os, 'iPad') !== false)) {
                    $os_stripped = 'iOS';
                } elseif (stripos($os, 'Windows') !== false) {
                    $os_stripped = 'Windows';
                } elseif (stripos($os, 'Mac OS') !== false) {
                    $os_stripped = 'Mac OS';
                } elseif (stripos($os, 'Linux') !== false) {
                    $os_stripped = 'Linux';
                } else {
                    $os_stripped = trim(preg_replace('#\s+#', ' ', preg_replace('#[\d\.]*#', '', $row['operating_system'])));
                }

                $web_browser = $this->remove_minor_versioning($row['browser']);
                if ((stripos($web_browser, 'Edge') !== false) || (stripos($web_browser, 'Edg/') !== false)) {
                    $web_browser_stripped = 'Microsoft Edge';
                } elseif (stripos($web_browser, 'Safari') !== false) {
                    $web_browser_stripped = 'Apple Safari';
                } elseif (stripos($web_browser, 'MSIE') !== false) {
                    $web_browser_stripped = 'Microsoft Internet Explorer';
                } elseif (stripos($web_browser, 'Chrome') !== false) {
                    $web_browser_stripped = 'Google Chrome';
                } elseif (stripos($web_browser, 'Firefox') !== false) {
                    $web_browser_stripped = 'Mozilla Firefox';
                } else {
                    $web_browser_stripped = trim(preg_replace('#\s+#', ' ', preg_replace('#[\d\.]*#', '', $row['browser'])));
                }

                $is_real_human = false;
                if (get_bot_type($web_browser . ' ' . $os) !== null) {
                    $user_agent_type = self::USER_AGENT_TYPE__BOT;
                } elseif (is_mobile($web_browser . ' ' . $os)) {
                    $user_agent_type = self::USER_AGENT_TYPE__MOBILE;
                    $is_real_human = true;
                } elseif (preg_match('#Mozilla#', $web_browser) != 0) {
                    $user_agent_type = self::USER_AGENT_TYPE__DESKTOP;
                    $is_real_human = true;
                } else {
                    $user_agent_type = self::USER_AGENT_TYPE__UNKNOWN;
                }

                if ($is_real_human) {
                    $found_sessions[$row['session_id']] = true;
                }

                $referrer_url = preg_replace('#\?.*$#', '', $row['referer_url']);
                $referrer_domain = @strval(cms_parse_url_safe($referrer_url, PHP_URL_HOST));

                if ($referrer_url == '') {
                    $referrer_type = self::REFERRER_TYPE__DIRECT;
                } elseif ($referrer_domain == '') {
                    $referrer_type = self::REFERRER_TYPE__UNKNOWN;
                } elseif (is_our_server($referrer_domain)) {
                    $referrer_type = self::REFERRER_TYPE__INTERNAL;
                } elseif ($this->domain_list_match($all_search_engine_domains, $referrer_domain)) {
                    $referrer_type = self::REFERRER_TYPE__EXTERNAL_SEARCH_ENGINE;
                } elseif ($this->domain_list_match($all_social_media_domains, $referrer_domain)) {
                    $referrer_type = self::REFERRER_TYPE__EXTERNAL_SOCIAL_MEDIA;
                } else {
                    $referrer_type = self::REFERRER_TYPE__EXTERNAL_MISC;
                }

                // Hits...

                foreach (array_keys($date_pivots) as $pivot) {
                    $pivot_value = $this->calculate_date_pivot_value($pivot, $timestamp);

                    if ($is_real_human) {
                        if (!isset($data_buckets['total_views'][$month][$pivot][$pivot_value][$country][$page_link])) {
                            $data_buckets['total_views'][$month][$pivot][$pivot_value][$country][$page_link] = 0;
                        }
                        $data_buckets['total_views'][$month][$pivot][$pivot_value][$country][$page_link]++;

                        $data_buckets['total_unique_views'][$month][$pivot][$pivot_value][$country][$unique_identifier] = true;

                        if ($referrer_type != self::REFERRER_TYPE__INTERNAL) {
                            if (!isset($data_buckets['total_referrals'][$month][$pivot][$pivot_value][$country])) {
                                $data_buckets['total_referrals'][$month][$pivot][$pivot_value][$country] = 0;
                            }
                            $data_buckets['total_referrals'][$month][$pivot][$pivot_value][$country]++;
                        }
                    }
                }

                if ($is_real_human) {
                    if (!isset($data_buckets['popular_pages'][$month][''][$country][$page_link])) {
                        $data_buckets['popular_pages'][$month][''][$country][$page_link] = 0;
                    }
                    $data_buckets['popular_pages'][$month][''][$country][$page_link]++;
                }

                // User agents...

                if ($os != '') {
                    if (!isset($data_buckets['operating_systems'][$month][''][$country][$os])) {
                        $data_buckets['operating_systems'][$month][''][$country][$os] = 0;
                    }
                    $data_buckets['operating_systems'][$month][''][$country][$os]++;
                }
                if ($os_stripped != '') {
                    if (!isset($data_buckets['operating_systems__stripped'][$month][''][$country][$os_stripped])) {
                        $data_buckets['operating_systems__stripped'][$month][''][$country][$os_stripped] = 0;
                    }
                    $data_buckets['operating_systems__stripped'][$month][''][$country][$os_stripped]++;
                }

                if ($web_browser != brand_name()) {
                    if (!isset($data_buckets['web_browsers'][$month][''][$country][$web_browser])) {
                        $data_buckets['web_browsers'][$month][''][$country][$web_browser] = 0;
                    }
                    $data_buckets['web_browsers'][$month][''][$country][$web_browser]++;
                    if (!isset($data_buckets['web_browsers__stripped'][$month][''][$country][$web_browser_stripped])) {
                        $data_buckets['web_browsers__stripped'][$month][''][$country][$web_browser_stripped] = 0;
                    }
                    $data_buckets['web_browsers__stripped'][$month][''][$country][$web_browser_stripped]++;

                    if (!isset($data_buckets['user_agent_types'][$month][''][$country][$user_agent_type])) {
                        $data_buckets['user_agent_types'][$month][''][$country][$user_agent_type] = 0;
                    }
                    $data_buckets['user_agent_types'][$month][''][$country][$user_agent_type]++;
                }

                // Referrers...

                if ($is_real_human) {
                    if ($referrer_type != self::REFERRER_TYPE__INTERNAL) {
                        if (!isset($data_buckets['referrer_urls'][$month][''][$country][$referrer_url])) {
                            $data_buckets['referrer_urls'][$month][''][$country][$referrer_url] = 0;
                        }
                        $data_buckets['referrer_urls'][$month][''][$country][$referrer_url]++;
                        if (!isset($data_buckets['referrer_domains'][$month][''][$country][$referrer_domain])) {
                            $data_buckets['referrer_domains'][$month][''][$country][$referrer_domain] = 0;
                        }
                        $data_buckets['referrer_domains'][$month][''][$country][$referrer_domain]++;
                    }

                    if (!isset($data_buckets['referrer_type'][$month][''][$country][$referrer_type])) {
                        $data_buckets['referrer_type'][$month][''][$country][$referrer_type] = 0;
                    }
                    $data_buckets['referrer_type'][$month][''][$country][$referrer_type]++;
                }

                // Speed...

                $page_speed = $row['milliseconds'];

                // Build up our distribution (in the Gaussian sense) of page speeds
                $speed_bracket = $this->find_value_bracket($this->speed_brackets, $page_speed);
                if (!isset($data_buckets['load_times_spread'][$month][''][$speed_bracket])) {
                    $data_buckets['load_times_spread'][$month][''][$speed_bracket] = 0;
                }
                $data_buckets['load_times_spread'][$month][''][$speed_bracket]++;

                // Build in speed of this hit to its particular page for its particular month -- gives us an idea about what are our slow and fast pages
                if (!isset($data_buckets['page_average_speeds'][$month][''][$page_link])) {
                    $data_buckets['page_average_speeds'][$month][''][$page_link] = [0, 0];
                }
                $data_buckets['page_average_speeds'][$month][''][$page_link][0] += $page_speed;
                $data_buckets['page_average_speeds'][$month][''][$page_link][1]++;

                foreach (array_keys($date_pivots) as $pivot) {
                    $pivot_value = $this->calculate_date_pivot_value($pivot, $timestamp);

                    // Build in speed of this hit to its particular page for its particular month - but pivoted e.g. by hour of day, day of week, etc -- gives us an idea about peak times
                    if (!isset($data_buckets['average_page_speed'][$month][$pivot][$pivot_value][$page_link])) {
                        $data_buckets['average_page_speed'][$month][$pivot][$pivot_value][$page_link] = [0, 0];
                    }
                    $data_buckets['average_page_speed'][$month][$pivot][$pivot_value][$page_link][0] += $page_speed;
                    $data_buckets['average_page_speed'][$month][$pivot][$pivot_value][$page_link][1]++;
                }

                // Languages and countries...

                if ($is_real_human) {
                    $language = preg_replace('#[\-_].*$#', '', $row['requested_language']);
                    if (!isset($data_buckets['requested_languages'][$month][''][$language])) {
                        $data_buckets['requested_languages'][$month][''][$language] = 0;
                    }
                    $data_buckets['requested_languages'][$month][''][$language]++;

                    if (has_geolocation_data()) {
                        if (!isset($data_buckets['countries'][$month][''][$country])) {
                            $data_buckets['countries'][$month][''][$country] = 0;
                        }
                        $data_buckets['countries'][$month][''][$country]++;
                    }
                }
            }

            cms_profile_end_for('Hook_admin_stats_views->preprocess_raw_data (group ' . integer_format($start) . ')');
            $start += $max;
        } while (!empty($rows));
        cms_profile_end_for('Hook_admin_stats_views->preprocess_raw_data (main processing loop)');

        // We need to anonymise the IPs and simplify the data structure...

        cms_profile_start_for('Hook_admin_stats_views->preprocess_raw_data (simplify data)');
        foreach ($data_buckets['total_unique_views'] as $month => $_) {
            foreach ($_ as $pivot => $__) {
                foreach ($__ as $pivot_value => $___) {
                    foreach ($___ as $country => $____) {
                        $data_buckets['total_unique_views'][$month][$pivot][$pivot_value][$country] = [];

                        foreach ($____ as $unique_identifier => $_____) {
                            if (!is_string($unique_identifier)) {
                                $unique_identifier = strval($unique_identifier);
                            }
                            $data_buckets['total_unique_views'][$month][$pivot][$pivot_value][$country][] = md5($unique_identifier);
                        }
                    }
                }
            }
        }
        cms_profile_end_for('Hook_admin_stats_views->preprocess_raw_data (simplify data)');

        // Session behaviours (slow)...

        cms_profile_start_for('Hook_admin_stats_views->preprocess_raw_data (session behaviours)');
        foreach (array_keys($found_sessions) as $session_id) {
            $first_page_link = null;
            $first_page_timestamp = null;
            $last_page_link = null;
            $last_page_timestamp = null;
            $total_views = 0;

            $month = null;
            $country = '';

            $start = 0;
            $max = 1000;
            do {
                cms_profile_start_for('Hook_admin_stats_views->preprocess_raw_data (session behaviours) (session ' . $session_id . ' group ' . integer_format($start) . ')');
                $rows = $GLOBALS['SITE_DB']->query_select('stats', ['page_link', 'date_and_time', 'ip'], ['session_id' => $session_id], 'ORDER BY date_and_time', $max, $start);
                foreach ($rows as $row) {
                    $timestamp = $row['date_and_time'];
                    if ($timestamp < $start_time) {
                        continue;
                    }
                    $timestamp = tz_time($timestamp, $server_timezone);

                    $month = to_epoch_interval_index($timestamp, 'months');

                    list($zone, $attributes) = page_link_decode($row['page_link']);
                    $page = isset($attributes['page']) ? $attributes['page'] : DEFAULT_ZONE_PAGE_NAME;
                    $page_link = $zone . ':' . $page;

                    if ($first_page_link === null) {
                        $first_page_link = $page_link;
                        $first_page_timestamp = $timestamp;

                        $ip_address = $row['ip'];
                        $country = geolocate_ip($ip_address);
                        if ($country === null) {
                            $country = '';
                        }
                    }

                    $last_page_link = $page_link;
                    $last_page_timestamp = $timestamp;

                    $total_views++;
                }

                cms_profile_start_for('Hook_admin_stats_views->preprocess_raw_data (session behaviours) (session ' . $session_id . ' group ' . integer_format($start) . ')');
                $start += $max;
            } while (!empty($rows));

            $is_bounce = ($total_views == 1);
            $session_duration = $last_page_timestamp - $first_page_timestamp;

            foreach (array_keys($date_pivots) as $pivot) {
                $pivot_value = $this->calculate_date_pivot_value($pivot, $timestamp);

                if (!isset($data_buckets['session_bounce_rates'][$month][$pivot][$pivot_value][$country][$page_link])) {
                    $data_buckets['session_bounce_rates'][$month][$pivot][$pivot_value][$country][$page_link] = [0, 0];
                }
                if ($is_bounce) {
                    $data_buckets['session_bounce_rates'][$month][$pivot][$pivot_value][$country][$page_link][0]++;
                }
                $data_buckets['session_bounce_rates'][$month][$pivot][$pivot_value][$country][$page_link][1]++;

                if (!isset($data_buckets['average_session_duration'][$month][$pivot][$pivot_value][$country])) {
                    $data_buckets['average_session_duration'][$month][$pivot][$pivot_value][$country] = [0, 0];
                }
                $data_buckets['average_session_duration'][$month][$pivot][$pivot_value][$country][0] += $session_duration;
                $data_buckets['average_session_duration'][$month][$pivot][$pivot_value][$country][1]++;

                if (!isset($data_buckets['average_session_total_views'][$month][$pivot][$pivot_value][$country])) {
                    $data_buckets['average_session_total_views'][$month][$pivot][$pivot_value][$country] = [0, 0];
                }
                $data_buckets['average_session_total_views'][$month][$pivot][$pivot_value][$country][0] += $total_views;
                $data_buckets['average_session_total_views'][$month][$pivot][$pivot_value][$country][1]++;
            }

            if (!isset($data_buckets['session_entry_pages'][$month][''][$country][$first_page_link])) {
                $data_buckets['session_entry_pages'][$month][''][$country][$first_page_link] = 0;
            }
            $data_buckets['session_entry_pages'][$month][''][$country][$first_page_link]++;

            if (!isset($data_buckets['session_exit_pages'][$month][''][$country][$last_page_link])) {
                $data_buckets['session_exit_pages'][$month][''][$country][$last_page_link] = 0;
            }
            $data_buckets['session_exit_pages'][$month][''][$country][$last_page_link]++;

            $session_duration_bracket = $this->find_value_bracket($this->session_duration_brackets, $session_duration);
            if (!isset($data_buckets['session_durations'][$month][''][$country][$session_duration_bracket])) {
                $data_buckets['session_durations'][$month][''][$country][$session_duration_bracket] = 0;
            }
            $data_buckets['session_durations'][$month][''][$country][$session_duration_bracket]++;

            if (!isset($data_buckets['session_total_views'][$month][''][$country][$total_views])) {
                $data_buckets['session_total_views'][$month][''][$country][$total_views] = 0;
            }
            $data_buckets['session_total_views'][$month][''][$country][$total_views]++;
        }
        cms_profile_end_for('Hook_admin_stats_views->preprocess_raw_data (session behaviours)');

        cms_profile_end_for('Hook_admin_stats_views->preprocess_raw_data');

        cms_set_time_limit($old);
    }

    /**
     * Remove minor versioning from a user agent string.
     *
     * @param  string $value Raw user agent string (or partial string)
     * @return string Simplified user agent string
     */
    protected function remove_minor_versioning(string $value) : string
    {
        $value = preg_replace('#(\d+)(\.\d+)+#', '$1.x', $value);
        return $value;
    }

    /**
     * See if a domain matches a list of regexps.
     *
     * @param  array $list List of regexps
     * @param  string $domain Domain name
     * @return boolean Whether it matches
     */
    protected function domain_list_match(array $list, string $domain) : bool
    {
        foreach ($list as $pattern) {
            $pattern = trim($pattern);
            if ($pattern != '') {
                if (preg_match('#' . $pattern . '#', $domain) != 0) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * Generate final data from preprocessed data.
     *
     * @param  string $bucket Data bucket we want data for
     * @param  string $pivot Pivot value
     * @param  array $filters Map of filters (including pivot if applicable)
     * @return ?array Final data in standardised map format (null: could not generate)
     */
    public function generate_final_data(string $bucket, string $pivot, array $filters) : ?array
    {
        require_lang('dates');

        switch ($bucket) {
            case 'total_views':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = $this->fill_data_by_date_pivots($pivot, $range[0], $range[1]);

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $pivot_value => $__) {
                        $pivot_value = $this->make_date_pivot_value_nice($pivot, $pivot_value);

                        foreach ($__ as $country => $___) {
                            if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                                continue;
                            }

                            foreach ($___ as $page_link => $total_views) {
                                if (!empty($filters[$bucket . '__page_link'])) {
                                    list($current_zone_name, $attributes) = page_link_decode($page_link);
                                    $current_page_name = isset($attributes['page']) ? $attributes['page'] : DEFAULT_ZONE_PAGE_NAME;
                                    if (!match_key_match($filters[$bucket . '__page_link'], false, null, $current_zone_name, $current_page_name)) {
                                        continue;
                                    }
                                }

                                if (!isset($data[$pivot_value])) {
                                    $data[$pivot_value] = 0;
                                }
                                $data[$pivot_value] += $total_views;
                            }
                        }
                    }
                }

                return [
                    'type' => null,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TIME_IN_TIMEZONE', escape_html(make_nice_timezone_name(get_site_timezone()))),
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                ];

            case 'total_unique_views':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = $this->fill_data_by_date_pivots($pivot, $range[0], $range[1]);

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $pivot_value => $__) {
                        $pivot_value = $this->make_date_pivot_value_nice($pivot, $pivot_value);

                        $_hashed_ips = [];

                        foreach ($__ as $country => $hashed_ips) {
                            if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                                continue;
                            }

                            foreach ($hashed_ips as $hashed_ip) {
                                $_hashed_ips[$hashed_ip] = true;
                            }
                        }

                        $data[$pivot_value] = count($_hashed_ips);
                    }
                }

                return [
                    'type' => null,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TIME_IN_TIMEZONE', escape_html(make_nice_timezone_name(get_site_timezone()))),
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                ];

            case 'total_referrals':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = $this->fill_data_by_date_pivots($pivot, $range[0], $range[1]);

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $pivot_value => $__) {
                        $pivot_value = $this->make_date_pivot_value_nice($pivot, $pivot_value);

                        foreach ($__ as $country => $total) {
                            if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                                continue;
                            }

                            if (!isset($data[$pivot_value])) {
                                $data[$pivot_value] = 0;
                            }
                            $data[$pivot_value] += $total;
                        }
                    }
                }

                return [
                    'type' => null,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TIME_IN_TIMEZONE', escape_html(make_nice_timezone_name(get_site_timezone()))),
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                ];

            case 'average_session_total_views':
            case 'average_session_duration':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = $this->fill_data_by_date_pivots($pivot, $range[0], $range[1]);

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $pivot_value => $__) {
                        $pivot_value = $this->make_date_pivot_value_nice($pivot, $pivot_value);

                        $aggregate_total = 0;
                        $records_total = 0;

                        foreach ($__ as $country => $___) {
                            if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                                continue;
                            }

                            $aggregate_total += $___[0];
                            $records_total += $___[1];
                        }

                        $data[$pivot_value] = floatval($aggregate_total) / floatval($records_total);
                    }
                }

                switch ($bucket) {
                    case 'average_session_total_views':
                        $y_axis_label = do_lang_tempcode('AVERAGE_SESSION_TOTAL_VIEWS');
                        break;

                    case 'average_session_duration':
                        $y_axis_label = do_lang_tempcode('AVERAGE_SESSION_DURATION_SECONDS');
                        break;
                }

                if (array_sum($data) == 0) {
                    $data = [];
                }

                return [
                    'type' => null,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TIME_IN_TIMEZONE', escape_html(make_nice_timezone_name(get_site_timezone()))),
                    'y_axis_label' => $y_axis_label,
                ];

            case 'session_durations':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];
                foreach ($this->session_duration_brackets as $bracket) {
                    $bracket = $this->cleanup_session_duration($bracket);
                    $data[$bracket] = 0;
                }

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $country => $__) {
                        if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                            continue;
                        }

                        foreach ($__ as $bracket => $total) {
                            $bracket = $this->cleanup_session_duration($bracket);
                            $data[$bracket] += $total;
                        }
                    }
                }

                if (array_sum($data) == 0) {
                    $data = [];
                }

                return [
                    'type' => self::GRAPH_BAR_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TIME_IN_TIMEZONE', escape_html(make_nice_timezone_name(get_site_timezone()))),
                    'y_axis_label' => do_lang_tempcode('ENTRIES'),
                ];

            case 'user_agent_types':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $country => $__) {
                        if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                            continue;
                        }

                        foreach ($__ as $user_agent_type => $total_views) {
                            switch ($user_agent_type) {
                                case self::USER_AGENT_TYPE__UNKNOWN:
                                    $user_agent_type = do_lang('_UNKNOWN');
                                    break;
                                case self::USER_AGENT_TYPE__DESKTOP:
                                    $user_agent_type = do_lang('USER_AGENT_TYPE__DESKTOP');
                                    break;
                                case self::USER_AGENT_TYPE__MOBILE:
                                    $user_agent_type = do_lang('USER_AGENT_TYPE__MOBILE');
                                    break;
                                case self::USER_AGENT_TYPE__BOT:
                                    $user_agent_type = do_lang('USER_AGENT_TYPE__BOT');
                                    break;
                            }

                            if (!isset($data[$user_agent_type])) {
                                $data[$user_agent_type] = 0;
                            }
                            $data[$user_agent_type] += $total_views;
                        }
                    }
                }

                if (array_sum($data) == 0) {
                    $data = [];
                }

                return [
                    'type' => self::GRAPH_PIE_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TYPE'),
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                ];

            case 'referrer_type':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $country => $__) {
                        if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                            continue;
                        }

                        foreach ($__ as $referrer_type => $total_views) {
                            switch ($referrer_type) {
                                case self::REFERRER_TYPE__DIRECT:
                                    $referrer_type = do_lang('REFERRER_TYPE__DIRECT');
                                    break;
                                case self::REFERRER_TYPE__UNKNOWN:
                                    $referrer_type = do_lang('_UNKNOWN');
                                    break;
                                case self::REFERRER_TYPE__INTERNAL:
                                    $referrer_type = do_lang('REFERRER_TYPE__INTERNAL');
                                    break;
                                case self::REFERRER_TYPE__EXTERNAL_SEARCH_ENGINE:
                                    $referrer_type = do_lang('REFERRER_TYPE__EXTERNAL_SEARCH_ENGINE');
                                    break;
                                case self::REFERRER_TYPE__EXTERNAL_SOCIAL_MEDIA:
                                    $referrer_type = do_lang('REFERRER_TYPE__EXTERNAL_SOCIAL_MEDIA');
                                    break;
                                case self::REFERRER_TYPE__EXTERNAL_MISC:
                                    $referrer_type = do_lang('REFERRER_TYPE__EXTERNAL_MISC');
                                    break;
                            }

                            if (!isset($data[$referrer_type])) {
                                $data[$referrer_type] = 0;
                            }
                            $data[$referrer_type] += $total_views;
                        }
                    }
                }

                if (array_sum($data) == 0) {
                    $data = [];
                }

                return [
                    'type' => self::GRAPH_PIE_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TYPE'),
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                ];

            case 'popular_pages':
            case 'operating_systems':
            case 'operating_systems__stripped':
            case 'web_browsers':
            case 'web_browsers__stripped':
            case 'referrer_urls':
            case 'referrer_domains':
            case 'session_entry_pages':
            case 'session_exit_pages':
            case 'session_total_views':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $country => $__) {
                        if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                            continue;
                        }

                        foreach ($__ as $bar => $total_views) {
                            if ($bar === '') {
                                $bar = do_lang('_UNKNOWN');
                            }

                            if (!isset($data[$bar])) {
                                $data[$bar] = 0;
                            }
                            $data[$bar] += $total_views;
                        }
                    }
                }

                if (array_sum($data) == 0) {
                    $data = [];
                }

                switch ($bucket) {
                    case 'operating_systems':
                    case 'operating_systems__stripped':
                        $x_axis_label = do_lang_tempcode('USER_OS');
                        break;

                    case 'web_browsers':
                    case 'web_browsers__stripped':
                        $x_axis_label = do_lang_tempcode('WEB_BROWSER');
                        break;

                    case 'referrer_urls':
                        $x_axis_label = do_lang_tempcode('REFERER');
                        break;

                    case 'referrer_domains':
                        $x_axis_label = do_lang_tempcode('DOMAIN');
                        break;

                    case 'popular_pages':
                    case 'session_entry_pages':
                    case 'session_exit_pages':
                        $x_axis_label = do_lang_tempcode('PAGE_LINK');
                        break;

                    case 'session_total_views':
                        $x_axis_label = do_lang_tempcode('COUNT_VIEWS');
                        break;

                    default:
                        fatal_exit(do_lang_tempcode('INTERNAL_ERROR', escape_html('6e32e7f641775168bafdf0cf49ac48b7')));
                }

                return [
                    'type' => self::GRAPH_BAR_CHART,
                    'data' => $data,
                    'x_axis_label' => $x_axis_label,
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                    'limit_bars' => true,
                ];

            case 'load_times_spread':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];
                foreach ($this->speed_brackets as $bracket) {
                    $bracket = $this->cleanup_speed($bracket);
                    $data[$bracket] = 0;
                }

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $bracket => $total) {
                        $bracket = $this->cleanup_speed($bracket);
                        $data[$bracket] += $total;
                    }
                }

                return [
                    'type' => self::GRAPH_BAR_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TIME_IN_TIMEZONE', escape_html(make_nice_timezone_name(get_site_timezone()))),
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                ];

            case 'requested_languages':
                require_code('lang2');

                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $bar => $total) {
                        if (empty($bar)) {
                            $nice_bar = do_lang('_UNKNOWN');
                        } else {
                            $nice_bar = lookup_language_full_name(cms_strtoupper_ascii($bar));
                            if (empty($nice_bar)) {
                                $nice_bar = lookup_language_full_name(cms_strtoupper_ascii(preg_replace('#_.*$#', '', $bar)));
                            }
                            if (empty($nice_bar)) {
                                $nice_bar = $bar;
                            }
                        }

                        if (!isset($data[$nice_bar])) {
                            $data[$nice_bar] = 0;
                        }
                        $data[$nice_bar] += $total;
                    }
                }

                if (array_sum($data) == 0) {
                    $data = [];
                }

                return [
                    'type' => self::GRAPH_BAR_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('LANGUAGE'),
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                    'limit_bars' => true,
                ];

            case 'countries':
                require_code('locations');

                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $bar => $total) {
                        if (empty($bar)) {
                            $nice_bar = do_lang('_UNKNOWN');
                        } else {
                            $nice_bar = find_country_name_from_iso($bar);
                            if (empty($nice_bar)) {
                                $nice_bar = $bar;
                            }
                        }

                        if (!isset($data[$nice_bar])) {
                            $data[$nice_bar] = 0;
                        }
                        $data[$nice_bar] += $total;
                    }
                }

                if (array_sum($data) == 0) {
                    $data = [];
                }

                return [
                    'type' => self::GRAPH_BAR_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('COUNTRY'),
                    'y_axis_label' => do_lang_tempcode('COUNT_TOTAL'),
                    'limit_bars' => true,
                ];

            case 'average_page_speed':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $pivot_value => $__) {
                        $pivot_value = $this->make_date_pivot_value_nice($pivot, $pivot_value);

                        $total_time_spent = 0;
                        $total_views = 0;

                        foreach ($__ as $page_link => $___) {
                            if (!empty($filters[$bucket . '__page_link'])) {
                                list($current_zone_name, $attributes) = page_link_decode($page_link);
                                $current_page_name = isset($attributes['page']) ? $attributes['page'] : DEFAULT_ZONE_PAGE_NAME;
                                if (!match_key_match($filters[$bucket . '__page_link'], false, null, $current_zone_name, $current_page_name)) {
                                    continue;
                                }
                            }

                            list($_total_time_spent, $_total_views) = $___;
                            $total_time_spent += $_total_time_spent;
                            $total_views += $_total_views;
                        }

                        if ($total_views != 0) {
                            $data[$pivot_value] = floatval($total_time_spent) / floatval($total_views);
                        }
                    }
                }

                return [
                    'type' => null,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TIME_IN_TIMEZONE', escape_html(make_nice_timezone_name(get_site_timezone()))),
                    'y_axis_label' => do_lang_tempcode('TIME_IN_MILLISECONDS'),
                ];

            case 'session_bounce_rates':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $pivot_value => $__) {
                        $pivot_value = $this->make_date_pivot_value_nice($pivot, $pivot_value);

                        $total_bounces = 0;
                        $total_views = 0;

                        foreach ($__ as $country => $___) {
                            if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                                continue;
                            }

                            foreach ($___ as $page_link => $____) {
                                if (!empty($filters[$bucket . '__page_link'])) {
                                    list($current_zone_name, $attributes) = page_link_decode($page_link);
                                    $current_page_name = isset($attributes['page']) ? $attributes['page'] : DEFAULT_ZONE_PAGE_NAME;
                                    if (!match_key_match($filters[$bucket . '__page_link'], false, null, $current_zone_name, $current_page_name)) {
                                        continue;
                                    }
                                }

                                list($_total_bounces, $_total_views) = $____;
                                $total_bounces += $_total_bounces;
                                $total_views += $_total_views;
                            }
                        }

                        if ($total_views != 0) {
                            $data[$pivot_value] = 100.0 * floatval($total_bounces) / floatval($total_views);
                        }
                    }
                }

                return [
                    'type' => null,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('TIME_IN_TIMEZONE', escape_html(make_nice_timezone_name(get_site_timezone()))),
                    'y_axis_label' => do_lang_tempcode('PERCENTAGE'),
                ];

            case 'page_average_speeds':
                $range = $this->convert_month_range_filter_to_pair($filters[$bucket . '__month_range']);

                $data = [];

                $where = [
                    'p_bucket' => $bucket,
                    'p_pivot' => $pivot,
                ];
                $extra = '';
                $extra .= ' AND p_month>=' . strval($range[0]);
                $extra .= ' AND p_month<=' . strval($range[1]);
                $data_rows = $GLOBALS['SITE_DB']->query_select('stats_preprocessed', ['p_data'], $where, $extra);
                foreach ($data_rows as $data_row) {
                    $_data = @unserialize($data_row['p_data']);
                    foreach ($_data as $page_link => $__) {
                        list($total_compound_speed, $total_views) = $__;

                        if ($total_views != 0) {
                            if (!isset($data[$page_link])) {
                                $data[$page_link] = 0.0;
                            }
                            $data[$page_link] += floatval($total_compound_speed) / floatval($total_views);
                        }
                    }
                }

                if (array_sum($data) == 0) {
                    $data = [];
                }

                return [
                    'type' => self::GRAPH_BAR_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('PAGE_LINK'),
                    'y_axis_label' => do_lang_tempcode('_AVERAGE', do_lang_tempcode('TIME_IN_MILLISECONDS')),
                    'limit_bars' => true,
                ];
        }

        fatal_exit(do_lang_tempcode('INTERNAL_ERROR', escape_html('11f6ccbda65856098498a9f31b8b9581')));

        return [];
    }

    /**
     * Make a speed range more readable.
     *
     * @param  string $bracket Bracket
     * @return string Readable bracket
     */
    protected function cleanup_speed(string $bracket) : string
    {
        if (empty($bracket)) {
            $bracket = do_lang('_UNKNOWN');
            return $bracket;
        }

        $matches = [];
        $num_matches = preg_match_all('#\d+#', $bracket, $matches);
        if (($num_matches == 2) && (intval($matches[0][0]) >= 1000) && (intval($matches[0][1]) >= 1000)) {
            $number = $matches[0][0] . '-' . $matches[0][1];
            $a = integer_format(intval(round(floatval($matches[0][0]) / 1000.0)));
            $b = integer_format(intval(round(floatval($matches[0][1]) / 1000.0)));
            $bracket = str_replace($number, do_lang('SECONDS', $a . '-' . $b), $bracket);
        } elseif ($num_matches == 2) {
            $number = $matches[0][0] . '-' . $matches[0][1];
            $a = integer_format(intval($matches[0][0]));
            $b = integer_format(intval($matches[0][1]));
            $bracket = str_replace($number, do_lang('MILLISECONDS', $a . '-' . $b), $bracket);
        } else {
            for ($i = 0; $i < $num_matches; $i++) {
                $number = $matches[0][$i];
                if (intval($number) >= 1000) {
                    $bracket = str_replace($number, do_lang('SECONDS', integer_format(intval(round(floatval($number) / 1000.0)))), $bracket);
                } else {
                    $bracket = str_replace($number, do_lang('MILLISECONDS', integer_format(intval($number))), $bracket);
                }
            }
        }

        return $bracket;
    }

    /**
     * Make a sesson duration range more readable.
     *
     * @param  string $bracket Bracket
     * @return string Readable bracket
     */
    protected function cleanup_session_duration(string $bracket) : string
    {
        if (empty($bracket)) {
            $bracket = do_lang('_UNKNOWN');
            return $bracket;
        }

        $matches = [];
        $num_matches = preg_match_all('#\d+#', $bracket, $matches);
        if (($num_matches == 2) && (intval($matches[0][0]) >= 3600) && (intval($matches[0][1]) >= 3600)) {
            $number = $matches[0][0] . '-' . $matches[0][1];
            $a = integer_format(intval(round(floatval($matches[0][0]) / 3600.0)));
            $b = integer_format(intval(round(floatval($matches[0][1]) / 3600.0)));
            $bracket = str_replace($number, do_lang('HOURS', $a . '-' . $b), $bracket);
        } elseif (($num_matches == 2) && (intval($matches[0][0]) >= 60) && (intval($matches[0][1]) >= 60)) {
            $number = $matches[0][0] . '-' . $matches[0][1];
            $a = integer_format(intval(round(floatval($matches[0][0]) / 60.0)));
            $b = integer_format(intval(round(floatval($matches[0][1]) / 60.0)));
            $bracket = str_replace($number, do_lang('MINUTES', $a . '-' . $b), $bracket);
        } elseif ($num_matches == 2) {
            $number = $matches[0][0] . '-' . $matches[0][1];
            $a = integer_format(intval($matches[0][0]));
            $b = integer_format(intval($matches[0][1]));
            $bracket = str_replace($number, do_lang('SECONDS', $a . '-' . $b), $bracket);
        } else {
            for ($i = 0; $i < $num_matches; $i++) {
                $number = $matches[0][$i];
                if (intval($number) >= 3600) {
                    $bracket = str_replace($number, do_lang('HOURS', integer_format(intval(round(floatval($number) / 3600.0)))), $bracket);
                } elseif (intval($number) >= 60) {
                    $bracket = str_replace($number, do_lang('MINUTES', integer_format(intval(round(floatval($number) / 60.0)))), $bracket);
                } else {
                    $bracket = str_replace($number, do_lang('SECONDS', integer_format(intval($number))), $bracket);
                }
            }
        }

        return $bracket;
    }
}
