-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 5
--
-- Convert the WaveformLog table into WaveformLog2, combining the TimestampUTC and 
-- FractionOfASecond columns.
-- ------------------------------------------------------------------------------------
	SELECT dbo.FormatStatusMessage('Step 5: Started.')
GO
	IF(dbo.TableExists('WaveformLog') = 1)
	BEGIN 
		IF(dbo.TableExists('WaveformLog2') = 1)
		BEGIN
		-- If the WaveformLog and WaveformLog2 tables exist then a previous database upgrade attempt
		-- may have failed. Drop the WaveformLog2 table, it will be re-created and data migrated.
		SELECT dbo.FormatStatusMessage('Step 5: WaveformLog and WaveformLog2 tables already existed. A previous upgrade attempt may have failed. Dropping WaveformLog2 table.')
		DROP TABLE [dbo].[WaveformLog2]
		END
	END
	ELSE
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 5: WaveformLog table did not exist, WaveformLog migration is not required.')
	END
GO	
	IF(dbo.TableExists('WaveformLog2') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 5: Creating WaveformLog2 table.')
			CREATE TABLE [dbo].[WaveformLog2]
			(
				[ID] [int] IDENTITY(1,1) NOT NULL
				, [SourceID] INT NOT NULL
				, [TimestampUTC] DATETIME2(7) NOT NULL
				, [QuantityID] SMALLINT NOT NULL
				, [TimeOfTriggerUTC] DATETIME2(7) NOT NULL
				, [TimeOfFirstPointUTC] DATETIME2(7) NOT NULL
				, [SamplingFrequency] FLOAT NULL
				, [Scale] FLOAT NULL
				, [Offset] INT NULL
				, [DataPointType] NVARCHAR(10) NULL
				, [Samples] VARBINARY(MAX) NULL
			) 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('WaveformLog') = 1)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 5: Starting data migration to WaveformLog2 table.')
		SET IDENTITY_INSERT WaveformLog2 ON

		INSERT INTO dbo.WaveformLog2 WITH(TABLOCK)
		(
			[ID]
			, [SourceID]
			, [TimestampUTC]
			, [QuantityID]
			, [TimeOfTriggerUTC]
			, [TimeOfFirstPointUTC]
			, [SamplingFrequency]
			, [Scale]
			, [Offset]
			, [DataPointType]
			, [Samples]
			)
		SELECT
			[ID]
			, [SourceID]		
			, [TimestampUTC] = (SELECT [TimestampUTC] FROM dbo.CombineTimestampAndFractionOfASecondAsTable(TimestampUTC, FractionOfASecond))
			, [QuantityID]
			, [TimeOfTriggerUTC] = (SELECT [TimestampUTC] FROM dbo.CombineTimestampAndFractionOfASecondAsTable(TimeOfTriggerUTC, TriggerFracOfASec))
			, [TimeOfFirstPointUTC] = (SELECT [TimestampUTC] FROM dbo.CombineTimestampAndFractionOfASecondAsTable(TimeOfFirstPointUTC, FirstFracOfASec))
			, [SamplingFrequency]
			, [Scale]
			, [Offset]
			, [DataPointType]
			, [Samples]
		FROM 
			dbo.WaveformLog

		SET IDENTITY_INSERT WaveformLog2 OFF
	END
	ELSE
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 5: WaveformLog table did not exist. Migration of records to WaveformLog2 table not necessary.')
	END
GO
	IF(dbo.TableExists('WaveformLog') = 1)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 5: Dropping old WaveformLog table.')
		DROP TABLE WaveformLog
	END
GO
	IF(dbo.IndexExists('WaveformLog2', 'CIX_WaveformLog2_TimestampUTC') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 5: Creating WaveformLog2 clustered index.')
		CREATE CLUSTERED INDEX
			[CIX_WaveformLog2_TimestampUTC]
		ON
			[dbo].[WaveformLog2]([TimestampUTC]) ON [PRIMARY]
	END
GO
	IF(dbo.IndexExists('WaveformLog2', 'IX_WaveFormLog2_SourceTime') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 5: Creating WaveformLog2 index.')
		CREATE NONCLUSTERED INDEX
			[IX_WaveFormLog2_SourceTime]
	    ON
			[dbo].[WaveformLog2] ( [SourceID] ASC, [TimestampUTC] ASC )
	END
GO
	IF(dbo.ConstraintExists('WaveformLog2', 'PK_WaveformLog2_ID') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 5: Creating primary key [PK_WaveformLog2_ID] on WaveformLog2 table.')
		
		ALTER TABLE
			[dbo].[WaveformLog2]
		ADD CONSTRAINT
			[PK_WaveformLog2_ID] PRIMARY KEY NONCLUSTERED([ID] ASC)
	END
GO
	IF(dbo.ConstraintExists('WaveformLog2', 'FK_WaveformLog2_Quantity') = 0)
	BEGIN	
		SELECT dbo.FormatStatusMessage('Step 5: Creating primary key [FK_WaveformLog2_Quantity] on WaveformLog2 table.')
		
		ALTER TABLE 
			[dbo].[WaveformLog2]
		WITH CHECK ADD  CONSTRAINT 
			[FK_WaveformLog2_Quantity] 
		FOREIGN KEY
		(
			[QuantityID]
		)
		REFERENCES 
			[dbo].[Quantity] ([ID])

		ALTER TABLE
			[dbo].[WaveformLog2]
		CHECK CONSTRAINT 
			[FK_WaveformLog2_Quantity]
	END

	IF(dbo.ConstraintExists('WaveformLog2', 'FK_WaveformLog2_Source') = 0)
	BEGIN	
		SELECT dbo.FormatStatusMessage('Step 5: Creating primary key [FK_WaveformLog2_Source] on WaveformLog2 table.')
		
		ALTER TABLE 
			[dbo].[WaveformLog2]
		WITH CHECK ADD  CONSTRAINT 
			[FK_WaveformLog2_Source]
		FOREIGN KEY
		(
			[SourceID]
		)
		REFERENCES
			[dbo].[Source] ([ID])

		ALTER TABLE 
			[dbo].[WaveformLog2]
		CHECK CONSTRAINT
			[FK_WaveformLog2_Source]
	END
GO
	IF(dbo.ConstraintExists('WaveformLog2', 'CK_WaveformLog2_DataPointType') = 0)
	BEGIN	
		SELECT dbo.FormatStatusMessage('Step 5: Creating primary key [CK_WaveformLog2_DataPointType] on WaveformLog2 table.')
	
		ALTER TABLE 
			[dbo].[WaveformLog2]
		WITH NOCHECK ADD  CONSTRAINT
			[CK_WaveformLog2_DataPointType]
		CHECK
		(
			([DataPointType]='int-16' 
				OR [DataPointType]='int-32' 
				OR [DataPointType]='float-32' 
				OR [DataPointType]='float-64' 
				OR [DataPointType]='uint-16' 
				OR [DataPointType]='uint-32' 
				OR [DataPointType]='int-8' 
				OR [DataPointType]='uint-8')
		)
	
		ALTER TABLE 
			[dbo].[WaveformLog2]
		CHECK CONSTRAINT 
			[CK_WaveformLog2_DataPointType]
	END
	
	IF(dbo.ConstraintExists('WaveformLog2', 'CK_WaveformLog2_SamplingFreq') = 0)
	BEGIN	
		SELECT dbo.FormatStatusMessage('Step 5: Creating primary key [CK_WaveformLog2_SamplingFreq] on WaveformLog2 table.')
	
		ALTER TABLE 
			[dbo].[WaveformLog2]
		WITH NOCHECK ADD  CONSTRAINT
			[CK_WaveformLog2_SamplingFreq]
		CHECK
			(([SamplingFrequency]>=(0)))
	
		ALTER TABLE 
			[dbo].[WaveformLog2]
		CHECK CONSTRAINT 
			[CK_WaveformLog2_SamplingFreq]
	END
	
	IF(dbo.DefaultValueConstraintExists('WaveformLog2', 'DF_WaveformLog2_SamplingFrequency') = 0)
	BEGIN	
		SELECT dbo.FormatStatusMessage('Step 5: Creating primary key [DF_WaveformLog2_SamplingFrequency] on WaveformLog2 table.')
	
		ALTER TABLE 
			[dbo].[WaveformLog2]
		ADD  CONSTRAINT
			[DF_WaveformLog2_SamplingFrequency]
		DEFAULT 
			((0)) 
		FOR 
			[SamplingFrequency]
	END
	
	IF(dbo.DefaultValueConstraintExists('WaveformLog2', 'DF_WaveformLog2_Scale') = 0)
	BEGIN	
		SELECT dbo.FormatStatusMessage('Step 5: Creating primary key [DF_WaveformLog2_Scale] on WaveformLog2 table.')
	
		ALTER TABLE 
			[dbo].[WaveformLog2]
		ADD  CONSTRAINT 
			[DF_WaveformLog2_Scale] 
		DEFAULT 
			((0)) 
		FOR 
			[Scale]
	END
	
	IF(dbo.DefaultValueConstraintExists('WaveformLog2', 'DF_WaveformLog2_Offset') = 0)
	BEGIN	
		SELECT dbo.FormatStatusMessage('Step 5: Creating primary key [DF_WaveformLog2_Offset] on WaveformLog2 table.')
	
		ALTER TABLE 
			[dbo].[WaveformLog2]
		ADD  CONSTRAINT
			[DF_WaveformLog2_Offset]
		DEFAULT 
			((0))
		FOR 
			[Offset]
	END
GO
	SELECT dbo.FormatStatusMessage('Step 5: Completed.')