USE [HeuristicLab.Hive-3.3]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/* remove statistics from trigger */
ALTER TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS 
BEGIN
	SELECT 
		he.OwnerUserId AS UserId, 
		ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS,  
		ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs,
		ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS
	FROM 
		deleted j, 
		Job he, 
		view_FirstState fs, 
		view_LastState ls
	WHERE 
		he.JobId = j.JobId AND 
		fs.TaskId = j.TaskId AND 
		ls.TaskId = j.TaskId
	GROUP BY he.OwnerUserId

	-- recursively delete jobs
	CREATE TABLE #Table( 
		TaskId uniqueidentifier 
	)
	INSERT INTO #Table (TaskId)
	SELECT TaskId FROM deleted
	
	DECLARE @c INT
	SET @c = 0
	
	WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
		SELECT @c = COUNT(TaskId) FROM #Table
		
		INSERT INTO #Table (TaskId)
			SELECT Task.TaskId
			FROM Task
			LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
			WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
				AND #Table.TaskId IS NULL
	END
	
	DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
	DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
END
GO

/* drop obsolete triggers/views/tables */
DROP TRIGGER [dbo].[tr_StatisticsDeleteCascade]
GO

DROP VIEW [dbo].[view_Statistics]
GO

DROP TABLE [dbo].[DeletedJobStatistics]
GO

DROP TABLE [dbo].[UserStatistics]
GO

DROP TABLE [dbo].[SlaveStatistics]
GO

DROP TABLE [dbo].[Statistics]
GO