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

/**
 * Module page class.
 */
class Module_admin_version
{
    /**
     * Find details of the module.
     *
     * @return ?array Map of module info (null: module is disabled)
     */
    public function info() : ?array
    {
        $info = [];
        $info['author'] = 'Chris Graham';
        $info['organisation'] = 'Composr';
        $info['hacked_by'] = null;
        $info['hack_version'] = null;
        $info['version'] = 20;
        $info['locked'] = true;
        $info['update_require_upgrade'] = true;
        $info['min_cms_version'] = 11.0;
        $info['addon'] = 'core';
        return $info;
    }

    /**
     * Uninstall the module.
     */
    public function uninstall()
    {
        $tables = [
            'url_id_monikers',
            'cache',
            'cache_on',
            'captchas',
            'rating',
            'member_tracking',
            'trackbacks',
            'menu_items',
            'values_elective',
            'tutorial_links',
            'edit_pings',
            'webstandards_checked_once',
            'member_privileges',
            'member_zone_access',
            'member_page_access',
            'member_category_access',
            'tracking',
            'autosave',
            'messages_to_render',
            'url_title_cache',
            'review_supplement',
            'logged_mail_messages',
            'incoming_uploads',
            'f_group_member_timeouts',
            'temp_block_permissions',
            'cron_caching_requests',
            'notifications_enabled',
            'digestives_tin',
            'digestives_consumed',
            'unbannable_ip',
            'alternative_ids',
            'content_privacy',
            'content_privacy__members',
            'task_queue',
            'comcode_pages',
            'cached_comcode_pages',
            'email_bounces',
            'unsubscribed_emails',
            'sitemap_cache',
            'urls_checked',
            'content_regions',
            'post_tokens',
            'cron_progression',
            'translation_cache',
            'ft_index_commonality',
            'cpages_fulltext_index',
            'daily_visits',
        ];
        $GLOBALS['SITE_DB']->drop_table_if_exists($tables);

        /* We don't want to get rid of on-disk data when reinstalling
        $zones = find_all_zones(true);
        require_code('files');
        $langs = find_all_langs(true);
        foreach ($zones as $zone) {
            foreach (array_keys($langs) as $lang) {
                deldir_contents(zone_black_magic_filterer(get_custom_file_base() . (($zone == '') ? '' : '/') . $zone . '/pages/comcode_custom/' . $lang, true), true);
            }
        }
        */

        delete_attachments('comcode_page');

        $privileges = [
            'edit_meta_fields',
            'view_private_content',
            'perform_webstandards_check_by_default',
        ];
        delete_privilege($privileges);
    }

    /**
     * Install the module.
     *
     * @param  ?integer $upgrade_from What version we're upgrading from (null: new install)
     * @param  ?integer $upgrade_from_hack What hack version we're upgrading from (null: new-install/not-upgrading-from-a-hacked-version)
     */
    public function install(?int $upgrade_from = null, ?int $upgrade_from_hack = null)
    {
        // A lot of "peripheral architectural" tables are defined here. Central ones are defined in the installer -- as they need to be installed before any module.
        // This is always the first module to be installed.

        if ($upgrade_from === null) { // These are only for fresh installs
            set_value('version', float_to_raw_string(cms_version_number()));
            set_value('cns_version', float_to_raw_string(cms_version_number()));

            $GLOBALS['SITE_DB']->create_table('menu_items', [
                'id' => '*AUTO',
                'i_menu' => 'ID_TEXT', // Foreign key in the future - currently it just binds together
                'i_order' => 'INTEGER',
                'i_parent_id' => '?AUTO_LINK',
                'i_caption' => 'SHORT_TRANS__COMCODE',
                'i_caption_long' => 'SHORT_TRANS__COMCODE',
                'i_link' => 'SHORT_TEXT', // Supports page-links or URLs
                'i_check_permissions' => 'BINARY',
                'i_expanded' => 'BINARY',
                'i_new_window' => 'BINARY',
                'i_include_sitemap' => 'SHORT_INTEGER',
                'i_page_only' => 'ID_TEXT', // Only show up if the page is this (allows page specific menus)
                'i_theme_img_code' => 'ID_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('menu_items', 'menu_extraction', ['i_menu']);

            $GLOBALS['SITE_DB']->create_table('trackbacks', [
                'id' => '*AUTO',
                'trackback_for_type' => 'ID_TEXT',
                'trackback_for_id' => 'ID_TEXT',
                'trackback_ip_address' => 'IP',
                'trackback_time' => 'TIME',
                'trackback_url' => 'URLPATH',
                'trackback_title' => 'SHORT_TEXT',
                'trackback_excerpt' => 'LONG_TEXT',
                'trackback_name' => 'SHORT_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('trackbacks', 'trackback_for_type', ['trackback_for_type']);
            $GLOBALS['SITE_DB']->create_index('trackbacks', 'trackback_for_id', ['trackback_for_id']);
            $GLOBALS['SITE_DB']->create_index('trackbacks', 'trackback_time', ['trackback_time']);

            $GLOBALS['SITE_DB']->create_table('captchas', [
                'si_session_id' => '*ID_TEXT',
                'si_time' => 'TIME',
                'si_code' => 'ID_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('captchas', 'si_time', ['si_time']);

            $GLOBALS['SITE_DB']->create_table('member_tracking', [
                'mt_member_id' => '*MEMBER',
                'mt_cache_username' => 'ID_TEXT',
                'mt_time' => '*TIME',
                'mt_page' => '*ID_TEXT',
                'mt_type' => '*ID_TEXT',
                'mt_id' => '*ID_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('member_tracking', 'mt_page', ['mt_page']);
            $GLOBALS['SITE_DB']->create_index('member_tracking', 'mt_id', ['mt_page', 'mt_id', 'mt_type']);

            $GLOBALS['SITE_DB']->create_table('cache_on', [
                'cached_for' => '*ID_TEXT',
                'cache_on' => 'LONG_TEXT',
                'special_cache_flags' => 'INTEGER',
                'cache_ttl' => 'INTEGER',
            ]);

            $GLOBALS['SITE_DB']->create_table('webstandards_checked_once', [
                'hash' => '*SHORT_TEXT',
            ], false, false, true);

            $GLOBALS['SITE_DB']->create_table('edit_pings', [
                'id' => '*AUTO',
                'the_page' => 'ID_TEXT',
                'the_type' => 'ID_TEXT',
                'the_id' => 'ID_TEXT',
                'the_time' => 'TIME',
                'the_member' => 'MEMBER',
            ]);

            $GLOBALS['SITE_DB']->create_table('values_elective', [ // This table could contain many rows / values
                'the_name' => '*ID_TEXT',
                'the_value' => 'LONG_TEXT',
                'date_and_time' => 'TIME',
            ]);
            set_value('call_home', strval(post_param_integer('advertise_on', 0)), true); // Relayed from installer

            $GLOBALS['SITE_DB']->create_table('tutorial_links', [
                'the_name' => '*ID_TEXT',
                'the_value' => 'LONG_TEXT',
            ]);

            $GLOBALS['SITE_DB']->create_table('member_privileges', [
                'member_id' => '*MEMBER',
                'privilege' => '*ID_TEXT',
                'the_page' => '*ID_TEXT',
                'module_the_name' => '*ID_TEXT', // The permission module (NOT the page module which is the_page)
                'category_name' => '*ID_TEXT',
                'the_value' => 'BINARY',
                'active_until' => '?TIME',
            ], false, false, true);

            $GLOBALS['SITE_DB']->create_table('member_zone_access', [
                'zone_name' => '*ID_TEXT',
                'member_id' => '*MEMBER',
                'active_until' => '?TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('member_zone_access', 'mzazone_name', ['zone_name']);
            $GLOBALS['SITE_DB']->create_index('member_zone_access', 'mzamember_id', ['member_id']);

            $GLOBALS['SITE_DB']->create_table('member_page_access', [
                'page_name' => '*ID_TEXT',
                'zone_name' => '*ID_TEXT',
                'member_id' => '*MEMBER',
                'active_until' => '?TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('member_page_access', 'mzaname', ['page_name', 'zone_name']);
            $GLOBALS['SITE_DB']->create_index('member_page_access', 'mzamember_id', ['member_id']);

            $GLOBALS['SITE_DB']->create_table('member_category_access', [
                'module_the_name' => '*ID_TEXT', // The permission module (NOT the page module)
                'category_name' => '*ID_TEXT',
                'member_id' => '*MEMBER',
                'active_until' => '?TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('member_category_access', 'mcaname', ['module_the_name', 'category_name']);
            $GLOBALS['SITE_DB']->create_index('member_category_access', 'mcamember_id', ['member_id']);

            $GLOBALS['SITE_DB']->create_table('autosave', [
                'id' => '*AUTO',
                'a_member_id' => 'MEMBER',
                'a_key' => 'LONG_TEXT',
                'a_value' => 'LONG_TEXT',
                'a_time' => 'TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('autosave', 'myautosaves', ['a_member_id']);

            $GLOBALS['SITE_DB']->create_table('messages_to_render', [
                'id' => '*AUTO',
                'r_session_id' => 'ID_TEXT',
                'r_message' => 'LONG_TEXT',
                'r_type' => 'ID_TEXT',
                'r_time' => 'TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('messages_to_render', 'forsession', ['r_session_id']);

            $GLOBALS['SITE_DB']->create_table('url_title_cache', [
                'id' => '*AUTO',
                't_url' => 'URLPATH',
                't_title' => 'SHORT_TEXT',
                't_meta_title' => 'LONG_TEXT',
                't_keywords' => 'LONG_TEXT',
                't_description' => 'LONG_TEXT',
                't_image_url' => 'URLPATH',
                't_mime_type' => 'ID_TEXT',
                // oEmbed...
                't_discovery_url_json' => 'URLPATH',
                't_discovery_url_xml' => 'URLPATH',
            ]);
            $GLOBALS['SITE_DB']->create_index('url_title_cache', 't_url', ['t_url']);

            $GLOBALS['SITE_DB']->create_table('rating', [
                'id' => '*AUTO',
                'rating_for_type' => 'ID_TEXT',
                'rating_for_id' => 'ID_TEXT',
                'rating_member' => 'MEMBER',
                'rating_ip_address' => 'IP',
                'rating_time' => 'TIME',
                'rating' => 'SHORT_INTEGER',
            ]);
            $GLOBALS['SITE_DB']->create_index('rating', 'alt_key', ['rating_for_type', 'rating_for_id']);
            $GLOBALS['SITE_DB']->create_index('rating', 'rating_for_id', ['rating_for_id']);

            $GLOBALS['SITE_DB']->create_table('comcode_pages', [
                'the_zone' => '*ID_TEXT',
                'the_page' => '*ID_TEXT',
                'p_parent_page' => 'ID_TEXT',
                'p_validated' => 'BINARY',
                'p_edit_date' => '?TIME',
                'p_add_date' => 'TIME',
                'p_submitter' => 'MEMBER',
                'p_show_as_edit' => 'BINARY',
                'p_include_on_sitemap' => 'BINARY',
                'p_order' => 'INTEGER',
            ]);
            $GLOBALS['SITE_DB']->create_index('comcode_pages', 'p_submitter', ['p_submitter']);
            $GLOBALS['SITE_DB']->create_index('comcode_pages', 'p_add_date', ['p_add_date']);
            $GLOBALS['SITE_DB']->create_index('comcode_pages', 'p_validated', ['p_validated']);

            $fields = [
                'the_zone' => '*ID_TEXT',
                'the_page' => '*ID_TEXT',
                'string_index' => 'LONG_TRANS__COMCODE',
                'the_theme' => '*ID_TEXT',
                'cc_page_title' => '?SHORT_TRANS', // We need to be very careful with nullable translatable fields, as they will not be nullable when !multi_lang_content()
            ];
            if ($GLOBALS['SITE_DB']->driver->full_text_needs_single_key()) { // FUDGE: Full-text search requires a single key
                $fields = [
                    'id' => '*AUTO',
                    'the_zone' => 'ID_TEXT',
                    'the_page' => 'ID_TEXT',
                    'the_theme' => 'ID_TEXT',
                ] + $fields;
            }
            $GLOBALS['SITE_DB']->create_table('cached_comcode_pages', $fields, false, false, false, true);

            $GLOBALS['SITE_DB']->create_index('cached_comcode_pages', 'ftjoin_ccpt', ['cc_page_title']);
            $GLOBALS['SITE_DB']->create_index('cached_comcode_pages', 'ftjoin_ccsi', ['string_index']);
            $GLOBALS['SITE_DB']->create_index('cached_comcode_pages', 'ccp_join', ['the_page', 'the_zone']);
        }

        // A lot of core upgrade is also here. When absolutely necessary it is put in upgrade.php.

        if (($upgrade_from !== null) && ($upgrade_from < 20)) { // LEGACY: 11.beta1 (later upgrade code depends on this)
            // Consistency changes which must occur first
            if ($upgrade_from == 19) {
                $GLOBALS['SITE_DB']->alter_table_field('cron_progression', 'c_last_run', '?TIME', 'c_last_run_time');
            }
        }

        if (($upgrade_from < 10) || ($upgrade_from === null)) {
            $GLOBALS['SITE_DB']->create_table('url_id_monikers', [
                'id' => '*AUTO',
                'm_resource_page' => 'ID_TEXT',
                'm_resource_type' => 'ID_TEXT',
                'm_resource_id' => 'ID_TEXT',
                'm_moniker' => 'SHORT_TEXT',
                'm_moniker_reversed' => 'SHORT_TEXT', // For indexed tail-end searching
                'm_deprecated' => 'BINARY',
                'm_manually_chosen' => 'BINARY',
            ]);
            $GLOBALS['SITE_DB']->create_index('url_id_monikers', 'uim_moniker', ['m_moniker']);
            $GLOBALS['SITE_DB']->create_index('url_id_monikers', 'uim_monrev', ['m_moniker_reversed']);

            $GLOBALS['SITE_DB']->create_table('review_supplement', [
                'r_post_id' => '*AUTO_LINK',
                'r_rating_type' => '*ID_TEXT',
                'r_rating' => 'SHORT_INTEGER',
                'r_topic_id' => 'AUTO_LINK',
                'r_rating_for_id' => 'ID_TEXT',
                'r_rating_for_type' => 'ID_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('review_supplement', 'rating_for_id', ['r_rating_for_id']);

            $GLOBALS['SITE_DB']->create_table('logged_mail_messages', [
                'id' => '*AUTO',
                'm_subject' => 'LONG_TEXT', // While data for a subject would be tied to SHORT_TEXT, a language string could bump it up higher
                'm_message' => 'LONG_TEXT',
                'm_to_email' => 'LONG_TEXT',
                'm_extra_cc_addresses' => 'LONG_TEXT',
                'm_extra_bcc_addresses' => 'LONG_TEXT',
                'm_join_time' => '?TIME',
                'm_to_name' => 'LONG_TEXT',
                'm_from_email' => 'SHORT_TEXT',
                'm_from_name' => 'SHORT_TEXT',
                'm_priority' => 'SHORT_INTEGER',
                'm_attachments' => 'LONG_TEXT',
                'm_no_cc' => 'BINARY',
                'm_as_member' => 'MEMBER',
                'm_as_admin' => 'BINARY',
                'm_in_html' => 'BINARY',
                'm_date_and_time' => 'TIME',
                'm_member_id' => 'MEMBER',
                'm_url' => 'LONG_TEXT',
                'm_queued' => 'BINARY',
                'm_template' => 'ID_TEXT',
                'm_sender_email' => 'SHORT_TEXT',
                'm_plain_subject' => 'BINARY',
            ]);
            $GLOBALS['SITE_DB']->create_index('logged_mail_messages', 'recentmessages', ['m_date_and_time']);
            $GLOBALS['SITE_DB']->create_index('logged_mail_messages', 'queued', ['m_queued']);
            $GLOBALS['SITE_DB']->create_index('logged_mail_messages', 'combo', ['m_date_and_time', 'm_queued']); // Used for number-sent-within querying

            $GLOBALS['SITE_DB']->create_table('incoming_uploads', [
                'id' => '*AUTO',
                'i_submitter' => 'MEMBER',
                'i_date_and_time' => 'TIME',
                'i_orig_filename' => 'SHORT_TEXT',
                'i_save_url' => 'URLPATH',
            ]);
        }

        if (($upgrade_from !== null) && ($upgrade_from < 11)) {
            $GLOBALS['SITE_DB']->query_update('comcode_pages', ['p_submitter' => 2], ['p_submitter' => $GLOBALS['FORUM_DRIVER']->get_guest_id()]);
        }

        if (($upgrade_from === null) || ($upgrade_from < 12)) {
            $GLOBALS['SITE_DB']->drop_table_if_exists('cache');
            $GLOBALS['SITE_DB']->create_table('cache', [
                'id' => '*AUTO',
                'cached_for' => 'ID_TEXT',
                'identifier' => 'MINIID_TEXT',
                'staff_status' => '?BINARY', // May be null
                'the_member' => '?MEMBER', // May be null
                'the_groups' => 'LONG_TEXT', // May be blank
                'is_bot' => '?BINARY', // May be null
                'timezone' => 'MINIID_TEXT', // May be blank
                'lang' => 'LANGUAGE_NAME', // *Always* set
                'the_theme' => 'MINIID_TEXT', // *Always* set
                'the_value' => 'LONG_TEXT',
                'dependencies' => 'LONG_TEXT',
                'date_and_time' => 'TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('cache', 'cached_ford', ['date_and_time']);
            $GLOBALS['SITE_DB']->create_index('cache', 'cached_fore', ['cached_for']);
            $GLOBALS['SITE_DB']->create_index('cache', 'cached_forh', ['the_theme']);
        }

        if (($upgrade_from === null) || ($upgrade_from < 13)) {
            if (!$GLOBALS['SITE_DB']->table_exists('f_group_member_timeouts')) {
                $GLOBALS['SITE_DB']->create_table('f_group_member_timeouts', [
                    'member_id' => '*MEMBER',
                    'group_id' => '*GROUP',
                    'timeout' => 'TIME',
                ]);
            }
        }

        if (($upgrade_from !== null) && ($upgrade_from < 13)) { // LEGACY
            $GLOBALS['SITE_DB']->create_index('translate', 'equiv_lang', ['text_original(4)']);
            $GLOBALS['SITE_DB']->create_index('translate', 'decache', ['text_parsed(2)']);
        }

        if (($upgrade_from !== null) && ($upgrade_from >= 10) && ($upgrade_from < 14)) { // LEGACY
            $GLOBALS['SITE_DB']->drop_table_if_exists('tracking');
            $GLOBALS['SITE_DB']->add_table_field('logged_mail_messages', 'm_template', 'ID_TEXT');
        }

        if (($upgrade_from !== null) && ($upgrade_from == 14)) { // LEGACY
            $GLOBALS['SITE_DB']->alter_table_field('digestives_tin', 'd_from_member_id', '?MEMBER');
        }

        if (($upgrade_from === null) || ($upgrade_from < 14)) {
            $GLOBALS['SITE_DB']->create_table('temp_block_permissions', [
                'id' => '*AUTO',
                'p_session_id' => 'ID_TEXT',
                'p_block_constraints' => 'LONG_TEXT',
                'p_time' => 'TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('temp_block_permissions', 'p_session_id', ['p_session_id']);

            $GLOBALS['SITE_DB']->create_table('cron_caching_requests', [
                'id' => '*AUTO',
                'c_codename' => 'ID_TEXT',
                'c_map' => 'LONG_TEXT',
                'c_store_as_tempcode' => 'BINARY',
                'c_staff_status' => '?BINARY',
                'c_member' => '?MEMBER',
                'c_groups' => 'SHORT_TEXT',
                'c_is_bot' => '?BINARY',
                'c_timezone' => 'MINIID_TEXT',
                'c_lang' => 'LANGUAGE_NAME',
                'c_theme' => 'ID_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('cron_caching_requests', 'c_compound', ['c_codename', 'c_theme', 'c_lang', 'c_timezone']);
            $GLOBALS['SITE_DB']->create_index('cron_caching_requests', 'c_is_bot', ['c_is_bot']);
            $GLOBALS['SITE_DB']->create_index('cron_caching_requests', 'c_store_as_tempcode', ['c_store_as_tempcode']);

            $GLOBALS['SITE_DB']->create_table('notifications_enabled', [
                'id' => '*AUTO',
                'l_member_id' => 'MEMBER',
                'l_notification_code' => 'ID_TEXT',
                'l_code_category' => 'SHORT_TEXT',
                'l_setting' => 'INTEGER',
            ]);
            $GLOBALS['SITE_DB']->create_index('notifications_enabled', 'l_code_category', ['l_code_category']);
            $GLOBALS['SITE_DB']->create_index('notifications_enabled', 'l_notification_code', ['l_notification_code']);

            $GLOBALS['SITE_DB']->create_table('digestives_tin', [ // Notifications queued up ready for the regular digest e-mail
                'id' => '*AUTO',
                'd_subject' => 'LONG_TEXT',
                'd_message' => 'LONG_TRANS__COMCODE',
                'd_from_member_id' => '?MEMBER',
                'd_to_member_id' => 'MEMBER',
                'd_priority' => 'SHORT_INTEGER',
                'd_no_cc' => 'BINARY',
                'd_date_and_time' => 'TIME',
                'd_notification_code' => 'ID_TEXT',
                'd_code_category' => 'SHORT_TEXT',
                'd_frequency' => 'INTEGER', // e.g. A_DAILY_EMAIL_DIGEST
                'd_read' => 'BINARY',
            ]);
            $GLOBALS['SITE_DB']->create_index('digestives_tin', 'd_date_and_time', ['d_date_and_time']);
            $GLOBALS['SITE_DB']->create_index('digestives_tin', 'd_frequency', ['d_frequency']);
            $GLOBALS['SITE_DB']->create_index('digestives_tin', 'd_to_member_id', ['d_to_member_id']);
            $GLOBALS['SITE_DB']->create_index('digestives_tin', 'd_read', ['d_read']);
            $GLOBALS['SITE_DB']->create_index('digestives_tin', 'unread', ['d_to_member_id', 'd_read']);
            $GLOBALS['SITE_DB']->create_table('digestives_consumed', [
                'c_member_id' => '*MEMBER',
                'c_frequency' => '*INTEGER', // e.g. A_DAILY_EMAIL_DIGEST
                'c_time' => 'TIME',
            ]);
        }

        if (($upgrade_from !== null) && ($upgrade_from < 16)) { // LEGACY
            $GLOBALS['SITE_DB']->delete_index_if_exists('cron_caching_requests', 'c_in_panel');
            $GLOBALS['SITE_DB']->delete_index_if_exists('cron_caching_requests', 'c_interlock');
            $GLOBALS['SITE_DB']->delete_table_field('cron_caching_requests', 'c_interlock');
            $GLOBALS['SITE_DB']->delete_table_field('cron_caching_requests', 'c_in_panel');

            $GLOBALS['SITE_DB']->delete_index_if_exists('rating', 'rating_for_id');
            $GLOBALS['SITE_DB']->create_index('rating', 'rating_for_id', ['rating_for_id']);
        }

        if (($upgrade_from !== null) && ($upgrade_from < 17)) { // LEGACY
            $comcode_lang_fields = [
                'award_types' => [
                    'a_description',
                ],
                'text' => [
                    'the_message',
                ],
                'community_billboard' => [
                    'the_message',
                ],
                'match_key_messages' => [
                    'k_message',
                ],
                'menu_items' => [
                    'i_caption',
                    'i_caption_long',
                ],
                'cached_comcode_pages' => [
                    'string_index',
                ],
                'authors' => [
                    'description',
                    'skills',
                ],
                'banners' => [
                    'caption',
                ],
                'calendar_types' => [
                    't_title',
                ],
                'calendar_events' => [
                    'e_title',
                    'e_content',
                ],
                'catalogue_efv_long_trans' => [
                    'cv_value',
                ],
                'catalogue_efv_short_trans' => [
                    'cv_value',
                ],
                'catalogues' => [
                    'c_description',
                ],
                'catalogue_categories' => [
                    'cc_description',
                ],
                'wiki_posts' => [
                    'the_message',
                ],
                'wiki_pages' => [
                    'description',
                ],
                'cedi_posts' => [
                    'the_message',
                ],
                'cedi_pages' => [
                    'description',
                ],
                'chat_messages' => [
                    'the_message',
                ],
                'download_downloads' => [
                    'description',
                    'comments',
                    'additional_details',
                ],
                'download_categories' => [
                    'description',
                ],
                'videos' => [
                    'comments',
                    'description',
                ],
                'galleries' => [
                    'description',
                    'teaser',
                    'fullname',
                ],
                'images' => [
                    'comments',
                    'description',
                ],
                'news' => [
                    'title',
                    'news',
                    'news_article',
                ],
                'points_ledger' => [
                    'reason',
                ],
                'escrow' => [
                    'reason',
                    'terms',
                ],
                'escrow_logs' => [
                    'information',
                ],
                'pstore_customs' => [
                    'c_description',
                ],
                'pstore_permissions' => [
                    'p_description',
                ],
                'poll' => [
                    'question',
                    'option1',
                    'option2',
                    'option3',
                    'option4',
                    'option5',
                    'option6',
                    'option7',
                    'option8',
                    'option9',
                    'option10',
                ],
                'quiz_question_answers' => [
                    'q_answer_text',
                ],
                'quizzes' => [
                    'q_start_text',
                    'q_end_text',
                    'q_end_text_fail',
                ],
                'bookable' => [
                    'title',
                    'description',
                    'categorisation',
                ],
                'bookable_blacked' => [
                    'blacked_explanation',
                ],
                'f_posts' => [
                    'p_post',
                ],
                'f_topics' => [
                    't_cache_first_post',
                ],
                'f_members' => [
                    'm_signature',
                    'm_pt_rules_text',
                ],
                'f_forums' => [
                    'f_description',
                    'f_intro_question',
                ],
                'f_usergroup_subs' => [
                    's_description',
                ],
            ];
            push_db_scope_check(false);
            $custom_fields = $GLOBALS['SITE_DB']->query('SELECT id FROM ' . get_table_prefix() . 'f_custom_fields WHERE cf_type IN (\'short_trans\',\'long_trans\')');
            $comcode_lang_fields['f_member_custom_fields'] = [];
            foreach ($custom_fields as $custom_field) {
                $comcode_lang_fields['f_member_custom_fields'][] = 'field_' . strval($custom_field['id']);
            }
            foreach ($comcode_lang_fields as $table => $fields) {
                foreach ($fields as $field) {
                    $sql = 'UPDATE ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'db_meta SET m_type=' . db_function('CONCAT', ['m_type', '\'__COMCODE\'']) . ' WHERE ' . db_string_equal_to('m_table', $table) . ' AND ' . db_string_equal_to('m_name', $field) . ' AND m_type NOT LIKE \'' . db_encode_like('%__COMCODE') . '\'';
                    $GLOBALS['SITE_DB']->query($sql);
                }
            }

            $GLOBALS['SITE_DB']->rename_table('security_images', 'captchas');
            $GLOBALS['SITE_DB']->rename_table('validated_once', 'webstandards_checked_once');
            $GLOBALS['SITE_DB']->rename_table('long_values', 'values_elective');

            $GLOBALS['SITE_DB']->change_primary_key('member_privileges', ['member_id', 'privilege', 'the_page', 'module_the_name', 'category_name']);
            $GLOBALS['SITE_DB']->change_primary_key('member_zone_access', ['member_id', 'zone_name']);
            $GLOBALS['SITE_DB']->change_primary_key('member_page_access', ['member_id', 'page_name', 'zone_name']);
            $GLOBALS['SITE_DB']->change_primary_key('member_category_access', ['member_id', 'module_the_name', 'category_name']);

            $GLOBALS['SITE_DB']->alter_table_field('member_privileges', 'active_until', '?TIME');
            $GLOBALS['SITE_DB']->alter_table_field('member_zone_access', 'active_until', '?TIME');
            $GLOBALS['SITE_DB']->alter_table_field('member_page_access', 'active_until', '?TIME');
            $GLOBALS['SITE_DB']->alter_table_field('member_category_access', 'active_until', '?TIME');

            $GLOBALS['SITE_DB']->query_update('values', ['the_name' => 'setupwizard_completed'], ['the_name' => 'setup_wizard_completed'], '', 1);

            $GLOBALS['SITE_DB']->promote_text_field_to_comcode('digestives_tin', 'd_message', 'id', 4);
            $GLOBALS['SITE_DB']->add_table_field('digestives_tin', 'd_read', 'BINARY');
            $GLOBALS['SITE_DB']->query('UPDATE ' . get_table_prefix() . 'notifications_enabled SET l_setting=l_setting+' . strval(A_WEB_NOTIFICATION) . ' WHERE l_setting<>0');
            $GLOBALS['SITE_DB']->create_index('digestives_tin', 'd_read', ['d_read']);
            $GLOBALS['SITE_DB']->create_index('digestives_tin', 'unread', ['d_to_member_id', 'd_read']);

            rename_config_option('ocp_show_conceded_mode_link', 'show_conceded_mode_link');
            rename_config_option('ocp_show_personal_adminzone_link', 'show_personal_adminzone_link');
            rename_config_option('ocp_show_personal_last_visit', 'show_personal_last_visit');
            rename_config_option('ocp_show_personal_sub_links', 'show_personal_sub_links');
            rename_config_option('ocp_show_personal_usergroup', 'show_personal_usergroup');
            rename_config_option('ocp_show_staff_page_actions', 'show_staff_page_actions');
            rename_config_option('ocp_show_su', 'show_su');
            rename_config_option('ocp_show_avatar', 'show_avatar');

            $GLOBALS['SITE_DB']->add_table_field('logged_mail_messages', 'm_extra_cc_addresses', 'LONG_TEXT', serialize([]));
            $GLOBALS['SITE_DB']->add_table_field('logged_mail_messages', 'm_extra_bcc_addresses', 'LONG_TEXT', serialize([]));
            $GLOBALS['SITE_DB']->add_table_field('logged_mail_messages', 'm_join_time', '?TIME');

            $GLOBALS['SITE_DB']->query_delete('url_title_cache');
            $GLOBALS['SITE_DB']->add_table_field('url_title_cache', 't_meta_title', 'LONG_TEXT');
            $GLOBALS['SITE_DB']->add_table_field('url_title_cache', 't_keywords', 'LONG_TEXT');
            $GLOBALS['SITE_DB']->add_table_field('url_title_cache', 't_description', 'LONG_TEXT');
            $GLOBALS['SITE_DB']->add_table_field('url_title_cache', 't_image_url', 'URLPATH');
            $GLOBALS['SITE_DB']->add_table_field('url_title_cache', 't_mime_type', 'ID_TEXT');
            $GLOBALS['SITE_DB']->add_table_field('url_title_cache', 't_json_discovery', 'URLPATH');
            $GLOBALS['SITE_DB']->add_table_field('url_title_cache', 't_xml_discovery', 'URLPATH');

            $GLOBALS['SITE_DB']->add_table_field('menu_items', 'i_include_sitemap', 'SHORT_INTEGER', 0);

            $GLOBALS['SITE_DB']->delete_table_field('zones', 'zone_displayed_in_menu');
            $GLOBALS['SITE_DB']->delete_table_field('zones', 'zone_wide');

            $GLOBALS['SITE_DB']->add_table_field('url_id_monikers', 'm_manually_chosen', 'BINARY');
            $GLOBALS['SITE_DB']->add_table_field('url_id_monikers', 'm_moniker_reversed', 'SHORT_TEXT');
            $reverse_function = db_function('REVERSE', ['m_moniker']);
            if ($reverse_function !== null) {
                $GLOBALS['SITE_DB']->query('UPDATE ' . get_table_prefix() . 'url_id_monikers SET m_moniker_reversed=' . $reverse_function);
            } else {
                $start = 0;
                $max = 500;
                do {
                    $url_id_monikers = $GLOBALS['SITE_DB']->query_select('url_id_monikers', ['DISTINCT m_moniker'], [], '', $max, $start);
                    foreach ($url_id_monikers as $url_id_moniker) {
                        $GLOBALS['SITE_DB']->query_update('url_id_monikers', ['m_moniker_reversed' => strrev($url_id_moniker['m_moniker'])], ['m_moniker' => $url_id_moniker['m_moniker']]);
                    }
                    $start += $max;
                } while (!empty($url_id_monikers));
            }
            $GLOBALS['SITE_DB']->create_index('url_id_monikers', 'uim_monrev', ['m_moniker_reversed']);

            $GLOBALS['SITE_DB']->alter_table_field('captchas', 'si_session_id', '*ID_TEXT');
            $GLOBALS['SITE_DB']->alter_table_field('captchas', 'si_code', 'ID_TEXT');
            $GLOBALS['SITE_DB']->alter_table_field('messages_to_render', 'r_session_id', 'ID_TEXT');
            $GLOBALS['SITE_DB']->alter_table_field('temp_block_permissions', 'p_session_id', 'ID_TEXT');

            $GLOBALS['SITE_DB']->delete_index_if_exists('cache', 'cached_fore2');
            $GLOBALS['SITE_DB']->delete_index_if_exists('cache', 'cached_forf');
            $GLOBALS['SITE_DB']->delete_index_if_exists('cache', 'cached_forg');
            $GLOBALS['SITE_DB']->alter_table_field('cache', 'langs_required', 'LONG_TEXT', 'dependencies');
            $GLOBALS['SITE_DB']->add_table_field('cache', 'staff_status', '?BINARY');
            $GLOBALS['SITE_DB']->add_table_field('cache', 'the_member', '?MEMBER');
            $GLOBALS['SITE_DB']->add_table_field('cache', 'the_groups', 'SHORT_TEXT');
            $GLOBALS['SITE_DB']->add_table_field('cache', 'is_bot', '?BINARY');
            $GLOBALS['SITE_DB']->add_table_field('cache', 'timezone', 'MINIID_TEXT');
            $GLOBALS['SITE_DB']->add_auto_key('cache');

            $GLOBALS['SITE_DB']->add_table_field('cron_caching_requests', 'c_staff_status', '?BINARY');
            $GLOBALS['SITE_DB']->add_table_field('cron_caching_requests', 'c_member', '?MEMBER');
            $GLOBALS['SITE_DB']->add_table_field('cron_caching_requests', 'c_groups', 'SHORT_TEXT');
            $GLOBALS['SITE_DB']->alter_table_field('cron_caching_requests', 'c_is_bot', '?BINARY');

            $GLOBALS['SITE_DB']->add_table_field('cache_on', 'special_cache_flags', 'INTEGER');

            $GLOBALS['SITE_DB']->add_table_field('comcode_pages', 'p_order', 'INTEGER');

            $GLOBALS['SITE_DB']->create_table('seo_meta_keywords', [
                'id' => '*AUTO',
                'sort_order' => 'INTEGER',
                'meta_for_type' => 'ID_TEXT',
                'meta_for_id' => 'ID_TEXT',
                'meta_keyword' => 'SHORT_TRANS',
            ]);

            $start = 0;
            $max = 300;
            do {
                $keywords = $GLOBALS['SITE_DB']->query_select('seo_meta', ['meta_for_type', 'meta_for_id', 'meta_keywords'], [], '', $max, $start);
                if ($keywords !== null) {
                    foreach ($keywords as $_keyword) {
                        $_keywords = array_unique(explode(',', trim(get_translated_text($_keyword['meta_keywords']))));
                        foreach ($_keywords as $i => $keyword) {
                            if (trim($keyword) == '') {
                                continue;
                            }

                            $GLOBALS['SITE_DB']->query_insert('seo_meta_keywords', [
                                'meta_for_type' => $_keyword['meta_for_type'],
                                'meta_for_id' => $_keyword['meta_for_id'],
                                'sort_order' => $i,
                            ] + insert_lang('meta_keyword', $keyword, 2));
                        }
                        delete_lang($_keyword['meta_keywords']);
                    }
                    $start += $max;
                }
            } while (!empty($keywords));

            $GLOBALS['SITE_DB']->delete_table_field('seo_meta', 'meta_keywords');

            $GLOBALS['SITE_DB']->drop_table_if_exists('translate_history');

            $GLOBALS['SITE_DB']->rename_table('sitewatchlist', 'staff_website_monitoring');
            $GLOBALS['SITE_DB']->alter_table_field('staff_website_monitoring', 'siteurl', 'URLPATH', 'site_url');
            $GLOBALS['SITE_DB']->rename_table('stafflinks', 'staff_links');
            $GLOBALS['SITE_DB']->rename_table('customtasks', 'staff_checklist_cus_tasks');
            $GLOBALS['SITE_DB']->alter_table_field('staff_checklist_cus_tasks', 'tasktitle', 'SHORT_TEXT', 'task_title');
            $GLOBALS['SITE_DB']->alter_table_field('staff_checklist_cus_tasks', 'datetimeadded', 'TIME', 'add_date');
            $GLOBALS['SITE_DB']->alter_table_field('staff_checklist_cus_tasks', 'recurinterval', 'INTEGER', 'recur_interval');
            $GLOBALS['SITE_DB']->alter_table_field('staff_checklist_cus_tasks', 'recurevery', 'ID_TEXT', 'recur_every');
            $GLOBALS['SITE_DB']->alter_table_field('staff_checklist_cus_tasks', 'taskisdone', '?TIME', 'done_time');

            $GLOBALS['SITE_DB']->delete_index_if_exists('member_privileges', 'mspname');
            $GLOBALS['SITE_DB']->delete_index_if_exists('member_privileges', 'mspmember_id');

            $GLOBALS['SITE_DB']->delete_index_if_exists('sessions', 'the_user');
            $GLOBALS['SITE_DB']->create_index('sessions', 'member_id', ['member_id']);

            $GLOBALS['SITE_DB']->delete_index_if_exists('url_id_monikers', 'uim_pagelink');

            $GLOBALS['SITE_DB']->delete_index_if_exists('translate', '#search');
            $GLOBALS['SITE_DB']->create_index('translate', '#tsearch', ['text_original']);

            $GLOBALS['SITE_DB']->delete_index_if_exists('sessions', 'userat');
            $GLOBALS['SITE_DB']->create_index('sessions', 'userat', ['the_zone', 'the_page', 'the_id']);

            $GLOBALS['SITE_DB']->create_index('seo_meta_keywords', 'keywords_alt_key', ['meta_for_type', 'meta_for_id']);
            $GLOBALS['SITE_DB']->create_index('seo_meta_keywords', 'ftjoin_dmeta_keywords', ['meta_keyword']);

            $GLOBALS['SITE_DB']->create_index('group_privileges', 'group_id', ['group_id']);

            $GLOBALS['SITE_DB']->delete_index_if_exists('cache', 'cached_forf');

            $GLOBALS['SITE_DB']->delete_index_if_exists('url_title_cache', 't_url');
            $GLOBALS['SITE_DB']->create_index('url_title_cache', 't_url', ['t_url']);
        }

        if (($upgrade_from === null) || ($upgrade_from < 17)) {
            $GLOBALS['SITE_DB']->create_table('alternative_ids', [ // Needs to be first, as install_create_custom_field needs it
                'resource_type' => '*ID_TEXT',
                'resource_id' => '*ID_TEXT',
                'resource_moniker' => 'ID_TEXT',
                'resource_label' => 'SHORT_TEXT',
                'resource_guid' => 'ID_TEXT',
                'resource_resource_fs_hook' => 'ID_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('alternative_ids', 'resource_guid', ['resource_guid']);
            $GLOBALS['SITE_DB']->create_index('alternative_ids', 'resource_label', ['resource_label'/*, 'resource_type'key would be too long*/]);
            $GLOBALS['SITE_DB']->create_index('alternative_ids', 'resource_moniker', ['resource_moniker', 'resource_type']);
            //$GLOBALS['SITE_DB']->create_index('alternative_ids', 'resource_label_uniqueness', ['resource_label', 'resource_resource_fs_hook']);key would be too long
            $GLOBALS['SITE_DB']->create_index('alternative_ids', 'resource_moniker_uniq', ['resource_moniker', 'resource_resource_fs_hook']);

            add_privilege('SUBMISSION', 'edit_meta_fields');
            add_privilege('SUBMISSION', 'perform_webstandards_check_by_default');

            $GLOBALS['SITE_DB']->create_table('email_bounces', [
                'id' => '*AUTO',
                'b_email_address' => 'SHORT_TEXT',
                'b_time' => 'TIME',
                'b_subject' => 'SHORT_TEXT',
                'b_body' => 'LONG_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('email_bounces', 'b_email_address', ['b_email_address']);
            $GLOBALS['SITE_DB']->create_index('email_bounces', 'b_time', ['b_time']);

            $GLOBALS['SITE_DB']->create_table('content_privacy', [
                'content_type' => '*ID_TEXT',
                'content_id' => '*ID_TEXT',
                'guest_view' => 'BINARY',
                'member_view' => 'BINARY',
                'friend_view' => 'BINARY',
            ]);
            $GLOBALS['SITE_DB']->create_index('content_privacy', 'guest_view', ['guest_view']);
            $GLOBALS['SITE_DB']->create_index('content_privacy', 'member_view', ['member_view']);
            $GLOBALS['SITE_DB']->create_index('content_privacy', 'friend_view', ['friend_view']);
            $GLOBALS['SITE_DB']->create_table('content_privacy__members', [
                'content_type' => '*ID_TEXT',
                'content_id' => '*ID_TEXT',
                'member_id' => '*MEMBER',
            ]);
            add_privilege('SUBMISSION', 'view_private_content', false, true);

            $GLOBALS['SITE_DB']->create_table('task_queue', [
                'id' => '*AUTO',
                't_title' => 'SHORT_TEXT',
                't_hook' => 'ID_TEXT',
                't_args' => 'LONG_TEXT',
                't_member_id' => 'MEMBER',
                't_secure_ref' => 'ID_TEXT', // Used like a temporary password to initiate the task
                't_send_notification' => 'BINARY',
                't_locked' => 'BINARY',
                't_add_time' => 'TIME',
            ]);

            require_code('users_active_actions');
            $admin_user = get_first_admin_user();

            require_code('global4');

            $GLOBALS['SITE_DB']->query_delete('comcode_pages', [
                'the_zone' => 'site',
                'the_page' => 'userguide_comcode',
            ], '', 1);
            $GLOBALS['SITE_DB']->query_insert('comcode_pages', [
                'the_zone' => 'site',
                'the_page' => 'userguide_comcode',
                'p_parent_page' => 'help',
                'p_validated' => 1,
                'p_edit_date' => null,
                'p_add_date' => time(),
                'p_submitter' => $admin_user,
                'p_show_as_edit' => 0,
                'p_include_on_sitemap' => comcode_page_include_on_sitemap('site', 'userguide_comcode') ? 1 : 0,
                'p_order' => 0,
            ], false, true);

            $GLOBALS['SITE_DB']->query_delete('comcode_pages', [
                'the_zone' => '',
                'the_page' => 'keymap',
            ], '', 1);
            $GLOBALS['SITE_DB']->query_insert('comcode_pages', [
                'the_zone' => '',
                'the_page' => 'keymap',
                'p_parent_page' => 'help',
                'p_validated' => 1,
                'p_edit_date' => null,
                'p_add_date' => time(),
                'p_submitter' => $admin_user,
                'p_show_as_edit' => 0,
                'p_include_on_sitemap' => comcode_page_include_on_sitemap('', 'keymap') ? 1 : 0,
                'p_order' => 0,
            ], false, true);

            $GLOBALS['SITE_DB']->create_index('cached_comcode_pages', '#page_search__combined', ['cc_page_title', 'string_index'], null, true);

            $GLOBALS['SITE_DB']->create_table('sitemap_cache', [
                'page_link' => '*SHORT_TEXT',
                'set_number' => 'INTEGER',
                'add_date' => '?TIME',
                'edit_date' => '?TIME',
                'last_updated' => 'TIME',
                'is_deleted' => 'BINARY',
                'priority' => 'REAL',
                'refreshfreq' => 'ID_TEXT',
                'guest_access' => 'BINARY',
            ], false, false, true);
            $GLOBALS['SITE_DB']->create_index('sitemap_cache', 'set_number', ['set_number', 'last_updated']);
            $GLOBALS['SITE_DB']->create_index('sitemap_cache', 'last_updated', ['last_updated']);
            $GLOBALS['SITE_DB']->create_index('sitemap_cache', 'is_deleted', ['is_deleted']);

            $GLOBALS['SITE_DB']->create_table('urls_checked', [
                'id' => '*AUTO',
                'url' => 'LONG_TEXT', // Support arbitrary length
                'url_exists' => 'BINARY',
                'response_message' => 'SHORT_TEXT',
                'url_destination_url' => 'URLPATH',
                'url_check_time' => 'TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('urls_checked', 'url', ['url(200)']);

            $GLOBALS['SITE_DB']->create_table('content_regions', [
                'content_type' => '*ID_TEXT',
                'content_id' => '*ID_TEXT',
                'region' => '*ID_TEXT',
            ]);

            $GLOBALS['SITE_DB']->create_table('unbannable_ip', [
                'ip' => '*IP',
                'note' => 'SHORT_TEXT',
            ]);

            $GLOBALS['SITE_DB']->create_index('member_privileges', 'member_privileges_name', ['privilege', 'the_page', 'module_the_name', 'category_name']);
            $GLOBALS['SITE_DB']->create_index('member_privileges', 'member_privileges_member', ['member_id']);

            $GLOBALS['SITE_DB']->create_index('url_id_monikers', 'uim_page_link', ['m_resource_page', 'm_resource_type', 'm_resource_id']);

            $GLOBALS['SITE_DB']->create_index('member_tracking', 'mt_time', ['mt_time']);

            $GLOBALS['SITE_DB']->create_index('edit_pings', 'edit_pings_on', ['the_page', 'the_type', 'the_id']);

            $GLOBALS['SITE_DB']->create_index('comcode_pages', 'p_order', ['p_order']);

            $GLOBALS['SITE_DB']->create_index('cache', 'cached_forf', ['cached_for', 'identifier', 'the_theme', 'lang', 'staff_status', 'the_member'/*, 'the_groups'So key is not too long*/, 'is_bot'/*, 'timezone'So key is not too long*/]);

            $GLOBALS['SITE_DB']->create_table('post_tokens', [
                'token' => '*ID_TEXT',
                'generation_time' => 'TIME',
                'member_id' => 'MEMBER',
                'session_id' => 'ID_TEXT',
                'ip_address' => 'IP',
                'usage_tally' => 'INTEGER',
            ]);
            $GLOBALS['SITE_DB']->create_index('post_tokens', 'generation_time', ['generation_time']);
        }

        if (($upgrade_from !== null) && ($upgrade_from < 18)) { // LEGACY 10.0.46
            $GLOBALS['FORUM_DRIVER']->install_edit_custom_field('smart_topic_notification', 'smart_topic_notification', 20, /*locked=*/1, /*viewable=*/0, /*settable=*/1, /*required=*/0, '', 'tick', 0, '0');
        }

        if (($upgrade_from === null) || ($upgrade_from < 19)) {
            $GLOBALS['SITE_DB']->create_index('digestives_tin', 'from_member_id', ['d_from_member_id']);
            $GLOBALS['SITE_DB']->create_index('cache', 'the_member', ['the_member']);
            $GLOBALS['SITE_DB']->create_index('rating', 'rating_member', ['rating_member']);
            $GLOBALS['SITE_DB']->create_index('attachment_refs', 'attachmentreferences', ['r_referer_type', 'r_referer_id']);
            $GLOBALS['SITE_DB']->create_index('notifications_enabled', 'who_has', ['l_notification_code', 'l_code_category(10)', 'l_setting']); // l_code_category is not enough as may be searched as ''

            $GLOBALS['SITE_DB']->create_table('cron_progression', [
                'c_hook' => '*ID_TEXT',
                'c_last_run_time' => '?TIME',
                'c_last_execution_secs' => '?INTEGER',
                'c_last_error' => 'LONG_TEXT',
                'c_enabled' => 'BINARY',
            ]);

            $GLOBALS['SITE_DB']->create_table('translation_cache', [
                'id' => '*AUTO',
                't_lang_from' => 'LANGUAGE_NAME',
                't_lang_to' => 'LANGUAGE_NAME',
                't_text' => 'LONG_TEXT',
                't_context' => 'INTEGER',
                't_text_result' => 'LONG_TEXT',
            ]);
            $GLOBALS['SITE_DB']->create_index('translation_cache', 'lookup', ['t_lang_from', 't_lang_to', 't_text(100)', 't_context']);
        }

        if (($upgrade_from !== null) && ($upgrade_from < 19)) { // LEGACY: 11 alpha
            $GLOBALS['SITE_DB']->drop_table_if_exists('bookmarks');

            rename_config_option('allowed_partner_sites', 'trusted_sites_2');

            rename_config_option('allow_international', 'enable_timezones');
            rename_config_option('allow_email_disable', 'member_email_receipt_configurability');
            rename_config_option('allow_email_from_staff_disable', 'staff_email_receipt_configurability');
            rename_config_option('collapse_user_zones', 'single_public_zone');
            $GLOBALS['SITE_DB']->query_update('config', ['c_value' => '2'], ['c_value' => '1', 'c_name' => 'dobs']);

            $elective_values_remap = [
                'last_health_check' => '_health_check',
                'last_catalogue_entry_timeouts_calc' => 'catalogue_entry_timeouts',
                'last_confirm_reminder_time' => 'cns_confirm_reminder',
                'last_welcome_mail_time' => 'cns_welcome_emails',
                'credit_card_cleanup_time' => 'credit_card_cleanup',
                'dynamic_firewall_time' => 'dynamic_firewall',
                'git_autopull_time' => 'git_autopull',
                'last_implicit_sync' => 'implicit_usergroup_sync',
                'mail_log_last_run_time' => 'ip_address_sharing',
                'last_cron_manual_subscription_notification' => 'manual_subscription_notification',
                'oracle_index_cleanup_last_time' => 'oracle',
                'last_sitemap_time_calc' => 'sitemap',
                'last_staff_checklist_notify' => 'staff_checklist_notify',
                'last_subscription_mail_send' => 'subscription_mails',
                'last_ticket_lead_time_calc' => 'ticket_type_lead_times',
                'last_time_cron_topic_pin' => 'topic_pin',
                'last_cron_user_sync' => 'user_sync',
                'last_newsletter_drip_send' => 'newsletter_drip_send',
            ];
            foreach ($elective_values_remap as $value => $cron_hook) {
                $GLOBALS['SITE_DB']->query_insert_or_replace('cron_progression', [
                    'c_last_run_time' => get_value($value, null, true),
                    'c_last_execution_secs' => null,
                    'c_last_error' => '',
                    'c_enabled' => 1,
                ], ['c_hook' => $cron_hook]);
            }

            $values_remap = [
                'last_classified_refresh' => 'classifieds',
                'last_disastr_time' => 'disastr',
                'last_downloads_followup_email_send' => 'downloads_followup_email',
                'last_group_points' => 'group_points',
                'last_insult_time' => 'insults',
                'last_password_censor_time' => 'password_censor',
                'last_demo_set_time' => 'site_cleanup',
                'last_thieving_time' => 'stealr',
                'last_user_export' => 'user_export',
                'last_user_import' => 'user_import',
            ];
            foreach ($values_remap as $value => $cron_hook) {
                $GLOBALS['SITE_DB']->query_insert_or_replace('cron_progression', [
                    'c_last_run_time' => get_value($value, null),
                    'c_last_execution_secs' => null,
                    'c_last_error' => '',
                    'c_enabled' => 1,
                ], ['c_hook' => $cron_hook]);
            }

            $GLOBALS['FORUM_DRIVER']->install_delete_custom_field('smart_topic_notification');

            rename_config_option('imap_folder', 'mail_folder');
            rename_config_option('imap_host', 'mail_server_host');
            rename_config_option('imap_port', 'mail_server_port');
            rename_config_option('imap_username', 'mail_username');
            rename_config_option('imap_password', 'mail_password');

            rename_config_option('email_log_days', 'email_log_store_time');

            rename_config_option('gzip_output', 'output_compression');

            rename_config_option('oembed_html_whitelist', 'oembed_html_safelist');

            rename_config_option('send_error_emails_ocproducts', 'send_error_emails_developers');

            $GLOBALS['FORUM_DB']->add_table_field('logged_mail_messages', 'm_sender_email', 'SHORT_TEXT');
            $GLOBALS['FORUM_DB']->add_table_field('logged_mail_messages', 'm_plain_subject', 'BINARY');

            $GLOBALS['SITE_DB']->query('UPDATE ' . get_table_prefix() . 'actionlogs SET param_a=param_b,param_b=param_a WHERE ' . db_string_equal_to('the_type', 'UNSILENCE_TOPIC') . ' OR ' . db_string_equal_to('the_type', 'UNSILENCE_FORUM'));

            $GLOBALS['SITE_DB']->query_update('actionlogs', ['the_type' => 'SET_PAGE_REDIRECTS'], ['the_type' => 'SET_REDIRECTS']);

            $GLOBALS['SITE_DB']->alter_table_field('cache', 'groups', 'LONG_TEXT', 'the_groups');

            if (get_value('timezone') !== null) {
                set_option('timezone', get_value('timezone'));
                delete_value('timezone');
            }

            $GLOBALS['SITE_DB']->delete_index_if_exists('notifications_enabled', 'l_member_id');

            $GLOBALS['SITE_DB']->drop_table_if_exists('staff_website_monitoring');

            $GLOBALS['SITE_DB']->drop_table_if_exists('link_tracker');

            $GLOBALS['SITE_DB']->add_table_field('comcode_pages', 'p_include_on_sitemap', 'BINARY', 1);

            $GLOBALS['SITE_DB']->add_table_field('task_queue', 't_add_time', 'TIME', time());

            $GLOBALS['SITE_DB']->add_table_field('urls_checked', 'url_message', 'SHORT_TEXT');
            $GLOBALS['SITE_DB']->add_table_field('urls_checked', 'url_destination_url', 'URLPATH');

            $GLOBALS['SITE_DB']->drop_table_if_exists('https_pages');

            $GLOBALS['SITE_DB']->query_update('db_meta', ['m_type' => '*MEMBER'], ['m_name' => 'member_id', 'm_table' => 'member_privileges'], '', 1);

            $GLOBALS['SITE_DB']->add_table_field('seo_meta_keywords', 'sort_order', 'INTEGER');
        }

        if (($upgrade_from === null) || ($upgrade_from < 19)) {
            $GLOBALS['SITE_DB']->create_index('attachment_refs', 'attachmentreferences2', ['a_id']);
            $GLOBALS['SITE_DB']->create_index('member_privileges', 'active_until', ['active_until']);
            $GLOBALS['SITE_DB']->create_index('member_zone_access', 'active_until', ['active_until']);
            $GLOBALS['SITE_DB']->create_index('member_page_access', 'active_until', ['active_until']);
            $GLOBALS['SITE_DB']->create_index('member_category_access', 'active_until', ['active_until']);
            $GLOBALS['SITE_DB']->create_index('messages_to_render', 'delete_old', ['r_time']);
            $GLOBALS['SITE_DB']->create_index('f_group_member_timeouts', 'expiring_timeouts', ['timeout']);
            $GLOBALS['SITE_DB']->create_index('group_privileges', 'by_privilege', ['privilege']);

            $GLOBALS['SITE_DB']->create_index('notifications_enabled', 'l_member_id', ['l_member_id', 'l_notification_code', 'l_code_category(10)']);

            require_code('global4');
            $rows = $GLOBALS['SITE_DB']->query_select('comcode_pages', ['the_zone', 'the_page']);
            foreach ($rows as $row) {
                $include_on_sitemap = _comcode_page_include_on_sitemap_default($row['the_zone'], $row['the_page']);
                $GLOBALS['SITE_DB']->query_update('comcode_pages', ['p_include_on_sitemap' => $include_on_sitemap ? 1 : 0], $row, '', 1);
            }

            $GLOBALS['SITE_DB']->create_table('ft_index_commonality', [
                'id' => '*AUTO',
                'c_ngram' => 'SHORT_TEXT',
                'c_commonality' => 'REAL',
            ]);

            $GLOBALS['SITE_DB']->create_index('ft_index_commonality', 'c_ngram', ['c_ngram']);
            $GLOBALS['SITE_DB']->create_index('ft_index_commonality', 'c_commonality', ['c_commonality']);

            $GLOBALS['SITE_DB']->create_table('cpages_fulltext_index', [
                'i_zone_name' => '*ID_TEXT',
                'i_page_name' => '*ID_TEXT',

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

                'i_occurrence_rate' => 'REAL',
            ]);

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

            $GLOBALS['SITE_DB']->create_index('cpages_fulltext_index', 'main', [
                'i_lang',
                'i_ngram',
                'i_ac',
                'i_occurrence_rate', // For sorting
            ]);

            $GLOBALS['SITE_DB']->create_index('cpages_fulltext_index', 'main_2', [
                'i_lang',
                'i_ngram',
                'i_occurrence_rate', // For sorting
            ]);

            $GLOBALS['SITE_DB']->create_table('daily_visits', [
                'id' => '*AUTO',
                'd_member_id' => '*MEMBER',
                'd_date_and_time' => 'TIME',
            ]);

            set_value('setupwizard_completed', get_value('setupwizard_completed', '0'), true);
            delete_value('setupwizard_completed');
        }

        if (($upgrade_from !== null) && ($upgrade_from < 20)) { // LEGACY: 11.beta1
            // Consistency changes
            $GLOBALS['SITE_DB']->alter_table_field('menu_items', 'i_parent', '?AUTO_LINK', 'i_parent_id');
            //$GLOBALS['SITE_DB']->alter_table_field('menu_items', 'i_url', 'SHORT_TEXT', 'i_link'); // Actually we do this in version_specific()
            $GLOBALS['SITE_DB']->alter_table_field('trackbacks', 'trackback_ip', 'IP', 'trackback_ip_address');
            $GLOBALS['SITE_DB']->alter_table_field('trackbacks', 'trackback_url', 'URLPATH');
            $GLOBALS['SITE_DB']->alter_table_field('url_title_cache', 't_json_discovery', 'URLPATH', 't_discovery_url_json');
            $GLOBALS['SITE_DB']->alter_table_field('url_title_cache', 't_xml_discovery', 'URLPATH', 't_discovery_url_xml');
            $GLOBALS['SITE_DB']->alter_table_field('rating', 'rating_ip', 'IP', 'rating_ip_address');
            $GLOBALS['SITE_DB']->alter_table_field('logged_mail_messages', 'm_as', 'MEMBER', 'm_as_member');
            $GLOBALS['SITE_DB']->alter_table_field('incoming_uploads', 'i_orig_filename', 'SHORT_TEXT');
            $GLOBALS['SITE_DB']->alter_table_field('incoming_uploads', 'i_save_url', 'URLPATH');
            $GLOBALS['SITE_DB']->alter_table_field('urls_checked', 'url_message', 'SHORT_TEXT', 'response_message');

            $GLOBALS['SITE_DB']->delete_index_if_exists('logged_mail_messages', 'm_as');
        }

        if (($upgrade_from === null) || ($upgrade_from < 20)) {
            $GLOBALS['SITE_DB']->create_index('logged_mail_messages', 'm_as_member', ['m_as_member']);

            $GLOBALS['SITE_DB']->create_table('unsubscribed_emails', [
                'id' => '*AUTO',
                'b_email_address' => 'SHORT_TEXT',
                'b_time' => 'TIME',
            ]);
            $GLOBALS['SITE_DB']->create_index('unsubscribed_emails', 'b_email_address', ['b_email_address']);
            $GLOBALS['SITE_DB']->create_index('unsubscribed_emails', 'b_time', ['b_time']);
        }
    }

    /**
     * Find entry-points available within this module.
     *
     * @param  boolean $check_perms Whether to check permissions
     * @param  ?MEMBER $member_id The member to check permissions as (null: current user)
     * @param  boolean $support_crosslinks Whether to allow cross links to other modules (identifiable via a full-page-link rather than a screen-name)
     * @param  boolean $be_deferential Whether to avoid any entry-point (or even return null to disable the page in the Sitemap) if we know another module, or page_group, is going to link to that entry-point. Note that "!" and "browse" entry points are automatically merged with container page nodes (likely called by page-groupings) as appropriate.
     * @return ?array A map of entry points (screen-name=>language-code/string or screen-name=>[language-code/string, icon-theme-image]) (null: disabled)
     */
    public function get_entry_points(bool $check_perms = true, ?int $member_id = null, bool $support_crosslinks = true, bool $be_deferential = false) : ?array
    {
        return [
            'browse' => ['PROJECT_CONTRIBUTORS', 'menu/adminzone/sponsors'],
        ];
    }

    public $title;

    /**
     * Module pre-run function. Allows us to know metadata for <head> before we start streaming output.
     *
     * @return ?Tempcode Tempcode indicating some kind of exceptional output (null: none)
     */
    public function pre_run() : ?object
    {
        require_lang('version');
        $this->title = get_screen_title('PROJECT_CONTRIBUTORS');

        return null;
    }

    /**
     * Execute the module.
     *
     * @return Tempcode The result of execution
     */
    public function run() : object
    {
        // Patreon
        $level = get_param_integer('level', 50);
        $patreon_patrons = [];
        $data = cms_http_request(get_brand_base_url() . '/data_custom/patreon_patrons.php?level=' . strval($level), ['convert_to_internal_encoding' => true, 'trigger_error' => false]);
        if ($data->data !== null) {
            $_patreon_patrons = json_decode($data->data);
            foreach ($_patreon_patrons as $patron) {
                $patreon_patrons[] = [
                    'NAME' => $patron['name'],
                    'USERNAME' => $patron['username'],
                ];
            }
        }

        // Direct contributors
        $contributors = [
            'Patrick Schmalstig' => ['AREAS' => ['Lead developer', 'Composr Core Developer']],
            'Christopher Graham' => ['AREAS' => ['Senior developer', 'Composr Core Developer']],
            'Adam Edington' => ['AREAS' => ['Moderator of the Composr site']],
            'PDStig, LLC' => ['AREAS' => ['Web hosting for composr.app']],
        ];
        require_code('files_spreadsheets_read');
        $sheet_reader = spreadsheet_open_read(get_file_base() . '/data/maintenance_status.csv');
        while (($row = $sheet_reader->read_row()) !== false) {
            /*if (!empty($row['Bug-fix sponsor'])) { Don't actually want to list bug-fix sponsors
                if (!isset($sponsors[$row['Bug-fix sponsor']])) {
                    $sponsors[$row['Bug-fix sponsor']] = ['AREAS' => []];
                }
                $sponsors[$row['Bug-fix sponsor']]['AREAS'][] = $row['Title'];
            }*/
            if (!empty($row['Actively Maintained by'])) {
                if (!isset($contributors[$row['Actively Maintained by']])) {
                    $contributors[$row['Actively Maintained by']] = ['AREAS' => []];
                }
                $contributors[$row['Actively Maintained by']]['AREAS'][] = $row['Title'];
            }
        }
        $sheet_reader->close();
        foreach ($contributors as $sponsor => &$areas) {
            $areas['AREAS'] = array_unique($areas['AREAS']);
            //cms_mb_sort($areas['AREAS'], SORT_NATURAL | SORT_FLAG_CASE); Actually order is meaningful
        }

        return do_template('CONTRIBUTORS_SCREEN', [
            '_GUID' => '504a3975e168ac7d32ed78f3fadf2cbe',
            'TITLE' => $this->title,
            'PATREON_PATRONS' => $patreon_patrons,
            'CONTRIBUTORS' => $contributors,
        ]);
    }
}
