CREATE PROCEDURE dbo.[Forum_Thread_Split]
    (
      @PostID INT,
      @ThreadID INT,
      @NewForumID INT,
      @ModeratorUserID INT,
      @Subject NVARCHAR(150),
      @Notes NVARCHAR(1024)
    )
AS -- Because of logic in vb code, we only want to create a new thread here and also make sure this post is removed from the previous
    -- thread stats (check forum level to make sure this post is not last post in forum)
    DECLARE @AnswerPostID INT
    DECLARE @UserID INT
    DECLARE @Status INT
    DECLARE @ParentPostID INT
    DECLARE @LastPostedPostID INT
    DECLARE @OldThreadNewLastPostID INT
    DECLARE @OldForumID INT
    DECLARE @OldForumOldLastPostID INT
    DECLARE @OldForumNewLastPostID INT
    DECLARE @NewForumNewLastPostID INT
    DECLARE @IsApproved BIT
    DECLARE @OldGroupID INT
    DECLARE @NewGroupID INT
    DECLARE @PortalID INT

    -- Make sure that new forum (or existing) exists
    IF EXISTS ( SELECT  [ForumID]
                FROM    dbo.Forum_Forums
                WHERE   ForumID = @NewForumID )
        BEGIN
            -- Create a new thread
            INSERT  INTO dbo.Forum_Threads
                    (
                      ThreadID,
                      ForumID,
                      IsPinned,
                      PollID,
                      LastPostedPostID,
                      Replies,
                      AnswerPostID,
                      AnswerUserID,
                      ThreadStatus
                    )
            VALUES  (
                      @PostID,
                      @NewForumID,
                      0,
                      -1,
                      @PostID,
                      0,
                      -1,
                      -1,
                      -1
                    )
            -- If this post is the answerpostid = @ThreadID we know this post is the answer to an unresolved status thread in its
            -- previous threadID. Make the threadstatus resolved and the answerpostid = @PostID in the new thread. In previous thread
            -- set answerpostid = 0 and set status to unresolved (this technically should never happen, but just in case) in previous thread
            SET @AnswerPostID = ( SELECT    AnswerPostID
                                  FROM      dbo.Forum_Threads
                                  WHERE     ThreadID = @ThreadID
                                )
            IF @AnswerPostID = @PostID
                BEGIN
                -- This was the previous threads answer
                    SET @UserID = ( SELECT  AnswerUserID
                                    FROM    dbo.Forum_Threads
                                    WHERE   ThreadID = @ThreadID
                                  )
                    SET @Status = ( SELECT  ThreadStatus
                                    FROM    dbo.Forum_Threads
                                    WHERE   ThreadID = @ThreadID
                                  )
                    -- new
                    EXEC dbo.Forum_Thread_StatusChange @PostID, @UserID,
                        @Status, @PostID
                    -- old
                    EXEC dbo.Forum_Thread_StatusChange @ThreadID, -1, 0, 0
                END
            ELSE
                BEGIN
                -- we know it wasn't answered, so move over the threadstatus to the new thread
                    SET @Status = ( SELECT  ThreadStatus
                                    FROM    dbo.Forum_Threads
                                    WHERE   ThreadID = @ThreadID
                                  )
                    EXEC dbo.Forum_Thread_StatusChange @ThreadID, -1, @Status,
                        0
                END
            -- here we have to update parentpostid to 0 for this post (new thread has no parent)
            SET @ParentPostID = ( SELECT    ParentPostID
                                  FROM      dbo.Forum_Posts
                                  WHERE     PostID = @PostID
                                )
            IF @ParentPostID > 0
                BEGIN
                -- This means it is a child post (which it always should be)
                    -- Set the post to have no parent
                    UPDATE  dbo.Forum_Posts
                    SET     ParentPostID = 0,
                            [Subject] = @Subject,
                            ThreadID = @PostID
                    WHERE   PostID = @PostID
                END
            -- Done w/ creating new thread, already made sure threadstatus is changed on both threads (if necessary) also handled clearing parent
            -- See if the post we are splitting into a new thread is the last post of the previous thread
            SET @LastPostedPostID = ( SELECT    LastPostedPostID
                                      FROM      dbo.Forum_Threads
                                      WHERE     ThreadID = @ThreadID
                                    )
            -- Get the old forumid here no matter what (we need it)
            SET @OldForumID = ( SELECT  ForumID
                                FROM    dbo.Forum_Threads
                                WHERE   ThreadID = @ThreadID
                              )
            IF @LastPostedPostID = @PostID
                BEGIN
                    -- The post we are splitting was the last post of the previous thread
                    SET @OldThreadNewLastPostID = ( SELECT TOP 1
                                                            PostID
                                                    FROM    dbo.Forum_Posts
                                                    WHERE   ThreadID = @ThreadID
                                                            AND IsApproved = 1
                                                    ORDER BY DateApproved DESC
                                                  )
                    -- Set the previous thread's lastpostid = @@OldThreadNewLastPostID
                    UPDATE  dbo.Forum_Threads
                    SET     LastPostedPostID = @OldThreadNewLastPostID
                    WHERE   ThreadID = @ThreadID
                    -- See if this post was the lastpostid of a forum ONLY if it changed forums
                    IF @OldForumID <> @NewForumID
                        BEGIN
                            SET @OldForumOldLastPostID = ( SELECT   MostRecentPostID
                                                           FROM     dbo.Forum_Forums
                                                           WHERE    ForumID = @OldForumID
                                                         )
                            IF @OldForumOldLastPostID = @PostID
                                BEGIN
                                -- Yes, the post was the last post of it's old forum
                                    -- Get the new value for the mostrecentpostid for the old forum
                                    SET @OldForumNewLastPostID = ( SELECT TOP 1
                                                                            PostID
                                                                   FROM     dbo.Forum_Posts FP
                                                                            INNER JOIN dbo.Forum_Threads FT ON FP.ThreadID = FT.ThreadID
                                                                   WHERE    FP.ThreadID = @ThreadID
                                                                            AND IsApproved = 1
                                                                            AND ForumID = @OldForumID
                                                                   ORDER BY DateApproved DESC
                                                                 )
                                    -- Update Forums table w/ the previous last postID we retrieved for old forum
                                    UPDATE  dbo.Forum_Forums
                                    SET     MostRecentPostID = @OldForumNewLastPostID
                                    WHERE   ForumID = @OldForumID
                                END
                            -- See if we need to update the new forum and make the postid as the mostrecentpostid for the newforumid
                            SET @NewForumNewLastPostID = ( SELECT TOP 1
                                                                    PostID
                                                           FROM     dbo.Forum_Posts FP
                                                                    INNER JOIN dbo.Forum_Threads FT ON FP.ThreadID = FT.ThreadID
                                                           WHERE    IsApproved = 1
                                                                    AND ForumID = @NewForumID
                                                           ORDER BY DateApproved DESC
                                                         )
                            -- Update Forums table w/ the previous last postID we retrieved for new forum
                            UPDATE  dbo.Forum_Forums
                            SET     MostRecentPostID = @NewForumNewLastPostID
                            WHERE   ForumID = @NewForumID
                        END
                    ELSE
                        -- It didn't move forums so just update the lastpostid for the previous thread if it was the lastpostid for the previous thread)
                        -- The post we are splitting was the last post of the previous thread (no need to check at forum level when we are not changing forums)
                        SET @OldThreadNewLastPostID = ( SELECT TOP 1
                                                                PostID
                                                        FROM    dbo.Forum_Posts
                                                        WHERE   ThreadID = @ThreadID
                                                                AND IsApproved = 1
                                                        ORDER BY DateApproved DESC
                                                      )
                        -- Set the previous thread's lastpostid = @@OldThreadNewLastPostID
                    UPDATE  dbo.Forum_Threads
                    SET     LastPostedPostID = @OldThreadNewLastPostID
                    WHERE   ThreadID = @ThreadID
                END
            -- section below has only restriction of forum existing from above
            -- The post can be the lastPostID IF it has moved forums (in the new forum) so update Forum stats
            SET @IsApproved = ( SELECT  IsApproved
                                FROM    dbo.Forum_Posts
                                WHERE   PostID = @PostID
                              )
            IF @IsApproved = 1
                BEGIN
                    UPDATE  dbo.Forum_Forums
                    SET     TotalThreads = TotalThreads + 1
                    WHERE   ForumID = @NewForumID

                    IF @OldForumID <> @NewForumID
                        BEGIN
                            UPDATE  dbo.Forum_Forums
                            SET     TotalPosts = TotalPosts + 1
                            WHERE   ForumID = @NewForumID    

                            UPDATE  dbo.Forum_Forums
                            SET     TotalPosts = TotalPosts - 1
                            WHERE   ForumID = @OldForumID    
                        END
                        -- If it has NOT moved forums, it should have been handled in logic above (since we looked for lastpostid first)
                        -- Since we check for lastpostid and only get here if it was not the last postid, there is no reason to check if staying
                END
                
            SET @PortalID = ( SELECT    PortalID
                              FROM      dbo.Forum_Threads T ( NOLOCK )
                                        INNER JOIN dbo.Forum_Forums F ( NOLOCK ) ON T.ForumID = F.ForumID
                                        INNER JOIN dbo.Forum_Groups G ( NOLOCK ) ON F.GroupID = G.GroupID
                              WHERE     T.ThreadID = @ThreadID
                            )  
            -- If the post was not approved, we don't have to update forum stats
            -- Log this moderation split action for moderator trail
            EXEC dbo.Forum_Moderate_AddHistory @PostID, @PortalID,
                @ModeratorUserID, @Notes, 5 -- 5 = thread split
            SET @IsApproved = ( SELECT  IsApproved
                                FROM    dbo.Forum_Posts
                                WHERE   PostID = @PostID
                              )
            -- Decrease previous thread Replies, views by one in previous thread only IF the post was approved
            IF @IsApproved = 1
                BEGIN
                    UPDATE  dbo.Forum_Threads
                    SET     Replies = Replies - 1
                    WHERE   ThreadID = @ThreadID
                END
            ELSE
                BEGIN
                    -- approve post and enforce moderation process (this will take care of stats for new thread/forum)
                    EXEC dbo.Forum_Moderate_Approve @PostID, @ModeratorUserID,
                        'Post Approved after Split'
                END
        END

GO