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

/*EXTRA FUNCTIONS: CRC24*/

/**
 * Standard code module initialisation function.
 *
 * @ignore
 */
function init__database_search()
{
    $GLOBALS['TOTAL_SEARCH_RESULTS'] = 0;

    if (!defined('MAXIMUM_RESULT_COUNT_POINT')) {
        if (addon_installed('search')) {
            $maximum_result_count_point = get_option('search_maximum_result_count_point');
            define('MAXIMUM_RESULT_COUNT_POINT', intval($maximum_result_count_point));
        } else {
            define('MAXIMUM_RESULT_COUNT_POINT', 1000);
        }
    }

    if (!defined('APPEARANCE_CONTEXT_TITLE')) {
        define('APPEARANCE_CONTEXT_TITLE', 1);
        define('APPEARANCE_CONTEXT_META', 2);
        define('APPEARANCE_CONTEXT_BODY', 3); // Nothing will be indexed in here that is indexed in one of the above, to avoid duplicated content records when doing non-appearance-filtered searches
    }
}

/**
 * Server opensearch requests.
 */
function opensearch_script()
{
    if (!addon_installed('search')) {
        warn_exit(do_lang_tempcode('MISSING_ADDON', escape_html('search')));
    }

    if (!has_actual_page_access(get_member(), 'search')) {
        return; // No access
    }

    $type = get_param_string('type', 'browse');
    switch ($type) {
        // Make a search suggestion (like Firefox's Awesome Bar)
        case 'suggest':
            require_code('search');

            header('Content-Type: application/x-suggestions+json; charset=' . get_charset());
            $request = get_param_string('request', false, INPUT_FILTER_GET_COMPLEX);

            $suggestions = find_search_suggestions($request);

            require_lang('search');

            cms_ini_set('ocproducts.xss_detect', '0');

            // JSON format
            echo '[' . "\n";
            // Original request
            echo '"' . php_addslashes($request) . '",' . "\n";

            // Suggestions
            echo '[';
            foreach ($suggestions as $i => $suggestion) {
                if ($i != 0) {
                    echo ',';
                }
                echo '"' . php_addslashes($suggestion) . '"';
            }
            echo '],' . "\n";

            // Descriptions of suggestions
            echo '[';
            foreach (array_values($suggestions) as $i => $suggestion) {
                if ($i != 0) {
                    echo ',';
                }
                echo '"' . php_addslashes(do_lang('NUM_RESULTS', integer_format($suggestion, 0))) . '"';
            }
            echo '],' . "\n";

            // URLs to search suggestions
            $filter = get_param_string('filter', '', INPUT_FILTER_GET_COMPLEX);
            $filter_map = [];
            if ($filter != '') {
                foreach (explode(':', $filter) as $f) {
                    if ($f != '') {
                        $parts = explode('=', $f, 2);
                        if (count($parts) == 1) {
                            $parts = [$parts[0], '1'];
                        }
                        $filter_map[$parts[0]] = $parts[1];
                    }
                }
            }
            echo '[';
            foreach (array_keys($suggestions) as $i => $suggestion) {
                if (is_integer($suggestion)) {
                    $suggestion = strval($suggestion);
                }

                if ($i != 0) {
                    echo ',';
                }
                $map = ['page' => 'search', 'type' => 'results', 'content' => $suggestion] + $filter_map;
                $_search_url = build_url($map, get_param_string('zone', get_module_zone('search')));
                $search_url = $_search_url->evaluate();
                echo '"' . php_addslashes($search_url) . '"';
            }
            echo ']' . "\n";
            echo ']' . "\n";
            break;

        // Provide details about the site search engine
        default:
            // Ideally we would use application/opensearchdescription+xml not text/xml, but that doesn't work consistently
            prepare_backend_response('text/xml', BACKEND_RESPONSE_CSP_SUPER_STRICT);

            $tpl = do_template('OPENSEARCH', ['_GUID' => '1fe46743805ade5958dcba0d58c4b0f2', 'DESCRIPTION' => get_option('description')], null, false, null, '.xml', 'xml');
            $tpl->evaluate_echo();
            break;
    }
}

/**
 * Find if we can use the fast custom index.
 *
 * @param  string $hook The hook it is for (assumption that this hook is at least capable in some situations)
 * @param  ?object $db Database connection (null: do not do indexed-already check)
 * @param  ?string $index_table Index table (null: do not do indexed-already check)
 * @param  ?string $search_query Search uery to run for (null: no query to check at this point)
 * @param  ?boolean $has_heavy_filtering Whether there is heavy filtering (which suggests to use fast custom index) (null: unknown at this point)
 * @return boolean Whether we can
 */
function can_use_fast_custom_index(string $hook, ?object $db = null, ?string $index_table = null, ?string $search_query = null, ?bool $has_heavy_filtering = null) : bool
{
    if ($search_query !== null) {
        $tokeniser = Fast_custom_index::get_tokeniser(user_lang());
        $ngrams = $tokeniser->query_to_search_tokens($search_query);
    }

    // Negative, cannot use for these reasons...

    if (($search_query !== null) && ($search_query == '')) {
        return false; // Blank queries not supported
    }

    if (($search_query !== null) && (Fast_custom_index::max_ngram_size(user_lang()) <= 1)) {
        if (array_unique(array_values($ngrams[0] + $ngrams[1] + $ngrams[2])) !== [true]) {
            return false; // Quoted text not supported in this configuration (because there are only singular ngrams being indexed)
        }
    }

    if (!cron_installed()) {
        return false; // No indexing working
    }

    if (($db !== null) && ($index_table !== null)) {
        if ($db->query_select_value_if_there($index_table, 'i_ngram') === null) {
            return false; // Nothing yet indexed
        }
    }

    // Explicit interactive choice...

    $by_url = get_param_integer('keep_fast_custom_index', null);
    if ($by_url !== null) {
        return ($by_url == 1); // Explicitly specified by URL
    }

    // Positive, should use for these reasons...

    if ($search_query !== null) {
        $_trigger_ngrams = get_option('fast_custom_index__enable_for_ngrams');
        $trigger_ngrams = ($_trigger_ngrams == '') ? [] : array_map('cms_mb_strtolower', array_map('trim', explode(',', $_trigger_ngrams)));

        if (!empty($trigger_ngrams)) {
            if (!empty(array_intersect(array_map('cms_mb_strtolower', array_keys($ngrams)), $trigger_ngrams))) {
                return true; // We will use the fast custom index if there's certain trigger ngrams
            }
        }
    }

    if (($has_heavy_filtering === true) && (get_option('fast_custom_index__enable_for_filtered') == '1')) {
        return true; // We will use the fast custom index if there's heavy filtering as there'll be a big speed boost
    }

    if ((!$GLOBALS['SITE_DB']->has_full_text()) && (get_option('fast_custom_index__enable_for_no_fulltext') == '1')) {
        return true; // No full-text support in database
    }

    if (($search_query !== null) && (is_under_radar($search_query)) && (get_option('fast_custom_index__enable_for_under_radar') == '1')) {
        return true; // Query is very short
    }

    if (($search_query !== null) && (count($ngrams) >= intval(get_option('fast_custom_index__enable_for_minimum_ngram_count')))) {
        return true; // Query is very long
    }

    // Explicit configured choice...

    $default_choice = get_value('fast_custom_index__enable_for__' . $hook, '');
    if ($default_choice != '') {
        return ($default_choice == '1'); // Explicitly specified by config for this hook
    }

    $default_choice = get_value('fast_custom_index__enable_for__' . user_lang(), '');
    if ($default_choice != '') {
        return ($default_choice == '1'); // Explicitly specified by config for current language
    }

    return (get_option('fast_custom_index__enable') == '1');
}

/**
 * The fast custom index search engine.
 *
 * @package search
 */
class Fast_custom_index
{
    // Querying...

    /**
     * Get some rows, queried from the database according to the search parameters, using the fast custom index.
     *
     * @param  object $db Database connection
     * @param  string $index_table Table containing our custom index
     * @param  string $content_table The content table to query (may contain JOIN components)
     * @param  array $key_transfer_map A map to show how keys relate between the index table and the content table, used to construct a JOIN
     * @param  string $where_clause Special WHERE clause querying things from content table (i.e. stuff not covered in our index)
     * @param  string $extra_join_clause Extra SQL to insert into the JOIN clauses of each keyword, used to enforce query constraints handled within our indexing
     * @param  string $search_query Search query
     * @param  boolean $only_search_meta Whether to only do a META (tags) search
     * @param  boolean $only_titles Whether to only search titles (as opposed to both titles and content)
     * @param  integer $max Start position in total results
     * @param  integer $start Maximum results to return in total
     * @param  ID_TEXT $order What to order by
     * @param  ID_TEXT $direction Order direction
     * @param  ?string $permissions_module The permission module to check category access for (null: none)
     * @param  ?string $index_permissions_field The field that specifies the permissions ID to check category access for (null: none)
     * @param  boolean $permissions_field_is_string Whether the permissions field is a string
     * @param  ?string $force_index Force a specific index to be used (null: none)
     * @return array The rows found
     */
    public function get_search_rows(object $db, string $index_table, string $content_table, array $key_transfer_map, string $where_clause, string $extra_join_clause, string $search_query, bool $only_search_meta, bool $only_titles, int $max, int $start, string $order, string $direction, ?string $permissions_module = null, ?string $index_permissions_field = null, bool $permissions_field_is_string = false, ?string $force_index = null) : array
    {
        if ($only_search_meta) {
            $appearance_context = APPEARANCE_CONTEXT_META;
        } elseif ($only_titles) {
            $appearance_context = APPEARANCE_CONTEXT_TITLE;
        } else {
            $appearance_context = null;
        }

        if (preg_match('#_fulltext_index$#', $index_table) == 0) {
            fatal_exit('Attempted to use a non-fulltext database table (did not end in _fulltext_index) for fast custom index searching');
        }

        // Load configuration
        global $SEARCH_CONFIG_OVERRIDE;
        if (isset($SEARCH_CONFIG_OVERRIDE['fast_custom_index__allow_fuzzy_search'])) {
            $allow_fuzzy_search = ($SEARCH_CONFIG_OVERRIDE['fast_custom_index__allow_fuzzy_search'] == '1');
        } else {
            $allow_fuzzy_search = (get_option('fast_custom_index__allow_fuzzy_search') == '1');
        }
        if (isset($SEARCH_CONFIG_OVERRIDE['fast_custom_index__scale_by_commonality'])) {
            $scale_by_commonality = ($SEARCH_CONFIG_OVERRIDE['fast_custom_index__scale_by_commonality'] == '1');
        } else {
            $scale_by_commonality = (get_option('fast_custom_index__scale_by_commonality') == '1');
        }
        if (isset($SEARCH_CONFIG_OVERRIDE['fast_custom_index__use_imprecise_ordering'])) {
            $use_imprecise_ordering = ($SEARCH_CONFIG_OVERRIDE['fast_custom_index__use_imprecise_ordering'] == '1');
        } else {
            $use_imprecise_ordering = (get_option('fast_custom_index__use_imprecise_ordering') == '1');
        }

        $lang = user_lang();

        $tokeniser = self::get_tokeniser($lang);
        $stemmer = self::get_stemmer($lang);

        // We start with the content table, which is needed for things like validation checks, or other stuff that basically won't be a dominant index culling factor
        $join = $content_table;
        $count_table = '';

        $extra_where_clause = '';
        $count_extra_where_clause = '';

        if (($order == '') || ($order == 'relevance')) {
            $order = 'contextual_relevance';
        }

        // Work out our search terms
        list($fuzzy_and, $and, $not) = $tokeniser->query_to_search_tokens($search_query);
        if (($order != 'contextual_relevance') || ($direction == 'ASC') || (!$allow_fuzzy_search)) {
            // We only allow fuzzy search when returning results in relevance order, with most relevant first
            //  - this is because it makes no sense for any other orders
            // We also allow it to be disabled as it could have major performance slow-down for large data-sets, it's a lot to pull out from the index, join, and sort.
            $and = $and + $fuzzy_and;
            $fuzzy_and = [];
        }
        $search_token_sets = [
            'fuzzy_and' => $fuzzy_and,
            'and' => $and,
            'not' => $not,
        ];

        // Load commonalities so we can scale by them
        if (((!empty($fuzzy_and)) || (!empty($and))) && ($scale_by_commonality)) {
            $commonalities = $this->load_commonalities($db, array_keys($fuzzy_and + $and));

            // Order search terms by commonality
            $_fuzzy_and = [];
            foreach ($commonalities as $ngram => $commonality) {
                if (is_integer($ngram)) {
                    $ngram = strval($ngram);
                }

                if (isset($fuzzy_and[$ngram])) {
                    $_fuzzy_and[$ngram] = $fuzzy_and[$ngram];
                }
            }
            $fuzzy_and = $_fuzzy_and;
            $_and = [];
            foreach ($commonalities as $ngram => $commonality) {
                if (is_integer($ngram)) {
                    $ngram = strval($ngram);
                }

                if (isset($and[$ngram])) {
                    $_and[$ngram] = $and[$ngram];
                }
            }
            $and = $_and;
        } else {
            $commonalities = null;
        }

        // Code for considering permissions
        if (($permissions_module !== null) && (!$GLOBALS['FORUM_DRIVER']->is_super_admin(get_member()))) {
            $g_or = get_permission_where_clause_groups(get_member());

            // this destroys mysqls query optimiser by forcing complexed OR's into the join, so we'll do this in PHP code
            /*$table .= ' LEFT JOIN ' . $db->get_table_prefix() . 'group_category_access z ON (' . db_string_equal_to('z.module_the_name', $permissions_module) . ' AND z.category_name=' . $permissions_field . (($g_or != '') ? (' AND ' . str_replace('group_id', 'z.group_id', $g_or)) : '') . ')';
            $extra_where_clause .= ' AND ';
            $extra_where_clause .= 'z.category_name IS NOT NULL';*/

            $cat_sql = '';
            $cat_sql .= 'SELECT DISTINCT category_name FROM ' . $db->get_table_prefix() . 'group_category_access WHERE (' . $g_or . ') AND ' . db_string_equal_to('module_the_name', $permissions_module);
            $cat_sql .= ' UNION ALL ';
            $cat_sql .= 'SELECT DISTINCT category_name FROM ' . $db->get_table_prefix() . 'member_category_access WHERE (member_id=' . strval(get_member()) . ' AND active_until>' . strval(time()) . ') AND ' . db_string_equal_to('module_the_name', $permissions_module);
            $cat_access = array_keys(list_to_map('category_name', $db->query($cat_sql, null, 0, false, true)));

            if (empty($cat_access)) {
                return [];
            }

            $extra_join_clause .= ' AND ixxx.' . $index_permissions_field . ' IN (';
            foreach ($cat_access as $i => $cat) {
                if (is_integer($cat)) {
                    $cat = strval($cat);
                }

                if ($i != 0) {
                    $extra_join_clause .= ',';
                }
                if ($permissions_field_is_string) {
                    $extra_join_clause .= '\'' . db_escape_string($cat) . '\'';
                } else {
                    if (is_numeric($cat)) {
                        $extra_join_clause .= $cat;
                    }
                    // else should not be possible
                }
            }
            $extra_join_clause .= ')';
        }

        // Code for querying against each ngram
        $order_by_total_ngrams_matched = '';
        $order_by_occurrence_rates = '';
        $is_all_hard_joins = true;
        $i = 0;
        $open_brackets = 0;
        foreach ($search_token_sets as $set_type => $search_tokens) {
            foreach ($search_tokens as $ngram => $is_singular_ngram) {
                if (is_integer($ngram)) {
                    $ngram = strval($ngram);
                }

                if ($is_singular_ngram) {
                    if ($this->singular_ngram_is_stop_word($ngram, $lang)) {
                        continue;
                    }

                    if (is_object($stemmer)) {
                        $ngram = $stemmer->stem($ngram);
                    }
                }

                $join_condition = '';
                $join_condition_keys = '';
                $key_i = 0;
                foreach ($key_transfer_map as $content_table_field => $index_table_field) {
                    $join_condition_keys .= ' AND ';
                    if ($i == 0) {
                        $join_condition_keys .= 'i' . strval($i) . '.' . $index_table_field . '=r.' . $content_table_field;
                    } else {
                        $join_condition_keys .= 'i' . strval($i) . '.' . $index_table_field . '=i0.' . $index_table_field;
                        break;
                    }
                    $key_i++;
                }
                $join_condition .= 'i' . strval($i) . '.i_ngram=' . strval($this->crc($ngram));
                $join_condition .= '/*' . str_replace('/', '\\', $ngram) . '*/';
                $join_condition .= str_replace('ixxx.', 'i' . strval($i) . '.', $extra_join_clause);
                $join_condition .= ' AND ' . db_string_equal_to('i' . strval($i) . '.i_lang', $lang);
                if ($appearance_context !== null) {
                    $join_condition .= ' AND i_ac=' . strval($appearance_context);
                }

                if (($i == 0) || (!$use_imprecise_ordering) || ($set_type == 'fuzzy_and')) {
                    if ($set_type == 'and') {
                        $join_type = 'JOIN'; // Will enforce the AND implicitly
                    } else {
                        $join_type = 'LEFT JOIN';
                        $is_all_hard_joins = false;
                    }
                    $join .= ' ' . $join_type . ' ' . $db->get_table_prefix() . $index_table . ' i' . strval($i);
                    if ($force_index !== null) {
                        $join .= $db->prefer_index($index_table, $force_index, false);
                    }
                    $join .= ' ON ' . $join_condition . $join_condition_keys;
                    if ($count_table == '') {
                        $count_table = $db->get_table_prefix() . $index_table . ' i' . strval($i);
                    } else {
                        $count_table .= ' ' . $join_type . ' ' . $db->get_table_prefix() . $index_table . ' i' . strval($i) . ' ON ' . $join_condition . $join_condition_keys;
                    }
                    $count_extra_where_clause .= ' AND ' . $join_condition;

                    if ($set_type == 'not') {
                        $extra_where_clause .= ' AND i' . strval($i) . '.i_ngram IS NULL';
                    }
                } else {
                    $extra_where_clause .= ' AND ' . (($set_type == 'and') ? 'EXISTS' : 'NOT EXISTS') . ' (SELECT * FROM ' . $db->get_table_prefix() . $index_table . ' i' . strval($i) . ' WHERE ' . $join_condition;
                    $open_brackets++; // We keep opening up more brackets to stop the MySQL query optimiser doing whacky things, partly executing random sub-queries into temporary tables before the first join
                }

                if (($set_type != 'not') && ($order_by_total_ngrams_matched != '')) {
                    $order_by_total_ngrams_matched .= '+';

                    if (!$use_imprecise_ordering) {
                        $order_by_occurrence_rates .= '+';
                    }
                }

                if ($set_type != 'not') {
                    $order_by_total_ngrams_matched .= db_function('IFF', ['i' . strval($i) . '.i_ngram IS NULL', '0', '1']);

                    if (!$use_imprecise_ordering) {
                        $scaler = (isset($commonalities[$ngram]) ? float_to_raw_string($commonalities[$ngram], 10) : '1');
                        $order_by_occurrence_rates .= db_function('COALESCE', ['i' . strval($i) . '.i_occurrence_rate', '0']) . '*' . $scaler;
                    } elseif ($order_by_occurrence_rates == '') {
                        $order_by_occurrence_rates = 'i' . strval($i) . '.i_occurrence_rate';
                    }
                }

                $i++;
            }
        }
        while ($open_brackets > 0) {
            $open_brackets--;
            $extra_where_clause .= ')';
        }

        if ($i == 0) {
            // Useful for automated testing
            global $LAST_SEARCH_QUERY, $LAST_COUNT_QUERY;
            $LAST_SEARCH_QUERY = 'N/A';
            $LAST_COUNT_QUERY = 'N/A';

            // This is important - if there are no ngrams to index against, then security will not have run either
            return [];
        }

        if (!$is_all_hard_joins) {
            $extra_where_clause .= ' AND ' . $order_by_total_ngrams_matched . '>0';
        }

        // Do querying...

        if ($use_imprecise_ordering) {
            $contextual_relevance_sql = $order_by_occurrence_rates; // Will just be the occurrence rate of the least common term
        } else {
            $contextual_relevance_sql = $order_by_total_ngrams_matched . ((($order_by_total_ngrams_matched == '') || ($order_by_occurrence_rates == '')) ? '' : '+') . $order_by_occurrence_rates; // $order_by_total_ngrams_matched will be the dominant factor (intended!) as it is an integer while $order_by_occurrence_rates cannot add to more than 1
        }
        if ($contextual_relevance_sql == '') {
            $contextual_relevance_sql = '1';
        }
        $select = 'r.*,' . $contextual_relevance_sql . ' AS contextual_relevance';

        // Rating ordering, via special encoding
        if (strpos($order, 'compound_rating:') !== false) {
            list(, $rating_type, $meta_rating_id_field) = explode(':', $order);
            $select .= ',(SELECT SUM(rating-1) FROM ' . $db->get_table_prefix() . 'rating WHERE ' . db_string_equal_to('rating_for_type', $rating_type) . ' AND rating_for_id=' . db_cast($meta_rating_id_field, 'CHAR') . ') AS compound_rating';
            $order = 'compound_rating';
        }
        if (strpos($order, 'average_rating:') !== false) {
            list(, $rating_type, $meta_rating_id_field) = explode(':', $order);
            $select .= ',(SELECT AVG(rating) FROM ' . $db->get_table_prefix() . 'rating WHERE ' . db_string_equal_to('rating_for_type', $rating_type) . ' AND rating_for_id=' . db_cast($meta_rating_id_field, 'CHAR') . ') AS average_rating';
            $order = 'average_rating';
        }

        $t_rows_sql = 'SELECT ' . $select . ' FROM ' . $join . ' WHERE 1=1' . $where_clause . $extra_where_clause . ' ORDER BY ' . $order . ' ' . $direction;

        if (($GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())) || ($GLOBALS['IS_ACTUALLY_ADMIN']) || ($GLOBALS['DEV_MODE'])) {
            if (get_param_integer('keep_show_query', 0) == 1) {
                attach_message($t_rows_sql, 'inform');
            }
            if (get_param_integer('keep_just_show_query', 0) == 1) {
                cms_ini_set('ocproducts.xss_detect', '0');
                @header('Content-Type: text/plain; charset=' . get_charset());
                exit($t_rows_sql);
            }
        }

        // Useful for automated testing
        global $LAST_SEARCH_QUERY, $LAST_COUNT_QUERY;

        $LAST_SEARCH_QUERY = $t_rows_sql;
        $t_rows = $db->query(remove_unneeded_joins_rough($t_rows_sql), $max, $start);

        $t_count_sql = '(SELECT COUNT(*) FROM (';
        if (get_option('fast_custom_index__count_estimate') == '1') {
            $t_count_sql .= 'SELECT 1 FROM ' . $count_table . ' WHERE 1=1' . $extra_where_clause . $count_extra_where_clause;
        } else {
            $t_count_sql .= 'SELECT 1 FROM ' . $join . ' WHERE 1=1' . $where_clause . $extra_where_clause;
        }
        $t_count_sql .= ' LIMIT ' . strval(MAXIMUM_RESULT_COUNT_POINT) . ') counter)';
        $LAST_COUNT_QUERY = $t_count_sql;
        $t_count = $db->query_value_if_there(remove_unneeded_joins_rough($t_count_sql));
        $GLOBALS['TOTAL_SEARCH_RESULTS'] += $t_count;

        if ((get_param_integer('keep_show_query', 0) == 1) && (($GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())) || ($GLOBALS['IS_ACTUALLY_ADMIN']))) {
            if ((array_key_exists(0, $t_rows)) && (array_key_exists('id', $t_rows[0]))) {
                $results = var_export(array_unique(collapse_1d_complexity('id', $t_rows)), true);
            } else {
                $results = var_export($t_rows, true);
            }
            attach_message(do_lang('COUNT_RESULTS') . ': ' . $results, 'inform');
        }

        return $t_rows;
    }

    /**
     * Find if we have to join in custom fields due to filtering.
     *
     * @return boolean Whether we have special filtering
     */
    public static function active_search_has_special_filtering() : bool
    {
        foreach ($_GET as $key => $val) {
            if ((is_string($key)) && (substr($key, 0, 7) == 'option_') && (substr($key, 0, 12) != 'option_tick_') && ($val != '')) {
                return true;
            }
        }
        return false;
    }

    /**
     * Helper function for hooks, used to get catalogue entry data (useful also for custom fields).
     *
     * @param  array $content_fields Map of content fields, field name to data (may contain fake fields)
     * @param  array $fields_to_index List of field names from $content_fields that should be indexed for ngram tokens
     * @param  string $c_name Catalogue codename
     * @param  integer $id Catalogue entry ID
     * @param  ?LANGUAGE_NAME $lang Language codename (null: default)
     */
    public function get_content_fields_from_catalogue_entry(array &$content_fields, array &$fields_to_index, string $c_name, int $id, ?string $lang = null)
    {
        if (!addon_installed('catalogues')) {
            return;
        }

        require_code('catalogues');

        $map = get_catalogue_entry_field_values($c_name, $id, null, null, false, 'PAGE', $lang);
        $i = 0;
        foreach ($map as $field) {
            if (is_string($field['effective_value_pure'])) {
                $fields_to_index['field_' . strval($i)] = ($i == 0) ? APPEARANCE_CONTEXT_TITLE : APPEARANCE_CONTEXT_BODY;
                $content_fields['field_' . strval($i)] = $field['effective_value_pure'];
            }
            $i++;
        }
    }

    /**
     * Helper function for hooks, used to generate SQL for filtering rows by timestamp.
     *
     * @param  object $db Database connection
     * @param  string $index_table Table containing our custom index
     * @param  array $since_index_fields List of fields which contain timestamps that we use for recency checks
     * @param  ?TIME $since Filter to records with recency since this timestamp (null: no limit)
     * @param  ?array $statistics_map Write into this map of singular ngram (typically, words) to number of occurrences (null: do not maintain a map)
     * @return string Extra SQL
     */
    public function generate_since_where_clause(object $db, string $index_table, array $since_index_fields, ?int $since, ?array &$statistics_map = null) : string
    {
        $where_clauses = [];

        if ($since !== null) {
            $test = $db->query_select_value_if_there($index_table, '1 AS test');
            if ($test !== null) {
                foreach ($since_index_fields as $field => $may_be_null) {
                    if ($may_be_null) {
                        $where_clause = $field . ' IS NOT NULL AND ' . $field . '>' . strval($since);
                    } else {
                        $where_clause = $field . '>' . strval($since);
                    }
                    $where_clauses[] = $where_clause;
                }

                // We nullify $statistics_map as it's useless if we're not doing a full indexing
                $statistics_map = null;
            }
            // else: If the table has been truncated we treat that as a signal to do a full regeneration
        }

        if (empty($where_clauses)) {
            return '';
        }

        return ' AND (' . implode(' OR ', $where_clauses) . ')';
    }

    // Indexing...

    /**
     * Index a content resource.
     *
     * @param  object $db Database connection
     * @param  string $index_table Table containing our custom index
     * @param  array $content_fields Map of content fields, field name to data (may contain fake fields)
     * @param  array $fields_to_index List of field names from $content_fields that should be indexed for ngram tokens
     * @param  array $key_transfer_map A map between content field keys to index field keys, so we can clear out old indexing for the content resource
     * @param  array $filter_field_transfer_map A map between content field keys to index field keys, so we can fill out some of the filtering that goes inside the index
     * @param  ?integer $total_singular_ngram_tokens Maintain a count of singular ngrams (typically words) in here (null: do not maintain)
     * @param  ?array $statistics_map Write into this map of singular ngram (typically, words) to number of occurrences (null: do not maintain a map)
     * @param  ?LANGUAGE_NAME $lang Passed content is for this specific language only (null: lookup for all installed languages)
     * @param  boolean $clean_scan If we are doing a clean scan and hence do not need to clean up old records
     */
    public function index_for_search(object $db, string $index_table, array $content_fields, array $fields_to_index, array $key_transfer_map, array $filter_field_transfer_map, ?int &$total_singular_ngram_tokens = null, ?array &$statistics_map = null, ?string $lang = null, bool $clean_scan = false)
    {
        // Clear out any previous indexing for this content resource
        $key_map = [];
        foreach ($key_transfer_map as $content_table_field => $index_table_field) {
            $key_map[$index_table_field] = $content_fields[$content_table_field];
        }
        if (!$clean_scan) {
            if ($lang === null) {
                $db->query_delete($index_table, $key_map);
            } else {
                $db->query_delete($index_table, $key_map + ['i_lang' => $lang]); // We're calling this method language-by-language
            }
        }

        if ($lang === null) {
            $langs = multi_lang_content() ? array_keys(find_all_langs()) : [get_site_default_lang()];
        } else {
            $langs = [$lang];
        }

        foreach ($langs as $lang) {
            if ($statistics_map !== null) {
                if (!array_key_exists($lang, $statistics_map)) {
                    $statistics_map[$lang] = [];
                }
            }

            $ngrams_for = [];
            foreach ([APPEARANCE_CONTEXT_TITLE, APPEARANCE_CONTEXT_META, APPEARANCE_CONTEXT_BODY] as $appearance_context) {
                $_fields_to_index = [];
                foreach ($fields_to_index as $field => $_appearance_context) {
                    if ($_appearance_context == $appearance_context) {
                        $_fields_to_index[] = $field;
                    }
                }

                if ($appearance_context == APPEARANCE_CONTEXT_BODY) {
                    $ngrams_exclude = $ngrams_for[APPEARANCE_CONTEXT_TITLE] + $ngrams_for[APPEARANCE_CONTEXT_META];
                } else {
                    $ngrams_exclude = [];
                }

                $ngrams_for[$appearance_context] = $this->index_for_search__lang__appearance_context($db, $lang, $index_table, $content_fields, $appearance_context, $_fields_to_index, $key_map, $filter_field_transfer_map, $ngrams_exclude, $total_singular_ngram_tokens, $statistics_map);
            }
        }
    }

    /**
     * Index a content resource, specifically for a particular language and appearance context.
     *
     * @param  object $db Database connection
     * @param  LANGUAGE_NAME $lang Language codename
     * @param  string $index_table Table containing our custom index
     * @param  array $content_fields Map of content fields, field name to data (may contain fake fields)
     * @param  integer $appearance_context An APPEARANCE_CONTEXT_* constant
     * @param  array $fields_to_index List of field names from $content_fields that should be indexed for ngram tokens
     * @param  array $key_map A map of keys for the index
     * @param  array $filter_field_transfer_map A map between content field keys to index field keys, so we can fill out some of the filtering that goes inside the index
     * @param  ?array $ngrams_exclude A list of ngrams to explicitly exclude (used internally to stop repetitions across multiple APPEARANCE_CONTEXTs, ultimately required to stop row repetition in output) (null: none)
     * @param  ?integer $total_singular_ngram_tokens Maintain a count of singular ngrams (typically words) in here (null: do not maintain)
     * @param  ?array $statistics_map Write into this map of singular ngram (typically, words) to number of occurrences (null: do not maintain a map)
     * @return array Map between ngrams and number of occurrences
     */
    protected function index_for_search__lang__appearance_context(object $db, string $lang, string $index_table, array $content_fields, int $appearance_context, array $fields_to_index, array $key_map, array $filter_field_transfer_map, ?array $ngrams_exclude, ?int &$total_singular_ngram_tokens = null, ?array &$statistics_map = null) : array
    {
        $combined_text = '';
        foreach ($fields_to_index as $field) {
            $text = is_integer($content_fields[$field]) ? get_translated_text($content_fields[$field], $db, $lang) : $content_fields[$field];
            $combined_text .= ' ' . $text;
        }

        $ngrams = $this->tokenise_text($combined_text, $lang, $ngrams_exclude, $total_singular_ngram_tokens, $statistics_map);

        $fields = $key_map;

        // Add indexing row for each ngram
        foreach ($filter_field_transfer_map as $content_table_field => $index_table_field) {
            $fields[$index_table_field] = $content_fields[$content_table_field];
        }
        $insert_arr = [];
        $ngrams_crc = [];
        foreach ($ngrams as $ngram => $count) {
            if (is_integer($ngram)) {
                $ngram = strval($ngram);
            }

            $crc = $this->crc($ngram);
            if (isset($ngrams_crc[$crc])) {
                // CRC hash collision. Happens about 1 in 200,000 -- so we can ignore it from a UX perspective but we have to stop key collisions!s
                $ngrams_crc[$crc] += $count;
            } else {
                $ngrams_crc[$crc] = $count;
            }
        }
        foreach ($ngrams_crc as $crc => $count) {
            $fields_for_ngram = [
                'i_lang' => $lang,
                'i_ngram' => $crc,
                'i_ac' => $appearance_context,
                'i_occurrence_rate' => floatval($count) / floatval($total_singular_ngram_tokens),
            ] + $fields;

            // We are bulk-inserting, for speed
            if (empty($insert_arr)) {
                foreach ($fields_for_ngram as $key => $val) {
                    $insert_arr[$key] = [];
                }
            }
            foreach ($fields_for_ngram as $key => $val) {
                $insert_arr[$key][] = $val;
            }
        }

        if (!empty($insert_arr)) {
            $db->query_insert($index_table, $insert_arr);
        }

        return $ngrams;
    }

    /**
     * Clear out a content resource from the index.
     *
     * @param  object $db Database connection
     * @param  string $index_table Table containing our custom index
     * @param  array $index_key_map Map of index keys, defining what to delete
     */
    public static function delete_from_index(object $db, string $index_table, array $index_key_map)
    {
        $db->query_delete($index_table, $index_key_map);
    }

    /**
     * Tokenise some text, so it can be indexed by token.
     *
     * @param  string $text The text
     * @param  LANGUAGE_NAME $lang Language codename
     * @param  ?array $ngrams_exclude A list of ngrams to explicitly exclude (used internally to stop repetitions across multiple APPEARANCE_CONTEXTs, ultimately required to stop row repetition in output) (null: none)
     * @param  ?integer $total_singular_ngram_tokens Maintain a count of singular ngrams (typically words) in here (null: do not maintain)
     * @param  ?array $statistics_map Write into this map of singular ngram (typically, words) to number of occurrences (null: do not maintain a map)
     * @return array Map between ngrams and number of occurrences
     */
    protected function tokenise_text(string $text, string $lang, ?array $ngrams_exclude = null, ?int &$total_singular_ngram_tokens = null, ?array &$statistics_map = null) : array
    {
        if (strpos($text, '&') !== false) {
            $text = html_entity_decode($text, ENT_QUOTES, get_charset());
        }

        static $max_ngram_size = null;
        if ($max_ngram_size === null) {
            $max_ngram_size = Fast_custom_index::max_ngram_size($lang);
        }

        $tokeniser = self::get_tokeniser($lang);
        $stemmer = self::get_stemmer($lang);

        $ngrams = [];

        $_ngrams = $tokeniser->text_to_ngrams($text, $max_ngram_size, $total_singular_ngram_tokens);
        foreach ($_ngrams as $ngram => $is_singular_ngram) {
            if (is_integer($ngram)) {
                $ngram = strval($ngram);
            }

            if ($is_singular_ngram) {
                if ($statistics_map !== null) {
                    if (!isset($statistics_map[$lang][$ngram])) {
                        $statistics_map[$lang][$ngram] = 0;
                    }
                    $statistics_map[$lang][$ngram]++;
                }
            }

            if ($is_singular_ngram) {
                if ($this->singular_ngram_is_stop_word($ngram, $lang)) {
                    continue;
                }
            }

            if ($ngrams_exclude !== null) {
                if (isset($ngrams_exclude[$ngram])) {
                    continue;
                }
            }

            if ($is_singular_ngram) {
                if (is_object($stemmer)) {
                    $ngram = $stemmer->stem($ngram);
                }
            }

            if (!isset($ngrams[$ngram])) {
                $ngrams[$ngram] = 0;
            }
            $ngrams[$ngram]++;
        }

        return $ngrams;
    }

    /**
     * Find whether a singular ngram (typically a word) is a stop word (i.e. too banal to be indexed).
     *
     * @param  string $ngram Singular ngram
     * @param  LANGUAGE_NAME $lang Language codename
     * @return boolean Whether it is
     */
    protected function singular_ngram_is_stop_word(string $ngram, string $lang) : bool
    {
        static $stop_list = [];
        if (!array_key_exists($lang, $stop_list)) {
            require_code('textfiles');
            $stop_list[$lang] = array_flip(explode("\n", read_text_file('too_common_words', $lang)));
            unset($stop_list[$lang]['']);
        }
        return isset($stop_list[$lang][$ngram]);
    }


    /**
     * Load up the commonalities for some ngrams, with caching.
     *
     * @param  object $db Database connection
     * @param  array $ngrams List of ngrams
     * @return array Map between ngram and commonality
     */
    protected function load_commonalities(object $db, array $ngrams) : array
    {
        static $cache = [];

        $commonality_query = 'SELECT * FROM ' . get_table_prefix() . 'ft_index_commonality WHERE ';
        $commonalities = [];
        $where = '';
        foreach ($ngrams as $ngram) {
            if (is_integer($ngram)) {
                $ngram = strval($ngram);
            }

            if (isset($cache[$ngram])) {
                $commonalities[$ngram] = $cache[$ngram];
            } else {
                if ($where != '') {
                    $where .= ' OR ';
                }
                $where .= db_string_equal_to('c_ngram', $ngram);
            }
        }
        if ($where != '') {
            $commonality_query .= $where;
            $results = collapse_2d_complexity('c_ngram', 'c_commonality', $db->query($commonality_query . ' ORDER BY c_commonality'));
            $commonalities += $results;
            $cache += $results;
        }
        return $commonalities;
    }

    /**
     * Find the maximum ngram size for us to index.
     *
     * @param  LANGUAGE_NAME $lang Language codename
     * @return integer Maximum ngram size
     */
    public static function max_ngram_size(string $lang) : int
    {
        return intval(get_value('fast_custom_index__max_ngram_size__' . $lang, get_option('fast_custom_index__max_ngram_size')));
    }

    /**
     * Get a tokeniser for a language.
     *
     * @param  LANGUAGE_NAME $lang Language codename
     * @return object Tokeniser
     */
    public static function get_tokeniser(string $lang) : object
    {
        static $tokeniser = [];
        if (!array_key_exists($lang, $tokeniser)) {
            if (((is_file(get_file_base() . '/sources/lang_tokeniser_' . $lang . '.php')) || (is_file(get_file_base() . '/sources_custom/tokeniser_' . $lang . '.php'))) && (!in_safe_mode())) {
                require_code('lang_tokeniser_' . $lang);
                $tokeniser[$lang] = object_factory('LangTokeniser_' . $lang);
            } else {
                require_code('lang_tokeniser_' . fallback_lang());
                $tokeniser[$lang] = object_factory('LangTokeniser_' . fallback_lang());
            }
        }
        return $tokeniser[$lang];
    }

    /**
     * Get a stemmer for a language.
     *
     * @param  LANGUAGE_NAME $lang Language codename
     * @return ?object Stemmer (null: none)
     */
    public static function get_stemmer(string $lang) : ?object
    {
        static $stemmer = [];
        if (!array_key_exists($lang, $stemmer)) {
            $stemmer[$lang] = null;
            if (((is_file(get_file_base() . '/sources/lang_stemmer_' . $lang . '.php')) || (is_file(get_file_base() . '/sources_custom/lang_stemmer_' . $lang . '.php'))) && (!in_safe_mode())) {
                if (get_option('fast_custom_index__do_stemming') === '1') {
                    require_code('lang_stemmer_' . $lang);
                    $stemmer[$lang] = object_factory('Stemmer_' . $lang);
                }
            }
        }
        return $stemmer[$lang];
    }

    /**
     * Calculate a CRC, effectively converting a string ngram to an integer hash of it.
     * CRC-24 algorithm, to avoid compatibility issues with PHP's crc32.
     *
     * @param  string $str String
     * @return integer CRC
     */
    protected function crc(string $str) : int
    {
        static $ob = null;
        if ($ob === null) {
            require_code('crc24');
            $ob = new CRC24();
        }
        return $ob->calculate($str);
    }
}

/**
 * Base class for catalogue search / custom content fields search.
 *
 * @package search
 */
abstract class FieldsSearchHook
{
    /**
     * Get a list of extra sort fields.
     *
     * @param  string $catalogue_name Catalogue we are searching in in (may be a special custom content fields catalogue)
     * @return array A map between parameter name and string label
     */
    protected function _get_extra_sort_fields(string $catalogue_name) : array
    {
        static $EXTRA_SORT_FIELDS_CACHE = [];
        if (array_key_exists($catalogue_name, $EXTRA_SORT_FIELDS_CACHE)) {
            return $EXTRA_SORT_FIELDS_CACHE[$catalogue_name];
        }

        $extra_sort_fields = [];

        if (addon_installed('catalogues')) {
            require_code('fields');

            $rows = $GLOBALS['SITE_DB']->query_select('catalogue_fields', ['id', 'cf_name', 'cf_type', 'cf_default', 'cf_order'], ['c_name' => $catalogue_name, 'cf_is_sortable' => 1, 'cf_visible' => 1], 'ORDER BY cf_order,' . $GLOBALS['SITE_DB']->translate_field_ref('cf_name'));
            foreach ($rows as $i => $row) {
                $ob = get_fields_hook($row['cf_type']);
                $temp = $ob->inputted_to_sql_for_search($row, $i);
                if ($temp === null) { // Standard direct 'substring' search
                    $extra_sort_fields['f' . strval($i) . '_actual_value'] = get_translated_text($row['cf_name']);
                }
            }
        }

        $EXTRA_SORT_FIELDS_CACHE[$catalogue_name] = $extra_sort_fields;

        return $extra_sort_fields;
    }

    /**
     * Get a list of extra fields to ask for.
     *
     * @param  string $catalogue_name Catalogue to search in (may be a special custom content fields catalogue)
     * @return array A list of maps specifying extra fields
     */
    protected function _get_fields(string $catalogue_name) : array
    {
        if (!addon_installed('catalogues')) {
            return [];
        }

        $fields = [];
        $rows = $GLOBALS['SITE_DB']->query_select('catalogue_fields', ['*'], ['c_name' => $catalogue_name, 'cf_allow_template_search' => 1, 'cf_visible' => 1], 'ORDER BY cf_order,' . $GLOBALS['SITE_DB']->translate_field_ref('cf_name'));
        require_code('fields');
        foreach ($rows as $row) {
            $ob = get_fields_hook($row['cf_type']);
            $temp = $ob->get_search_inputter($row);
            if ($temp === null) {
                $type = '_TEXT';
                $special = get_param_string('option_' . strval($row['id']), '', INPUT_FILTER_GET_COMPLEX);
                $extra = '';
                $display = get_translated_text($row['cf_name']);
                $fields[] = ['NAME' => strval($row['id']) . $extra, 'DISPLAY' => $display, 'TYPE' => $type, 'SPECIAL' => $special];
            } else {
                $fields[] = $temp;
            }
        }
        return $fields;
    }

    /**
     * Get details needed (SQL etc) to perform an advanced field search.
     *
     * @param  string $catalogue_name Catalogue we are searching in in (may be a special custom content fields catalogue)
     * @param  string $table_alias Table alias for main content table
     * @return ?array A big tuple of details used to search with (null: no fields)
     */
    protected function _get_search_parameterisation_advanced(string $catalogue_name, string $table_alias = 'r') : ?array
    {
        if (!addon_installed('catalogues')) {
            return null;
        }

        $where_clause = '';

        $fields = $GLOBALS['SITE_DB']->query('SELECT * FROM ' . get_table_prefix() . 'catalogue_fields WHERE ' . db_string_equal_to('c_name', $catalogue_name) . ' AND (cf_include_in_main_search = 1 OR cf_allow_template_search = 1) ORDER BY cf_order,' . $GLOBALS['SITE_DB']->translate_field_ref('cf_name'), null, 0, false, false, ['cf_name' => 'SHORT_TRANS']);
        if (empty($fields)) {
            return null;
        }

        $table = '';
        $trans_fields = ['!' => '!'];
        $nontrans_fields = [];
        $title_field = null;
        require_code('fields');
        foreach ($fields as $i => $field) {
            $ob = get_fields_hook($field['cf_type']);
            $include_in_main_search = $field['cf_include_in_main_search'] == 1;
            $allow_template_search = $field['cf_allow_template_search'] == 1;
            $temp = null;
            if ($allow_template_search) {
                $temp = $ob->inputted_to_sql_for_search($field, $i, $table_alias);
            }
            if ($temp === null) { // Standard direct 'substring' search
                list(, , $row_type) = $ob->get_field_value_row_bits($field);
                switch ($row_type) {
                    case 'long_trans':
                        if ($include_in_main_search) {
                            $trans_fields['f' . strval($i) . '.cv_value'] = 'LONG_TRANS__COMCODE';
                        }
                        $table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_long_trans f' . strval($i) . ' ON f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']);
                        if (multi_lang_content()) {
                            $search_field = 't' . strval(count($trans_fields) - 1) . '.text_original';
                        } else {
                            $search_field = 'f' . strval($i) . '.cv_value';
                        }
                        break;
                    case 'short_trans':
                        if ($include_in_main_search) {
                            $trans_fields['f' . strval($i) . '.cv_value'] = 'SHORT_TRANS__COMCODE';
                        }
                        $table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_short_trans f' . strval($i) . ' ON f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']);
                        if (multi_lang_content()) {
                            $search_field = 't' . strval(count($trans_fields) - 1) . '.text_original';
                        } else {
                            $search_field = 'f' . strval($i) . '.cv_value';
                        }
                        break;
                    case 'long':
                        if ($include_in_main_search) {
                            $nontrans_fields[] = 'f' . strval($i) . '.cv_value';
                        }
                        $table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_long f' . strval($i) . ' ON f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']);
                        if (multi_lang_content()) {
                            $search_field = 't' . strval(count($trans_fields) - 1) . '.text_original';
                        } else {
                            $search_field = 'f' . strval($i) . '.cv_value';
                        }
                        break;
                    case 'short':
                        if ($include_in_main_search) {
                            $nontrans_fields[] = 'f' . strval($i) . '.cv_value';
                        }
                        $table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_short f' . strval($i) . ' ON f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']);
                        $search_field = 'f' . strval($i) . '.cv_value';
                        break;
                    case 'float':
                        $table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_float f' . strval($i) . ' ON f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']);
                        $search_field = 'f' . strval($i) . '.cv_value';
                        break;
                    case 'integer':
                        $table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_integer f' . strval($i) . ' ON f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']);
                        $search_field = 'f' . strval($i) . '.cv_value';
                        break;
                }

                if ($allow_template_search) {
                    $range_search = (option_value_from_field_array($field, 'range_search', 'off') == 'on');
                    if ($range_search) {
                        if (method_exists($ob, 'get_search_filter_from_env')) {
                            list($from, $to) = explode(';', $ob->get_search_filter_from_env($field));
                        } else {
                            $from = get_param_string('option_' . strval($field['id']) . '_from', '');
                            $to = get_param_string('option_' . strval($field['id']) . '_to', '');
                        }
                        if ($from != '' || $to != '') {
                            if ($from == '') {
                                $from = $to;
                            }
                            if ($to == '') {
                                $to = $from;
                            }

                            $where_clause .= ' AND ';

                            if (is_numeric($from) && is_numeric($to)) {
                                $where_clause .= $search_field . '>=' . $from . ' AND ' . $search_field . '<=' . $to;
                            } else {
                                $where_clause .= $search_field . '>=\'' . db_escape_string($from) . '\' AND ' . $search_field . '<=\'' . db_escape_string($to) . '\'';
                            }
                        }
                    } else {
                        if (method_exists($ob, 'get_search_filter_from_env')) {
                            $param = $ob->get_search_filter_from_env($field);
                        } else {
                            $param = get_param_string('option_' . strval($field['id']), '', INPUT_FILTER_GET_COMPLEX);
                        }

                        if ($param != '') {
                            $where_clause .= ' AND ';

                            if (substr($param, 0, 1) == '=') {
                                $where_clause .= db_string_equal_to($search_field, substr($param, 1));
                            } elseif ($row_type == 'integer' || $row_type == 'float') {
                                if (is_numeric($param)) {
                                    $where_clause .= $search_field . '=' . $param;
                                } else {
                                    $where_clause .= db_string_equal_to($search_field, $param);
                                }
                            } else {
                                if (($GLOBALS['SITE_DB']->has_full_text()) && ($GLOBALS['SITE_DB']->driver->has_full_text_boolean()) && (!is_under_radar($param))) {
                                    $temp = $GLOBALS['SITE_DB']->full_text_assemble('+"' . $param . '"');
                                } else {
                                    list($temp) = db_like_assemble($param);
                                }
                                $where_clause .= preg_replace('#\?#', $search_field, $temp);
                            }
                        }
                    }
                }
            } else {
                $table .= $temp[2];
                $search_field = $temp[3];
                if ($temp[4] != '') {
                    $where_clause .= ' AND ';
                    $where_clause .= $temp[4];
                } else {
                    $trans_fields = array_merge($trans_fields, $temp[0]);
                    $nontrans_fields = array_merge($nontrans_fields, $temp[1]);
                }
            }
            if ($i == 0) {
                $title_field = $search_field;
            }
        }

        $where_clause_2 = ' AND ';
        if ($catalogue_name[0] == '_') {
            $where_clause_2 .= '(' . db_string_equal_to($table_alias . '.c_name', $catalogue_name) . ' OR ' . $table_alias . '.c_name IS NULL' . ')';
        } else {
            $where_clause_2 .= db_string_equal_to($table_alias . '.c_name', $catalogue_name);
        }

        return [$table, $where_clause, $where_clause_2, $trans_fields, $nontrans_fields, $title_field];
    }

    /**
     * Get details needed (SQL etc) to perform an advanced field search for custom content fields (builds on _get_search_parameterisation_advanced).
     *
     * @param  string $catalogue_name Catalogue we are searching in in (may be a special custom content fields catalogue)
     * @param  string $table Table clause to add to
     * @param  string $where_clause Where clause to add to
     * @param  array $trans_fields Translatable fields to add to
     * @param  array $nontrans_fields Non-translatable fields to add to
     * @param  ?string $content_id_field Content-ID field (null: default r.id field)
     */
    protected function _get_search_parameterisation_advanced_for_content_type(string $catalogue_name, string &$table, string &$where_clause, array &$trans_fields, array &$nontrans_fields, ?string $content_id_field = null)
    {
        $advanced = $this->_get_search_parameterisation_advanced($catalogue_name, 'ce');
        if ($advanced === null) {
            return;
        }

        if ($content_id_field === null) {
            $content_id_field = db_cast('r.id', 'CHAR');
        }

        list($sup_table, $sup_where_clause, , $sup_trans_fields, $sup_nontrans_fields) = $advanced;

        $table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_entry_linkage l ON l.content_id=' . $content_id_field . ' AND ' . db_string_equal_to('content_type', substr($catalogue_name, 1));
        $table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_entries ce ON ce.id=l.catalogue_entry_id';

        $table .= $sup_table;
        $where_clause .= $sup_where_clause;
        $trans_fields = array_merge($trans_fields, $sup_trans_fields);
        $nontrans_fields = array_merge($nontrans_fields, $sup_nontrans_fields);
    }

    /**
     * Insert a date range check into a WHERE clause.
     *
     * @param  mixed $cutoff Cutoff date (TIME or a pair representing the range or null)
     * @param  string $field The field name of the timestamp field in the database
     * @param  string $where_clause Additional where clause will be written into here
     */
    protected function _handle_date_check($cutoff, string $field, string &$where_clause)
    {
        if ($cutoff !== null) {
            if (is_integer($cutoff)) {
                $where_clause .= ' AND ' . $field . '>' . strval($cutoff);
            } elseif (is_array($cutoff)) {
                if ($cutoff[0] !== null) {
                    $where_clause .= ' AND ' . $field . '>=' . strval($cutoff[0]);
                }
                if ($cutoff[1] !== null) {
                    $where_clause .= ' AND ' . $field . '<=' . strval($cutoff[1]);
                }
            }
        }
    }

    /**
     * Do a date range check for a known timestamp.
     *
     * @param  mixed $cutoff Cutoff date (TIME or a pair representing the range or null)
     * @param  TIME $compare Timestamp to compare to
     * @return boolean Whether the date matches the requirements of $cutoff
     */
    protected function _handle_date_check_runtime($cutoff, int $compare) : bool
    {
        if ($cutoff !== null) {
            if (is_integer($cutoff)) {
                if ($compare < $cutoff) {
                    return false;
                }
            } elseif (is_array($cutoff)) {
                if ((($cutoff[0] !== null) && ($compare < $cutoff[0])) || (($cutoff[1] !== null) && ($compare > $cutoff[1]))) {
                    return false;
                }
            }
        }
        return true;
    }
}

/**
 * Find whether a phrase is too small for full-text search.
 *
 * @param  string $test The phrase
 * @return boolean Whether it is
 */
function is_under_radar(string $test) : bool
{
    return (strlen($test) < $GLOBALS['SITE_DB']->get_minimum_search_length()) && ($test != '');
}

/**
 * Get a list of MySQL stopwords.
 * May be overridden for other databases, if you want to tune your stopword list.
 *
 * @return array List of stopwords (actually a map of stopword to true)
 */
function get_stopwords_list() : array
{
    // Hard-coded from MySQL manual (https://dev.mysql.com/doc/refman/8.0/en/fulltext-stopwords.html). No way to read it dynamically.
    return [
        'a\'s' => true,
        'able' => true,
        'about' => true,
        'above' => true,
        'according' => true,
        'accordingly' => true,
        'across' => true,
        'actually' => true,
        'after' => true,
        'afterwards' => true,
        'again' => true,
        'against' => true,
        'ain\'t' => true,
        'all' => true,
        'allow' => true,
        'allows' => true,
        'almost' => true,
        'alone' => true,
        'along' => true,
        'already' => true,
        'also' => true,
        'although' => true,
        'always' => true,
        'am' => true,
        'among' => true,
        'amongst' => true,
        'an' => true,
        'and' => true,
        'another' => true,
        'any' => true,
        'anybody' => true,
        'anyhow' => true,
        'anyone' => true,
        'anything' => true,
        'anyway' => true,
        'anyways' => true,
        'anywhere' => true,
        'apart' => true,
        'appear' => true,
        'appreciate' => true,
        'appropriate' => true,
        'are' => true,
        'aren\'t' => true,
        'around' => true,
        'as' => true,
        'aside' => true,
        'ask' => true,
        'asking' => true,
        'associated' => true,
        'at' => true,
        'available' => true,
        'away' => true,
        'awfully' => true,
        'be' => true,
        'became' => true,
        'because' => true,
        'become' => true,
        'becomes' => true,
        'becoming' => true,
        'been' => true,
        'before' => true,
        'beforehand' => true,
        'behind' => true,
        'being' => true,
        'believe' => true,
        'below' => true,
        'beside' => true,
        'besides' => true,
        'best' => true, // Could be a surname, hence issue
        'better' => true,
        'between' => true,
        'beyond' => true,
        'both' => true,
        'brief' => true,
        'but' => true,
        'by' => true,
        'c\'mon' => true,
        'c\'s' => true,
        'came' => true,
        'can' => true,
        'can\'t' => true,
        'cannot' => true,
        'cant' => true,
        'cause' => true,
        'causes' => true,
        'certain' => true,
        'certainly' => true,
        'changes' => true,
        'clearly' => true,
        'co' => true,
        'com' => true,
        'come' => true,
        'comes' => true,
        'concerning' => true,
        'consequently' => true,
        'consider' => true,
        'considering' => true,
        'contain' => true,
        'containing' => true,
        'contains' => true,
        'corresponding' => true,
        'could' => true,
        'couldn\'t' => true,
        'course' => true,
        'currently' => true,
        'definitely' => true,
        'described' => true,
        'despite' => true,
        'did' => true,
        'didn\'t' => true,
        'different' => true,
        'do' => true,
        'does' => true,
        'doesn\'t' => true,
        'doing' => true,
        'don\'t' => true,
        'done' => true,
        'down' => true,
        'downwards' => true,
        'during' => true,
        'each' => true,
        'edu' => true,
        'eg' => true,
        'eight' => true,
        'either' => true,
        'else' => true,
        'elsewhere' => true,
        'enough' => true,
        'entirely' => true,
        'especially' => true,
        'et' => true,
        'etc' => true,
        'even' => true,
        'ever' => true,
        'every' => true,
        'everybody' => true,
        'everyone' => true,
        'everything' => true,
        'everywhere' => true,
        'ex' => true,
        'exactly' => true,
        'example' => true,
        'except' => true,
        'far' => true,
        'few' => true,
        'fifth' => true,
        'first' => true,
        'five' => true,
        'followed' => true,
        'following' => true,
        'follows' => true,
        'for' => true,
        'former' => true,
        'formerly' => true,
        'forth' => true,
        'four' => true,
        'from' => true,
        'further' => true,
        'furthermore' => true,
        'get' => true,
        'gets' => true,
        'getting' => true,
        'given' => true,
        'gives' => true,
        'go' => true,
        'goes' => true,
        'going' => true,
        'gone' => true,
        'got' => true,
        'gotten' => true,
        'greetings' => true,
        'had' => true,
        'hadn\'t' => true,
        'happens' => true,
        'hardly' => true,
        'has' => true,
        'hasn\'t' => true,
        'have' => true,
        'haven\'t' => true,
        'having' => true,
        'he' => true,
        'he\'s' => true,
        'hello' => true,
        'help' => true,
        'hence' => true,
        'her' => true,
        'here' => true,
        'here\'s' => true,
        'hereafter' => true,
        'hereby' => true,
        'herein' => true,
        'hereupon' => true,
        'hers' => true,
        'herself' => true,
        'hi' => true,
        'him' => true,
        'himself' => true,
        'his' => true,
        'hither' => true,
        'hopefully' => true,
        'how' => true,
        'howbeit' => true,
        'however' => true,
        'i\'d' => true,
        'i\'ll' => true,
        'i\'m' => true,
        'i\'ve' => true,
        'ie' => true,
        'if' => true,
        'ignored' => true,
        'immediate' => true,
        'in' => true,
        'inasmuch' => true,
        'inc' => true,
        'indeed' => true,
        'indicate' => true,
        'indicated' => true,
        'indicates' => true,
        'inner' => true,
        'insofar' => true,
        'instead' => true,
        'into' => true,
        'inward' => true,
        'is' => true,
        'isn\'t' => true,
        'it' => true,
        'it\'d' => true,
        'it\'ll' => true,
        'it\'s' => true,
        'its' => true,
        'itself' => true,
        'just' => true,
        'keep' => true,
        'keeps' => true,
        'kept' => true,
        'know' => true,
        'known' => true,
        'knows' => true,
        'last' => true,
        'lately' => true,
        'later' => true,
        'latter' => true,
        'latterly' => true,
        'least' => true,
        'less' => true,
        'lest' => true,
        'let' => true,
        'let\'s' => true,
        'like' => true,
        'liked' => true,
        'likely' => true,
        'little' => true,
        'look' => true,
        'looking' => true,
        'looks' => true,
        'ltd' => true,
        'mainly' => true,
        'many' => true,
        'may' => true,
        'maybe' => true,
        'me' => true,
        'mean' => true,
        'meanwhile' => true,
        'merely' => true,
        'might' => true,
        'more' => true,
        'moreover' => true,
        'most' => true,
        'mostly' => true,
        'much' => true,
        'must' => true,
        'my' => true,
        'myself' => true,
        'name' => true,
        'namely' => true,
        'nd' => true,
        'near' => true,
        'nearly' => true,
        'necessary' => true,
        'need' => true,
        'needs' => true,
        'neither' => true,
        'never' => true,
        'nevertheless' => true,
        'new' => true,
        'next' => true,
        'nine' => true,
        'no' => true,
        'nobody' => true,
        'non' => true,
        'none' => true,
        'noone' => true,
        'nor' => true,
        'normally' => true,
        'not' => true,
        'nothing' => true,
        'novel' => true,
        'now' => true,
        'nowhere' => true,
        'obviously' => true,
        'of' => true,
        'off' => true,
        'often' => true,
        'oh' => true,
        'ok' => true,
        'okay' => true,
        'old' => true,
        'on' => true,
        'once' => true,
        'one' => true,
        'ones' => true,
        'only' => true,
        'onto' => true,
        'or' => true,
        'other' => true,
        'others' => true,
        'otherwise' => true,
        'ought' => true,
        'our' => true,
        'ours' => true,
        'ourselves' => true,
        'out' => true,
        'outside' => true,
        'over' => true,
        'overall' => true,
        'own' => true,
        'particular' => true,
        'particularly' => true,
        'per' => true,
        'perhaps' => true,
        'placed' => true,
        'please' => true,
        'plus' => true,
        'possible' => true,
        'presumably' => true,
        'probably' => true,
        'provides' => true,
        'que' => true,
        'quite' => true,
        'qv' => true,
        'rather' => true,
        'rd' => true,
        're' => true,
        'really' => true,
        'reasonably' => true,
        'regarding' => true,
        'regardless' => true,
        'regards' => true,
        'relatively' => true,
        'respectively' => true,
        'right' => true,
        'said' => true,
        'same' => true,
        'saw' => true,
        'say' => true,
        'saying' => true,
        'says' => true,
        'second' => true,
        'secondly' => true,
        'see' => true,
        'seeing' => true,
        'seem' => true,
        'seemed' => true,
        'seeming' => true,
        'seems' => true,
        'seen' => true,
        'self' => true,
        'selves' => true,
        'sensible' => true,
        'sent' => true,
        'serious' => true,
        'seriously' => true,
        'seven' => true,
        'several' => true,
        'shall' => true,
        'she' => true,
        'should' => true,
        'shouldn\'t' => true,
        'since' => true,
        'six' => true,
        'so' => true,
        'some' => true,
        'somebody' => true,
        'somehow' => true,
        'someone' => true,
        'something' => true,
        'sometime' => true,
        'sometimes' => true,
        'somewhat' => true,
        'somewhere' => true,
        'soon' => true,
        'sorry' => true,
        'specified' => true,
        'specify' => true,
        'specifying' => true,
        'still' => true,
        'sub' => true,
        'such' => true,
        'sup' => true,
        'sure' => true,
        't\'s' => true,
        'take' => true,
        'taken' => true,
        'tell' => true,
        'tends' => true,
        'th' => true,
        'than' => true,
        'thank' => true,
        'thanks' => true,
        'thanx' => true,
        'that' => true,
        'that\'s' => true,
        'thats' => true,
        'the' => true,
        'their' => true,
        'theirs' => true,
        'them' => true,
        'themselves' => true,
        'then' => true,
        'thence' => true,
        'there' => true,
        'there\'s' => true,
        'thereafter' => true,
        'thereby' => true,
        'therefore' => true,
        'therein' => true,
        'theres' => true,
        'thereupon' => true,
        'these' => true,
        'they' => true,
        'they\'d' => true,
        'they\'ll' => true,
        'they\'re' => true,
        'they\'ve' => true,
        'think' => true,
        'third' => true,
        'this' => true,
        'thorough' => true,
        'thoroughly' => true,
        'those' => true,
        'though' => true,
        'three' => true,
        'through' => true,
        'throughout' => true,
        'thru' => true,
        'thus' => true,
        'to' => true,
        'together' => true,
        'too' => true,
        'took' => true,
        'toward' => true,
        'towards' => true,
        'tried' => true,
        'tries' => true,
        'truly' => true,
        'try' => true,
        'trying' => true,
        'twice' => true,
        'two' => true,
        'un' => true,
        'under' => true,
        'unfortunately' => true,
        'unless' => true,
        'unlikely' => true,
        'until' => true,
        'unto' => true,
        'up' => true,
        'upon' => true,
        'us' => true,
        'use' => true,
        'used' => true,
        'useful' => true,
        'uses' => true,
        'using' => true,
        'usually' => true,
        'value' => true,
        'various' => true,
        'very' => true,
        'via' => true,
        'viz' => true,
        'vs' => true,
        'want' => true,
        'wants' => true,
        'was' => true,
        'wasn\'t' => true,
        'way' => true,
        'we' => true,
        'we\'d' => true,
        'we\'ll' => true,
        'we\'re' => true,
        'we\'ve' => true,
        'welcome' => true,
        'well' => true,
        'went' => true,
        'were' => true,
        'weren\'t' => true,
        'what' => true,
        'what\'s' => true,
        'whatever' => true,
        'when' => true,
        'whence' => true,
        'whenever' => true,
        'where' => true,
        'where\'s' => true,
        'whereafter' => true,
        'whereas' => true,
        'whereby' => true,
        'wherein' => true,
        'whereupon' => true,
        'wherever' => true,
        'whether' => true,
        'which' => true,
        'while' => true,
        'whither' => true,
        'who' => true,
        'who\'s' => true,
        'whoever' => true,
        'whole' => true,
        'whom' => true,
        'whose' => true,
        'why' => true,
        'will' => true,
        'willing' => true,
        'wish' => true,
        'with' => true,
        'within' => true,
        'without' => true,
        'won\'t' => true,
        'wonder' => true,
        'would' => true,
        'wouldn\'t' => true,
        'yes' => true,
        'yet' => true,
        'you' => true,
        'you\'d' => true,
        'you\'ll' => true,
        'you\'re' => true,
        'you\'ve' => true,
        'your' => true,
        'yours' => true,
        'yourself' => true,
        'yourselves' => true,
        'zero' => true,
    ];
}

/**
 * Perform a database-style in-memory boolean search on single item.
 *
 * @param  array $filter A map of POST data in search-form style. May contain 'only_titles' and 'content' (the critical one!)
 * @param  string $title The title to try and match
 * @param  ?string $post The post to try and match (null: not used)
 * @return boolean Whether we have a match
 */
function in_memory_search_match(array $filter, string $title, ?string $post = null) : bool
{
    if ((!array_key_exists('content', $filter)) || ($filter['content'] == '')) {
        return true;
    }

    $search_filter = $filter['content'];
    if (((array_key_exists('only_titles', $filter)) && ($filter['only_titles'] == 1)) || ($post === null)) {
        $context = $title;
    } else {
        $context = $title . ' ' . $post;
    }

    list($body_words, $include_words, $exclude_words) = _boolean_search_prepare($search_filter);
    foreach ($body_words as $word) {
        if (!simulated_wildcard_match($context, $word)) {
            return false;
        }
    }
    foreach ($include_words as $word) {
        if (!simulated_wildcard_match($context, $word)) {
            return false;
        }
    }
    foreach ($exclude_words as $word) {
        if (simulated_wildcard_match($context, $word)) {
            return false;
        }
    }
    return true;
}

/**
 * Get some rows, queried from the database according to the search parameters.
 *
 * @param  ?ID_TEXT $meta_type The META type used by our content (null: Cannot support META search)
 * @param  string $id_field The ID field that can be used to de-duplicate results / connect to the meta table; if there are multiple fields then delimit them with ':'
 * @param  string $search_query Search query
 * @param  string $content_where WHERE clause that selects the content according to the search query; passed in addition to $search_query to avoid unnecessary reparsing.  ? refers to the yet-unknown field name (blank: full-text search)
 * @param  string $where_clause The WHERE clause
 * @param  boolean $only_search_meta Whether to only do a META (tags) search
 * @param  boolean $only_titles Whether to only search titles (as opposed to both titles and content)
 * @param  integer $max Start position in total results
 * @param  integer $start Maximum results to return in total
 * @param  ID_TEXT $order What to order by
 * @param  ID_TEXT $direction Order direction
 * @param  ID_TEXT $table The table name
 * @param  string $select What to select
 * @param  array $trans_fields The translatable fields to search over (or an !=>! which is skipped, or an <blank>=><blank> which is skipped). The first of these will be the title field unless it is !=>! (in which case the title fields will be the first raw-field) or <blank>=><blank> (in which case there is no title field)
 * @param  array $raw_fields The non-translatable fields to search over
 * @param  ?string $permissions_module The permission module to check category access for (null: none)
 * @param  ?string $permissions_field The field that specifies the permissions ID to check category access for (null: none)
 * @param  boolean $permissions_field_is_string Whether the permissions field is a string
 * @return array The rows found
 */
function get_search_rows(?string $meta_type, string $id_field, string $search_query, string $content_where, string $where_clause, bool $only_search_meta, bool $only_titles, int $max, int $start, string &$order, string $direction, string $table, string $select = '*', array $trans_fields = [], array $raw_fields = [], ?string $permissions_module = null, ?string $permissions_field = null, bool $permissions_field_is_string = false) : array
{
    $db = get_db_for($table);

    $id_fields = explode(':', $id_field);

    // Handle timeouts well
    $db->set_query_time_limit(30);
    if (multi_lang_content()) {
        @ignore_user_abort(false); // If the user multi-submits a search, we don't want to run parallel searches (very slow!). That said, this currently doesn't work in PHP, because PHP does not realise the connection has died until way too late :(. So we also use a different tact (dedupe_mode) but hope PHP will improve with time.
    }

    // Make $where_clause consistent (no 'AND' on either side)
    if (substr($where_clause, 0, 5) == ' AND ') {
        $where_clause = substr($where_clause, 5);
    }
    if (substr($where_clause, -5) == ' AND ') {
        $where_clause = substr($where_clause, 0, strlen($where_clause) - 5);
    }
    if ($where_clause == '') {
        $where_clause = '1=1';
    }

    // Apply category permission restriction
    if (($permissions_module !== null) && (!$GLOBALS['FORUM_DRIVER']->is_super_admin(get_member()))) {
        $g_or = get_permission_where_clause_groups(get_member());
        $where_clause .= get_category_permission_where_clause($permissions_module, $permissions_field, get_member(), $g_or, 'r');
    }

    // No possible results, because no title field?
    if ($only_titles) {
        if (empty($trans_fields) || key($trans_fields) != '') {
            return [];
        }
        if (key($trans_fields) == '!' && empty($raw_fields)) {
            return [];
        }
    }

    $table_clause = $db->get_table_prefix() . $table;

    $t_rows = [];
    $t_count = 0;

    // Rating ordering, via special encoding
    if (strpos($order, 'compound_rating:') !== false) {
        list(, $rating_type, $meta_rating_id_field) = explode(':', $order);
        $select .= ',(SELECT SUM(rating-1) FROM ' . get_table_prefix() . 'rating WHERE ' . db_string_equal_to('rating_for_type', $rating_type) . ' AND rating_for_id=' . db_cast($meta_rating_id_field, 'CHAR') . ') AS compound_rating';
        $order = 'compound_rating';
    }
    if (strpos($order, 'average_rating:') !== false) {
        list(, $rating_type, $meta_rating_id_field) = explode(':', $order);
        $select .= ',(SELECT AVG(rating) FROM ' . get_table_prefix() . 'rating WHERE ' . db_string_equal_to('rating_for_type', $rating_type) . ' AND rating_for_id=' . db_cast($meta_rating_id_field, 'CHAR') . ') AS average_rating';
        $order = 'average_rating';
    }

    // Joins needed for multi-lang-content
    $translate_table_joins = [];
    $translate_table_joins_stock = [];
    if (multi_lang_content()) {
        foreach (array_keys($trans_fields) as $i => $field) { // Translatable fields present in 'select'
            if (($field == '') || ($field == '!')) {
                $translate_table_joins[$field] = '';
                continue;
            }

            $join = $db->translate_field_join($field, 't' . strval($i), user_lang(), 'JOIN');

            $translate_table_joins_stock[$field] = $join;

            if ((strpos($select, 't' . strval($i) . '.text_original') === false) && (strpos($where_clause, 't' . strval($i) . '.text_original') === false)) {
                $translate_table_joins[$field] = '';
                continue;
            }

            $translate_table_joins[$field] = $join;
        }
    }

    // Defined-keywords/tags search
    if ((get_param_integer('keep_just_show_query', 0) == 0) && ($meta_type !== null) && ($search_query != '') && (!$only_titles)) {
        if (strpos($search_query, '"') !== false || preg_match('#(^|\s)-#', $search_query) != 0 || strpos($search_query, ' ') !== false) {
            list($meta_content_where) = build_content_where($search_query, true); // A full-text search on keywords
        } else {
            $meta_content_where = db_string_equal_to('?', $search_query); // Consider it a simple keyword search
        }

        if (count($id_fields) > 1) { // Special case
            $concat_arr = [];
            foreach ($id_fields as $i => $id_field) {
                if ($i != 0) {
                    $concat_arr[] = "':'";
                    $concat_arr[] = 'r.' . $id_field;
                }
            }
            $meta_join_clause = 'm.meta_for_id=' . db_function('CONCAT', $concat_arr);
        } else {
            $meta_join_clause = 'm.meta_for_id=' . db_cast('r.' . $id_fields[0], 'CHAR');
        }
        $meta_join_clause .= ' AND ' . db_string_equal_to('m.meta_for_type', $meta_type);

        $meta_table_clause = $table_clause . ' JOIN ' . $db->get_table_prefix() . 'seo_meta_keywords m ON ' . $meta_join_clause;
        if (multi_lang_content()) {
            $meta_table_clause .= $db->translate_field_join('m.meta_keyword', 'tm', user_lang(), 'JOIN');
        }

        if (multi_lang_content()) {
            $keywords_where = ' AND ' . preg_replace('#\?#', 'tm.text_original', $meta_content_where);
        } else {
            $keywords_where = ' AND ' . preg_replace('#\?#', 'meta_keyword', $meta_content_where);
        }

        $_keywords_query = $meta_table_clause . implode('', $translate_table_joins) . ' WHERE ' . $where_clause . $keywords_where;

        $keywords_query = 'SELECT DISTINCT ' . $select . ' FROM ' . $_keywords_query;
        $tmp_subquery = 'SELECT 1 AS x FROM ' . $_keywords_query;
        $GLOBALS['SITE_DB']->driver->apply_sql_limit_clause($tmp_subquery, MAXIMUM_RESULT_COUNT_POINT);
        $_count_query_keywords_search = '(SELECT COUNT(*) FROM (' . $tmp_subquery . ') counter)';

        if (($order != '') && ($order . ' ' . $direction != 'contextual_relevance DESC')) {
            $keywords_query .= ' ORDER BY ' . $order;
            if ($direction == 'DESC') {
                $keywords_query .= ' DESC';
            }
        }

        $db->dedupe_mode = true;

        cms_profile_start_for('SEARCH:t_keyword_search_rows_count');
        $t_keyword_search_rows_count = $db->query_value_if_there(remove_unneeded_joins_rough($_count_query_keywords_search), true);
        cms_profile_end_for('SEARCH:t_keyword_search_rows_count', $_count_query_keywords_search);
        if ($t_keyword_search_rows_count === null) {
            $t_keyword_search_rows_count = MAXIMUM_RESULT_COUNT_POINT; // Too slow, so just put in a maximum
        }
        $t_count += $t_keyword_search_rows_count;

        cms_profile_start_for('SEARCH:t_keyword_search_rows');
        $keywords_query_final = remove_unneeded_joins_rough($keywords_query);
        if (get_param_integer('keep_show_query', 0) == 1) {
            attach_message($keywords_query_final, 'inform');
        }
        $t_keyword_search_rows = $db->query($keywords_query_final, $max + $start);
        cms_profile_end_for('SEARCH:t_keyword_search_rows', $keywords_query);
        if ($t_keyword_search_rows === null) {
            warn_exit(do_lang_tempcode('SEARCH_QUERY_TOO_SLOW'), false, true);
        }
        $t_rows = array_merge($t_rows, $t_keyword_search_rows);

        $db->dedupe_mode = false;
    } else {
        $_count_query_keywords_search = null;
        $t_keyword_search_rows = [];
    }

    // Main content search
    if (!$only_search_meta) {
        if (multi_lang_content()) {
            // Complex multi-language search algorithm...

            $where_alternative_matches = [];

            if (($content_where != '') || (preg_match('#t\d+\.text_original#', $where_clause) != 0) || (preg_match('#t\d+\.text_original#', $select) != 0)) {
                // Each of the fields represents an 'OR' match, so we put it together into a list ($where_alternative_matches) of specifiers for each. Hopefully we will 'UNION' them rather than 'OR' them as it is much more efficient in terms of table index usage

                $where_alternative_matches = [];
                foreach (array_keys($trans_fields) as $i => $field) { // Translatable fields
                    if (($field == '') || ($field == '!')) {
                        continue;
                    }

                    if ($field == $order) {
                        $order = 't' . $i . '.text_original'; // Ah, remap to the textual equivalent then
                    }

                    if (($only_titles) && ($i != 0)) {
                        break;
                    }

                    $_where_clause = ' AND ' . preg_replace('#\?#', 't' . strval($i) . '.text_original', $content_where);

                    if (($order == '') && ($db->driver->has_expression_ordering_by_alias()) && ($content_where != '')) {
                        $_select = preg_replace('#\?#', 't' . strval($i) . '.text_original', $content_where) . ' AS contextual_relevance';
                    } else {
                        $_select = null;
                    }

                    $_table_clause = implode('', $translate_table_joins);
                    if ($translate_table_joins[$field] == '') {
                        $_table_clause .= $translate_table_joins_stock[$field];
                    }

                    $where_alternative_matches[] = [$_where_clause, $_select, $_table_clause, 't' . strval($i), $field];
                }
                reset($trans_fields);
                if ($content_where != '') { // Non-translatable fields
                    foreach ($raw_fields as $i => $field) {
                        if (($only_titles) && (($i != 0) || (empty($trans_fields)) || (key($trans_fields) != '!'))) {
                            break;
                        }

                        $_where_clause = ' AND ' . preg_replace('#\?#', $field, $content_where);

                        if (($order == '') && ($db->driver->has_expression_ordering_by_alias()) && ($content_where != '')) {
                            $_select = preg_replace('#\?#', $field, $content_where) . ' AS contextual_relevance';
                        } else {
                            $_select = null;
                        }

                        $where_alternative_matches[] = [$_where_clause, $_select, '', null, $field];
                    }
                }
            }

            if (empty($where_alternative_matches)) {
                $where_alternative_matches[] = ['', null, '', null, null];
            } else {
                if (($order == '') && ($db->driver->has_expression_ordering_by_alias()) && ($content_where != '')) {
                    $order = 'contextual_relevance DESC';
                }
            }

            // Work out queries
            $main_query_parts = [];
            $count_query_parts = [];
            foreach ($where_alternative_matches as $parts) { // We UNION them, because doing OR's on MATCH's is insanely slow in MySQL (sometimes I hate SQL...)
                list($_where_clause, $_select, $_table_clause, $tid, $field) = $parts;

                $__table_clause = $table_clause . $_table_clause;

                if (($field !== null) && (preg_match('#^f\d+\.cv_value$#', $field) != 0)) {
                    // Needed to make the where condition indexed
                    $__table_clause = str_replace(' LEFT JOIN ' . get_table_prefix() . 'catalogue_entry_linkage l', ' JOIN ' . get_table_prefix() . 'catalogue_entry_linkage l', $__table_clause);
                }

                $__select = $select;
                if ($_select !== null) {
                    $__select .= ',' . $_select;
                }

                $__where_clause = $where_clause . $_where_clause;

                $main_query_part = 'SELECT DISTINCT ' . $__select . ' FROM ' . $__table_clause . ' WHERE ' . $__where_clause;
                if (($order != '') && ($order . ' ' . $direction != 'contextual_relevance DESC') && ($order != 'contextual_relevance DESC')) {
                    $main_query_part .= ' ORDER BY ' . $order;
                    if (($direction == 'DESC') && (substr($order, -4) != ' ASC') && (substr($order, -5) != ' DESC')) {
                        $main_query_part .= ' DESC';
                    }
                }
                $GLOBALS['SITE_DB']->driver->apply_sql_limit_clause($main_query_part, $max + $start);
                $main_query_parts[] = remove_unneeded_joins_rough($main_query_part);

                // Has to do a nested subquery to reduce scope of COUNT(*), because the unbounded full-text's binary tree descendance can be extremely slow on physical disks if common words exist that aren't defined as MySQL stop words
                $_count_query_part = 'SELECT 1 AS x FROM ' . $__table_clause . ' WHERE ' . $__where_clause;
                $GLOBALS['SITE_DB']->driver->apply_sql_limit_clause($_count_query_part, MAXIMUM_RESULT_COUNT_POINT);
                $count_query_part = '(SELECT COUNT(*) FROM (' . $_count_query_part . ') counter)';
                $count_query_parts[] = remove_unneeded_joins_rough($count_query_part);
            }
            $query = '';
            foreach ($main_query_parts as $part_i => $main_query_part) {
                if ($part_i != 0) {
                    $query .= ' UNION ';
                }
                $query .= '(' . $main_query_part . ')';
            }
            if (($order != '') && ($order . ' ' . $direction != 'contextual_relevance DESC') && ($order != 'contextual_relevance DESC')) {
                $query .= ' ORDER BY ' . $order;
                if (($direction == 'DESC') && (substr($order, -4) != ' ASC') && (substr($order, -5) != ' DESC')) {
                    $query .= ' DESC';
                }
            }
            $_count_query_main_search = 'SELECT (' . implode(' + ', $count_query_parts) . ')';
            if (($GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())) || ($GLOBALS['IS_ACTUALLY_ADMIN'])) {
                if (get_param_integer('keep_show_query', 0) == 1) {
                    attach_message($query, 'inform');
                }
                if (get_param_integer('keep_just_show_query', 0) == 1) {
                    cms_ini_set('ocproducts.xss_detect', '0');
                    header('Content-Type: text/plain; charset=' . get_charset());
                    exit($query);
                }
            }

            $db->dedupe_mode = true;

            // Useful for automated testing
            global $LAST_SEARCH_QUERY, $LAST_COUNT_QUERY;

            $LAST_COUNT_QUERY = $_count_query_main_search;
            cms_profile_start_for('SEARCH:t_main_search_rows_count');
            $t_main_search_rows_count = $db->query_value_if_there(remove_unneeded_joins_rough($_count_query_main_search));
            if ($t_main_search_rows_count === null) {
                $t_main_search_rows_count = MAXIMUM_RESULT_COUNT_POINT; // Too slow, so just put in a maximum
            }
            cms_profile_end_for('SEARCH:t_main_search_rows_count', $_count_query_main_search);
            $t_count += $t_main_search_rows_count;

            $LAST_SEARCH_QUERY = $query;
            cms_profile_start_for('SEARCH:t_main_search_rows');
            $t_main_search_rows = $db->query(remove_unneeded_joins_rough($query), $max * 2 /*In case of duplication*/ + $start, 0, false, true);
            cms_profile_end_for('SEARCH:t_main_search_rows', $query);
            if ($t_main_search_rows === null) {
                warn_exit(do_lang_tempcode('SEARCH_QUERY_TOO_SLOW'), false, true);
            }

            $db->dedupe_mode = false;
        } else {
            // If we don't have multi-lang-content...

            list(, $body_where, $include_where, $exclude_where) = build_content_where($search_query);

            $simple_table = preg_replace('# .*#', '', $table);
            $indices_for_table = $GLOBALS['SITE_DB']->query_select('db_meta_indices', ['i_name', 'i_fields'], ['i_table' => $simple_table]);

            $where_clause_and = '';
            $all_fields = array_merge($raw_fields, array_keys($trans_fields));
            reset($raw_fields);
            reset($trans_fields);
            $search_clause_sets = [$include_where, $body_where];
            foreach ($search_clause_sets as $_where) {
                foreach ($_where as $__where) {
                    // See if we have a combined full-text index covering multiple columns
                    $has_combined_index_coverage = false;
                    foreach ($indices_for_table as $index) {
                        if (substr($index['i_name'], 0, 1) == '#') {
                            $index_coverage = explode(',', $index['i_fields']);

                            $has_combined_index_coverage = true;
                            foreach ($all_fields as $field) {
                                if (($field == '') || ($field == '!')) {
                                    continue;
                                }

                                $field_stripped = preg_replace('#.*\.#', '', $field);
                                if (!in_array($field_stripped, $index_coverage)) {
                                    $has_combined_index_coverage = false;
                                    break;
                                }
                            }

                            if ($has_combined_index_coverage) {
                                break;
                            }
                        }
                    }

                    $where_clause_or = '';
                    $where_clause_or_fields = '';
                    foreach ($all_fields as $field) {
                        if (($field == '') || ($field == '!')) {
                            continue;
                        }

                        if ($only_titles) {
                            if ((empty($trans_fields)) || (key($trans_fields) == '')) {
                                continue;
                            }

                            if (key($trans_fields) != '!') {
                                if ($field != key($trans_fields)) {
                                    continue;
                                }
                            } else {
                                if ($field != current($raw_fields)) {
                                    continue;
                                }
                            }
                        }

                        if ((strpos($__where, ' AGAINST ') !== false) && ($has_combined_index_coverage)) {
                            if ($where_clause_or_fields != '') {
                                $where_clause_or_fields .= ',';
                            }
                            $where_clause_or_fields .= $field;
                        } else {
                            if ($where_clause_or != '') {
                                $where_clause_or .= ' OR ';
                            }
                            $where_clause_or .= preg_replace('#\?#', $field, $__where);
                        }
                    }

                    if ($where_clause_or_fields != '') {
                        if ($where_clause_or != '') {
                            $where_clause_or .= ' OR ';
                        }
                        $where_clause_or .= preg_replace('#\?#', $where_clause_or_fields, $__where);
                    }

                    if ($where_clause_or != '') {
                        if ($where_clause_and != '') {
                            $where_clause_and .= ' AND ';
                        }

                        $where_clause_and .= '(' . $where_clause_or . ')';
                    }
                }
            }
            if ($exclude_where != '') {
                foreach ($all_fields as $field) {
                    if (($field == '') || ($field == '!')) {
                        continue;
                    }

                    if ($only_titles) {
                        if ((empty($trans_fields)) || (key($trans_fields) == '')) {
                            continue;
                        }

                        if (key($trans_fields) != '!') {
                            if ($field != key($trans_fields)) {
                                continue;
                            }
                        } else {
                            if ($field != current($raw_fields)) {
                                continue;
                            }
                        }
                    }

                    $where_clause .= ' AND ' . preg_replace('#\?#', $field, $exclude_where);
                }
            }

            // Start assembling query (shared between count and main queries)
            $query = ' FROM ' . $table_clause . ' WHERE ' . $where_clause;
            if ($where_clause_and != '') {
                $query .= ' AND ' . $where_clause_and;
            }

            // Count query
            //  Has to do a nested subquery to reduce scope of COUNT(*), because the unbounded full-text's binary tree descendance can be extremely slow on physical disks if common words exist that aren't defined as MySQL stop words
            $tmp_subquery = 'SELECT 1 AS x' . $query;
            $GLOBALS['SITE_DB']->driver->apply_sql_limit_clause($tmp_subquery, MAXIMUM_RESULT_COUNT_POINT);
            $_count_query_main_search = '(SELECT COUNT(*) FROM (' . $tmp_subquery . ') counter)';

            // Main query
            $query = 'SELECT DISTINCT ' . $select . $query;
            if (($order != '') && ($order . ' ' . $direction != 'contextual_relevance DESC') && ($order != 'contextual_relevance DESC')) {
                $query .= ' ORDER BY ' . $order;
                if (($direction == 'DESC') && (substr($order, -4) != ' ASC') && (substr($order, -5) != ' DESC')) {
                    $query .= ' DESC';
                }
            }

            if (($GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())) || ($GLOBALS['IS_ACTUALLY_ADMIN'])) {
                if (get_param_integer('keep_show_query', 0) == 1) {
                    attach_message($query, 'inform');
                }
                if (get_param_integer('keep_just_show_query', 0) == 1) {
                    cms_ini_set('ocproducts.xss_detect', '0');
                    @header('Content-Type: text/plain; charset=' . get_charset());
                    exit($query);
                }
            }

            $db->dedupe_mode = true;

            // Useful for automated testing
            global $LAST_SEARCH_QUERY, $LAST_COUNT_QUERY;

            $LAST_COUNT_QUERY = $_count_query_main_search;
            cms_profile_start_for('SEARCH:t_main_search_rows_count');
            $t_main_search_rows_count = $db->query_value_if_there(remove_unneeded_joins_rough($_count_query_main_search));
            cms_profile_end_for('SEARCH:t_main_search_rows_count', $_count_query_main_search);
            $t_count += $t_main_search_rows_count;

            $LAST_SEARCH_QUERY = $query;
            cms_profile_start_for('SEARCH:t_main_search_rows');
            $t_main_search_rows = $db->query(remove_unneeded_joins_rough($query), $max + $start, 0, false, true/*, $trans_fields Actually will hurt performance - we usually won't show text_parsed fields as we re-parse Comcode with syntax highlighting*/);
            cms_profile_end_for('SEARCH:t_main_search_rows', $query);
            if ($t_main_search_rows === null) {
                warn_exit(do_lang_tempcode('SEARCH_QUERY_TOO_SLOW'), false, true);
            }

            $db->dedupe_mode = false;
        }
    } else {
        // Useful for automated testing
        global $LAST_SEARCH_QUERY, $LAST_COUNT_QUERY;
        $LAST_SEARCH_QUERY = 'N/A';
        $LAST_COUNT_QUERY = 'N/A';

        $t_main_search_rows = [];
    }

    // Clean results and return

    $t_rows = array_merge($t_rows, $t_main_search_rows);

    $_rows_deduped = [];
    foreach ($t_rows as $t_row) {
        $unique_id = [];
        foreach ($id_fields as $_id_field) {
            $unique_id[] = $t_row[$_id_field];
        }

        $sz = serialize($unique_id);
        if (isset($_rows_deduped[$sz])) {
            $t_count--;
        } else {
            $_rows_deduped[$sz] = $t_row;
        }
    }
    $t_rows = array_values($_rows_deduped);

    if ((get_param_integer('keep_show_query', 0) == 1) && (($GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())) || ($GLOBALS['IS_ACTUALLY_ADMIN']))) {
        if ((array_key_exists(0, $t_rows)) && (array_key_exists('id', $t_rows[0]))) {
            $results = var_export(array_unique(collapse_1d_complexity('id', $t_rows)), true);
        } else {
            $results = var_export($t_rows, true);
        }
        attach_message(do_lang('COUNT_RESULTS') . ': ' . $results, 'inform');
    }

    array_splice($t_rows, $max * 2 + $start); // We return more than max in case our search hook does some extra in-code filtering (Catalogues, Comcode pages). It shouldn't really but sometimes it has to, and it certainly shouldn't filter more than 50%. Also so our overall ordering can be better.

    if ((count($t_main_search_rows) < $max) && (count($t_keyword_search_rows) < $max)) {
        $GLOBALS['TOTAL_SEARCH_RESULTS'] += min($t_count, count($t_rows)); // Preference on the count($t_rows) because we are counting de-duplication and we know $t_rows is a complete set
    } else {
        $GLOBALS['TOTAL_SEARCH_RESULTS'] += $t_count;
    }

    return $t_rows;
}

/**
 * Optimise out JOIN queries we don't need, using a fairly simple MySQL-centric algorithm.
 * We do this as apparently on at least some MySQL versions unused LEFT JOINs are not optimised out.
 * Assumes that any fields from a join are referenced using the JOINs alias.
 *
 * @param  string $query The SQL query
 * @return string Optimised query
 */
function remove_unneeded_joins_rough(string $query) : string
{
    $left_joins = [];

    $max = 0;
    $matches = [];
    $matches2 = [];
    do {
        $l_pos = strpos($query, ' LEFT JOIN ', $max);
        if ($l_pos !== false) {
            $match_attempt = preg_match('#^( LEFT JOIN \w+( AS)? (\w+) ON )(.*)#', substr($query, $l_pos), $matches);
            if ($match_attempt != 0) {
                $join_length = strlen($matches[1]); // Will be incremented
                $alias = $matches[3];
                $equate_term_1 = '[\w\.]+=[\w\.]+';
                $equate_term_2 = '[\w\.]+=\'[^\']+\'';
                $equate_term_3 = '[\w\.]+=CAST\([\w\.]+ AS \w+(\(\d+\))?\)';
                $equate_terms = '(' . implode('|', [$equate_term_1, $equate_term_2, $equate_term_3]) . ')';
                $multiplied_equate_terms = $equate_terms . '( AND ' . $equate_terms . ')*';
                $components = preg_match('#^(\(' . $multiplied_equate_terms . '\)|' . $multiplied_equate_terms . ')(?= (LEFT|JOIN|WHERE))#', $matches[4], $matches2);
                if ($components != 0) {
                    $join_length += strlen($matches2[0]);
                    $left_joins[$alias] = substr($query, $l_pos, $join_length);
                }
            }
            $max = $l_pos + 1;
        }
    } while ($l_pos !== false);

    $left_joins = array_reverse($left_joins, true);
    foreach ($left_joins as $alias => $join) {
        if (preg_match('#[\(\s=,]' . $alias . '\.#', str_replace($join, '', $query)) == 0) {
            // Alias not used
            $query = str_replace($join, '', $query);
        }
    }

    return $query;
}

/**
 * Build a full-text query WHERE clause from given content.
 *
 * @param  string $search_query The search query
 * @param  boolean $full_coverage Whether we can assume we require full coverage (i.e. not substring matches)
 * @param  boolean $force_like Whether to force LIKE syntax rather than full-text search
 * @return array A tuple (any SQL component may be blank): The combined where clause SQL, the boolean operator, body where clause SQL, positive where clause SQL, negative where clause SQL
 */
function build_content_where(string $search_query, bool $full_coverage = false, bool $force_like = false) : array
{
    list($body_words, $include_words, $exclude_words) = _boolean_search_prepare($search_query);

    if ($search_query == '') {
        $content_where = '';
        $body_where = [];
        $include_where = [];
        $exclude_where = '';
    } else {
        $under_radar = is_under_radar($search_query);
        $boolean = (preg_match('#[\-+"]#', $search_query) != 0);
        if (($GLOBALS['SITE_DB']->has_full_text()) && (!$under_radar) && (!$force_like) && (($GLOBALS['SITE_DB']->driver->has_full_text_boolean()) || (!$boolean))) {
            $content_where = $GLOBALS['SITE_DB']->full_text_assemble($search_query);
            $body_where = [$content_where];
            $include_where = [];
            $exclude_where = '';
        } else {
            list($content_where, $body_where, $include_where, $exclude_where) = db_like_assemble($search_query, $full_coverage);
            if ($content_where == '') {
                $content_where = '1=1';
            }
        }
    }

    return [$content_where, $body_where, $include_where, $exclude_where];
}

/**
 * Generate SQL for a boolean search.
 *
 * @param  string $search_query Boolean search string
 * @param  boolean $full_coverage Whether we can assume we require full coverage
 * @return array A tuple (any SQL component may be blank): The combined where clause SQL, the boolean operator, body where clause SQL, positive where clause SQL, negative where clause SQL
 */
function db_like_assemble(string $search_query, bool $full_coverage = false) : array
{
    $search_query = str_replace('?', '_', $search_query);
    $search_query = str_replace('*', '%', $search_query);

    list($body_words, $include_words, $exclude_words) = _boolean_search_prepare($search_query);

    $fc_before = $full_coverage ? '' : '%';
    $fc_after = $full_coverage ? '' : '%';

    $body_where = [];
    foreach ($body_words as $word) {
        $body_where[] = '? LIKE \'' . db_encode_like($fc_before . $word . $fc_after) . '\'';
    }
    $include_where = [];
    foreach ($include_words as $word) {
        $include_where[] = '? LIKE \'' . db_encode_like($fc_before . $word . $fc_after) . '\'';
    }
    $exclude_where = '';
    foreach ($exclude_words as $word) {
        if ($exclude_where != '') {
            $exclude_where .= ' AND ';
        }
        $exclude_where .= '? NOT LIKE \'' . db_encode_like($fc_before . $word . $fc_after) . '\'';
    }

    // $content_where combines all
    $content_where = '';
    if (!empty($body_words)) { // For a LIKE clause handler we can't do fuzzy search, so anything without an operator will just default to an AND
        if ($content_where != '') {
            $content_where .= ' AND ';
        }
        $content_where .= '(' . implode(' AND ', $body_where) . ')';
    }
    if (!empty($include_where)) {
        if ($content_where != '') {
            $content_where .= ' AND ';
        }
        $content_where .= '(' . implode(' AND ', $include_where) . ')';
    }
    if ($exclude_where != '') {
        if ($content_where != '') {
            $content_where .= ' AND ';
        }
        $content_where .= '(' . $exclude_where . ')';
    }

    return [$content_where, $body_where, $include_where, $exclude_where];
}

/**
 * Take a search string and find boolean search parameters from it.
 *
 * @param  string $search_query The search query
 * @return array Words to search under the boolean operator, words that must be included, words that must not be included
 *
 * @ignore
 */
function _boolean_search_prepare(string $search_query) : array
{
    $content_explode = explode(' ', $search_query);

    $body_words = [];
    $include_words = [];
    $exclude_words = [];
    for ($i = 0; $i < count($content_explode); $i++) {
        $word = trim($content_explode[$i]);
        if (($word == '') || ($word == '+') || ($word == '-')) {
            continue;
        }

        // Handle quotes
        if ($word[0] == '"') {
            $i++;
            while ($i < count($content_explode)) {
                $word2 = trim($content_explode[$i]);
                if ($word2 != '') {
                    $word .= ' ' . $word2;
                }
                if (substr($word2, -1) == '"') {
                    break;
                } else {
                    $i++;
                }
            }
            if (substr($word, -1) != '"') {
                $word .= '"';
            }
            $word = substr($word, 1, strlen($word) - 2);
            if ($word === false) {
                continue;
            }
            if ($word == '') {
                continue;
            }
        }

        if ($word[0] == '+') {
            $include_words[] = ltrim($word, '+');
        } elseif ($word[0] == '-') {
            $exclude_words[] = ltrim($word, '-');
        } else {
            $body_words[] = $word;
        }
    }

    return [$body_words, $include_words, $exclude_words];
}

/**
 * Build up a submitter search clause, taking into account members, authors, usernames, and usergroups.
 *
 * @param  ?ID_TEXT $member_field_name The field name for member IDs (null: Cannot match against member IDs)
 * @param  ?MEMBER $member_id Member ID (null: Unknown, so cannot search)
 * @param  ID_TEXT $author Author
 * @param  ?ID_TEXT $author_field_name The field name for authors (null: Cannot match against member IDs)
 * @return ?string An SQL fragment (null: block query)
 */
function build_search_submitter_clauses(?string $member_field_name, ?int $member_id, string $author, ?string $author_field_name = null) : ?string
{
    $clauses = '';

    // Member ID
    if (($member_id !== null) && ($member_field_name !== null)) {
        if ($clauses != '') {
            $clauses .= ' OR ';
        }
        $clauses .= $member_field_name . '=' . strval($member_id);
    }

    // Groups
    if (($member_field_name !== null) && ($author != '')) {
        $all_usergroups = $GLOBALS['FORUM_DRIVER']->get_usergroup_list(true);
        foreach ($all_usergroups as $usergroup => $usergroup_name) {
            if ($usergroup_name == $author) {
                $members_in_group = $GLOBALS['FORUM_DRIVER']->member_group_query([$usergroup], 50);
                if (count($members_in_group) < 50) { // Let's be reasonable with how long the SQL could get!
                    foreach (array_keys($members_in_group) as $group_member_id) {
                        if ($clauses != '') {
                            $clauses .= ' OR ';
                        }
                        $clauses .= $member_field_name . '=' . strval($group_member_id);
                    }
                }
                break;
            }
        }
    }

    // Author
    if (($author_field_name !== null) && ($author != '')) {
        if ($clauses != '') {
            $clauses .= ' OR ';
        }
        $clauses .= db_string_equal_to($author_field_name, $author);
    }

    if ($clauses == '') {
        if ($author != '') {
            return null; // Query should never succeed
        }

        return '';
    }

    return ' AND (' . $clauses . ')';
}

/**
 * Get special SQL from POSTed parameters for a catalogue search field that is to be exact-matched.
 *
 * @param  array $field The field details
 * @param  integer $i We're processing for the ith row
 * @param  ID_TEXT $type Table type
 * @set short long
 * @param  ?string $param Search term (null: lookup from environment)
 * @param  string $table_alias Table alias for catalogue entry table
 * @return ?array Tuple of SQL details (array: extra trans fields to search, array: extra plain fields to search, string: an extra table segment for a join, string: the name of the field to use as a title, if this is the title, extra WHERE clause stuff) (null: nothing special)
 */
function exact_match_sql(array $field, int $i, string $type = 'short', ?string $param = null, string $table_alias = 'r') : ?array
{
    $table = ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_' . $type . ' f' . strval($i) . ' ON f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']);
    $search_field = 'f' . strval($i) . '.cv_value';
    if ($param === null) {
        $param = get_param_string('option_' . strval($field['id']), '', INPUT_FILTER_GET_COMPLEX);
    }
    $where_clause = '';
    if ($param != '') {
        if (is_numeric($param) && ($type == 'float' || $type == 'integer')) {
            $where_clause = $search_field . '=' . $param;
        } else {
            $where_clause = db_string_equal_to($search_field, $param);
        }
    }

    $nontrans_fields = [];
    if (($type !== 'float') && ($type !== 'integer')) { // Numeric column types don't have full-text indexes on them
        $nontrans_fields[] = $search_field;
    }

    return [[], $nontrans_fields, $table, $search_field, $where_clause];
}

/**
 * Get special SQL from POSTed parameters for a catalogue search field for a multi-input field that is to be exact-matched.
 *
 * @param  array $field The field details
 * @param  integer $i We're processing for the ith row
 * @param  ID_TEXT $type Table type
 * @set short long
 * @param  ?string $param Search term (null: lookup from environment)
 * @param  string $table_alias Table alias for catalogue entry table
 * @return ?array Tuple of SQL details (array: extra trans fields to search, array: extra plain fields to search, string: an extra table segment for a join, string: the name of the field to use as a title, if this is the title, extra WHERE clause stuff) (null: nothing special)
 */
function nl_delim_match_sql(array $field, int $i, string $type = 'short', ?string $param = null, string $table_alias = 'r') : ?array
{
    $table = ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_' . $type . ' f' . strval($i) . ' ON f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']);
    $search_field = 'f' . strval($i) . '.cv_value';
    if ($param === null) {
        $param = get_param_string('option_' . strval($field['id']), '', INPUT_FILTER_GET_COMPLEX);
    }
    $where_clause = '';
    if ($param != '') {
        $where_clause = '(' . $search_field . ' LIKE \'' . db_encode_like($param) . '\' OR ' . $search_field . ' LIKE \'' . db_encode_like('%' . "\n" . $param) . '\' OR ' . $search_field . ' LIKE \'' . db_encode_like($param . "\n" . '%') . '\' OR ' . $search_field . ' LIKE \'' . db_encode_like('%' . "\n" . $param . "\n" . '%') . '\')';
    }

    return [[], [$search_field], $table, $search_field, $where_clause];
}

/**
 * Sort search results as returned by the search hook.
 *
 * @param  array $hook_results Search results from the search hook, assumed already sorted
 * @param  array $results Existing array of results (originally starts blank)
 * @param  string $direction Sort direction
 * @set ASC DESC
 * @return array Sorted results
 */
function sort_search_results(array $hook_results, array $results, string $direction) : array
{
    // Do a merge sort
    $results_position = 0;
    $done_all = false;
    foreach ($hook_results as $i => $result) {
        while (true) {
            if (!array_key_exists($results_position, $results)) {
                // If we've run off the end of our current results - everything we have left in $hook_results fits AFTER $results, and is already sorted...
                $results = array_merge($results, array_slice($hook_results, $i));
                $done_all = true;
                break;
            }
            if (
                (array_key_exists('orderer', $result)) &&
                (
                    (!array_key_exists('orderer', $results[$results_position])) ||
                    (
                        (($direction == 'ASC') && ($result['orderer'] <= $results[$results_position]['orderer'])) ||
                        (($direction == 'DESC') && ($result['orderer'] >= $results[$results_position]['orderer']))
                    )
                )
            ) {
                // If it definitely beats, put in front. If it's unknown (no orderer on one - which is very common) it has to go on the end so FIFO is preserved
                $results = array_merge(array_slice($results, 0, $results_position), [$result], array_slice($results, $results_position));
                break;
            }
            $results_position++;
        }
        if ($done_all) {
            break;
        }
    }

    return $results;
}

/**
 * Highlight keywords in an extracted portion of a piece of text.
 *
 * @param  string $_temp_summary What was searched
 * @param  array $words_searched List of words searched
 * @param  ?integer $cnt Count of matched words (null: pass by reference)
 * @return string Highlighted portion
 */
function generate_text_summary(string $_temp_summary, array $words_searched, ?int &$cnt = null) : string
{
    require_code('xhtml');

    $summary = '';

    global $SEARCH_QUERY_TERMS;

    $_temp_summary_lower = cms_mb_strtolower($_temp_summary);

    // Add in some highlighting direct to HTML
    $all_occurrences = [];
    foreach ($words_searched as $content_bit) {
        if ($content_bit == '') {
            continue;
        }

        $all_upper = (cms_mb_strtoupper($content_bit) == $content_bit);

        $last_pos = 0;
        $content_bit_pos = 0;
        do {
            $content_bit_matched = $content_bit;
            if ($all_upper) { // all upper case so don't want case insensitive
                $content_bit_pos = strpos($_temp_summary, $content_bit, $last_pos);
            } else {
                $content_bit_pos = stripos($_temp_summary_lower, $content_bit, $last_pos);
                if (strpos($content_bit, '-') !== false) {
                    $content_bit_pos_2 = strpos($_temp_summary_lower, str_replace('-', '', $content_bit), $last_pos);
                    if (($content_bit_pos_2 !== false) && (($content_bit_pos === false) || ($content_bit_pos_2 < $content_bit_pos))) {
                        $content_bit_pos = $content_bit_pos_2;
                        $content_bit_matched = str_replace('-', '', $content_bit);
                    }
                }
            }

            if ($content_bit_pos !== false) {
                $last_gt = strrpos(substr($_temp_summary, 0, $content_bit_pos), '>');
                $last_lt = strrpos(substr($_temp_summary, 0, $content_bit_pos), '<');

                if (($last_gt === false) || ($last_gt > $last_lt)) {
                    $extra_pre = '<span class="comcode-highlight">';
                    $extra_post = '</span>';
                    $_temp_summary = substr($_temp_summary, 0, $content_bit_pos) .
                                     $extra_pre .
                                     substr($_temp_summary, $content_bit_pos, strlen($content_bit_matched)) .
                                     $extra_post .
                                     substr($_temp_summary, $content_bit_pos + strlen($content_bit_matched));
                    $_temp_summary_lower = cms_mb_strtolower($_temp_summary);
                    $last_pos = $content_bit_pos + strlen($extra_pre) + strlen($content_bit_matched) + strlen($extra_post);

                    // Adjust all stores occurrence offsets
                    foreach ($all_occurrences as $i => $occ) {
                        if ($occ[0] > $last_pos) {
                            $all_occurrences[$i] = [$all_occurrences[$i][0] + strlen($extra_pre) + strlen($extra_post), $all_occurrences[$i][0] + strlen($extra_pre) + strlen($extra_post)];
                        } elseif ($occ[0] > $content_bit_pos) {
                            $all_occurrences[$i] = [$all_occurrences[$i][0] + strlen($extra_pre), $all_occurrences[$i][0] + strlen($extra_pre)];
                        }
                    }

                    $all_occurrences[] = [$content_bit_pos, $last_pos];
                } else {
                    $last_pos = $content_bit_pos + strlen($content_bit_matched);
                }
            }
        } while ($content_bit_pos !== false);
    }

    if (strlen($_temp_summary) < 500) {
        $summary = $_temp_summary;
    } else {
        // Find optimal position
        $len = strlen($_temp_summary);
        $best_yet = 0;
        $best_pos_min = 250;
        $best_pos_max = 250;
        if (count($all_occurrences) < 60) { // Only bother doing this if we need to dig for the keyword
            for ($i = 250; $i < $len - 250; $i++) { // Move window along all possible positions
                $count = 0;
                $i_pre = $i - 250;
                $i_post = $i + 250;
                foreach ($all_occurrences as $occ) {
                    $occ_pre = $occ[0];
                    $occ_post = $occ[1];
                    if (($occ_pre >= $i_pre) && ($occ_pre <= $i_post) && ($occ_post >= $i_pre) && ($occ_post <= $i_post)) {
                        $count++;

                        if ($count > 5) {
                            break; // Good enough
                        }
                    }
                }
                if (($count > $best_yet) || (($best_yet == $count) && ($i - 500 < $best_pos_min))) {
                    if ($best_yet == $count) {
                        $best_pos_max = $i;
                    } else {
                        $best_yet = $count;
                        $best_pos_min = $i;
                        $best_pos_max = $i;
                    }

                    if ($count > 5) {
                        break; // Good enough
                    }
                }
            }
            $best_pos = intval(floatval($best_pos_min + $best_pos_max) / 2.0) - 250; // Move it from center pos, to where we want to start from
        } else {
            $best_pos = 0;
        }

        // Render (with ellipses if required)
        if (false) { // Far far too slow
            $summary = xhtml_substr($_temp_summary, $best_pos, min(500, $len - $best_pos), true, true);
        } else {
            $summary = substr($_temp_summary, $best_pos, min(500, $len - $best_pos));
            $summary = xhtmlise_html($summary, true);
            if ($best_pos > 0) {
                $summary = '&hellip;' . $summary;
            }
            if ($best_pos + 500 < strlen($_temp_summary)) {
                $summary .= '&hellip;';
            }
        }
    }

    $cnt = count($all_occurrences);

    return $summary;
}

/**
 * Build a templated list of the given search results, for viewing.
 *
 * @param  array $results Search results
 * @param  integer $start Start index
 * @param  integer $max Maximum index
 * @param  string $direction Sort direction
 * @set ASC DESC
 * @param  boolean $general_search Whether this is a general search, rather than a search for a specific result-type (such as all members)
 * @return Tempcode Interface
 */
function build_search_results_interface(array $results, int $start, int $max, string $direction, bool $general_search = false) : object
{
    require_code('content');

    $out = new Tempcode();
    $i = 0;
    $tabular_results = [];
    foreach ($results as $result) {
        if (array_key_exists('restricted', $result) && ($result['restricted'] === true)) {
            continue; // Insufficient permissions
        }

        $content_type = convert_cms_type_codes('search_hook', $result['type'], 'content_type');
        $id = null;
        if ($content_type != '') {
            require_code('content');
            $cma_ob = get_content_object($content_type);
            $cma_info = $cma_ob->info();
            if ($cma_info !== null) {
                $id = extract_content_str_id_from_data($result['data'], $cma_info);
            }
        }

        if (($i >= $start) && ($i < $start + $max)) {
            $object = get_hook_ob('modules', 'search', $result['type'], 'Hook_search_');

            if (array_key_exists('template', $result)) {
                $rendered_result = $result['template'];
            } else {
                $rendered_result = $object->render($result['data']);
            }
            if ($rendered_result !== null) {
                if (is_array($rendered_result)) {
                    $class = get_class($object);
                    if (!array_key_exists($class, $tabular_results)) {
                        $tabular_results[$class] = [];
                    }
                    $tabular_results[$class][] = $rendered_result;
                } else {
                    $out->attach(do_template('SEARCH_RESULT', ['_GUID' => '47da093f9ace87819e246f0cec1402a9', 'TYPE' => $content_type, 'ID' => $id, 'CONTENT' => $rendered_result]));
                }
            }
        } else {
            $out->attach(static_evaluate_tempcode(do_template('SEARCH_RESULT', ['_GUID' => 'd8422a971f55a8a94d090861d519ca7a', 'TYPE' => $content_type, 'ID' => $id])));
        }
        $i++;
    }
    foreach ($tabular_results as $tabular_type => $types_results) {
        // Normalisation process
        $ultimate_field_map = [];
        foreach ($types_results as $r) {
            $ultimate_field_map += $r;
        }
        $ultimate_field_map = array_keys($ultimate_field_map);
        foreach ($types_results as $i => $r) {
            $r2d2 = [];
            foreach ($ultimate_field_map as $key) {
                if (!array_key_exists($key, $r)) {
                    $r[$key] = '';
                }
                $r2d2[$key] = $r[$key];
            }
            $r = $r2d2;
            $types_results[$i] = ['R' => $r];
        }

        // Output
        $out->attach(do_template('SEARCH_RESULT_TABLE', ['_GUID' => '816ec14dc0df432ca6e1e1014ef1f3d1', 'HEADERS' => $ultimate_field_map, 'ROWS' => $types_results]));
    }

    set_extra_request_metadata([
        'opensearch_totalresults' => strval($i),
        'opensearch_startindex' => strval($start),
        'opensearch_itemsperpage' => strval($max),
    ]);

    $SEARCH_QUERY_TERMS = null;

    return $out;
}
