Delimiter //
/**
 * Author:  armen
 * Created: 23 sept. 2016
 */

Drop Procedure If Exists BUSINESS_DELETE_PROCESSES //
Create Procedure BUSINESS_DELETE_PROCESSES(
    In idProcesses int    
    , In useTransaction TinyInt
)
Begin
    Main: Begin
        Declare transactionStarted int default 0 ;
        DECLARE Exit HANDLER FOR SQLEXCEPTION
        Begin
                if transactionStarted  <> 0 then
                        rollback;
                        set transactionStarted  = 0;
                End if;
                resignal;
        end ;
        Set useTransaction = Coalesce(useTransaction, 0) ;

		Set @idProcesses = idProcesses;
		
		-- vérification si le process est utilisé 
		bsndelrcss_chkInuse: Begin
			Set @psCount = Coalesce((Select Count(*) from ENTITIES where @idProcesses in (ENTITIES_PROCESSES_RELATION, ENTITIES_PROCESSES_RECLAIMS)), 0);
			if(@psCount > 0) Then

				Set @bsndelrcss_txt = 'Could not delete specified process because is used';
				SIGNAL SQLSTATE '99001'
					SET MESSAGE_TEXT = @bsndelrcss_txt
				;
			End if;
		End ;

        -- deleting data
        bsndelrcss_deleteData: Begin 
            
			-- supression des PROCESS
			Delete target
			From
				PROCESS target
				Inner Join PROCESS_TREE T2
					On target.ID_PROCESS = T2.PRCSSTR_ID_PROCESS
				Inner Join PROCESS_LIST T1
					On T2.PRCSSTR_ID_PROCESS_TREE_DESC = T1.PRCSSLST_ID_PROCESS_TREE_DESC
			Where 
				T1.PRCSSLST_ID_PROCESSES = @idProcesses
			;
			-- supression des PROCESS_TREE
			Delete target
			From
				PROCESS_TREE target
				Inner Join PROCESS_LIST T1
					On target.PRCSSTR_ID_PROCESS_TREE_DESC = T1.PRCSSLST_ID_PROCESS_TREE_DESC
			Where 
				T1.PRCSSLST_ID_PROCESSES = @idProcesses
			;
			-- supression PROCESS_TREE_DESC
			Delete target
			From
				PROCESS_TREE_DESC target
				Inner Join PROCESS_LIST T1
					On target.ID_PROCESS_TREE_DESC = T1.PRCSSLST_ID_PROCESS_TREE_DESC
			Where 
				T1.PRCSSLST_ID_PROCESSES = @idProcesses
			;
			-- supression PROCESS_LIST
			Delete target
			From
				PROCESS_LIST target
			Where 
				target.PRCSSLST_ID_PROCESSES = @idProcesses
			;
			-- supression PROCESSES
			Delete target
			From
				PROCESSES target
			Where 
				target.ID_PROCESSES = @idProcesses
			;

        End;
        -- validating all
        if transactionStarted <> 0 Then
            commit ;
        End If;
    End ;


End //

Delimiter ;