DELIMITER $$
CREATE DEFINER=`access`@`localhost` PROCEDURE `ConcatActiveTours`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE str_concat TEXT DEFAULT '';
    DECLARE tour_id INT;
    DECLARE tour_name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT id, name FROM tbl_tour;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tour_id, tour_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET str_concat = CONCAT(str_concat, ' ', tour_id, ' => ', tour_name, ', ');
    END LOOP;

    CLOSE cur;
    SELECT str_concat;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`access`@`localhost` PROCEDURE `handle_object_case`(    
    IN new_data JSON,
    IN old_data JSON,
    IN updated_by VARCHAR(255),
    OUT obj JSON
)
BEGIN
    DECLARE old_value JSON;
    DECLARE new_value JSON;
    DECLARE obj_keys JSON;
    DECLARE obj_keys_count INT;
    DECLARE idx INT DEFAULT 0;

    SET obj = JSON_OBJECT();

    SET obj_keys = JSON_KEYS(new_data);
    SET obj_keys_count = JSON_LENGTH(obj_keys);

    WHILE idx < obj_keys_count DO
        SET old_value = JSON_EXTRACT(old_data, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(obj_keys, CONCAT('$[', idx, ']')))));
        SET new_value = JSON_EXTRACT(new_data, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(obj_keys, CONCAT('$[', idx, ']')))));

        IF (COALESCE(old_value, '+') != COALESCE(new_value, '-')) THEN
            SET obj = JSON_MERGE_PATCH(
                obj,
                JSON_OBJECT(
                    JSON_UNQUOTE(JSON_EXTRACT(obj_keys, CONCAT('$[', idx, ']'))),
                    JSON_OBJECT(
                        'new_value', COALESCE(new_value, ''),
                        'old_value', COALESCE(old_value, ''),
                        'updated_by', COALESCE(updated_by, 'unknown'),
                        'updated_at', NOW()
                    )
                )
            );
        END IF;

        SET idx = idx + 1;
    END WHILE;
END$$
DELIMITER ;
