USE [DevlDUTASJobSchedule]
GO
/****** Object:  StoredProcedure [dbo].[usp_CheckJobConditions_Generic]    Script Date: 10/4/2025 7:25:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object: StoredProcedure [dbo].[usp_CheckJobConditions_Generic] ******/
CREATE OR ALTER PROCEDURE [dbo].[usp_CheckJobConditions_Generic]
    @JobName VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CanRun BIT, @Message VARCHAR(200);
    DECLARE @ScheduledStartTime TIME;

    -- Call main condition check
    EXEC dbo.usp_CheckJobConditions @JobName, @CanRun OUTPUT, @Message OUTPUT;

    -- Get scheduled start time for logging
    SELECT @ScheduledStartTime = ScheduledStartTime
    FROM dbo.JobControl
    WHERE JobName = @JobName;

    IF @CanRun = 0
    BEGIN
        PRINT @Message;

        -- Insert Failed row if it doesn't already exist
        IF NOT EXISTS (
            SELECT 1 FROM dbo.JobExecutionHistory
            WHERE JobName = @JobName
              AND RunDate = CAST(GETDATE() AS DATE)
        )
        BEGIN
            INSERT INTO dbo.JobExecutionHistory (JobName, RunDate, ScheduledStartTime, Status, ErrorMessage)
            VALUES (@JobName, CAST(GETDATE() AS DATE), @ScheduledStartTime, 'Failed', @Message);
        END
        ELSE
        BEGIN
            -- Update existing row with failure info
            UPDATE dbo.JobExecutionHistory
            SET Status = 'Failed',
                ErrorMessage = @Message
            WHERE JobName = @JobName
              AND RunDate = CAST(GETDATE() AS DATE);
        END

        RAISERROR(@Message, 16, 1);
    END
    ELSE
    BEGIN
        PRINT 'All conditions met - starting job...';

        -- Insert Running row if it doesn't already exist
        IF NOT EXISTS (
            SELECT 1 FROM dbo.JobExecutionHistory
            WHERE JobName = @JobName
              AND RunDate = CAST(GETDATE() AS DATE)
        )
        BEGIN
            INSERT INTO dbo.JobExecutionHistory (JobName, RunDate, ScheduledStartTime, Status, ActualStartTime)
            VALUES (@JobName, CAST(GETDATE() AS DATE), @ScheduledStartTime, 'Running', GETDATE());
        END
        ELSE
        BEGIN
            -- Update existing row to Running
            UPDATE dbo.JobExecutionHistory
            SET Status = 'Running',
                ActualStartTime = GETDATE(),
                ErrorMessage = NULL
            WHERE JobName = @JobName
              AND RunDate = CAST(GETDATE() AS DATE);
        END
    END
END
