ALTER TABLE [AV_Action] DROP CONSTRAINT [AV_Action_AV_CloudCareAgent_FK];
GO
ALTER TABLE [AVCC_devices] DROP CONSTRAINT [AVCC_devices_AV_CloudCareAgent_FK];
GO
ALTER TABLE [AVCC_devices] DROP CONSTRAINT [AVCC_devices_AV_Policy_FK];
GO
ALTER TABLE [AVCC_nics] DROP CONSTRAINT [FK_nics_devices];
GO
ALTER TABLE [AV_VirusVault] DROP CONSTRAINT [AV_VirusVault_AV_CloudCareAgent_FK];
GO
ALTER TABLE [AVCC_device_services] DROP CONSTRAINT [FK_device_services_devices];
GO
ALTER TABLE [AVCC_device_subservice_versions] DROP CONSTRAINT [AVCC_device_subservice_versions_AV_CloudCareAgent_FK];
GO
ALTER TABLE [AV_CloudCareAgent] DROP CONSTRAINT [AV_CloudCareAgent_Device_FK];
GO
ALTER TABLE [AVCC_alert_event_types] DROP CONSTRAINT [AVCC_alert_event_types_Device_FK];
GO
CREATE TABLE [ASM_IVNT_ApprovalGroup] (
  [ApprovalGroupGuid] uniqueidentifier NOT NULL
, [ApprovalGroupName] nvarchar(255) NULL
);
GO
CREATE TABLE [ASM_IVNT_BulletinApproval] (
  [ApprovalGroupGuid] uniqueidentifier NOT NULL
, [BulletinID] nvarchar(36) NOT NULL
, [Action] tinyint NOT NULL
);
GO
CREATE TABLE [ASM_IVNT_Device] (
  [DeviceID] int NOT NULL
, [PatchToolsVersion] nvarchar(20) NOT NULL
, [PatchToolsState] tinyint NOT NULL
, [LastReportTime] datetime NULL
, [LastReportedStatusTime] datetime NULL
);
GO
CREATE TABLE [ASM_IVNT_DeviceDesiredState] (
  [DeviceID] int NOT NULL
, [ApprovalGroupGuid] uniqueidentifier NOT NULL
, [PatchToolsMode] nvarchar(10) NOT NULL
, [DetectionFrequency] int NOT NULL
, [ExcludedVendorIDs] nvarchar(50) NOT NULL
, [ScheduleType] tinyint NULL
, [ScheduleTime] tinyint NULL
, [Monday] bit NULL
, [Tuesday] bit NULL
, [Wednesday] bit NULL
, [Thursday] bit NULL
, [Friday] bit NULL
, [Saturday] bit NULL
, [Sunday] bit NULL
, [DateOfMonth] tinyint NULL
, [WeekOfMonth] tinyint NULL
, [DayOfWeek] tinyint NULL
);
GO
CREATE TABLE [ASM_IVNT_DeviceUrlUpdateHistory] (
  [DeviceID] int NOT NULL
, [UrlUpdateStatus] bit DEFAULT (0) NULL
, [LastUpdateTime] datetime DEFAULT (getdate()) NULL
);
GO
CREATE TABLE [ASM_IVNT_PatchDeployResult] (
  [DeployResultID] bigint IDENTITY (1,1) NOT NULL
, [DeviceID] int NOT NULL
, [PatchUID] uniqueidentifier NOT NULL
, [DeploymentTime] datetime NOT NULL
, [DeploymentStatus] tinyint NOT NULL
, [Failure] bit NOT NULL
, [ReturnCode] int NULL
, [DeploymentID] uniqueidentifier NOT NULL
, [DateCreated] datetime NOT NULL
, [DateUpdated] datetime NULL
);
GO
CREATE TABLE [ASM_IVNT_PatchScanResult] (
  [ScanResultID] bigint IDENTITY (1,1) NOT NULL
, [DeviceID] int NOT NULL
, [PatchUID] uniqueidentifier NOT NULL
, [ScanStatus] tinyint NOT NULL
, [ScanStartTime] datetime NOT NULL
, [ScanEndTime] datetime NOT NULL
, [DateCreated] datetime NOT NULL
, [DateUpdated] datetime NULL
);
GO
CREATE TABLE [ASM_IVNT_PatchStatus] (
  [ID] bigint IDENTITY (1,1) NOT NULL
, [DeviceID] int NOT NULL
, [PatchUID] uniqueidentifier NOT NULL
, [PatchScanTime] datetime NULL
, [PatchDeploymentTime] datetime NULL
, [PatchDeploymentStatus] int NULL
, [PatchStatus] int NOT NULL
, [DateUpdated] datetime DEFAULT (getdate()) NULL
);
GO
CREATE TABLE [Asset_ProductKey] (
  [ProductKeyGuid] uniqueidentifier NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [Name] nvarchar(100) NOT NULL
, [ProductKey] nvarchar(100) NOT NULL
, [DateCreated] datetime NOT NULL
, [DateModified] datetime NOT NULL
, [OMRowVersion] rowversion NOT NULL
);
GO
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 DEFAULT ((0)) NOT NULL
, [RequiresSeeding] bit NOT NULL
);
GO
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
);
GO
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
);
GO
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
);
GO
CREATE TABLE [AvastAgentNic] (
  [id] int NOT NULL
, [device_id] int NOT NULL
, [mac] nvarchar(12) NULL
, [ipv4] nvarchar(16) NULL
, [ipv6] nvarchar(46) NULL
);
GO
CREATE TABLE [AvastAgentPolicy] (
  [PolicyID] int NOT NULL
, [Configuration] ntext NOT NULL
);
GO
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
);
GO
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] rowversion NOT NULL
);
GO
CREATE TABLE [AvastAgentSubserviceVersion] (
  [DeviceGuid] uniqueidentifier NOT NULL
, [OMRowVersion] rowversion 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
);
GO
ALTER TABLE [ASM_IVNT_ApprovalGroup] ADD CONSTRAINT [PK_ASM_IVNT_ApprovalGroup] PRIMARY KEY ([ApprovalGroupGuid]);
GO
ALTER TABLE [ASM_IVNT_BulletinApproval] ADD CONSTRAINT [PK_ASM_IVNT_BulletinApproval] PRIMARY KEY ([ApprovalGroupGuid],[BulletinID]);
GO
ALTER TABLE [ASM_IVNT_Device] ADD CONSTRAINT [PK_ASM_IVNT_Device] PRIMARY KEY ([DeviceID]);
GO
ALTER TABLE [ASM_IVNT_DeviceDesiredState] ADD CONSTRAINT [PK_ASM_IVNT_DeviceDesiredState] PRIMARY KEY ([DeviceID]);
GO
ALTER TABLE [ASM_IVNT_DeviceUrlUpdateHistory] ADD CONSTRAINT [PK_ASM_IVNT_DeviceUrlUpdateHisory] PRIMARY KEY ([DeviceID]);
GO
ALTER TABLE [ASM_IVNT_PatchDeployResult] ADD CONSTRAINT [PK_ASM_IVNT_PatchDeployResult] PRIMARY KEY ([DeployResultID]);
GO
ALTER TABLE [ASM_IVNT_PatchScanResult] ADD CONSTRAINT [PK_ASM_IVNT_PatchScanResult] PRIMARY KEY ([ScanResultID]);
GO
ALTER TABLE [ASM_IVNT_PatchStatus] ADD CONSTRAINT [PK_ASM_IVNT_PatchStatus] PRIMARY KEY ([ID]);
GO
ALTER TABLE [Asset_ProductKey] ADD CONSTRAINT [Asset_ProductKey_ProductKeyGuid_PK] PRIMARY KEY ([ProductKeyGuid]);
GO
ALTER TABLE [AvastAgent] ADD CONSTRAINT [AvastAgent_PK_DeviceID] PRIMARY KEY ([DeviceID]);
GO
ALTER TABLE [AvastAgentAction] ADD CONSTRAINT [AvastAgentAction_PK] PRIMARY KEY ([ActionID]);
GO
ALTER TABLE [AvastAgentAlertEventType] ADD CONSTRAINT [AvastAgentAlertEventType_AK_DeviceGuid_event_type_id_CLUST] PRIMARY KEY ([DeviceGuid],[event_type_id]);
GO
ALTER TABLE [AvastAgentDevice] ADD CONSTRAINT [AvastAgentDevice_PK] PRIMARY KEY ([device_id]);
GO
ALTER TABLE [AvastAgentNic] ADD CONSTRAINT [AvastAgentNic_PK] PRIMARY KEY ([device_id],[id]);
GO
ALTER TABLE [AvastAgentPolicy] ADD CONSTRAINT [AvastAgentPolicy_PK] PRIMARY KEY ([PolicyID]);
GO
ALTER TABLE [AvastAgentService] ADD CONSTRAINT [AvastAgentService_DeviceID_ServiceID_PK] PRIMARY KEY ([DeviceID],[ServiceID]);
GO
ALTER TABLE [AvastAgentServiceConfiguration] ADD CONSTRAINT [AvastAgentServiceConfiguration_PK] PRIMARY KEY ([id]);
GO
ALTER TABLE [AvastAgentSubserviceVersion] ADD CONSTRAINT [AvastAgentSubserviceVersion_AK_SubserviceItemGuid] PRIMARY KEY ([SubserviceItemGuid]);
GO
CREATE INDEX [IX_ASM_IVNT_PatchDeployResult] ON [ASM_IVNT_PatchDeployResult] ([DeviceID] ASC,[DeploymentID] ASC,[PatchUID] ASC);
GO
CREATE INDEX [IX_ASM_IVNT_PatchDeployResult_DeviceID] ON [ASM_IVNT_PatchDeployResult] ([DeviceID] ASC);
GO
ALTER TABLE [ASM_IVNT_PatchDeployResult] ADD CONSTRAINT [UIX_ASM_IVNT_PatchDeployResult] UNIQUE ([DeviceID],[PatchUID]);
GO
CREATE INDEX [IX_ASM_IVNT_PatchScanResult] ON [ASM_IVNT_PatchScanResult] ([DeviceID] ASC);
GO
ALTER TABLE [ASM_IVNT_PatchScanResult] ADD CONSTRAINT [UIX_ASM_IVNT_PatchScanResult] UNIQUE ([DeviceID],[PatchUID]);
GO
CREATE INDEX [IX_ASM_IVNT_PatchStatus_DeviceID] ON [ASM_IVNT_PatchStatus] ([DeviceID] ASC);
GO
ALTER TABLE [ASM_IVNT_PatchStatus] ADD CONSTRAINT [UIX_ASM_IVNT_PatchStatus] UNIQUE ([DeviceID],[PatchUID]);
GO
CREATE INDEX [Asset_ProductKey_DeviceGuid_IX] ON [Asset_ProductKey] ([DeviceGuid] ASC);
GO
ALTER TABLE [Asset_ProductKey] ADD CONSTRAINT [Asset_ProductKey_ProductKeyGuid_UX] UNIQUE ([ProductKeyGuid]);
GO
ALTER TABLE [AvastAgent] ADD CONSTRAINT [AvastAgent_AK_DeviceGuid_CLUST] UNIQUE ([DeviceGuid]);
GO
ALTER TABLE [AvastAgentAction] ADD CONSTRAINT [AvastAgentAction_AK_DeviceID_ActionID_CLUST] UNIQUE ([DeviceID],[ActionID]);
GO
ALTER TABLE [AvastAgentNic] ADD CONSTRAINT [AvastAgentNic_AK_id] UNIQUE ([id]);
GO
ALTER TABLE [AvastAgentServiceConfiguration] ADD CONSTRAINT [AvastAgentServiceConfiguration_AK_service_id_key] UNIQUE ([service_id],[key]);
GO
ALTER TABLE [AvastAgentSubserviceVersion] ADD CONSTRAINT [AvastAgentSubserviceVersion_subservice_id_CLUST] UNIQUE ([DeviceGuid],[subservice_id]);
GO
ALTER TABLE [ASM_IVNT_Device] ADD CONSTRAINT [FK_ASM_IVNT_Device_Device] FOREIGN KEY ([DeviceID]) REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;
GO
ALTER TABLE [ASM_IVNT_DeviceDesiredState] ADD CONSTRAINT [FK_ASM_IVNT_DeviceDesiredState_Device] FOREIGN KEY ([DeviceID]) REFERENCES [Device]([DeviceID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [ASM_IVNT_DeviceUrlUpdateHistory] ADD CONSTRAINT [FK_ASM_IVNT_DeviceUrlUpdateHisory] FOREIGN KEY ([DeviceID]) REFERENCES [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;
GO
ALTER TABLE [ASM_IVNT_PatchDeployResult] ADD CONSTRAINT [FK_ASM_IVNT_PatchDeployResult_ASM_IVNT_Device] FOREIGN KEY ([DeviceID]) REFERENCES [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;
GO
ALTER TABLE [ASM_IVNT_PatchScanResult] ADD CONSTRAINT [FK_ASM_IVNT_PatchScanResult_ASM_IVNT_Device] FOREIGN KEY ([DeviceID]) REFERENCES [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;
GO
ALTER TABLE [ASM_IVNT_PatchStatus] ADD CONSTRAINT [FK_ASM_IVNT_PatchStatus_ASM_IVNT_Device] FOREIGN KEY ([DeviceID]) REFERENCES [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;
GO
ALTER TABLE [AvastAgent] ADD CONSTRAINT [AvastAgent_Device_FK] FOREIGN KEY ([DeviceID]) REFERENCES [Device]([DeviceID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [AvastAgentAction] ADD CONSTRAINT [AvastAgentAction_AvastAgent_FK] FOREIGN KEY ([DeviceID]) REFERENCES [AvastAgent]([DeviceID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [AvastAgentDevice] ADD CONSTRAINT [AvastAgentDevice_AvastAgent_FK] FOREIGN KEY ([device_id]) REFERENCES [AvastAgent]([DeviceID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [AvastAgentDevice] ADD CONSTRAINT [AvastAgentDevice_AvastAgentPolicy_FK] FOREIGN KEY ([policy_id]) REFERENCES [AvastAgentPolicy]([PolicyID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [AvastAgentNic] ADD CONSTRAINT [FK_AvastAgentNic_devices] FOREIGN KEY ([device_id]) REFERENCES [AvastAgentDevice]([device_id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [AvastAgentService] ADD CONSTRAINT [FK_AvastAgentService_devices] FOREIGN KEY ([DeviceID]) REFERENCES [AvastAgent]([DeviceID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [AvastAgentSubserviceVersion] ADD CONSTRAINT [AvastAgentSubserviceVersion_AvastAgent_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [AvastAgent]([DeviceGuid]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
