CREATE PROCEDURE dbo.[Forum_Thread_Move]
    (
      @ThreadID INT,
      @NewForumID INT,
      @ModeratorID INT,
      @Notes NVARCHAR(1024)
    )
AS
    DECLARE @OldForumID INT
    DECLARE @OldForumLastPostID INT
    DECLARE @OldGroupID INT
    DECLARE @PortalID INT
    DECLARE @NewForumLastPostID INT
    DECLARE @NewGroupID INT
    DECLARE @MovePostCount INT
    DECLARE @NewestMovingPostID INT
    DECLARE @NewForumNewLastPostID INT

    IF EXISTS ( SELECT  [ForumID]
                FROM    dbo.Forum_Forums
                WHERE   ForumID = @NewForumID )
        BEGIN
        -- get the newest post in the thread we are moving (remember, it will be approved shortly)
            SET @NewestMovingPostID = ( SELECT TOP 1
                                                PostID
                                        FROM    dbo.Forum_Posts
                                        WHERE   ThreadID = @ThreadID
                                        ORDER BY CreatedDate DESC
                                      )
        -- get info pertaining to previous forum
            SELECT  @OldGroupID = [GroupID],
                    @OldForumID = T.[ForumID],
                    @OldForumLastPostID = [LastPostedPostID],
                    @MovePostCount = [Replies] + 1
            FROM    dbo.Forum_Threads T
                    INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID
            WHERE   [ThreadID] = @ThreadID
        -- Get info of target forum
            SELECT  @NewGroupID = [GroupID],
                    @NewForumLastPostID = [MostRecentPostID]
            FROM    dbo.Forum_Forums
            WHERE   [ForumID] = @NewForumID
        -- Move thread to new forum
            UPDATE  dbo.Forum_Threads
            SET     [ForumID] = @NewForumID
            WHERE   [ThreadID] = @ThreadID
        -- set last postid if a post being moved is newer than the new forum's current last post
            IF @NewForumLastPostID < @NewestMovingPostID
                BEGIN        
                    UPDATE  dbo.Forum_Forums
                    SET     MostRecentPostID = @NewestMovingPostID
                    WHERE   ForumID = @NewForumID
                END
            -- update counts
            UPDATE  dbo.Forum_Forums
            SET     [TotalThreads] = [TotalThreads] + 1,
                    [TotalPosts] = [TotalPosts] + @MovePostCount
            WHERE   [ForumID] = @NewForumID
        -- set last postid if a post being moved is the last post of the forum it is leaving
            IF @OldForumLastPostID = @NewestMovingPostID
                BEGIN
                    SET @NewForumNewLastPostID = ( SELECT   MAX(PostID)
                                                   FROM     dbo.Forum_Posts P
                                                            INNER JOIN dbo.Forum_Threads T ON P.ThreadID = T.ThreadID
                                                   WHERE    IsApproved = 1
                                                            AND ForumID = @OldForumID
                                                 )
                    UPDATE  dbo.Forum_Forums
                    SET     MostRecentPostID = @NewForumNewLastPostID
                    WHERE   ForumID = @OldForumID
                END
            -- Update the old forum
            UPDATE  dbo.Forum_Forums
            SET     [TotalThreads] = [TotalThreads] - 1,
                    [TotalPosts] = [TotalPosts] - @MovePostCount
            WHERE   [ForumID] = @OldForumID
            -- Set all posts to approved on move
            UPDATE  dbo.Forum_Posts
            SET     [IsApproved] = 1
            WHERE   [ThreadID] = @ThreadID
            SET @PortalID = ( SELECT    PortalID
                              FROM      dbo.Forum_Groups
                              WHERE     GroupID = @NewGroupID
                            )
            -- Take care of moderation biz. - 6 is from enum in application (for thread move)
            EXEC dbo.Forum_Moderate_AddHistory @ThreadID, @PortalID,
                @ModeratorID, @Notes, 6
        END

GO