USE [ProdDUTASJobSchedule];
GO

/**************************************************************************************************
-- FILE: JobControl.sql
-- PURPOSE: Manage JobControl table - Create, Read, Update, Delete for a single job
-- TABLE: dbo.JobControl
**************************************************************************************************/

-------------------------------
-- SELECT - View Job Record
-------------------------------
SELECT *
FROM [dbo].[JobControl]
WHERE [JobName] = N'PROD_DAILY_DTSBX215';
GO

-------------------------------
-- INSERT - Create Job Record
-------------------------------
INSERT INTO [dbo].[JobControl]
(
    [JobName],
    [ScheduledStartTime],
    [SchedulerAction],
    [IsActive],
    [CreatedDate],
    [Frequency],
    [FrequencyPattern]
)
VALUES
(
    N'PROD_DAILY_DTSBX215',                            -- Job name
    '14:00:00',                                            -- 2 PM (24-hour format)
    N'CONTINUE',                                           -- Action
    1,                                                     -- Active
    GETDATE(),                                             -- Current system time
    N'DAILY',                                              -- Frequency
    N'{"Frequency":"Daily","Days":["Mon","Tue","Wed","Thu","Fri"]}'  -- JSON pattern
);
GO

-------------------------------
-- UPDATE - Modify Job Record
-------------------------------
UPDATE [dbo].[JobControl]
SET
    [ScheduledStartTime] = '15:00:00',   -- Change to 3 PM
    [SchedulerAction] = 'STOP',          -- Change to STOP
    [IsActive] = 0                       -- Deactivate
WHERE
    [JobName] = N'PROD_DAILY_DTSBX215';
GO

-------------------------------
-- DELETE - Remove Job Record
-------------------------------
DELETE FROM [dbo].[JobControl]
WHERE [JobName] = N'PROD_DAILY_DTSBX215';
GO

-------------------------------
-- SELECT - View Job Record
-------------------------------
SELECT *
FROM [dbo].[JobControl]
WHERE [JobName] = N'PROD_DAILY_DTSBX215';
GO