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

CREATE TABLE [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	
) ;
GO

alter table [Security_Firewall]
	add CONSTRAINT [Security_Firewall_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_Firewall_DeviceGuid_IX]
    ON [Security_Firewall]([DeviceGuid] ASC);
go

create unique nonclustered index Security_Firewal_FirewallAssetGuid_UX 
		on Security_Firewall(FirewallAssetGuid)  	
go



CREATE TABLE [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
);


GO
alter table [Security_AvInfo]
	add CONSTRAINT [Security_AvInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_AvInfo_DeviceGuid_IX]
    ON [Security_AvInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_AvInfo_AvInfo_i_DeviceGuid_OMRowVersion_UX 
		on Security_AvInfo(AvInfoGuid)  
go


/* Add concreate table for local User Info Asset */
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	
) ;
GO

alter table [Security_LocalUserInfo]
	add CONSTRAINT [Security_LocalUserInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_LocalUserInfo_DeviceGuid_IX]
    ON [Security_LocalUserInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_LocalUserInfo_UserInfoGuid_UX 
		on Security_LocalUserInfo(UserInfoGuid)  	
go


/* Add concreate table for domain User Info Asset */
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 unique nonclustered index Security_DomainUserInfo_UserInfoGuid_UX 
		on Security_DomainUserInfo(UserInfoGuid)  	
go

/* Add concreate table for security domain group policy Info Asset */
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

/*  Add concreate table for device security policy info asset */
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

/* Add concrete table for Antispyware Info Asset*/

CREATE TABLE [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
);


GO
alter table [Security_AntiSpywareInfo]
	add CONSTRAINT [Security_AntiSpywareInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_AntiSpywareInfo_DeviceGuid_IX]
    ON [Security_AntiSpywareInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_AntiSpywareInfo_AntiSpywareInfoGuid_UX 
		on [Security_AntiSpywareInfo](AntiSpywareInfoGuid)  
go


/* Newly added tables for Security Patch features */

/* Table for device missing patch  */
CREATE TABLE [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

/*Table for device Windows Update Configuration  */

CREATE TABLE [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


/*  Table for Device data collection status  */
CREATE TABLE [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 nonClustered index [Security_DeviceDataCollectionStatus_CLUST_IX]
    on [Security_DeviceDataCollectionStatus]( DeviceGuid, ManagedElementTypeGuid  ASC)

go

create unique nonclustered index [Security_DeviceDataCollectionStatus_StatusGuid_i_DeviceGuid_OMRowVersion_UX] 
		on  [Security_DeviceDataCollectionStatus](DeviceDataCollectionStatusGuid)
go

/* Table for Site Data Collection Status */

CREATE TABLE [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 nonClustered index [Security_SiteDataCollectionStatus_CLUST_IX]
    on [Security_SiteDataCollectionStatus]( CustomerGuid, ManagedElementTypeGuid  ASC)

go
create unique nonclustered index [Security_SiteDataCollectionStatus_StatusGuid_i_DeviceGuid_OMRowVersion_UX] 
		on  [Security_SiteDataCollectionStatus](SiteDataCollectionStatusGuid)
go


