USE [DevlDUTASJobSchedule]
GO
/****** Object:  StoredProcedure [dbo].[usp_UpdateJobStatus_Generic]    Script Date: 10/4/2025 7:29:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[usp_UpdateJobStatus_Generic]
    @JobName VARCHAR(50),
    @ExitCode INT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Today DATE = CAST(GETDATE() AS DATE);
    DECLARE @ExecutionID INT;

    -- Get the latest execution ID for today
    SELECT TOP 1 @ExecutionID = ExecutionID 
    FROM dbo.JobExecutionHistory 
    WHERE JobName = @JobName AND RunDate = @Today 
    ORDER BY ExecutionID DESC;

    IF @ExitCode = 0 -- Success
    BEGIN
        UPDATE dbo.JobExecutionHistory 
        SET Status = 'Success', ActualEndTime = GETDATE()
        WHERE ExecutionID = @ExecutionID;
        PRINT 'Job completed successfully';
    END
    ELSE -- Failure
    BEGIN
        UPDATE dbo.JobExecutionHistory 
        SET Status = 'Failed', ActualEndTime = GETDATE(), 
            ErrorMessage = 'PowerShell script failed with exit code: ' + CAST(@ExitCode AS VARCHAR)
        WHERE ExecutionID = @ExecutionID;
        
        -- Check if this job has STOP action
        DECLARE @Action VARCHAR(10);
        SELECT @Action = SchedulerAction FROM dbo.JobControl WHERE JobName = @JobName;
        
        IF @Action = 'STOP'
        BEGIN
            PRINT 'Job failed with STOP action - chain will be halted';
        END
        ELSE
        BEGIN
            PRINT 'Job failed with CONTINUE action - chain will continue';
        END
    END
END
