-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 1
--
-- Adds database objects that assist the database upgrade process.
-- ------------------------------------------------------------------------------------
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDatabaseFileSize]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[GetDatabaseFileSize]
GO
	-- GetDatabaseFileSize
	-- Retrieves size of specified database file name
	CREATE FUNCTION [dbo].[GetDatabaseFileSize]
	(
		@FileName AS NVARCHAR(250)
	)  
	RETURNS BIGINT AS  
	BEGIN 
		DECLARE @Size AS BIGINT
	
		SELECT 
			@Size = size		
		FROM 
			sysfiles WITH (NOLOCK)
		WHERE 
			name = @FileName
		RETURN @Size
	END
GO
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatStatusMessage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[FormatStatusMessage]
GO
	-- FormatStatusMessage
	-- Prints the provided status message. Adds timestamp and database MDF 
	-- and LDF file sizes to output.
	CREATE FUNCTION [dbo].[FormatStatusMessage]
	(
		@Message AS NVARCHAR(250)
	)
	RETURNS NVARCHAR(MAX) AS
	BEGIN

		DECLARE @LogFileName AS NVARCHAR(250)
		SET @LogFileName = (SELECT TOP 1 [name] FROM sysfiles WITH (NOLOCK) WHERE filename LIKE '%.MDF')
		DECLARE @DataFileName AS NVARCHAR(250)
		SET @DataFileName = (SELECT TOP 1 [name] FROM sysfiles WITH (NOLOCK) WHERE filename LIKE '%.LDF')

		DECLARE @Timestamp AS NVARCHAR(250)
		SET @Timestamp = CAST(GETDATE() AS NVARCHAR(250))
	
		RETURN @Timestamp
			+ CHAR(13)
			+ CHAR(9)
			+ @Message
			+ CHAR(13)
			+ CHAR(9)
			+ 'Size of file ''' + @LogFileName + ''': ' + CAST(dbo.GetDatabaseFileSize(@LogFileName) AS NVARCHAR(1000)) + '.'
			+ CHAR(13)
			+ CHAR(9)			
			+ 'Size of file ''' + @DataFileName + ''': ' + CAST(dbo.GetDatabaseFileSize(@DataFileName) AS NVARCHAR(1000)) + '.'
			+ CHAR(13)	
	END
GO


	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSourceWithOrdinalNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[GetSourceWithOrdinalNumber]
GO
	-- GetSourceWithOrdinalNumber
	-- Returns the SourceId of the source record in row x
	CREATE FUNCTION [dbo].[GetSourceWithOrdinalNumber]
	(
		@OrdinalNumber INT
	)
	RETURNS INT
	AS
	BEGIN
		DECLARE @SourceID INT
	
		SELECT TOP 1 
			@SourceID = ID 
		FROM
		(
			SELECT
				ROW_NUMBER() OVER(ORDER BY ID) [RowNumber]
				, ID
				, Name
			FROM 
				Source
		) 
		AS 
			OrderedSourceList
		WHERE 
			RowNumber = @OrdinalNumber
	
		RETURN @SourceID
	END
GO
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableExists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[TableExists]
GO
	CREATE FUNCTION [dbo].[TableExists]
	(
		@TableName NVARCHAR(1000)
	)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @Exists AS BIT

		IF EXISTS(SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(@TableName)  AND type in (N'U'))
		BEGIN
			SET @Exists = 1
		END
		ELSE
		BEGIN
			SET @Exists = 0
		END
	
		RETURN @Exists
	END
GO
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ViewExists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[ViewExists]
GO
	CREATE FUNCTION [dbo].[ViewExists]
	(
		@ViewName NVARCHAR(1000)
	)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @Exists AS BIT

		IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(@ViewName))
		BEGIN
			SET @Exists = 1
		END
		ELSE
		BEGIN
			SET @Exists = 0
		END
	
		RETURN @Exists
	END
GO
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcedureExists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[ProcedureExists]
GO
	CREATE FUNCTION [dbo].[ProcedureExists]
	(
		@ProcedureName NVARCHAR(1000)
	)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @Exists AS BIT

		IF EXISTS(SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(@ProcedureName) AND type = N'P')
		
		BEGIN
			SET @Exists = 1
		END
		ELSE
		BEGIN
			SET @Exists = 0
		END
	
		RETURN @Exists
	END
GO
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FunctionExists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[FunctionExists]
GO
	CREATE FUNCTION [dbo].[FunctionExists]
	(
		@FunctionName NVARCHAR(1000)
	)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @Exists AS BIT

		IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@FunctionName) AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
		
		BEGIN
			SET @Exists = 1
		END
		ELSE
		BEGIN
			SET @Exists = 0
		END
	
		RETURN @Exists
	END
GO
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexExists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
		DROP FUNCTION [dbo].[IndexExists]
GO
	CREATE FUNCTION [dbo].[IndexExists]
	(
		@TableName NVARCHAR(1000)
		, @IndexName NVARCHAR(1000)
	)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @Exists AS BIT

		IF EXISTS (SELECT index_id FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) AND [name] = @IndexName)
		BEGIN
			SET @Exists = 1
		END
		ELSE
		BEGIN
			SET @Exists = 0
		END
	
		RETURN @Exists
	END
GO
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConstraintExists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[ConstraintExists]
GO
	CREATE FUNCTION [dbo].[ConstraintExists]
	(
		@TableName NVARCHAR(1000)
		, @ConstraintName NVARCHAR(1000)
	)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @Exists AS BIT

		IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME = @ConstraintName)
		BEGIN
			SET @Exists = 1
		END
		ELSE
		BEGIN
			SET @Exists = 0
		END
	
		RETURN @Exists
	END
GO
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RelationshipExists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[RelationshipExists]
GO
	CREATE FUNCTION [dbo].[RelationshipExists]
	(
		@TableName NVARCHAR(1000)
		, @ForeignKeyName NVARCHAR(1000)
	)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @Exists AS BIT

		IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(@ForeignKeyName) AND parent_object_id = OBJECT_ID(@TableName))
		BEGIN
			SET @Exists = 1
		END
		ELSE
		BEGIN
			SET @Exists = 0
		END
	
		RETURN @Exists
	END
GO
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DefaultValueConstraintExists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[DefaultValueConstraintExists]
GO
	CREATE FUNCTION [dbo].[DefaultValueConstraintExists]
	(
		@TableName NVARCHAR(1000)
		, @ConstraintName NVARCHAR(1000)
	)
	RETURNS BIT
	AS
	BEGIN
		DECLARE @Exists AS BIT

		IF EXISTS
		(
			SELECT TOP 1			
				a.name AS CONSTRAINT_NAME       
			FROM 
				sys.sysobjects a 
			INNER JOIN
				(SELECT name, id
				 FROM sys.sysobjects 
				 WHERE xtype = 'U') b ON (a.parent_obj = b.id)
			 WHERE 
				a.xtype = 'D'
				AND a.name = @ConstraintName
				AND b.name = @TableName
		)
		BEGIN
			SET @Exists = 1
		END
		ELSE
		BEGIN
			SET @Exists = 0
		END
	
		RETURN @Exists
	END
 GO

 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemoveDuplicateDataLogReadingsSingleSourceQuantity]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[RemoveDuplicateDataLogReadingsSingleSourceQuantity]
GO

--
-- Remove duplicates from the system, based on the parameters. 
--  @SourceID: The source for which to remove duplicates.
--  @QuantityID: The quantity for which to remove duplicates.
--
-- This removes duplicate datalog rows, replacing all instances with a single row. Duplicates are
-- defined as data readings that have the same SourceID, QuantityID, TimestampUTC and FractionOfASecond. 
-- The value that is written is simply the MAX value of those duplicates. (NULL will only be used as a
-- value if ALL the duplicate values are NULL.)
--
CREATE PROCEDURE [dbo].[RemoveDuplicateDataLogReadingsSingleSourceQuantity]
(
	@SourceID INT,
	@QuantityID SMALLINT
)
AS

CREATE TABLE #RowsToKeep
	(
	TimestampUTCToKeep DATETIME2 NOT NULL,
	ValueToKeep FLOAT NULL,
	IDToKeep BIGINT NULL
	)

DECLARE @RowCount INT

INSERT INTO
	#RowsToKeep (TimestampUTCToKeep, ValueToKeep)
SELECT
	TimestampUTC,
	CASE WHEN ISNULL(ABS(MIN(Value)),-2) > ISNULL(ABS(MAX(Value)),-1)
		THEN MIN(VALUE)
		ELSE MAX(VALUE)
	END
FROM 
	dbo.DataLog2
WHERE 
	SourceID = @SourceID
	AND QuantityID = @QuantityID
GROUP BY 
	SourceID,
	QuantityID, 
	TimestampUTC
HAVING 
	COUNT_BIG(*) > 1
	
UPDATE 
	#RowsToKeep
SET
	IDToKeep = 
		(
			SELECT 
				MAX(ID)
			FROM 
				dbo.DataLog2 
			WHERE
				SourceID = @SourceID
				AND QuantityID = @QuantityID
				AND TimestampUTC = TimestampUtcToKeep
				AND (Value = ValueToKeep OR Value IS NULL and ValueToKeep IS NULL)
		)	

IF EXISTS (SELECT 1 FROM #RowsToKeep)
BEGIN

	BEGIN TRAN
	
	-- Delete all rows identified as duplicates from DataLog
	DELETE FROM
		dbo.DataLog2
	WHERE 
		SourceID = @SourceID
		AND QuantityID = @QuantityID
		AND TimestampUTC IN (SELECT DISTINCT TimestampUtcToKeep FROM #RowsToKeep)

	SET IDENTITY_INSERT dbo.DataLog2 ON		
	INSERT INTO dbo.DataLog2
		(ID, SourceID, QuantityID, TimestampUTC, Value)
	SELECT
		IDToKeep, @SourceID, @QuantityID, TimestampUTCToKeep, ValueToKeep
	FROM
		#RowsToKeep
	SET IDENTITY_INSERT dbo.DataLog2 OFF
		
	COMMIT TRAN

END

DROP TABLE #RowsToKeep

RETURN 0
GO



-- a table to hold the progress of the "remove duplicates" step
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmpRemoveDuplicatesProgress]') AND type in (N'U'))
	DROP TABLE [dbo].[tmpRemoveDuplicatesProgress]
GO

CREATE TABLE dbo.tmpRemoveDuplicatesProgress
(
	PairsToProcess INT,
	PairsLeftToProcess INT
)
GO


SELECT dbo.FormatStatusMessage('Step 1: Completed. Added database objects that assist with upgrade process.')
GO