CREATE TABLE [dbo].[Security_ThirdPartyFirewallInfo] (
    [FirewallInfoGuid] UNIQUEIDENTIFIER NOT NULL,
    [DeviceGuid]       UNIQUEIDENTIFIER NOT NULL,
    [Name]             NVARCHAR (200)   NOT NULL,
    [Enabled]          BIT              NOT NULL,
    [InstallDate]      DATETIME         NULL,
    [DateCreated]      DATETIME         NOT NULL,
    [DateModified]     DATETIME         NOT NULL,
    [OMRowVersion]     ROWVERSION       NOT NULL
);
GO

DROP TABLE [dbo].[Security_AntiSpywareInfo];
GO

DROP TABLE [dbo].[SystemAssets];
GO

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

ALTER TABLE [dbo].[Security_ThirdPartyFirewallInfo] WITH CHECK CHECK CONSTRAINT [Security_ThirdPartyFirewallInfo_Device_FK];
GO

DELETE
FROM [dbo].[Security_DeviceDataCollectionStatus]
WHERE ManagedElementTypeGuid = 'A452910E-9F3A-4C1F-BCD5-DB23919B3C28'
GO

DELETE
FROM [dbo].[ManagedElementAttributeType]
WHERE ManagedElementTypeGuid = 'A452910E-9F3A-4C1F-BCD5-DB23919B3C28'
GO

DELETE
FROM [dbo].[ManagedElementType]
WHERE ManagedElementTypeGuid = 'A452910E-9F3A-4C1F-BCD5-DB23919B3C28'
GO

UPDATE [AVCC_service_configurations]
SET [value] = '4.2.0'
WHERE [key] = 'latest_version'
GO

--This deletion only needs to be executed on upgrades
DELETE
FROM AVCC_device_subservice_versions
WHERE DeviceGuid IN (
		SELECT DeviceGuid
		FROM AV_CloudCareAgent
		WHERE [State] = 101
		)
GO

