/**
*
*    Assigns a new due date and/or user to 1 or more tasks.
*
*    @inToDoIds IntList      A integer list of the primary keys of ToDo records that we are updating
*  @inChangedBySysuserID int    the SysUser responsible for the task change
*    @inNewDueDate datetime        A new due date for the tasks, or null to keep them the same
*    @inNewSysuserID int            A new SysUser for the tasks, or null to keep them the same
*  @inNewNote varchar( max )    A note for the task(s)
*/

CREATE procedure [dbo].[UpdateTasks]
    @inToDoIds IntList readonly,
    @inChangedBySysuserID int,
    @inNewDueDate datetime,
    @inNewSysuserID int,
    @inNewNote varchar(max)
as
set nocount on

declare @theTaskID int
-- Temp table that holds all tasks that are not free tasks.
declare @theDependencyDateTasks table
(
    DependencyDateTaskID int identity( 1, 1 ),
    TaskID int,
    ToDoID int,
    OrderId int,
    ProfileID int,
    SecModuleID int,
    SecModuleDesc varchar(30),
    Identifier varchar(64)
)

declare @theTasksThatDependOnTasksWeUpdatedPreviously table
(
    ID int identity( 1, 1 ),
    TaskId int,
    OtherToDoID int,
    NewTaskId int,
    OrderId int,
    ProfileID int,
    SecModuleID int,
    SecModuleDesc varchar(30),
    Identifier varchar(64)
)

declare @theInnerCounter int
declare @theInnerRecordID int
declare @theInnerRecordCount int
declare @theCopyTaskCount int
declare @theUpdatedTaskId int
declare @theUpdatedToDoId int
declare @theUpdatedToDoOrderId int
declare @theUpdatedTaskProfileID int
declare @theUpdatedTaskSecModuleID int
declare @theUpdatedTaskSecModuleDesc varchar(30)
declare @theUpdatedTaskIdentifier varchar(64)


declare @theTempTableID int
declare @theIsFreeTask smallint
declare @theNewTaskID int
declare @theFreedTaskID int
declare @theTempToDoID int
declare @theTempOtherTaskID int
declare @theTempToDoOrderId int
declare @theTempTaskProfileID int
declare @theTempTaskSecModuleID int
declare @theTempTaskSecModuleDesc varchar(30)
declare @theTempTaskIdentifier varchar(64)

-- This is a special Dependency Date type that gets special handling.
declare @theCompletionOfOtherTasksDependencyDateId int
set @theCompletionOfOtherTasksDependencyDateId = ( select DependencyDateID from DependencyDate where [Description] = 'Completion of Other Task' )

-- Temp table that holds all ToDo records that were passed in.
declare @theToDoCounter int = 1
declare @theToDoRecordCount int
declare @theCurrentToDoID int
declare @theToDoIDs table
(
    ID int identity( 1, 1 ),
    ToDoID int
)
insert into @theToDoIDs( ToDoID )
select ToDoID = ToDoParam.Item
from @inToDoIds as ToDoParam

set @theToDoRecordCount = @@rowcount

-- We need to process the passed in tasks one at a time since the user could be
-- updating ToDos that are dependent upon other ToDos in this one operation. If
-- we did updates in bulk, there would be great difficulties in keeping things
-- straight.
while( @theToDoCounter <= @theToDoRecordCount )
begin
    select
        @theCurrentToDoID = ToDoID
    from @theToDoIDs
    where ID = @theToDoCounter

    -- Update the Original Due Date and the new due date and clear out the dependency
    -- date type if a manual due date is specified.  Clear out the CoordinatorTypeID
    -- if a sys user is specified or restore it if not.
    if( @inNewDueDate is not null or @inNewSysuserID is not null )
    begin
        update ToDo set
            OriginalDueDate =
                case
                    when @inNewDueDate is null then ToDo.OriginalDueDate
                    when isnull( ToDo.OriginalDueDate, 0 ) != 0 then ToDo.OriginalDueDate
                    when isnull( Tasks.DependencyDateID, 0 ) = 0 then Tasks.FreeTaskDate
                    else ToDo.DueDate
                end,
            DueDate = isnull( @inNewDueDate, ToDo.DueDate ),
            DependencyDateID =
                case
                    -- This is a "dependency task" not a "dependency date" so don't clear out DependencyDateID in this special case.
                    when @inNewDueDate is null then ToDo.DependencyDateID
                    when ToDo.DependencyDateID = @theCompletionOfOtherTasksDependencyDateId then ToDo.DependencyDateID
                    else null
                end,
            CoordinatorTypeFID =
                case
                    when @inNewSysuserID is null then ToDo.CoordinatorTypeFID
                    else null
                end
        from ToDo
        inner join Tasks on ToDo.TaskID = Tasks.TaskID
        where ToDo.ToDoID = @theCurrentToDoID
    end

    -- update tasks that are free tasks
    update Tasks set
        DependencyDateID =
            case
                -- This is a "dependency task" not a "dependency date" so don't clear out DependencyDateID in this special case.
                when Tasks.DependencyDateID = @theCompletionOfOtherTasksDependencyDateId then Tasks.DependencyDateID
                when @inNewDueDate is null then Tasks.DependencyDateID
                else null
            end,
            -- remove Coordinator type if the task user changed
        CoordinatorTypeID =
            case
                when @inNewSysuserID is null  then Tasks.CoordinatorTypeID
                else null
            end,
        FreeTaskDate = isnull( @inNewDueDate, FreeTaskDate )
    from Tasks
    inner join ToDo on Tasks.TaskID = ToDo.TaskID
    where ToDo.ToDoID = @theCurrentToDoID and
        Tasks.FreeTask = 1

    -- Temp table that holds any Tasks/ToDo items that are not already a free task
    insert into @theDependencyDateTasks
    (
        TaskID,
        ToDoID,
        OrderId,
        ProfileID,
        SecModuleID,
        SecModuleDesc,
        Identifier
    )
    select
        Tasks.TaskID,
        ToDo.ToDoID,
        ToDo.OrderID,
        ToDo.ProfileID,
        SecModules.ModulePriKey,
        SecModules.[Description],
        ToDo.Identifier
    from Tasks
    inner join ToDo on Tasks.TaskID = ToDo.TaskID
    inner join Profiles on ToDo.ProfileID = Profiles.ProfileID
    inner join SecModules on Profiles.SecModulesFID = SecModules.ModulePriKey
    where ToDo.ToDoID = @theCurrentToDoID and
        Tasks.FreeTask = 0

    set @theInnerRecordCount = @@rowcount
    set @theInnerCounter = 1

    while( @theInnerCounter <= @theInnerRecordCount )
    begin
        -- Get first/next IDs for the task being updated.
        select top 1
            @theUpdatedTaskId = TaskID,
            @theUpdatedToDoId = ToDoID,
            @theUpdatedToDoOrderId = OrderId,
            @theInnerRecordID = DependencyDateTaskID,
            @theUpdatedTaskProfileID = ProfileID,
            @theUpdatedTaskSecModuleID = SecModuleID,
            @theUpdatedTaskSecModuleDesc = SecModuleDesc,
            @theUpdatedTaskIdentifier = Identifier
        from @theDependencyDateTasks
        order by DependencyDateTaskID

        -- Note that we are only handling non-free tasks so
        -- insert new Free Task based off the old non-free task
        insert into Tasks
        (
            ProfileID,
            TaskDescription,
            CoordinatorTypeID,
            DependencyDateID,
            DependencyTaskFID,
            DueDays,
            DueType,
            IsActive,
            FreeTask,
            FreeTaskDate,
            SysUserFID
        )
        select
            ProfileID = Tasks.ProfileID,
            TaskDescription = Tasks.TaskDescription,
            CoordinatorTypeID = case
                when @inNewSysuserID is null then Tasks.CoordinatorTypeID
                else null
            end,
            DependencyDateID = case
                -- This is a "dependency task" not a "dependency date" so don't clear out DependencyDateID in this special case.
                when Tasks.DependencyDateID = @theCompletionOfOtherTasksDependencyDateId then Tasks.DependencyDateID
                when @inNewDueDate is null then Tasks.DependencyDateID
                else null
            end,
            DependencyTaskFID = case
                -- This is a "dependency task" not a "dependency date" so don't clear out DependencyDateID in this special case.
                when Tasks.DependencyDateID = @theCompletionOfOtherTasksDependencyDateId then Tasks.DependencyTaskFID
                when @inNewDueDate is null then Tasks.DependencyTaskFID
                else null
            end,
            DueDays = Tasks.DueDays,
            DueType = Tasks.DueType,
            IsActive = 1,  -- Task was active when ToDo was created.
            FreeTask = 1,
            FreeTaskDate = isnull( @inNewDueDate, Tasks.FreeTaskDate ),
            SysUserFID = Tasks.SysUserFID
        from Tasks
        where Tasks.TaskID = @theUpdatedTaskId

        -- If the insert above failed to copy anything, let's not make things worse.
        set @theCopyTaskCount = @@rowcount
        if( @theCopyTaskCount = 1 )
        begin
            set @theTaskID = scope_identity()

            update ToDo set
                TaskID = @theTaskID
            from ToDo
            where ToDo.ToDoID = @theUpdatedToDoId

            -- We'll need to deal with any tasks that reference the task we just "freed" up.  Notice that
            -- these other tasks (if any) could be either non-free tasks or free tasks. It all depends on
            -- if the other tasks were ever previously updated or not.  And this could be a one to many
            -- relationship.
            insert into @theTasksThatDependOnTasksWeUpdatedPreviously
            (
                TaskId,
                OtherToDoID,
                NewTaskId,
                OrderId,
                ProfileID,
                SecModuleID,
                SecModuleDesc,
                Identifier
            )
            select
                TaskId = Tasks.TaskID,
                OtherToDoID = ToDo.ToDoID,
                NewTaskId = @theTaskID,
                OrderId = @theUpdatedToDoOrderId,
                ProfileID = @theUpdatedTaskProfileID,
                SecModuleID = @theUpdatedTaskSecModuleID,
                SecModuleDesc = @theUpdatedTaskSecModuleDesc,
                Identifier = @theUpdatedTaskIdentifier
            from Tasks
            inner join ToDo on
                ToDo.TaskID = Tasks.TaskID and
                ToDo.OrderID = @theUpdatedToDoOrderId
            where ToDo.DependencyTaskFID = @theUpdatedTaskId and
                ToDo.ProfileID = @theUpdatedTaskProfileID and
                ToDo.Identifier = @theUpdatedTaskIdentifier
        end

        -- Delete the inner row we just processed
        delete from @theDependencyDateTasks
        where DependencyDateTaskID = @theInnerRecordID

        -- Update the inner counter.
        set @theInnerCounter = @theInnerCounter + 1
    end

    -- Now, update any tasks that depend upon the one task we just updated, if we freed up that task.  These
    -- could be free tasks or non-free tasks.
    while( exists( select top 1 1 from @theTasksThatDependOnTasksWeUpdatedPreviously ) )
    begin
        -- Get first/next IDs for the task being updated.
        select top 1
            @theTempTableID = TempTasks.ID,
            @theUpdatedTaskId = TempTasks.TaskId,
            @theUpdatedToDoId = TempTasks.OtherToDoID,
            @theNewTaskID = TempTasks.NewTaskId,
            @theTempToDoOrderId = TempTasks.OrderId,
            @theTempOtherTaskID = Tasks.TaskID,
            @theIsFreeTask = Tasks.FreeTask,
            @theTempToDoID = ToDo.ToDoID,
            @theTempTaskProfileID = TempTasks.ProfileID,
            @theTempTaskSecModuleID = TempTasks.SecModuleID,
            @theTempTaskSecModuleDesc = TempTasks.SecModuleDesc,
            @theTempTaskIdentifier = TempTasks.Identifier
        from @theTasksThatDependOnTasksWeUpdatedPreviously as TempTasks
        left outer join Tasks on Tasks.TaskID = TempTasks.TaskID
        left outer join ToDo on ToDo.ToDoID = TempTasks.OtherToDoID
        order by TempTasks.ID

        if( @theTempOtherTaskID is not null and @theTempToDoID is not null)
        begin
            -- We only can do something if both the Task and the ToDo both still exist.
            if( @theIsFreeTask = 1 )
            begin
                --  Cool, this task is already a free task, so just a simple update will suffice.
                update Tasks set
                    DependencyTaskFID = @theNewTaskID
                from Tasks
                where Tasks.TaskID = @theUpdatedTaskId
            end
            else --if( @theIsFreeTask = 0 )
            begin
                -- Now, do a 2-step process to create free tasks for any tasks that depend on the updated task, and update any other ToDo items
                -- on this order which depend on the updated task to be linked with new free tasks that depend on the new free task that was
                -- created above.  We could have any number of tasks in the chain and branching (multiple tasks dependent on one task) to any
                -- depth.
                -- STEP 1: Copy this task that depends on the updated task.
                insert into Tasks
                (
                    ProfileID,
                    TaskDescription,
                    CoordinatorTypeID,
                    DependencyDateID,
                    DueDays,
                    DueType,
                    IsActive,
                    FreeTask,
                    FreeTaskDate,
                    SysUserFID,
                    DependencyTaskFID
                )
                select
                    Tasks.ProfileID,
                    Tasks.TaskDescription,
                    ToDo.CoordinatorTypeFID,
                    ToDo.DependencyDateID,
                    ToDo.DueDays,
                    ToDo.DueType,
                    1,  -- Task was active when ToDo was created.
                    1,
                    Tasks.FreeTaskDate,
                    Tasks.SysUserFID,
                    @theNewTaskID
                from Tasks
                inner join ToDo on
                    ToDo.TaskID = Tasks.TaskID and
                    ToDo.OrderID = @theTempToDoOrderId
                where ToDo.ToDoID = @theUpdatedToDoId and
                    Tasks.TaskID = @theUpdatedTaskId

                -- If the insert above failed to copy anything, let's not make things worse.
                set @theCopyTaskCount = @@rowcount
                if( @theCopyTaskCount = 1 )
                begin
                    set @theFreedTaskID = scope_identity()

                    update ToDo set
                        TaskID = @theFreedTaskID,
                        DependencyTaskFID = @theNewTaskID
                    from ToDo
                    where ToDo.ToDoID = @theUpdatedToDoId

                    -- STEP 2: We'll need to make another pass in case any other tasks reference the task we just "freed" up.
                    -- Notice that these other tasks (if any) could be either non-free tasks or free tasks. It all depends on
                    -- if the other tasks were ever previously updated or not.  And this could be a none to many relationship.
                    insert into @theTasksThatDependOnTasksWeUpdatedPreviously
                    (
                        TaskId,
                        OtherToDoID,
                        NewTaskId,
                        OrderId,
                        ProfileID,
                        SecModuleID,
                        SecModuleDesc,
                        Identifier
                    )
                    select
                        TaskId = Tasks.TaskID,
                        OtherToDoID = ToDo.ToDoID,
                        NewTaskId = @theFreedTaskID,
                        OrderId = @theTempToDoOrderId,
                        ProfileID = @theTempTaskProfileID,
                        SecModuleID = @theTempTaskSecModuleID,
                        SecModuleDesc = @theTempTaskSecModuleDesc,
                        Identifier = @theTempTaskIdentifier
                    from Tasks
                    inner join ToDo on
                        ToDo.TaskID = Tasks.TaskID and
                        ToDo.OrderID = @theTempToDoOrderId
                    where ToDo.DependencyTaskFID = @theUpdatedTaskId and
                        ToDo.ProfileID = @theTempTaskProfileID and
                        ToDo.Identifier = @theTempTaskIdentifier
                end
            end
        end

        -- delete the row we just processed.
        delete from @theTasksThatDependOnTasksWeUpdatedPreviously
        where ID = @theTempTableID
    end

    -- This pass is complete. Loop back and update the next ToDo that was passed in.
    set @theToDoCounter = @theToDoCounter + 1
end

-- This is bulk updating all of the ToDos that were passed in.
update ToDo set
    TaskChangedOnDate = getdate(),
    TaskChangedBySysuserFID = @inChangedBySysuserID,
    OpenUserFlag =
        case
            when @inNewSysuserID is null then OpenUserFlag
            else 1
        end,
    UserID = isnull( @inNewSysuserID, UserID ),
    TaskUserFID = isnull( @inNewSysuserID, TaskUserFID )
from ToDo
inner join @theToDoIDs as theToDoIDs on theToDoIDs.ToDoID = ToDo.ToDoID

--  If a note was provided...
if( @inNewNote is not null )
begin
    -- Update task notes in bulk that already exist for any ToDo that was passed in.
    update ToDoNote set
        Notes = @inNewNote,
        ChangedBySysuserFID = @inChangedBySysuserID,
        ChangedDateTime = getdate()
    from @theToDoIDs as theToDoIDs
    inner join ToDoNote on ToDoNote.ToDoFID = theToDoIDs.ToDoID

    -- Add a ToDoNote record in bulk if one does not exist yet for any ToDo that was passed in.
    insert into ToDoNote
    (
        ToDoFID,
        ChangedBySysuserFID,
        ChangedDateTime,
        Notes
    )
    select
        ToDo.ToDoID,
        @inChangedBySysuserID,
        getdate(),
        @inNewNote
    from @theToDoIDs as theToDoIDs
    inner join ToDo on ToDo.ToDoID = theToDoIDs.ToDoID
    left outer join ToDoNote as ExistingNotes on ExistingNotes.ToDoFID = theToDoIDs.ToDoID
    where
        ExistingNotes.ToDoNoteID is null
end
GO
GRANT EXECUTE ON  [dbo].[UpdateTasks] TO [MssExec]
GO