<?php /*

 Composr
 Copyright (c) ocProducts, 2004-2016

 See text/EN/licence.txt for full licencing 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  ocProducts Ltd
 * @package    core
 */

/**
 * Read an Filtercode parameter value from GET/POST.
 *
 * @param  ID_TEXT $field_name The field name
 * @param  ?ID_TEXT $field_type The field type (null: work out what is there to read automatically)
 * @return string The parameter value
 */
function read_filtercode_parameter_from_env($field_name, $field_type = null)
{
    $env = $_POST + $_GET;

    if ($field_type === null) {
        $field_type = 'line';
        if (!array_key_exists('filter_' . $field_name, $env)) {
            if (array_key_exists('filter_' . $field_name . '_year', $env)) {
                $field_type = 'time';
            }
        } elseif (is_array($env['filter_' . $field_name])) {
            $field_type = 'list_multi';
        }
    }

    if (($field_type == 'date') || ($field_type == 'time')) {
        $_default_value = post_param_date('filter_' . $field_name, true);
        $default_value = ($_default_value === null) ? '' : strval($_default_value);
    } elseif ($field_type == 'list_multi') {
        if (array_key_exists('filter_' . $field_name, $env)) {
            $default_value = implode(',', is_array($env['filter_' . $field_name]) ? $env['filter_' . $field_name] : array($env['filter_' . $field_name]));
        } else {
            $default_value = '';
        }
    } else {
        $default_value = either_param_string('filter_' . $field_name, '');
    }
    return $default_value;
}

/**
 * Get a form for inputting unknown variables within a filter.
 *
 * @param  string $filter String-based search filter (blank: make one up to cover everything, but only works if $table is known)
 * @param  ?array $labels Labels for field names (null: none, use auto-generated)
 * @param  ?ID_TEXT $content_type Content-type to auto-probe from (null: none, use string inputs)
 * @param  ?array $types Field types (null: none, use string inputs / defaults for table)
 * @return array The form fields, The modded filter, Merger links
 */
function form_for_filtercode($filter, $labels = null, $content_type = null, $types = null)
{
    $table = mixed();
    $db = $GLOBALS['SITE_DB'];
    $info = array();
    if ($content_type !== null) {
        require_code('content');
        $ob = get_content_object($content_type);
        $info = $ob->info();

        if ($info !== null) {
            $table = $info['table'];
            if (get_forum_type() == 'cns') {
                if (($content_type == 'post') || ($content_type == 'topic') || ($content_type == 'member') || ($content_type == 'group') || ($content_type == 'forum')) {
                    $db = $GLOBALS['FORUM_DB'];
                }
            }
        }
    }

    if ($labels === null) {
        $labels = array();
    }
    if ($types === null) {
        $types = array();
    }

    $fields_needed = array();

    require_lang('filtercode');

    $catalogue_name = mixed();
    if (preg_match('#^\w+$#', $filter) != 0) {
        $catalogue_name = $filter;
        $filter = '';
    }

    $_links = array();

    // Load up fields to compare to
    if ($table !== null) {
        $db_fields = collapse_2d_complexity('m_name', 'm_type', $db->query_select('db_meta', array('m_name', 'm_type'), array('m_table' => $table)));

        if (isset($info['feedback_type_code'])) {
            $db_fields['compound_rating'] = 'INTEGER';
            $types['compound_rating'] = 'rating';

            $db_fields['average_rating'] = 'INTEGER';
            $types['average_rating'] = 'rating';
        }

        if (isset($info['seo_type_code'])) {
            $db_fields['meta_keywords'] = 'SHORT_TEXT';
            $db_fields['meta_description'] = 'LONG_TEXT';
            $types['meta_keywords'] = 'list_multi';
            $types['meta_description'] = 'line';
        }

        // Custom fields
        require_code('content');
        $ob2 = get_content_object($content_type);
        $info2 = $ob2->info();
        if (($info2 !== null) && (($info2['support_custom_fields']) || ($content_type == 'catalogue_entry'))) {
            require_code('fields');
            $catalogue_fields = list_to_map('id', get_catalogue_fields(($content_type == 'catalogue_entry') ? $catalogue_name : '_' . $content_type));
            foreach ($catalogue_fields as $catalogue_field) {
                if ($catalogue_field['cf_put_in_search'] == 1) {
                    $remapped_name = 'field_' . strval($catalogue_field['id']);
                    $db_fields[$remapped_name] = 'SHORT_TEXT';
                    $types[$remapped_name] = $catalogue_field['cf_type'];
                    $labels[$remapped_name] = get_translated_text($catalogue_field['cf_name']);
                }
            }
        }

        if ($filter == '') {
            foreach ($db_fields as $key => $type) {
                if ($key == 'notes') {
                    continue; // Protected, staff notes
                }
                if ((isset($info['filtercode_protected_fields'])) && (in_array($key, $info['filtercode_protected_fields']))) {
                    continue;
                }

                $type = str_replace(array('?', '*'), array('', ''), $type);
                switch ($type) {
                    // Any of these field types will go into the default filter (we support some that don't, but user likely does not want them)
                    case 'BINARY':
                    case 'SHORT_INTEGER':
                    case 'UINTEGER':
                    case 'INTEGER':
                    case 'TIME':
                    case 'MEMBER':
                    case 'REAL':
                    case 'LONG_TEXT':
                    case 'SHORT_TEXT':
                    case 'LONG_TRANS':
                    case 'SHORT_TRANS':
                    case 'LONG_TRANS__COMCODE':
                    case 'SHORT_TRANS__COMCODE':
                    case 'MINIID_TEXT':
                    case 'ID_TEXT':
                        if ($filter != '') {
                            $filter .= ',';
                        }
                        $filter .= $key . '<' . $key . '_op><' . $key . '>';
                        break;
                }
            }
        }
    } else {
        $db_fields = array();
    }

    $filters = parse_filtercode($filter);

    foreach ($filters as $_filter) {
        list(, $filter_op, $filter_val) = $_filter;

        // Operator
        $matches = array();
        if (preg_match('#^<([^<>]+)>$#', $filter_op, $matches) != 0) {
            $field_name = filter_naughty_harsh($matches[1], true);
            if (array_key_exists($field_name, $labels)) {
                $field_title = escape_html($labels[$field_name]);
            } else {
                $target_name = preg_replace('#^filter\_#', '', preg_replace('#\_op$#', '', $field_name));
                if (array_key_exists($target_name, $labels)) {
                    $operator_target_label = escape_html($labels[$target_name]);
                } else {
                    $operator_target_label = escape_html(titleify($target_name));
                }
                $field_title = do_lang_tempcode('OPERATOR_FOR', $operator_target_label);
            }

            $fields_needed[] = array(
                'list',
                $field_name,
                $field_title,
                either_param_string('filter_' . $field_name, '~='),
                array(
                    '<' => do_lang_tempcode('FILTERCODE_OP_LT'),
                    '>' => do_lang_tempcode('FILTERCODE_OP_GT'),
                    '<=' => do_lang_tempcode('FILTERCODE_OP_LTE'),
                    '>=' => do_lang_tempcode('FILTERCODE_OP_GTE'),
                    '=' => do_lang_tempcode('FILTERCODE_OP_EQ'),
                    '==' => do_lang_tempcode('FILTERCODE_OP_EQE'),
                    '~=' => do_lang_tempcode('FILTERCODE_OP_CO'),
                    '~' => do_lang_tempcode('FILTERCODE_OP_FT'),
                    '<>' => do_lang_tempcode('FILTERCODE_OP_NE'),
                    '!=' => do_lang_tempcode('FILTERCODE_OP_NEE'),
                    '@' => do_lang_tempcode('FILTERCODE_OP_RANGE'),
                )
            );
        }

        // Filter inputter
        $matches = array();
        if (preg_match('#^<([^<>]+)>$#', $filter_val, $matches) != 0) {
            $field_name = filter_naughty_harsh($matches[1], true);

            $extra = mixed();

            if (array_key_exists($field_name, $types)) {
                $field_type = $types[$field_name];

                if (($field_type == 'list') || ($field_type == 'linklist'/*tag-cloud-narrow-in*/) || ($field_type == 'list_multi')) {
                    // Work out what list values there are
                    $extra = array();
                    if ($table !== null) {
                        if (substr($field_name, 0, 6) == 'field_') {
                            $_extra = $db->query_select_value('catalogue_fields', 'cf_default', array('id' => intval(substr($field_name, 6))));
                            $_extra_parts = explode('|', $_extra);
                            foreach ($_extra_parts as $e) {
                                if ($e != '') {
                                    $extra[$e] = $e;
                                }
                            }
                        } elseif (($field_name != 'meta_keywords') && ($field_name != 'meta_description') && ($field_name != 'compound_rating') && ($field_name != 'average_rating')) {
                            $_extra = $db->query_select($table, array('DISTINCT ' . filter_naughty_harsh($field_name, true)), null, 'ORDER BY ' . filter_naughty_harsh($field_name, true));
                            foreach ($_extra as $e) {
                                if (!is_string($e[$field_name])) {
                                    $e[$field_name] = strval($e[$field_name]);
                                }
                                $extra[$e[$field_name]] = $e[$field_name];
                            }
                        } elseif ($field_name == 'meta_keywords') {
                            $_extra = $db->query_select('seo_meta_keywords', array('DISTINCT meta_keyword'), null, 'ORDER BY meta_keyword');
                            foreach ($_extra as $e) {
                                $extra[trim($e['meta_keyword'])] = trim($e['meta_keyword']);
                            }
                        }
                    }
                    ksort($extra);
                }
            } else {
                $field_type = 'line';
                if (array_key_exists($field_name, $db_fields)) {
                    switch (str_replace(array('?', '*'), array('', ''), $db_fields[$field_name])) {
                        case 'TIME':
                            $field_type = 'time';
                            break;
                        case 'BINARY':
                            $field_type = 'tick';
                            break;
                        case 'AUTO':
                        case 'AUTO_LINK':
                        case 'SHORT_INTEGER':
                        case 'UINTEGER':
                        case 'INTEGER':
                        case 'GROUP':
                            $field_type = 'integer';
                            break;
                        case 'MEMBER':
                            $field_type = 'username';
                            break;
                        case 'REAL':
                            $field_type = 'float';
                            break;
                        case 'URLPATH':
                        case 'IP':
                        case 'LONG_TEXT':
                        case 'SHORT_TEXT':
                        case 'LONG_TRANS':
                        case 'SHORT_TRANS':
                        case 'LONG_TRANS__COMCODE':
                        case 'SHORT_TRANS__COMCODE':
                            $field_type = 'line';
                            break;
                        case 'MINIID_TEXT':
                        case 'ID_TEXT':
                            $field_type = 'codename';
                            break;
                        case 'LANGUAGE_NAME':
                            $field_type = 'list';
                            require_code('lang2');
                            $_extra = array_keys(find_all_langs());
                            $extra = array();
                            foreach (array_keys(find_all_langs()) as $lang) {
                                $extra[$lang] = lookup_language_full_name($lang);
                            }
                            break;
                    }
                }
            }

            $field_title = array_key_exists($field_name, $labels) ? $labels[$field_name] : titleify(preg_replace('#^filter\_#', '', $field_name));

            $default_value = read_filtercode_parameter_from_env($field_name, $field_type);

            $fields_needed[] = array(
                $field_type,
                $field_name,
                $field_title,
                $default_value,
                $extra,
            );
        }
    }

    require_code('form_templates');

    $form_fields = new Tempcode();
    foreach ($fields_needed as $field) {
        list($field_type, $field_name, $field_label, $default_value, $extra) = $field;

        switch ($field_type) { // NB: These type codes also vaguely correspond to field hooks, just for convention (we don't use them)
            case 'time':
                $form_fields->attach(form_input_date($field_label, '', 'filter_' . $field_name, false, $default_value == '', true, ($default_value == '') ? null : intval($default_value)));
                break;

            case 'date':
                $form_fields->attach(form_input_date($field_label, '', 'filter_' . $field_name, false, $default_value == '', false, ($default_value == '') ? null : intval($default_value)));
                break;

            case 'days':
                $list_options = new Tempcode();
                $days_options = array();
                foreach (array(2, 5, 15, 30, 45, 60, 120, 240, 365) as $days_option) {
                    $days_options[strval(time() - 60 * 60 * 24 * $days_option)] = do_lang_tempcode('SUBMIT_AGE_DAYS', escape_html(integer_format($days_option)));
                }
                $list_options->attach(form_input_list_entry('', $default_value == '', ''));
                foreach ($days_options as $key => $val) {
                    $list_options->attach(form_input_list_entry($key, $default_value == $key, $val));
                }
                $form_fields->attach(form_input_list($field_label, '', 'filter_' . $field_name, $list_options, null, false, false));
                break;

            case 'tick':
                $list_options = new Tempcode();
                foreach (array('' => '', '0' => do_lang_tempcode('NO'), '1' => do_lang_tempcode('YES')) as $key => $val) {
                    $list_options->attach(form_input_list_entry($key, $default_value == $key, $val));
                }
                $form_fields->attach(form_input_list($field_label, '', 'filter_' . $field_name, $list_options, null, false, false));
                break;

            case 'rating':
                $list_options = new Tempcode();
                $list_options->attach(form_input_list_entry('', $default_value == '', ''));
                foreach (array(1 => '&#10025;', 4 => '&#10025;&#10025;', 6 => '&#10025;&#10025;&#10025;', 8 => '&#10025;&#10025;&#10025;&#10025;', 10 => '&#10025;&#10025;&#10025;&#10025;&#10025;') as $rating => $rating_label) {
                    $list_options->attach(form_input_list_entry(strval($rating), $default_value == strval($rating), protect_from_escaping($rating_label)));
                }
                $form_fields->attach(form_input_list($field_label, '', 'filter_' . $field_name, $list_options, null, false, false));
                break;

            case 'list':
                $list_options = new Tempcode();
                $list_options->attach(form_input_list_entry('', $default_value == '', ''));
                foreach ($extra as $key => $val) {
                    $list_options->attach(form_input_list_entry($key, $default_value == $key, $val));
                }
                $form_fields->attach(form_input_list($field_label, '', 'filter_' . $field_name, $list_options, null, false, false));
                break;

            case 'list_multi':
                $list_options = new Tempcode();
                foreach ($extra as $key => $val) {
                    $list_options->attach(form_input_list_entry($key, preg_match('#(^|,)' . preg_quote($key, '#') . '(,|$)#', $default_value) != 0, $val));
                }
                $form_fields->attach(form_input_multi_list($field_label, '', 'filter_' . $field_name, $list_options, null, 5, false));
                break;

            case 'linklist':
                foreach ($extra as $key => $val) {
                    $_links[$val] = $key;
                }
                break;

            case 'float':
                $form_fields->attach(form_input_float($field_label, '', 'filter_' . $field_name, ($default_value == '') ? null : floatval($default_value), false));
                break;

            case 'integer':
                $form_fields->attach(form_input_integer($field_label, '', 'filter_' . $field_name, ($default_value == '') ? null : intval($default_value), false));
                break;

            case 'email':
                $form_fields->attach(form_input_email($field_label, '', 'filter_' . $field_name, $default_value, false));
                break;

            case 'author':
                $form_fields->attach(form_input_author($field_label, '', 'filter_' . $field_name, $default_value, false));
                break;

            case 'username':
                $form_fields->attach(form_input_username($field_label, '', 'filter_' . $field_name, $default_value, false));
                break;

            case 'codename':
                $form_fields->attach(form_input_codename($field_label, '', 'filter_' . $field_name, $default_value, false));
                break;

            case 'line':
            default:
                $form_fields->attach(form_input_line($field_label, '', 'filter_' . $field_name, $default_value, false));
                break;
        }
    }

    return array($form_fields, $filter, $_links);
}

/**
 * Parse some string based Filtercode search filters into the expected array structure.
 *
 * @param  string $filter String-based search filter
 * @return array Parsed structure
 */
function parse_filtercode($filter)
{
    $parsed = array();
    $filters = explode((strpos($filter, "\n") !== false) ? "\n" : ',', $filter);
    foreach ($filters as $bit) {
        if ($bit != '') {
            $parts = preg_split('#(<[\w\-]+>|<=|>=|<>|!=|<|>|==|~=|=|~|@|\#)#', $bit, 2, PREG_SPLIT_DELIM_CAPTURE); // NB: preg_split is not greedy, so longest operators need to go first
            if (count($parts) == 3) {
                $parts[0] = ltrim($parts[0]);
                $is_join = false;
                if ((substr($parts[0], 0, 1) == '{') && (substr($parts[2], -1) == '}')) {
                    $is_join = true;
                    $parts[0] = substr($parts[0], 1);
                    $parts[2] = substr($parts[2], 0, strlen($parts[2]) - 1);
                }
                $parsed[] = array($parts[0], $parts[1], $parts[2], $is_join);
            }
        }
    }
    return $parsed;
}

/**
 * Take some parsed Filtercode search filters into the string format (i.e. reverse of parse_filtercode).
 *
 * @param  array $parsed Parsed structure
 * @return string String-based search filter
 */
function unparse_filtercode($parsed)
{
    $filter = '';
    foreach ($parsed as $_filter) {
        list($filter_key, $filter_op, $filter_val, $is_join) = $_filter;
        if ($filter != '') {
            $filter .= ',';
        }
        $filter .= ($is_join ? '{' : '') . $filter_key . $filter_op . $filter_val . ($is_join ? '}' : '');
    }
    return $filter;
}

/**
 * Find field by checking fields API, by field name.
 *
 * @param  object $db Database connection
 * @param  array $info Content type info
 * @param  ?ID_TEXT $catalogue_name Name of the catalogue (null: unknown; reduces performance)
 * @param  array $extra_join List of joins (passed as reference)
 * @param  array $extra_select List of selects (passed as reference)
 * @param  ID_TEXT $filter_key The field to get
 * @param  string $filter_val The field value for this
 * @param  array $db_fields Database field data
 * @param  string $table_join_code What the database will join the table with
 * @return ?array A triple: Proper database field name to access with, The fields API table type (blank: no special table), The new filter value (null: error)
 * @ignore
 */
function _fields_api_filtercode_named($db, $info, $catalogue_name, &$extra_join, &$extra_select, $filter_key, $filter_val, $db_fields, $table_join_code)
{
    require_code('fields');
    $fields = get_catalogue_fields($catalogue_name);
    if (count($fields) != 0) {
        foreach ($fields as $i => $field) {
            if (get_translated_text($field['cf_name']) == $filter_key) {
                return _fields_api_filtercode($db, $info, $catalogue_name, $extra_join, $extra_select, 'field_' . strval($field['id']), $filter_val, $db_fields, $table_join_code);
            }
        }
    }
    if (strpos($filter_key, '.') !== false) {
        list($_catalogue_name, $filter_key) = explode('.', $filter_key, 2);
        $fields = get_catalogue_fields($_catalogue_name);
        if (count($fields) != 0) {
            if ($filter_key == 'id') { // ID field of a named catalogue (we need to handle this here, as otherwise a name prefix will be considered a real DB table name)
                $catalogue_key = generate_filtercode_join_key_from_string($_catalogue_name);
                $table_join_code_here = $table_join_code . '_' . $catalogue_key;
                $extra_join[$table_join_code_here] = ' JOIN ' . $db->get_table_prefix() . 'catalogue_entries ' . $table_join_code_here . ' ON 1=1'; // Actual join condition will happen via WHERE clause, which SQL engines typically can optimise fine

                return array($table_join_code_here . '.id', '', $filter_val);
            } else {
                foreach ($fields as $i => $field) {
                    if (get_translated_text($field['cf_name']) == $filter_key) {
                        return _fields_api_filtercode($db, $info, $catalogue_name, $extra_join, $extra_select, $_catalogue_name . '.field_' . strval($i), $filter_val, $db_fields, $table_join_code);
                    }
                }
            }
        }
    }
    return null;
}

/**
 * Find field by checking fields API, by field ID.
 *
 * @param  object $db Database connection
 * @param  array $info Content type info
 * @param  ?ID_TEXT $catalogue_name Name of the catalogue (null: unknown; reduces performance)
 * @param  array $extra_join List of joins (passed as reference)
 * @param  array $extra_select List of selects (passed as reference)
 * @param  ID_TEXT $filter_key The field to get
 * @param  string $filter_val The field value for this
 * @param  array $db_fields Database field data
 * @param  string $table_join_code What the database will join the table with
 * @return ?array A triple: Proper database field name to access with, The fields API table type (blank: no special table), The new filter value (null: error)
 * @ignore
 */
function _fields_api_filtercode($db, $info, $catalogue_name, &$extra_join, &$extra_select, $filter_key, $filter_val, $db_fields, $table_join_code)
{
    $matches = array();
    if (preg_match('#^((.*)\.)?field\_(\d+)#', $filter_key, $matches) == 0) {
        return null;
    }

    require_code('fields');
    $this_catalogue_name = ($matches[1] == '') ? $catalogue_name : $matches[2];
    $fields = list_to_map('id', get_catalogue_fields($this_catalogue_name));

    $field_id = intval($matches[3]);

    if ((!isset($fields[$field_id])) || ($fields[$field_id]['cf_put_in_search'] == 0)) {
        return null;
    }

    $ob = get_fields_hook($fields[$field_id]['cf_type']);
    list(, , $table) = $ob->get_field_value_row_bits($fields[$field_id]);

    $catalogue_key = generate_filtercode_join_key_from_string($this_catalogue_name);

    if ($this_catalogue_name != $catalogue_name) {
        $table_join_code_here = $table_join_code . '_' . $catalogue_key;

        $extra_join[$table_join_code_here] = ' JOIN ' . $db->get_table_prefix() . 'catalogue_entries ' . $table_join_code_here . ' ON 1=1'; // Actual join condition will happen via WHERE clause, which SQL engines typically can optimise fine
    } else {
        $table_join_code_here = $table_join_code;
    }

    if ((!isset($info['content_type'])) || ($info['content_type'] == 'catalogue_entry')) {
        $join_sql = ' LEFT JOIN ' . $db->get_table_prefix() . 'catalogue_efv_' . $table . ' f' . strval($field_id) . '_' . $catalogue_key . ' ON f' . strval($field_id) . '_' . $catalogue_key . '.ce_id=' . $table_join_code_here . '.id AND f' . strval($field_id) . '_' . $catalogue_key . '.cf_id=' . strval($field_id);
    } else {
        $id_field = is_array($info['id_field']) ? $info['id_field'][0] : $info['id_field'];
        $join_sql = ' LEFT JOIN ' . $db->get_table_prefix() . 'catalogue_entry_linkage l' . strval($field_id) . '_' . $catalogue_key . ' ON ' . db_string_equal_to('l' . strval($field_id) . '_' . $catalogue_key . '.content_type', $info['content_type']) . ' AND l' . strval($field_id) . '_' . $catalogue_key . '.content_id=' . db_cast($table_join_code_here . '.' . $id_field, 'CHAR') . ' LEFT JOIN ' . $db->get_table_prefix() . 'catalogue_efv_' . $table . ' f' . strval($field_id) . '_' . $catalogue_key . ' ON f' . strval($field_id) . '_' . $catalogue_key . '.ce_id=l' . strval($field_id) . '_' . $catalogue_key . '.catalogue_entry_id AND f' . strval($field_id) . '_' . $catalogue_key . '.cf_id=' . strval($field_id);
    }

    if ((strpos($table, '_trans') !== false) && (multi_lang_content())) {
        $join_sql .= ' LEFT JOIN ' . $db->get_table_prefix() . 'translate t' . strval($field_id) . '_' . $catalogue_key . ' ON f' . strval($field_id) . '_' . $catalogue_key . '.cv_value=t' . strval($field_id) . '_' . $catalogue_key . '.id AND t' . strval($field_id) . '_' . $catalogue_key . '.' . db_string_equal_to('language', user_lang());

        if (!in_array($join_sql, $extra_join)) {
            $extra_join[$filter_key] = $join_sql;
        }
        return array('t' . strval($field_id) . '_' . $catalogue_key . '.text_original', $table, $filter_val);
    }

    if (!in_array($join_sql, $extra_join)) {
        $extra_join[$filter_key] = $join_sql;
    }
    return array('f' . strval($field_id) . '_' . $catalogue_key . '.cv_value', $table, $filter_val);
}

/**
 * Produce a key we can use for SQL join names, from some string that may be too complex for this. It should be reproducible and unique for the given input.
 *
 * @param  string $str Input string
 * @return string Suitable key name
 */
function generate_filtercode_join_key_from_string($str)
{
    return substr(md5($str), 0, 3);
}

/**
 * Make sure we are doing necessary join to be able to access the given field
 *
 * @param  object $db Database connection
 * @param  array $info Content type info
 * @param  ?ID_TEXT $catalogue_name Name of the catalogue (null: unknown; reduces performance)
 * @param  array $extra_join List of joins (passed as reference)
 * @param  array $extra_select List of selects (passed as reference)
 * @param  ID_TEXT $filter_key The field to get
 * @param  string $filter_val The field value for this
 * @param  array $db_fields Database field data
 * @param  string $table_join_code What the database will join the table with
 * @return ?array A triple: Proper database field name to access with, The fields API table type (blank: no special table), The new filter value (null: error)
 * @ignore
 */
function _default_conv_func($db, $info, $catalogue_name, &$extra_join, &$extra_select, $filter_key, $filter_val, $db_fields, $table_join_code)
{
    if (isset($info['id_field'])) {
        $first_id_field = (is_array($info['id_field']) ? implode(',', $info['id_field']) : $info['id_field']);
    } else {
        $first_id_field = 'id';
    }

    // Special case for ratings
    $matches = ($filter_key == 'compound_rating') ? array('', $info['feedback_type_code']) : array();
    if (($filter_key == 'compound_rating') || (preg_match('#^compound_rating\_\_(.+)#', $filter_key, $matches) != 0)) {
        $clause = '(SELECT SUM(rating-1) FROM ' . $db->get_table_prefix() . 'rating rat WHERE ' . db_string_equal_to('rat.rating_for_type', $matches[1]) . ' AND rat.rating_for_id=' . db_cast($table_join_code . '.' . $first_id_field, 'CHAR') . ')';
        $extra_select[$filter_key] = ', ' . $clause . ' AS compound_rating_' . fix_id($matches[1]);
        return array($clause, '', $filter_val);
    }
    $matches = ($filter_key == 'average_rating') ? array('', $info['feedback_type_code']) : array();
    if (($filter_key == 'average_rating') || (preg_match('#^average_rating\_\_(.+)#', $filter_key, $matches) != 0)) {
        $table_and_where = $db->get_table_prefix() . 'rating rat WHERE ' . db_string_equal_to('rat.rating_for_type', $matches[1]) . ' AND rat.rating_for_id=' . db_cast($table_join_code . '.' . $first_id_field, 'CHAR');
        $clause = '(SELECT AVG(' . db_cast('rating' , 'FLOAT') . ')/2 FROM ' . $table_and_where . ')';
        if ($catalogue_name !== null) {
            $time_sensitivity = get_value('time_sensitive_rankings__' . $catalogue_name, '');
            if ($time_sensitivity != '') {
                $clause = '(SELECT AVG(' . db_cast('rating' , 'FLOAT') . ') OVER (ORDER BY rating_time DESC ROWS BETWEEN 0 PRECEDING AND ' . strval(intval($time_sensitivity)) . ' FOLLOWING)/2 FROM ' . $table_and_where . ' LIMIT 1)'; // TODO: Make not MySQL-only in v11 (LIMIT)

                // The below may work in databases that do not support window functions - but won't work in MariaDB or MySQL < 8.0.14
                //$table_and_where = '(SELECT * FROM ' . $table_and_where . ' LIMIT ' . strval(intval($time_sensitivity)) . ') ' . uniqid('');
                //$clause = '(SELECT AVG(' . db_cast('rating' , 'FLOAT') . ')/2 FROM ' . $table_and_where . ')';
            }
        }
        $clause = db_function('COALESCE', array($clause, '2.5'));
        $extra_select[$filter_key] = ', ' . $clause . ' AS average_rating_' . fix_id($matches[1]);
        return array($clause, '', $filter_val);
    }

    // Random
    $matches = ($filter_key == 'fixed_random') ? array('', $info['feedback_type_code']) : array();
    if (($filter_key == 'fixed_random') || (preg_match('#^fixed_random\_\_(.+)#', $filter_key, $matches) != 0)) {
        $clause = db_cast('r.' . $first_id_field, 'INT');
        $clause = '(' . db_function('MOD', array($clause, date('d'))) . ')';
        $extra_select[$filter_key] = ', ' . $clause . ' AS fixed_random_' . fix_id($matches[1]);
        return array($clause, '', $filter_val);
    }

    // Special case for SEO fields
    if ($filter_key == 'meta_description') {
        $seo_type_code = isset($info['seo_type_code']) ? $info['seo_type_code'] : '!!!ERROR!!!';
        $join = ' LEFT JOIN ' . $db->get_table_prefix() . 'seo_meta sm ON sm.meta_for_id=' . db_cast($table_join_code . '.' . $first_id_field, 'CHAR') . ' AND ' . db_string_equal_to('sm.meta_for_type', $seo_type_code);
        if (multi_lang_content()) {
            $join .= ' LEFT JOIN ' . $db->get_table_prefix() . 'translate dt ON dt.id=sm.meta_description AND dt.' . db_string_equal_to('language', user_lang());
            $filter_key = 'dt.text_original';
        }
        if (!in_array($join, $extra_join)) {
            $extra_join[$filter_key] = $join;
        }
        return array($filter_key, '', $filter_val);
    }
    if ($filter_key == 'meta_keywords') {
        $seo_type_code = isset($info['seo_type_code']) ? $info['seo_type_code'] : '!!!ERROR!!!';
        if (multi_lang_content()) {
            $clause = '(' . db_function('GROUP_CONCAT', array('text_original', $db->get_table_prefix() . 'seo_meta_keywords kw JOIN ' . $db->get_table_prefix() . 'translate kwt ON kwt.id=kw.meta_keyword AND ' . db_string_equal_to('kwt.language', user_lang()) . ' WHERE kw.meta_for_id=' . db_cast($table_join_code . '.' . $first_id_field, 'CHAR') . ' AND ' . db_string_equal_to('kw.meta_for_type', $seo_type_code))) . ')';
        } else {
            $clause = '(' . db_function('GROUP_CONCAT', array('meta_keyword', $db->get_table_prefix() . 'seo_meta_keywords kw WHERE kw.meta_for_id=' . db_cast($table_join_code . '.' . $first_id_field, 'CHAR') . ' AND ' . db_string_equal_to('kw.meta_for_type', $seo_type_code))) . ')';
        }
        $extra_select[$filter_key] = ', ' . $clause . ' AS meta_keyword_' . fix_id($seo_type_code);
        return array($clause, '', $filter_val);
    }

    // Fields API
    if ((preg_match('#^((.*)\.)?field\_(\d+)#', $filter_key) != 0) && (isset($info['content_type']))) {
        return _fields_api_filtercode($db, $info, '_' . $info['content_type'], $extra_join, $extra_select, $filter_key, $filter_val, $db_fields, $table_join_code);
    }

    // Natural fields...

    $matches = array();
    preg_match('#^((.*)\.)?(.*)#', $filter_key, $matches);

    $table_scope = $matches[2];
    $inner_filter_key = $matches[3];
    if ($table_scope != '') {
        $table_key = generate_filtercode_join_key_from_string($table_scope);
        $table_join_code_here = $table_join_code . '_' . $table_key;

        $extra_join[$table_join_code_here] = ' JOIN ' . $db->get_table_prefix() . $table_scope . ' ' . $table_join_code_here . ' ON 1=1'; // Actual join condition will happen via WHERE clause, which SQL engines typically can optimise fine

        $db_fields_here = collapse_2d_complexity('m_name', 'm_type', $db->query_select('db_meta', array('m_name', 'm_type'), array('m_table' => $table_scope)));
    } else {
        $table_join_code_here = $table_join_code;
        $db_fields_here = $db_fields;
    }

    $field_type = '';
    if (array_key_exists($inner_filter_key, $db_fields_here)) {
        switch (str_replace(array('?', '*'), array('', ''), $db_fields_here[$inner_filter_key])) {
            case 'AUTO':
            case 'AUTO_LINK':
            case 'SHORT_INTEGER':
            case 'UINTEGER':
            case 'INTEGER':
            case 'TIME':
            case 'BINARY':
            case 'GROUP':
                $field_type = 'integer';
                $filter_key = $table_join_code_here . '.' . $inner_filter_key;
                break;
            case 'MEMBER':
                $field_type = 'integer';
                if (($filter_val != '') && (preg_match('#^[\d|-]+$#', $filter_val) == 0)) {
                    $_filter_val = $GLOBALS['FORUM_DRIVER']->get_member_from_username($filter_val);
                    $filter_val = ($_filter_val === null) ? '' : strval($_filter_val);
                }
                $filter_key = $table_join_code_here . '.' . $inner_filter_key;
                break;
            case 'REAL':
                $field_type = 'float';
                $filter_key = $table_join_code_here . '.' . $inner_filter_key;
                break;
            case 'URLPATH':
            case 'LANGUAGE_NAME':
            case 'IP':
            case 'MINIID_TEXT':
            case 'ID_TEXT':
            case 'LONG_TEXT':
            case 'SHORT_TEXT':
                $field_type = 'line';
                $filter_key = $table_join_code_here . '.' . $inner_filter_key;
                break;
            case 'LONG_TRANS':
            case 'SHORT_TRANS':
            case 'LONG_TRANS__COMCODE':
            case 'SHORT_TRANS__COMCODE':
                $field_type = 'line';
                if (multi_lang_content()) {
                    static $filter_i = 1;
                    $extra_join[$inner_filter_key] = ' LEFT JOIN ' . $db->get_table_prefix() . 'translate ft' . strval($filter_i) . ' ON ft' . strval($filter_i) . '.id=' . strval($inner_filter_key) . ' AND ft' . strval($filter_i) . '.' . db_string_equal_to('language', user_lang());
                    $filter_key = 'ft' . strval($filter_i) . '.text_original';
                    $filter_i++;
                }
                break;
        }
    } else {
        // Fields API (named)
        if (isset($info['content_type'])) {
            return _fields_api_filtercode_named($db, $info, '_' . $info['content_type'], $extra_join, $extra_select, $filter_key, $filter_val, $db_fields, $table_join_code);
        }

        return null;
    }

    if (strpos($filter_key, '.') !== false) {
        $new_filter_key = $filter_key;
    } else {
        $new_filter_key = $table_join_code . '.' . $filter_key;
    }

    return array($new_filter_key, $field_type, $filter_val);
}

/**
 * Convert some Filtercode filters into some SQL fragments.
 *
 * @param  object $db Database object to use
 * @param  array $filters Parsed Filtercode structure
 * @param  ?ID_TEXT $content_type The content type (null: no function needed, direct in-table mapping always works)
 * @param  ?string $context First parameter to send to the conversion function, may mean whatever that function wants it to. If we have no conversion function, this is the name of a table to read field metadata from (null: none)
 * @param  string $table_join_code What the database will join the table with
 * @return array Tuple: array of extra select (implode with ''), array of extra join (implode with ''), string of extra where
 */
function filtercode_to_sql($db, $filters, $content_type = null, $context = null, $table_join_code = 'r')
{
    // Nothing to do?
    if (($filters === null) || ($filters == array())) {
        return array(array(), array(), '');
    }

    // Get the conversion function. The conversion function takes field names and works out how that results in SQL
    $info = array();
    $conv_func = '_default_conv_func';
    if (!empty($content_type)) {
        require_code('content');
        $ob = get_content_object($content_type);
        $info = $ob->info();
        if ($info !== null) {
            $info['content_type'] = $content_type; // We'll need this later, so add it in

            if (array_key_exists('filtercode', $info)) {
                if (strpos($info['filtercode'], '::') !== false) {
                    list($code_file, $conv_func) = explode('::', $info['filtercode']);
                    require_code($code_file);
                } else {
                    $conv_func = $info['filtercode'];
                }
            }
        }
    }

    $extra_select = array();
    $extra_join = array();
    $where_clause = '';

    $disallowed_fields = array('notes');
    $disallowed_fields[] = 'notes';
    if (isset($info['filtercode_protected_fields'])) {
        $disallowed_fields = array_merge($disallowed_fields, $info['filtercode_protected_fields']);
    }
    $configured_protected_fields = get_value('filtercode_protected_fields');
    if (($configured_protected_fields !== null) && ($configured_protected_fields != '')) {
        $disallowed_fields = array_merge($disallowed_fields, explode(',', $configured_protected_fields));
    }

    // Load up fields to compare to
    $db_fields = array();
    if (isset($info['table'])) {
        $table = $info['table'];
        static $db_fields_for_table = array();
        if (isset($db_fields_for_table[$table])) {
            $db_fields = $db_fields_for_table[$table];
        } else {
            $db_fields = collapse_2d_complexity('m_name', 'm_type', $db->query_select('db_meta', array('m_name', 'm_type'), array('m_table' => $table)));
            $db_fields_for_table[$table] = $db_fields;
        }
    } else {
        $table = null;
    }

    foreach ($filters as $filter_i => $filter) {
        list($filter_keys, $filter_op, $filter_val, $is_join) = $filter;

        $filter_val = str_replace('\n', "\n", $filter_val);

        // Allow specification of reading from the environment
        $matches = array();
        if (preg_match('#^<([^<>]+)>$#', $filter_op, $matches) != 0) {
            $filter_op = either_param_string($matches[1], '~=');
        }
        if (preg_match('#^<([^<>]+)>$#', $filter_val, $matches) != 0) {
            $filter_val = read_filtercode_parameter_from_env($matches[1]);
        }

        if (($filter_op != '==') && ($filter_op != '!=') && (!$is_join)) {
            if ($filter_val == '') {
                continue;
            }
        }

        $alt = '';

        // Go through each filter (these are ANDd)
        foreach (explode('|', $filter_keys) as $filter_key) {
            if (in_array($filter_key, $disallowed_fields)) {
                continue;
            }

            $filter_key = preg_replace('#[^\w\s\|\.]#', '', $filter_key); // So can safely come from environment
            $bits = call_user_func_array($conv_func, array($db, $info, &$context, &$extra_join, &$extra_select, &$filter_key, $filter_val, $db_fields, $table_join_code)); // call_user_func_array has to be used for reference passing, bizarrely
            if ($bits === null) {
                require_lang('filtercode');
                attach_message(do_lang_tempcode('FILTERCODE_UNKNOWN_FIELD', escape_html($filter_key)), 'warn');

                continue;
            }
            list($filter_key, $field_type, $filter_val) = $bits;

            if ($is_join) {
                $bits2 = call_user_func_array($conv_func, array($db, $info, &$context, &$extra_join, &$extra_select, &$filter_val, '', $db_fields, $table_join_code)); // call_user_func_array has to be used for reference passing, bizarrely
                if (is_null($bits2)) {
                    require_lang('filtercode');
                    attach_message(do_lang_tempcode('FILTERCODE_UNKNOWN_FIELD', escape_html($filter_val)), 'warn');

                    continue;
                }
                list($filter_val, ,) = $bits2;

                $filter_val = preg_replace('#[^\w\s\|\.]#', '', $filter_val); // So can safely come from environment
            }

            if (in_array($filter_key, $disallowed_fields)) {
                continue;
            }

            switch ($filter_op) {
                case '@':
                    if ((preg_match('#^[\d\.]+-[\d\.]+$#', $filter_val) != 0) && (($field_type == 'integer') || ($field_type == 'float') || ($field_type == ''))) {
                        if ($alt != '') {
                            $alt .= ' OR ';
                        }
                        $_filter_val = explode('-', $filter_val, 2);
                        //$alt .= $filter_key . '>=' . $_filter_val[0] . ' AND ' . $filter_key . '<=' . $_filter_val[1];     Less efficient than the below, due to possibility of $filter_key being a subselect
                        $alt .= $filter_key . ' BETWEEN ' . $_filter_val[0] . ' AND ' . $_filter_val[1];
                    }
                    break;

                case '<':
                case '>':
                case '<=':
                case '>=':
                    if (($is_join) || ((is_numeric($filter_val)) && (($field_type == 'integer') || ($field_type == 'float') || ($field_type == '')))) {
                        if ($alt != '') {
                            $alt .= ' OR ';
                        }
                        $alt .= $filter_key . $filter_op . $filter_val;
                    } else {
                        if ($alt != '') {
                            $alt .= ' OR ';
                        }

                        $alt .= 'REPLACE(' . $filter_key . ',\'-\',\'\')' . $filter_op . 'REPLACE(\'' . db_escape_string($filter_val) . '\',\'-\',\'\')';
                    }
                    break;

                case '<>':
                case '!=':
                    if (($is_join) || ((is_numeric($filter_val)) && (($field_type == 'integer') || ($field_type == 'float') || ($field_type == '')))) {
                        if (($filter_val != '') || ($filter_op == '!=')) {
                            if ($alt != '') {
                                $alt .= ' OR ';
                            }
                            $alt .= $filter_key . '<>' . $filter_val;
                        }
                    } else {
                        if (($filter_val != '') || ($filter_op == '!=')) {
                            if ($alt != '') {
                                $alt .= ' OR ';
                            }
                            $alt .= db_string_not_equal_to($filter_key, $filter_val);
                        }
                    }
                    break;

                case '=':
                case '==':
                    if ($alt != '') {
                        $alt .= ' OR ';
                    }
                    $alt .= '(';
                    foreach (explode('|', $filter_val) as $it_id => $it_value) {
                        if ($it_id != 0) {
                            $alt .= ' OR ';
                        }
                        if (($is_join) || ((is_numeric($it_value)) && (($field_type == 'integer') || ($field_type == 'float') || ($field_type == '')))) {
                            $alt .= $filter_key . '=' . $it_value;
                        } else {
                            $alt .= db_string_equal_to($filter_key, $it_value);
                        }
                    }
                    $alt .= ')';
                    break;

                case '~':
                    require_code('database_search');
                    if ((db_has_full_text($GLOBALS['SITE_DB']->connection_read, $table, $filter_key)) && (strlen($filter_val) > get_minimum_search_length())) {
                        if ($filter_val != '') {
                            if ($alt != '') {
                                $alt .= ' OR ';
                            }
                            $alt .= '(';
                            foreach (explode('|', $filter_val) as $it_id => $it_value) {
                                if ($it_id != 0) {
                                    $alt .= ' OR ';
                                }
                                $alt .= str_replace('?', $filter_key, db_full_text_assemble($it_value, false));
                            }
                            $alt .= ')';
                        }
                        break;
                    }
                // intentionally rolls on...

                case '#':
                    if ($filter_val != '') {
                        if ($alt != '') {
                            $alt .= ' OR ';
                        }
                        $alt .= '(';
                        foreach (explode('|', $filter_val) as $it_id => $it_value) {
                            if ($it_id != 0) {
                                $alt .= ' OR ';
                            }
                            $alt .= '(';
                            foreach (array('|', "\n"/*list_multi*/, ','/*GROUP_CONCAT*/) as $delimi_i => $delim) {
                                if ($delimi_i != 0) {
                                    $alt .= ' OR ';
                                }

                                if ($is_join) {
                                    $alt .= $filter_key . ' LIKE ' . db_function('CONCAT', array($it_value, '\'' . $delim . '%\''));
                                    $alt .= ' OR ';
                                    $alt .= $filter_key . ' LIKE ' . db_function('CONCAT', array('\'%' . $delim . '\'',  $it_value));
                                    $alt .= ' OR ';
                                    $alt .= $filter_key . ' LIKE ' . db_function('CONCAT', array('\'%' . $delim . '\'', $it_value, '\'' . $delim . '%\''));
                                    $alt .= ' OR ';
                                    $alt .= $filter_key . '=' . $it_value;
                                } else {
                                    $alt .= $filter_key . ' LIKE \'' . db_encode_like($it_value . '' . $delim . '%') . '\'';
                                    $alt .= ' OR ';
                                    $alt .= $filter_key . ' LIKE \'' . db_encode_like('%' . $delim . '' . $it_value) . '\'';
                                    $alt .= ' OR ';
                                    $alt .= $filter_key . ' LIKE \'' . db_encode_like('%' . $delim . '' . $it_value . '' . $delim . '%') . '\'';
                                    $alt .= ' OR ';
                                    $alt .= db_string_equal_to($filter_key, $it_value);
                                }
                            }
                            $alt .= ')';
                        }
                        $alt .= ')';
                    }
                    break;

                case '~=':
                    $filter_val = trim($filter_val, '*'); // In case user does not understand that this is not a wildcard search

                    if ($filter_val != '') {
                        if ($alt != '') {
                            $alt .= ' OR ';
                        }
                        $alt .= '(';
                        foreach (explode('|', $filter_val) as $it_id => $it_value) {
                            if ($it_id != 0) {
                                $alt .= ' OR ';
                            }
                            if ($is_join) {
                                $alt .= $filter_key . ' LIKE ' . db_function('CONCAT', array('\'%\'', $it_value, '\'%\''));
                            } else {
                                $alt .= $filter_key . ' LIKE \'' . db_encode_like('%' . $it_value . '%') . '\'';
                            }
                        }
                        $alt .= ')';
                    }
                    break;

                default:
                    fatal_exit(do_lang_tempcode('INTERNAL_ERROR')); // Impossible opcode
            }
        }
        if ($alt != '') {
            $where_clause .= ' AND (' . $alt . ')';
        }
    }

    return array($extra_select, $extra_join, $where_clause);
}

/**
 * Get template-ready details for a merger-link style selectcode. This is used to do filtering via drill-down using links.
 *
 * @param  string $_link_filter Filtercode filter
 * @return array Template-ready details
 */
function prepare_filtercode_merger_link($_link_filter)
{
    $active_filter = parse_filtercode(either_param_string('active_filter', ''));
    $link_filter = parse_filtercode($_link_filter);
    $extra_params = array();
    $old_filter = $active_filter;
    foreach ($link_filter as $filter_bits) {
        list($filter_key, $filter_op, $filter_val) = $filter_bits;

        // Propagate/inject in filter value
        $matches = array();
        if (preg_match('#^<([^<>]+)>$#', $filter_val, $matches) != 0) {
            $filter_val = read_filtercode_parameter_from_env($matches[1]);
            $extra_params['filter_' . $matches[1]] = $filter_val;
        }

        // Take out any rules pertaining to this key from the active filter
        foreach ($old_filter as $i2 => $filter_bits_2) {
            list($filter_key_2, $filter_op_2, $filter_val_2) = $filter_bits_2;
            if ($filter_key_2 == $filter_key) {
                unset($old_filter[$i2]);
            }
        }
    }
    $extra_params['active_filter'] = unparse_filtercode(array_merge($old_filter, $link_filter));
    $link_url = get_self_url(false, false, $extra_params);
    $active = true;
    foreach ($extra_params as $key => $val) {
        if (read_filtercode_parameter_from_env($key) != $val) {
            $active = false;
        }
    }

    return array(
        'ACTIVE' => $active,
        'URL' => $link_url,
    );
}
