ALTER TABLE [dbo].[CustomLog] DROP CONSTRAINT [FK_CustomLog_Device];
GO
ALTER TABLE [dbo].[CustomLogMonitor] DROP CONSTRAINT [FK_CustomLogMonitor_CustomLog];
GO
ALTER TABLE [dbo].[CustomLogData] DROP CONSTRAINT [FK_CustomLogData_CustomLogMonitorSearch];
GO
ALTER TABLE [dbo].[CustomLogMonitorSearch] DROP CONSTRAINT [FK_CustomLogMonitorSearch_CustomLogMonitor];
GO
CREATE TABLE [dbo].[tmp_ms_xx_CustomLog] (
    [CustomLogID] INT             IDENTITY (1, 1) NOT NULL,
    [DeviceID]    INT             NULL,
    [DeviceIDRef] INT             NULL,
    [Name]        NVARCHAR (100)  NOT NULL,
    [Description] NVARCHAR (1000) NULL,
    [FilePath]    NVARCHAR (255)  NOT NULL
);
GO
IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[CustomLog])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_CustomLog] ON;
        INSERT INTO [dbo].[tmp_ms_xx_CustomLog] ([CustomLogID], [DeviceID], [Name], [Description], [FilePath])
        SELECT   [CustomLogID],
                 [DeviceID],
                 [Name],
                 [Description],
                 [FilePath]
        FROM     [dbo].[CustomLog]
        ORDER BY [CustomLogID] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_CustomLog] OFF;

        UPDATE [dbo].[tmp_ms_xx_CustomLog] SET [DeviceIDRef] = [DeviceID]
    END
GO
DROP TABLE [dbo].[CustomLog];
GO
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_CustomLog]', N'CustomLog';
GO
ALTER TABLE [dbo].[CustomLog] ALTER COLUMN [DeviceIDRef] INT NOT NULL
GO
ALTER TABLE [CustomLog] ADD CONSTRAINT [PK_CustomLog_DeviceID] PRIMARY KEY ([CustomLogID],[DeviceIDRef]);
GO

CREATE TABLE [dbo].[tmp_ms_xx_CustomLogData] (
    [CustomLogMonitorSearchID] INT             NOT NULL,
    [DeviceIDRef]              INT             NULL,
    [LineNumber]               INT             NOT NULL,
    [Capture]                  NVARCHAR (4000) NULL
);
GO
IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[CustomLogData])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_CustomLogData] ([CustomLogMonitorSearchID], [LineNumber], [Capture])
        SELECT [CustomLogMonitorSearchID],
               [LineNumber],
               [Capture]
        FROM   [dbo].[CustomLogData];

        UPDATE t SET t.[DeviceIDRef] = l.[DeviceIDRef]
		        FROM  [dbo].[tmp_ms_xx_CustomLogData] t 
				JOIN [CustomLogMonitorSearch] s on t.CustomLogMonitorSearchID = s.CustomLogMonitorSearchID
				JOIN [CustomLog] l on s.CustomLogID = l.CustomLogID

    END
GO
DROP TABLE [dbo].[CustomLogData];
GO
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_CustomLogData]', N'CustomLogData';
GO
ALTER TABLE [dbo].[CustomLogData] ALTER COLUMN [DeviceIDRef] INT NOT NULL
GO

CREATE TABLE [dbo].[tmp_ms_xx_CustomLogMonitor] (
    [CustomLogID]          INT            NOT NULL,
    [DeviceIDRef]          INT            NULL,
    [UserName]             NVARCHAR (50)  NULL,
    [Password]             NVARCHAR (255) NULL,
    [PollingInterval]      INT            NOT NULL,
    [RegularExpression]    NVARCHAR (255) NOT NULL,
    [MatchCase]            BIT            NOT NULL,
    [MatchWholeWord]       BIT            NOT NULL,
    [UseRegularExpression] BIT            NOT NULL,
    [Capture]              BIT            NOT NULL,
    [StartingPosition]     INT            NOT NULL  
);
GO
IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[CustomLogMonitor])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_CustomLogMonitor] ([CustomLogID], [UserName], [Password], [PollingInterval], [RegularExpression], [MatchCase], [MatchWholeWord], [UseRegularExpression], [Capture], [StartingPosition])
        SELECT   [CustomLogID],
                 [UserName],
                 [Password],
                 [PollingInterval],
                 [RegularExpression],
                 [MatchCase],
                 [MatchWholeWord],
                 [UseRegularExpression],
                 [Capture],
                 [StartingPosition]
        FROM     [dbo].[CustomLogMonitor]
        ORDER BY [CustomLogID] ASC;

       UPDATE t SET t.[DeviceIDRef] = l.[DeviceIDRef]
		        FROM  [dbo].[tmp_ms_xx_CustomLogMonitor] t 
				JOIN [CustomLog] l on t.CustomLogID = l.CustomLogID
    END
GO
DROP TABLE [dbo].[CustomLogMonitor];
GO
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_CustomLogMonitor]', N'CustomLogMonitor';
GO
ALTER TABLE [dbo].[CustomLogMonitor] ALTER COLUMN [DeviceIDRef] INT NOT NULL
GO
ALTER TABLE [CustomLogMonitor] ADD CONSTRAINT [PK_CustomLogMonitor] PRIMARY KEY ([CustomLogID],[DeviceIDRef]);

GO
CREATE TABLE [dbo].[tmp_ms_xx_CustomLogMonitorSearch] (
    [CustomLogMonitorSearchID] INT      IDENTITY (1, 1) NOT NULL,
    [DeviceIDRef]              INT      NULL,
    [CustomLogID]              INT      NOT NULL,
    [StartTime]                DATETIME NOT NULL,
    [EndTime]                  DATETIME NOT NULL,
    [StartLineNumber]          INT      NOT NULL,
    [EndLineNumber]            INT      NOT NULL
);
GO
IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[CustomLogMonitorSearch])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_CustomLogMonitorSearch] ON;
        INSERT INTO [dbo].[tmp_ms_xx_CustomLogMonitorSearch] ([CustomLogMonitorSearchID], [CustomLogID], [StartTime], [EndTime], [StartLineNumber], [EndLineNumber])
        SELECT   [CustomLogMonitorSearchID],
                 [CustomLogID],
                 [StartTime],
                 [EndTime],
                 [StartLineNumber],
                 [EndLineNumber]
        FROM     [dbo].[CustomLogMonitorSearch]
        ORDER BY [CustomLogMonitorSearchID] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_CustomLogMonitorSearch] OFF;

        UPDATE t SET t.[DeviceIDRef] = l.[DeviceIDRef]
		FROM [dbo].[tmp_ms_xx_CustomLogMonitorSearch] t 
		JOIN [dbo].[CustomLog] l on t.[CustomLogID] = l.[CustomLogID]
    END
GO
DROP TABLE [dbo].[CustomLogMonitorSearch];
GO
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_CustomLogMonitorSearch]', N'CustomLogMonitorSearch';
GO
ALTER TABLE [dbo].[CustomLogMonitorSearch] ALTER COLUMN [DeviceIDRef] INT NOT NULL
GO
ALTER TABLE [CustomLogMonitorSearch] ADD CONSTRAINT [PK_CustomLogMonitorSearch] PRIMARY KEY ([CustomLogMonitorSearchID],[DeviceIDRef]);
GO

CREATE TABLE [dbo].[Asset_IBUAgent] (
    [DeviceGuid]   UNIQUEIDENTIFIER NOT NULL,
    [UserName]     NVARCHAR (50)    NULL,
    [ComputerID]   CHAR (4)         NULL,
    [AgentVersion] NVARCHAR (50)    NULL,
    [DateCreated]  DATETIME         NOT NULL,
    [DateModified] DATETIME         NOT NULL,
    [OMRowVersion] ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_IBUAgent_DeviceGuid_PK] PRIMARY KEY CLUSTERED ([DeviceGuid] ASC)
);
GO
ALTER TABLE [dbo].[CustomLog] WITH NOCHECK
    ADD CONSTRAINT [FK_CustomLog_Device] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE;
GO

ALTER TABLE [dbo].[CustomLogMonitor] WITH NOCHECK
    ADD CONSTRAINT [FK_CustomLogMonitor_CustomLog] FOREIGN KEY ([CustomLogID], [DeviceIDRef]) REFERENCES [dbo].[CustomLog] ([CustomLogID], [DeviceIDRef]) ON DELETE CASCADE ON UPDATE CASCADE;
GO

ALTER TABLE [dbo].[CustomLogData] WITH NOCHECK
    ADD CONSTRAINT [FK_CustomLogData_CustomLogMonitorSearch_Device] FOREIGN KEY ([CustomLogMonitorSearchID], [DeviceIDRef]) REFERENCES [dbo].[CustomLogMonitorSearch] ([CustomLogMonitorSearchID], [DeviceIDRef]) ON DELETE CASCADE ON UPDATE CASCADE;
GO
ALTER TABLE [dbo].[CustomLogMonitorSearch] WITH NOCHECK
    ADD CONSTRAINT [FK_CustomLogMonitorSearch_CustomLogMonitor] FOREIGN KEY ([CustomLogID], [DeviceIDRef]) REFERENCES [dbo].[CustomLogMonitor] ([CustomLogID], [DeviceIDRef]) ON DELETE CASCADE ON UPDATE CASCADE;

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

GO
ALTER TABLE [dbo].[CustomLog] WITH CHECK CHECK CONSTRAINT [FK_CustomLog_Device];
GO
ALTER TABLE [dbo].[CustomLogMonitor] WITH CHECK CHECK CONSTRAINT [FK_CustomLogMonitor_CustomLog];
GO
ALTER TABLE [dbo].[CustomLogData] WITH CHECK CHECK CONSTRAINT [FK_CustomLogData_CustomLogMonitorSearch_Device];
GO
ALTER TABLE [dbo].[CustomLogMonitorSearch] WITH CHECK CHECK CONSTRAINT [FK_CustomLogMonitorSearch_CustomLogMonitor];
GO
ALTER TABLE [dbo].[Asset_IBUAgent] WITH CHECK CHECK CONSTRAINT [Asset_IBUAgent_DeviceGuid_Device_DeviceGuid_FK];
GO