
USE CFADEV
GO

/**********************************************************************************************************************************************************/
-- reset


IF EXISTS (SELECT * FROM sys.partition_schemes WHERE [name] = 'TransactionControlInfoAggPS1')
	DROP PARTITION SCHEME [TransactionControlInfoAggPS1]

IF EXISTS (SELECT * FROM sys.partition_functions WHERE [name] = 'TransactionControlInfoAggPF1')
	DROP PARTITION FUNCTION [TransactionControlInfoAggPF1]

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]

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]

/**********************************************************************************************************************************************************/
-- partition by month, for 2019 and 2020. may need to adjust depending on what data is available.

CREATE PARTITION FUNCTION [TransactionControlInfoAggPF1](SMALLDATETIME) AS RANGE RIGHT FOR VALUES (
	N'2000-01-01T00:00:00', --> empty partition at the beginning
	N'2019-01-01T00:00:00', N'2019-02-01T00:00:00', N'2019-03-01T00:00:00', N'2019-04-01T00:00:00',
	N'2019-05-01T00:00:00', N'2019-06-01T00:00:00', N'2019-07-01T00:00:00', N'2019-08-01T00:00:00',
	N'2019-09-01T00:00:00', N'2019-10-01T00:00:00', N'2019-11-01T00:00:00', N'2019-12-01T00:00:00',
	N'2020-01-01T00:00:00', N'2020-02-01T00:00:00', N'2020-03-01T00:00:00', N'2020-04-01T00:00:00',
	N'2020-05-01T00:00:00', N'2020-06-01T00:00:00', N'2020-07-01T00:00:00', N'2020-08-01T00:00:00',
	N'2020-09-01T00:00:00', N'2020-10-01T00:00:00', N'2020-11-01T00:00:00', N'2020-12-01T00:00:00',
	N'2021-01-01T00:00:00') --> empty partition at the end

/**********************************************************************************************************************************************************/
-- create scheme (all to same filegroup for now)

CREATE PARTITION SCHEME [TransactionControlInfoAggPS1] AS PARTITION [TransactionControlInfoAggPF1] TO (
	[PRIMARY],
	[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
	[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
	[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
	[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
	[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
	[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
	[PRIMARY], [PRIMARY])

/**********************************************************************************************************************************************************/
-- create partitioned versions of tables

/****** Object:  Table [dbo].[tbTransactionControlInfo]    Script Date: 2020-07-22 09:48:56 AM ******/
CREATE TABLE [dbo].[tbTransactionControlInfo_partitioned] (
	[TransactionControlInfoID] [bigint] IDENTITY(1,1) NOT NULL,
	[ControlInfoID] [bigint] NULL,
	[EntityID] [bigint] NULL,
	[EntityTypeID] [bigint] NULL,
	[OwnerEntityID] [bigint] NULL,
	[FiscalYear] [varchar](10) NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[PeriodNo] [int] NULL,
	[TransactionDate] [datetime] NULL,
	[RegisterNumber] [int] NULL,
	[ClerkName] [varchar](150) NULL,
	[ControlInfoAmount] [money] NULL,
	[ControlInfoCount] [float] NULL,
	[PolledDataID] [bigint] NULL,
	[POSTransactionID] [bigint] NULL,
	[OtherInfo1] [varchar](50) NULL,
	[CurrentDayNumber] [bigint] NULL,
	[CurrentShiftNumber] [bigint] NULL,
	[OtherInfo2] [varchar](50) NULL,
	[OtherInfo3] [varchar](50) NULL,
	[SystemTransactionID] [bigint] NULL CONSTRAINT [DF_tbTransactionControlInfo_partitioned_SystemTransactionID] DEFAULT (0),
	[DateAdded] [datetime] NULL,
	[BusinessDay] [smalldatetime] NOT NULL,
	[ClerkID] [int] NULL,
	CONSTRAINT [PK_tbTransactionControlInfo_partitioned] PRIMARY KEY CLUSTERED ([BusinessDay] ASC, [TransactionControlInfoID] ASC) --> BusinessDay FIRST
		WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON TransactionControlInfoAggPS1(BusinessDay) --> on partition scheme
) ON TransactionControlInfoAggPS1(BusinessDay) --> on partition scheme
GO

/********************************************************/

/****** Object:  Table [dbo].[tbCustomerOnAccountBalanceEvent]    Script Date: 2020-07-22 09:53:05 AM ******/
CREATE TABLE [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] (
	[CustomerOnAccountBalanceEventId] [bigint] IDENTITY(1,1) NOT NULL,
	[CustomerId] [bigint] NOT NULL,
	[AppliedAmount] [money] NOT NULL,
	[EventTimestamp] [datetime] NOT NULL,
	[CreditAdjustmentReason] [int] NULL,
	[Comments] [varchar](250) NULL,
	[PreviousCustomerBalance] [money] NULL,
	[NewCustomerBalance] [money] NULL,
	[Type] [int] NULL,
	[OnAccountSaleId] [bigint] NULL,
	[TransactionControlInfoId] [bigint] NULL,
	[TransactionPettyCashID] [bigint] NULL,
	[PaidOutAmount] [money] NULL,
	[BusinessDay] [smalldatetime] NOT NULL,
	CONSTRAINT [PK_tbCustomerOnAccountBalanceEvent_partitioned] PRIMARY KEY CLUSTERED ([CustomerOnAccountBalanceEventId] ASC)
		WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbCustomerOnAccountBalanceEvent_partitioned_tbOnAccountSale] FOREIGN KEY([OnAccountSaleId])
REFERENCES [dbo].[tbOnAccountSale] ([OnAccountSaleId])
GO

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

ALTER TABLE [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbCustomerOnAccountBalanceEvent_partitioned_tbTransactionPettyCash] FOREIGN KEY([TransactionPettyCashID])
REFERENCES [dbo].[tbTransactionPettyCash] ([TransactionPettyCashID])
GO

/********************************************************/

/****** Object:  Table [dbo].[tbOnAccountCollectedSaleIgnoreList]    Script Date: 2020-07-22 09:47:50 AM ******/
CREATE TABLE [dbo].[tbOnAccountCollectedSaleIgnoreList_partitioned] (
	[EntityID] [bigint] NOT NULL,
	[TransactionControlInfoId] [bigint] NOT NULL,
	[CustomerID] [nvarchar](50) NULL,
	[UsedForCreditFlag] [bit] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[UserID] [bigint] NOT NULL,
	[BusinessDay] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbOnAccountCollectedSaleIgnoreList_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbOnAccountCollectedSaleIgnoreList_partitioned_tbEntity] FOREIGN KEY([EntityID])
REFERENCES [dbo].[tbEntity] ([EntityID])
GO

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

/********************************************************/

/****** Object:  Table [dbo].[tbOnAccountSalePayment]    Script Date: 2020-07-22 10:06:55 AM ******/
CREATE TABLE [dbo].[tbOnAccountSalePayment_partitioned] (
	[OnAccountSalePaymentId] [bigint] IDENTITY(1,1) NOT NULL,
	[OnAccountSaleId] [bigint] NOT NULL,
	[TransactionControlInfoID] [bigint] NULL,
	[AppliedAmount] [money] NULL,
	[CheckNumber] [varchar](15) NULL,
	[CreditAppliedFlag] [bit] NULL,
	[IsDeposit] [bit] NOT NULL CONSTRAINT [DF_tbOnAccountSalePayment_partitioned_IsDeposit] DEFAULT (0),
	[CreatedTimestamp] [datetime] NULL,
	[BusinessDay] [smalldatetime] NOT NULL,
	CONSTRAINT [PK_tbOnAccountSalesPayment_partitioned] PRIMARY KEY CLUSTERED ([OnAccountSalePaymentId] ASC)
		WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbOnAccountSalePayment_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbOnAccountSalePayment_partitioned_tbOnAcocuntSale] FOREIGN KEY([OnAccountSaleId])
REFERENCES [dbo].[tbOnAccountSale] ([OnAccountSaleId])
GO

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

/**********************************************************************************************************************************************************/
/**********************************************************************************************************************************************************/
-- move some data

DECLARE @cols1 VARCHAR(MAX) = (select '[' + [name] + '], ' as [text()] from sys.columns where object_id = object_id('tbTransactionControlInfo') for xml path(''))
SET @cols1 = LEFT(@cols1, LEN(@cols1) - 1)

DECLARE @sql1 NVARCHAR(MAX) = '
SET IDENTITY_INSERT [dbo].[tbTransactionControlInfo_partitioned] ON

INSERT INTO [dbo].[tbTransactionControlInfo_partitioned](' + @cols1 + ')
SELECT TOP 1000000 ' + REPLACE(@cols1, '[', 'a.[') + ' FROM [dbo].[tbTransactionControlInfo] a
WHERE a.[BusinessDay] >= ''2018-12-01'' AND a.[SystemTransactionID] % 19 = 0 -- % 19 to gt a bit more of a spread of transactions

SET IDENTITY_INSERT [dbo].[tbTransactionControlInfo_partitioned] OFF

/********************************************************/
'

PRINT @sql1
EXEC sp_executesql @sql1

/********************************************************/

DECLARE @cols2 VARCHAR(MAX) = (select '[' + [name] + '], ' as [text()] from sys.columns where object_id = object_id('tbCustomerOnAccountBalanceEvent') for xml path(''))
SET @cols2 = LEFT(@cols2, LEN(@cols2) - 1)

DECLARE @sql2 NVARCHAR(MAX) = '
SET IDENTITY_INSERT [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] ON

INSERT INTO [dbo].[tbCustomerOnAccountBalanceEvent_partitioned](' + @cols2 + ', [BusinessDay])
SELECT ' + REPLACE(@cols2, '[', 'a.[') + ', tci_p.[BusinessDay] FROM [dbo].[tbCustomerOnAccountBalanceEvent] a
	INNER JOIN tbTransactionControlInfo_partitioned tci_p on tci_p.TransactionControlInfoID = a.TransactionControlInfoId

SET IDENTITY_INSERT [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] OFF

/********************************************************/
'

PRINT @sql2
EXEC sp_executesql @sql2

/********************************************************/

DECLARE @cols3 VARCHAR(MAX) = (select '[' + [name] + '], ' as [text()] from sys.columns where object_id = object_id('tbOnAccountCollectedSaleIgnoreList') for xml path(''))
SET @cols3 = LEFT(@cols3, LEN(@cols3) - 1)

DECLARE @sql3 NVARCHAR(MAX) = '
INSERT INTO [dbo].[tbOnAccountCollectedSaleIgnoreList_partitioned](' + @cols3 + ', [BusinessDay])
SELECT ' + REPLACE(@cols3, '[', 'a.[') + ', tci_p.[BusinessDay] FROM [dbo].[tbOnAccountCollectedSaleIgnoreList] a
	INNER JOIN tbTransactionControlInfo_partitioned tci_p on tci_p.TransactionControlInfoID = a.TransactionControlInfoId

/********************************************************/
'

PRINT @sql3
EXEC sp_executesql @sql3

/********************************************************/

DECLARE @cols4 VARCHAR(MAX) = (select '[' + [name] + '], ' as [text()] from sys.columns where object_id = object_id('tbOnAccountSalePayment') for xml path(''))
SET @cols4 = LEFT(@cols4, LEN(@cols4) - 1)

DECLARE @sql4 NVARCHAR(MAX) = '
SET IDENTITY_INSERT [dbo].[tbOnAccountSalePayment_partitioned] ON

INSERT INTO [dbo].[tbOnAccountSalePayment_partitioned](' + @cols4 + ', [BusinessDay])
SELECT ' + REPLACE(@cols4, '[', 'a.[') + ', tci_p.[BusinessDay] FROM [dbo].[tbOnAccountSalePayment] a
	INNER JOIN tbTransactionControlInfo_partitioned tci_p on tci_p.TransactionControlInfoID = a.TransactionControlInfoId

SET IDENTITY_INSERT [dbo].[tbOnAccountSalePayment_partitioned] OFF

/********************************************************/
'

PRINT @sql4
EXEC sp_executesql @sql4

/********************************************************/

/*

select count(*), min(BusinessDay), max(BusinessDay) from [dbo].[tbTransactionControlInfo_partitioned]
select count(*), min(BusinessDay), max(BusinessDay) from [dbo].[tbTransactionControlInfo]

select top 100 * from [dbo].[tbTransactionControlInfo_partitioned]
select top 100 * from [dbo].[tbCustomerOnAccountBalanceEvent_partitioned]
select top 100 * from [dbo].[tbOnAccountCollectedSaleIgnoreList_partitioned]
select top 100 * from [dbo].[tbOnAccountSalePayment_partitioned]

*/

/**********************************************************************************************************************************************************/
-- adjust indexes

---- create clustered index on partition key column (on new partition scheme)
--CREATE CLUSTERED INDEX IX_tbTransactionControlInfo_partitioned_BusinessDay ON [dbo].[tbTransactionControlInfo_partitioned](BusinessDay)
--WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON TransactionControlInfoAggPS1(BusinessDay)


--ALTER TABLE [dbo].[tbOnAccountSalePayment_partitioned] DROP CONSTRAINT [FK_tbOnAccountSalePayment_partitioned_TransactionControlInfo_partitioned] WITH (ONLINE = OFF)
--GO

--ALTER TABLE [dbo].[tbOnAccountCollectedSaleIgnoreList_partitioned] DROP CONSTRAINT [FK_tbOnAccountCollectedSaleIgnoreList_partitioned_tbTransactionControlInfo_partitioned] WITH (ONLINE = OFF)
--GO

--ALTER TABLE [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] DROP CONSTRAINT [FK_tbCustomerOnAccountBalanceEvent_partitioned_TransactionControlInfo_partitioned] WITH (ONLINE = OFF)
--GO

---- drop existing clustered index
--ALTER TABLE [dbo].[tbTransactionControlInfo_partitioned] DROP CONSTRAINT [PK_tbTransactionControlInfo_partitioned] WITH (ONLINE = OFF)
--GO

/**********************************************************************************************************************************************************/

---- recreate as NONCLUSTERED
--ALTER TABLE [dbo].[tbTransactionControlInfo_partitioned] ADD
--CONSTRAINT [PK_tbTransactionControlInfo_partitioned] PRIMARY KEY NONCLUSTERED ([TransactionControlInfoID] ASC)
--WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
--GO

/**********************************************************************************************************************************************************/

---- now create clustered index on partition key column (on new partition scheme)
--CREATE CLUSTERED INDEX IX_tbTransactionControlInfo_partitioned_BusinessDay ON [dbo].[tbTransactionControlInfo_partitioned](BusinessDay)
--WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON TransactionControlInfoAggPS1(BusinessDay)

--ALTER TABLE [dbo].[tbOnAccountSalePayment_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbOnAccountSalePayment_partitioned_TransactionControlInfo_partitioned] FOREIGN KEY([TransactionControlInfoID])
--REFERENCES [dbo].[tbTransactionControlInfo_partitioned] ([TransactionControlInfoID])
--GO

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

--ALTER TABLE [dbo].[tbCustomerOnAccountBalanceEvent_partitioned] WITH NOCHECK ADD CONSTRAINT [FK_tbCustomerOnAccountBalanceEvent_partitioned_TransactionControlInfo_partitioned] FOREIGN KEY([TransactionControlInfoId])
--REFERENCES [dbo].[tbTransactionControlInfo_partitioned] ([TransactionControlInfoID])
--GO


/**********************************************************************************************************************************************************/
-- paritioned table and index details

SELECT OBJECT_NAME(p.object_id) AS ObjectName,
	i.[name] AS IndexName,
	p.index_id AS IndexID,
	ds.[name] AS PartitionScheme,
	p.partition_number AS PartitionNumber,
	fg.[name] AS FileGroupName,
	prv_left.value AS LowerBoundaryValue,
	prv_right.value AS UpperBoundaryValue,
	CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS [Range],
	p.[rows] AS [RowCount]
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
	INNER JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
	INNER JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
	INNER JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.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
	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')
ORDER BY p.partition_number


/**********************************************************************************************************************************************************/
-- partition maintenance - create new partitions for next year)

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


/**********************************************************************************************************************************************************/
-- remove old partitions (for last year)

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


/**********************************************************************************************************************************************************/
-- perform the maintenance

exec [dbo].[MXC_Partitioning_tbTransactionControlInfo_UpdateScheme]

exec [dbo].[MXC_Partitioning_tbTransactionControlInfo_Purge]

/**********************************************************************************************************************************************************/

SELECT OBJECT_NAME(p.object_id) AS ObjectName,
	i.[name] AS IndexName,
	p.index_id AS IndexID,
	ds.[name] AS PartitionScheme,
	p.partition_number AS PartitionNumber,
	fg.[name] AS FileGroupName,
	prv_left.value AS LowerBoundaryValue,
	prv_right.value AS UpperBoundaryValue,
	CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS [Range],
	p.[rows] AS [RowCount]
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
	INNER JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
	INNER JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
	INNER JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.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
	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')
ORDER BY p.partition_number

/**********************************************************************************************************************************************************/


