-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 7
--
-- Updates the PQ_Events table, combining the separate TimestampUTC/FractionOfASecond
-- columns to a single DATETIME2 type.
-- ------------------------------------------------------------------------------------
	SELECT dbo.FormatStatusMessage('Step 7: Started.')
GO
	ALTER TABLE dbo.PQ_Events ADD
		StartTimeStampUTCTmp DATETIME NULL,
		EndTimeStampUTCTmp DATETIME NULL
GO
	SELECT dbo.FormatStatusMessage('Step 7: Copying timestamp values to temporary column(s).')
	UPDATE
		dbo.PQ_Events
	SET
		StartTimeStampUTCTmp = StartTimeStampUtc,
		EndTimeStampUTCTmp = EndTimeStampUtc
GO
	ALTER TABLE dbo.PQ_Events ALTER COLUMN StartTimeStampUTC DATETIME2(7) NOT NULL
	ALTER TABLE dbo.PQ_Events ALTER COLUMN EndTimeStampUTC DATETIME2(7) NOT NULL
GO
	SELECT dbo.FormatStatusMessage('Step 7: Converting TimestampUTC and FoS values to DATETIME2.')
	UPDATE
		dbo.PQ_Events
	SET
		StartTimeStampUTC =
		(
			SELECT 
				[TimestampUTC] 
			FROM 
				dbo.CombineTimestampAndFractionOfASecondAsTable(StartTimeStampUtcTmp, StartFractionOfASecond)
		)
		, EndTimeStampUTC =
		(
			SELECT 
				[TimestampUTC] 
			FROM 
				dbo.CombineTimestampAndFractionOfASecondAsTable(EndTimeStampUtcTmp, EndFractionOfASecond)
		)
GO
	EXEC sp_RENAME 'dbo.PQ_Events.StartDataLogStampId' , 'StartDataLogId', 'COLUMN'
	EXEC sp_RENAME 'dbo.PQ_Events.EndDataLogStampId' , 'EndDataLogId', 'COLUMN'
GO
	ALTER TABLE dbo.PQ_Events ALTER COLUMN StartDataLogId BIGINT NULL
	ALTER TABLE dbo.PQ_Events ALTER COLUMN EndDataLogId BIGINT NULL
GO
	ALTER TABLE dbo.PQ_Events DROP COLUMN StartTimeStampUtcTmp
	ALTER TABLE dbo.PQ_Events DROP COLUMN StartFractionOfASecond
	ALTER TABLE dbo.PQ_Events DROP COLUMN EndTimeStampUtcTmp
	ALTER TABLE dbo.PQ_Events DROP COLUMN EndFractionOfASecond
GO
	SELECT dbo.FormatStatusMessage('Step 7: Completed.')