<?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
 */

// File also covers tracking codes

/**
 * Hook class.
 */
class Hook_admin_stats_events extends CMSStatsProvider
{
    /**
     * Get a list of top events.
     *
     * @return array List of events
     */
    protected function get_top_events() : array
    {
        static $events = null;
        if ($events !== null) {
            return $events;
        }
        $events = [];
        $event_rows = $GLOBALS['SITE_DB']->query_select('stats_known_events', ['*'], [], 'ORDER BY e_count_logged DESC', 50);
        foreach ($event_rows as $event_row) {
            $event_label = do_lang($event_row['e_event'], null, null, null, null, false);
            if ($event_label === null) {
                $event_label = $event_row['e_event'];
            }
            $events[$event_row['e_event']] = $event_label . ' (' . integer_format($event_row['e_count_logged']) . ')';
        }
        asort($events);
        return $events;
    }

    /**
     * Get a list of top tracking codes.
     *
     * @return array List of tracking codes
     */
    protected function get_top_tracking_codes() : array
    {
        static $tracking_codes = null;
        if ($tracking_codes !== null) {
            return $tracking_codes;
        }
        $tracking_codes = [];
        $tracking_code_rows = $GLOBALS['SITE_DB']->query_select('stats_known_tracking', ['*'], [], 'ORDER BY t_count_logged DESC', 50);
        foreach ($tracking_code_rows as $tracking_code_row) {
            $tracking_codes[$tracking_code_row['t_tracking_code']] = $tracking_code_row['t_tracking_code'] . ' (' . integer_format($tracking_code_row['t_count_logged']) . ')';
        }
        asort($tracking_codes);
        return $tracking_codes;
    }

    /**
     * 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_code('locations');

        $top_events = $this->get_top_events();

        $top_tracking_codes = $this->get_top_tracking_codes();

        return [
            'events' => [
                'label' => do_lang_tempcode('STATS_EVENTS'),
                'category' => 'conversions',
                'filters' => [
                    'events__month_range' => new CMSStatsDateMonthRangeFilter('events__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'events__country' => new CMSStatsListFilter('events__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()),
                    'events__event' => new CMSStatsListFilter('events__event', do_lang_tempcode('STATS_EVENT'), $top_events),
                ],
                'pivot' => new CMSStatsDatePivot('events__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
            'tracking_code_usage' => [
                'label' => do_lang_tempcode('TRACKING_CODE_USAGE'),
                'category' => 'conversions',
                'filters' => [
                    'tracking_code_usage__month_range' => new CMSStatsDateMonthRangeFilter('tracking_code_usage__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'tracking_code_usage__country' => new CMSStatsListFilter('tracking_code_usage__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()),
                    'tracking_code_usage__event' => new CMSStatsListFilter('tracking_code_usage__tracking_code', do_lang_tempcode('TRACKING_CODE'), $top_tracking_codes),
                ],
                'pivot' => new CMSStatsDatePivot('tracking_code_usage__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
            'conversion_rates' => [
                'label' => do_lang_tempcode('CONVERSION_RATES'),
                'category' => 'conversions',
                'filters' => [
                    'conversion_rates__month_range' => new CMSStatsDateMonthRangeFilter('conversion_rates__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'conversion_rates__country' => new CMSStatsListFilter('conversion_rates__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()),
                    'conversion_rates__event' => new CMSStatsListFilter('conversion_rates__event', do_lang_tempcode('STATS_EVENT'), $top_events),
                ],
                'pivot' => new CMSStatsDatePivot('conversion_rates__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
            'tracking_code_conversion_rates' => [
                'label' => do_lang_tempcode('TRACKING_CODE_CONVERSION_RATES'),
                'category' => 'conversions',
                'filters' => [
                    'tracking_code_conversion_rates__month_range' => new CMSStatsDateMonthRangeFilter('tracking_code_conversion_rates__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'tracking_code_conversion_rates__country' => new CMSStatsListFilter('tracking_code_conversion_rates__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()),
                    'tracking_code_conversion_rates__event' => new CMSStatsListFilter('tracking_code_conversion_rates__event', do_lang_tempcode('STATS_EVENT'), $top_events),
                    'tracking_code_conversion_rates_usage__event' => new CMSStatsListFilter('tracking_code_conversion_rates_usage__tracking_code', do_lang_tempcode('TRACKING_CODE'), $top_tracking_codes),
                ],
                'pivot' => new CMSStatsDatePivot('tracking_code_conversion_rates__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
        ];
    }

    /**
     * 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)
    {
        require_code('temporal');

        $server_timezone = get_server_timezone();

        $max = 1000;
        $start = 0;

        require_code('locations');

        $top_events = $this->get_top_events();
        $top_tracking_codes = $this->get_top_tracking_codes();

        $date_pivots = $this->get_date_pivots();

        // Basic event processing...

        $events_seen = [];

        $query_events = 'SELECT * FROM ' . get_table_prefix() . 'stats_events WHERE ';
        $query_events .= 'e_date_and_time>=' . strval($start_time) . ' AND ';
        $query_events .= 'e_date_and_time<=' . strval($end_time);
        $query_events .= ' ORDER BY e_date_and_time';
        do {
            $event_rows = $GLOBALS['SITE_DB']->query($query_events, $max, $start);
            foreach ($event_rows as $event_row) {
                $timestamp = $event_row['e_date_and_time'];
                $timestamp = tz_time($timestamp, $server_timezone);

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

                $country_code = $event_row['e_country_code'];
                $event = $event_row['e_event'];

                if (!isset($events_seen[$event])) {
                    $events_seen[$event] = 0;
                }
                $events_seen[$event]++;

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

                    if (!isset($data_buckets['events'][$month][$pivot][$pivot_value][$event][$country_code])) {
                        $data_buckets['events'][$month][$pivot][$pivot_value][$event][$country_code] = 0;
                    }
                    $data_buckets['events'][$month][$pivot][$pivot_value][$event][$country_code]++;
                }
            }

            $start += $max;
        } while (!empty($event_rows));

        // Processing of tracking codes...

        $tracking_codes_seen = [];

        $start = 0;

        // Find sessions
        $query_sessions = 'SELECT session_id,MIN(date_and_time) AS date_and_time FROM ' . get_table_prefix() . 'stats WHERE ';
        $query_sessions .= 'date_and_time>=' . strval($start_time) . ' AND ';
        $query_sessions .= 'date_and_time<=' . strval($end_time) . ' AND ';
        $query_sessions .= db_string_not_equal_to('session_id', '');
        $query_sessions .= ' GROUP BY session_id';
        $query_sessions .= ' ORDER BY date_and_time';
        do {
            $session_rows = $GLOBALS['SITE_DB']->query($query_sessions, $max, $start);
            foreach ($session_rows as $session_row) {
                $timestamp = $session_row['date_and_time'];
                if ($timestamp < $start_time) {
                    continue;
                }
                $timestamp = tz_time($timestamp, $server_timezone);

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

                $session_id = $session_row['session_id'];

                // Find tracking codes for this session
                $ip = null;
                $tracking_codes = [];
                $tracking_code_rows = $GLOBALS['SITE_DB']->query_select('stats', ['tracking_code', 'ip'], ['session_id' => $session_id], ' AND ' . db_string_not_equal_to('tracking_code', ''));
                foreach ($tracking_code_rows as $tracking_code_row) {
                    if ($ip === null) {
                        $ip = $tracking_code_row['ip'];
                    }
                    $tracking_codes = array_merge($tracking_codes, explode(',', $tracking_code_row['tracking_code']));
                }
                $tracking_codes = array_unique($tracking_codes);

                $country_code = geolocate_ip($ip);
                if ($country_code === null) {
                    $country_code = '';
                }

                // Each tracking code
                foreach ($tracking_codes as $tracking_code) {
                    if (!isset($tracking_codes_seen[$tracking_code])) {
                        $tracking_codes_seen[$tracking_code] = 0;
                    }
                    $tracking_codes_seen[$tracking_code]++;

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

                        if (!isset($data_buckets['tracking_code_usage'][$month][$pivot][$pivot_value][$tracking_code][$country_code])) {
                            $data_buckets['tracking_code_usage'][$month][$pivot][$pivot_value][$tracking_code][$country_code] = 0;
                        }
                        $data_buckets['tracking_code_usage'][$month][$pivot][$pivot_value][$tracking_code][$country_code]++;
                    }
                }

                // Find events for this session
                $events_for_session = collapse_2d_complexity('e_event', 'e_event', $GLOBALS['SITE_DB']->query_select('stats_events', ['e_event'], ['e_session_id' => $session_id]));

                // Each combination of event wrt session
                foreach (array_keys($top_events) as $event) {
                    foreach (array_keys($date_pivots) as $pivot) {
                        $pivot_value = $this->calculate_date_pivot_value($pivot, $timestamp);

                        if (!isset($data_buckets['conversion_rates'][$month][$pivot][$pivot_value][$event])) {
                            $data_buckets['conversion_rates'][$month][$pivot][$pivot_value][$event] = [0, 0];
                        }

                        $data_buckets['conversion_rates'][$month][$pivot][$pivot_value][$event][0]++;
                        if (isset($events_for_session[$event])) {
                            $data_buckets['conversion_rates'][$month][$pivot][$pivot_value][$event][1]++;
                        }
                    }
                }

                // Each combination of event tracking code wrt session
                foreach (array_keys($top_tracking_codes) as $tracking_code) {
                    $data_buckets['tracking_code_conversion_rates'][$month][$pivot][$pivot_value][$session_id][$tracking_code] = []; // We need this as we need to know tracking codes with no events
                    foreach (array_keys($top_events) as $event) {
                        foreach (array_keys($date_pivots) as $pivot) {
                            $pivot_value = $this->calculate_date_pivot_value($pivot, $timestamp);

                            if (!isset($data_buckets['tracking_code_conversion_rates'][$month][$pivot][$pivot_value][$session_id][$tracking_code][$event])) {
                                $data_buckets['tracking_code_conversion_rates'][$month][$pivot][$pivot_value][$session_id][$tracking_code][$event] = [0, 0];
                            }

                            $data_buckets['tracking_code_conversion_rates'][$month][$pivot][$pivot_value][$session_id][$tracking_code][$event][0]++;
                            if (isset($events_for_session[$event])) {
                                $data_buckets['tracking_code_conversion_rates'][$month][$pivot][$pivot_value][$session_id][$tracking_code][$event][1]++;
                            }
                        }
                    }
                }
            }

            $start += $max;
        } while (!empty($session_rows));

        // Now keep a record of events
        foreach ($events_seen as $event => $times_seen) {
            $times_seen_before = $GLOBALS['SITE_DB']->query_select_value_if_there('stats_known_events', 'e_count_logged', ['e_event' => $event]);
            if ($times_seen_before === null) {
                $GLOBALS['SITE_DB']->query_insert('stats_known_events', [
                    'e_event' => $event,
                    'e_count_logged' => $times_seen,
                ]);
            } else {
                $GLOBALS['SITE_DB']->query_update('stats_known_events', [
                    'e_count_logged' => $times_seen + $times_seen_before,
                ], [
                    'e_event' => $event,
                ], '', 1);
            }
        }

        // Now keep a record of tracking codes
        foreach ($tracking_codes_seen as $tracking_code => $times_seen) {
            $times_seen_before = $GLOBALS['SITE_DB']->query_select_value_if_there('stats_known_tracking', 't_count_logged', ['t_tracking_code' => $tracking_code]);
            if ($times_seen_before === null) {
                $GLOBALS['SITE_DB']->query_insert('stats_known_tracking', [
                    't_tracking_code' => $tracking_code,
                    't_count_logged' => $times_seen,
                ]);
            } else {
                $GLOBALS['SITE_DB']->query_update('stats_known_tracking', [
                    't_count_logged' => $times_seen + $times_seen_before,
                ], [
                    't_tracking_code' => $tracking_code,
                ], '', 1);
            }
        }
    }

    /**
     * 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
    {
        $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);

        switch ($bucket) {
            case 'events':
                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 $event => $__) {
                            if ((!empty($filters[$bucket . '__event'])) && ($filters[$bucket . '__event'] != $event)) {
                                continue;
                            }

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

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

                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 'tracking_code_usage':
                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 $tracking_code => $__) {
                            if ((!empty($filters[$bucket . '__tracking_code'])) && ($filters[$bucket . '__tracking_code'] != $tracking_code)) {
                                continue;
                            }

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

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

                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 'conversion_rates':
                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);

                        $num_sessions = 0;
                        $num_conversions = 0;
                        foreach ($_ as $event => $__) {
                            if ((!empty($filters[$bucket . '__event'])) && ($filters[$bucket . '__event'] != $event)) {
                                continue;
                            }

                            $num_sessions += $__[0];
                            $num_conversions += $__[1];
                        }

                        if ($num_sessions > 0) {
                            $data[$pivot_value] = 100.0 * floatval($num_conversions) / floatval($num_sessions);
                        }
                    }
                }

                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 'tracking_code_conversion_rates':
                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);

                        $num_tracking_code_sessions = 0;
                        $num_conversions = 0;
                        foreach ($_ as $session_id => $__) {
                            foreach ($__ as $tracking_code => $___) {
                                if ((!empty($filters[$bucket . '__tracking_code'])) && ($filters[$bucket . '__tracking_code'] != $tracking_code)) {
                                    continue;
                                }

                                $has_conversion = false;
                                foreach ($___ as $event => $____) {
                                    if ((!empty($filters[$bucket . '__event'])) && ($filters[$bucket . '__event'] != $event)) {
                                        continue;
                                    }

                                    $num_tracking_code_sessions += $____[0];
                                    $num_conversions += $____[1];
                                }
                            }
                        }

                        if ($num_tracking_code_sessions > 0) {
                            $data[$pivot_value] = 100.0 * floatval($num_conversions) / floatval($num_tracking_code_sessions);
                        }
                    }
                }

                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'),
                ];
        }

        fatal_exit(do_lang_tempcode('INTERNAL_ERROR', escape_html('7e0f287999a35d65a4b4b743337a2f4b')));
    }
}
