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

/*

Conversr is designed to run under a number of different situations, via the object orientated driver situation, and some driver variable switcher functions in import.php.
 - Standalone Conversr (FORUM_DB==SITE_DB, but not explicitly)
 - Installation (force standalone Conversr, regardless of whether it is on an MSN in normal use: FORUM_DB:=SITE_DB, for installation/upgrading of Conversr only)
 - Multi site network linking / Importing a non-forum (FORUM_DB!=SITE_DB)
 - Importing a forum (import.php functions used to to and from Conversr and normal-forum-driver [which might not be Conversr])

Exception: Welcome Mails are per-site, so run through SITE_DB. They have their own install code in the admin module though.

SITE_DB - The site database, always
FORUM_DB - The forum database. If not using Conversr, will not be the Conversr db. Switched around by installer/forum-importers according for need for locality/Conversrality.
FORUM_DRIVER - The forum driver. May not be Conversr, depending on which forum driver is used.
CNS_DRIVER - A forum driver to Conversr. This is always used in Conversr rather than FORUM_DRIVER, in case Conversr functions are being called when Conversr is not the primary forum driver running (when importing to Conversr but using a different forum driver, often when in the process of switching forums)

The Conversr functions should always call up the attachment and language systems using explicitly the forum database, so multi site networks function properly.

*/

/**
 * Standard code module initialisation function.
 *
 * @ignore
 */
function init__cns_install()
{
    global $CNS_TRUE_PERMISSIONS, $CNS_FALSE_PERMISSIONS;
    $CNS_TRUE_PERMISSIONS = [
        'run_multi_moderations',
        'use_pt',
        'edit_private_topic_posts',
        'may_unblind_own_poll',
        'view_member_photos',
        'use_quick_reply',
        'view_profiles',
        'own_avatars',
        'double_post',
        'delete_account',
    ];
    $CNS_FALSE_PERMISSIONS = [
        'rename_self',
        'use_special_emoticons',
        'view_any_profile_field',
        'disable_lost_passwords',
        'close_own_topics',
        'edit_own_polls',
        'see_warnings',
        'see_ip',
        'may_choose_custom_title',
        'view_other_pt',
        'view_poll_results_before_voting',
        'moderate_private_topic',
        'member_maintenance',
        'probate_members',
        'warn_members',
        'control_usergroups',
        'multi_delete_topics',
        'show_user_browsing',
        'see_hidden_groups',
        'pt_anyone',
        'delete_private_topic_posts',
    ];
}

/**
 * Uninstall Conversr.
 */
function uninstall_cns()
{
    cms_extend_time_limit(TIME_LIMIT_EXTEND__MODEST);

    global $CNS_TRUE_PERMISSIONS, $CNS_FALSE_PERMISSIONS;

    delete_privilege(array_merge($CNS_TRUE_PERMISSIONS, $CNS_FALSE_PERMISSIONS));

    $GLOBALS['FORUM_DB']->query_delete('group_category_access', ['module_the_name' => 'forums']);

    $values = [
        'cns_newest_member_id',
        'cns_newest_member_username',
        'cns_member_count',
        'cns_topic_count',
        'cns_post_count',
    ];
    delete_values($values);

    require_code('files');
    if (!$GLOBALS['DEV_MODE']) {
        deldir_contents(get_custom_file_base() . '/uploads/cns_avatars', true);
        deldir_contents(get_custom_file_base() . '/uploads/cns_photos', true);
    }

    delete_attachments('cns_post');
    delete_attachments('cns_signature');

    require_all_core_cms_code();

    $tables = [
        'f_emoticons',
        'f_custom_fields',
        'f_member_custom_fields',
        'f_groups',
        'f_forum_groupings',
        'f_forums',
        'f_forum_intro_ip',
        'f_forum_intro_member',
        'f_topics',
        'f_posts',
        'f_polls',
        'f_poll_answers',
        'f_poll_votes',
        'f_post_templates',
        'f_warnings',
        'f_moderator_logs',
        'f_member_known_login_ips',
        'f_members',
        'f_group_members',
        'f_group_approvals',
        'f_read_logs',
        'f_forum_tracking',
        'f_topic_tracking',
        'f_multi_moderations',
        'f_invites',
        'f_forum_group_access',
        'f_special_pt_access',
        'f_saved_warnings',
        'f_member_cpf_perms',
        'f_group_join_log',
        'f_password_history',
        'f_pposts_fulltext_index',
        'f_posts_fulltext_index',
    ];
    $GLOBALS['FORUM_DB']->drop_table_if_exists($tables);

    $GLOBALS['FORUM_DB']->query_delete('group_privileges', ['module_the_name' => 'forums']);

    $privileges = [
        'bypass_required_cpfs',
        'bypass_required_cpfs_if_already_empty',
        'bypass_email_address',
        'bypass_email_address_if_already_empty',
        'bypass_dob',
        'bypass_dob_if_already_empty',
        'appear_under_birthdays',

        'exceed_post_edit_time_limit',
        'exceed_post_delete_time_limit',
    ];
    delete_privilege($privileges);
}

/**
 * Install/upgrade Conversr.
 *
 * @param  ?float $upgrade_from The version to upgrade from (null: fresh install)
 */
function install_cns(?float $upgrade_from = null)
{
    cms_extend_time_limit(TIME_LIMIT_EXTEND__SLUGGISH);

    require_code('cns_members');
    require_code('cns_topics');
    require_code('cns_groups');
    require_code('cns_forums');
    require_lang('cns');
    require_lang('cns_polls');
    require_lang('cns_config');
    require_lang('cns_special_cpf');
    require_code('cns_moderation_action');
    require_code('cns_posts_action');
    require_code('cns_members_action');
    require_code('cns_groups_action');
    require_code('cns_general_action');
    require_code('cns_forums_action');
    require_code('cns_topics_action');

    require_all_core_cms_code();

    if ($upgrade_from === null) {
        uninstall_cns(); // Remove if already installed
    }

    // Upgrade code for making changes (<8 not supported) lots of LEGACY code below
    if (($upgrade_from === null) || ($upgrade_from < 10.0)) {
        $GLOBALS['FORUM_DB']->create_table('f_group_join_log', [
            'id' => '*AUTO',
            'member_id' => 'MEMBER',
            'usergroup_id' => '?GROUP',
            'join_time' => 'TIME',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_group_join_log', 'member_id', ['member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_group_join_log', 'join_time', ['join_time']);
        $GLOBALS['FORUM_DB']->create_index('f_group_join_log', 'usergroup_id', ['usergroup_id']);

        $GLOBALS['FORUM_DB']->create_table('f_password_history', [
            'id' => '*AUTO',
            'p_member_id' => 'MEMBER',
            'p_hash_salted' => 'SHORT_TEXT',
            'p_salt' => 'SHORT_TEXT',
            'p_time' => 'TIME',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_password_history', 'p_member_id', ['p_member_id']);
    }

    if (($upgrade_from !== null) && ($upgrade_from < 11.0)) { // LEGACY
        $GLOBALS['FORUM_DB']->delete_table_field('f_multi_moderations', 'mm_sink_state');
        $GLOBALS['FORUM_DB']->delete_table_field('f_topics', 't_sunk');

        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_topics', 'topic_order_2');
        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_topics', 'topic_order_3');
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'topic_order_forum', ['t_forum_id', 't_cascading', 't_pinned', 't_cache_last_time']);

        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'date_time'], ['cf_type' => 'date']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'date'], ['cf_type' => 'just_date']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'time'], ['cf_type' => 'just_time']);

        $GLOBALS['FORUM_DB']->add_table_field('f_custom_fields', 'cf_include_in_main_search', 'BINARY');
        $GLOBALS['FORUM_DB']->add_table_field('f_custom_fields', 'cf_allow_template_search', 'BINARY');

        $GLOBALS['FORUM_DB']->add_table_field('f_custom_fields', 'cf_icon', 'ID_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_custom_fields', 'cf_section', 'ID_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_custom_fields', 'cf_tempcode', 'LONG_TEXT');

        $GLOBALS['FORUM_DB']->add_table_field('f_custom_fields', 'cf_autofill_type', 'ID_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_custom_fields', 'cf_autofill_hint', 'ID_TEXT');

        $GLOBALS['FORUM_DB']->add_table_field('f_warnings', 'w_topic_id', '?AUTO_LINK');
        $GLOBALS['FORUM_DB']->add_table_field('f_moderator_logs', 'l_warning_id', '?AUTO_LINK');

        $GLOBALS['FORUM_DB']->add_table_field('f_groups', 'g_promotion_approval', 'BINARY');

        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_group_members', 'gm_validated');
        $GLOBALS['FORUM_DB']->delete_table_field('f_group_members', 'gm_validated');

        // Optionally provide autofill types for bundled CPFs (if any found)
        $autofill_map = [
            'cms_currency' => ['transaction-currency'],
            'cms_payment_cardholder_name' => ['cc-name'],
            'cms_payment_card_type' => ['cc-type'],
            'cms_payment_card_number' => ['cc-number'],
            'cms_payment_card_expiry_date' => ['cc-exp'],
            'cms_firstname' => ['given-name'],
            'cms_lastname' => ['family-name'],
            'cms_billing_street_address' => ['street-address', 'billing'],
            'cms_billing_city' => ['address-level2', 'billing'],
            'cms_billing_state' => ['address-level1', 'billing'],
            'cms_billing_post_code' => ['postal-code', 'billing'],
            'cms_billing_country' => ['country', 'billing'],
            'cms_street_address' => ['street-address'],
            'cms_city' => ['address-level2'],
            'cms_state' => ['address-level1'],
            'cms_post_code' => ['postal-code'],
            'cms_country' => ['country'],
        ];
        foreach ($autofill_map as $cf_name => $arr) {
            $field_id = $GLOBALS['SITE_DB']->query_select_value_if_there('f_custom_fields', 'id', [$GLOBALS['FORUM_DB']->translate_field_ref('cf_name') => $cf_name]);
            if ($field_id !== null) {
                $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_autofill_type' => $arr[0], 'cf_autofill_hint' => isset($arr[1]) ? $arr[1] : ''], ['id' => $field_id]);
            }
        }

        $GLOBALS['FORUM_DB']->add_table_field('f_member_known_login_ips', 'i_time', 'TIME');
    }

    if (($upgrade_from !== null) && ($upgrade_from < 10.0)) { // LEGACY
        delete_config_option('no_dob_ask');

        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_posts', 'posts_by');

        $GLOBALS['FORUM_DB']->add_table_field('f_members', 'm_profile_views', 'UINTEGER');
        $GLOBALS['FORUM_DB']->add_table_field('f_members', 'm_total_sessions', 'UINTEGER');

        $GLOBALS['FORUM_DB']->add_auto_key('f_poll_votes');
        $GLOBALS['FORUM_DB']->add_table_field('f_poll_votes', 'pv_ip_address', 'IP');

        $GLOBALS['FORUM_DB']->rename_table('f_categories', 'f_forum_groupings');
        $GLOBALS['FORUM_DB']->alter_table_field('f_forums', 'f_category_id', '?AUTO_LINK', 'f_forum_grouping_id');
        $privileges = ['moderate_personal_topic' => 'moderate_private_topic', 'edit_personal_topic_posts' => 'edit_private_topic_posts'];
        foreach ($privileges as $old => $new) {
            rename_privilege($old, $new);
        }
        $GLOBALS['FORUM_DB']->delete_table_field('f_members', 'm_notes');
        $GLOBALS['FORUM_DB']->delete_table_field('f_members', 'm_zone_wide');
        delete_config_option('skip_email_confirm_join');
        delete_config_option('prevent_shouting');

        // Initialise f_password_history with current data (we'll assume m_last_submit_time represents last password change, which is not true - but ok enough for early initialisation, and will scatter things quite nicely to break in the new rules gradually)
        $max = 500;
        $start = 0;
        do {
            $old_limit = cms_set_time_limit(30);

            $members = $GLOBALS['FORUM_DB']->query_select('f_members', ['id', 'm_pass_hash_salted', 'm_pass_salt', 'm_last_submit_time', 'm_join_time'], [], '', $max, $start);
            foreach ($members as $member) {
                if ($member['id'] != $GLOBALS['FORUM_DRIVER']->get_guest_id()) {
                    $GLOBALS['FORUM_DB']->query_insert('f_password_history', [
                        'p_member_id' => $member['id'],
                        'p_hash_salted' => $member['m_pass_hash_salted'],
                        'p_salt' => $member['m_pass_salt'],
                        'p_time' => ($member['m_last_submit_time'] === null) ? $member['m_join_time'] : $member['m_last_submit_time'],
                    ]);
                }
            }
            $start += $max;

            cms_set_time_limit($old_limit);
        } while (!empty($members));

        $GLOBALS['FORUM_DB']->add_table_field('f_custom_fields', 'cf_options', 'SHORT_TEXT');

        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'member'], ['cf_type' => 'user']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'member_multi'], ['cf_type' => 'user_multi']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'codename', 'cf_options' => 'default=RANDOM'], ['cf_type' => 'random']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'list_multi', 'cf_options' => 'widget=vertical_checkboxes,custom_values=yes'], ['cf_type' => 'combo_multi']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'list_multi'], ['cf_type' => 'multilist']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'list_multi', 'cf_options' => 'widget=horizontal_checkboxes'], ['cf_type' => 'tick_multi']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'list', 'cf_options' => 'widget=radio,custom_values=yes'], ['cf_type' => 'combo']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'list', 'cf_options' => 'widget=radio'], ['cf_type' => 'radiolist']);
        $GLOBALS['FORUM_DB']->query_update('f_custom_fields', ['cf_type' => 'integer', 'cf_options' => 'default=AUTO_INCREMENT'], ['cf_type' => 'auto_increment']);

        $is_on_anonymous_posts = get_option('is_on_anonymous_posts', true);
        if ($is_on_anonymous_posts === null) {
            $is_on_anonymous_posts = '0';
        }
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_allows_anonymous_posts', 'BINARY', intval($is_on_anonymous_posts));

        $GLOBALS['FORUM_DB']->add_table_field('f_members', 'm_auto_mark_read', 'BINARY', 1);

        $GLOBALS['SITE_DB']->query('DELETE FROM ' . get_table_prefix() . 'db_meta_indices WHERE i_name LIKE \'#mcf%\'');

        $GLOBALS['FORUM_DB']->drop_table_if_exists('f_post_history');

        rename_config_option('post_history_days', 'post_read_history_days');

        // Directory moving
        require_code('upgrade_lib');
        $fields = [
            'm_photo_url' => 'uploads/ocf_photos',
            'm_avatar_url' => 'uploads/ocf_avatars',
        ];
        foreach ($fields as $field => $dir) {
            $GLOBALS['FORUM_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_members SET ' . $field . '=REPLACE(' . $field . ',\'ocf_\',\'cns_\') WHERE ' . $field . ' LIKE \'%ocf\_%\'');
            move_folder_contents($dir, str_replace('ocf_', 'cns_', $dir));
        }
        move_folder_contents('uploads/ocf_cpf_upload', 'uploads/cns_cpf_upload');

        $GLOBALS['FORUM_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_emoticons SET e_theme_img_code=REPLACE(e_theme_img_code,\'ocf_\',\'cns_\') WHERE e_theme_img_code LIKE \'%ocf\_%\'');

        $GLOBALS['FORUM_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_groups SET g_rank_image=REPLACE(g_rank_image,\'ocf_\',\'cns_\') WHERE g_rank_image LIKE \'%ocf\_%\'');

        $GLOBALS['FORUM_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_topics SET t_emoticon=REPLACE(t_emoticon,\'ocf_\',\'cns_\') WHERE t_emoticon LIKE \'%ocf\_%\'');
        $GLOBALS['FORUM_DB']->query_update('f_topics', ['t_emoticon' => 'icons/14x14/cns_topic_modifiers/announcement'], ['t_emoticon' => 'cns_topic_modifiers/announcement']);

        $GLOBALS['FORUM_DB']->change_primary_key('f_multi_moderations', ['id']);

        $GLOBALS['SITE_DB']->query_update('privilege_list', ['the_default' => 1], ['the_name' => 'double_post']);
        $GLOBALS['SITE_DB']->query_update('privilege_list', ['the_default' => 1], ['the_name' => 'delete_account']);

        add_privilege('FORUMS_AND_MEMBERS', 'delete_private_topic_posts', false, false);

        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_topics', 'unread_forums');
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'unread_forums', ['t_forum_id', 't_cache_last_time']);

        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_posts', 'posts_since');
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'posts_since', ['p_time', 'p_cache_forum_id']); // p_cache_forum_id is used to not count PT posts

        // Fix up legacy issues with CPFs that we can no longer tolerate
        $fields = $GLOBALS['FORUM_DB']->query_select('f_custom_fields', ['id', 'cf_type']);
        require_code('cns_members_action');
        foreach ($fields as $field) {
            $id = $field['id'];
            $type = $field['cf_type'];
            list($_type) = get_cpf_storage_for($type);

            $id = $field['id'];

            $GLOBALS['FORUM_DB']->delete_index_if_exists('f_member_custom_fields', 'mcf' . strval($id));
            $GLOBALS['FORUM_DB']->delete_index_if_exists('f_member_custom_fields', '#mcf_ft_' . strval($id));

            if (strpos(get_db_type(), 'mysql') !== false) {
                $GLOBALS['FORUM_DB']->query('SET sql_mode=\'\'', null, 0, true); // Turn off strict mode, suppress errors in case access denied
            }
            $GLOBALS['FORUM_DB']->alter_table_field('f_member_custom_fields', 'field_' . strval($id), $_type);
        }

        require_code('cns_members_action2');
        rebuild_all_cpf_indices();
    }
    if (($upgrade_from !== null) && ($upgrade_from < 11.0)) { // LEGACY
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_email_address', 'SHORT_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_server_type', 'ID_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_server_host', 'SHORT_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_server_port', '?INTEGER');
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_folder', 'SHORT_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_username', 'SHORT_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_password', 'SHORT_TEXT');
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_nonmatch_policy', 'ID_TEXT', 'post_as_guest');
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_mail_unconfirmed_notice', 'BINARY', 1);
        $GLOBALS['FORUM_DB']->add_table_field('f_forums', 'f_poll_default_options_xml', 'LONG_TEXT');

        $GLOBALS['FORUM_DB']->add_table_field('f_members', 'm_smart_topic_notification', 'BINARY', 0);
        $GLOBALS['FORUM_DB']->add_table_field('f_members', 'm_mailing_list_style', 'BINARY', 1);
        $GLOBALS['FORUM_DB']->add_table_field('f_members', 'm_sound_enabled', 'BINARY', 0);
        $GLOBALS['FORUM_DB']->add_table_field('f_members', 'm_password_change_code_time', '?TIME');
        $GLOBALS['FORUM_DB']->add_table_field('f_members', 'm_login_key_hash', 'SHORT_TEXT');

        $GLOBALS['FORUM_DB']->alter_table_field('f_members', 'm_is_perm_banned', 'ID_TEXT');

        $GLOBALS['FORUM_DB']->add_table_field('f_polls', 'po_closing_time', '?TIME');
        $GLOBALS['FORUM_DB']->add_table_field('f_polls', 'po_view_member_votes', 'BINARY');
        $GLOBALS['FORUM_DB']->add_table_field('f_polls', 'po_vote_revocation', 'BINARY');
        $GLOBALS['FORUM_DB']->add_table_field('f_polls', 'po_guests_can_vote', 'BINARY');
        $GLOBALS['FORUM_DB']->add_table_field('f_polls', 'po_point_weighting', 'BINARY');
        $GLOBALS['FORUM_DB']->add_table_field('f_polls', 'po_cache_voting_power', '?REAL');

        $GLOBALS['FORUM_DB']->add_table_field('f_poll_votes', 'pv_date_time', 'TIME');
        $GLOBALS['FORUM_DB']->add_table_field('f_poll_votes', 'pv_revoked', 'BINARY');
        $GLOBALS['FORUM_DB']->add_table_field('f_poll_votes', 'pv_points_when_voted', 'INTEGER');
        $GLOBALS['FORUM_DB']->add_table_field('f_poll_votes', 'pv_cache_voting_power', '?REAL');

        $GLOBALS['FORUM_DB']->alter_table_field('f_poll_votes', 'pv_answer_id', '?AUTO_LINK');
        $GLOBALS['FORUM_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_poll_votes SET pv_answer_id=NULL WHERE pv_answer_id=-1');

        $GLOBALS['FORUM_DB']->add_table_field('f_poll_answers', 'pa_cache_voting_power', '?REAL');
        $GLOBALS['FORUM_DB']->add_table_field('f_poll_answers', 'pa_order', 'INTEGER');
        $GLOBALS['FORUM_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_poll_answers SET pa_order=id');

        $GLOBALS['FORUM_DB']->create_index('f_poll_answers', 'pa_poll_id', ['pa_poll_id']);

        $GLOBALS['FORUM_DB']->add_table_field('f_warnings', 'p_changed_usergroup_to', '?GROUP');

        $max = 100;
        $start = 0;
        do {
            $rows = $GLOBALS['FORUM_DB']->query_select('f_members', ['id', 'm_photo_thumb_url'], [], ' AND m_photo_thumb_url LIKE \'' . db_encode_like('uploads/cns_photos_thumbs/%') . '\' ORDER BY id', $max, $start);
            foreach ($rows as $row) {
                @unlink(get_custom_file_base() . '/' . urldecode($row['m_photo_thumb_url']));
            }
            $start += $max;
        } while (!empty($rows));
        $GLOBALS['FORUM_DB']->delete_table_field('f_members', 'm_photo_thumb_url');

        // Changes to custom fields
        if (multi_lang_content()) {
            $GLOBALS['SITE_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_custom_fields f JOIN ' . get_table_prefix() . 'translate t ON t.id=f.cf_name SET text_original=\'cms_payment_card_type\' WHERE ' . db_string_equal_to('text_original', 'cms_payment_type'));
            $GLOBALS['SITE_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_custom_fields f JOIN ' . get_table_prefix() . 'translate t ON t.id=f.cf_name SET cf_type=\'year_month\' WHERE ' . db_string_equal_to('text_original', 'cms_payment_card_start_date') . ' OR ' . db_string_equal_to('text_original', 'cms_payment_card_expiry_date'));
        } else {
            $GLOBALS['SITE_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_custom_fields SET cf_name=\'cms_payment_card_type\' WHERE ' . db_string_equal_to('cf_name', 'cms_payment_type'));
            $GLOBALS['SITE_DB']->query('UPDATE ' . $GLOBALS['FORUM_DB']->get_table_prefix() . 'f_custom_fields SET cf_type=\'year_month\' WHERE ' . db_string_equal_to('cf_name', 'cms_payment_card_start_date') . ' OR ' . db_string_equal_to('cf_name', 'cms_payment_card_expiry_date'));
        }
    }

    // If we have the forum installed to this db already, leave
    if ($upgrade_from === null) {
        $GLOBALS['FORUM_DB']->create_table('f_member_cpf_perms', [
            'member_id' => '*MEMBER',
            'field_id' => '*AUTO_LINK',
            'guest_view' => 'BINARY',
            'member_view' => 'BINARY',
            'friend_view' => 'BINARY',
            'group_view' => 'SHORT_TEXT',
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_emoticons', [
            'e_code' => '*ID_TEXT',
            'e_theme_img_code' => 'SHORT_TEXT',
            'e_relevance_level' => 'INTEGER', // 0=core,1=supported,2=unsupported,3=poor,4=unused
            'e_use_topics' => 'BINARY', // Whether to use it to show a topics emotion
            'e_is_special' => 'BINARY',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_emoticons', 'relevantemoticons', ['e_relevance_level']);
        $GLOBALS['FORUM_DB']->create_index('f_emoticons', 'topicemos', ['e_use_topics']);

        $GLOBALS['FORUM_DB']->create_table('f_custom_fields', [
            'id' => '*AUTO',
            'cf_locked' => 'BINARY',  // Can't be deleted
            'cf_name' => 'SHORT_TRANS',
            'cf_description' => 'LONG_TRANS',
            'cf_default' => 'LONG_TEXT',
            'cf_public_view' => 'BINARY',
            'cf_owner_view' => 'BINARY',
            'cf_owner_set' => 'BINARY',
            'cf_type' => 'ID_TEXT', // can be short_text,long_text,long_trans,integer,url,upload,picture,list,tick
            'cf_required' => 'BINARY',
            'cf_show_in_posts' => 'BINARY',
            'cf_show_in_post_previews' => 'BINARY',
            'cf_order' => 'INTEGER',
            'cf_only_group' => 'LONG_TEXT',
            'cf_encrypted' => 'BINARY',
            'cf_show_on_join_form' => 'BINARY',
            'cf_options' => 'SHORT_TEXT',
            'cf_include_in_main_search' => 'BINARY',
            'cf_allow_template_search' => 'BINARY',
            'cf_icon' => 'ID_TEXT',
            'cf_section' => 'ID_TEXT',
            'cf_tempcode' => 'LONG_TEXT',
            'cf_autofill_type' => 'ID_TEXT',
            'cf_autofill_hint' => 'ID_TEXT',
        ]);

        // These don't need to be filled in. We just use default from custom field if they aren't
        $GLOBALS['FORUM_DB']->create_table('f_member_custom_fields', [
            'mf_member_id' => '*MEMBER',
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_invites', [
            'id' => '*AUTO',
            'i_invite_member' => 'MEMBER',
            'i_email_address' => 'SHORT_TEXT',
            'i_time' => 'TIME',
            'i_taken' => 'BINARY',
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_group_members', [
            'gm_group_id' => '*GROUP',
            'gm_member_id' => '*MEMBER',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_group_members', 'gm_member_id', ['gm_member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_group_members', 'gm_group_id', ['gm_group_id']);

        $GLOBALS['FORUM_DB']->create_table('f_members', [
            // Basic details
            'id' => '*AUTO',
            'm_username' => 'ID_TEXT',
            'm_email_address' => 'SHORT_TEXT',
            'm_primary_group' => 'GROUP',
            'm_dob_day' => '?SHORT_INTEGER',
            'm_dob_month' => '?SHORT_INTEGER',
            'm_dob_year' => '?INTEGER',

            // Login security
            'm_pass_hash_salted' => 'SHORT_TEXT',
            'm_pass_salt' => 'SHORT_TEXT',
            'm_password_change_code' => 'SHORT_TEXT',
            'm_password_change_code_time' => '?TIME',
            'm_password_compat_scheme' => 'ID_TEXT',
            'm_login_key_hash' => 'SHORT_TEXT',

            // Selections
            'm_timezone_offset' => 'SHORT_TEXT',
            'm_language' => 'ID_TEXT',
            'm_theme' => 'ID_TEXT', // Blank means default

            // Rich data
            'm_title' => 'SHORT_TEXT', // Blank means use title
            'm_photo_url' => 'URLPATH', // Blank means no photo
            'm_avatar_url' => 'URLPATH', // Blank means no avatar
            'm_signature' => 'LONG_TRANS__COMCODE',

            // Settings
            'm_preview_posts' => 'BINARY',
            'm_reveal_age' => 'BINARY',
            'm_views_signatures' => 'BINARY',
            'm_auto_monitor_contrib_content' => 'BINARY',
            'm_smart_topic_notification' => 'BINARY',
            'm_mailing_list_style' => 'BINARY',
            'm_auto_mark_read' => 'BINARY',
            'm_sound_enabled' => 'BINARY',
            'm_allow_emails' => 'BINARY',
            'm_allow_emails_from_staff' => 'BINARY',
            'm_highlighted_name' => 'BINARY',
            'm_pt_allow' => 'SHORT_TEXT',
            'm_pt_rules_text' => 'LONG_TRANS__COMCODE',

            // Security
            'm_validated' => 'BINARY',
            'm_validated_email_confirm_code' => 'SHORT_TEXT',
            'm_probation_expiration_time' => '?TIME',
            'm_is_perm_banned' => 'ID_TEXT',

            // Auto-generated values
            'm_ip_address' => 'IP',
            'm_join_time' => 'TIME',
            'm_last_visit_time' => 'TIME', // This field is generally kept up-to-date, while the cookie 'last_visit' refers to the previous browsing session's time
            'm_last_submit_time' => 'TIME',
            'm_profile_views' => 'UINTEGER',
            'm_total_sessions' => 'UINTEGER',

            // Cached values
            'm_cache_num_posts' => 'INTEGER',
            'm_cache_warnings' => 'INTEGER',

            // No editing interface for the below, but may be edited in Commandr
            'm_max_email_attach_size_mb' => 'INTEGER',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_members', '#search_user', ['m_username']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'user_list', ['m_username']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'menail', ['m_email_address']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'external_auth_lookup', ['m_pass_hash_salted']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'sort_post_count', ['m_cache_num_posts']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'm_join_time', ['m_join_time']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'whos_validated', ['m_validated']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'birthdays', ['m_dob_day', 'm_dob_month']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'ftjoin_msig', ['m_signature']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'primary_group', ['m_primary_group']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'avatar_url', ['m_avatar_url']); // Used for uniform avatar randomisation

        $core_emoticons = [
            ':P' => ['cheeky', false],
            ":'(" => ['cry', false],
            ':(' => ['sad', false],
            ':)' => ['smile', true],
            ':thumbs:' => ['thumbs', false],
            ':o' => ['shocked', false],
            ';)' => ['wink', true],
        ];
        foreach ($core_emoticons as $type_code => $_) {
            list($theme_image_code, $no_use_topics) = $_;
            cns_make_emoticon($type_code, 'cns_emoticons/' . $theme_image_code, 0, $no_use_topics ? 0 : 1);
        }

        require_code('content2');
        $content = ['have_default_full_emoticon_set' => true, 'about' => true, 'interests' => true, 'occupation' => true, 'staff_notes' => true];
        if (!addon_installed('user_mappr')) {
            $content['location'] = true;
        }
        install_predefined_content('core_cns', $content);

        $GLOBALS['FORUM_DB']->create_table('f_groups', [
            'id' => '*AUTO',
            'g_name' => 'SHORT_TRANS',
            'g_is_default' => 'BINARY',
            'g_is_presented_at_install' => 'BINARY',
            'g_is_super_admin' => 'BINARY',
            'g_is_super_moderator' => 'BINARY',
            'g_group_lead_member' => '?MEMBER',
            'g_title' => 'SHORT_TRANS',
            'g_promotion_target_group' => '?GROUP',
            'g_promotion_threshold' => '?INTEGER',
            'g_promotion_approval' => 'BINARY',
            'g_flood_control_submit_secs' => 'INTEGER',
            'g_flood_control_access_secs' => 'INTEGER',
            'g_gift_points_base' => 'INTEGER',
            'g_gift_points_per_day' => 'INTEGER',
            'g_max_daily_upload_mb' => 'INTEGER',
            'g_max_attachments_per_post' => 'INTEGER',
            'g_max_avatar_width' => 'INTEGER',
            'g_max_avatar_height' => 'INTEGER',
            'g_max_post_length_comcode' => 'INTEGER',
            'g_max_sig_length_comcode' => 'INTEGER',
            'g_enquire_on_new_ips' => 'BINARY',
            'g_rank_image' => 'ID_TEXT',
            'g_hidden' => 'BINARY',
            'g_order' => 'INTEGER',
            'g_rank_image_pri_only' => 'BINARY',
            'g_open_membership' => 'BINARY',
            'g_is_private_club' => 'BINARY',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'ftjoin_gname', ['g_name']);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'ftjoin_gtitle', ['g_title']);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'is_private_club', ['g_is_private_club']);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'is_super_admin', ['g_is_super_admin']);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'is_super_moderator', ['g_is_super_moderator']);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'is_default', ['g_is_default']);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'hidden', ['g_hidden']);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'is_presented_at_install', ['g_is_presented_at_install']);
        $GLOBALS['FORUM_DB']->create_index('f_groups', 'gorder', ['g_order', 'id']);

        // For the_zone_access table
        require_code('zones2');
        reinstall_module('adminzone', 'admin_permissions');

        require_lang('cns_ranks');
        // Make guest
        $guest_group = cns_make_group(do_lang('GUESTS'), 0, 0, 0, do_lang('DESCRIPTION_GUESTS'));
        // Make administrators
        $administrator_group = cns_make_group(do_lang('ADMINISTRATORS'), 0, 1, 0, do_lang('DESCRIPTION_ADMINISTRATORS'), 'cns_rank_images/admin', null, null, 0, null, 0);
        // Make super-moderators
        $super_moderator_group = cns_make_group(do_lang('SUPER_MODERATORS'), 0, 0, 1, do_lang('DESCRIPTION_SUPER_MODERATORS'), 'cns_rank_images/mod', null, null, 0, null, 0);
        // Make member
        list($member_group_0, $member_group_1, $member_group_2, $member_group_3, $member_group_4) = cns_make_rank_set('fun');
        // Make probation
        $probation_group = cns_make_group(do_lang('PROBATION'), 0, 0, 0, do_lang('DESCRIPTION_PROBATION'), '', null, null, 0, null, 0);

        $GLOBALS['FORUM_DB']->create_table('f_forum_groupings', [
            'id' => '*AUTO',
            'c_title' => 'SHORT_TEXT',
            'c_description' => 'LONG_TEXT',
            'c_expanded_by_default' => 'BINARY',
        ]);
        $forum_grouping_id = cns_make_forum_grouping(do_lang('DEFAULT_GROUPING_TITLE'), '');
        $forum_grouping_id_staff = cns_make_forum_grouping(do_lang('STAFF'), '');

        $GLOBALS['FORUM_DB']->create_table('f_forums', [
            'id' => '*AUTO',
            'f_name' => 'SHORT_TEXT',
            'f_description' => 'LONG_TRANS__COMCODE',
            'f_forum_grouping_id' => '?AUTO_LINK', // Categories can exist on multiple forum levels and positions - wherever a forum exists, the forum grouping it uses exists too (but not forums in the forum grouping which aren't at level and position)
            'f_parent_forum_id' => '?AUTO_LINK',
            'f_position' => 'INTEGER', // might have been called 'f_order' => 'INTEGER' (consistent with other table's ordering fields) if we had not used f_order as a text field to determine the automatic ordering type
            'f_order_sub_alpha' => 'BINARY',
            'f_post_count_increment' => 'BINARY',
            'f_intro_question' => 'LONG_TRANS__COMCODE',
            'f_intro_answer' => 'SHORT_TEXT',    // Comcode
            'f_cache_num_topics' => 'INTEGER',
            'f_cache_num_posts' => 'INTEGER',
            'f_cache_last_topic_id' => '?AUTO_LINK',
            'f_cache_last_title' => 'SHORT_TEXT',
            'f_cache_last_time' => '?TIME',
            'f_cache_last_username' => 'ID_TEXT',
            'f_cache_last_member_id' => '?MEMBER',
            'f_cache_last_forum_id' => '?AUTO_LINK',
            'f_redirection' => 'SHORT_TEXT',
            'f_order' => 'ID_TEXT',
            'f_is_threaded' => 'BINARY',
            'f_allows_anonymous_posts' => 'BINARY',
            'f_mail_email_address' => 'SHORT_TEXT',
            'f_mail_server_type' => 'ID_TEXT',
            'f_mail_server_host' => 'SHORT_TEXT',
            'f_mail_server_port' => '?INTEGER',
            'f_mail_folder' => 'SHORT_TEXT',
            'f_mail_username' => 'SHORT_TEXT',
            'f_mail_password' => 'SHORT_TEXT',
            'f_mail_nonmatch_policy' => 'ID_TEXT',
            'f_mail_unconfirmed_notice' => 'BINARY',
            'f_poll_default_options_xml' => 'LONG_TEXT',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_forums', 'cache_num_posts', ['f_cache_num_posts']); // Used to find active forums
        $GLOBALS['FORUM_DB']->create_index('f_forums', 'subforum_parenting', ['f_parent_forum_id']);
        $GLOBALS['FORUM_DB']->create_index('f_forums', 'findnamedforum', ['f_name']);
        $GLOBALS['FORUM_DB']->create_index('f_forums', 'f_position', ['f_position']);
        $typical_access = [$guest_group => 4, $administrator_group => 5, $super_moderator_group => 5, $probation_group => 2, $member_group_0 => 4, $member_group_1 => 4, $member_group_2 => 4, $member_group_3 => 4, $member_group_4 => 4];
        $staff_post_access = [$guest_group => 1, $administrator_group => 5, $super_moderator_group => 5, $probation_group => 1, $member_group_0 => 1, $member_group_1 => 1, $member_group_2 => 1, $member_group_3 => 1, $member_group_4 => 1];
        $staff_access = [$administrator_group => 5, $super_moderator_group => 5];
        $root_forum = cns_make_forum(do_lang('ROOT_FORUM'), '', null, $staff_post_access, null);
        cns_make_forum(do_lang('DEFAULT_FORUM_TITLE'), '', $forum_grouping_id, $typical_access, $root_forum);
        $trash_forum_id = cns_make_forum(do_lang('TRASH'), '', $forum_grouping_id_staff, $staff_access, $root_forum);
        cns_make_forum(do_lang('COMMENT_FORUM_NAME'), '', $forum_grouping_id, $typical_access, $root_forum, 1, 1, 0, '', '', '', 'last_post', 1);
        $staff_forum_id = cns_make_forum(do_lang('STAFF'), '', $forum_grouping_id_staff, $staff_access, $root_forum);

        $GLOBALS['FORUM_DB']->create_table('f_topics', [
            'id' => '*AUTO',
            't_pinned' => 'BINARY',
            't_cascading' => 'BINARY', // Cascades to deeper forums, as an announcement
            't_forum_id' => '?AUTO_LINK', // Null if it's a Private Topic
            't_pt_from_member' => '?MEMBER',
            't_pt_to_member' => '?MEMBER',
            't_pt_from_category' => 'SHORT_TEXT',
            't_pt_to_category' => 'SHORT_TEXT',
            't_description' => 'SHORT_TEXT',
            't_description_link' => 'SHORT_TEXT',
            't_emoticon' => 'SHORT_TEXT',
            't_num_views' => 'INTEGER',
            't_validated' => 'BINARY',
            't_is_open' => 'BINARY',
            't_poll_id' => '?AUTO_LINK',
            't_cache_first_post_id' => '?AUTO_LINK',
            't_cache_first_time' => '?TIME',
            't_cache_first_title' => 'SHORT_TEXT',
            't_cache_first_post' => '?LONG_TRANS__COMCODE', // Careful, can't use this if !multi_lang_content()
            't_cache_first_username' => 'ID_TEXT',
            't_cache_first_member_id' => '?MEMBER',
            't_cache_last_post_id' => '?AUTO_LINK',
            't_cache_last_time' => '?TIME',
            't_cache_last_title' => 'SHORT_TEXT',
            't_cache_last_username' => 'ID_TEXT',
            't_cache_last_member_id' => '?MEMBER',
            't_cache_num_posts' => 'INTEGER',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_num_views', ['t_num_views']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_validated', ['t_validated']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'in_forum', ['t_forum_id']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'topic_order_time', ['t_cache_last_time']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'topic_order_time_2', ['t_cache_first_time']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', '#t_description', ['t_description']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'descriptionsearch', ['t_description']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'forumlayer', ['t_cache_first_title']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_cascading', ['t_cascading']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_cascading_or_forum', ['t_cascading', 't_forum_id']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'topic_order', ['t_cascading', 't_pinned', 't_cache_last_time']); // Ordering for forumview, is picked up over topic_order_3 for just the ordering bit (it seems)
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'topic_order_forum', ['t_forum_id', 't_cascading', 't_pinned', 't_cache_last_time']); // Total index for forumview, including ordering
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'ownedtopics', ['t_cache_first_member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 'unread_forums', ['t_forum_id', 't_cache_last_time']);

        // Welcome topic
        $topic_id = cns_make_topic($staff_forum_id, '', '', 1, 1, 0, 0, null, null, false);

        $GLOBALS['FORUM_DB']->create_table('f_posts', [
            'id' => '*AUTO',
            'p_title' => 'SHORT_TEXT',
            'p_post' => 'LONG_TRANS__COMCODE',
            'p_time' => 'TIME',
            'p_ip_address' => 'IP',
            'p_posting_member' => 'MEMBER',
            'p_whisper_to_member' => '?MEMBER',
            'p_poster_name_if_guest' => 'ID_TEXT',
            'p_validated' => 'BINARY',
            'p_topic_id' => 'AUTO_LINK',
            'p_cache_forum_id' => '?AUTO_LINK', // Null if for a PT
            'p_last_edit_time' => '?TIME',
            'p_last_edit_member' => '?MEMBER',
            'p_is_emphasised' => 'BINARY',
            'p_skip_sig' => 'BINARY',
            'p_parent_id' => '?AUTO_LINK',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'p_validated', ['p_validated']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'in_topic', ['p_topic_id', 'p_time', 'id']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'post_order_time', ['p_time', 'id']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'posts_since', ['p_time', 'p_cache_forum_id']); // p_cache_forum_id is used to not count PT posts
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'p_last_edit_time', ['p_last_edit_time']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'find_pp', ['p_whisper_to_member']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'search_join', ['p_post']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'postsinforum', ['p_cache_forum_id']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'deletebyip', ['p_ip_address']);

        $GLOBALS['FORUM_DB']->create_table('f_special_pt_access', [
            's_member_id' => '*MEMBER',
            's_topic_id' => '*AUTO_LINK',
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_saved_warnings', [
            's_title' => '*SHORT_TEXT',
            's_explanation' => 'LONG_TEXT',
            's_message' => 'LONG_TEXT',
        ], false, false, true);

        $GLOBALS['FORUM_DB']->create_table('f_forum_intro_ip', [
            'i_forum_id' => '*AUTO_LINK',
            'i_ip_address' => '*IP',
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_forum_intro_member', [
            'i_forum_id' => '*AUTO_LINK',
            'i_member_id' => '*MEMBER',
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_post_templates', [
            'id' => '*AUTO',
            't_title' => 'SHORT_TEXT',
            't_text' => 'LONG_TEXT',
            't_forum_multi_code' => 'SHORT_TEXT',
            't_use_default_forums' => 'BINARY',
        ]);
        if (addon_installed('cns_post_templates')) {
            require_lang('cns_post_templates');
            cns_make_post_template(do_lang('DEFAULT_POST_TEMPLATE_bug_title'), do_lang('DEFAULT_POST_TEMPLATE_bug_text'), '', 0);
            cns_make_post_template(do_lang('DEFAULT_POST_TEMPLATE_task_title'), do_lang('DEFAULT_POST_TEMPLATE_task_text'), '', 0);
            cns_make_post_template(do_lang('DEFAULT_POST_TEMPLATE_fault_title'), do_lang('DEFAULT_POST_TEMPLATE_fault_text'), '', 0);
        }

        $GLOBALS['FORUM_DB']->create_index('f_posts', '#p_title', ['p_title']);

        $GLOBALS['FORUM_DB']->create_table('f_polls', [
            'id' => '*AUTO',
            'po_question' => 'SHORT_TEXT',
            'po_cache_total_votes' => 'INTEGER',
            'po_is_private' => 'BINARY',
            'po_is_open' => 'BINARY',
            'po_minimum_selections' => 'INTEGER',
            'po_maximum_selections' => 'INTEGER',
            'po_requires_reply' => 'BINARY',
            'po_closing_time' => '?TIME',
            'po_view_member_votes' => 'BINARY',
            'po_vote_revocation' => 'BINARY',
            'po_guests_can_vote' => 'BINARY',
            'po_point_weighting' => 'BINARY',
            'po_cache_voting_power' => '?REAL'
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_poll_answers', [
            'id' => '*AUTO',
            'pa_poll_id' => 'AUTO_LINK',
            'pa_answer' => 'SHORT_TEXT',
            'pa_cache_num_votes' => 'INTEGER',
            'pa_order' => 'INTEGER',
            'pa_cache_voting_power' => '?REAL'
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_poll_votes', [
            'id' => '*AUTO',
            'pv_poll_id' => 'AUTO_LINK',
            'pv_member_id' => 'MEMBER',
            'pv_answer_id' => '?AUTO_LINK', // null means forfeited vote
            'pv_ip_address' => 'IP',
            'pv_date_time' => 'TIME',
            'pv_revoked' => 'BINARY', // True means the member revoked this vote
            'pv_points_when_voted' => 'INTEGER',
            'pv_cache_voting_power' => '?REAL'
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_multi_moderations', [
            'id' => '*AUTO',
            'mm_name' => 'SHORT_TRANS',
            'mm_post_text' => 'LONG_TEXT',    // Comcode
            'mm_move_to_forum_id' => '?AUTO_LINK',
            'mm_pin_state' => '?BINARY',
            'mm_open_state' => '?BINARY',
            'mm_forum_multi_code' => 'SHORT_TEXT',
            'mm_title_suffix' => 'SHORT_TEXT',
        ]);
        cns_make_multi_moderation(do_lang('TRASH_VERB'), '', $trash_forum_id, 0, 0);

        $GLOBALS['FORUM_DB']->create_table('f_warnings', [
            'id' => '*AUTO',
            'w_member_id' => 'MEMBER',
            'w_time' => 'TIME',
            'w_explanation' => 'LONG_TEXT',
            'w_issuing_member' => 'MEMBER',
            'w_is_warning' => 'BINARY',
            'w_topic_id' => '?AUTO_LINK',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_warnings', 'warningsmemberid', ['w_member_id']);

        $GLOBALS['FORUM_DB']->create_table('f_moderator_logs', [
            'id' => '*AUTO',
            'l_the_type' => 'ID_TEXT', // Language string codename
            'l_param_a' => 'SHORT_TEXT',
            'l_param_b' => 'SHORT_TEXT',
            'l_date_and_time' => 'TIME',
            'l_reason' => 'LONG_TEXT',
            'l_by_member' => 'MEMBER',
            'l_warning_id' => '?AUTO_LINK'
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_member_known_login_ips', [
            'i_member_id' => '*MEMBER',
            'i_ip_address' => '*IP',
            'i_val_code' => 'SHORT_TEXT',
            'i_time' => 'TIME',
        ]);

        // NB: post_param_string's will return default's if Conversr is being installed but not used yet (e.g. IPB forum driver chosen at installation)
        // Make guest
        cns_make_member(
            do_lang('GUEST'), // username
            '', // password
            '', // email_address
            $guest_group, // primary_group
            null, // secondary_groups
            null, // dob_day
            null, // dob_month
            null, // dob_year
            [], // custom_fields
            null, // timezone
            '', // language
            '', // theme
            '', // title
            '', // photo_url
            null, // avatar_url
            '', // signature
            null, // preview_posts
            1, // reveal_age
            1, // views_signatures
            null, // auto_monitor_contrib_content
            null, // smart_topic_notification
            null, // mailing_list_style
            1, // auto_mark_read
            null, // sound_enabled
            1, // allow_emails
            1, // allow_emails_from_staff
            0, // highlighted_name
            '*', // pt_allow
            '', // pt_rules_text
            1, // validated
            '', // validated_email_confirm_code
            null, // probation_expiration_time
            '0', // is_perm_banned
            false // check_correctness
        );
        // Make admin user
        cns_make_member(
            post_param_string('admin_username', 'admin', INPUT_FILTER_POST_IDENTIFIER), // username
            post_param_string('cns_admin_password', 'admin', INPUT_FILTER_PASSWORD), // password
            post_param_string('email', '', INPUT_FILTER_POST_IDENTIFIER | INPUT_FILTER_EMAIL_ADDRESS), // email_address
            $administrator_group, // primary_group
            null, // secondary_groups
            null, // dob_day
            null, // dob_month
            null, // dob_year
            [], // custom_fields
            null, // timezone
            '', // language
            '', // theme
            '', // title
            '', // photo_url
            'themes/default/images/cns_default_avatars/default.png', // avatar_url
            '', // signature
            null, // preview_posts
            1, // reveal_age
            1, // views_signatures
            null, // auto_monitor_contrib_content
            null, // smart_topic_notification
            null, // mailing_list_style
            1, // auto_mark_read
            null, // sound_enabled
            1, // allow_emails
            1, // allow_emails_from_staff
            0, // highlighted_name
            '*', // pt_allow
            '', // pt_rules_text
            1, // validated
            '', // validated_email_confirm_code
            null, // probation_expiration_time
            '0', // is_perm_banned
            false // check_correctness
        );
        // Make test user
        cns_make_member(
            'test', // username
            post_param_string('cns_admin_password', 'admin', INPUT_FILTER_PASSWORD), // password
            '', // email_address
            $member_group_0, // primary_group
            null, // secondary_groups
            null, // dob_day
            null, // dob_month
            null, // dob_year
            [], // custom_fields
            null, // timezone
            '', // language
            '', // theme
            '', // title
            '', // photo_url
            null, // avatar_url
            '', // signature
            null, // preview_posts
            1, // reveal_age
            1, // views_signatures
            null, // auto_monitor_contrib_content
            null, // smart_topic_notification
            null, // mailing_list_style
            1, // auto_mark_read
            null, // sound_enabled
            1, // allow_emails
            1, // allow_emails_from_staff
            0, // highlighted_name
            '*', // pt_allow
            '', // pt_rules_text
            1, // validated
            '', // validated_email_confirm_code
            null, // probation_expiration_time
            '0', // is_perm_banned
            false // check_correctness
        );

        $GLOBALS['FORUM_DB']->create_table('f_read_logs', [
            'l_member_id' => '*MEMBER',
            'l_topic_id' => '*AUTO_LINK',
            'l_time' => 'TIME',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_read_logs', 'erase_old_read_logs', ['l_time']);

        cns_make_post($topic_id, do_lang('DEFAULT_POST_TITLE'), do_lang('DEFAULT_POST_CONTENT'), 0, true, 1, 0, do_lang('SYSTEM'), '127.0.0.1', time(), $GLOBALS['CNS_DRIVER']->get_guest_id(), null, null, null, false, true);

        // Add privileges
        global $CNS_TRUE_PERMISSIONS, $CNS_FALSE_PERMISSIONS;
        foreach ($CNS_TRUE_PERMISSIONS as $permission) {
            add_privilege('FORUMS_AND_MEMBERS', $permission, true);
        }
        foreach ($CNS_FALSE_PERMISSIONS as $permission) {
            add_privilege('FORUMS_AND_MEMBERS', $permission, false, ($permission == 'view_other_pt'));
        }
    }

    if (($upgrade_from === null) || ($upgrade_from < 10.0)) {
        $GLOBALS['FORUM_DB']->create_index('f_members', 'last_visit_time', ['m_dob_month', 'm_dob_day', 'm_last_visit_time']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'posts_by_in_forum', ['p_posting_member', 'p_cache_forum_id']); // Used for searching by member, filtered by forum access
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'posts_by_in_topic', ['p_posting_member', 'p_topic_id']); // Used to help on some joins / complex queries
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'posts_by', ['p_posting_member', 'p_time']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_cache_num_posts', ['t_cache_num_posts']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'in_topic_change_order', ['p_topic_id', 'p_last_edit_time', 'p_time', 'id']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_cache_last_member_id', ['t_cache_last_member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_cache_first_post_id', ['t_cache_first_post_id']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_cache_last_post_id', ['t_cache_last_post_id']);

        $GLOBALS['FORUM_DB']->create_index('f_groups', '#groups_search__combined', ['g_name', 'g_title']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', '#posts_search__combined', ['p_post', 'p_title']);

        // Has to be done after f_groups is added
        add_privilege('FORUMS_AND_MEMBERS', 'exceed_post_edit_time_limit', true);
        add_privilege('FORUMS_AND_MEMBERS', 'exceed_post_delete_time_limit', true);
        add_privilege('FORUMS_AND_MEMBERS', 'bypass_required_cpfs');
        add_privilege('FORUMS_AND_MEMBERS', 'bypass_required_cpfs_if_already_empty');
        add_privilege('FORUMS_AND_MEMBERS', 'bypass_email_address');
        add_privilege('FORUMS_AND_MEMBERS', 'bypass_email_address_if_already_empty');
        add_privilege('FORUMS_AND_MEMBERS', 'bypass_dob');
        add_privilege('FORUMS_AND_MEMBERS', 'bypass_dob_if_already_empty');
    }

    if (($upgrade_from === null) || ($upgrade_from < 11.0)) {
        add_privilege('FORUMS_AND_MEMBERS', 'appear_under_birthdays', true);

        $GLOBALS['FORUM_DB']->create_index('f_forums', 'club_search', ['f_description']);

        $GLOBALS['FORUM_DB']->create_index('f_special_pt_access', 'sp_member', ['s_member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_special_pt_access', 'sp_topic', ['s_topic_id']);
        $GLOBALS['FORUM_DB']->create_index('f_poll_votes', 'member_id', ['pv_member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_members', 'last_visit_time_2', ['m_last_visit_time']);

        $test = $GLOBALS['FORUM_DB']->query_select_value_if_there('f_groups', 'id', ['g_is_default' => 1]);
        if ($test === null) {
            $GLOBALS['FORUM_DB']->query_update('f_groups', ['g_is_default' => 1], ['id' => db_get_first_id() + 8], '', 1);
        }

        $GLOBALS['FORUM_DB']->create_table('f_group_approvals', [
            'id' => '*AUTO',
            'ga_date_and_time' => 'TIME',
            'ga_member_id' => 'MEMBER',
            'ga_member_username' => 'ID_TEXT',
            'ga_old_group_id' => '?GROUP', // null: join request, not null: rank (promotion)
            'ga_new_group_id' => 'GROUP',
            'ga_status' => 'SHORT_INTEGER', // -1 = declined, 0 = pending, 1 = approved
            'ga_status_member_id' => '?MEMBER', // Member who accepted / declined the request
            'ga_reason' => 'SHORT_TRANS__COMCODE',
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_group_approvals', 'ga_date_and_time', ['ga_date_and_time']);
        $GLOBALS['FORUM_DB']->create_index('f_group_approvals', 'ga_member_id', ['ga_member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_group_approvals', 'ga_old_group_id', ['ga_old_group_id']);
        $GLOBALS['FORUM_DB']->create_index('f_group_approvals', 'ga_new_group_id', ['ga_new_group_id']);
        $GLOBALS['FORUM_DB']->create_index('f_group_approvals', 'ga_status', ['ga_status']);
        $GLOBALS['FORUM_DB']->create_index('f_group_approvals', 'ga_status_member_id', ['ga_status_member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_group_approvals', 'ga_promotion_to', ['ga_new_group_id', 'ga_status']);

        $GLOBALS['FORUM_DB']->create_table('f_pposts_fulltext_index', [
            'i_post_id' => '*AUTO_LINK',
            'i_for' => '*MEMBER',

            'i_lang' => '*LANGUAGE_NAME',
            'i_ngram' => '*INTEGER',
            'i_ac' => '*INTEGER',

            'i_occurrence_rate' => 'REAL',

            // De-normalised stuff from main content tables for any major filters that shape the results provided
            //  (other stuff will come in via join back to the main content table)
            'i_add_time' => 'TIME',
            'i_posting_member' => 'MEMBER',
            'i_starter' => 'BINARY',
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'content_id', [ // Used for clean-outs and potentially optimising some JOINs if query planner decides to start at the content table
            'i_post_id',
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_add_time',
            'i_posting_member',
            'i_starter',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_2', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_3', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_4', [
            'i_lang',
            'i_ngram',
            'i_posting_member',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_5', [
            'i_lang',
            'i_ngram',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_6', [
            'i_lang',
            'i_ngram',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_7', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_add_time',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_8', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_posting_member',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_9', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_10', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_11', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_posting_member',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_12', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_13', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_14', [
            'i_lang',
            'i_ngram',
            'i_posting_member',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_15', [
            'i_lang',
            'i_ngram',
            'i_posting_member',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_16', [
            'i_lang',
            'i_ngram',
            'i_starter',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_17', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_add_time',
            'i_posting_member',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_18', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_add_time',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_19', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_add_time',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_20', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_posting_member',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_21', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_posting_member',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_22', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_starter',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_23', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_posting_member',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_24', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_posting_member',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_25', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_starter',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_26', [
            'i_lang',
            'i_ngram',
            'i_posting_member',
            'i_starter',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_27', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_add_time',
            'i_posting_member',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_28', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_add_time',
            'i_posting_member',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_29', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_add_time',
            'i_starter',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_30', [
            'i_lang',
            'i_ngram',
            'i_ac',
            'i_posting_member',
            'i_starter',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_31', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_posting_member',
            'i_starter',
            'i_for',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_pposts_fulltext_index', 'main_32', [
            'i_lang',
            'i_ngram',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_posts_fulltext_index', [
            'i_post_id' => '*AUTO_LINK',

            'i_lang' => '*LANGUAGE_NAME',
            'i_ngram' => '*INTEGER',
            'i_ac' => '*INTEGER',

            'i_occurrence_rate' => 'REAL',

            // De-normalised stuff from main content tables for any major filters that shape the results provided
            //  (other stuff will come in via join back to the main content table)
            'i_add_time' => 'TIME',
            'i_forum_id' => 'AUTO_LINK',
            'i_posting_member' => 'MEMBER',
            'i_open' => 'BINARY',
            'i_pinned' => 'BINARY',
            'i_starter' => 'BINARY',
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'content_id', [ // Used for clean-outs and potentially optimising some JOINs if query planner decides to start at the content table
            'i_post_id',
        ]);

        /* TODO: too long
            $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main', [
                'i_lang',
                'i_ngram',
                'i_ac',
                'i_add_time',
                'i_forum_id',
                'i_posting_member',
                'i_open',
                'i_pinned',
                'i_starter',
                'i_occurrence_rate', // For sorting
            ]);
        */

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_2', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_3', [
            'i_lang',
            'i_ngram',
            'i_forum_id',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_4', [
            'i_lang',
            'i_ngram',
            'i_posting_member',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_5', [
            'i_lang',
            'i_ngram',
            'i_open',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_6', [
            'i_lang',
            'i_ngram',
            'i_pinned',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_7', [
            'i_lang',
            'i_ngram',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_8', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_forum_id',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_9', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_open',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_10', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_11', [
            'i_lang',
            'i_ngram',
            'i_forum_id',
            'i_open',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_12', [
            'i_lang',
            'i_ngram',
            'i_forum_id',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_13', [
            'i_lang',
            'i_ngram',
            'i_open',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_14', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_forum_id',
            'i_open',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_15', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_forum_id',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_16', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_open',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_17', [
            'i_lang',
            'i_ngram',
            'i_forum_id',
            'i_open',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_18', [
            'i_lang',
            'i_ngram',
            'i_add_time',
            'i_forum_id',
            'i_open',
            'i_starter',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_index('f_posts_fulltext_index', 'main_19', [
            'i_lang',
            'i_ngram',
            'i_occurrence_rate', // For sorting
        ]);

        $GLOBALS['FORUM_DB']->create_table('f_warnings_punitive', [
            'id' => '*AUTO',
            'p_warning_id' => 'AUTO_LINK',
            'p_member_id' => 'MEMBER', // The member to which the action applies
            'p_ip_address' => 'IP', // The IP address to which the action applies, if applicable
            'p_email_address' => 'SHORT_TEXT', // The e-mail address to which the action applies, if applicable
            'p_hook' => 'ID_TEXT', // name of a hook in systems/cns_warnings that performed the action
            'p_action' => 'ID_TEXT', // A punitive action language string code, which p_param_a and p_param_b will be injected for written context
            'p_param_a' => 'SHORT_TEXT', // some parameter relating to the action
            'p_param_b' => 'SHORT_TEXT', // some other parameter relating to the action
            'p_reversed' => 'BINARY', // Whether this punitive action was reversed
        ]);
        $GLOBALS['FORUM_DB']->create_index('f_warnings_punitive', 'warningsid', ['p_warning_id']);
        $GLOBALS['FORUM_DB']->create_index('f_warnings_punitive', 'warninghook', ['p_hook']);
    }

    if (($upgrade_from !== null) && ($upgrade_from < 11.0)) { // LEGACY
        delete_privilege('may_report_post'); // Combined into the enhanced report_content addon

        rename_config_option('is_on_coppa', 'is_on_parental_consent');
        rename_config_option('coppa_age', 'parental_consent_age');

        // Database changes (a lot of these are naming convention fixes for the database repair tool)
        $GLOBALS['FORUM_DB']->alter_table_field('f_forums', 'f_cache_last_username', 'ID_TEXT');
        $GLOBALS['FORUM_DB']->alter_table_field('f_poll_answers', 'pa_poll_id', 'AUTO_LINK');
        $GLOBALS['FORUM_DB']->alter_table_field('f_group_join_log', 'usergroup_id', '?GROUP');
        $GLOBALS['FORUM_DB']->alter_table_field('f_invites', 'i_inviter', 'MEMBER', 'i_invite_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_members', 'm_on_probation_until', '?TIME', 'm_probation_expiration_time');
        $GLOBALS['FORUM_DB']->alter_table_field('f_groups', 'g_group_leader', '?MEMBER', 'g_group_lead_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_groups', 'g_promotion_target', '?GROUP', 'g_promotion_target_group');
        $GLOBALS['FORUM_DB']->alter_table_field('f_forums', 'f_parent_forum', '?AUTO_LINK', 'f_parent_forum_id');
        $GLOBALS['FORUM_DB']->alter_table_field('f_topics', 't_pt_from', '?MEMBER', 't_pt_from_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_topics', 't_pt_to', '?MEMBER', 't_pt_to_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_posts', 'p_poster', 'MEMBER', 'p_posting_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_posts', 'p_intended_solely_for', '?MEMBER', 'p_whisper_to_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_posts', 'p_last_edit_by', '?MEMBER', 'p_last_edit_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_forum_intro_ip', 'i_ip', '*IP', 'i_ip_address');
        $GLOBALS['FORUM_DB']->alter_table_field('f_poll_votes', 'pv_ip', 'IP', 'pv_ip_address');
        $GLOBALS['FORUM_DB']->alter_table_field('f_poll_votes', 'pv_cache_points_at_voting_time', 'INTEGER', 'pv_points_when_voted');
        $GLOBALS['FORUM_DB']->alter_table_field('f_multi_moderations', 'mm_move_to', '?AUTO_LINK', 'mm_move_to_forum_id');
        $GLOBALS['FORUM_DB']->alter_table_field('f_warnings', 'w_by', 'MEMBER', 'w_issuing_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_moderator_logs', 'l_by', 'MEMBER', 'l_by_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_member_known_login_ips', 'i_ip', '*IP', 'i_ip_address');
        $GLOBALS['FORUM_DB']->alter_table_field('f_pposts_fulltext_index', 'i_poster_id', 'MEMBER', 'i_posting_member');
        $GLOBALS['FORUM_DB']->alter_table_field('f_posts_fulltext_index', 'i_poster_id', 'MEMBER', 'i_posting_member');

        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_posts', 'last_edit_by');
        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_posts_fulltext_index', 'main');
        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_topics', 't_pt_to');
        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_topics', 't_pt_from');
        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_poll_votes', 'v_voter_id');
        $GLOBALS['FORUM_DB']->delete_index_if_exists('f_poll_votes', 'v_voter_ip');

        $GLOBALS['FORUM_DB']->create_index('f_poll_answers', 'pollid', ['pa_poll_id']);

        // Migrate old f_warnings columns to f_warnings_punitive rows. Then, delete the old columns.
        $start = 0;
            do {
                $rows = $GLOBALS['FORUM_DB']->query_select('f_warnings', ['*'], [], '', 500, $start);
                foreach ($rows as $i => $row) {
                    if ($row['p_silence_from_topic'] !== null) {
                        $GLOBALS['FORUM_DB']->query_insert('f_warnings_punitive', [
                            'p_warning_id' => $row['id'],
                            'p_member_id' => $row['w_member_id'],
                            'p_ip_address' => '',
                            'p_email_address' => '',
                            'p_hook' => 'silencing',
                            'p_action' => '_PUNITIVE_SILENCE_FROM_TOPIC',
                            'p_param_a' => strval($row['p_silence_from_topic']),
                            'p_param_b' => '', // f_warnings did not track time
                            'p_reversed' => 0,
                        ]);
                    }
                    if ($row['p_silence_from_forum'] !== null) {
                        $GLOBALS['FORUM_DB']->query_insert('f_warnings_punitive', [
                            'p_warning_id' => $row['id'],
                            'p_member_id' => $row['w_member_id'],
                            'p_ip_address' => '',
                            'p_email_address' => '',
                            'p_hook' => 'silencing',
                            'p_action' => '_PUNITIVE_SILENCE_FROM_FORUM',
                            'p_param_a' => strval($row['p_silence_from_forum']),
                            'p_param_b' => '', // f_warnings did not track time
                            'p_reversed' => 0,
                        ]);
                    }
                    if (($row['p_probation'] !== null) && ($row['p_probation'] > 0)) {
                        $GLOBALS['FORUM_DB']->query_insert('f_warnings_punitive', [
                            'p_warning_id' => $row['id'],
                            'p_member_id' => $row['w_member_id'],
                            'p_ip_address' => '',
                            'p_email_address' => '',
                            'p_hook' => 'probation',
                            'p_action' => '_PUNITIVE_PROBATION',
                            'p_param_a' => strval($row['p_probation']),
                            'p_param_b' => '',
                            'p_reversed' => 0,
                        ]);
                    }
                    if (($row['p_banned_ip'] !== null) && ($row['p_banned_ip'] != '')) {
                        $GLOBALS['FORUM_DB']->query_insert('f_warnings_punitive', [
                            'p_warning_id' => $row['id'],
                            'p_member_id' => $row['w_member_id'],
                            'p_ip_address' => strval($row['p_banned_ip']),
                            'p_email_address' => '',
                            'p_hook' => 'ban_ip',
                            'p_action' => '_PUNITIVE_IP_BANNED',
                            'p_param_a' => '',
                            'p_param_b' => '',
                            'p_reversed' => 0,
                        ]);
                    }
                    if (($row['p_charged_points'] !== null) && ($row['p_charged_points'] > 0)) {
                        $GLOBALS['FORUM_DB']->query_insert('f_warnings_punitive', [
                            'p_warning_id' => $row['id'],
                            'p_member_id' => $row['w_member_id'],
                            'p_ip_address' => '',
                            'p_email_address' => '',
                            'p_hook' => 'points',
                            'p_action' => '_PUNITIVE_CHARGED_POINTS',
                            'p_param_a' => strval($row['p_charged_points']),
                            'p_param_b' => '',
                            'p_reversed' => 0,
                        ]);
                    }
                    if ($row['p_banned_member'] !== 0) {
                        $GLOBALS['FORUM_DB']->query_insert('f_warnings_punitive', [
                            'p_warning_id' => $row['id'],
                            'p_member_id' => $row['w_member_id'],
                            'p_ip_address' => '',
                            'p_email_address' => '',
                            'p_hook' => 'ban_member',
                            'p_action' => '_PUNITIVE_BAN_ACCOUNT',
                            'p_param_a' => '',
                            'p_param_b' => '',
                            'p_reversed' => 0,
                        ]);
                    }
                    if ($row['p_changed_usergroup_from'] !== null) {
                        $GLOBALS['FORUM_DB']->query_insert('f_warnings_punitive', [
                            'p_warning_id' => $row['id'],
                            'p_member_id' => $row['w_member_id'],
                            'p_ip_address' => '',
                            'p_email_address' => '',
                            'p_hook' => 'change_group',
                            'p_action' => '_PUNITIVE_CHANGE_USERGROUP',
                            'p_param_a' => strval($row['p_changed_usergroup_from']),
                            'p_param_b' => strval($row['p_changed_usergroup_to']),
                            'p_reversed' => 0,
                        ]);
                    }
                }
                $start += 500;
            } while (array_key_exists(0, $rows));
            $GLOBALS['FORUM_DB']->delete_table_field('f_warnings', 'p_silence_from_topic');
            $GLOBALS['FORUM_DB']->delete_table_field('f_warnings', 'p_silence_from_forum');
            $GLOBALS['FORUM_DB']->delete_table_field('f_warnings', 'p_probation');
            $GLOBALS['FORUM_DB']->delete_table_field('f_warnings', 'p_banned_ip');
            $GLOBALS['FORUM_DB']->delete_table_field('f_warnings', 'p_charged_points');
            $GLOBALS['FORUM_DB']->delete_table_field('f_warnings', 'p_banned_member');
            $GLOBALS['FORUM_DB']->delete_table_field('f_warnings', 'p_changed_usergroup_from');
            $GLOBALS['FORUM_DB']->delete_table_field('f_warnings', 'p_changed_usergroup_to');
    }

    if (($upgrade_from === null) || ($upgrade_from < 11.0)) {
        $GLOBALS['FORUM_DB']->change_primary_key('f_poll_answers', ['id']);
        $GLOBALS['FORUM_DB']->create_index('f_invites', 'inviter', ['i_invite_member']);
        $GLOBALS['FORUM_DB']->create_index('f_posts', 'last_edit_member', ['p_last_edit_member']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_pt_to_member', ['t_pt_to_member']);
        $GLOBALS['FORUM_DB']->create_index('f_topics', 't_pt_from_member', ['t_pt_from_member']);
        $GLOBALS['FORUM_DB']->create_index('f_poll_votes', 'voting_member_id', ['pv_member_id']);
        $GLOBALS['FORUM_DB']->create_index('f_poll_votes', 'voting_ip_address', ['pv_ip_address']);
    }
}
