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

CREATE TABLE [dbo].[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 NONCLUSTERED ([ActionID]),
    CONSTRAINT [AV_Action_AK_DeviceID_ActionID_CLUST]  UNIQUE CLUSTERED ([DeviceID], [ActionID])
)
go



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

CREATE TABLE [dbo].[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 NONCLUSTERED ([DeviceID]),
    CONSTRAINT [AV_CloudCareAgent_AK_DeviceGuid_CLUST]  UNIQUE CLUSTERED ([DeviceGuid])
)
go



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

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



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

CREATE TABLE [dbo].[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 NONCLUSTERED ([VirusVaultItemGuid]),
    CONSTRAINT [AV_VirusVault_AK_DeviceGuid_VirusVaultId_CLUST]  UNIQUE CLUSTERED ([DeviceGuid], [VirusVaultId])
)
go



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

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



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

CREATE TABLE [dbo].[AVCC_device_services](
    [DeviceGuid]                   uniqueidentifier    NOT NULL,
    [OMRowVersion]                 timestamp           NOT NULL,
    [DateCreated]                  datetime            NOT NULL,
    [DateModified]                 datetime            NOT NULL,
    [SyncGuid]                     uniqueidentifier    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 CLUSTERED ([DeviceGuid], [service_id]),
    CONSTRAINT [AVCC_device_services_SyncGuid_UC]  UNIQUE ([SyncGuid])
)
go



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

CREATE TABLE [dbo].[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 NONCLUSTERED ([SubserviceItemGuid]),
    CONSTRAINT [AVCC_device_subservice_versions_subservice_id_CLUST]  UNIQUE CLUSTERED ([DeviceGuid], [subservice_id])
)
go



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

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



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

CREATE TABLE [dbo].[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 CLUSTERED ([device_id], [id]),
    CONSTRAINT [AVCC_nics_AK_id]  UNIQUE ([id])
)
go



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

CREATE TABLE [dbo].[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 CLUSTERED ([id]),
    CONSTRAINT [AK_AVCC_service_configurations_AK_service_id_key]  UNIQUE ([service_id], [key])
)
go



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

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



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

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



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

CREATE TABLE [dbo].[Cert](
    [Feature]       nvarchar(100)     NOT NULL,
    [CertName]      nvarchar(255)     NOT NULL,
    [Thumbprint]    nvarchar(100)     NOT NULL,
    [CertBinary]    varbinary(max)    NOT NULL,
    [DateModified]  datetime          NOT NULL,
    CONSTRAINT [PK_Cert] PRIMARY KEY CLUSTERED ([Feature], [CertName])
)
go



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

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


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

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


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

ALTER TABLE [dbo].[AV_VirusVault] ADD CONSTRAINT [AV_VirusVault_AV_CloudCareAgent_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [dbo].[AV_CloudCareAgent]([DeviceGuid])
go


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

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


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

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


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

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


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

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

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

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


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

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


