-- -------------------------
--  How to Run the script --
-- -------------------------
-- (1) Copy the script to relevant db host
-- (2) Execute the following command

-- 	>> mysql -u[username] -p[password] [appfac-runtime-db-name] < migration-script.sql

-- This script will create a new stored procedure in your db, execute it and update the data.
-- (3) please drop the 4 stored procedures created "debug_msg", "Migrate_Dev_Runtime_Data", "Migrate_Test_Runtime_Data" and "Migrate_Prod_Runtime_Data" after your work.


-- stored procedure for migrating data from dev stratos registry to afdb
DELIMITER //

-- procedure for logging
DROP PROCEDURE IF EXISTS `debug_msg` //
CREATE PROCEDURE `debug_msg`(enabled int, msg VARCHAR(255))
BEGIN
    IF enabled THEN BEGIN
    	select concat("** ", msg) AS '   ';
    END; END IF;
END //


-- procedure for update database records
DROP PROCEDURE IF EXISTS `Migrate_Dev_Runtime_Data` //
CREATE PROCEDURE `Migrate_Dev_Runtime_Data`()
BEGIN

	-- declare variables begin
	declare log_enabled int default 1; 
	declare main_done int default 0; 
	declare tenant_id int(11) default 0;
	declare tenant_count int(11) default 0;

	-- (1) declare main cursor for tenant list (get tenant id list from afdb)
	declare tenant_cur cursor for select distinct afdb.AF_APPLICATION.TENANT_ID from afdb.AF_APPLICATION;
	
	declare continue handler for not found set main_done = 1;

	-- declare the exception handler
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
	    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
	    ROLLBACK;
	END;
	
	-- get tenant count
	call debug_msg(log_enabled, "    ");
	call debug_msg(log_enabled, "**** Begins Data Migration From Stratos Dev Registry ****");
	select count(afdb.AF_APPLICATION.TENANT_ID) into tenant_count from afdb.AF_APPLICATION; 
	call debug_msg(log_enabled, "=====================================");
	call debug_msg(log_enabled, concat("Tenant Count : ", tenant_count));
	call debug_msg(log_enabled, "=====================================");

	-- open main cursor
	open tenant_cur;
	main_loop:LOOP
		
		-- termiate the loop
		fetch tenant_cur into tenant_id;
		IF main_done = 1 THEN
		      LEAVE main_loop;
		END IF;

		-- start the transaction
		START TRANSACTION;
			BLOCK1: BEGIN	
				-- decalare variables
				declare update_done int default 0;
				declare application_key varchar(100);
				declare application_id varchar(100);
				declare registry_path varchar(100);
				declare res_tenant_id int(11) default 0;
				declare resource_type varchar(100);
				declare resource_name varchar(100);
				declare enviornment varchar(100);
				declare description varchar(100);
				declare api_resource_name varchar(100);
				declare appkey_plus_resname varchar(100);
		
				-- (2) fetch resource data from devregistry db
				declare res_cur cursor for select 
					path.REG_PATH_VALUE as REG_PATH,
					res.REG_TENANT_ID as TENANT_ID, 
					res.REG_MEDIA_TYPE as RESOURCE_TYPE, 
					res.REG_NAME as RESOURCE_NAME, 
					'Development' as ENVIRONMENT,
					res.REG_DESCRIPTION as DESCRIPTION	
				from devregistry.REG_RESOURCE res inner join devregistry.REG_PATH path on path.REG_PATH_ID=res.REG_PATH_ID 
				where 
					((res.REG_MEDIA_TYPE='Registry' and (res.REG_NAME!='url' and res.REG_NAME!='authentication')) or res.REG_MEDIA_TYPE='External API')
				and res.REG_TENANT_ID=tenant_id;

				-- continue handler for data not found error
				declare continue handler for not found set update_done = 1;
				
				call debug_msg(log_enabled, concat("Begin Transaction for Tenant ID : ", tenant_id));
				call debug_msg(log_enabled, "--------------------------------------");

				-- open the resource cursor
				open res_cur;
				
					resource_loop:LOOP

						fetch res_cur into registry_path, res_tenant_id, resource_type, resource_name, enviornment, description;
						
						IF update_done = 1 THEN
						      LEAVE resource_loop;
						END IF;

						-- (3) generate resource name for external api
						IF resource_type = 'External API' THEN 
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -1) into resource_name;
							END;
						END IF;
			
						-- (4) make description empty for null values
						IF description is null THEN 
							BEGIN
								select "" into description;
							END;
						END IF;

						-- (5) generate application key from reg path
						IF resource_type = 'Registry' THEN 
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -1) into application_key;
							END;
						ELSEIF resource_type = 'External API' THEN
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -2) into appkey_plus_resname;
								select SUBSTRING_INDEX(appkey_plus_resname, '/', 1) into application_key;
							END;	
						END IF;
						-- (6) set relevant application id				
						select distinct app.id from afdb.AF_APPLICATION app where app.APPLICATION_KEY=application_key into application_id;

						-- (7) update resource table block
						BLOCK2: BEGIN
							call debug_msg(log_enabled, concat("Tenant id : ", res_tenant_id, "/ Resource Name : ", resource_name, " / Resource Type : ", resource_type, " / Env : ", enviornment));
	
							-- add new data to afdb
							insert into afdb.AF_RESOURCE (APPLICATION_ID, RESOURCE_TYPE, RESOURCE_NAME, ENVIRONMENT, DESCRIPTION, TENANT_ID)
								values (application_id, resource_type, resource_name, enviornment, description, res_tenant_id);
	
						END BLOCK2;

					END LOOP;
				close res_cur;
			END BLOCK1;
		COMMIT;
		
	-- end the transaction
	END LOOP;

	close tenant_cur;
END //


-- stored procedure for migrating data from test stratos registry to afdb
DROP PROCEDURE IF EXISTS `Migrate_Test_Runtime_Data` //
CREATE PROCEDURE `Migrate_Test_Runtime_Data`()
BEGIN

	-- declare variables begin
	declare log_enabled int default 1; 
	declare main_done int default 0; 
	declare tenant_id int(11) default 0;
	declare tenant_count int(11) default 0;

	-- (1) declare main cursor for tenant list (get tenant id list from afdb)
	declare tenant_cur cursor for select distinct afdb.AF_APPLICATION.TENANT_ID from afdb.AF_APPLICATION;
	
	declare continue handler for not found set main_done = 1;

	-- declare the exception handler
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
	    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
	    ROLLBACK;
	END;
	
	-- get tenant count
	call debug_msg(log_enabled, "    ");
	call debug_msg(log_enabled, "**** Begins Data Migration From Stratos Test Registry ****");
	select count(afdb.AF_APPLICATION.TENANT_ID) into tenant_count from afdb.AF_APPLICATION; 
	call debug_msg(log_enabled, "=====================================");
	call debug_msg(log_enabled, concat("Tenant Count : ", tenant_count));
	call debug_msg(log_enabled, "=====================================");

	-- open main cursor
	open tenant_cur;
	main_loop:LOOP
		
		-- termiate the loop
		fetch tenant_cur into tenant_id;
		IF main_done = 1 THEN
		      LEAVE main_loop;
		END IF;

		-- start the transaction
		START TRANSACTION;
			BLOCK1: BEGIN	
				-- decalare variables
				declare update_done int default 0;
				declare application_key varchar(100);
				declare application_id varchar(100);
				declare registry_path varchar(100);
				declare res_tenant_id int(11) default 0;
				declare resource_type varchar(100);
				declare resource_name varchar(100);
				declare enviornment varchar(100);
				declare description varchar(100);
				declare api_resource_name varchar(100);
				declare appkey_plus_resname varchar(100);
		
				-- (2) fetch resource data from devregistry db
				declare res_cur cursor for select 
					path.REG_PATH_VALUE as REG_PATH,
					res.REG_TENANT_ID as TENANT_ID, 
					res.REG_MEDIA_TYPE as RESOURCE_TYPE, 
					res.REG_NAME as RESOURCE_NAME, 
					'Testing' as ENVIRONMENT,
					res.REG_DESCRIPTION as DESCRIPTION	
				from testregistry.REG_RESOURCE res inner join testregistry.REG_PATH path on path.REG_PATH_ID=res.REG_PATH_ID 
				where 
					((res.REG_MEDIA_TYPE='Registry' and (res.REG_NAME!='url' and res.REG_NAME!='authentication')) or res.REG_MEDIA_TYPE='External API')
				and res.REG_TENANT_ID=tenant_id;

				-- continue handler for data not found error
				declare continue handler for not found set update_done = 1;
				
				call debug_msg(log_enabled, concat("Begin Transaction for Tenant ID : ", tenant_id));
				call debug_msg(log_enabled, "--------------------------------------");

				-- open the resource cursor
				open res_cur;
				
					resource_loop:LOOP

						fetch res_cur into registry_path, res_tenant_id, resource_type, resource_name, enviornment, description;
						
						IF update_done = 1 THEN
						      LEAVE resource_loop;
						END IF;

						-- (3) generate resource name for external api
						IF resource_type = 'External API' THEN 
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -1) into resource_name;
							END;
						END IF;
			
						-- (4) make description empty for null values
						IF description is null THEN 
							BEGIN
								select "" into description;
							END;
						END IF;

						-- (5) generate application key from reg path
						IF resource_type = 'Registry' THEN 
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -1) into application_key;
							END;
						ELSEIF resource_type = 'External API' THEN
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -2) into appkey_plus_resname;
								select SUBSTRING_INDEX(appkey_plus_resname, '/', 1) into application_key;
							END;	
						END IF;
						-- (6) set relevant application id				
						select distinct app.id from afdb.AF_APPLICATION app where app.APPLICATION_KEY=application_key into application_id;

						-- (7) update resource table block
						BLOCK2: BEGIN
							call debug_msg(log_enabled, concat("Tenant id : ", res_tenant_id, "/ Resource Name : ", resource_name, " / Resource Type : ", resource_type, " / Env : ", enviornment));
	
							-- add new data to afdb
							insert into afdb.AF_RESOURCE (APPLICATION_ID, RESOURCE_TYPE, RESOURCE_NAME, ENVIRONMENT, DESCRIPTION, TENANT_ID)
								values (application_id, resource_type, resource_name, enviornment, description, res_tenant_id);
	
						END BLOCK2;

					END LOOP;
				close res_cur;
			END BLOCK1;
		COMMIT;
		
	-- end the transaction
	END LOOP;

	close tenant_cur;
END //


-- stored procedure for migrating data from prod stratos registry to afdb
DROP PROCEDURE IF EXISTS `Migrate_Prod_Runtime_Data` //
CREATE PROCEDURE `Migrate_Prod_Runtime_Data`()
BEGIN

	-- declare variables begin
	declare log_enabled int default 1; 
	declare main_done int default 0; 
	declare tenant_id int(11) default 0;
	declare tenant_count int(11) default 0;

	-- (1) declare main cursor for tenant list (get tenant id list from afdb)
	declare tenant_cur cursor for select distinct afdb.AF_APPLICATION.TENANT_ID from afdb.AF_APPLICATION;
	
	declare continue handler for not found set main_done = 1;

	-- declare the exception handler
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
	    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
	    ROLLBACK;
	END;
	
	-- get tenant count
	call debug_msg(log_enabled, "    ");
	call debug_msg(log_enabled, "**** Begins Data Migration From Stratos Prod Registry ****");
	select count(afdb.AF_APPLICATION.TENANT_ID) into tenant_count from afdb.AF_APPLICATION; 
	call debug_msg(log_enabled, "=====================================");
	call debug_msg(log_enabled, concat("Tenant Count : ", tenant_count));
	call debug_msg(log_enabled, "=====================================");

	-- open main cursor
	open tenant_cur;
	main_loop:LOOP
		
		-- termiate the loop
		fetch tenant_cur into tenant_id;
		IF main_done = 1 THEN
		      LEAVE main_loop;
		END IF;

		-- start the transaction
		START TRANSACTION;
			BLOCK1: BEGIN	
				-- decalare variables
				declare update_done int default 0;
				declare application_key varchar(100);
				declare application_id varchar(100);
				declare registry_path varchar(100);
				declare res_tenant_id int(11) default 0;
				declare resource_type varchar(100);
				declare resource_name varchar(100);
				declare enviornment varchar(100);
				declare description varchar(100);
				declare api_resource_name varchar(100);
				declare appkey_plus_resname varchar(100);
		
				-- (2) fetch resource data from devregistry db
				declare res_cur cursor for select 
					path.REG_PATH_VALUE as REG_PATH,
					res.REG_TENANT_ID as TENANT_ID, 
					res.REG_MEDIA_TYPE as RESOURCE_TYPE, 
					res.REG_NAME as RESOURCE_NAME, 
					'Production' as ENVIRONMENT,
					res.REG_DESCRIPTION as DESCRIPTION	
				from prodregistry.REG_RESOURCE res inner join prodregistry.REG_PATH path on path.REG_PATH_ID=res.REG_PATH_ID 
				where 
					((res.REG_MEDIA_TYPE='Registry' and (res.REG_NAME!='url' and res.REG_NAME!='authentication')) or res.REG_MEDIA_TYPE='External API')
				and res.REG_TENANT_ID=tenant_id;

				-- continue handler for data not found error
				declare continue handler for not found set update_done = 1;
				
				call debug_msg(log_enabled, concat("Begin Transaction for Tenant ID : ", tenant_id));
				call debug_msg(log_enabled, "--------------------------------------");

				-- open the resource cursor
				open res_cur;
				
					resource_loop:LOOP

						fetch res_cur into registry_path, res_tenant_id, resource_type, resource_name, enviornment, description;
						
						IF update_done = 1 THEN
						      LEAVE resource_loop;
						END IF;

						-- (3) generate resource name for external api
						IF resource_type = 'External API' THEN 
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -1) into resource_name;
							END;
						END IF;
			
						-- (4) make description empty for null values
						IF description is null THEN 
							BEGIN
								select "" into description;
							END;
						END IF;

						-- (5) generate application key from reg path
						IF resource_type = 'Registry' THEN 
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -1) into application_key;
							END;
						ELSEIF resource_type = 'External API' THEN
							BEGIN
								select SUBSTRING_INDEX(registry_path, '/', -2) into appkey_plus_resname;
								select SUBSTRING_INDEX(appkey_plus_resname, '/', 1) into application_key;
							END;	
						END IF;
						-- (6) set relevant application id				
						select distinct app.id from afdb.AF_APPLICATION app where app.APPLICATION_KEY=application_key into application_id;

						-- (7) update resource table block
						BLOCK2: BEGIN
							call debug_msg(log_enabled, concat("Tenant id : ", res_tenant_id, "/ Resource Name : ", resource_name, " / Resource Type : ", resource_type, " / Env : ", enviornment));
	
							-- add new data to afdb
							insert into afdb.AF_RESOURCE (APPLICATION_ID, RESOURCE_TYPE, RESOURCE_NAME, ENVIRONMENT, DESCRIPTION, TENANT_ID)
								values (application_id, resource_type, resource_name, enviornment, description, res_tenant_id);
	
						END BLOCK2;

					END LOOP;
				close res_cur;
			END BLOCK1;
		COMMIT;
		
	-- end the transaction
	END LOOP;

	close tenant_cur;
END //
DELIMITER ;

-- call the stored procedure (needs to migrate data from 3 envionrment serperately to afdb)
call Migrate_Dev_Runtime_Data();
call Migrate_Test_Runtime_Data();
call Migrate_Prod_Runtime_Data();






