CREATE PROCEDURE [dbo].[MCC_UpdateTaskForBabyAndOrphanedMum]( @AFollowupNumber INT, @AUserIdNumber INT, @ANewState TINYINT, @ADeviation BIT = 0, @ANote VARCHAR(8000) = '' )  AS
BEGIN

--SET NOCOUNT ON
/*
  Created by Carsten Cremer December 2023 
  Purpose:
   Sometimes task mums are hangining around without babies. This can be due to queries not being executed as they should 
   or because a user has chosen to not set the task mum done when all babies are done. 
   The problem with this is that the room will show up on the todo-list, but without anything to do. 
   
   This function acts as a wrapper function around the standard 'FollowupUpdateState' function and does a clean up round after 'FollowupUpdateState'
   has been executed. It is supposed to locate the taskmum of a given baby (AFollowupnumber), usually just having been set to done, and check if there 
   are siblings of that baby still active - if there are then nothing should be done, if there are not, then the mum should be set to done too.
   The check is not regarding which user id has a sibling or not, just that there are any siblings active at all for any user.
   It is designed to only handle cleaning type tasks (19) and will of course slow down the updating of tasks, but the impact is negligable 
   since this function is used to update individual task and not batches of tasks.

   TaskMums = (FK_FollowupNumber) TaskBabies = (FK_FollowupNumberSub)
*/

    -- First do the standard legacy update
	EXEC FollowupUpdateState  @AFollowupNumber , @AUserIdNumber, @ANewState, @ADeviation, @ANote;


	-- Then go orphan mum hunting

	DECLARE @WorkWithThisNumber INT = 0;  
	DECLARE @MumIsActive BIT = 0;
	DECLARE @MumHasOtherActiveBabies BIT = 0;
	

	
	-- Find the mum belonging to this task
	SET @WorkWithThisNumber = ISNULL(( SELECT TOP(1) FK_FollowupNumber FROM FollowupFollowupXTab WITH (NOLOCK) WHERE FK_FollowupNumberSUB = @AFollowupNumber ), 0); 
    --PRINT '@WorkWithThisNumber STEP 1. Task has mum = number > 0: ' + CAST(@WorkWithThisNumber AS VARCHAR(20)) 
	
	-- I no mum can be found the task itself is a mum
	IF @WorkWithThisNumber = 0 SET @WorkWithThisNumber = @AFollowupNumber 
	--PRINT '@WorkWithThisNumber STEP 2: ' + CAST(@WorkWithThisNumber AS VARCHAR(20)) 
	

	-- Now check if the mum is active. 
	IF (( SELECT TOP(1) FollowUpNumber FROM Followup f WITH (NOLOCK) WHERE f.FollowUpNumber =  @WorkWithThisNumber AND f.Type = 19 AND ISNULL( f.State, 0) IN (0,-1) ) IS NOT NULL ) SET @MumIsActive = 1;
	--PRINT '@MumIsActive: ' + CAST( @MumIsActive AS VARCHAR(20)) 
	IF ( @MumIsActive = 1 )
	BEGIN
  	-- If YES check if she has other active babies.	
	    IF (( SELECT TOP(1) FollowUpNumber FROM Followup f WITH (NOLOCK) 
		    WHERE f.FollowUpNumber IN ( SELECT FK_FollowupNumberSub FROM  FollowupFollowupXTab WITH (NOLOCK) WHERE FK_FollowUpNumber = @WorkWithThisNumber ) 
			AND f.Type = 19 
			AND ISNULL( f.State, 0) IN (0,-1) ) IS NOT NULL ) SET @MumHasOtherActiveBabies = 1; 
	   
	    --PRINT '@MumHasOtherActiveBabies: ' + CAST( @MumHasOtherActiveBabies AS VARCHAR(20)) 
	    IF (@MumHasOtherActiveBabies = 0 )-- NO babies active, then mum should not be active either.
		BEGIN
		  EXEC FollowupUpdateState @WorkWithThisNumber, @AUserIdNumber, @ANewState = 3;
		END
    END
  	-- If NOT active mum go back to sleep. 

END;

	




