
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: [AV_Action] 
 */

CREATE TABLE [AV_Action](
    [ActionID]       int               NOT NULL,
    [DeviceID]       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,
    CONSTRAINT [AV_Action_PK] PRIMARY KEY  ([ActionID]),
    CONSTRAINT [AV_Action_AK_DeviceID_ActionID_CLUST]  UNIQUE  ([DeviceID], [ActionID])
)
go



/* 
 * TABLE: [AV_CloudCareAgent] 
 */

CREATE TABLE [AV_CloudCareAgent](
    [DeviceID]      int                 NOT NULL,
    [DeviceGuid]    uniqueidentifier    NOT NULL,
	[Version]       nvarchar(32)        NOT NULL,
    [State]         int                 NOT NULL,
    [Status]        int                 NOT NULL,
    [LastContact]   datetime            NULL,
    CONSTRAINT [AV_CloudCareAgent_PK_DeviceID]          PRIMARY KEY  ([DeviceID]),
    CONSTRAINT [AV_CloudCareAgent_AK_DeviceGuid_CLUST]  UNIQUE       ([DeviceGuid])
)
go



/* 
 * TABLE: [AV_Policy] 
 */

CREATE TABLE [AV_Policy](
    [PolicyID]             int                 NOT NULL,
    [InstalledComponents]  bigint              NOT NULL,
    [Configuration]        ntext                 NOT NULL,
    [PolicyVersionGuid]    uniqueidentifier    NOT NULL,
    CONSTRAINT [AV_Policy_PK] PRIMARY KEY  ([PolicyID])
)
go





/* 
 * TABLE: [AV_VirusVault] 
 */

CREATE TABLE [AV_VirusVault](
    [DeviceGuid]          uniqueidentifier    NOT NULL,
    [OMRowVersion]        timestamp           NOT NULL,
    [DateCreated]         datetime            NOT NULL,
    [DateModified]        datetime            NOT NULL,
    [VirusVaultItemGuid]  uniqueidentifier    NOT NULL,
    [VirusVaultId]        int                 NOT NULL,
    [ThreatDescription]   nvarchar(1000)      NULL,
    [ObjectName]          nvarchar(1000)      NOT NULL,
    [OrigObjectName]      nvarchar(1000)      NULL,
    [ThreatObjectTypeId]  int                 NULL,
    [ThreatSeverityId]    int                 NULL,
    [ThreatState]         bigint              NULL,
    [ThreatTypeId]        int                 NULL,
    [DetectionTime]       nvarchar(1000)      NULL,
    [ProcessId]           int                 NULL,
    [ThreatSourceId]      int                 NULL,
    CONSTRAINT [AV_VirusVault_AK_SyncGuid]                       PRIMARY KEY  ([VirusVaultItemGuid]),
    CONSTRAINT [AV_VirusVault_AK_DeviceGuid_VirusVaultId_CLUST]  UNIQUE  ([DeviceGuid], [VirusVaultId])
)
go

ALTER TABLE [AV_VirusVault]
	add
		CONSTRAINT [AV_VirusVault_AV_CloudCareAgent_FK] FOREIGN KEY ([DeviceGuid])
			REFERENCES [AV_CloudCareAgent] ([DeviceGuid])
go

/* 
 * TABLE: [AVCC_device_services] 
 */

CREATE TABLE [AVCC_device_services](
 DeviceGuid		uniqueidentifier	not null
,OMRowVersion	rowversion			not null
,DateCreated	datetime			not null
,DateModified	datetime			not null
,SyncGuid		uniqueidentifier	not null constraint AVCC_device_services_SyncGuid_UC UNIQUE
,

   -- [id]                           int    NOT NULL IDENTITY(1,1),
   -- [device_id]                    int    NOT NULL,
    [service_id]                   int    NOT NULL,
    [upgrade_action_id]            int    NULL,
    [enabled]                      bit    NULL,
    [include_firewall]             bit    NULL,
    [include_mail_protection]      bit    NULL,
    [include_mail_addin]           bit    NULL,
    [include_id_protection]        bit    NULL,
    [include_web_protection]       bit    NULL,
    [remove_competitive_products]  bit    NULL,
    [device_service_status_id]     int    NULL,
    [install_beta]                 bit    NULL,
    CONSTRAINT [AVCC_device_services_PK] PRIMARY KEY  (DeviceGuid, service_id)
)
go



/* 
 * TABLE: [AVCC_device_subservice_versions] 
 */

CREATE TABLE [AVCC_device_subservice_versions](
    [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 [AVCC_device_subservice_versions_AK_SubserviceItemGuid] PRIMARY KEY  ([SubserviceItemGuid]),
    CONSTRAINT [AVCC_device_subservice_versions_subservice_id_CLUST]   UNIQUE  ([DeviceGuid], [subservice_id])
)
go



/* 
 * TABLE: [AVCC_devices] 
 */

CREATE TABLE [AVCC_devices](
    [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 [AVCC_devices_PK] PRIMARY KEY  ([device_id])
)
go



/* 
 * TABLE: [AVCC_nics] 
 */

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



/* 
 * TABLE: [AVCC_service_configurations] 
 */

CREATE TABLE [AVCC_service_configurations](
    [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 [AVCC_service_configurations_PK] PRIMARY KEY  ([id]),
    CONSTRAINT [AK_AVCC_service_configurations_AK_service_id_key]  UNIQUE ([service_id], [key])
)
go



/* 
 * TABLE: [AVCC_time_servers] 
 */

CREATE TABLE [AVCC_time_servers](
    [id]           int              NOT NULL,
    [url]          nvarchar(255)    NOT NULL,
    [row_version]  timestamp        NOT NULL,
    CONSTRAINT [AVCC_time_servers_PK] PRIMARY KEY  ([id])
)
go



/* 
 * TABLE: [AVCC_time_zones] 
 */

CREATE TABLE [AVCC_time_zones](
    [id]                  int              NOT NULL,
    [name]                nvarchar(255)    NOT NULL,
    [bias]                int              NULL,
    [standard_bias]       int              NULL,
    [daylight_bias]       int              NULL,
    [standard_date_time]  datetime         NULL,
    [daylight_date_time]  datetime         NULL,
    [row_version]         timestamp        NOT NULL,
    CONSTRAINT [AVCC_time_zones_PK] PRIMARY KEY  ([id])
)
go





/* 
 * TABLE: [AV_Action] 
 */

ALTER TABLE [AV_Action] ADD CONSTRAINT [AV_Action_AV_CloudCareAgent_FK] 
    FOREIGN KEY ([DeviceID])
    REFERENCES [AV_CloudCareAgent]([DeviceID])
go


/* 
 * TABLE: [AV_CloudCareAgent] 
 */

ALTER TABLE [AV_CloudCareAgent] ADD CONSTRAINT [AV_CloudCareAgent_Device_FK] 
    FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID])
go




/* 
 * TABLE: [AVCC_device_services] 
 */

ALTER TABLE [AVCC_device_services] ADD CONSTRAINT [FK_device_services_devices] 
    FOREIGN KEY (DeviceGuid)
    REFERENCES [AV_CloudCareAgent]([DeviceGuid])
go


/* 
 * TABLE: [AVCC_device_subservice_versions] 
 */

ALTER TABLE [AVCC_device_subservice_versions] ADD CONSTRAINT [AVCC_device_subservice_versions_AV_CloudCareAgent_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [AV_CloudCareAgent]([DeviceGuid])
go


/* 
 * TABLE: [AVCC_devices] 
 */

ALTER TABLE [AVCC_devices] ADD CONSTRAINT [AVCC_devices_AV_CloudCareAgent_FK] 
    FOREIGN KEY ([device_id])
    REFERENCES [AV_CloudCareAgent]([DeviceID])
go

ALTER TABLE [AVCC_devices] ADD CONSTRAINT [AVCC_devices_AV_Policy_FK] 
    FOREIGN KEY ([policy_id])
    REFERENCES [AV_Policy]([PolicyID])
go

ALTER TABLE [AVCC_devices] ADD CONSTRAINT [AVCC_devices_AVCC_time_zones_FK] 
    FOREIGN KEY ([time_zone_id])
    REFERENCES [AVCC_time_zones]([id])
go


/* 
 * TABLE: [AVCC_nics] 
 */

ALTER TABLE [AVCC_nics] ADD CONSTRAINT [FK_nics_devices] 
    FOREIGN KEY ([device_id])
    REFERENCES [AVCC_devices]([device_id])
go


/* 
 * TABLE: [AVCC_alert_event_types] 
 */

CREATE TABLE [AVCC_alert_event_types](
    [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 [AVCC_alert_event_type_AK_DeviceGuid_event_type_id_CLUST] PRIMARY KEY ([DeviceGuid], [event_type_id])
)
go

 ALTER TABLE [AVCC_alert_event_types]
    add
		CONSTRAINT [AVCC_alert_event_types_Device_FK] FOREIGN KEY ([DeviceGuid]) 
			REFERENCES [Device] ([DeviceGuid])
go	
