/*
The column DateUpdated from the ASM_IVNT_PatchStatus table has an unnamed constraint. 
Unnamed contraints are given a random name when they are created.
The below code will find the constraint name and will delete it.
*/

DECLARE @Command  NVARCHAR(1000)
SELECT TOP 1 @Command = 'ALTER TABLE [dbo].[ASM_IVNT_PatchStatus] DROP CONSTRAINT [' + RTRIM(con.[name]) + '];'
FROM sys.default_constraints con
LEFT OUTER JOIN sys.objects t on con.parent_object_id = t.object_id
LEFT OUTER JOIN sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id
WHERE t.[name]  = 'ASM_IVNT_PatchStatus'
AND col.[name] = 'DateUpdated'

IF @Command IS NOT NULL
BEGIN
	EXECUTE (@Command)
END

GO
ALTER TABLE [dbo].[BitLockerSettings] DROP CONSTRAINT [BitLockerSettings_IsEnabled_DF];
GO
ALTER TABLE [dbo].[BitLockerSettings] DROP CONSTRAINT [BitLockerSettings_EncryptUsedOnly_DF];
GO
ALTER TABLE [dbo].[BitLockerSettings] DROP CONSTRAINT [BitLockerSettings_GenerateKey_DF];
GO
ALTER TABLE [dbo].[ASM_IVNT_PatchDeployResult] DROP CONSTRAINT [FK_ASM_IVNT_PatchDeployResult_ASM_IVNT_Device];
GO
ALTER TABLE [dbo].[ASM_IVNT_PatchNow] DROP CONSTRAINT [FK_ASM_IVNT_PatchNow_ASM_IVNT_Device];
GO
ALTER TABLE [dbo].[ASM_IVNT_PatchScanResult] DROP CONSTRAINT [FK_ASM_IVNT_PatchScanResult_ASM_IVNT_Device];
GO
ALTER TABLE [dbo].[ASM_IVNT_PatchStatus] DROP CONSTRAINT [FK_ASM_IVNT_PatchStatus_ASM_IVNT_Device];
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_ASM_IVNT_BulletinApproval] (
    [ApprovalGroupGuid] UNIQUEIDENTIFIER NOT NULL,
    [BulletinID]        NVARCHAR (36)    NOT NULL,
    [OSType]            TINYINT          NOT NULL,
    [Action]            TINYINT          NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_ASM_IVNT_BulletinApproval_PK1] PRIMARY KEY CLUSTERED ([ApprovalGroupGuid] ASC, [BulletinID] ASC, [OSType] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[ASM_IVNT_BulletinApproval])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_ASM_IVNT_BulletinApproval] ([ApprovalGroupGuid], [BulletinID], [OSType], [Action])
        SELECT   [ApprovalGroupGuid],
                 [BulletinID],
                 1,
                 [Action]
        FROM     [dbo].[ASM_IVNT_BulletinApproval]
        ORDER BY [ApprovalGroupGuid] ASC, [BulletinID] ASC;
    END

DROP TABLE [dbo].[ASM_IVNT_BulletinApproval];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_ASM_IVNT_BulletinApproval]', N'ASM_IVNT_BulletinApproval';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_ASM_IVNT_BulletinApproval_PK1]', N'ASM_IVNT_BulletinApproval_PK', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
ALTER TABLE [dbo].[ASM_IVNT_Device] ADD [PatchToolsInstallReturnCode] INT NULL;
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_ASM_IVNT_PatchDeployResult] (
    [DeployResultID]   BIGINT           IDENTITY (1, 1) NOT NULL,
    [DeviceID]         INT              NOT NULL,
    [PatchUID]         UNIQUEIDENTIFIER NOT NULL,
    [BulletinID]       NVARCHAR (36)    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 [tmp_ms_xx_constraint_PK_ASM_IVNT_PatchDeployResult1] PRIMARY KEY CLUSTERED ([DeployResultID] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[ASM_IVNT_PatchDeployResult])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_ASM_IVNT_PatchDeployResult] ON;
        INSERT INTO [dbo].[tmp_ms_xx_ASM_IVNT_PatchDeployResult] ([DeployResultID], [DeviceID], [PatchUID], [DeploymentTime], [DeploymentStatus], [Failure], [ReturnCode], [DeploymentID], [DateCreated], [DateUpdated])
        SELECT   [DeployResultID],
                 [DeviceID],
                 [PatchUID],
                 [DeploymentTime],
                 [DeploymentStatus],
                 [Failure],
                 [ReturnCode],
                 [DeploymentID],
                 [DateCreated],
                 [DateUpdated]
        FROM     [dbo].[ASM_IVNT_PatchDeployResult]
        ORDER BY [DeployResultID] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_ASM_IVNT_PatchDeployResult] OFF;
    END

DROP TABLE [dbo].[ASM_IVNT_PatchDeployResult];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_ASM_IVNT_PatchDeployResult]', N'ASM_IVNT_PatchDeployResult';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_ASM_IVNT_PatchDeployResult1]', N'PK_ASM_IVNT_PatchDeployResult', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

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
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_ASM_IVNT_PatchNow] (
    [DeviceID]   INT           NOT NULL,
    [BulletinID] NVARCHAR (36) NOT NULL,
    [OSType]     TINYINT       NOT NULL,
    [Status]     SMALLINT      NOT NULL,
    [ReasonCode] SMALLINT      NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_ASM_IVNT_PatchNow1] PRIMARY KEY CLUSTERED ([DeviceID] ASC, [BulletinID] ASC, [OSType] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[ASM_IVNT_PatchNow])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_ASM_IVNT_PatchNow] ([DeviceID], [BulletinID], [OSType], [Status], [ReasonCode])
        SELECT   [DeviceID],
                 [BulletinID],
                 1,
                 [Status],
                 [ReasonCode]
        FROM     [dbo].[ASM_IVNT_PatchNow]
        ORDER BY [DeviceID] ASC, [BulletinID] ASC;
    END

DROP TABLE [dbo].[ASM_IVNT_PatchNow];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_ASM_IVNT_PatchNow]', N'ASM_IVNT_PatchNow';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_ASM_IVNT_PatchNow1]', N'PK_ASM_IVNT_PatchNow', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_ASM_IVNT_PatchScanResult] (
    [ScanResultID]  BIGINT           IDENTITY (1, 1) NOT NULL,
    [DeviceID]      INT              NOT NULL,
    [PatchUID]      UNIQUEIDENTIFIER NOT NULL,
    [BulletinID]    NVARCHAR (36)    NOT NULL,
    [ScanStatus]    TINYINT          NOT NULL,
    [ScanStartTime] DATETIME         NOT NULL,
    [ScanEndTime]   DATETIME         NOT NULL,
    [DateCreated]   DATETIME         NOT NULL,
    [DateUpdated]   DATETIME         NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_ASM_IVNT_PatchScanResult1] PRIMARY KEY CLUSTERED ([ScanResultID] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[ASM_IVNT_PatchScanResult])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_ASM_IVNT_PatchScanResult] ON;
        INSERT INTO [dbo].[tmp_ms_xx_ASM_IVNT_PatchScanResult] ([ScanResultID], [DeviceID], [PatchUID], [BulletinID], [ScanStatus], [ScanStartTime], [ScanEndTime], [DateCreated], [DateUpdated])
        SELECT   [ScanResultID],
                 [DeviceID],
                 [PatchUID],
                 'unknown',
                 [ScanStatus],
                 [ScanStartTime],
                 [ScanEndTime],
                 [DateCreated],
                 [DateUpdated]
        FROM     [dbo].[ASM_IVNT_PatchScanResult]
        ORDER BY [ScanResultID] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_ASM_IVNT_PatchScanResult] OFF;
    END

DROP TABLE [dbo].[ASM_IVNT_PatchScanResult];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_ASM_IVNT_PatchScanResult]', N'ASM_IVNT_PatchScanResult';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_ASM_IVNT_PatchScanResult1]', N'PK_ASM_IVNT_PatchScanResult', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
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
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_ASM_IVNT_PatchStatus] (
    [ID]                    BIGINT           IDENTITY (1, 1) NOT NULL,
    [DeviceID]              INT              NOT NULL,
    [PatchUID]              UNIQUEIDENTIFIER NOT NULL,
    [BulletinID]            NVARCHAR (36)    NOT NULL,
    [PatchScanTime]         DATETIME         NULL,
    [PatchDeploymentTime]   DATETIME         NULL,
    [PatchDeploymentStatus] INT              NULL,
    [PatchStatus]           INT              NOT NULL,
    [DateUpdated]           DATETIME         CONSTRAINT [ASM_IVNT_PatchStatus_DateUpdated_DF] DEFAULT (getdate()) NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_ASM_IVNT_PatchStatus1] PRIMARY KEY CLUSTERED ([ID] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[ASM_IVNT_PatchStatus])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_ASM_IVNT_PatchStatus] ON;
        INSERT INTO [dbo].[tmp_ms_xx_ASM_IVNT_PatchStatus] ([ID], [DeviceID], [PatchUID], [BulletinID], [PatchScanTime], [PatchDeploymentTime], [PatchDeploymentStatus], [PatchStatus], [DateUpdated])
        SELECT   [ID],
                 [DeviceID],
                 [PatchUID],
                  'unknown',
                 [PatchScanTime],
                 [PatchDeploymentTime],
                 [PatchDeploymentStatus],
                 [PatchStatus],
                 [DateUpdated]
        FROM     [dbo].[ASM_IVNT_PatchStatus]
        ORDER BY [ID] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_ASM_IVNT_PatchStatus] OFF;
    END

DROP TABLE [dbo].[ASM_IVNT_PatchStatus];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_ASM_IVNT_PatchStatus]', N'ASM_IVNT_PatchStatus';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_ASM_IVNT_PatchStatus1]', N'PK_ASM_IVNT_PatchStatus', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

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
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_BitLockerSettings] (
    [DeviceGuid]      UNIQUEIDENTIFIER NOT NULL,
    [IsEnabled]       BIT              CONSTRAINT [BitLockerSettings_IsEnabled_DF] DEFAULT ((0)) NOT NULL,
    [RecoveryKey]     NVARCHAR (100)   NULL,
    [EncryptUsedOnly] BIT              CONSTRAINT [BitLockerSettings_EncryptUsedOnly_DF] DEFAULT ((1)) NOT NULL,
    [GenerateKey]     BIT              CONSTRAINT [BitLockerSettings_GenerateKey_DF] DEFAULT ((1)) NOT NULL,
    [DriveLetter]     NCHAR (1)        NOT NULL,
    [SiteName]        NVARCHAR (255)   NOT NULL,
    [DeviceName]      NVARCHAR (255)   NOT NULL,
    [Modified]        DATETIME         NOT NULL,
    [IVector]         NVARCHAR (100)   NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_BitLockerSettings1] PRIMARY KEY CLUSTERED ([DeviceGuid] ASC, [DriveLetter] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[BitLockerSettings])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_BitLockerSettings] ([DeviceGuid], [DriveLetter], [IsEnabled], [RecoveryKey], [EncryptUsedOnly], [GenerateKey], [SiteName], [DeviceName], [Modified], [IVector])
        SELECT   [DeviceGuid],
                 [DriveLetter],
                 [IsEnabled],
                 [RecoveryKey],
                 [EncryptUsedOnly],
                 [GenerateKey],
                 [SiteName],
                 [DeviceName],
                 [Modified],
                 [IVector]
        FROM     [dbo].[BitLockerSettings]
        ORDER BY [DeviceGuid] ASC, [DriveLetter] ASC;
    END

DROP TABLE [dbo].[BitLockerSettings];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_BitLockerSettings]', N'BitLockerSettings';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_BitLockerSettings1]', N'PK_BitLockerSettings', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
CREATE TABLE [dbo].[Asset_BitlockerRecoveryKey] (
    [BitlockerRecoveryKeyGuid] UNIQUEIDENTIFIER NOT NULL,
    [DeviceGuid]               UNIQUEIDENTIFIER NOT NULL,
    [DriveLetter]              NVARCHAR (1000)  NULL,
    [BitlockerRecoveryKey]     NVARCHAR (1000)  NULL,
    [DateCreated]              DATETIME         NOT NULL,
    [DateModified]             DATETIME         NOT NULL,
    [OMRowVersion]             ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_BitlockerRecoveryKey_BitlockerRecoveryKey_PK] PRIMARY KEY NONCLUSTERED ([BitlockerRecoveryKeyGuid] ASC)
);
GO
CREATE UNIQUE CLUSTERED INDEX [Asset_BitlockerRecoveryKey_BitlockerRecoveryKeyGuid_UX] ON [dbo].[Asset_BitlockerRecoveryKey]([BitlockerRecoveryKeyGuid] ASC, [DeviceGuid] ASC);
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_PatchNow] WITH NOCHECK ADD CONSTRAINT [FK_ASM_IVNT_PatchNow_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_BitlockerRecoveryKey] WITH NOCHECK ADD CONSTRAINT [Asset_BitlockerRecoveryKey_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]);
GO
ALTER TABLE [dbo].[ASM_IVNT_PatchDeployResult] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_PatchDeployResult_ASM_IVNT_Device];
GO
ALTER TABLE [dbo].[ASM_IVNT_PatchNow] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_PatchNow_ASM_IVNT_Device];
GO
ALTER TABLE [dbo].[ASM_IVNT_PatchScanResult] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_PatchScanResult_ASM_IVNT_Device];
GO
ALTER TABLE [dbo].[ASM_IVNT_PatchStatus] WITH CHECK CHECK CONSTRAINT [FK_ASM_IVNT_PatchStatus_ASM_IVNT_Device];
GO
ALTER TABLE [dbo].[Asset_BitlockerRecoveryKey] WITH CHECK CHECK CONSTRAINT [Asset_BitlockerRecoveryKey_Device_FK];
GO
