ALTER TABLE [dbo].[AV_CloudCareAgent] DROP CONSTRAINT [DF_AV_CloudCareAgent_IsPolicyAssignedInAfb];


GO
ALTER TABLE [dbo].[AV_Action] DROP CONSTRAINT [AV_Action_AV_CloudCareAgent_FK];


GO
ALTER TABLE [dbo].[AVCC_devices] DROP CONSTRAINT [AVCC_devices_AV_CloudCareAgent_FK];


GO
ALTER TABLE [dbo].[AVCC_devices] DROP CONSTRAINT [AVCC_devices_AV_Policy_FK];


GO
ALTER TABLE [dbo].[AVCC_nics] DROP CONSTRAINT [FK_nics_devices];


GO
ALTER TABLE [dbo].[AV_VirusVault] DROP CONSTRAINT [AV_VirusVault_AV_CloudCareAgent_FK];


GO
ALTER TABLE [dbo].[AVCC_device_services] DROP CONSTRAINT [FK_device_services_devices];


GO
ALTER TABLE [dbo].[AVCC_device_subservice_versions] DROP CONSTRAINT [AVCC_device_subservice_versions_AV_CloudCareAgent_FK];


GO
ALTER TABLE [dbo].[AV_CloudCareAgent] DROP CONSTRAINT [AV_CloudCareAgent_Device_FK];


GO
ALTER TABLE [dbo].[AVCC_alert_event_types] DROP CONSTRAINT [AVCC_alert_event_types_Device_FK];


GO
IF OBJECT_ID('[dbo].[SC_DB_Raise_Error]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[SC_DB_Raise_Error]
END

GO
CREATE TABLE [dbo].[ASM_IVNT_ApprovalGroup] (
    [ApprovalGroupGuid] UNIQUEIDENTIFIER NOT NULL,
    [ApprovalGroupName] NVARCHAR (255)   NULL,
    CONSTRAINT [ASM_IVNT_ApprovalGroup_PK] PRIMARY KEY CLUSTERED ([ApprovalGroupGuid] ASC)
);


GO
CREATE TABLE [dbo].[ASM_IVNT_BulletinApproval] (
    [ApprovalGroupGuid] UNIQUEIDENTIFIER NOT NULL,
    [BulletinID]        NVARCHAR (36)    NOT NULL,
    [Action]            TINYINT          NOT NULL,
    CONSTRAINT [ASM_IVNT_BulletinApproval_PK] PRIMARY KEY CLUSTERED ([ApprovalGroupGuid] ASC, [BulletinID] ASC)
);


GO
CREATE TABLE [dbo].[ASM_IVNT_Device] (
    [DeviceID]               INT           NOT NULL,
    [PatchToolsVersion]      NVARCHAR (20) NOT NULL,
    [PatchToolsState]        TINYINT       NOT NULL,
    [LastReportTime]         DATETIME2 (7) NULL,
    [LastReportedStatusTime] DATETIME2 (7) NULL,
    CONSTRAINT [ASM_IVNT_Device_PK] PRIMARY KEY CLUSTERED ([DeviceID] ASC)
);


GO
CREATE TABLE [dbo].[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,
    CONSTRAINT [ASM_IVNT_DeviceDesiredState_PK] PRIMARY KEY CLUSTERED ([DeviceID] ASC)
);


GO
CREATE TABLE [dbo].[ASM_IVNT_DeviceUrlUpdateHistory] (
    [DeviceID]        INT      NOT NULL,
    [UrlUpdateStatus] BIT      NULL,
    [LastUpdateTime]  DATETIME NULL,
    CONSTRAINT [PK_ASM_IVNT_DeviceUrlUpdateHisory] PRIMARY KEY CLUSTERED ([DeviceID] ASC)
);


GO
CREATE TABLE [dbo].[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,
    CONSTRAINT [PK_ASM_IVNT_PatchDeployResult] PRIMARY KEY CLUSTERED ([DeployResultID] ASC)
);


GO
CREATE NONCLUSTERED INDEX [IX_ASM_IVNT_PatchDeployResult_DeviceID]
    ON [dbo].[ASM_IVNT_PatchDeployResult]([DeviceID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ASM_IVNT_PatchDeployResult]
    ON [dbo].[ASM_IVNT_PatchDeployResult]([DeviceID] ASC, [DeploymentID] ASC, [PatchUID] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [UIX_ASM_IVNT_PatchDeployResult]
    ON [dbo].[ASM_IVNT_PatchDeployResult]([DeviceID] ASC, [PatchUID] ASC);


GO
CREATE TABLE [dbo].[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,
    CONSTRAINT [PK_ASM_IVNT_PatchScanResult] PRIMARY KEY CLUSTERED ([ScanResultID] ASC)
);


GO
CREATE NONCLUSTERED INDEX [IX_ASM_IVNT_PatchScanResult]
    ON [dbo].[ASM_IVNT_PatchScanResult]([DeviceID] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [UIX_ASM_IVNT_PatchScanResult]
    ON [dbo].[ASM_IVNT_PatchScanResult]([DeviceID] ASC, [PatchUID] ASC);


GO
CREATE TABLE [dbo].[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         NULL,
    CONSTRAINT [PK_ASM_IVNT_PatchStatus] PRIMARY KEY CLUSTERED ([ID] ASC)
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [UIX_ASM_IVNT_PatchStatus]
    ON [dbo].[ASM_IVNT_PatchStatus]([DeviceID] ASC, [PatchUID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ASM_IVNT_PatchStatus_DeviceID]
    ON [dbo].[ASM_IVNT_PatchStatus]([DeviceID] ASC);


GO
CREATE TABLE [dbo].[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,
    CONSTRAINT [Asset_ProductKey_ProductKeyGuid_PK] PRIMARY KEY NONCLUSTERED ([ProductKeyGuid] ASC)
);


GO
CREATE NONCLUSTERED INDEX [Asset_ProductKey_DeviceGuid_IX]
    ON [dbo].[Asset_ProductKey]([DeviceGuid] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [Asset_ProductKey_ProductKeyGuid_UX]
    ON [dbo].[Asset_ProductKey]([ProductKeyGuid] ASC);


GO
CREATE TABLE [dbo].[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,
    [RequiresSeeding]       BIT              NOT NULL,
    CONSTRAINT [AvastAgent_PK_DeviceID] PRIMARY KEY NONCLUSTERED ([DeviceID] ASC),
    CONSTRAINT [AvastAgent_AK_DeviceGuid_CLUST] UNIQUE CLUSTERED ([DeviceGuid] ASC)
);


GO
CREATE TABLE [dbo].[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 NONCLUSTERED ([ActionID] ASC),
    CONSTRAINT [AvastAgentAction_AK_DeviceID_ActionID_CLUST] UNIQUE CLUSTERED ([DeviceID] ASC, [ActionID] ASC)
);


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


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


GO
CREATE TABLE [dbo].[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 CLUSTERED ([device_id] ASC, [id] ASC),
    CONSTRAINT [AvastAgentNic_AK_id] UNIQUE NONCLUSTERED ([id] ASC)
);


GO
CREATE TABLE [dbo].[AvastAgentPolicy] (
    [PolicyID]      INT   NOT NULL,
    [Configuration] NTEXT NOT NULL,
    CONSTRAINT [AvastAgentPolicy_PK] PRIMARY KEY CLUSTERED ([PolicyID] ASC)
);


GO
CREATE TABLE [dbo].[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 NONCLUSTERED ([DeviceID] ASC, [ServiceID] ASC)
);


GO
CREATE TABLE [dbo].[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,
    CONSTRAINT [AvastAgentServiceConfiguration_PK] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [AvastAgentServiceConfiguration_AK_service_id_key] UNIQUE NONCLUSTERED ([service_id] ASC, [key] ASC)
);


GO
CREATE TABLE [dbo].[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,
    CONSTRAINT [AvastAgentSubserviceVersion_AK_SubserviceItemGuid] PRIMARY KEY NONCLUSTERED ([SubserviceItemGuid] ASC),
    CONSTRAINT [AvastAgentSubserviceVersion_subservice_id_CLUST] UNIQUE CLUSTERED ([DeviceGuid] ASC, [subservice_id] ASC)
);


GO
ALTER TABLE [dbo].[ASM_IVNT_DeviceUrlUpdateHistory]
    ADD DEFAULT (getdate()) FOR [LastUpdateTime];


GO
ALTER TABLE [dbo].[ASM_IVNT_DeviceUrlUpdateHistory]
    ADD DEFAULT ((0)) FOR [UrlUpdateStatus];


GO
ALTER TABLE [dbo].[ASM_IVNT_PatchStatus]
    ADD DEFAULT (getdate()) FOR [DateUpdated];


GO
ALTER TABLE [dbo].[AvastAgent]
    ADD CONSTRAINT [DF_AvastAgent_IsPolicyAssignedInAfb] DEFAULT ((0)) FOR [IsPolicyAssignedInAfb];


GO
ALTER TABLE [dbo].[ASM_IVNT_Device] WITH NOCHECK
    ADD CONSTRAINT [FK_ASM_IVNT_Device_Device] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
ALTER TABLE [dbo].[ASM_IVNT_DeviceDesiredState] WITH NOCHECK
    ADD CONSTRAINT [FK_ASM_IVNT_DeviceDesiredState_Device] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[Device] ([DeviceID]);


GO
ALTER TABLE [dbo].[ASM_IVNT_DeviceUrlUpdateHistory] WITH NOCHECK
    ADD CONSTRAINT [FK_ASM_IVNT_DeviceUrlUpdateHisory] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[ASM_IVNT_Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
ALTER TABLE [dbo].[ASM_IVNT_PatchDeployResult] WITH NOCHECK
    ADD CONSTRAINT [FK_ASM_IVNT_PatchDeployResult_ASM_IVNT_Device] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[ASM_IVNT_Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
ALTER TABLE [dbo].[ASM_IVNT_PatchScanResult] WITH NOCHECK
    ADD CONSTRAINT [FK_ASM_IVNT_PatchScanResult_ASM_IVNT_Device] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[ASM_IVNT_Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
ALTER TABLE [dbo].[ASM_IVNT_PatchStatus] WITH NOCHECK
    ADD CONSTRAINT [FK_ASM_IVNT_PatchStatus_ASM_IVNT_Device] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[ASM_IVNT_Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
ALTER TABLE [dbo].[Asset_ProductKey] WITH NOCHECK
    ADD CONSTRAINT [Asset_ProductKey_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]) ON DELETE CASCADE;


GO
ALTER TABLE [dbo].[AvastAgent] WITH NOCHECK
    ADD CONSTRAINT [AvastAgent_Device_FK] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[Device] ([DeviceID]);


GO
ALTER TABLE [dbo].[AvastAgentAction] WITH NOCHECK
    ADD CONSTRAINT [AvastAgentAction_AvastAgent_FK] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[AvastAgent] ([DeviceID]);


GO
ALTER TABLE [dbo].[AvastAgentAlertEventType] WITH NOCHECK
    ADD CONSTRAINT [AvastAgentAlertEventTypes_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]);


GO
ALTER TABLE [dbo].[AvastAgentDevice] WITH NOCHECK
    ADD CONSTRAINT [AvastAgentDevice_AvastAgent_FK] FOREIGN KEY ([device_id]) REFERENCES [dbo].[AvastAgent] ([DeviceID]);


GO
ALTER TABLE [dbo].[AvastAgentDevice] WITH NOCHECK
    ADD CONSTRAINT [AvastAgentDevice_AvastAgentPolicy_FK] FOREIGN KEY ([policy_id]) REFERENCES [dbo].[AvastAgentPolicy] ([PolicyID]);


GO
ALTER TABLE [dbo].[AvastAgentNic] WITH NOCHECK
    ADD CONSTRAINT [FK_nics_devices] FOREIGN KEY ([device_id]) REFERENCES [dbo].[AvastAgentDevice] ([device_id]);


GO
ALTER TABLE [dbo].[AvastAgentService] WITH NOCHECK
    ADD CONSTRAINT [AvastAgentService_AvastAgent_FK] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[AvastAgent] ([DeviceID]);


GO
ALTER TABLE [dbo].[AvastAgentSubserviceVersion] WITH NOCHECK
    ADD CONSTRAINT [AvastAgentSubserviceVersion_AvastAgent_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[AvastAgent] ([DeviceGuid]);


GO
ALTER TABLE [dbo].[ASM_IVNT_Device] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_Device_Device];

ALTER TABLE [dbo].[ASM_IVNT_DeviceDesiredState] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_DeviceDesiredState_Device];

ALTER TABLE [dbo].[ASM_IVNT_DeviceUrlUpdateHistory] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_DeviceUrlUpdateHisory];

ALTER TABLE [dbo].[ASM_IVNT_PatchDeployResult] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_PatchDeployResult_ASM_IVNT_Device];

ALTER TABLE [dbo].[ASM_IVNT_PatchScanResult] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_PatchScanResult_ASM_IVNT_Device];

ALTER TABLE [dbo].[ASM_IVNT_PatchStatus] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_PatchStatus_ASM_IVNT_Device];

ALTER TABLE [dbo].[Asset_ProductKey] WITH CHECK CHECK CONSTRAINT [Asset_ProductKey_Device_FK];


GO
