DELIMITER $$
# Keep this as FIRST line!!!
# Keep this for CLI use
# PHP: Strip this if used by PHP and explode by END$$

###
#
# GETFUNCTIONSHASH() is used for checking whether this file has been played properly in DatabaseUpdate.php
#
DROP FUNCTION IF EXISTS GETFUNCTIONSHASH;
CREATE FUNCTION GETFUNCTIONSHASH()
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    RETURN '%%FUNCTIONSHASH%%';
END$$

###
#
# Encodes special characters in the input text into HTML-safe numeric entities (e.g., < becomes &#60;).
#
DROP FUNCTION IF EXISTS QENT_ENCODE;

CREATE FUNCTION QENT_ENCODE(input_text TEXT)
    RETURNS TEXT
    DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE len INT;
    DECLARE current_char VARCHAR(1);
    DECLARE char_code INT;
    DECLARE temp_text TEXT DEFAULT '';

    IF input_text IS NULL THEN RETURN NULL; END IF;

    SET len = CHAR_LENGTH(input_text);

    WHILE i <= len DO
        SET current_char = SUBSTRING(input_text, i, 1);

        -- Convert to utf16, then extract the hex, then convert to decimal
        SET char_code = CONV(HEX(CONVERT(current_char USING utf16)), 16, 10);

        IF char_code > 127 OR char_code IN (34, 38, 39, 60, 62, 123, 125) THEN
            SET temp_text = CONCAT(temp_text, CONCAT('&#', char_code, ';'));
        ELSE
            SET temp_text = CONCAT(temp_text, current_char);
        END IF;

        SET i = i + 1;
    END WHILE;

    RETURN temp_text;
END$$


###
#
# Decodes HTML numeric and common named entities in the input text back into their original characters.
#
DROP FUNCTION IF EXISTS QENT_DECODE;

CREATE FUNCTION QENT_DECODE(input_text TEXT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE temp_text TEXT DEFAULT input_text;
    DECLARE entity_pos INT;
    DECLARE entity_end INT;
    DECLARE entity_str VARCHAR(20);
    DECLARE char_code INT;
    DECLARE replacement CHAR(1);
    DECLARE loop_counter INT DEFAULT 0;
    DECLARE max_loops INT DEFAULT 1000;

    IF input_text IS NULL THEN RETURN NULL; END IF;

    SET temp_text = REPLACE(temp_text, '&quot;', '"');
    SET temp_text = REPLACE(temp_text, '&apos;', '\'');
    SET temp_text = REPLACE(temp_text, '&amp;', '&');
    SET temp_text = REPLACE(temp_text, '&lt;', '<');
    SET temp_text = REPLACE(temp_text, '&gt;', '>');
    SET temp_text = REPLACE(temp_text, '&lbrace;', '{');
    SET temp_text = REPLACE(temp_text, '&rbrace;', '}');
    SET temp_text = REPLACE(temp_text, '&nbsp;', ' ');

    entity_loop: LOOP
        SET loop_counter = loop_counter + 1;
        IF loop_counter > max_loops THEN
            LEAVE entity_loop;
        END IF;

        SET entity_pos = LOCATE('&#', temp_text);
        IF entity_pos = 0 THEN
            LEAVE entity_loop;
        END IF;

        SET entity_end = LOCATE(';', temp_text, entity_pos);
        IF entity_end = 0 THEN
            LEAVE entity_loop;
        END IF;

        SET entity_str = SUBSTRING(temp_text, entity_pos + 2, entity_end - entity_pos - 2);

        IF entity_str REGEXP '^[0-9]+$' THEN
            SET char_code = CAST(entity_str AS UNSIGNED);
        ELSEIF entity_str REGEXP '^[xX][0-9a-fA-F]+$' THEN
            SET char_code = CONV(SUBSTRING(entity_str, 2), 16, 10);
        ELSE
            -- Malformed entity: leave as-is and skip over it
            SET entity_end = entity_end + 1;
            ITERATE entity_loop;
        END IF;

        IF char_code <= 127 THEN
            SET replacement = CHAR(char_code);
            SET temp_text = CONCAT(
                SUBSTRING(temp_text, 1, entity_pos - 1),
                replacement,
                SUBSTRING(temp_text, entity_end + 1)
            );
        ELSE
            SET entity_end = entity_end + 1;
        END IF;
    END LOOP entity_loop;

    RETURN temp_text;
END$$


###
#
# QMORE(input, maxlen)
# inserts a span into `input` after `maxlen` number of characters and returns it.
#
DROP FUNCTION IF EXISTS QMORE;
CREATE FUNCTION QMORE(input TEXT, maxlen INT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER

BEGIN
    DECLARE clean TEXT;
    DECLARE i INT DEFAULT 1;
    DECLARE logical_len INT DEFAULT 0;
    DECLARE cut_len INT DEFAULT 0;
    DECLARE str_len INT;
    DECLARE next_char CHAR(1);
    DECLARE semicolon_pos INT;

    IF input IS NULL OR input = '' THEN
        RETURN input;
    END IF;

    IF maxlen < 1 THEN
        SET maxlen = 1;
    END IF;

    SET clean = strip_tags(input);
    SET str_len = CHAR_LENGTH(clean);

    -- First pass: count logical characters
    SET i = 1;
    WHILE i <= str_len DO
            SET next_char = SUBSTRING(clean, i, 1);

            IF next_char = '&' THEN
                SET semicolon_pos = LOCATE(';', clean, i);
                IF semicolon_pos > i THEN
                    SET i = semicolon_pos + 1;
                ELSE
                    SET i = i + 1;
                END IF;
            ELSE
                SET i = i + 1;
            END IF;

            SET logical_len = logical_len + 1;
        END WHILE;

    -- No cutting needed → return input untouched
    IF logical_len <= maxlen THEN
        RETURN input;
    END IF;

    -- Second pass: determine cut position
    SET i = 1;
    SET logical_len = 0;

    WHILE i <= str_len AND logical_len < maxlen DO
            SET next_char = SUBSTRING(clean, i, 1);

            IF next_char = '&' THEN
                SET semicolon_pos = LOCATE(';', clean, i);
                IF semicolon_pos > i THEN
                    SET i = semicolon_pos + 1;
                ELSE
                    SET i = i + 1;
                END IF;
            ELSE
                SET i = i + 1;
            END IF;

            SET logical_len = logical_len + 1;
        END WHILE;

    SET cut_len = i - 1;

    RETURN CONCAT(
            INSERT(clean, cut_len + 1, 0, '<span class="qfq-more-text">'),
            '</span>'
           );
END$$

###
#
# QBAR(input)
# replaces '|' in `input` with '\|'
#
DROP FUNCTION IF EXISTS QBAR;
CREATE FUNCTION QBAR(input TEXT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = REPLACE(input, '|', '\\|');
    RETURN output;
END$$

###
#
# QCC(input)
# replaces ':' (colon) and ',' (coma)  in `input` with '\:' and '\,'
#
DROP FUNCTION IF EXISTS QCC;
CREATE FUNCTION QCC(input TEXT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = REPLACE(REPLACE(input, ':', '\\:'), ',', '\\,');
    RETURN output;
END$$

###
#
# QNL2BR(input)
# replaces '|' in `input` with '\|'
#
DROP FUNCTION IF EXISTS QNL2BR;
CREATE FUNCTION QNL2BR(input TEXT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = REPLACE(REPLACE(input, CHAR(13), ''), CHAR(10), '<br>');
    RETURN output;
END$$

###
#
# QNBSP(input)
# replaces ' ' in `input` with '&nbsp'
#
DROP FUNCTION IF EXISTS QNBSP;
CREATE FUNCTION QNBSP(input TEXT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = REPLACE(input, ' ', '&nbsp;');
    RETURN output;
END$$

###
#
# QIFEMPTY(input, token)
# If 'input' is empty|0|0000-00-00|0000-00-00 00:00:00, replace by 'token'
#
DROP FUNCTION IF EXISTS QIFEMPTY;
CREATE FUNCTION QIFEMPTY(input TEXT, token TEXT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output =
            IF(ISNULL(input) OR input = '' OR input = '0' OR input = '0000-00-00' OR input = '0000-00-00 00:00:00',
               token,
               input);
    RETURN output;
END$$

###
#
# strip_tags(input) - copied from https://stackoverflow.com/questions/2627940/remove-html-tags-from-record
#
DROP FUNCTION IF EXISTS strip_tags;
CREATE FUNCTION `strip_tags`(str TEXT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE start, end INT DEFAULT 1;

    IF str IS NULL THEN RETURN NULL; END IF;

    LOOP
        SET start = LOCATE("<", str, start);
        IF (!start) THEN RETURN str; END IF;
        SET end = LOCATE(">", str, start);
        IF (!end) THEN SET end = start; END IF;
        SET str = INSERT(str, start, end - start + 1, '');
    END LOOP;
END$$

###
#
# QDATE_FORMAT(timestamp)
#
DROP FUNCTION IF EXISTS QDATE_FORMAT;
CREATE FUNCTION `QDATE_FORMAT`(ts DATETIME)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = IF(ts = 0, '-', DATE_FORMAT(ts, "%d.%m.%Y %H:%i"));
    RETURN output;
END$$

###
#
# QSLUGIFY(string)
#

/*
The MIT License (MIT)
Copyright (c) 2014 jose reis<jose.reis@artbit.pt>
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
Credits:
- http://stackoverflow.com/questions/5409831/mysql-stored-function-to-create-a-slug
*/

DROP FUNCTION IF EXISTS `QSLUGIFY`;
CREATE FUNCTION `QSLUGIFY`(dirty_string varchar(255)) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE x, y , z , k INT;
    DECLARE temp_string, new_string, accents, noAccents VARCHAR(255);
    DECLARE is_allowed BOOL;
    DECLARE c, check_char VARCHAR(1);

    -- IF NULL DO NOT PROCEED
    If dirty_string IS NULL Then
        return dirty_string;
    End If;

    set temp_string = LOWER(dirty_string);

    -- REPLACE GERMAN UMLAUTS (multi-char, must run before generic accent stripping)
    set temp_string = REPLACE(temp_string, 'ä', 'ae');
    set temp_string = REPLACE(temp_string, 'ö', 'oe');
    set temp_string = REPLACE(temp_string, 'ü', 'ue');
    set temp_string = REPLACE(temp_string, 'ß', 'ss');

    -- REPLACE ACCENTS
    -- WITH CAPS
    -- set accents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
    -- set noAccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';
    -- ONLY SMALL CAPS
    set accents = 'šžàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
    set noAccents = 'szaaaaaaaceeeeiiiinoooooouuuuyybf';
    set k = CHAR_LENGTH(accents);

    while k > 0
        do
            set temp_string = REPLACE(temp_string, SUBSTRING(accents, k, 1), SUBSTRING(noAccents, k, 1));
            set k = k - 1;
        end while;

    -- CONVERT & TO EMPTY SPACE
    Set temp_string = REPLACE(temp_string, '&', '');

    -- REPLACE ALL UNWANTED CHARS
    Select temp_string REGEXP ('[^a-z0-9\-]+') into x;
    If x = 1 then
        set z = 1;
        set k = CHAR_LENGTH(temp_string);
        While z <= k
            Do
                Set c = SUBSTRING(temp_string, z, 1);
                Set is_allowed = FALSE;
                If !((ascii(c) = 45) or (ascii(c) >= 48 and ascii(c) <= 57) or
                     (ascii(c) >= 97 and ascii(c) <= 122)) Then
                    Set temp_string = REPLACE(temp_string, c, '-');
                End If;
                set z = z + 1;
            End While;
    End If;

    Select temp_string REGEXP ("^-|-$|'") into x;
    If x = 1 Then
        Set temp_string = Replace(temp_string, "'", '');
        Set z = CHAR_LENGTH(temp_string);
        Set y = CHAR_LENGTH(temp_string);
        Dash_check:
        While z > 1
            Do
                If STRCMP(SUBSTRING(temp_string, -1, 1), '-') = 0 Then
                    Set temp_string = SUBSTRING(temp_string, 1, y - 1);
                    Set y = y - 1;
                Else
                    Leave Dash_check;
                End If;
                Set z = z - 1;
            End While;
    End If;

    Repeat
        Select temp_string REGEXP ("--") into x;
        If x = 1 Then
            Set temp_string = REPLACE(temp_string, "--", "-");
        End If;
    Until x <> 1 End Repeat;

    If LOCATE('-', temp_string) = 1 Then
        Set temp_string = SUBSTRING(temp_string, 2);
    End If;

    Return temp_string;
END$$

###
# QENT_SQUOTE(text)
# Replaces single tick by html entity &apos;
#
DROP FUNCTION IF EXISTS `QENT_SQUOTE`;
CREATE FUNCTION `QENT_SQUOTE`(`input` TEXT) RETURNS text CHARSET utf8
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = REPLACE(input, "'", '&apos;');
    RETURN output;
END$$

###
# QENT_DQUOTE(text)
# Replaces double tick by html entity &quot;
#
DROP FUNCTION IF EXISTS `QENT_DQUOTE`;
CREATE FUNCTION `QENT_DQUOTE`(`input` TEXT) RETURNS text CHARSET utf8
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = REPLACE(input, '"', '&quot;');
    RETURN output;
END$$

###
# QESC_SQUOTE(text)
# Escape single tick by \'. Replace double \ by single \.
#
DROP FUNCTION IF EXISTS `QESC_SQUOTE`;
CREATE FUNCTION `QESC_SQUOTE`(`input` TEXT) RETURNS text CHARSET utf8
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = REPLACE(REPLACE(input, "'", "\\'"), "\\\\'", "\\'");
    RETURN output;
END$$


###
# QESC_DQUOTE(text)
# Escape double tick by \". Replace double \ by single \.
#
DROP FUNCTION IF EXISTS `QESC_DQUOTE`;
CREATE FUNCTION `QESC_DQUOTE`(`input` TEXT) RETURNS text CHARSET utf8
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output = REPLACE(REPLACE(input, '"', '\\"'), '\\\\"', '\\"');
    RETURN output;
END$$


###
#
# QLEFT(input, maxlen)
# Like LEFT, but add '...' if it is truncated.
#
DROP FUNCTION IF EXISTS QLEFT;
CREATE FUNCTION QLEFT(input TEXT, maxlen INT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    IF maxlen < 1 THEN
        SET maxlen = 1;
    END IF;
    IF CHAR_LENGTH(input) > maxlen THEN
        SET output = CONCAT(LEFT(input, maxlen), '...');
    ELSE
        SET output = input;
    END IF;
    RETURN output;
END$$


###
#
# QRIGHT(input, maxlen)
# Like RIGHT, but prepend '...' if it is truncated.
#
DROP FUNCTION IF EXISTS QRIGHT;
CREATE FUNCTION QRIGHT(input TEXT, maxlen INT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    IF maxlen < 1 THEN
        SET maxlen = 1;
    END IF;
    IF CHAR_LENGTH(input) > maxlen THEN
        SET output = CONCAT('...', RIGHT(input, maxlen));
    ELSE
        SET output = input;
    END IF;
    RETURN output;
END$$


###
#
# QMARK(content, searched_string)
# Mark searched string from given content and output 20 chars before and after that string.
#
DROP FUNCTION IF EXISTS QMARK;
CREATE FUNCTION QMARK(content TEXT, searched_string TEXT)
    RETURNS TEXT CHARSET utf8mb4
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    SET @content = strip_tags(content);
    SET @searched_string = searched_string;
    SET @start_pos = INSTR(@content, @searched_string);
    SET @end_pos = @start_pos + LENGTH(@searched_string);
    SET @highlighted_text = '';
    IF @start_pos > 0 THEN
        SET @highlighted_text = CONCAT(
                IF(@start_pos > 20, SUBSTR(@content, @start_pos - 20, 20), SUBSTR(@content, 1, @start_pos - 1)),
                '<strong style="background:yellow;">',
                MID(@content, @start_pos, LENGTH(@searched_string)),
                '</strong>',
                IF(@end_pos + 20 < LENGTH(@content), SUBSTR(@content, @end_pos, 20), SUBSTR(@content, @end_pos)),
                '<br><br>'
                                );
    END IF;
    RETURN @highlighted_text;
END$$

###
#
# QMANR(manr)
# Matrikel-Nr. format: 12-345-567
#
DROP FUNCTION IF EXISTS `QMANR`;
CREATE FUNCTION `QMANR`(manr VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8mb4
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE matrikelNr VARCHAR(255);
    SET matrikelNr = CONCAT(SUBSTRING(manr, 1, 2), '-', SUBSTRING(manr, 3, 3), '-', SUBSTRING(manr, 6, 3));
    RETURN matrikelNr;
END$$

###
#
# QIFPREPEND(separator, input)
# If 'input' is not empty|0, prepend separator to given input
#
DROP FUNCTION IF EXISTS QIFPREPEND;
CREATE FUNCTION QIFPREPEND(`separator` VARCHAR(128), `input` TEXT)
    RETURNS TEXT
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE output TEXT;
    SET output =
            IF(ISNULL(`input`) OR `input` = '' OR `input` = '0',
               '',
               CONCAT(`separator`, `input`));
    RETURN output;
END$$


###
#
# find_match_in_lists(list1, list2)
#
DROP FUNCTION IF EXISTS find_match_in_lists;
CREATE FUNCTION `find_match_in_lists`(list1 VARCHAR(255), list2 VARCHAR(255))
    RETURNS int(11)
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE found_match INT DEFAULT 0;
    DECLARE list1_item VARCHAR(255);
    DECLARE list2_item VARCHAR(255);
    DECLARE list1_length INT DEFAULT 1;
    DECLARE list2_length INT DEFAULT 1;
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;

    SET list1_length = (LENGTH(list1) - LENGTH(REPLACE(list1, ',', ''))) + 1;
    SET list2_length = (LENGTH(list2) - LENGTH(REPLACE(list2, ',', ''))) + 1;

    list1_loop:
    WHILE i <= list1_length
        DO
            SET list1_item = SUBSTRING_INDEX(SUBSTRING_INDEX(list1, ',', i), ',', -1);
            SET j = 1;

            list2_loop:
            WHILE j <= list2_length
                DO
                    SET list2_item = SUBSTRING_INDEX(SUBSTRING_INDEX(list2, ',', j), ',', -1);

                    IF list1_item = list2_item THEN
                        SET found_match = 1;
                        LEAVE list1_loop;
                    END IF;

                    SET j = j + 1;
                END WHILE;
            SET i = i + 1;
        END WHILE;

    RETURN found_match;
END$$


#
# Auto insert merge rules via information_schema
#
DROP PROCEDURE IF EXISTS auto_insert_merge_rules;
CREATE PROCEDURE `auto_insert_merge_rules`(IN `tableSchema` VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE grMergeRuleId INT;
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
          AND TABLE_SCHEMA = tableSchema;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SELECT id INTO grMergeRuleId FROM Grp WHERE reference = 'qfq_merge_rule' LIMIT 1;
    OPEN cur;

    read_loop:
    LOOP
        FETCH cur INTO _tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @columnNames = (SELECT GROUP_CONCAT(COLUMN_NAME)
                            FROM information_schema.COLUMNS
                            WHERE TABLE_NAME = _tableName
                              AND COLUMN_NAME LIKE 'pId%'
                              AND TABLE_SCHEMA = tableSchema);

        IF @columnNames IS NOT NULL THEN
            IF NOT EXISTS (SELECT 1
                           FROM Grp AS grMergeRules,
                                Grp AS grType
                           WHERE grMergeRules.name = _tableName
                             AND grMergeRules.grId = grMergeRuleId) THEN
                INSERT INTO Grp (name, grId, value3, comment, value2, enabled)
                VALUES (_tableName, grMergeRuleId, @columnNames, 'Auto generated', '*', 'yes');
            END IF;
        END IF;

    END LOOP;
    CLOSE cur;
END$$


#
# Create dynamically new columns for sql querys
#
DROP FUNCTION IF EXISTS build_dynamic_columns;
CREATE FUNCTION `build_dynamic_columns`(`columnList` TEXT, `tableName` VARCHAR(128), `dbName` VARCHAR(128),
                                        `outputType` VARCHAR(128), `stringIdVariable` VARCHAR(128))
    RETURNS text CHARSET utf8mb4 COLLATE utf8mb4_general_ci
    DETERMINISTIC

BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE columnName TEXT DEFAULT '';
    DECLARE constructedString TEXT DEFAULT '';
    DECLARE delim TEXT DEFAULT ',';
    DECLARE columnListCursor TEXT DEFAULT columnList;
    DECLARE tableName1 VARCHAR(128) DEFAULT tableName;
    DECLARE tableSchema VARCHAR(128) DEFAULT dbName;
    DECLARE currentPosition INT DEFAULT 1;
    DECLARE nextCommaPosition INT;
    DECLARE conditionValue VARCHAR(128) DEFAULT outputType;
    DECLARE idVariable VARCHAR(128) DEFAULT stringIdVariable;
    DECLARE columnNames TEXT DEFAULT '';
    DECLARE cur CURSOR FOR SELECT COLUMN_NAME
                           FROM information_schema.columns
                           WHERE TABLE_NAME = tableName1 AND TABLE_SCHEMA = tableSchema;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    IF columnListCursor = '*' THEN
        OPEN cur;
        read_loop:
        LOOP
            FETCH cur INTO columnName;
            IF finished THEN
                LEAVE read_loop;
            END IF;
            SET columnNames = CONCAT(columnNames, columnName, ',');
        END LOOP;
        CLOSE cur;
        SET columnListCursor = TRIM(TRAILING ',' FROM columnNames);
        SET finished = 0;
    END IF;

    -- Handling the list without whitespaces
    SET columnListCursor = REPLACE(columnListCursor, ' ', '');

    -- Loop through the comma-separated list
    WHILE NOT finished
        DO
            -- Find the next comma (or end of string if no more commas)
            SET nextCommaPosition = LOCATE(delim, columnListCursor, currentPosition);

            -- Extract the next column name
            IF nextCommaPosition = 0 THEN
                SET columnName = SUBSTRING(columnListCursor, currentPosition);
                SET finished = 1;
            ELSE
                SET columnName = SUBSTRING(columnListCursor, currentPosition, nextCommaPosition - currentPosition);
                SET currentPosition = nextCommaPosition + 1;
            END IF;

            -- Build the output string
            IF LENGTH(columnName) > 0 THEN
                IF LENGTH(conditionValue) > 0 AND conditionValue = 'columnSearch' THEN
                    SET constructedString =
                            CONCAT(constructedString, "IF(", columnName, "='{{", idVariable, "}}', CONCAT('",
                                   columnName, "', ','), ''), ");
                ELSEIF LENGTH(conditionValue) > 0 AND conditionValue = 'setQuery' THEN
                    SET constructedString = CONCAT(constructedString, "`", columnName, "`='{{primaryId:S0}}', ");
                ELSE
                    SET constructedString =
                            CONCAT(constructedString, "'<b>", columnName, "</b>: ', IF(ISNULL(`", columnName,
                                   "`), '', IF(LENGTH(`", columnName, "`) > 100, QMORE(strip_tags(`", columnName,
                                   "`), 100), `", columnName, "`)), '<br>', ");
                END IF;
            END IF;
        END WHILE;

    -- Remove the last comma and space and "<br>"
    IF LENGTH(conditionValue) > 0 AND (conditionValue = 'columnSearch' OR conditionValue = 'setQuery') THEN
        SET constructedString = TRIM(TRAILING ', ' FROM constructedString);
    ELSE
        SET constructedString = TRIM(TRAILING ", '<br>', " FROM constructedString);
    END IF;

    IF LENGTH(conditionValue) > 0 AND conditionValue = 'columnSearch' THEN
        SET constructedString = CONCAT("TRIM(TRAILING ',' FROM CONCAT(", constructedString, "))");
    END IF;

    RETURN constructedString;
END$$
