
CREATE TABLE [Cert](
	 [Feature]			nvarchar(100) not null
	,[CertName]			nvarchar(255) NOT NULL
	,[Thumbprint]		nvarchar(100) not null
	,[CertBinary]		image not null
	,[DateModified]		datetime not null 
)
GO

alter table [Cert]
   add constraint [PK_Cert] primary key 												 
	( [Feature], [CertName])
go

/*
 * ER/Studio Data Architect 9.7 SQL Code Generation
 * Company :      LPI
 * Project :      MWData_TRUNK_Express.dm1
 * Author :       LPI
 *
 * Date Created : Friday, May 30, 2014 14:00:25
 * Target DBMS : Microsoft SQL Server 2008
 */

/* 
 * TABLE: [AvastAgentAction] 
 */

CREATE TABLE [AvastAgentAction](
    [ActionID]       int               NOT NULL,
    [DeviceID]       int               NOT NULL,
	[ServiceID]      INT               NOT NULL,
    [Status]         int               NOT NULL,
    [Command]        nvarchar(128)     NOT NULL,
    [CmdParameters]  nvarchar(2000)    NULL,
    [CmdCreated]     datetime          NOT NULL,
    [CmdStarted]     datetime          NULL,
    [CmdCompleted]   datetime          NULL,
    [ResultCode]     int               NULL,
    [ResultMessage]  nvarchar(2000)    NULL,
    [Retries]        int               NOT NULL,
	[ParentActionID] INT               NULL,
    CONSTRAINT [AvastAgentAction_PK] PRIMARY KEY  ([ActionID]),
    CONSTRAINT [AvastAgentAction_AK_DeviceID_ActionID_CLUST]  UNIQUE  ([DeviceID], [ActionID])
)
go

/* 
 * TABLE: [AvastAgent] 
 */

CREATE TABLE [AvastAgent](
    [DeviceID]      int                 NOT NULL,
    [DeviceGuid]    uniqueidentifier    NOT NULL,
	[Version]       nvarchar(32)        NOT NULL,
    [State]         int                 NOT NULL,
    [ReasonCode]    int                 NOT NULL,
    [LastContact]   datetime            NULL,
	[IsPolicyAssignedInAfb] [bit]       NOT NULL CONSTRAINT [DF_AvastAgent_IsPolicyAssignedInAfb] DEFAULT ((0)),
	[RequiresSeeding] [bit]             NOT NULL,
    CONSTRAINT [AvastAgent_PK_DeviceID]          PRIMARY KEY  ([DeviceID]),
    CONSTRAINT [AvastAgent_AK_DeviceGuid_CLUST]  UNIQUE       ([DeviceGuid])
)
go

/* 
 * TABLE: [AvastAgentPolicy] 
 */

CREATE TABLE [AvastAgentPolicy](
    [PolicyID]             int                 NOT NULL,
    [Configuration]        ntext               NOT NULL,
    CONSTRAINT [AvastAgentPolicy_PK] PRIMARY KEY  ([PolicyID])
)
go

/* 
 * TABLE: [AvastAgentService] 
 */

CREATE TABLE [AvastAgentService](
	[DeviceID] [int] NOT NULL,
	[ServiceID] [int] NOT NULL,
	[UpgradeActionID] [int] NULL,
	[Enabled] [bit] NOT NULL,
	[RemoveCompetitiveProducts] [bit] NULL,
	[StatusID] [INT] NOT NULL,
	[StateForServiceCenter] [INT] NOT NULL,
	[ReasonCode] [INT] NOT NULL,
	[InstallBeta] [bit] NULL,
	[InstallationToken] [nvarchar](255) NULL,
	[ManagementUrl] [nvarchar](255) NULL,
	[DateCreated] [datetime] NOT NULL,
	CONSTRAINT [AvastAgentService_DeviceID_ServiceID_PK] PRIMARY KEY  ([DeviceID], [ServiceID])
)
GO

/* 
 * TABLE: [AvastAgentSubserviceVersion] 
 */

CREATE TABLE [AvastAgentSubserviceVersion](
    [DeviceGuid]          uniqueidentifier    NOT NULL,
    [OMRowVersion]        timestamp           NOT NULL,
    [DateCreated]         datetime            NOT NULL,
    [DateModified]        datetime            NOT NULL,
    [SubserviceItemGuid]  uniqueidentifier    NOT NULL,
    [subservice_id]       int                 NOT NULL,
    [major]               int                 NOT NULL,
    [minor]               int                 NULL,
    [micro]               int                 NULL,
    [build]               int                 NULL,
    [incremental]         int                 NULL,
    [version_date]        nvarchar(100)       NULL,
    [out_of_date]         bit                 NULL,
    CONSTRAINT [AvastAgentSubserviceVersion_AK_SubserviceItemGuid] PRIMARY KEY  ([SubserviceItemGuid]),
    CONSTRAINT [AvastAgentSubserviceVersion_subservice_id_CLUST]   UNIQUE  ([DeviceGuid], [subservice_id])
)
go


/* 
 * TABLE: [AvastAgentDevice] 
 */

CREATE TABLE [AvastAgentDevice](
    [device_id]                 int              NOT NULL,
    [account_id]                int              NOT NULL,
    [policy_id]                 int              NOT NULL,
    [device_type_id]            int              NULL,
    [operating_system_id]       int              NULL,
    [device_status_id]          tinyint          NOT NULL,
    [name]                      nvarchar(255)    NOT NULL,
    [description]               nvarchar(255)    NULL,
    [comment]                   nvarchar(255)    NULL,
    [ccl_id]                    nvarchar(255)    NULL,
    [token]                     nvarchar(100)    NULL,
    [time_zone_id]              int              NULL,
    [manufacturer]              nvarchar(255)    NULL,
    [domain]                    nvarchar(80)     NULL,
    [wan_ip]                    nvarchar(46)     NULL,
    [device_group_id]           int              NULL,
    [is_master]                 int              NULL,
    [allow_remote_connection]   int              NULL,
    [device_detection_enabled]  int              NULL,
    [install_invite_id]         int              NULL,
    CONSTRAINT [AvastAgentDevice_PK] PRIMARY KEY  ([device_id])
)
go

/* 
 * TABLE: [AvastAgentNic] 
 */

CREATE TABLE [AvastAgentNic](
    [id]         int             NOT NULL,
    [device_id]  int             NOT NULL,
    [mac]        nvarchar(12)    NULL,
    [ipv4]       nvarchar(16)    NULL,
    [ipv6]       nvarchar(46)    NULL,
    CONSTRAINT [AvastAgentNic_PK] PRIMARY KEY  ([device_id], [id]),
    CONSTRAINT [AvastAgentNic_AK_id]  UNIQUE ([id])
)
go


/* 
 * TABLE: [AvastAgentServiceConfiguration] 
 */

CREATE TABLE [AvastAgentServiceConfiguration](
    [id]           int              NOT NULL,
    [service_id]   int              NOT NULL,
    [key]          nvarchar(255)    NOT NULL,
    [value]        nvarchar(255)    NOT NULL,
    [is_url]       bit              NULL,
    [row_version]  timestamp        NOT NULL,
    CONSTRAINT [AvastAgentServiceConfiguration_PK] PRIMARY KEY  ([id]),
    CONSTRAINT [AvastAgentServiceConfiguration_AK_service_id_key]  UNIQUE ([service_id], [key])
)
go


/* 
 * TABLE: [AvastAgentAction] 
 */

ALTER TABLE [AvastAgentAction] ADD CONSTRAINT [AvastAgentAction_AvastAgent_FK] 
    FOREIGN KEY ([DeviceID])
    REFERENCES [AvastAgent]([DeviceID])
go


/* 
 * TABLE: [AvastAgent] 
 */

ALTER TABLE [AvastAgent] ADD CONSTRAINT [AvastAgent_Device_FK] 
    FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID])
go




/* 
 * TABLE: [AvastAgentService] 
 */

ALTER TABLE [AvastAgentService] ADD CONSTRAINT [FK_AvastAgentService_devices] 
    FOREIGN KEY (DeviceID)
    REFERENCES [AvastAgent]([DeviceID])
go


/* 
 * TABLE: [AvastAgentSubserviceVersion] 
 */

ALTER TABLE [AvastAgentSubserviceVersion] ADD CONSTRAINT [AvastAgentSubserviceVersion_AvastAgent_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [AvastAgent]([DeviceGuid])
go


/* 
 * TABLE: [AvastAgentDevice] 
 */

ALTER TABLE [AvastAgentDevice] ADD CONSTRAINT [AvastAgentDevice_AvastAgent_FK] 
    FOREIGN KEY ([device_id])
    REFERENCES [AvastAgent]([DeviceID])
go

ALTER TABLE [AvastAgentDevice] ADD CONSTRAINT [AvastAgentDevice_AvastAgentPolicy_FK] 
    FOREIGN KEY ([policy_id])
    REFERENCES [AvastAgentPolicy]([PolicyID])
go

/* 
 * TABLE: [AvastAgentNic] 
 */

ALTER TABLE [AvastAgentNic] ADD CONSTRAINT [FK_AvastAgentNic_devices] 
    FOREIGN KEY ([device_id])
    REFERENCES [AvastAgentDevice]([device_id])
go

/* 
 * TABLE: [AV_VirusChest] 
 */

CREATE TABLE [AV_VirusChest](
    [DeviceGuid]          uniqueidentifier    NOT NULL,
    [OMRowVersion]        timestamp           NOT NULL,
    [DateCreated]         datetime            NOT NULL,
    [DateModified]        datetime            NOT NULL,
    [VirusChestItemGuid]  uniqueidentifier    NOT NULL,
    [Id]                  nvarchar(100)       NOT NULL,
    [FileName]            nvarchar(1000)      NOT NULL,
    [Location]            nvarchar(1000)      NOT NULL,
    [VirusName]           nvarchar(1000)      NOT NULL,
    [AddedTime]           nvarchar(100)       NOT NULL,
    [Status]              nvarchar(100)       NOT NULL,
    [StatusChangedTime]   nvarchar(100)       NOT NULL,
    CONSTRAINT [AV_VirusChest_AK_SyncGuid]                       PRIMARY KEY  ([VirusChestItemGuid]),
    CONSTRAINT [AV_VirusChest_AK_DeviceGuid_Id_CLUST]            UNIQUE  ([DeviceGuid], [Id])
)
go

/* 
 * TABLE: [AvastAgentAlertEventType] 
 */

CREATE TABLE [AvastAgentAlertEventType](
    [DeviceGuid]            uniqueidentifier    NOT NULL,
    [event_type_id]         int                 NOT NULL,
    [name]                  nvarchar(100)       NOT NULL,
    [enabled]               bit                 NOT NULL,
    [suppression_duration]  int                 NOT NULL,
    [threshold]             int                 NOT NULL,
    [threshold_unit_id]     int                 NOT NULL,
    [time_window]           int                 NOT NULL,
    [details]               nvarchar(100)       NULL,
    CONSTRAINT [AvastAgentAlertEventType_AK_DeviceGuid_event_type_id_CLUST] PRIMARY KEY ([DeviceGuid], [event_type_id])
)
go

 ALTER TABLE [AvastAgentAlertEventType]
    add
		CONSTRAINT [AvastAgentAlertEventType_Device_FK] FOREIGN KEY ([DeviceGuid]) 
			REFERENCES [Device] ([DeviceGuid])
go	
