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

/**
 * Hook class.
 */
class Hook_admin_stats_cns_members extends CMSStatsProvider
{
    protected $default_age_brackets;

    /**
     * Constructor.
     */
    public function __construct()
    {
        $this->default_age_brackets = [
            '<6',
            '6-11',
            '12-17',
            '18-24',
            '25-34',
            '35-44',
            '45-54',
            '55-64',
            '65+',
        ];
    }

    /**
     * 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
    {
        if (get_forum_type() != 'cns') {
            return null;
        }

        require_lang('cns');
        require_code('locations');

        $ret = [
            'members' => [
                'label' => do_lang_tempcode('JOINING'),
                'category' => 'conversions',
                'filters' => [
                    'members__month_range' => new CMSStatsDateMonthRangeFilter('members__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'members__country' => has_geolocation_data() ? new CMSStatsListFilter('members__country', do_lang_tempcode('VISITOR_COUNTRY'), find_countries()) : null,
                ],
                'pivot' => new CMSStatsDatePivot('members__pivot', $this->get_date_pivots(!$for_kpi)),
                'support_kpis' => self::KPI_HIGH_IS_GOOD,
            ],
            'demographics' => [
                'label' => do_lang_tempcode('AGE_RANGE'),
                'category' => 'audience_demographics',
                'filters' => [
                    'demographics__month_range' => new CMSStatsDateMonthRangeFilter('demographics__month_range', do_lang_tempcode('DATE_RANGE'), null, $for_kpi),
                    'demographics__age_brackets' => new CMSStatsTextFilter('demographics__age_brackets', do_lang_tempcode('AGE_RANGE'), implode(',', $this->default_age_brackets)),
                ],
                'pivot' => null,
            ],
            'top_members_by_visits' => [
                'label' => do_lang_tempcode('TOP_MEMBERS_BY_VISITS'),
                'category' => 'feedback_and_engagement',
                'filters' => [
                ],
                'pivot' => null,
            ],
        ];

        if (addon_installed('cns_forum')) {
            $ret['top_members_by_forum_posts'] = [
                'label' => do_lang_tempcode('TOP_MEMBERS_BY_FORUM_POSTS'),
                'category' => 'feedback_and_engagement',
                'filters' => [
                ],
                'pivot' => null,
            ];
        }

        if (addon_installed('points')) {
            $ret['top_members_by_points'] = [
                'label' => do_lang_tempcode('TOP_MEMBERS_BY_POINTS'),
                'category' => 'feedback_and_engagement',
                'filters' => [
                ],
                '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)
    {
        require_code('locations');

        $server_timezone = get_server_timezone();

        $max = 1000;
        $start = 0;

        $date_pivots = $this->get_date_pivots();

        $query = 'SELECT m_join_time,m_dob_year,m_dob_month,m_dob_day,m_ip_address FROM ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_members WHERE ';
        $query .= 'm_join_time>=' . strval($start_time) . ' AND ';
        $query .= 'm_join_time<=' . strval($end_time);
        $query .= ' ORDER BY m_join_time';
        do {
            $rows = $GLOBALS['FORUM_DB']->query($query, $max, $start);
            foreach ($rows as $row) {
                $timestamp = $row['m_join_time'];
                $timestamp = tz_time($timestamp, $server_timezone);

                $month = get_stats_month_for_timestamp($timestamp);

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

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

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

                if ($row['m_dob_year'] !== null) {
                    $age = intval(date('Y')) - $row['m_dob_year'];
                    if (date('md', cms_mktime(0, 0, 0, $row['m_dob_month'], $row['m_dob_day'], $row['m_dob_year'])) > date('md')) {
                        $age--;
                    }

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

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

    /**
     * Preprocess raw data in the database into something we can efficiently draw graphs/conclusions from.
     * This is for flat and timeless data.
     *
     * @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
     */
    public function preprocess_raw_data_flat(int $start_time, int $end_time, array &$data_buckets)
    {
        if (addon_installed('points')) {
            require_code('points');
        }

        $max = 1000;
        $start = 0;

        $query = 'SELECT id,m_username,m_cache_num_posts FROM ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_members WHERE ';
        $query .= 'id<>' . strval($GLOBALS['FORUM_DRIVER']->get_guest_id());
        do {
            $rows = $GLOBALS['FORUM_DB']->query($query, $max, $start);
            foreach ($rows as $row) {
                $member_id = $row['id'];
                $username = $row['m_username'];

                $visits = $GLOBALS['SITE_DB']->query_select_value('stats', 'COUNT(DISTINCT session_id)', ['member_id' => $member_id]);
                if ($visits > 0) {
                    if (!isset($data_buckets['top_members_by_visits'][$username])) {
                        $data_buckets['top_members_by_visits'][$username] = 0;
                    }
                    $data_buckets['top_members_by_visits'][$username] += $visits;
                }

                if (addon_installed('cns_forum')) {
                    $posts = $row['m_cache_num_posts'];
                    if ($posts > 0) {
                        $data_buckets['top_members_by_forum_posts'][$username] = $posts;
                    }
                }

                if (addon_installed('points')) {
                    $points = points_rank($member_id);
                    if ($points > 100) { // Hard-coded minimum
                        $data_buckets['top_members_by_points'][$username] = $points;
                    }
                }
            }

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

    /**
     * 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
    {
        switch ($bucket) {
            case 'members':
                $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_joins) {
                            if ((!empty($filters[$bucket . '__country'])) && ($filters[$bucket . '__country'] != $country)) {
                                continue;
                            }

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

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

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

                $age_brackets = explode(',', $filters[$bucket . '__age_brackets']);

                $data = [];
                foreach ($age_brackets as $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 $age => $num_users) {
                        $bracket = $this->find_value_bracket($age_brackets, $age);
                        if ($bracket === null) {
                            if (!isset($data[do_lang('OTHER')])) {
                                $data[do_lang('OTHER')] = 0;
                            }
                            $data[do_lang('OTHER')] += $num_users;
                        } else {
                            $data[$bracket] += $num_users;
                        }
                    }
                }

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

                return [
                    'type' => self::GRAPH_BAR_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('AGE_RANGE'),
                    'y_axis_label' => do_lang_tempcode('COUNT_MEMBERS'),
                ];

            case 'top_members_by_visits':
            case 'top_members_by_forum_posts':
            case 'top_members_by_points':
                $_data = $GLOBALS['SITE_DB']->query_select_value_if_there('stats_preprocessed_flat', 'p_data', ['p_bucket' => $bucket]);
                if ($_data !== null) {
                    $data = @unserialize($_data);
                    if ($data === false) {
                        $data = [];
                    }
                } else {
                    $data = [];
                }

                switch ($bucket) {
                    case 'top_members_by_visits':
                        $y_axis_label = do_lang_tempcode('COUNT_TOTAL');
                        break;

                    case 'top_members_by_forum_posts':
                        $y_axis_label = do_lang_tempcode('COUNT_POSTS');
                        break;

                    case 'top_members_by_points':
                        $y_axis_label = do_lang_tempcode('POINTS');
                        break;

                    default:
                        $y_axis_label = new Tempcode();
                        fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
                }

                return [
                    'type' => self::GRAPH_BAR_CHART,
                    'data' => $data,
                    'x_axis_label' => do_lang_tempcode('USERNAME'),
                    'y_axis_label' => $y_axis_label,
                ];
        }

        fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
    }
}
