/*
	PostMWData_Branch_GeneratedTables.sql START
*/

/* 
 * TABLE: [dbo].[MwusBundle] 
 */

CREATE TABLE [dbo].[MwusBundle](
	[BundleID]							[int] IDENTITY(1,1)			NOT NULL,
	[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] ASC
	)
) 



/* 
 * TABLE: [dbo].[MwusDeployment] 
 */

CREATE TABLE [dbo].[MwusDeployment](
	[DeploymentID] [int] IDENTITY(1,1) NOT NULL,
    [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 CLUSTERED ([RevisionID], [TargetGroupGuid])
)
go



/* 
 * TABLE: [dbo].[MwusDevice] 
 */

CREATE TABLE [dbo].[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
	,
    CONSTRAINT [PK_MwusDevice] PRIMARY KEY CLUSTERED ([DeviceGuid])
)
go



/* 
 * TABLE: [dbo].[MwusFile] 
 */

CREATE TABLE [dbo].[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 CLUSTERED ([MwusFileGuid])
)
go



/* 
 * TABLE: [dbo].[MwusFileForRevision] 
 */

CREATE TABLE [dbo].[MwusFileForRevision](
    [RevisionID]    int                 NOT NULL,
    [MwusFileGuid]  uniqueidentifier    NOT NULL,
    [PatchType]     int                 NOT NULL,
    CONSTRAINT [PK_MwusFileForRevision] PRIMARY KEY CLUSTERED ([RevisionID], [MwusFileGuid])
)
go



/* 
 * TABLE: [dbo].[MwusLanguage] 
 */

CREATE TABLE [dbo].[MwusLanguage](
    [LanguageID]  int             NOT NULL,
    [Code]        nvarchar(10)    NOT NULL,
    [Name]        nvarchar(50)    NOT NULL,
    CONSTRAINT [PK_MwusLanguage] PRIMARY KEY CLUSTERED ([LanguageID])
)
go



/* 
 * TABLE: [dbo].[MwusMetadata] 
 */

CREATE TABLE [dbo].[MwusMetadata](
    [MetadataID]    int              IDENTITY(1,1),
    [RevisionID]    int              NOT NULL,
    [XmlFragment]   varbinary(max)       NULL,
    [FragmentType]  tinyint          NOT NULL,
    [Locale]        nvarchar(50)     NULL,
    CONSTRAINT [MwusMetadata_PK] PRIMARY KEY NONCLUSTERED ([MetadataID]),
    CONSTRAINT [MwusMetadata_AK]  UNIQUE CLUSTERED ([RevisionID], [FragmentType], [Locale])
)
go



/* 
 * TABLE: [dbo].[MwusParentUss] 
 */

CREATE TABLE [dbo].[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 CLUSTERED ([MwusParentUssID])
)
go



/* 
 * TABLE: [dbo].[MwusPrerequisite] 
 */

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



/* 
 * TABLE: [dbo].[MwusRevision] 
 */

CREATE TABLE [dbo].[MwusRevision](
    [RevisionID]              int                 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                 CONSTRAINT [MwusRevision_IsRoot_DF]					DEFAULT 0				NOT NULL,
    [IsLatestRevisionNumber]  bit                 CONSTRAINT [MwusRevision_IsLatestRevisionNumber_DF]	DEFAULT 0				NOT NULL,
    TimeCreated               datetime            CONSTRAINT DF_MwusRevision_TimeCreated				default(getutcdate())   not null
    ,
	CONSTRAINT [PK_Revision] PRIMARY KEY NONCLUSTERED ([RevisionID])
)
go

create index MwusRevision_UpdateGuid_IsLatestRevisionNumber_i_RevisionID_IX on dbo.MwusRevision(UpdateGuid,IsLatestRevisionNumber) include (RevisionID)
go

create index MwusPrerequisite_PrerequisiteUpdateGuid on dbo.MwusPrerequisite(PrerequisiteUpdateGuid)
go



/* 
 * TABLE: [dbo].[MwusTargetGroup] 
 */

CREATE TABLE [dbo].[MwusTargetGroup](
    [TargetGroupGuid]  uniqueidentifier    NOT NULL,
    [TargetGroupName]  nvarchar(256)       NOT NULL,
    CONSTRAINT [PK_MwusTargetGroup] PRIMARY KEY CLUSTERED ([TargetGroupGuid])
)
go




/* 
 * TABLE: [dbo].[MwusUpdateCategoryLink] 
 */

CREATE TABLE [dbo].[MwusUpdateCategoryLink](
	[UpdateRevisionID]					[int]				NOT NULL,
	[CategoryGuid]						[uniqueidentifier]	NOT NULL,
	 CONSTRAINT [PK_MwusUpdateCategoryLink] PRIMARY KEY CLUSTERED 
	(
		[UpdateRevisionID] ASC,
		[CategoryGuid] ASC
	)
) 

GO


/* 
 * INDEX: [AK_MwusBundle] 
 */

CREATE UNIQUE CLUSTERED INDEX [AK_MwusBundle] ON [dbo].[MwusBundle]
    (
	   [RevisionID]
	,  [BundledUpdateGuid]
	,  [BundledRevisionNumber] DESC
	,  [ClauseGuid]
	)
go
/* 
 * INDEX: [MwusFile_FileDigest_AK] 
 */

CREATE UNIQUE INDEX [MwusFile_FileDigest_AK] ON [dbo].[MwusFile]([MwusFileGuid])
go
/* 
 * INDEX: [MwusRevision_AK] 
 */

CREATE UNIQUE CLUSTERED INDEX [MwusRevision_AK] ON [dbo].[MwusRevision]([UpdateGuid], [RevisionNumber] DESC)
go
/* 
 * TABLE: [dbo].[MwusBundle] 
 */

ALTER TABLE [dbo].[MwusBundle] ADD CONSTRAINT [FK_MwusBundle_MwusRevision_RevisionID] 
    FOREIGN KEY ([RevisionID])
    REFERENCES [dbo].[MwusRevision]([RevisionID])
go

/* 
 * TABLE: [dbo].[MwusDeployment] 
 */

ALTER TABLE [dbo].[MwusDeployment] ADD CONSTRAINT [FK_MwusDeployment_MwusRevision] 
    FOREIGN KEY ([RevisionID])
    REFERENCES [dbo].[MwusRevision]([RevisionID])
go

ALTER TABLE [dbo].[MwusDeployment] ADD CONSTRAINT [FK_MwusDeployment_MwusTargetGroup] 
    FOREIGN KEY ([TargetGroupGuid])
    REFERENCES [dbo].[MwusTargetGroup]([TargetGroupGuid])
go


/* 
 * TABLE: [dbo].[MwusDevice] 
 */

ALTER TABLE [dbo].[MwusDevice] ADD CONSTRAINT [FK_MwusDevice_MwusTargetGroup] 
    FOREIGN KEY ([TargetGroupGuid])
    REFERENCES [dbo].[MwusTargetGroup]([TargetGroupGuid])
go


/* 
 * TABLE: [dbo].[MwusFileForRevision] 
 */

ALTER TABLE [dbo].[MwusFileForRevision] ADD CONSTRAINT [FK_MwusFileForRevision_MwusFile] 
    FOREIGN KEY ([MwusFileGuid])
    REFERENCES [dbo].[MwusFile]([MwusFileGuid])
go

ALTER TABLE [dbo].[MwusFileForRevision] ADD CONSTRAINT [FK_MwusFileForRevision_MwusRevision] 
    FOREIGN KEY ([RevisionID])
    REFERENCES [dbo].[MwusRevision]([RevisionID])
go


/* 
 * TABLE: [dbo].[MwusMetadata] 
 */

ALTER TABLE [dbo].[MwusMetadata] ADD CONSTRAINT [FK_MwusMetadata_MwusRevision] 
    FOREIGN KEY ([RevisionID])
    REFERENCES [dbo].[MwusRevision]([RevisionID])
go


/* 
 * TABLE: [dbo].[MwusPrerequisite] 
 */

ALTER TABLE [dbo].[MwusPrerequisite] ADD CONSTRAINT [FK_MwusPrerequisite_MwusRevision] 
    FOREIGN KEY ([RevisionID])
    REFERENCES [dbo].[MwusRevision]([RevisionID])
go


/* 
 * TABLE: [dbo].[MwusUpdateCategoryLink] 
 */

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

/*
   TABLE [dbo].[MwusDevicePatchStatus](
*/

CREATE TABLE [dbo].[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(getutcdate())
,
 CONSTRAINT [PK_MwusDevicePatchStatus] PRIMARY KEY CLUSTERED 
(
	[DeviceGuid] ASC,
	[UpdateGuid] ASC,
	[RevisionNumber] ASC,
	[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/*
	table [MwusConfiguration]
*/
CREATE TABLE [dbo].[MwusConfiguration](
	[Key]			[nvarchar](32) NOT NULL,
	[Value]			[nvarchar](max) NULL,
	 CONSTRAINT [PK_MwusConfiguration] PRIMARY KEY CLUSTERED 
	(
		[Key] ASC
	)
) 
go


/*
TABLE [dbo].[MwusCategory]
*/
CREATE TABLE [dbo].[MwusCategory](
	[MwusCategoryGuid] [uniqueidentifier] NOT NULL CONSTRAINT [PK_MwusCategory]      PRIMARY KEY CLUSTERED ,
	[Name]             [nvarchar](255)    NOT NULL CONSTRAINT [AK_MwusCategory_Name] UNIQUE 
) 

GO

CREATE TABLE [dbo].[MwusClassification](
	[MwusClassificationGuid] [uniqueidentifier] NOT NULL CONSTRAINT [PK_MwusClassification]     PRIMARY KEY CLUSTERED,
	[Name]                   [nvarchar](255)    NOT NULL CONSTRAINT [AK_MwusClassification_Name] unique
) 

GO
/*
	PostMWData_Branch_GeneratedTables.sql END]
*/


/*
ALTER OLD TABLES         from PostMWData_TRUNK.sql

*/
alter table PatchManagedDevice
add LastReportedStatusTime datetime null
go

alter table PatchManagedDevice
add LastSyncTime datetime null
go


alter table PatchAgentConfigurations
add TargetGroupGuid uniqueidentifier
	constraint PatchAgentConfigurations_TargetGroupGuid_DF default(newid()) not null
go

update PatchAgentConfigurations
set TargetGroupGuid = '00000000-9C41-4E17-7381-000000000000'
where TargetGroupName = 'Unassigned Computers'
go





if object_id('MwusRevisionLanguage') is not null
   drop table MwusRevisionLanguage
go

CREATE TABLE [dbo].[MwusRevisionLanguage](
	[RevisionID] [int] NOT NULL,
	[LanguageID] [int] NOT NULL,
 CONSTRAINT [PK_MwusRevisionLanguage] PRIMARY KEY CLUSTERED 
(
	[RevisionID] ASC,
	[LanguageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

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

ALTER TABLE [dbo].[MwusRevisionLanguage] CHECK CONSTRAINT [FK_MwusRevisionLanguage_MwusLanguage]
GO

ALTER TABLE [dbo].[MwusRevisionLanguage]  WITH CHECK ADD  CONSTRAINT [FK_MwusRevisionLanguage_MwusRevision] FOREIGN KEY([RevisionID])
REFERENCES [dbo].[MwusRevision] ([RevisionID])
GO

ALTER TABLE [dbo].[MwusRevisionLanguage] CHECK CONSTRAINT [FK_MwusRevisionLanguage_MwusRevision]
GO
