SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Post_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Post_Delete]
GO

CREATE PROCEDURE dbo.cs_Post_Delete
(
	@SectionID int,
	@SettingsID int,
	@PostID int,
	@ResetStatistics bit = 1,
	@DeletedBy INT,
	@Reason NVARCHAR(1024) = ''
)
AS
-- Permanently deletes one post at a time and eventually its thread.
-- If the post has some replies then subsequent calls are made till all the tree's nodes and leafs are removed.
-- Also if the post is level 1 then we can delete the thread itself without any other checkings.

SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @PostLevel INT
DECLARE @ThreadID INT
DECLARE @HasReplies INT

-- Figure out if this is a thread starter post, who is its thread and if it has some replies
--------------------------------------------------------------------------------
SELECT 
	@PostLevel = PostLevel,
	@ThreadID = ThreadID	
FROM 
	cs_Posts 
WHERE 
	PostID = @PostID AND
	SectionID = @SectionID AND
	SettingsID = @SettingsID

SET @HasReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ParentID = @PostID AND SectionID = @SectionID AND SettingsID = @SettingsID)
--------------------------------------------------------------------------------

-- Is a starter thread post?
IF (@PostLevel = 1)
BEGIN
	-- Easy job: delete the thread and its content with statistics update
	--------------------------------------------------------------------------------
	EXEC cs_Thread_Delete @SettingsID, @SectionID, @ThreadID, @ResetStatistics, @DeletedBy, ''

	RETURN 1
	--------------------------------------------------------------------------------
END
ELSE
BEGIN
	-- This is not a thread starter post, so we have to delete it carefully

	-- Delete parent post
	--------------------------------------------------------------------------------	
	DELETE FROM cs_PostAttachments WHERE PostID = @PostID AND SettingsID = @SettingsID
	
	DELETE FROM cs_PostEditNotes WHERE PostID = @PostID AND SettingsID = @SettingsID
	
	DELETE FROM cs_Posts_InCategories WHERE PostID = @PostID AND SettingsID = @SettingsID
		
	DELETE FROM cs_PostsArchive WHERE PostID = @PostID AND SettingsID = @SettingsID
	
	DELETE FROM cs_SearchBarrel WHERE SectionID = @SectionID  AND ThreadID = @ThreadID AND PostID = @PostID AND SettingsID = @SettingsID
	
	DELETE FROM cs_Posts WHERE PostID = @PostID AND ThreadID = @ThreadID AND SectionID = @SectionID AND SettingsID = @SettingsID
	
	IF (@Reason IS NULL OR @Reason = '')
		SET @Reason = 'Automatic generated reason: the post has been permanently deleted on request.'

	EXEC cs_system_ModerationAction_AuditEntry 4, @DeletedBy, @PostID, null, null, @SettingsID, @Reason	
	--------------------------------------------------------------------------------	

	-- Do we have some replies?
	--------------------------------------------------------------------------------	
	IF (@HasReplies > 0)
	BEGIN
		-- There should be some replies/children bound to this post.
		-- Get replying posts one by one through a local cursor and call its removal.

		DECLARE @CurrentPostID INT

		DECLARE Posts_Cursor CURSOR LOCAL FORWARD_ONLY FOR
		SELECT 
			PostID 
		FROM
			cs_Posts
		WHERE
			ParentID = @PostID AND
			ThreadID = @ThreadID AND
			SectionID = @SectionID AND
			SettingsID = @SettingsID

		OPEN Posts_Cursor
		
		FETCH NEXT FROM Posts_Cursor INTO @CurrentPostID

		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			-- Recursively call with no statistics update
			EXEC cs_Post_Delete @SectionID, @SettingsID, @CurrentPostID, 0, @DeletedBy, @Reason

			FETCH NEXT FROM Posts_Cursor INTO @CurrentPostID
		END

		CLOSE Posts_Cursor

		DEALLOCATE Posts_Cursor		
	END
	--------------------------------------------------------------------------------	
	
	-- Update statistics?
	--------------------------------------------------------------------------------	
	IF (@ResetStatistics = 1)
	BEGIN
		EXEC cs_system_ResetThreadStatistics @ThreadID
		EXEC cs_system_ResetForumStatistics @SectionID
	END
	--------------------------------------------------------------------------------	

	RETURN 1
END

END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


grant execute on [dbo].[cs_Post_Delete] to public
go