-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 8
--
-- This script determines all the source-quantity pairs in the database, 
-- along with the minimum and maximum TimestampUTC for each pair, and 
-- stores the results in the SourceQuantityPair table.
-- ------------------------------------------------------------------------------------
	SELECT dbo.FormatStatusMessage('Step 8: Started.')
GO
	IF(dbo.TableExists('SourceQuantity') = 1)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 8: SourceQuantity table already existed, dropping table.')
		DROP TABLE [dbo].[SourceQuantity]
	END
GO
	IF(dbo.TableExists('SourceQuantity') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 8: Creating SourceQuantity table.')
		CREATE TABLE [dbo].[SourceQuantity]
		(
			[SourceID] INT NOT NULL
			, [QuantityID] SMALLINT NOT NULL
			, [MinTimestampUtc] DATETIME2(7) NOT NULL
			, [MaxTimestampUtc] DATETIME2(7) NOT NULL
		) ON [PRIMARY]
	END
GO
	SELECT dbo.FormatStatusMessage('Step 8: Populate SourceQuantity table.')
	INSERT 
		dbo.SourceQuantity WITH (TABLOCK) (SourceID, QuantityID, MinTimestampUTC, MaxTimestampUTC)
	SELECT DISTINCT
		SourceID,
		QuantityID,
		MIN(TimestampUTC),
		MAX(TimestampUTC)
	FROM
		dbo.Datalog2
	GROUP BY
		SourceID,
		QuantityID
GO
	IF(dbo.ConstraintExists('SourceQuantity', 'PK_SourceQuantity_SourceIDQuantityID') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 8: Add Primary Key [PK_SourceQuantity_SourceIDQuantityID] to SourceQuantity table.')
		ALTER TABLE 
			[dbo].[SourceQuantity]
		ADD CONSTRAINT 
			[PK_SourceQuantity_SourceIDQuantityID] PRIMARY KEY CLUSTERED 
			(
				[SourceID] ASC
				, [QuantityID] ASC
			)
	END
GO
	
	IF(dbo.ConstraintExists('SourceQuantity', 'FK_SourceQuantity_Quantity') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 8: Add foreign key constraint [FK_SourceQuantity_Quantity] to SourceQuantity table.')
	
		ALTER TABLE 
			[dbo].[SourceQuantity]  
		WITH CHECK ADD CONSTRAINT 
			[FK_SourceQuantity_Quantity] 
		FOREIGN KEY
		(
			[QuantityID]
		)
		REFERENCES 
			[dbo].[Quantity] ([ID])

		ALTER TABLE 
			[dbo].[SourceQuantity] 
		CHECK CONSTRAINT 
			[FK_SourceQuantity_Quantity]
	END
GO
	IF(dbo.ConstraintExists('SourceQuantity', 'FK_SourceQuantity_Source') = 0)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 8: Add foreign key constraint [FK_SourceQuantity_Source] to SourceQuantity table.')
	
		ALTER TABLE 
			[dbo].[SourceQuantity] 
		WITH CHECK ADD CONSTRAINT 
			[FK_SourceQuantity_Source] 
		FOREIGN KEY
		(
			[SourceID]
		)
		REFERENCES 
			[dbo].[Source] ([ID])

		ALTER TABLE 
			[dbo].[SourceQuantity] 
		CHECK CONSTRAINT 
			[FK_SourceQuantity_Source]
	END
GO
	SELECT dbo.FormatStatusMessage('Step 8: Completed.')