DELIMITER //
CREATE PROCEDURE sys_transfer_owned2rolemenusecurity(
  IN p_para_menuid VARCHAR(100)
)
/*
  function   : 配合 amis 的 transfer 元件, 將暫存在 owned 欄位的值, 寫到 rolemenusecurity
  description: 目前先配合 omi_api 的存檔後機制(sys_transaction) 執行
  build date : 2021/10/22
  
  modify history
    item who        date       modify docs
    ==== ========== ========== ================================================
    1    michael    2021-10-17 新增
	2    Michael    2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
*/
BEGIN
  /*
    Function: Transfer ownership data from menu table to role menu security
    Build Date: 2021/10/22
  */
  DECLARE v_done INT DEFAULT FALSE;
  DECLARE v_menuid VARCHAR(100);
  DECLARE v_owned TEXT;
  
  DECLARE cur CURSOR FOR 
    SELECT menuid, owned FROM sys_menutable WHERE menuid = p_para_menuid;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
  
  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO v_menuid, v_owned;
    IF v_done THEN
      LEAVE read_loop;
    END IF;
    
    -- Delete existing records
    DELETE FROM sys_rolemenusecurity WHERE menuid = v_menuid;
    
    -- Insert new records from comma-separated list
    SET @sql = CONCAT(
      'INSERT INTO sys_rolemenusecurity (menuid, roleid, controlname) ',
      'SELECT ''', v_menuid, ''', value, ''read'' ',
      'FROM STRING_SPLIT(''', IFNULL(v_owned, ''), ''', '','') ',
      'WHERE value != '''';'
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  
  CLOSE cur;
END //
DELIMITER ;