SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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



CREATE       PROCEDURE [dbo].cs_Moderate_Thread_Split
(
	@PostID INT,
	@MoveToForum INT,
	@SplitBy INT,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @IsSticky BIT
DECLARE @StickyDate DATETIME
DECLARE @IsLocked BIT
DECLARE @NewThreadID INT
DECLARE @OldThreadID INT
DECLARE @UserID INT
DECLARE @PostAuthor NVARCHAR(64)
DECLARE @PostDate DATETIME
DECLARE @EmoticonID INT
DECLARE @TopPostLevel INT
DECLARE @TopSortOrder INT
DECLARE @TotalReplies INT
DECLARE	@MostRecentPostAuthor NVARCHAR(64)
DECLARE	@MostRecentPostAuthorID INT
DECLARE	@MostRecentPostID INT

-- Get details on the post
SELECT 
	@PostDate = PostDate,
	@UserID = UserID,
	@PostAuthor = PostAuthor,
	@IsSticky = 0,			-- shouldn't be a stickie when splitting
	@IsLocked = IsLocked,
	@StickyDate = GetDate(),
	@EmoticonID = EmoticonID,
	@OldThreadID = ThreadID		-- to delete later if no more replies
FROM 
	cs_Posts 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

BEGIN TRAN

-- Create a new thread by inserting
INSERT cs_Threads 	
	( SectionID,
	PostDate, 
	UserID, 
	PostAuthor, 
	ThreadDate, 
	MostRecentPostAuthor, 
	MostRecentPostAuthorID, 	
	MostRecentPostID, 
	IsLocked, 
	IsApproved,
	IsSticky, 
	StickyDate, 
	ThreadEmoticonID,
	SettingsID )
VALUES
	( @MoveToForum, 	-- the forum we are moving to
	@PostDate, 
	@UserID, 
	@PostAuthor,
	@PostDate,
	@PostAuthor,	-- Dummy data until we move all posts below
	@UserID, 	-- Dummy data until we move all posts below
	0,		-- MostRecentPostID, which we don't know yet.
	@IsLocked,
	1,		-- Wouldn't be shown in the forum unless it wasn't approved already.
	@IsSticky,
	@StickyDate,
	@EmoticonID,
	@SettingsID )

SELECT 
	@NewThreadID = @@IDENTITY
FROM
	cs_Threads


-- Update post
UPDATE	cs_Posts
SET		PostLevel = 1,	-- this is now a thread starter
		IsApproved = 1	-- by splitting, if it wasn't approved, it is now
WHERE	PostID = @PostID AND SettingsID = @SettingsID

UPDATE
	cs_PostAttachments
SET
	SectionID = @MoveToForum
WHERE
	PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @OldThreadID AND (PostID = @PostID OR ParentID = @PostID) AND SettingsID = @SettingsID)

-- Update the post and it's childred (if any) with the new threadid
UPDATE 
	cs_Posts 
SET 
	ThreadID = @NewThreadID,
	SectionID = @MoveToForum,
	ParentID = @PostID	-- the toplevel post should now reference itself.
--	PostDate = GetDate()	-- We're not going to reset the DATETIME for the posts
WHERE
	ThreadID = @OldThreadID
	AND (PostID = @PostID OR ParentID = @PostID)  and SettingsID = @SettingsID

-- this is now controlled in the cs_system_UpdateThread sproc
-- Fix the PostLevel and SortOrder details of the new thread
--SELECT 
--	@TopPostLevel = PostLevel,
--	@TopSortOrder = SortOrder
--FROM 
--	cs_Posts 
--WHERE 
--	PostID = @PostID
--
--UPDATE 
--	cs_Posts 
--SET 
---	PostLevel = (PostLevel - @TopPostLevel) + 1,
--	SortOrder = (SortOrder - @TopSortOrder) + 1
--WHERE
--	ThreadID = @NewThreadID

-- Update the threads...
EXEC cs_system_UpdateThread @NewThreadID, 0, @SettingsID
EXEC cs_system_UpdateThread @OldThreadID, 0, @SettingsID

-- Update forum statistics
EXEC cs_system_UpdateForum @MoveToForum, @NewThreadID, @PostID, @SettingsID

-- #7. Update moderation actions
EXEC cs_system_ModerationAction_AuditEntry 8, @SplitBy, @PostID, null, null, @SettingsID, null


COMMIT TRAN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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