use bitnami_redmine;

DROP PROCEDURE IF EXISTS copyCategories;
DROP PROCEDURE IF EXISTS copyCategoriesForAll;

DELIMITER $$
create procedure copyCategories(in sourceProject int, in targetProject int)
/**
This script copy all issue-categories from "sourceProject" to "targetProject".
*/
begin
   insert into issue_categories (name, project_id, assigned_to_id, reminder_notification )
   SELECT name, targetProject, assigned_to_id, reminder_notification from issue_categories where project_id = sourceProject;
end$$
DELIMITER ;

DELIMITER $$
create procedure copyCategoriesForAll(in sourceProject int)
/**
This script copy all issue-categories from "sourceProject" to others projects.
*/
begin
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE id_ INT;
	declare cnt int;
	declare zero int default 0;
	DECLARE projects_cursor CURSOR FOR SELECT id FROM projects;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
	
	open projects_cursor;
	
	projects_cursor: LOOP
		FETCH projects_cursor INTO id_;
		IF finished = 1 THEN 
			LEAVE projects_cursor;
		END IF;
		
		select count(id) 
		into cnt
		from issue_categories 
		where project_id = id_ 
			and name in (select name from issue_categories where project_id = sourceProject);
		
		if (cnt = 0) then	
			
			insert into issue_categories (name, project_id, assigned_to_id, reminder_notification)
			SELECT	name, id_, assigned_to_id, reminder_notification /* , 		project_id, id_, project_id = id_*/
			from	issue_categories
			where	project_id != id_ and project_id = sourceProject;
			
		end if;
		
	END LOOP projects_cursor;
	CLOSE projects_cursor;
	
end$$
DELIMITER ;
/* 
THERE TEST'S

call copyCategoriesForAll(1);
call copyCategories(1,2); 
*/