USE [EnVisage]
GO

ALTER PROCEDURE [dbo].[sp_DeleteProject] (@id uniqueidentifier) AS
	BEGIN
		DECLARE @ProjectPartsCount int
		DECLARE @ScenarioId uniqueidentifier
		DECLARE @ProjectPartId uniqueidentifier

		SELECT @ProjectPartsCount = COUNT(1) FROM Project WHERE ParentProjectId = @id

		IF (@ProjectPartsCount > 0)
		BEGIN
			-- Delete project parts (if has any)
		DECLARE PartsToDelete CURSOR FOR
			SELECT Id from Project where ParentProjectId = @id
			FOR READ ONLY

			--PRINT 'Parts for project found: ' + STR(@ProjectPartsCount);
			OPEN PartsToDelete;
			FETCH NEXT FROM PartsToDelete INTO @ProjectPartId;
			WHILE @@FETCH_STATUS = 0
			BEGIN
				--PRINT 'Deleting part ' + CAST(@ProjectPartId AS VARCHAR(50));
				EXEC sp_DeleteProject @ProjectPartId
				FETCH NEXT FROM PartsToDelete INTO @ProjectPartId;
			END;
		
			CLOSE PartsToDelete;
			DEALLOCATE PartsToDelete;
			--PRINT 'All parts deleted';
		END

		-- Delete scenarios
		DECLARE ScenariosToDelete CURSOR FOR
			SELECT Id from Scenario where ParentId = @id
			FOR READ ONLY

		--PRINT 'Deleting scenarios for ' + CAST(@id AS VARCHAR(50)) + '...';
		OPEN ScenariosToDelete;
		FETCH NEXT FROM ScenariosToDelete INTO @ScenarioId;
		WHILE @@FETCH_STATUS = 0
		BEGIN
			--PRINT 'Deleting scenario ' + CAST(@ScenarioId AS VARCHAR(50));
			EXEC sp_DeleteScenario @ScenarioId
			FETCH NEXT FROM ScenariosToDelete INTO @ScenarioId;
		END;
		
		CLOSE ScenariosToDelete;
		DEALLOCATE ScenariosToDelete;
		--PRINT 'All scenarios deleted';
		
		--PRINT 'Clearing tables fro project ' + CAST(@Id AS VARCHAR(50));
		--PRINT 'Deleting from [Team2Project]';
		delete from Team2Project where ProjectId =@Id
		--PRINT 'Deleting from [Contact2Project]';
		delete from Contact2Project where ShowId =@Id
		--PRINT 'Deleting from [ProjectAccess]';
		delete from ProjectAccess where ProjectId =@Id
		--PRINT 'Deleting from [Project]';
		delete from Project where id = @id
 	END
GO