IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[MXC_Partitioning_tbTransactionControlInfo_Purge]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MXC_Partitioning_tbTransactionControlInfo_Purge]
GO

CREATE PROCEDURE [dbo].[MXC_Partitioning_tbTransactionControlInfo_Purge]
AS
BEGIN 

	-- this is the lower bound of the partitions to keep.  ie. all table data and partitions earlier than this date will be cleared.
	DECLARE @newLowerBoundaryDate SMALLDATETIME = DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
	DECLARE @lowerBoundaryPartitionNum INT

	-- 1 : delete the table data from the referenced tables
	DELETE FROM [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] WHERE [BusinessDay] < @newLowerBoundaryDate
	DELETE FROM [dbo].[tbOnAccountCollectedSaleIgnoreList_partitioned] WHERE [BusinessDay] < @newLowerBoundaryDate
	DELETE FROM [dbo].[tbOnAccountSalePayment_partitioned] WHERE [BusinessDay] < @newLowerBoundaryDate


	-- 2 : drop foreign key contraints so we can use TRUNCATE to clear table data
	ALTER TABLE [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] DROP CONSTRAINT [FK_tbCustomerOnAccountBalanceEvent_partitioned_tbTransactionControlInfo_partitioned]
	ALTER TABLE [dbo].[tbOnAccountCollectedSaleIgnoreList_partitioned] DROP CONSTRAINT [FK_tbOnAccountCollectedSaleIgnoreList_partitioned_tbTransactionControlInfo_partitioned]
	ALTER TABLE [dbo].[tbOnAccountSalePayment_partitioned] DROP CONSTRAINT [FK_tbOnAccountSalePayment_partitioned_tbTransactionControlInfo_partitioned]


	-- 3.1 : calculate partition number for lower boundary date
	SELECT @lowerBoundaryPartitionNum = MAX(p.partition_number) - 1
	FROM sys.partitions AS p
		INNER JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
		INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
		INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
		LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1
	WHERE p.[object_id] = OBJECT_ID('tbTransactionControlInfo_partitioned')
		AND prv_left.[value] <= @newLowerBoundaryDate

	print '@newLowerBoundaryDate = ' + cast(@newLowerBoundaryDate as varchar)
	print '@lowerBoundaryPartitionNum = ' + cast(@lowerBoundaryPartitionNum as varchar)

	-- 3.2 : delete the table data
	TRUNCATE TABLE [dbo].[tbTransactionControlInfo_partitioned] WITH (PARTITIONS (1 TO @lowerBoundaryPartitionNum))
	PRINT 'TRUNCATE TABLE [dbo].[tbTransactionControlInfo_partitioned] WITH (PARTITIONS (1 TO ' + CAST(@lowerBoundaryPartitionNum AS VARCHAR) + '))'


	-- 4 : recreate foreign key constraints
	ALTER TABLE [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbCustomerOnAccountBalanceEvent_partitioned_tbTransactionControlInfo_partitioned] FOREIGN KEY([BusinessDay], [TransactionControlInfoId])
	REFERENCES [dbo].[tbTransactionControlInfo_partitioned] ([BusinessDay], [TransactionControlInfoID])

	ALTER TABLE [dbo].[tbOnAccountCollectedSaleIgnoreList_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbOnAccountCollectedSaleIgnoreList_partitioned_tbTransactionControlInfo_partitioned] FOREIGN KEY([BusinessDay], [TransactionControlInfoId])
	REFERENCES [dbo].[tbTransactionControlInfo_partitioned] ([BusinessDay], [TransactionControlInfoID])

	ALTER TABLE [dbo].[tbOnAccountSalePayment_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbOnAccountSalePayment_partitioned_tbTransactionControlInfo_partitioned] FOREIGN KEY([BusinessDay], [TransactionControlInfoID])
	REFERENCES [dbo].[tbTransactionControlInfo_partitioned] ([BusinessDay], [TransactionControlInfoID])

	
	DECLARE @partitionsToRemove TABLE(partitionBoundary VARCHAR(50), isProcessed BIT DEFAULT 0)

	-- 5.1 : get a list of the partition boundaries to remove
	INSERT INTO @partitionsToRemove(partitionBoundary)
	SELECT CAST(prv_right.[value] AS DATE)
	FROM sys.partitions AS p
		INNER JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
		INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
		INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
		LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
	WHERE p.[object_id] = OBJECT_ID('tbTransactionControlInfo_partitioned')
		AND p.[partition_number] > 1 -- exclude the very first empty partition
		AND prv_right.[value] < @newLowerBoundaryDate

	-- 5.2 : merge away the partitions one by one
	WHILE EXISTS(SELECT 1 FROM @partitionsToRemove WHERE isProcessed = 0)
	BEGIN
		DECLARE @partitionToRemove VARCHAR(50) = (SELECT TOP 1 partitionBoundary FROM @partitionsToRemove WHERE isProcessed = 0)

		ALTER PARTITION SCHEME [TransactionControlInfoAggPS1] NEXT USED [PRIMARY]; -- TODO: CHECK FILEGROUP
		ALTER PARTITION FUNCTION TransactionControlInfoAggPF1() MERGE RANGE (@partitionToRemove)
		
		PRINT 'ALTER PARTITION FUNCTION TransactionControlInfoAggPF1() MERGE RANGE (''' + CAST(@partitionToRemove AS VARCHAR)  + ''');'
		
		DECLARE @now DATETIME = GETDATE()
		DECLARE @details VARCHAR(MAX) = '[dbo].[tbTransactionControlInfo_partitioned] partition dropped = ' + CAST(@partitionToRemove AS VARCHAR)
		EXECUTE qryMMSAudit_Insert @now, 4, 370, 'Table Partition Maintenance', @details, 'System', 'System', 0 , 'System', 0, NULL, NULL, NULL, 0
		PRINT @details

		UPDATE @partitionsToRemove SET isProcessed = 1 WHERE partitionBoundary = @partitionToRemove
	END

END
GO
