/*************************** Schedule All Table START *************/
/*************************** Schedule START *************/

CREATE TABLE [ScheduleType]
(
[ScheduleTypeID] [int] NOT NULL,
[Type] [nvarchar] (50) NOT NULL
)
GO
ALTER TABLE [ScheduleType] ADD CONSTRAINT [PK_ScheduleType] PRIMARY KEY ([ScheduleTypeID])
GO
ALTER TABLE [ScheduleType] ADD CONSTRAINT [AK_ScheduleType_Type] UNIQUE  ([Type])
GO


CREATE TABLE [Schedule]
(
[ScheduleID] [int] NOT NULL IDENTITY(1, 1),
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[ScheduleTypeID] [int] NOT NULL
    ,StartTime        int null
	,EndTime          int null
	,Interval_Mins    int NULL
	,LegacyDaysOf     int not null constraint Schedule_LegacyDaysOf_DF default(0)
	,RunImmediately   bit not null constraint Schedule_RunImmediately_DF default(0)
	,RunOnceTime      datetime null
	,DateCreated      datetime not null 
	,RunAfterDeviceReboot     bit      not null Constraint DF_Schedule_RunAfterDeviceReboot DEFAULT(0)
	,RunTimeAfterDeviceReboot DateTime     null
	,TimeZone				  nvarchar(50) NOT NULL constraint Schedule_TimeZone_DF default 'UTC'	
)
GO
ALTER TABLE [Schedule] ADD CONSTRAINT [PK_Schedule] PRIMARY KEY ([ScheduleID])
GO

ALTER TABLE [Schedule] 
	ADD  CONSTRAINT FK_Schedule_ScheduleTypeID_ScheduleType_ScheduleTypeID FOREIGN KEY([ScheduleTypeID])
		REFERENCES[ScheduleType] ([ScheduleTypeID])
go

/*************************** Schedule END *************/

/* --------------------------- ScheduleRunTime START ----------------- */

CREATE table [ScheduleRunTime](
	[ScheduleID]         int NOT NULL ,
	[RunTime]            int NOT NULL
) 
GO

    alter table [ScheduleRunTime]
        add constraint [PK_DayScheduleRunTime] PRIMARY KEY  
	(
		[ScheduleID] ,
		[RunTime] 
	)
go

    alter table [ScheduleRunTime]
        add constraint ScheduleRunTime_ScheduleID_Schedule_ScheduleID_FK foreign key(ScheduleID) references Schedule(ScheduleID)
go

/* --------------------------- ScheduleRunTime END ----------------- */


/* --------------------------- ScheduleMonthDay START ------------------------ */


CREATE TABLE [ScheduleMonthDay](
    [ScheduleID]  int    NOT NULL,
    [DayNonZeroIndex]  int    NOT NULL
)
go


	ALTER TABLE [ScheduleMonthDay] ADD 
		CONSTRAINT ScheduleMonthDay_PK PRIMARY KEY  (ScheduleID, [DayNonZeroIndex])
go

	ALTER TABLE [ScheduleMonthDay] ADD 
		CONSTRAINT ScheduleMonthDay_ScheduleID_Schedule_ScheduleID_FK foreign key (ScheduleID) references Schedule(ScheduleID)
go

/* --------------------------- ScheduleMonthDay END ------------------------ */


/* ----------------------  ScheduleMonth START ----------- */

CREATE TABLE [ScheduleMonth](
	[ScheduleID]		[int] NOT NULL ,
	[RunOnLastDay]		[bit] NOT NULL,
	[WeekIntervalMode] [bit] NOT NULL,
	[WeekIntervalModePositionNonZeroIndex] [int]	CONSTRAINT [DF_MonthSchedule_WeekIntervalModePositionNonZeroIndex]  DEFAULT (1) NOT NULL,
	[WeekIntervalModeDayOfWeek] [nvarchar](50)		CONSTRAINT [DF_MonthSchedule_WeekIntervalModeDayOfWeek]  DEFAULT ('Monday') NOT NULL
)

go


    alter table ScheduleMonth add constraint ScheduleMonth_PK primary key (ScheduleID)
go    


/* -- add FK to Schedule */
	alter table [ScheduleMonth]
		add constraint ScheduleMonth_ScheduleID_Schedule_ScheduleID_FK 
			foreign key(ScheduleID) references Schedule(ScheduleID)
go
/* ----------------------  ScheduleMonth END ----------- */

/* --------------------------- ScheduleWeekDay START ------------------------ */

	CREATE TABLE [ScheduleWeekDay]
	(
		 [ScheduleID] [int] NOT NULL 
		,[DayOfWeek] [nvarchar](50)  NOT NULL 		 
		,CONSTRAINT ScheduleWeekDay_PK PRIMARY KEY  
		(
			[ScheduleID] ,
			[DayOfWeek] 
		)
	) 	
GO
	alter table ScheduleWeekDay
		add constraint ScheduleWeekDay_ScheduleID_Schedule_ScheduleID_FK foreign key(ScheduleID) references Schedule(ScheduleID)
go


/* --------------------------- ScheduleWeekDay END ------------------------ */

/*************************** Schedule All Tables END *************/







/**************************** Script START *************/

CREATE TABLE [ScriptStandardOutputType]
(
[ScriptStandardOutputTypeID] [int] NOT NULL,
[ScriptStandardOutputTypeName] [nvarchar] (50) NOT NULL
)
GO
ALTER TABLE [ScriptStandardOutputType] ADD CONSTRAINT [PK_ScriptStandardOutputType] PRIMARY KEY ([ScriptStandardOutputTypeID])
GO
ALTER TABLE [ScriptStandardOutputType] ADD CONSTRAINT [AK_ScriptStandardOutputType_ScriptStandardOutputTypeName] UNIQUE  ([ScriptStandardOutputTypeName])
GO


/**************************** Script END *************/



CREATE TABLE [AutomatedTask]
(
[AutomatedTaskID] [int] NOT NULL IDENTITY(1, 1),
[DeviceUnreachableRetryPeriod_Mins] [int] NULL,
[ExecutionTimeout_Mins] [int] NULL,
[SynchronizationGuid] [uniqueidentifier] NOT NULL
,AutomatedTaskIDFromSC int not null
, [Enabled] bit not null constraint AutomatedTask_Enabled_DF default (1 )
, [ScriptID]                           int                     NULL
, DeviceToRunScript                    uniqueidentifier        null
,[WakeOnLan] bit not null constraint AutomatedTask_WakeOnLAN_DF default (0)
,[EnablePowerShell]		[bit]	NULL
,AutomatedTaskPackageRevisionGuid uniqueidentifier NULL
)

GO
ALTER TABLE [AutomatedTask] ADD CONSTRAINT [PK_AutomatedTask] PRIMARY KEY ([AutomatedTaskID])
GO
CREATE TABLE [Script]
(
[ScriptID] [int] NOT NULL IDENTITY(1, 1),
[ScriptGuid] [uniqueidentifier] NOT NULL,
[Version] [nvarchar] (50) NOT NULL,
[EntryPointFileName] [nvarchar] (255) NOT NULL,
[FileName] [nvarchar] (255) NOT NULL,
[ScriptTypeID] [int] NOT NULL,
[ScriptStandardOutputTypeID] [int] NULL,
--[FileID] [int] NOT NULL,
[FileSize] [int] NOT NULL,
[FileHash] [nvarchar] (255) NOT NULL 
	,ScriptIDFromSC				int				not null constraint [ScriptIDFromSC_DF] default(0) 
    ,EarliestMWVersionSupported nvarchar(255)	not null  
	,UseLegacyDeploymentPath bit not null constraint Script_UseLegacyDeploymentPath_DF default (0)
	,RunExclusively		     bit not null constraint Script_RunExclusively_DF          default (0)
	,ScriptRevisionGuid      uniqueidentifier     not null  ROWGUIDCOL constraint AK_Script_ScriptRevisionGuid  UNIQUE
	,[ScriptName]			  nvarchar(50)     NULL
)
GO
ALTER TABLE [Script] ADD CONSTRAINT [PK_Script] PRIMARY KEY ([ScriptID])
GO
CREATE TABLE [AutomatedTaskDeviceLink]
(
[AutomatedTaskID] [int] NOT NULL,
[DeviceGuid] [uniqueidentifier] NOT NULL
,ScheduleID int NOT null 
)
GO
ALTER TABLE [AutomatedTaskDeviceLink] ADD CONSTRAINT [PK_AutomatedTaskDeviceLink] PRIMARY KEY ([AutomatedTaskID], [DeviceGuid])
GO
CREATE TABLE [AutomatedTaskParameter]
(
[AutomatedTaskID] [int] NOT NULL,
[Key] [nvarchar] (50) NOT NULL,
[Value] [nvarchar] (1024) NOT NULL
,[AutomatedTaskParameterFileID]			int     null
,[ParameterTypeID]						int not null constraint [ParameterTypeID_DF] default(0) 
)
GO
ALTER TABLE [AutomatedTaskParameter] ADD CONSTRAINT [PK_AutomatedTaskParameter] PRIMARY KEY ([AutomatedTaskID], [Key])
GO




CREATE TABLE [ScriptType]
(
[ScriptTypeID] [int] NOT NULL,
[ScriptTypeName] [nvarchar] (50) NOT NULL
)
GO
ALTER TABLE [ScriptType] ADD CONSTRAINT [PK_ScriptType] PRIMARY KEY ([ScriptTypeID])
GO



/* ---------------------------- -------------------------- */
CREATE TABLE [AutomatedTaskInstanceStatus]
(
[InstanceID] [uniqueidentifier] NOT NULL ,
[AutomatedTaskID] [int] NOT NULL ,
[DeviceGuid] [uniqueidentifier] NOT NULL,
[ScheduledTime] [datetime] NOT NULL,
[NextScheduledTime] [datetime] NULL,
[Status] [int] NOT NULL ,
[ActualStartTime] [datetime] NULL,
[ActualEndTime] [datetime] NULL,
[ScriptReturnCode] [int] NULL,
[StatusMessage] [nvarchar] (4000) NULL
,TaskError int NOT NULL constraint AutomatedTaskInstanceStatus_TaskError_DF DEFAULT (-1)
,AutomatedTaskIDFromSC int not null
,RemExecProcessID INT NULL 
,TaskErrorDetail VarBinary(4000) not null
,[ScheduleID]	int  not null 
)  
GO  
ALTER TABLE [AutomatedTaskInstanceStatus] ADD CONSTRAINT PK_AutomatedTaskInstanceStatus PRIMARY KEY ([InstanceID])
GO
/*
NO FK?
ID - is it needed? why not AutomatedTaskPurgeJobID ??
*/
CREATE TABLE [AutomatedTaskPurgeJob]
(
[ID] [int] NOT NULL IDENTITY(1, 1),  
[ScheduleJobID] [int] NOT NULL,
[DeviceID] [int] NOT NULL
)
GO  
ALTER TABLE [AutomatedTaskPurgeJob] ADD CONSTRAINT PK_AutomatedTaskPurgeJob PRIMARY KEY ([ID])
GO


CREATE TABLE AutomatedTaskAlertRule 
  (
       [AlertRuleID]			INT				NOT NULL,
	   [AutomatedTaskIDFromSC]  INT             NOT NULL,
       [ScriptErrorCode]		INT				NULL,
       [ScriptErrorCodeOp]	    bit				null,
       [ScriptStandardOutput]   nvarchar(4000)	null,  
       [ScriptStandardOutputOp] bit				null,
       [ScriptStandardError]    nvarchar(4000)	null,
       [ScriptStandardErrorOp]	bit				null,
       [TaskErrorOp]			bit				null 
     ,[ScriptStandardOutputSearchOptions]  ntext null     
     ,[ScriptStandardErrorSearchOptions]   ntext null     
      
	,CONSTRAINT [PK_AutomatedTaskAlertRule] PRIMARY KEY ([AlertRuleID]) 
	,CONSTRAINT [FK_AutomatedTaskAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
		   REFERENCES [AlertRule]([AlertRuleID]) 
	)
go



CREATE TABLE AutomatedTaskAlert 
  (
       [AutomatedTaskAlertGuid]					uniqueidentifier		NOT NULL,
       [AlertRuleID]			INT						NOT NULL,	   	   
	   [AutomatedTaskIDFromSC]  int						NOT NULL,
	   [ObjectID]               int						NOT NULL,	
       [ScriptErrorCode]	    INT						NULL, 
       [TaskError]				INT						NOT NULL,    
       [ScriptStandardOutput]   nvarchar(4000)			NULL,   
       [ScriptStandardError]    nvarchar(4000)           NULL  
	 , CONSTRAINT [PK_AutomatedTaskAlert] PRIMARY KEY ([AutomatedTaskAlertGuid])
     , CONSTRAINT [FK_AutomatedTaskAlert_AlertRule] FOREIGN KEY ([AlertRuleID]) REFERENCES [AlertRule]([AlertRuleID])
       )
GO

CREATE TABLE ScriptLastUsed
(
	ScriptID           int constraint ScriptLastUsed_PK PRIMARY KEY,
    ScriptLastUsedTime DateTime not null
)
GO

CREATE TABLE AutomatedTaskHistory
(     
	 [AutomatedTaskGuid]        uniqueidentifier NOT NULL
	 ,[DeleteTime]              DateTime         not null	
	 ,CONSTRAINT PK_AutomatedTaskHistory PRIMARY KEY ([AutomatedTaskGuid])       
)
GO

/* ---------------------------- FK -------------------------- */



ALTER TABLE [ScriptType] ADD CONSTRAINT [AK_ScriptType_ScriptTypeName] UNIQUE  ([ScriptTypeName])
GO
ALTER TABLE [AutomatedTaskDeviceLink] ADD
CONSTRAINT [FK_AutomatedTaskDeviceLink_AutomatedTask] FOREIGN KEY ([AutomatedTaskID]) REFERENCES [AutomatedTask] ([AutomatedTaskID])
GO
ALTER TABLE [AutomatedTaskParameter] ADD
CONSTRAINT [FK_AutomatedTaskParameter_AutomatedTask] FOREIGN KEY ([AutomatedTaskID]) REFERENCES [AutomatedTask] ([AutomatedTaskID])
GO


ALTER TABLE [AutomatedTask] ADD
CONSTRAINT [FK_AutomatedTask_ScriptID_Script_ScriptID_FK] 
    FOREIGN KEY ([ScriptID])
    REFERENCES [Script]([ScriptID])
GO




ALTER TABLE [Script] ADD
CONSTRAINT [FK_Script_ScriptType] FOREIGN KEY ([ScriptTypeID]) REFERENCES [ScriptType] ([ScriptTypeID]),
CONSTRAINT [FK_Script_ScriptStandardOutputType] FOREIGN KEY ([ScriptStandardOutputTypeID]) REFERENCES [ScriptStandardOutputType] ([ScriptStandardOutputTypeID])
GO

ALTER TABLE ScriptLastUsed
ADD 
	CONSTRAINT [FK_ScriptLastUsed_Script] FOREIGN KEY ([ScriptID])
    REFERENCES [Script]([ScriptID])
GO


/* ---------------------- AutomationTask File START ----- */

Create table AutomatedTaskParameterFile(
	[AutomatedTaskParameterFileID]		int				not null
	,[DateAdded]						datetime		not null
	,[FileName]							nvarchar(255)	not null	
	,[FileBinaryHash]					nvarchar(64)	not null
	,[FileBinarySizeInBytes]			int				not null
	,constraint [AutomatedTaskParameterFile_PK] primary key 
	(
		[AutomatedTaskParameterFileID]
	)
)
go


/* ---------------------- AutomationTask File START ----- */



alter table
	[AutomatedTaskParameter]
	add constraint 
					AutomatedTaskParameter_AutomatedTaskParameterFileID_FK
	foreign key
					(AutomatedTaskParameterFileID)
	references
					AutomatedTaskParameterFile(AutomatedTaskParameterFileID)
go


CREATE TABLE AutomatedTaskFileLastUsed
(
	[AutomatedTaskParameterFileID]  int			not null,
    [LastUsedTime]					DateTime	not null,    	
	constraint [AutomatedTaskFileLastUsed_PK] primary key    
	(
            [AutomatedTaskParameterFileID]
    )
)
GO
ALTER TABLE AutomatedTaskFileLastUsed
ADD 
	CONSTRAINT [AutomatedTaskFileLastUsed_AutomatedTaskParameterFile_FK] FOREIGN KEY ([AutomatedTaskParameterFileID])
		REFERENCES [AutomatedTaskParameterFile]([AutomatedTaskParameterFileID])
GO


CREATE TABLE ScriptTargetOSLink
(      ScriptID                 int     not null,
      ScriptTargetOSID          TINYINT not null,
	CONSTRAINT PK_ScriptTarggetOSLink 
			PRIMARY KEY  
											   nonclustered
			(ScriptID, ScriptTargetOSID) 
)
go

create table AutomatedTaskPackage
(
	AutomatedTaskPackageGuid uniqueidentifier not null,
	AutomatedTaskPackageRevisionGuid uniqueidentifier not null,
	PackageName nvarchar(256) not null,	
	PackageTargetOSID int not null
		
) 
go

create table AutomatedTaskPackageNode 
(
	AutomatedTaskPackageNodeGuid uniqueidentifier not null,
	AutomatedTaskPackageRevisionGuid uniqueidentifier not null,
	RunOrder int null,
	ScriptID int not null
)
go

create table AutomatedTaskPackageNotification
(
	AutomatedTaskPackageNotificationGuid     uniqueidentifier not null,
	AutomatedTaskPackageNodeGuid       uniqueidentifier not null,
	Condition                          int              not null,
	ConditionValue                     nvarchar(255)    not null,
	Message                            nvarchar(255)    not null
)
go

create table AutomatedTaskPackageAction
(
	AutomatedTaskPackageActionGuid     uniqueidentifier not null,
	AutomatedTaskPackageNodeGuid       uniqueidentifier not null,
	Condition                          int              not null,
	ConditionValue                     nvarchar(255)    not null,
	ActionType                         int              not null,
	NextAutomatedTaskPackageNodeGuid   uniqueidentifier      null,
	RunOrder                           int               not null	
)
go

create table AutomatedTaskPackageScriptParameter 
(
    AutomatedTaskID int not null,
	AutomatedTaskPackageNodeGuid uniqueidentifier not null,
	ScriptParameterKey nvarchar(50) not null,
    ParameterValue nvarchar(255) not null,
    AutomatedTaskParameterFileID int null,
    ParameterTypeID int not null
)
go

alter table AutomatedTaskPackage
	add constraint PK_AutomatedTaskPackage primary key  (AutomatedTaskPackageRevisionGuid)
go

alter table AutomatedTaskPackageNode add 
	constraint PK_AutomatedTaskPackageNode primary key  ( AutomatedTaskPackageNodeGuid)
go
alter table AutomatedTaskPackageNode add 
	constraint FK_AutomatedTaskPackageNode_AutomatedTaskPackage foreign key ( AutomatedTaskPackageRevisionGuid) 
		references AutomatedTaskPackage ( AutomatedTaskPackageRevisionGuid)
go

alter table AutomatedTaskPackageNode add 
	constraint FK_AutomatedTaskPackageNonde_Script foreign key ( ScriptID) references Script(ScriptID)			
go


alter table AutomatedTaskPackageNotification
	add constraint 
        PK_AutomatedTaskPackageNotification primary key  ( AutomatedTaskPackageNotificationGuid)
go

alter table AutomatedTaskPackageNotification
	add constraint 
	   FK_AutomatedTaskPackageNotification_AutomatedTaskPackageNode 
           foreign key ( AutomatedTaskPackageNodeGuid) references AutomatedTaskPackageNode(AutomatedTaskPackageNodeGuid)
go	


alter table AutomatedTaskPackageAction
	add constraint 
        PK_AutomatedTaskPackageAction primary key  ( AutomatedTaskPackageActionGuid)
go

alter table AutomatedTaskPackageAction
	add constraint 
	   FK_AutomatedTaskPackageAction_AutomatedTaskPackageNode 
           foreign key ( AutomatedTaskPackageNodeGuid) references AutomatedTaskPackageNode(AutomatedTaskPackageNodeGuid)
go	

alter table AutomatedTaskPackageAction
	add constraint 
         FK_AutoamtedTaskPackageAction_AutomatedTaskPackageNode_NextNode 
              foreign key (NextAutomatedTaskPackageNodeGuid) references AutomatedTaskPackageNode(AutomatedTaskPackageNodeGuid)	
go



alter table AutomatedTaskPackageScriptParameter add
    constraint PK_AutomatedTaskPackageScriptParameter primary key (AutomatedTaskID, AutomatedTaskPackageNodeGuid,ScriptParameterKey)
go
alter table AutomatedTaskPackageScriptParameter add
    constraint FK_AutomatedTaskPackageScriptParameter_AutoamtedTask foreign key ( AutomatedTaskID) references AutomatedTask(AutomatedTaskID) 
go
alter table AutomatedTaskPackageScriptParameter add
    constraint FK_AutomatedTaskPackageScriptParameter_AutomatedTaskPackageNode foreign key (AutomatedTaskPackageNodeGuid) 
		references AutomatedTaskPackageNode(AutomatedTaskPackageNodeGuid)
go
alter table AutomatedTaskPackageScriptParameter add
    constraint FK_AutomatedTaskPacakageScriptParameter_AutomatedTaskParameterFile foreign key (AutomatedTaskParameterFileID) 
		references AutomatedTaskParameterFile (AutomatedTaskParameterFileID)
go

/*
	AutomatedTask
*/
alter table AutomatedTask add constraint FK_AutomatedTask_AutomatedTaskPackage foreign key (AutomatedTaskPackageRevisionGuid) references AutomatedTaskPackage(AutomatedTaskPackageRevisionGuid)
go


alter TABLE [AutomatedTaskDeviceLink]
add 
	constraint AutomatedTaskDeviceLink_ScheduleID_FK 
		foreign key (ScheduleID)
			references Schedule(ScheduleID)
go