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

CREATE PROCEDURE [dbo].[MXC_Partitioning_tbTransactionControlInfo_UpdateScheme]
AS
BEGIN
    
    DECLARE @nextYearLastPartitionDate DATE = DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()) + 1, 12, 1))
    DECLARE @newBoundary DATE

    SELECT @newBoundary = DATEADD(MONTH, 1, MAX(CONVERT(DATE, prv.[value])))
    FROM sys.partition_schemes ps
		INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id
		INNER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
		INNER JOIN sys.partition_range_values prv ON prv.boundary_id = dds.destination_id AND prv.function_id = ps.function_id
    WHERE ps.[name] = 'TransactionControlInfoAggPS1' AND prv.[value] IS NOT NULL

	print '@nextYearLastPartitionDate = ' + cast(@nextYearLastPartitionDate as varchar)
	print '@newBoundary = ' + cast(@newBoundary as varchar)

    -- ensure that we keep future partitions available
    WHILE @newBoundary <= @nextYearLastPartitionDate
    BEGIN
		ALTER PARTITION SCHEME [TransactionControlInfoAggPS1] NEXT USED [PRIMARY]; -- TODO: CHECK FILEGROUP
	    ALTER PARTITION FUNCTION TransactionControlInfoAggPF1() SPLIT RANGE (@newBoundary);

		print 'ALTER PARTITION FUNCTION TransactionControlInfoAggPF1() SPLIT RANGE (' + cast(@newBoundary as varchar) + ');'

        -- log partition update details
		DECLARE @now DATETIME = GETDATE()
        DECLARE @details VARCHAR(MAX) = '[dbo].[tbTransactionControlInfo_partitioned] new partition added = ' + CAST(@newBoundary AS VARCHAR(50))
        EXECUTE qryMMSAudit_Insert @now, 4, 370, 'Table Partition Maintenance', @details, 'System', 'System', 0 , 'System', 0, NULL, NULL, NULL, 0
		PRINT @details

        SET @newBoundary = DATEADD(MONTH, 1, @newBoundary)
    END

END
GO
