-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 3d
--
-- Make clustered index UNIQUE.
-- ------------------------------------------------------------------------------------

SELECT dbo.FormatStatusMessage('Step 3d: Started.')

	IF(dbo.IndexExists('DataLog2', 'CIX_DataLog2_SourceID_QuantityID_TimestampUTC') = 1)
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 3d: Changing existing clustered index [CIX_DataLog2_SourceID_QuantityID_TimestampUTC] from non-unique to unique.')
		CREATE UNIQUE CLUSTERED INDEX 
			[CIX_DataLog2_SourceID_QuantityID_TimestampUTC] 
		ON 
			[dbo].[DataLog2] 
			(
				[SourceID] ASC,
				[QuantityID] ASC,
				[TimestampUTC] ASC
			)
		WITH (DROP_EXISTING = ON)
	END
GO
	IF(dbo.IndexExists('DataLog2', 'IX_DataLog2_ID') = 0)	
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 3d: Adding non-unique non-clustered index [IX_DataLog2_ID] to DataLog2 table.')
		CREATE NONCLUSTERED INDEX 
			[IX_DataLog2_ID]
		ON 
			[dbo].[DataLog2] 
			(
				[ID] ASC
			)
	END
GO
	IF(dbo.ConstraintExists('DataLog2', 'FK_DataLog2_Source') = 0)	
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 3d: Adding [FK_DataLog2_Source] foreign key constraint on DataLog2 table.')
		ALTER TABLE 
			[dbo].[DataLog2] 
		ADD	CONSTRAINT 
			[FK_DataLog2_Source] 
		FOREIGN KEY 
			([SourceID]) REFERENCES [dbo].[Source] ([ID])
	END
	IF(dbo.ConstraintExists('DataLog2', 'FK_DataLog2_Quantity') = 0)	
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 3d: Adding [FK_DataLog2_Quantity] foreign key constraint on DataLog2 table.')
		ALTER TABLE
			[dbo].[DataLog2]
		ADD CONSTRAINT
			[FK_DataLog2_Quantity]
		FOREIGN KEY
			([QuantityID]) REFERENCES [dbo].[Quantity] ([ID])
	END
GO
	DECLARE @MaxID AS INTEGER
	SET @MaxID = (SELECT MAX(ID) FROM dbo.DataLog2)
	IF(@MaxID IS NOT NULL)
	BEGIN
		PRINT dbo.FormatStatusMessage
		(
			'Step 3d: RESEED DataLog2 table identity value to '
			+ CAST(@MaxID AS NVARCHAR(250))
			+ '.'
		)
		DBCC CHECKIDENT ("dbo.DataLog2", RESEED, @MaxID);
	END
GO

SELECT 'Step 3d: Completed.'