-- -----------------------------------------------------------------------------
-- 1) CONFIGURATION TABLE
--    Stores the definition of every job. ScheduleType drives which of the other
--    scheduling columns/tables are actually used for that row.
-- -----------------------------------------------------------------------------
CREATE TABLE dbo.scheduler_config(
    IdJob               INT IDENTITY(1,1) PRIMARY KEY,
    JobName             NVARCHAR(100)     NOT NULL,                -- Human-friendly name, not used by the engine
    ProcedureSchema     SYSNAME           NOT NULL DEFAULT 'dbo',  -- Schema of the target procedure
    ProcedureName       SYSNAME           NOT NULL,                -- Name of the stored procedure to execute
    Parameters          NVARCHAR(MAX)     NULL,                    -- Optional literal parameter string, e.g. '@Param1=1,@Param2=''ABC'''
    ScheduleType        VARCHAR(15)       NOT NULL,                -- INTERVAL   -> runs every FrequencyMinutes minutes
                                                                   -- DAILY_TIME -> runs at specific times of day, listed in scheduler_config_times
                                                                   -- ONE_TIME   -> runs once at SpecificDateTime, then never again
    FrequencyMinutes    INT               NULL,                    -- required only when ScheduleType = INTERVAL
    SpecificDateTime    DATETIME          NULL,                    -- required only when ScheduleType = ONE_TIME
    StartTime           TIME              NULL,                    -- optional time-of-day window, applies to INTERVAL only
    EndTime             TIME              NULL,                    --        
    WeekDays            VARCHAR(20)       NULL,                    -- optional allowed weekdays (1=Monday..7=Sunday), applies to INTERVAL and DAILY_TIME
    IsActive            BIT               NOT NULL DEFAULT 1,      -- enable/disable without deleting the row
    IsRunning           BIT               NOT NULL DEFAULT 0,      -- prevents overlapping executions of the same job
    LastRunDate         DATETIME          NULL,                    -- timestamp of the last time this job started
      
    CONSTRAINT sK_Schedu_cerConfig_ScheduleType
        CHECK (ScheduleType IN ('INTERVAL','DAILY_TIME','ONE_TIME'))
    );
GO
 
-- -----------------------------------------------------------------------------------
-- 2) FIXED DAILY RUN TIMES
--    Holds one or more fixed times of day for jobs whose ScheduleType = 'DAILY_TIME'.
--    Example: a job can run at both 08:00 and 18:00 by inserting two rows here.
-- -----------------------------------------------------------------------------------
CREATE TABLE dbo.scheduler_config_times (
    ID             INT IDENTITY(1,1) PRIMARY KEY,
    ConfigID       INT               NOT NULL REFERENCES dbo.scheduler_config(ID),
    RunTime        TIME              NOT NULL,        -- e.g. '08:00:00'
    LastRunDate    DATE              NULL             -- last calendar date this specific slot fired; prevents re-firing within the same day
    );
GO
 
-- ----------------------------------------------------------------------------
-- 3) EXECUTION LOG
--    Keeps an execution history for every run of every job, including duration
--    and any error raised.
-- ----------------------------------------------------------------------------
CREATE TABLE dbo.scheduler_log (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    ConfigID        INT               NOT NULL,   -- FK back to scheduler_config.ID
    ProcedureName   SYSNAME           NOT NULL,   -- denormalized for quick reading without a join
    StartDate       DATETIME          NOT NULL,
    EndDate         DATETIME          NULL,
    Outcome         VARCHAR(20)       NULL,       -- 'RUNNING' / 'OK' / 'ERROR'
    ErrorMessage    NVARCHAR(MAX)     NULL        -- populated only when Outcome = 'ERROR'
    );
GO
 
-- -----------------------------------------------------------------------------
-- 4) SUPPORTING INDEXES
--    The dispatcher filters on ScheduleType / IsActive / IsRunning on every run
--    (every minute), so these columns need a covering index to avoid a table
--    scan as scheduler_config grows.
-- -----------------------------------------------------------------------------
CREATE NONCLUSTERED INDEX sX_Schedu_cerConfig_Dispatch
ON dbo.scheduler_config (ScheduleType, IsActive, IsRunning)
INCLUDE (ProcedureSchema, ProcedureName, Parameters, FrequencyMinutes, LastRunDate, StartTime, EndTime, WeekDays, SpecificDateTime);
GO
  
CREATE NONCLUSTERED INDEX sX_Schedu_cerConfigTimes_ConfigID
ON dbo.scheduler_config_times (ConfigID)
INCLUDE (RunTime, LastRunDate);
GO

-- ----------------------------------------------------------------------------
-- DISPATCHER PROCEDURE
-- Main orchestrator, meant to be called every minute by a single SQL Server
-- Agent Job. Handles INTERVAL, DAILY_TIME and ONE_TIME scheduling.
-- No CURSOR object is used: due jobs are collected in a table variable and
-- walked with a WHILE loop, which is required only because each dynamic
-- EXEC needs its own isolated TRY/CATCH so one failing job never blocks
-- the others. All status/log updates that don't need per-row isolation
-- are done as set-based statements instead.
-- --------------------------------------------------------------------------
-- SAMPLE CONFIGURATION ROWS
-- -------------------------------------------------------------------------- */
-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType, FrequencyMinutes, StartTime, EndTime, WeekDays)
-- VALUES ('Import CSV X3', 'sp_ImportCsvX3', 'INTERVAL', 15, '06:00', '22:00', '1,2,3,4,5');
 
-- DECLARE @NewConfigID INT;
-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType)
-- VALUES ('Daily Cost Recalc', 'sp_RecalcCosts', 'DAILY_TIME');
-- SET @NewConfigID = SCOPE_IDENTITY();
-- INSERT INTO dbo.scheduler_config_times (ConfigID, RunTime)
-- VALUES (@NewConfigID, '08:00'), (@NewConfigID, '18:00');
 
-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType, SpecificDateTime)
-- VALUES ('Year-End Fix', 'sp_YearEndFix', 'ONE_TIME', '2026-12-31 23:55:00');
-- ----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE dbo.sp_scheduler_dispatcher
AS
BEGIN
    SET NOCOUNT ON;
 
    DECLARE @Now DATETIME            = GETDATE();
    DECLARE @Today DATE              = CAST(@Now AS DATE);
    DECLARE @NowTime TIME            = CAST(@Now AS TIME);
    DECLARE @TodayWeekDay VARCHAR(1) = CAST(DATEPART(WEEKDAY, @Now) AS VARCHAR(1));
 
    -- Working table with a row number, used to walk through the due jobs one
    -- at a time without allocating a CURSOR object. LogID is filled later via
    -- an OUTPUT clause, avoiding a lookup query inside the loop.
    DECLARE @DueJobs TABLE (
        RowNum           INT IDENTITY(1,1) PRIMARY KEY,
        ConfigID         INT,
        ProcedureSchema  SYSNAME,
        ProcedureName    SYSNAME,
        Parameters       NVARCHAR(MAX),
        TimeSlotID       INT NULL,
        LogID            INT NULL
    );
 
    -- 1) INTERVAL jobs: due when enough minutes have passed since LastRunDate,
    --    and (if set) we're inside the allowed time window and weekday list.
    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
        SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL
        FROM dbo.scheduler_config
        WHERE IsActive = 1
            AND IsRunning = 0
            AND ScheduleType = 'INTERVAL'
            AND (LastRunDate IS NULL OR DATEDIFF(MINUTE, LastRunDate, @Now) >= FrequencyMinutes)
            AND (StartTime IS NULL OR @NowTime >= StartTime)
            AND (EndTime IS NULL OR @NowTime <= EndTime)
            AND (WeekDays IS NULL OR WeekDays LIKE '%' + @TodayWeekDay + '%');
 
    -- 2) DAILY_TIME jobs: due when current time has reached a configured
    --    RunTime slot and that slot hasn't already fired today.
    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
        SELECT c.ID, c.ProcedureSchema, c.ProcedureName, c.Parameters, t.ID
        FROM dbo.scheduler_config c
        INNER JOIN dbo.scheduler_config_times t ON t.ConfigID = c.ID
        WHERE c.IsActive = 1
            AND c.IsRunning = 0
            AND c.ScheduleType = 'DAILY_TIME'
            AND (c.WeekDays IS NULL OR c.WeekDays LIKE '%' + @TodayWeekDay + '%')
            AND (t.LastRunDate IS NULL OR t.LastRunDate < @Today)
            AND CONVERT(CHAR(5), @NowTime, 108) >= CONVERT(CHAR(5), t.RunTime, 108);
 
    -- 3) ONE_TIME jobs: due once, when current datetime has reached
    --    SpecificDateTime and the job has never run before.
    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
        SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL
        FROM dbo.scheduler_config
        WHERE IsActive = 1
            AND IsRunning = 0
            AND ScheduleType = 'ONE_TIME'
            AND LastRunDate IS NULL
            AND @Now >= SpecificDateTime;
 
    -- Nothing due: exit early, no further statements needed.
    IF NOT EXISTS (SELECT * FROM @DueJobs)
        RETURN;
 
    -- Mark every due job as running and stamp LastRunDate in one set-based UPDATE,
    -- instead of doing it row-by-row inside the loop.
    UPDATE c SET
         c.IsRunning = 1
        ,c.LastRunDate = GETDATE()
    FROM dbo.scheduler_config c
        INNER JOIN @DueJobs d ON d.ConfigID = c.ID;
 
    -- Same for DAILY_TIME slots: stamp all fired slots at once.
    UPDATE t SET
        t.LastRunDate = @Today
    FROM dbo.scheduler_config_times t
        INNER JOIN @DueJobs d ON d.TimeSlotID = t.ID
    WHERE d.TimeSlotID IS NOT NULL;
 
    -- Bulk-insert one 'RUNNING' log row per due job, capturing the generated
    -- IDs directly via OUTPUT so the loop below needs no lookup query.
    DECLARE @LogMap TABLE (
         ConfigID INT
        ,LogID INT
        );
 
    INSERT INTO dbo.scheduler_log (ConfigID, ProcedureName, StartDate, Outcome)
         OUTPUT inserted.ConfigID, inserted.ID INTO @LogMap (ConfigID, LogID)
         SELECT ConfigID, ProcedureName, GETDATE(), 'RUNNING' FROM @DueJobs;
 
    UPDATE d SET
        d.LogID = m.LogID
    FROM @DueJobs d
        INNER JOIN @LogMap m ON m.ConfigID = d.ConfigID;
 
    -- Execute each due procedure individually: dynamic SQL with per-job error
    -- handling genuinely requires row-by-row processing, so this is a plain
    -- WHILE loop keyed on RowNum rather than a CURSOR.
    DECLARE @i INT = 1;
    DECLARE @Count INT = (SELECT COUNT(*) FROM @DueJobs);
    DECLARE @ConfigID INT;
    DECLARE @Schema SYSNAME;
    DECLARE @Name SYSNAME;
    DECLARE @Params NVARCHAR(MAX)
    DECLARE @LogID INT;
    DECLARE @SqlCmd NVARCHAR(MAX);
 
    WHILE @i <= @Count
    BEGIN
        SELECT
            @ConfigID = ConfigID,
            @Schema   = ProcedureSchema,
            @Name     = ProcedureName,
            @Params   = Parameters,
            @LogID    = LogID
        FROM @DueJobs
            WHERE RowNum = @i;
 
        -- QUOTENAME() protects schema/procedure names against injection and
        -- reserved-word issues; parameters, if present, are appended as-is.
        SET @SqlCmd = QUOTENAME(@Schema) + '.' + QUOTENAME(@Name) + CASE WHEN @Params IS NOT NULL THEN ' ' + @Params ELSE '' END;
 
        BEGIN TRY
            EXEC (@SqlCmd);
            UPDATE dbo.scheduler_log SET EndDate = GETDATE(), Outcome = 'OK' WHERE ID = @LogID;
        END TRY
        BEGIN CATCH
            -- One failing procedure never stops the loop: error is logged,
            -- next job proceeds.
            UPDATE dbo.scheduler_log
            SET EndDate = GETDATE(), Outcome = 'ERROR', ErrorMessage = ERROR_MESSAGE()
            WHERE ID = @LogID;
        END CATCH
 
        -- Always release the running flag, whether the procedure succeeded or failed.
        UPDATE dbo.scheduler_config SET IsRunning = 0 WHERE ID = @ConfigID;
 
        SET @i += 1;
    END
END
GO