CREATE PROCEDURE dbo.[Forum_Post_Delete]
    (
      @PostID INT,
      @ModeratorID INT,
      @Notes NVARCHAR(1024),
      @PortalID INT
    )
AS
    DECLARE @ThreadID INT
    DECLARE @ForumID INT
    DECLARE @GroupID INT
    DECLARE @IsApproved BIT
    DECLARE @AuthorID INT
    DECLARE @ContentItemID INT
    DECLARE @NewThreadID INT
    
    SET @NewThreadID = -1
    SET @ThreadID = ( SELECT    ThreadID
                      FROM      dbo.Forum_Posts
                      WHERE     PostID = @PostID
                    )
    SET @ForumID = ( SELECT ForumID
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID = @ThreadID
                   )
    SET @GroupID = ( SELECT TOP 1
                            GroupID
                     FROM   dbo.Forum_Forums
                     WHERE  ForumID = @ForumID
                   )
    SET @IsApproved = ( SELECT  IsApproved
                        FROM    dbo.Forum_Posts
                        WHERE   PostID = @PostID
                      )
    SET @AuthorID = ( SELECT    UserID
                      FROM      dbo.Forum_Posts
                      WHERE     PostID = @PostID
                    )
    IF ( @ThreadID = @PostID )
        BEGIN
            DECLARE @PostCount INT        
            SET @PostCount = ( SELECT   COUNT(PostID)
                               FROM     dbo.Forum_Posts
                               WHERE    ThreadID = @ThreadID
                             )                          
            DECLARE @PollID INT
            SET @PollID = ( SELECT  PollID
                            FROM    dbo.Forum_Threads
                            WHERE   ThreadID = @ThreadID
                          )                               
            IF ( @PostCount > 1 )
                BEGIN                 
                    SET @NewThreadID = ( SELECT MIN(PostID)
                                         FROM   dbo.Forum_Posts
                                         WHERE  PostID <> @PostID
                                                AND ThreadID = @ThreadID
                                       )
                    UPDATE  dbo.Forum_Posts
                    SET     ParentPostID = 0
                    WHERE   PostID = @NewThreadID

                    UPDATE  dbo.Forum_Posts
                    SET     ParentPostID = @NewThreadID
                    WHERE   ParentPostID = @PostID
                    -- we have to create a new thread before going any further (because of keys)
                    INSERT  dbo.Forum_Threads
                            (
                              ThreadID,
                              ForumID,
                              [Views],
                              LastPostedPostID,
                              Replies,
                              IsPinned,
                              PinnedDate,
                              ThreadStatus,
                              AnswerPostID,
                              AnswerUserID,
                              PollID,
                              SitemapInclude,
                              ContentItemID,
                              AnswerDate
                            )
                            SELECT  @NewThreadID,
                                    ForumID,
                                    [Views],
                                    LastPostedPostID,
                                    Replies,
                                    IsPinned,
                                    PinnedDate,
                                    ThreadStatus,
                                    AnswerPostID,
                                    AnswerUserID,
                                    PollID,
                                    SitemapInclude,
                                    ContentItemID,
                                    AnswerDate
                            FROM    dbo.Forum_Threads
                            WHERE   ThreadID = @ThreadID
                                         
                    UPDATE  dbo.Forum_Posts
                    SET     ThreadID = @NewThreadID
                    WHERE   ThreadID = @PostID

                    UPDATE  dbo.Forum_Bookmark_Threads
                    SET     ThreadID = @NewThreadID
                    WHERE   ThreadID = @ThreadID

                    UPDATE  dbo.Forum_Polls_Polls
                    SET     ThreadID = @NewThreadID
                    WHERE   ThreadID = @ThreadID

                    DELETE  FROM dbo.Forum_Threads
                    WHERE   ThreadID = @ThreadID
                END
            ELSE
                BEGIN
                    EXEC dbo.Forum_Polls_PollDelete @PollID

                    DELETE  dbo.Forum_Threads
                    WHERE   ThreadID = @ThreadID

                    IF ( @IsApproved = 1 )
                        BEGIN
                            UPDATE  dbo.Forum_Forums
                            SET     TotalThreads = ( TotalThreads - 1 )
                            WHERE   ForumID = @ForumID
                        END

                    EXEC dbo.Forum_Moderate_AddHistory @PostID, @PortalID,
                        @ModeratorID, @Notes, 7
                END
        END
    ELSE
        BEGIN
            UPDATE  dbo.Forum_Posts
            SET     ParentPostID = @ThreadID
            WHERE   ParentPostID = @PostID

            DECLARE @LastPostID INT
            DECLARE @IsPinned BIT
            DECLARE @PinnedDate DATETIME
            SET @LastPostID = ( SELECT  MAX(PostID)
                                FROM    dbo.Forum_Posts
                                WHERE   [ThreadID] = @ThreadID
                                        AND IsApproved = 1
                                        AND PostID <> @PostID
                              )
            SET @IsPinned = ( SELECT    IsPinned
                              FROM      dbo.Forum_Threads
                              WHERE     ThreadID = @ThreadID
                            )
            SET @PinnedDate = ( SELECT  PinnedDate
                                FROM    dbo.Forum_Threads
                                WHERE   ThreadID = @ThreadID
                              )
            UPDATE  dbo.Forum_Threads
            SET     LastPostedPostID = @LastPostID,
                    IsPinned = @IsPinned,
                    PinnedDate = @PinnedDate
            WHERE   ThreadID = @ThreadID
        END
    IF ( @IsApproved = 1 )
        BEGIN    
            DECLARE @NewLastPostID INT
            DECLARE @TotalPosts INT
            SET @NewLastPostID = ( SELECT   MAX(P.PostID)
                                   FROM     dbo.Forum_Posts P
                                            INNER JOIN dbo.Forum_Threads T ON P.ThreadID = T.ThreadID
                                   WHERE    P.IsApproved = 1
                                            AND T.ForumID = @ForumID
                                            AND P.PostID <> @PostID
                                 )
            SET @TotalPosts = ( ( SELECT    TotalPosts
                                  FROM      dbo.Forum_Forums
                                  WHERE     ForumID = @ForumID
                                ) - 1 )
            UPDATE  dbo.Forum_Forums
            SET     TotalPosts = @TotalPosts,
                    MostRecentPostID = @NewLastPostID
            WHERE   ForumID = @ForumID
        END
    DELETE  dbo.Forum_Attachments
    WHERE   PostID = @PostID
    
    DELETE  dbo.Forum_Posts
    WHERE   PostID = @PostID    
    
    EXEC dbo.Forum_Moderate_Delete @PostID, @ModeratorID, @Notes, @IsApproved,
        @PortalID, @ThreadID, @ForumID, @GroupID, @AuthorID
    
    SELECT  @NewThreadID

GO