CREATE PROCEDURE dbo.[Forum_Post_Move]
    @PostID INT,
    @oldThreadID INT,
    @newThreadID INT,
    @newForumID INT,
    @oldForumID INT,
    @ModeratorUserID INT,
    @SortOrder INT,
    @Notes NVARCHAR(1024)
AS
    DECLARE @AnswerPostID INT
    DECLARE @UserID INT
    DECLARE @Status INT
    DECLARE @LastPostedPostID INT
    DECLARE @OldThreadNewLastPostID INT
    DECLARE @OldForumOldLastPostID INT
    DECLARE @OldForumNewLastPostID INT
    DECLARE @NewThreadNewLastPostID INT
    DECLARE @NewForumNewLastPostID INT
    DECLARE @IsApproved BIT
    DECLARE @OldGroupID INT
    DECLARE @NewGroupID INT

    BEGIN
        UPDATE  dbo.Forum_Posts
        SET     -- even if the post is not changing threads, @newThreadID is proper as it and oldThreadID pass in matching (if thats the case)
                ParentPostID = @newThreadID,
                ThreadID = @newThreadID
        WHERE   PostID = @PostID
    END

    IF @oldThreadID <> @newThreadID
        BEGIN
            -- if the post is moving to the new thread
            -- If this post is the answerpostid = @oldThreadID 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 = @oldThreadID
                                )
            IF @AnswerPostID = @PostID
                BEGIN
                    -- This was the previous threads answer
                    SET @UserID = ( SELECT  AnswerUserID
                                    FROM    dbo.Forum_Threads
                                    WHERE   ThreadID = @oldThreadID
                                  )
                    SET @Status = ( SELECT  ThreadStatus
                                    FROM    dbo.Forum_Threads
                                    WHERE   ThreadID = @oldThreadID
                                  )
                    -- new
                    EXEC dbo.Forum_Thread_StatusChange @newThreadID, @UserID,
                        @Status, @PostID
                    -- old
                    EXEC dbo.Forum_Thread_StatusChange @oldThreadID, -1, 0, 0
                END
            -- The post can be the lastPostID IF it has moved forums (in the new forum) so update Forum stats
            BEGIN
                -- since we have changed threads, we need to check lastpost status (possibly at forum level too)
                -- 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 = @oldThreadID
                                        )
                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 = @oldThreadID
                                                                AND IsApproved = 1
                                                        ORDER BY DateApproved DESC
                                                      )
                        -- Set the previous thread's lastpostid = @@OldThreadNewLastPostID
                        UPDATE  dbo.Forum_Threads
                        SET     LastPostedPostID = @OldThreadNewLastPostID
                        WHERE   ThreadID = @oldThreadID
                        -- See if we 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 = @oldThreadID
                                                                                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
                            -- 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 = @oldThreadID
                                                                    AND IsApproved = 1
                                                            ORDER BY DateApproved DESC
                                                          )
                            -- Set the previous thread's lastpostid = @@OldThreadNewLastPostID
                        UPDATE  dbo.Forum_Threads
                        SET     LastPostedPostID = @OldThreadNewLastPostID
                        WHERE   ThreadID = @oldThreadID
                    END
            END
            DECLARE @PortalID INT
            DECLARE @GroupID INT
            
            SET @GroupID = ( SELECT GroupID
                             FROM   dbo.Forum_Forums
                             WHERE  ForumID = @oldForumID
                           )
            SET @PortalID = ( SELECT    PortalID
                              FROM      dbo.Forum_Groups
                              WHERE     GroupID = @GroupID
                            )
            -- Log this moderation split action for moderator trail
            EXEC dbo.Forum_Moderate_AddHistory @PostID, @PortalID,
                @ModeratorUserID, @Notes, 14 -- 14 = post move
            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 AND newThread
            -- If a post wasn't approved, it should have never been added to the stats
            IF @IsApproved = 1
                BEGIN
                    IF @oldThreadID <> @newThreadID
                        BEGIN
                            UPDATE  dbo.Forum_Threads
                            SET     Replies = Replies - 1
                            WHERE   ThreadID = @oldThreadID

                            UPDATE  dbo.Forum_Threads
                            SET     Replies = Replies + 1
                            WHERE   ThreadID = @newThreadID
                        END
                    -- We now have to handle the forum level stats (Total Posts, Total Threads) but only for approved
                    IF @oldForumID <> @newForumID
                        BEGIN
                            -- The thread has changed forums
                            -- decrease in old forum
                            UPDATE  dbo.Forum_Forums
                            SET     TotalPosts = TotalPosts - 1
                            WHERE   ForumID = @oldForumID
                            -- increase in new forum
                            UPDATE  dbo.Forum_Forums
                            SET     TotalPosts = TotalPosts + 1
                            WHERE   ForumID = @NewForumID
                        END
                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