CREATE TABLE [dbo].[ATScheduledItem] (
    [ScheduleId]         INT IDENTITY(1,1) NOT NULL,
    [ServerHost]         NVARCHAR(255) NOT NULL,
    [Name]               NVARCHAR(255) NOT NULL,
    [Description]        NVARCHAR(255) NULL,

    [Enabled]            BIT NOT NULL 
        CONSTRAINT [DF_ATScheduledItem_Enabled] DEFAULT(1),

    [WaitForCompletion]  BIT NOT NULL 
        CONSTRAINT [DF_ATScheduledItem_WaitForCompletion] DEFAULT(0),

    [Priority]           INT NULL 
        CONSTRAINT [DF_ATScheduledItem_Priority] DEFAULT(0),

    [ScheduleTypeId]     INT NOT NULL 
        CONSTRAINT [DF_ATScheduledItem_ScheduleTypeId] DEFAULT(1),  -- FK to ATScheduleType
    [RecurrenceExpression] NVARCHAR(MAX) NULL,
    [StartTime]          DATETIME2 NULL,       -- when first run should start
    [ExpireTime]         DATETIME2 NULL,       -- when the schedule should stop running
    
    [StatusId]           INT NOT NULL 
        CONSTRAINT [DF_ATScheduledItem_StatusId] DEFAULT(1),

    [LastRunStatusId]    INT NULL,
    [LastRun]            DATETIME2 NULL,

    [CreatedAt]          DATETIME2 NOT NULL 
        CONSTRAINT [DF_ATScheduledItem_CreatedAt] DEFAULT(GETUTCDATE()),

    [UpdatedAt]          DATETIME2 NOT NULL 
        CONSTRAINT [DF_ATScheduledItem_UpdatedAt] DEFAULT(GETUTCDATE()),

    CONSTRAINT [PK_ATScheduledItem] 
        PRIMARY KEY CLUSTERED ([ScheduleId] ASC),

    CONSTRAINT [UQ_ATScheduledItem_Name] 
        UNIQUE ([Name]),

    CONSTRAINT [FK_ATScheduledItem_ATServer]
        FOREIGN KEY ([ServerHost])
        REFERENCES [dbo].[ATServer]([HostName]),

    CONSTRAINT [FK_ATScheduledItem_ATScheduleStatusType]
        FOREIGN KEY ([StatusId]) 
        REFERENCES [dbo].[ATScheduleStatusType]([StatusId]),

    CONSTRAINT [FK_ATScheduledItem_ATRunStatusType]
        FOREIGN KEY ([LastRunStatusId]) 
        REFERENCES [dbo].[ATRunStatusType]([StatusId]),

    CONSTRAINT [FK_ATScheduledItem_ATScheduleType]
        FOREIGN KEY ([ScheduleTypeId]) 
        REFERENCES [dbo].[ATScheduleType]([ScheduleTypeId])
);
GO
