CREATE PROCEDURE dbo.[Forum_Subscriptions_NewThread]
    @ThreadID INT,
    @EmailQueueID INT
AS
    DECLARE @ForumID INT,
        @GroupID INT,
        @PortalID INT,
        @NotificationForum INT,
        @PublicView BIT,
        @PermissionID INT,
        @NotificationPermID INT,
        @LastPostedPostID INT,
        @PosterID INT,
        @NotifyPoster BIT,
        @ExcludeEmail NVARCHAR(256)
    SET @ForumID = ( SELECT ForumID
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID = @ThreadID
                   )
    SET @GroupID = ( SELECT GroupID
                     FROM   dbo.Forum_Forums
                     WHERE  ForumID = @ForumID
                   )
    SET @PortalID = ( SELECT    PortalID
                      FROM      dbo.Forum_Groups
                      WHERE     GroupID = @GroupID
                    )
    SET @NotificationForum = ( SELECT   ForumType
                               FROM     dbo.Forum_Forums
                               WHERE    ForumID = @ForumID
                             )
    SET @PublicView = ( SELECT  PublicView
                        FROM    dbo.Forum_Forums
                        WHERE   ForumID = @ForumID
                      )
    SET @PermissionID = ( SELECT    PermissionID
                          FROM      dbo.Forum_Permission
                          WHERE     PermissionKey = 'VIEW'
                                    AND PermissionCode = 'PRIVATE'
                        )
    SET @NotificationPermID = ( SELECT  PermissionID
                                FROM    dbo.Forum_Permission
                                WHERE   PermissionKey = 'NOTIFICATION'
                                        AND PermissionCode = 'NOTIFICATION'
                              )
    SET @LastPostedPostID = ( SELECT    LastPostedPostID
                              FROM      dbo.Forum_Threads
                              WHERE     ThreadID = @ThreadID
                            )
    SET @PosterID = ( SELECT    UserID
                      FROM      dbo.Forum_Posts
                      WHERE     PostID = @LastPostedPostID
                    )
-- we need to see if the poster has self notifications = false, if they do we need to delete from temp table
    SET @NotifyPoster = ISNULL(( SELECT EnableSelfNotifications
                                 FROM   dbo.Forum_Users
                                 WHERE  UserID = @PosterID
                                        AND PortalID = @PortalID
                               ), 1)
    IF @NotifyPoster = 0
        BEGIN
            SET @ExcludeEmail = ( SELECT    Email
                                  FROM      dbo.Users
                                  WHERE     UserID = @PosterID
                                )
        END
-- Create a temp table to store the select results
    CREATE TABLE #EmailIndex
        (
          IndexID INT IDENTITY(1, 1)
                      NOT NULL,
          Email NVARCHAR(100) COLLATE database_default,
          EmailFormat INT,
          DisplayName NVARCHAR(100) COLLATE database_default,
          UserID INT
        )
    IF @PublicView = 1
        BEGIN
            INSERT  INTO #EmailIndex
                    (
                      Email,
                      EmailFormat,
                      DisplayName,
                      U.UserID
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                EmailFormat,
                                DisplayName,
                                U.UserID
                      FROM      dbo.vw_Users U
                                INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                                INNER JOIN dbo.Forum_TrackedForums TF ON U.UserID = TF.UserID
                      WHERE     TF.ForumID = @ForumID
                                AND FU.PortalID = @PortalID
                                AND IsDeleted = 0
                                AND Authorised = 1
                    )
            INSERT  INTO #EmailIndex
                    (
                      Email,
                      EmailFormat,
                      DisplayName,
                      U.UserID
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                EmailFormat,
                                DisplayName,
                                U.UserID
                      FROM      dbo.vw_Users U
                                INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                                INNER JOIN dbo.Forum_TrackedThreads TT ON U.UserID = TT.UserID
                      WHERE     TT.ThreadID = @ThreadID
                                AND FU.PortalID = @PortalID
                                AND IsDeleted = 0
                                AND Authorised = 1
                    )            
        END
    ELSE
        -- Private Forums
        BEGIN
            INSERT  INTO #EmailIndex
                    (
                      Email,
                      EmailFormat,
                      DisplayName,
                      U.UserID
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                EmailFormat,
                                DisplayName,
                                U.UserID
                      FROM      dbo.vw_Users U
                                INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                                INNER JOIN dbo.Forum_TrackedForums TF ON U.UserID = TF.UserID
                      WHERE     TF.ForumID = @ForumID
                                AND U.UserID IN (
                                SELECT  UR.UserID
                                FROM    dbo.UserRoles UR
                                        INNER JOIN dbo.Forum_ForumPermission FP ON UR.RoleID = FP.RoleID
                                WHERE   PermissionID = @PermissionID
                                        AND ForumID = @ForumID
                                        AND ( ExpiryDate > GETDATE()
                                              OR ExpiryDate IS NULL
                                            )
                                        AND ( EffectiveDate < GETDATE()
                                              OR EffectiveDate IS NULL
                                            )
                                        AND IsDeleted = 0
                                        AND Authorised = 1 )
                    )
            -- Get individual users assigned perms at forum level
            INSERT  INTO #EmailIndex
                    (
                      Email,
                      EmailFormat,
                      DisplayName,
                      U.UserID
                    
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                EmailFormat,
                                DisplayName,
                                U.UserID
                      FROM      dbo.vw_Users U
                                INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                                INNER JOIN dbo.Forum_ForumPermission FP ON FU.UserID = FP.UserID
                      WHERE     FP.ForumID = @ForumID
                                AND FU.EnableModNotification = 1
                                AND FP.PermissionID = @PermissionID
                                AND FU.PortalID = @PortalID
                                AND IsDeleted = 0
                                AND Authorised = 1
                    )
            INSERT  INTO #EmailIndex
                    (
                      Email,
                      EmailFormat,
                      DisplayName,
                      U.UserID
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                EmailFormat,
                                DisplayName,
                                U.UserID
                      FROM      dbo.vw_Users U
                                INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                                INNER JOIN dbo.Forum_TrackedThreads TT ON U.UserID = TT.UserID
                      WHERE     TT.ThreadID = @ThreadID
                                AND FU.PortalID = @PortalID
                                AND U.UserID IN (
                                SELECT  UR.UserID
                                FROM    dbo.UserRoles UR
                                        INNER JOIN dbo.Forum_ForumPermission FP ON UR.RoleID = FP.RoleID
                                WHERE   PermissionID = @PermissionID
                                        AND ForumID = @ForumID
                                        AND ( ExpiryDate > GETDATE()
                                              OR ExpiryDate IS NULL
                                            )
                                        AND ( EffectiveDate < GETDATE()
                                              OR EffectiveDate IS NULL
                                            )
                                        AND IsDeleted = 0
                                        AND Authorised = 1 )
                    )
        END
    IF @NotificationForum = 1
        BEGIN
            INSERT  INTO #EmailIndex
                    (
                      Email,
                      EmailFormat,
                      DisplayName,
                      U.UserID
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                EmailFormat,
                                DisplayName,
                                U.UserID
                      FROM      dbo.vw_Users U
                                INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                                INNER JOIN dbo.UserRoles UR ON FU.UserID = UR.UserID
                                INNER JOIN dbo.Forum_ForumPermission FP ON UR.RoleID = FP.RoleID
                      WHERE     FP.ForumID = @ForumID
                                AND FP.PermissionID = @NotificationPermID
                                AND FU.PortalID = @PortalID
                                AND ( ExpiryDate > GETDATE()
                                      OR ExpiryDate IS NULL
                                    )
                                AND ( EffectiveDate < GETDATE()
                                      OR EffectiveDate IS NULL
                                    )
                                AND IsDeleted = 0
                                AND Authorised = 1
                    )
            -- Get individual users assigned perms at forum level
            INSERT  INTO #EmailIndex
                    (
                      Email,
                      EmailFormat,
                      DisplayName,
                      U.UserID
                    
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                EmailFormat,
                                DisplayName,
                                U.UserID
                      FROM      dbo.vw_Users U
                                INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                                INNER JOIN dbo.Forum_ForumPermission FP ON FU.UserID = FP.UserID
                      WHERE     FP.ForumID = @ForumID
                                AND FU.EnableModNotification = 1
                                AND FP.PermissionID = @NotificationPermID
                                AND FU.PortalID = @PortalID
                                AND IsDeleted = 0
                                AND Authorised = 1
                    )
        END
    -- DELETE the user who posted if they don't want notified of their own posts.
    IF @NotifyPoster = 0
        BEGIN
            DELETE  FROM #EmailIndex
            WHERE   Email = @ExcludeEmail
        END
    IF @EmailQueueID > 0
        BEGIN
            INSERT  INTO dbo.Forum_EmailQueue_TaskEmails
                    (
                      EmailAddress,
                      EmailQueueID,
                      IsHTML,
                      DisplayName,
                      DateAdded
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                @EmailQueueID,
                                EmailFormat,
                                DisplayName,
                                GETDATE()
                      FROM      #EmailIndex
                      WHERE     Email NOT IN (
                                SELECT  Email
                                FROM    dbo.Forum_EmailQueue_TaskEmails
                                WHERE   EmailQueueID = @EmailQueueID )
                    )
        END
    ELSE
        BEGIN
            SELECT DISTINCT
                    ( Email ),
                    EmailFormat,
                    DisplayName
            FROM    #EmailIndex
        END

GO