ALTER TABLE [ManagedElementType]
ADD [IsForSite] BIT NOT NULL constraint ManagedElementType_IsForSite_DF DEFAULT(0)
GO

CREATE TABLE [dbo].[Security_Firewall](
	[FirewallAssetGuid]			[uniqueidentifier] NOT NULL,
	[DeviceGuid]				[uniqueidentifier] NOT NULL,
	[Name]						[nvarchar](200) NOT NULL,
	[Version]					[nvarchar](100) NULL,
	[Vendor]					[nvarchar](200) NULL,
	[InstallDate]				[datetime] NULL,
	[FirewallPolicies]			nvarchar(4000) null,
	[FirewallRules]				nvarchar(4000) null,
	[DateCreated]				[datetime] NOT NULL,
	[DateModified]				[datetime] NOT NULL,
	[OMRowVersion]				[timestamp]	 NOT NULL
	CONSTRAINT [Security_Firewall_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
) ;
GO

CREATE CLUSTERED INDEX [Security_Firewall_DeviceGuid_CLUST_IX]
    ON [dbo].[Security_Firewall]([DeviceGuid] ASC);
go

create unique nonclustered index Security_Firewal_FirewallAssetGuid_i_DeviceGuid_OMRowVersion_UX 
		on dbo.Security_Firewall(FirewallAssetGuid)  include(DeviceGuid, OMRowVersion)	
go

CREATE TABLE [dbo].[Security_AvInfo]
(
		AvInfoGuid          uniqueidentifier  NOT NULL,
		DeviceGuid          uniqueidentifier  NOT NULL,
		DisplayName         nvarchar(200)     NOT NULL,
		ProductState        nvarchar(200)     NOT NULL,
		Enabled             bit               NOT NULL,
		UptoDate            bit               NOT NULL,
		DateCreated         datetime          NOT NULL,
		DateModified        datetime          NOT NULL,
		OMRowVersion        timestamp         NOT NULL,
		CONSTRAINT [Security_AvInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid])
);
GO

CREATE CLUSTERED INDEX [Security_AvInfo_DeviceGuid_CLUST_IX]
    ON [dbo].[Security_AvInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_AvInfo_AvInfo_i_DeviceGuid_OMRowVersion_UX  
		on dbo.Security_AvInfo(AvInfoGuid)  include(DeviceGuid, OMRowVersion)	
go

CREATE TABLE [Security_LocalUserInfo](
	[UserInfoGuid]			[uniqueidentifier]	NOT NULL,
	[DeviceGuid]			[uniqueidentifier]	NOT NULL,
	[LogOnName]				[nvarchar](200)		NOT NULL,	
	[SID]                   [nvarchar](50)      NOT NULL,
	[AccountEnabled]          [bit] NOT NULL,
	[AccountCreatedTime]      [datetime] NULL,
	[AccountExpirationDate]   [datetime] NULL,
	[CannotChangePassword]   [bit] NOT NULL,
	[IsPasswordNotRequired]  [bit] NOT NULL,
	[IsPasswordNeverExpired] [bit] NOT NULL,	
	[LastPwdChangeTime]     [datetime] NULL,
	[DateCreated]			[datetime]			NOT NULL,
	[DateModified]			[datetime]			NOT NULL,
	[OMRowVersion]			[timestamp]		NOT NULL,
	CONSTRAINT [Security_userInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid])
) ;
GO

CREATE CLUSTERED INDEX [Security_LocalUserInfo_DeviceGuid_CLUST_IX]
    ON [dbo].[Security_LocalUserInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_LocalUserInfo_UserInfoGuid_i_DeviceGuid_OMRowVersion_UX 
		on dbo.Security_LocalUserInfo(UserInfoGuid)  include(DeviceGuid, OMRowVersion)	
go

CREATE TABLE [Security_DomainUserInfo](
	[UserInfoGuid]			[uniqueidentifier]	NOT NULL,
	[CustomerGuid]			[uniqueidentifier]	NOT NULL,
	[LogOnName]				[nvarchar](200)		NOT NULL,	
	[DistinguishedName]     [nvarchar](1000)      NULL,
	[SID]                   [nvarchar](50)      NOT NULL,
	[AccountEnabled]          [bit] NOT NULL,
	[AccountCreatedTime]      [datetime] NULL,
	[AccountExpirationDate]   [datetime] NULL,
	[CannotChangePassword]   [bit] NOT NULL,
	[IsPasswordNotRequired]  [bit] NOT NULL,
	[IsPasswordNeverExpired] [bit] NOT NULL,
	[AllowReversiblePasswordEncryption] [bit] NOT NULL,
	[LastPwdChangeTime]     [datetime] NULL,
	[DateCreated]			[datetime]			NOT NULL,
	[DateModified]			[datetime]			NOT NULL,
	[OMRowVersion]			[timestamp]		NOT NULL	
) ;
GO

CREATE CLUSTERED INDEX [Security_DomainUserInfo_CustomerGuid_CLUST_IX]
    ON [dbo].[Security_DomainUserInfo]([CustomerGuid] ASC);
go

create unique nonclustered index Security_DomainUserInfo_UserInfoGuid_i_CustomerGuid_OMRowVersion_UX 
		on dbo.Security_DomainUserInfo(UserInfoGuid)  include(CustomerGuid, OMRowVersion)	
go

CREATE TABLE [Security_DomainGroupPolicyInfo](
	[PolicyInfoGuid]		[uniqueidentifier]	NOT NULL,
	[CustomerGuid]			[uniqueidentifier]	NOT NULL,
	[DisplayName]			[nvarchar](200)		NOT NULL,
	[DomainName]			[nvarchar](200)		NOT NULL,
	[Path]					[nvarchar](1000)    NOT NULL,
	[ID]					[uniqueidentifier]	NOT NULL,
	[PolicyLastModifiedTime] [datetime]		NULL,
	[PasswordHistorySize]	[int] NULL,
	[MaxPasswordAge]        [int] NULL,
	[PasswordComplexity]	[bit] NULL,
	[StorePasswordWithReversibleEncryption] [bit] NULL,
	[LockoutBadCount]	    [int] NULL,
	[DateCreated]			[datetime]	NOT NULL,
	[DateModified]			[datetime]	NOT NULL,
	[OMRowVersion]			[timestamp]	NOT NULL	
) ;
GO

CREATE nonCLUSTERED INDEX [Security_DomainGroupPolicyInfo_CustomerGuid_IX]
    ON [Security_DomainGroupPolicyInfo]([CustomerGuid] ASC);
go

create unique nonclustered index Security_DomainGroupPolicyInfo_PolicyInfoGuid_UX 
		on Security_DomainGroupPolicyInfo(PolicyInfoGuid)  	
go

CREATE TABLE [Security_LocalSecurityPolicyInfo](
	[PolicyInfoGuid]		[uniqueidentifier]	NOT NULL,
	[DeviceGuid]			[uniqueidentifier]	NOT NULL,
	[PasswordHistorySize]	[int] NULL,
	[MaxPasswordAge]        [int] NULL,
	[PasswordComplexity]	[bit] NULL,
	[StorePasswordWithReversibleEncryption] [bit] NULL,
	[LockoutBadCount]	    [int] NULL,
	[DateCreated]			[datetime]	NOT NULL,
	[DateModified]			[datetime]	NOT NULL,
	[OMRowVersion]			[timestamp]	NOT NULL	
) ;
GO

alter table [Security_LocalSecurityPolicyInfo]
	add CONSTRAINT [Security_LocalSecurityPolicyInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_LocalSecurityPolicyInfo_DeviceGuid_IX]
    ON [Security_LocalSecurityPolicyInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_LocalSecurityPolicyInfo_PolicyInfoGuid_UX 
		on [Security_LocalSecurityPolicyInfo](PolicyInfoGuid)  
go

CREATE TABLE [dbo].[Security_AntiSpywareInfo]
(
		AntiSpywareInfoGuid          uniqueidentifier  NOT NULL,
		DeviceGuid          uniqueidentifier  NOT NULL,
		DisplayName         nvarchar(200)     NOT NULL,
		ProductState        nvarchar(200)     NOT NULL,
		Enabled             bit               NOT NULL,
		UptoDate            bit               NOT NULL,
		DateCreated         datetime          NOT NULL,
		DateModified        datetime          NOT NULL,
		OMRowVersion        timestamp         NOT NULL,
		CONSTRAINT [Security_AntiSpywareInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid])
);
GO

CREATE CLUSTERED INDEX [Security_AntiSpywareInfo_DeviceGuid_CLUST_IX]
    ON [dbo].[Security_AntiSpywareInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_AntiSpywareInfo_AntiSpywareInfoGuid_i_DeviceGuid_OMRowVersion_UX 
		on [dbo].[Security_AntiSpywareInfo](AntiSpywareInfoGuid)  include(DeviceGuid, OMRowVersion)	
go

CREATE TABLE [dbo].[Security_MissingPatchInfo](
	[PatchInfoGuid]			       uniqueidentifier	NOT NULL,
	[DeviceGuid]			       uniqueidentifier	NOT NULL,
	[UpdateID]			           uniqueidentifier	NOT NULL,   	
	[DateCreated]			       datetime			NOT NULL,
	[DateModified]			       datetime			NOT NULL,
	[OMRowVersion]			       timestamp		NOT NULL	
) ;
GO

alter table [Security_MissingPatchInfo]
	add CONSTRAINT [Security_MissingPatchInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_MissingPatchInfo_DeviceGuid_IX]
    ON [Security_MissingPatchInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_MissingPatchInfo_PatchInfoGuid_UX 
		on [Security_MissingPatchInfo](PatchInfoGuid)  
go

CREATE TABLE [dbo].[Security_WUAConfig](
	[WUAConfigGuid]			       uniqueidentifier	NOT NULL,
	[DeviceGuid]			       uniqueidentifier	NOT NULL,
    [IsAutoUpdateEnabled]          bit              NOT NULL,
	[DateCreated]			       datetime			NOT NULL,
	[DateModified]			       datetime			NOT NULL,
	[OMRowVersion]			       timestamp		NOT NULL	
) ;
GO

alter table [Security_WUAConfig]
	add CONSTRAINT [Security_WUAConfig_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_WUAConfig_DeviceGuid_IX]
    ON [Security_WUAConfig]([DeviceGuid] ASC);
go

create unique nonclustered index [Security_WUAConfig_WUAConfigGuid_UX]
		ON [Security_WUAConfig] (WUAConfigGuid)
go

CREATE TABLE [dbo].[Security_DeviceDataCollectionStatus]
(
	[DeviceDataCollectionStatusGuid]			       uniqueidentifier	NOT NULL,
	[DeviceGuid]			       uniqueidentifier	NOT NULL,
    [ManagedElementTypeGuid]       uniqueidentifier NOT NULL,
	[Status]                       int              NOT NULL,
	[Reason]                       int              NULL,
	[StatusMessage]                nvarchar(500)    NULL,
	[DateCreated]			       datetime			NOT NULL,
	[DateModified]			       datetime			NOT NULL,
	[OMRowVersion]			       timestamp		NOT NULL	
) ;
go

alter table [Security_DeviceDataCollectionStatus]
   add constraint [Security_DeviceDataCollectionStatus_FK_DeviceGuid] foreign key ([DeviceGuid]) references [Device] ([DeviceGuid])
go

alter table [Security_DeviceDataCollectionStatus]
   add constraint [Security_DeviceDataCollectionStatus_FK_METypeGuid] foreign key ([ManagedElementTypeGuid]) references [ManagedElementType] ([ManagedElementTypeGuid])
go

create clustered index [Security_DeviceDataCollectionStatus_CLUST_IX]
    on [dbo].[Security_DeviceDataCollectionStatus]( DeviceGuid, ManagedElementTypeGuid  ASC)
go

create unique nonclustered index Security_DeviceDataCollectionStatus_StatusGuid_i_DeviceGuid_OMRowVersion_UX 
		on  Security_DeviceDataCollectionStatus([DeviceDataCollectionStatusGuid])  include(DeviceGuid, OMRowVersion)	
go

CREATE TABLE [dbo].[Security_SiteDataCollectionStatus]
(
	[SiteDataCollectionStatusGuid]			       uniqueidentifier	NOT NULL,
	[CustomerGuid]			       uniqueidentifier	NOT NULL,
    [ManagedElementTypeGuid]       uniqueidentifier NOT NULL,
	[Status]                       int              NOT NULL,
	[Reason]                       int              NULL,
	[StatusMessage]                nvarchar(500)    NULL,
	[DateCreated]			       datetime			NOT NULL,
	[DateModified]			       datetime			NOT NULL,
	[OMRowVersion]			       timestamp		NOT NULL	
) ;
go

alter table [Security_SiteDataCollectionStatus]
   add constraint [Security_SiteDataCollectionStatus_FK_METypeGuid] foreign key ([ManagedElementTypeGuid]) references [ManagedElementType] ([ManagedElementTypeGuid])
go

create clustered index [Security_SiteDataCollectionStatus_CLUST_IX]
    on [dbo].[Security_SiteDataCollectionStatus]( CustomerGuid, ManagedElementTypeGuid  ASC)
go

create unique nonclustered index Security_SiteDataCollectionStatus_StatusGuid_i_DeviceGuid_OMRowVersion_UX 
		on  Security_SiteDataCollectionStatus([SiteDataCollectionStatusGuid])  include(CustomerGuid, OMRowVersion)	
go

