DELIMITER //

CREATE PROCEDURE AlterOrAddDrawDBAndDrawIOColumnTypeInReqAddTables()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tableName VARCHAR(255);
    DECLARE cur CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE 'T_ARMS_REQADD%'
  AND table_name NOT LIKE '%_LOG';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;
OPEN cur;

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

        -- Check if the c_drawdb_contents column exists
        SET @column_exists_db = (
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = tableName
              AND column_name = 'c_drawdb_contents'
        );

        -- Check if the c_drawio_contents column exists
        SET @column_exists_io = (
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = tableName
              AND column_name = 'c_drawio_contents'
        );

        -- Build the dynamic SQL for altering or adding columns
        SET @alter_sql = CONCAT('ALTER TABLE ', tableName);

        -- If c_drawdb_contents exists, modify it; if not, add it
        IF @column_exists_db = 1 THEN
            SET @alter_sql = CONCAT(@alter_sql, ' MODIFY COLUMN c_drawdb_contents LONGTEXT NULL COMMENT ''drawdb json data''');
ELSE
            SET @alter_sql = CONCAT(@alter_sql, ' ADD COLUMN c_drawdb_contents LONGTEXT NULL COMMENT ''drawdb json data''');
END IF;

        -- If both columns exist, add a comma separator; otherwise, add only if needed
        IF @column_exists_db = 1 OR @column_exists_io = 1 THEN
            SET @alter_sql = CONCAT(@alter_sql, ', ');
END IF;

        -- If c_drawio_contents exists, modify it; if not, add it
        IF @column_exists_io = 1 THEN
            SET @alter_sql = CONCAT(@alter_sql, ' MODIFY COLUMN c_drawio_contents LONGTEXT NULL COMMENT ''drawio xml data''');
ELSE
            SET @alter_sql = CONCAT(@alter_sql, ' ADD COLUMN c_drawio_contents LONGTEXT NULL COMMENT ''drawio xml data''');
END IF;

        -- Prepare and execute the dynamic SQL
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;

CLOSE cur;
COMMIT;
END//

DELIMITER ;

-- Call the procedure to execute the column type modifications or additions
CALL AlterOrAddDrawDBAndDrawIOColumnTypeInReqAddTables();
