DELIMITER $$
CREATE DEFINER=`medtool_dev`@`localhost` 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$$
DELIMITER ;
