-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 4
--
-- Convert the EventLog table into EventLog2, combining the TimestampUTC and 
-- FractionOfASecond columns.
-- ------------------------------------------------------------------------------------
	SELECT dbo.FormatStatusMessage('Step 4: Started.')
GO
	IF(dbo.TableExists('EventLog') = 1)
	BEGIN 
		IF(dbo.TableExists('EventLog2') = 1)
		BEGIN
		-- If the EventLog and EventLog2 tables exist then a previous database upgrade attempt
		-- may have failed. Drop the EventLog2 table, it will be re-created and data migrated.
		SELECT dbo.FormatStatusMessage('Step 4: EventLog and EventLog2 tables already existed. A previous upgrade attempt may have failed. Dropping EventLog2 table.')
		DROP TABLE [dbo].[EventLog2]
		END
	END
	ELSE
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: EventLog table did not exist, EventLog migration is not required.')
	END
GO
	IF(dbo.TableExists('EventLog2') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating EventLog2 table.')
		CREATE TABLE [dbo].[EventLog2]
		(
			[ID] [int] IDENTITY(1,1) NOT NULL
			, [SourceID] INT NOT NULL
			, [TimestampUTC] DATETIME2(7) NOT NULL
			, [Priority] TINYINT NULL
			, [Type] TINYINT NULL
			, [CauseID] INT NULL
			, [CauseValueID] INT NULL
			, [EffectID] INT NULL
			, [EffectValueID] INT NULL
			, [TranType] AS CAST( CASE WHEN [Type]>=16 AND [Type]<32 AND [Type]<>23 THEN 1 WHEN [Type]>=32 AND [Type]<48 THEN 2 ELSE 0 END AS TINYINT)
		) ON [PRIMARY]
	END
GO
	-- Only migrate data if EventLog table exists. Database Upgrade scripts have always been
	-- able to be re-executed on without error
	IF(dbo.TableExists('EventLog') = 1)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Starting data migration to EventLog2 table.')

		-- Remove unreferenced EventLogIDs
		DELETE FROM 
			dbo.EventAcknowledgement 
		WHERE 
			EventLogID NOT IN (SELECT ID FROM dbo.EventLog)
		
		-- Move the data
		DECLARE @pid table (id int)
		DECLARE @did table (id int)
		DECLARE @cfgid table (id int)

		INSERT INTO @pid Select ID from [dbo].EventString e where  e.String like 'Active%' or e.String like 'Disturbance Start%' or e.String = 'Disturbance' or e.String = 'ON'
		INSERT into @did Select ID from [dbo].EventString e where  e.String like 'Inactive%' or e.String like 'Disturbance End%' or e.String = 'Normal' or e.String = 'OFF'
		INSERT INTO @cfgid Select ID from [dbo].EventString e where  e.String = 'Changed Setup' or e.String = 'Wrote Control Action'

		SET IDENTITY_INSERT EventLog2 ON

		INSERT INTO 
			dbo.EventLog2 WITH (TABLOCK) ([ID], [SourceID], [TimestampUTC], [Priority], [Type], [CauseID], [CauseValueID], [EffectID], [EffectValueID])
		SELECT
			[ID]
			, [SourceID]
			, [TimestampUTC] = (SELECT [TimestampUTC] FROM dbo.CombineTimestampAndFractionOfASecondAsTable(TimestampUTC, FractionOfASecond))
			, [Priority]
			, [Type] =
				CASE
					WHEN CauseValueId in (select id from @cfgid) THEN 3
					WHEN EffectValueID in (select id from @pid) THEN 22
					WHEN EffectValueID in (select id from @did) THEN 38
					ELSE 0
				END
			, [CauseID]
			, [CauseValueID]
			, [EffectID]
			, [EffectValueID]
		FROM
			dbo.EventLog

		SET IDENTITY_INSERT EventLog2 OFF
		
	END
	ELSE
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: EventLog table did not exist. Migration of records to EventLog2 table not necessary.')
	END
GO
	IF(dbo.RelationshipExists('EventAcknowledgement', 'FK_EventAcknowledgement_EventLog') <> 0)
	BEGIN
		ALTER TABLE [dbo].[EventAcknowledgement] DROP CONSTRAINT [FK_EventAcknowledgement_EventLog]
	END

	IF(dbo.TableExists('EventLog') = 1)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Dropping old EventLog table.')
		DROP TABLE EventLog
	END
GO
	IF(dbo.IndexExists('EventLog2', 'CIX_EventLog2_TimestampUTC') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating clustered index [CIX_EventLog2_TimestampUTC] on EventLog2 table.')
		CREATE CLUSTERED INDEX 
			[CIX_EventLog2_TimestampUTC] 
		ON 
			[dbo].[EventLog2] ([TimestampUTC])
	END
	
GO
	IF(dbo.IndexExists('EventLog2', 'IX_EventLog2_Alarms') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating nonclustered index [IX_EventLog2_Alarms] on EventLog2 table.')

		SET QUOTED_IDENTIFIER ON
		SET ANSI_WARNINGS ON
		SET CONCAT_NULL_YIELDS_NULL ON

		CREATE NONCLUSTERED INDEX [IX_EventLog2_Alarms] ON [dbo].[EventLog2] 
		(
			[TranType] ASC,
			[SourceID] ASC,
			[EffectID] ASC,
			[TimestampUTC] ASC,
			[ID] ASC
		)
	END
GO	
	IF(dbo.ConstraintExists('EventLog2', 'PK_EventLog2_ID') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating primary key constraint [PK_EventLog2_ID] on EventLog2 table.')
		ALTER TABLE 
			[dbo].[EventLog2]
		ADD  CONSTRAINT
			[PK_EventLog2_ID] PRIMARY KEY NONCLUSTERED([ID] ASC)	
	END
GO
	IF(dbo.ConstraintExists('EventLog2', 'FK_EventLog2_EventString_CauseID') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating foreign key constraint [FK_EventLog2_EventString_CauseID] on EventLog2 table.')
		ALTER TABLE 
			[dbo].[EventLog2]  
		ADD  CONSTRAINT 
			[FK_EventLog2_EventString_CauseID] FOREIGN KEY([CauseID])
		REFERENCES
			[dbo].[EventString] ([ID])

		ALTER TABLE
			[dbo].[EventLog2]
		CHECK CONSTRAINT 
			[FK_EventLog2_EventString_CauseID]
	END
GO
	IF(dbo.ConstraintExists('EventLog2', 'FK_EventLog2_EventString_CauseValueID') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating foreign key constraint [FK_EventLog2_EventString_CauseValueID] on EventLog2 table.')

		ALTER TABLE 
			[dbo].[EventLog2]  
		ADD  CONSTRAINT 
			[FK_EventLog2_EventString_CauseValueID] FOREIGN KEY([CauseValueID])
		REFERENCES [dbo].[EventString] ([ID])

		ALTER TABLE 
			[dbo].[EventLog2] 
		CHECK CONSTRAINT 
			[FK_EventLog2_EventString_CauseValueID]
	END
GO
	IF(dbo.ConstraintExists('EventLog2', 'FK_EventLog2_EventString_EffectID') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating foreign key constraint [FK_EventLog2_EventString_EffectID] on EventLog2 table.')
	
		ALTER TABLE 
			[dbo].[EventLog2] 
		ADD  CONSTRAINT 
			[FK_EventLog2_EventString_EffectID] FOREIGN KEY([EffectID])
		REFERENCES 
			[dbo].[EventString] ([ID])

		ALTER TABLE 
			[dbo].[EventLog2]
		CHECK CONSTRAINT
			[FK_EventLog2_EventString_EffectID]
	END
GO
	IF(dbo.ConstraintExists('EventLog2', 'FK_EventLog2_EventString_EffectValueID') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating foreign key constraint [FK_EventLog2_EventString_EffectValueID] on EventLog2 table.')
	
		ALTER TABLE 
			[dbo].[EventLog2]
		ADD  CONSTRAINT
			[FK_EventLog2_EventString_EffectValueID] FOREIGN KEY([EffectValueID])
		REFERENCES
			[dbo].[EventString] ([ID])

		ALTER TABLE 
			[dbo].[EventLog2]
		CHECK CONSTRAINT
			[FK_EventLog2_EventString_EffectValueID]
	END
GO
	IF(dbo.ConstraintExists('EventLog2', 'FK_EventLog2_Source') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating foreign key constraint [FK_EventLog2_Source] on EventLog2 table.')
	
		ALTER TABLE 
			[dbo].[EventLog2]
		WITH CHECK ADD  CONSTRAINT
			[FK_EventLog2_Source] FOREIGN KEY([SourceID])
		REFERENCES
			[dbo].[Source] ([ID])

		ALTER TABLE 
			[dbo].[EventLog2]
		CHECK CONSTRAINT
			[FK_EventLog2_Source]
	END
GO
	IF(dbo.RelationshipExists('EventAcknowledgement', 'FK_EventAcknowledgement_EventLog') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 4: Creating [FK_EventAcknowledgement_EventLog] constraint on EventLogAcknowledgement table.')
	
		ALTER TABLE 
			[dbo].[EventAcknowledgement] 
		WITH CHECK ADD CONSTRAINT
			[FK_EventAcknowledgement_EventLog]
		FOREIGN KEY
		(
			[EventLogID]
		)
		REFERENCES 
			[dbo].[EventLog2] ([ID])

		ALTER TABLE 
			[dbo].[EventAcknowledgement]
		CHECK CONSTRAINT
			[FK_EventAcknowledgement_EventLog]
	END
GO
	SELECT dbo.FormatStatusMessage('Step 4: Completed.')
