-- Section 1: Create procedure
DELIMITER //
CREATE PROCEDURE `dwsystem`.`UpdateCharsetAndCollation`(IN DATABASE_NAME VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE column_done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(255);
    DECLARE columnName VARCHAR(255);
    DECLARE columnType VARCHAR(255);
    DECLARE columnCollation VARCHAR(255);

    DECLARE table_cursor CURSOR FOR
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = DATABASE_NAME;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN table_cursor;
    read_loop: LOOP
        FETCH table_cursor INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @table_sql = CONCAT('ALTER TABLE ', DATABASE_NAME, '.', tableName, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8mb3_unicode_ci;');
        
        PREPARE stmt FROM @table_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
    END LOOP read_loop;
    CLOSE table_cursor;
END//
DELIMITER ;

-- Section 2: Update collation for the given database
SET @@FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE dwsystem CHARACTER SET = utf8 COLLATE = 'utf8mb3_unicode_ci';
call UpdateCharsetAndCollation('dwsystem');
SET @@FOREIGN_KEY_CHECKS=1;