DELIMITER //
DROP PROCEDURE IF EXISTS `Migrate_Data`//

CREATE PROCEDURE `Migrate_Data`()
BEGIN
	declare done int(11) default 0;
	declare res_id int(11) default 0;
	declare res_application_id int(11);
	declare res_resource_type varchar(100);
	declare res_resource_name varchar(100);
	declare res_env varchar(100);
	declare record_count int(10) default 0;

	-- declare tenant id
	declare tenant_id int(11) default 0; 

	-- declare rss username
	declare rss_username varchar(16); 
	
	-- declare main cursor
	declare resource_cur cursor for select afdb.AF_RESOURCE.* from afdb.AF_RESOURCE where afdb.AF_RESOURCE.RESOURCE_TYPE='DATABASE_USER';

	-- error handler
	declare exit handler for not found set done=1;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET done = 1;
	open resource_cur;

	-- main loop
	update_resource: LOOP
		fetch resource_cur into res_id, res_application_id, res_resource_type, res_resource_name, res_env;

		-- get relavant tenant id
		select distinct app.TENANT_ID into tenant_id 
			from afdb.AF_RESOURCE res inner join afdb.AF_APPLICATION app 
			on app.ID = res.APPLICATION_ID	
			where res.ID=res_id;
		
		-- DEBUG : log tenant id
		select concat(" Tenant Id : ", tenant_id) AS '';

		-- get relevant username with postfix		
		select distinct dbuser.USERNAME into rss_username
			from afdb.AF_APPLICATION app1
			inner join rssmgt.RM_DATABASE_USER dbuser 
			on app1.TENANT_ID=dbuser.TENANT_ID
			and dbuser.TENANT_ID=tenant_id
			and dbuser.USERNAME like CONCAT(res_resource_name, '\_%') COLLATE latin1_general_cs;

		-- update AF_RESOURCE table enties
		update afdb.AF_RESOURCE	set RESOURCE_NAME=rss_username
			where afdb.AF_RESOURCE.ID=res_id;	

		-- DEBUG : log Record Count ::: Old Name : New Name
		set record_count = record_count + 1;
		select concat("Record No : ", record_count, ":::" , res_resource_name, " : ", rss_username) AS '';
		
		-- end of one record processing
	end LOOP update_resource;
	close resource_cur;

END //
DELIMITER ;

-- call the stored procedure
call Migrate_Data();


