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