SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DECLARE @CalculateOnly INTEGER
DECLARE @TimeoutSeconds INTEGER
SET @CalculateOnly = 0 -- used by DAPA in DigitaExtensions\Alerts\DatabaseCleanup\Tasks\RemoveDeletedItemsTask.cs
SET @TimeoutSeconds = 10800 -- Change the Max Time Limit Duration here
 
DECLARE @Started DATETIME
Set @Started= GETDATE()
DECLARE @HasTimedOut BIT
Set @HasTimedOut = 0
 
DECLARE @MarkedPeriods TABLE ([ID] UNIQUEIDENTIFIER)
DECLARE @MarkedVersionedTemplates TABLE ([VersionedTemplateID] UNIQUEIDENTIFIER)
DECLARE @MarkedClients TABLE ([ID] UNIQUEIDENTIFIER)

BEGIN TRANSACTION

/*******************************************************************************/
/****************************Collect things to delete Phase*********************/
/*******************************************************************************/

/***Collect Clients to delete***/
INSERT @MarkedClients ([ID])
	SELECT [ID] FROM [dbo].[Private_Client] WHERE [Deleted] = 1 

INSERT @MarkedClients ([ID]) -- Include clients who have no period.
	SELECT c.[ID]
	FROM   Private_Client c
		   LEFT JOIN Private_Period p
				  ON c.[ID] = p.ClientID
	WHERE  p.[ID] IS NULL AND c.[ID] NOT IN (SELECT [ID] FROM @MarkedClients);


/***Collect Periods to delete***/
INSERT @MarkedPeriods ([ID])
	SELECT [ID] FROM [dbo].[Private_Period] WHERE [Deleted] = 1

INSERT @MarkedPeriods ([ID]) -- Include periods where they don't have a versioned template or it's under construction
	SELECT p.[ID]
	FROM   Private_Period p
		   LEFT JOIN VersionedTemplate
				  ON VersionedTemplate.ParentPeriodID = p.[ID]
	WHERE  ([VersionedTemplateID] IS NULL or [IsUnderConstruction] = 1) AND p.[ID] NOT IN (SELECT [ID] FROM @MarkedPeriods);

INSERT @MarkedPeriods ([ID]) -- Include periods that have their clients about to be deleted
  SELECT pv.[ID] FROM [dbo].[Private_Period] pv
	WHERE pv.ClientID IN (SELECT [ID] FROM @MarkedClients) AND pv.[ID] NOT IN (SELECT [ID] FROM @MarkedPeriods);

/***Collect Versioned Templates to delete***/
INSERT @MarkedVersionedTemplates ([VersionedTemplateID])
  SELECT [VersionedTemplateID] FROM [dbo].[Private_VersionedTemplate] WHERE [Deleted] = 1 and [Published]=0

-- Include Templates that are:
--		1. under construction. 
--		or 
--		2. They're not published and they either have no period or that period is about to be deleted.
INSERT @MarkedVersionedTemplates ([VersionedTemplateID]) 
  SELECT VersionedTemplateID
  FROM   Private_VersionedTemplate
  LEFT JOIN Period  p on p.[ID] = Private_VersionedTemplate.ParentPeriodID
  LEFT JOIN @MarkedPeriods aboutToBeDeleted on aboutToBeDeleted.[ID] = Private_VersionedTemplate.ParentPeriodID
  WHERE  (((ParentPeriodID is null or ParentPeriodID = aboutToBeDeleted.[ID])
         AND Published = 0) or p.IsUnderConstruction = 1) AND VersionedTemplateID NOT IN  (SELECT VersionedTemplateID FROM @MarkedVersionedTemplates);
 
COMMIT TRANSACTION

DECLARE @TotalOperationsCount as int;
SELECT @TotalOperationsCount = (SELECT COUNT(*) FROM @MarkedClients) + (SELECT COUNT(*) FROM @MarkedPeriods) + (SELECT COUNT(*) FROM @MarkedVersionedTemplates)
PRINT 'Total Operations: ' + cast(@TotalOperationsCount as nvarchar(max))
RAISERROR ('', 10, 1) WITH NOWAIT; --force message to be sent now

if (@CalculateOnly = 1) -- used by DAPA in DigitaExtensions\Alerts\DatabaseCleanup\Tasks\RemoveDeletedItemsTask.cs
BEGIN
	select @TotalOperationsCount;
	RETURN;
END

/*******************************************************************************/
/****************************Start the delete Phase*****************************/
/*******************************************************************************/

DECLARE @CurrentOperationNumber as int;
SET @CurrentOperationNumber = 0;

DECLARE @VersionedTemplateID UNIQUEIDENTIFIER

--Break links between comparative periods that are to be deleted via the client to be deleted. Same table references
--will get in the way otherwise...
UPDATE NH_ComparativePeriod SET SourceComparativePeriodId=NULL WHERE PeriodId in 
(
       SELECT ID FROM @MarkedPeriods
)

UPDATE innerCP SET innerCP.SourceComparativePeriodId = NULL FROM NH_ComparativePeriod outerCP join NH_ComparativePeriod innerCP on outerCP.Id = innerCP.SourceComparativePeriodId
WHERE outerCP.PeriodId in 
(
       SELECT ID FROM @MarkedPeriods
)


DECLARE [VersionedTemplateCursor] CURSOR LOCAL FAST_FORWARD FOR
  SELECT [VersionedTemplateID] FROM @MarkedVersionedTemplates
OPEN [VersionedTemplateCursor]
FETCH FROM [VersionedTemplateCursor] INTO @VersionedTemplateID
WHILE @@FETCH_STATUS = 0 AND @HasTimedOut = 0
BEGIN
	SET @CurrentOperationNumber = @CurrentOperationNumber + 1;
	PRINT convert(varchar(8), getdate(), 108) + ': ' + 'Operation ' + cast(@CurrentOperationNumber as nvarchar(max)) + ' of ' + cast(@TotalOperationsCount as nvarchar(max))
	
	PRINT 'Calling [UncheckedDeleteVersionedTemplate] ' + cast(@VersionedTemplateID as nvarchar(max));
	RAISERROR ('', 10, 1) WITH NOWAIT; --force message to be sent now

	EXEC [UncheckedDeleteVersionedTemplate] @VersionedTemplateID
	FETCH FROM [VersionedTemplateCursor] INTO @VersionedTemplateID
	IF (@TimeoutSeconds > 0 AND DATEDIFF (SECOND,@Started,GetDate())>= @TimeoutSeconds)
	BEGIN
	Set @HasTimedOut = 1
	END
END
CLOSE [VersionedTemplateCursor]
DEALLOCATE [VersionedTemplateCursor]


DECLARE @PeriodID UNIQUEIDENTIFIER
 
DECLARE [PeriodCursor] CURSOR LOCAL FAST_FORWARD FOR
  SELECT [ID] FROM @MarkedPeriods
OPEN [PeriodCursor]
FETCH FROM [PeriodCursor] INTO @PeriodID
WHILE @@FETCH_STATUS = 0 AND @HasTimedOut = 0
BEGIN
	SET @CurrentOperationNumber = @CurrentOperationNumber + 1;
	PRINT convert(varchar(8), getdate(), 108) + ': ' + 'Operation ' + cast(@CurrentOperationNumber as nvarchar(max)) + ' of ' + cast(@TotalOperationsCount as nvarchar(max))

	--We need to delete the comparative period first as there is no
	--cascade delete.
	PRINT 'Deleting period ' + cast(@PeriodID as nvarchar(max));
	RAISERROR ('', 10, 1) WITH NOWAIT; --force message to be sent now

	UPDATE NH_ComparativePeriod set SourcePeriodId = NULL WHERE SourcePeriodId = @PeriodID
	DELETE NH_ComparativePeriod WHERE PeriodId = @PeriodID
	DELETE [dbo].[Private_Period] WHERE [ID] = @PeriodID
	FETCH FROM [PeriodCursor] INTO @PeriodID
	IF (@TimeoutSeconds > 0 AND DATEDIFF (SECOND,@Started,GetDate())>= @TimeoutSeconds)
	BEGIN
	Set @HasTimedOut = 1
	END
END
CLOSE [PeriodCursor]
DEALLOCATE [PeriodCursor]

DECLARE @ClientID UNIQUEIDENTIFIER

DECLARE [ClientCursor] CURSOR LOCAL FAST_FORWARD FOR
  SELECT [ID] FROM @MarkedClients
OPEN [ClientCursor]
FETCH FROM [ClientCursor] INTO @ClientID
WHILE @@FETCH_STATUS = 0 AND @HasTimedOut = 0
BEGIN
	SET @CurrentOperationNumber = @CurrentOperationNumber + 1;
	PRINT convert(varchar(8), getdate(), 108) + ': ' + 'Operation ' + cast(@CurrentOperationNumber as nvarchar(max)) + ' of ' + cast(@TotalOperationsCount as nvarchar(max))

	PRINT 'Deleting [Private_Client] ' + cast(@ClientID as nvarchar(max));
	RAISERROR ('', 10, 1) WITH NOWAIT; --force message to be sent now

	DELETE [dbo].[Private_Client]  WHERE [ID] = @ClientID
	FETCH FROM [ClientCursor] INTO @ClientID
	IF (@TimeoutSeconds > 0 AND DATEDIFF (SECOND,@Started,GetDate())>= @TimeoutSeconds)
	BEGIN
	Set @HasTimedOut = 1
	END
END
CLOSE [ClientCursor]
DEALLOCATE [ClientCursor]





IF(@HasTimedOut = 1)
	PRINT 'Operation was stopped before finishing as maximum script running time was reached ' +  convert(varchar(8), getdate(), 108);
ELSE
	PRINT 'Finished at ' +  convert(varchar(8), getdate(), 108);


SET NOCOUNT OFF