CREATE PROCEDURE dbo.[Forum_Forum_PostAdded]
    (
      @ForumID INT,
      @ThreadID INT,
      @PostID INT,
      @PostAuthorID INT,
      @Action NVARCHAR(50)
    )
AS
    DECLARE @ForumThreadCount INT
    DECLARE @PostsToModerate INT
    DECLARE @PreviousLastPostID INT
    DECLARE @PreviousLastPostDate DATETIME
    DECLARE @ApprovedPostDate DATETIME
    DECLARE @TotalPosts INT
    
    SET @PreviousLastPostID = ( SELECT  MostRecentPostID
                                FROM    dbo.Forum_Forums
                                WHERE   ForumID = @ForumID
                              )
    SET @PreviousLastPostDate = ( SELECT    CreatedDate
                                  FROM      dbo.Forum_Posts
                                  WHERE     PostID = @PreviousLastPostID
                                )
    -- Post added to non-moderate forum
    IF @Action = 'add'
        BEGIN
    -- If this is the first post of thread, also increase forum thread count
            IF @ThreadID = @PostID
                BEGIN
                    SET @ForumThreadCount = ( ( SELECT  TotalThreads
                                                FROM    dbo.Forum_Forums
                                                WHERE   ForumID = @ForumID
                                              ) + 1 )
                END
            ELSE
                BEGIN
                    SET @ForumThreadCount = ( SELECT    TotalThreads
                                              FROM      dbo.Forum_Forums
                                              WHERE     ForumID = @ForumID
                                            )
                END
            SET @TotalPosts = ( ( SELECT    TotalPosts
                                  FROM      dbo.Forum_Forums
                                  WHERE     ForumID = @ForumID
                                ) + 1 )
            UPDATE  dbo.Forum_Forums
            SET     TotalPosts = @TotalPosts,
                    TotalThreads = @ForumThreadCount,
                    MostRecentPostID = @PostID
            WHERE   ForumID = @ForumID
        END
    -- Post submited to a moderated forum
    ELSE
        IF @Action = 'submit'
            BEGIN
                SET @PostsToModerate = ( SELECT COUNT(IsApproved)
                                         FROM   dbo.Forum_Posts FP
                                                INNER JOIN dbo.Forum_Threads FT ON FP.ThreadID = FT.ThreadID
                                                INNER JOIN dbo.Forum_Forums FF ON FT.ForumID = FF.ForumID
                                         WHERE  FT.ForumID = @ForumID
                                                AND FP.IsApproved = 0
                                                AND IsActive = 1
                                       )
                UPDATE  dbo.Forum_Forums
                SET     PostsToModerate = @PostsToModerate
                WHERE   ForumID = @ForumID
            END
        -- Post approved by moderator
        ELSE
            IF @Action = 'approve'
                BEGIN
                    -- If this is the first post of thread, also increase forum thread count
                    IF @ThreadID = @PostID
                        BEGIN
                            SET @ForumThreadCount = ( ( SELECT  TotalThreads
                                                        FROM    dbo.Forum_Forums
                                                        WHERE   ForumID = @ForumID
                                                      ) + 1 )
                        END
                    ELSE
                        BEGIN
                            SET @ForumThreadCount = ( SELECT    TotalThreads
                                                      FROM      dbo.Forum_Forums
                                                      WHERE     ForumID = @ForumID
                                                    )
                        END
                    SET @PostsToModerate = ( SELECT COUNT(IsApproved)
                                             FROM   dbo.Forum_Posts FP
                                                    INNER JOIN dbo.Forum_Threads FT ON FP.ThreadID = FT.ThreadID
                                                    INNER JOIN dbo.Forum_Forums FF ON FT.ForumID = FF.ForumID
                                             WHERE  FT.ForumID = @ForumID
                                                    AND FP.IsApproved = 0
                                                    AND IsActive = 1
                                           )
                    SET @TotalPosts = ( ( SELECT    TotalPosts
                                          FROM      dbo.Forum_Forums
                                          WHERE     ForumID = @ForumID
                                        ) + 1 )
                    UPDATE  dbo.Forum_Forums
                    SET     PostsToModerate = @PostsToModerate,
                            TotalPosts = @TotalPosts,
                            TotalThreads = @ForumThreadCount
                    WHERE   ForumID = @ForumID

                    SET @ApprovedPostDate = ( SELECT    CreatedDate
                                              FROM      dbo.Forum_Posts
                                              WHERE     PostID = @PostID
                                            )
                    -- we only need to update the MostRecent items if the post being approved was created after the last approved post for this forum
                    IF @ApprovedPostDate > @PreviousLastPostDate
                        BEGIN
                            UPDATE  dbo.Forum_Forums
                            SET     MostRecentPostID = @PostID
                            WHERE   ForumID = @ForumID
                        END
                END
            -- Post rejected or deleted by moderator
            ELSE
                IF @Action = 'delete'
                    BEGIN
                        SET @PostsToModerate = ( ( SELECT   COUNT(IsApproved)
                                                   FROM     dbo.Forum_Posts FP
                                                            INNER JOIN dbo.Forum_Threads FT ON FP.ThreadID = FT.ThreadID
                                                            INNER JOIN dbo.Forum_Forums FF ON FT.ForumID = FF.ForumID
                                                   WHERE    FT.ForumID = @ForumID
                                                            AND FP.IsApproved = 0
                                                            AND IsActive = 1
                                                 ) - 1 )
                        UPDATE  dbo.Forum_Forums
                        SET     PostsToModerate = @PostsToModerate
                        WHERE   ForumID = @ForumID
                    END

GO