CREATE TABLE [PatchNow]
(
[DeviceGuid] [uniqueidentifier] NOT NULL,
[PatchGuid] [uniqueidentifier] NOT NULL,
[RevisionNumber] [int] NOT NULL,
[DateCreated] [datetime] NOT NULL
,CONSTRAINT [PK_PatchNow] PRIMARY Key NonClustered  ([DeviceGuid], [PatchGuid], [RevisionNumber])
)
GO


CREATE TABLE [MwusRevision]
(
[RevisionID] [int] NOT NULL IDENTITY(1, 1),
[UpdateGuid] [uniqueidentifier] NOT NULL,
[RevisionNumber] [int] NOT NULL,
[UpdateType] [tinyint] NOT NULL,
[IsLeaf] [bit] NOT NULL,
[IsLeafChange] [datetime] NOT NULL,
[LastChangeAnchor] [nvarchar] (255) NULL,
[Hidden] [bit] NOT NULL,
[Name] [nvarchar] (255) NOT NULL,
[PublicationState] [nvarchar] (50) NOT NULL,
[IsRoot] [bit] NOT NULL CONSTRAINT [MwusRevision_IsRoot_DF] DEFAULT ((0)),
[IsLatestRevisionNumber] [bit] NOT NULL CONSTRAINT [MwusRevision_IsLatestRevisionNumber_DF] DEFAULT ((0)),
[TimeCreated] [datetime] NOT NULL CONSTRAINT [DF_MwusRevision_TimeCreated] DEFAULT (getdate())  /* getutcdate  */
,CONSTRAINT [PK_Revision] PRIMARY KEY NONCLUSTERED  ([RevisionID])
)
GO

CREATE UNIQUE NonCLUSTERED INDEX [MwusRevision_AK] ON [MwusRevision] ([UpdateGuid], [RevisionNumber] DESC)
GO

CREATE NONCLUSTERED INDEX [MwusRevision_IsLatestRevisionNumber_RevisionID_UpdateGuid] ON [MwusRevision] ([IsLatestRevisionNumber],[RevisionID], [UpdateGuid])
GO

CREATE NONCLUSTERED INDEX [MwusRevision_UpdateGuid_IsLatestRevisionNumber_RevisionID_IX] ON [MwusRevision] ([UpdateGuid], [IsLatestRevisionNumber],[RevisionID])
go

CREATE TABLE [MwusLanguage]
(
[LanguageID] [int] NOT NULL,
[Code] [nvarchar] (10) NOT NULL,
[Name] [nvarchar] (50) NOT NULL
,CONSTRAINT [PK_MwusLanguage] PRIMARY Key NonClustered  ([LanguageID])
)
GO


CREATE TABLE [MwusDevicePatchStatus]
(
    [DeviceGuid]        [UNIQUEIDENTIFIER]  NOT NULL,
    [UpdateGuid]        [UNIQUEIDENTIFIER]  NOT NULL,
    [RevisionNumber]    [INT]               NOT NULL,
    [EventID]           [SMALLINT]          NOT NULL,
    [LastChangeTime]    [DATETIME]          NOT NULL CONSTRAINT [DF_MwusDevicePatchStatus_LastChangeTime] DEFAULT  getDATE()
    ,RowID               INT NOT NULL IDENTITY(1,1)
    ,CONSTRAINT [PK_MwusDevicePatchStatus] PRIMARY Key NonClustered  ([UpdateGuid], RowID )
)
GO
/*
CREATE NONCLUSTERED INDEX [MwusDevicePatchStatus_EventID_DeviceGuid_UpdateGuid_LastChangeTime] ON [MwusDevicePatchStatus] ([EventID],[DeviceGuid], [LastChangeTime], [UpdateGuid])
*/

CREATE NONCLUSTERED INDEX [MwusDevicePatchStatus_EventID_LastChangeTime] ON [MwusDevicePatchStatus]
(
	[EventID] ,LastChangeTime
)
go

CREATE TABLE [MwusDevice]
(
[DeviceGuid] [uniqueidentifier] NOT NULL,
[TargetGroupGuid] [uniqueidentifier] NOT NULL,
[SusClientId] [uniqueidentifier] NULL,
[LastChangeTime] [datetime] NULL,
[ProtocolVersion] [nvarchar] (10) NULL,
[RequiresFileLocationUpdate] [bit] NOT NULL CONSTRAINT [DF_MwusDevice_RequiresFileLocationUpdate] DEFAULT ((1)),
[OSLocale] [nvarchar] (50) NULL,
[DnsName] [nvarchar] (255) NULL,
[RequiresCookieUpdate] [bit] NOT NULL CONSTRAINT [MwusDevice_RequiresCookieUpdate_DF] DEFAULT ((0))
,CONSTRAINT [PK_MwusDevice] PRIMARY Key NonClustered  ([DeviceGuid])
)
GO

CREATE TABLE [MwusDeployment]
(
[RevisionID] [int] NOT NULL,
[TargetGroupGuid] [uniqueidentifier] NOT NULL,
[Action] [tinyint] NOT NULL,
[Deadline] [datetime] NULL,
[LastChangeTime] [datetime] NOT NULL,
[DownloadPriority] [tinyint] NOT NULL,
[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_MwusDeployment_IsDeleted] DEFAULT ((0))
,CONSTRAINT [PK_MwusDeployment] PRIMARY Key NonClustered  ([RevisionID], [TargetGroupGuid])
)
GO

CREATE NONCLUSTERED INDEX [MwusDeployment_TargetGroupGuid_LastChangeTime_Action_Deadline_DownloadPriority_IsDeleted_RevisionID] 
	ON [MwusDeployment] ([TargetGroupGuid], [LastChangeTime], [Action], [Deadline], [DownloadPriority], [IsDeleted], [RevisionID]) 
go


CREATE TABLE [MwusRevisionLanguage]
(
[RevisionID] [int] NOT NULL,
[LanguageID] [int] NOT NULL
,CONSTRAINT [PK_MwusRevisionLanguage] PRIMARY Key NonClustered  ([RevisionID], [LanguageID])
)
GO

CREATE TABLE [MwusFileForRevision]
(
[RevisionID] [int] NOT NULL,
[MwusFileGuid] [uniqueidentifier] NOT NULL,
[PatchType] [int] NOT NULL
,CONSTRAINT [PK_MwusFileForRevision] PRIMARY Key NonClustered  ([RevisionID], [MwusFileGuid])
)
GO

CREATE TABLE [MwusFile]
(
[MwusFileGuid] [uniqueidentifier] NOT NULL,
[FileDigest] [binary] (20) NOT NULL,
[FileName] [nvarchar] (256) NOT NULL,
[MUURL] [nvarchar] (1024) NULL,
[CacheStatus] [tinyint] NULL,
[CacheJobGuid] [uniqueidentifier] NULL,
[CachePath] [nvarchar] (2048) NULL
,CONSTRAINT [PK_MwusFile] PRIMARY Key NonClustered  ([MwusFileGuid])
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [MwusFile_FileDigest_AK] ON [MwusFile] ([MwusFileGuid])
GO

CREATE TABLE [MwusBundle]
(
[BundleID] [int] NOT NULL IDENTITY(1, 1),
[RevisionID] [int] NOT NULL,
[BundledUpdateGuid] [uniqueidentifier] NOT NULL,
[BundledRevisionNumber] [int] NOT NULL,
[BundledRevisionID] [int] NOT NULL CONSTRAINT [DF_MwusBundle_BundledRevisionID] DEFAULT ((0)),
[ClauseGuid] [uniqueidentifier] NOT NULL
,CONSTRAINT [MwusBundle_PK] PRIMARY KEY NONCLUSTERED  ([BundleID])
)
GO


CREATE UNIQUE NonCLUSTERED INDEX [AK_MwusBundle] ON [MwusBundle] ([RevisionID], [BundledUpdateGuid], [BundledRevisionNumber] DESC, [ClauseGuid])
GO



CREATE TABLE [MwusMetadata]
(
[MetadataID] [int] NOT NULL IDENTITY(1, 1),
[RevisionID] [int] NOT NULL,
[XmlFragment] [varbinary] (8000) NULL,                   /*  [varbinary] (max)  */
[FragmentType] [tinyint] NOT NULL,
[Locale] [nvarchar] (50) NULL
, CONSTRAINT [MwusMetadata_AK] UNIQUE NonCLUSTERED  ([RevisionID], [FragmentType], [Locale])
, CONSTRAINT [MwusMetadata_PK] PRIMARY KEY NONCLUSTERED  ([MetadataID])
)
GO


CREATE TABLE [MwusPrerequisite]
(
[PrerequisiteID] [int] NOT NULL IDENTITY(1, 1),
[RevisionID] [int] NOT NULL,
[PrerequisiteUpdateGuid] [uniqueidentifier] NOT NULL,
[ClauseGuid] [uniqueidentifier] NOT NULL,
[IsCategory] [bit] NOT NULL
,CONSTRAINT [AK_MwusPrerequisite] UNIQUE NonCLUSTERED  ([RevisionID], [PrerequisiteUpdateGuid], [ClauseGuid])
,CONSTRAINT [PK_MwusPrerequisite] PRIMARY KEY NONCLUSTERED  ([PrerequisiteID])
)
GO


CREATE NONCLUSTERED INDEX [MwusPrerequisite_PrerequisiteUpdateGuid] ON [MwusPrerequisite] ([PrerequisiteUpdateGuid])
GO

CREATE TABLE [MwusUpdateCategoryLink]
(
[UpdateRevisionID] [int] NOT NULL,
[CategoryGuid] [uniqueidentifier] NOT NULL
,CONSTRAINT [PK_MwusUpdateCategoryLink] PRIMARY Key NonClustered  ([UpdateRevisionID], [CategoryGuid])
)
GO


CREATE TABLE [MwusUpdatePatchApprovals]
(
[PatchGuid] [uniqueidentifier] NOT NULL,
[RevisionNumber] [int] NOT NULL,
[TargetGroupGuid] [uniqueidentifier] NOT NULL,
[TargetGroupName] [nvarchar] (256) NOT NULL,
[Action] [int] NULL,
[Deadline] [datetime] NULL,
[MappedRevisionID] [int] NULL
)
GO


CREATE TABLE [MwusTargetGroup]
(
[TargetGroupGuid] [uniqueidentifier] NOT NULL,
[TargetGroupName] [nvarchar] (256) NOT NULL
,CONSTRAINT [PK_MwusTargetGroup] PRIMARY Key NonClustered  ([TargetGroupGuid])
)
GO


CREATE TABLE [PatchAgentConfigurations]
(
[DeviceID] [int] NOT NULL,
[WsusServerURL] [nvarchar] (512) NOT NULL,
[WsusStatusServerURL] [nvarchar] (512) NULL,
[TargetGroupName] [nvarchar] (256) NULL,
[ElevateNonAdmins] [int] NULL,
[AutomaticUpdateOptions] [int] NULL,
[AutoInstallMinorUpdates] [int] NULL,
[DetectionFrequency] [int] NULL,
[NoAutoRebootWithLoggedOnUsers] [int] NULL,
[NoAutoUpdate] [int] NULL,
[RebootRelaunchTimeout] [int] NULL,
[RebootWarningTimeout] [int] NULL,
[RescheduleWaitTime] [int] NULL,
[TargetGroupGuid] [uniqueidentifier] NOT NULL ROWGUIDCOL , /* CONSTRAINT [PatchAgentConfigurations_TargetGroupGuid_DF] DEFAULT (newid()) */ 
[ScheduleType] [tinyint] NULL,
[ScheduleTime] [tinyint] NULL,
[Monday] [bit] NULL,
[Tuesday] [bit] NULL,
[Wednesday] [bit] NULL,
[Thursday] [bit] NULL,
[Friday] [bit] NULL,
[Saturday] [bit] NULL,
[Sunday] [bit] NULL,
[DateOfMonth] [tinyint] NULL,
[WeekOfMonth] [tinyint] NULL,
[DayOfWeek] [tinyint] NULL,
[ForceReboot] [bit] NOT NULL CONSTRAINT [PatchAgentConfigurations_ForceReboot_DF] DEFAULT ((0)),
[DeviceGuid]  uniqueidentifier NOT NULL
,CONSTRAINT [PK_PatchAgentConfigurations] PRIMARY Key NonClustered  ([DeviceID])
)
GO


CREATE TABLE [MwusCategory]
(
[MwusCategoryGuid] [uniqueidentifier] NOT NULL,
[Name] [nvarchar] (255) NOT NULL
,CONSTRAINT [AK_MwusCategory_Name] UNIQUE NONCLUSTERED  ([Name])
,CONSTRAINT [PK_MwusCategory] PRIMARY Key NonClustered  ([MwusCategoryGuid])
)
go

CREATE TABLE [MwusClassification]
(
[MwusClassificationGuid] [uniqueidentifier] NOT NULL,
[Name] [nvarchar] (255) NOT NULL
, CONSTRAINT [PK_MwusClassification] PRIMARY Key NonClustered  ([MwusClassificationGuid])
,CONSTRAINT [AK_MwusClassification_Name] UNIQUE NONCLUSTERED  ([Name])
)
GO


CREATE TABLE [MwusConfiguration]
(
[Key] [nvarchar] (32) NOT NULL,
[Value]  [nvarchar] (4000) NULL                                                      /*  [nvarchar] (max)  */
,CONSTRAINT [PK_MwusConfiguration] PRIMARY Key NonClustered  ([Key])
)
GO


CREATE TABLE [MwusParentUss]
(
[MwusParentUssID] [int] NOT NULL,
[CatalogOnlySync] [bit] NOT NULL,
[LazySync] [bit] NOT NULL,
[ServerHostsPsfFiles] [bit] NOT NULL,
[MaxNumberOfUpdatesPerRequest] [int] NOT NULL,
[Fqdn] [nvarchar] (1000) NOT NULL,
[NewConfigAnchor] [nvarchar] (300) NULL,
[LastAuthorizationCookie] [varbinary] (1000) NULL,
[LastCookie] [varbinary] (1000) NULL,
[LastConfigAnchor] [nvarchar] (300) NULL,
[LastSyncAnchor] [nvarchar] (300) NULL,
[LanguageUpdateList] [nvarchar] (300) NULL
,CONSTRAINT [PK_MwusParentUss] PRIMARY Key NonClustered  ([MwusParentUssID])
)
GO

CREATE TABLE [MwusRevisionSupersedesUpdate](
	[RevisionID] [int] NOT NULL,
	[SupersededUpdateGuid] [uniqueidentifier] NOT NULL,
 	CONSTRAINT [PK_MwusRevisionSupersedesUpdate] PRIMARY KEY NONCLUSTERED ([RevisionID], [SupersededUpdateGuid])
)
GO

CREATE NONCLUSTERED INDEX [MwusRevisionSupersedesUpdate_SupersededUpdateGuid_IX] ON [MwusRevisionSupersedesUpdate]
(
	[SupersededUpdateGuid] ASC
)
GO




ALTER TABLE [MwusBundle] ADD CONSTRAINT [FK_MwusBundle_MwusRevision_RevisionID] FOREIGN KEY ([RevisionID]) REFERENCES [MwusRevision] ([RevisionID])
GO

ALTER TABLE [MwusDeployment] ADD CONSTRAINT [FK_MwusDeployment_MwusRevision] FOREIGN KEY ([RevisionID]) REFERENCES [MwusRevision] ([RevisionID])
GO

ALTER TABLE [MwusDeployment] ADD CONSTRAINT [FK_MwusDeployment_MwusTargetGroup] FOREIGN KEY ([TargetGroupGuid]) REFERENCES [MwusTargetGroup] ([TargetGroupGuid])
GO

ALTER TABLE [MwusDevice] ADD CONSTRAINT [FK_MwusDevice_MwusTargetGroup] FOREIGN KEY ([TargetGroupGuid]) REFERENCES [MwusTargetGroup] ([TargetGroupGuid])
GO

ALTER TABLE [MwusFileForRevision] ADD CONSTRAINT [FK_MwusFileForRevision_MwusFile] FOREIGN KEY ([MwusFileGuid]) REFERENCES [MwusFile] ([MwusFileGuid])
GO

ALTER TABLE [MwusFileForRevision] ADD CONSTRAINT [FK_MwusFileForRevision_MwusRevision] FOREIGN KEY ([RevisionID]) REFERENCES [MwusRevision] ([RevisionID])
GO

ALTER TABLE [MwusMetadata] ADD CONSTRAINT [FK_MwusMetadata_MwusRevision] FOREIGN KEY ([RevisionID]) REFERENCES [MwusRevision] ([RevisionID])
GO

ALTER TABLE [MwusPrerequisite] ADD CONSTRAINT [FK_MwusPrerequisite_MwusRevision] FOREIGN KEY ([RevisionID]) REFERENCES [MwusRevision] ([RevisionID])
GO

ALTER TABLE [MwusRevisionLanguage] ADD CONSTRAINT [FK_MwusRevisionLanguage_MwusLanguage] FOREIGN KEY ([LanguageID]) REFERENCES [MwusLanguage] ([LanguageID])
GO

ALTER TABLE [MwusRevisionLanguage] ADD CONSTRAINT [FK_MwusRevisionLanguage_MwusRevision] FOREIGN KEY ([RevisionID]) REFERENCES [MwusRevision] ([RevisionID]) ON DELETE CASCADE
GO

ALTER TABLE [MwusUpdateCategoryLink] ADD CONSTRAINT [FK_MwusUpdateCategoryLink_MwusRevision_UpdateRevisionID] FOREIGN KEY ([UpdateRevisionID]) REFERENCES [MwusRevision] ([RevisionID])
GO


ALTER TABLE [PatchAgentConfigurations] ADD CONSTRAINT [FK_PatchAgentConfigurations_Device] FOREIGN KEY ([DeviceID]) REFERENCES [Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [MwusRevisionSupersedesUpdate]  ADD  CONSTRAINT [FK_MwusSupersedesUpdate_MwusRevision] FOREIGN KEY([RevisionID]) REFERENCES [MwusRevision] ([RevisionID])
GO
