IF OBJECT_ID (N'b_ForumCategory', N'U') IS NULL
	CREATE TABLE b_ForumCategory
	(
		ID INT IDENTITY(1,1) NOT NULL,
		Name NVARCHAR(255) NOT NULL,
		Sort INT NOT NULL CONSTRAINT DF_b_ForumCategory_Sort  DEFAULT ((10)),
		XmlId NVARCHAR(255) NULL,
		CONSTRAINT PK_b_ForumCategory PRIMARY KEY (ID ASC)
	)
GO

IF OBJECT_ID (N'b_Forum', N'U') IS NULL
	CREATE TABLE b_Forum
	(
		ID INT IDENTITY(1,1) NOT NULL,
		Active BIT NOT NULL CONSTRAINT DF_b_Forum_Active  DEFAULT ((1)),
		Name NVARCHAR(255) NOT NULL,
		DateCreate DATETIME NOT NULL CONSTRAINT DF_b_Forum_DateCreate  DEFAULT (getdate()),
		Description NVARCHAR(255) NULL,
		CategoryId INT NOT NULL CONSTRAINT DF_b_Forum_CategoryId  DEFAULT ((0)),
		Sort INT NOT NULL CONSTRAINT DF_b_Forum_Sort  DEFAULT ((10)),
		Topics INT NOT NULL CONSTRAINT DF_b_Forum_Topics  DEFAULT ((0)),
		QueuedTopics INT NOT NULL CONSTRAINT DF_b_Forum_QueuedTopics  DEFAULT ((0)),
		Replies INT NOT NULL CONSTRAINT DF_b_Forum_Posts  DEFAULT ((0)),
		QueuedReplies INT NOT NULL CONSTRAINT DF_b_Forum_QueuedPosts  DEFAULT ((0)),
		Code NVARCHAR(255) NULL,
		XmlId VARCHAR(255) NULL,
		AllowBBCode BIT NOT NULL CONSTRAINT DF_b_Forum_AllowBBCode  DEFAULT ((1)),
		AllowSmiles BIT NOT NULL CONSTRAINT DF_b_Forum_AllowSmiles  DEFAULT ((1)),
		LastPostId BIGINT NOT NULL CONSTRAINT DF_b_Forum_LastPostId  DEFAULT ((0)),
		LastPostDate DATETIME NULL,
		LastPosterId INT NOT NULL CONSTRAINT DF_b_Forum_LastPosterId  DEFAULT ((0)),
		LastPosterName NVARCHAR(256) NULL,
		IndexContent BIT NOT NULL CONSTRAINT DF_b_Forum_IndexContent  DEFAULT ((1)),
		CONSTRAINT PK_b_Forum PRIMARY KEY (ID ASC)
	)

GO

IF OBJECT_ID (N'b_ForumInSite', N'U') IS NULL
	CREATE TABLE b_ForumInSite
	(
		ForumId INT NOT NULL,
		SiteId VARCHAR(50) NOT NULL,
		CONSTRAINT PK_b_ForumInSite PRIMARY KEY (ForumId ASC, SiteId ASC),
		CONSTRAINT FK_ForumInSite_ForumId_Forum_Id FOREIGN KEY(ForumId) REFERENCES b_Forum (ID),
		CONSTRAINT FK_ForumInSite_SiteId_Site_Id FOREIGN KEY(SiteId) REFERENCES b_Site (ID)
	)
GO

IF OBJECT_ID (N'b_ForumUser', N'U') IS NULL
	CREATE TABLE b_ForumUser
	(
		ID INT NOT NULL,
		Posts INT NOT NULL CONSTRAINT DF_b_ForumUser_Posts  DEFAULT ((0)),
		Signature NVARCHAR(400) NULL,
		CONSTRAINT PK_b_ForumUser PRIMARY KEY (ID ASC)
	)
GO

IF OBJECT_ID (N'b_ForumTopic', N'U') IS NULL
	CREATE TABLE b_ForumTopic
	(
		ID INT IDENTITY(1,1) NOT NULL,
		ForumId INT NOT NULL,
		Name NVARCHAR(255) NOT NULL,
		Description NVARCHAR(255) NULL,
		DateCreate DATETIME NOT NULL CONSTRAINT DF_b_ForumTopic_DateCreate  DEFAULT (getdate()),
		AuthorId INT NOT NULL CONSTRAINT DF_b_ForumTopic_StarterId  DEFAULT ((0)),
		AuthorName NVARCHAR(256) NOT NULL,
		Closed BIT NOT NULL CONSTRAINT DF_b_ForumTopic_Open  DEFAULT ((0)),
		Approved BIT NOT NULL CONSTRAINT DF_b_ForumTopic_Approved  DEFAULT ((1)),
		StickyIndex INT NOT NULL CONSTRAINT DF_b_ForumTopic_StickyIndex  DEFAULT ((0)),
		Replies INT NOT NULL CONSTRAINT DF_b_ForumTopic_Posts  DEFAULT ((0)),
		QueuedReplies INT NOT NULL CONSTRAINT DF_b_ForumTopic_QueuedPosts  DEFAULT ((0)),
		Views bigINT NOT NULL CONSTRAINT DF_b_ForumTopic_Views  DEFAULT ((0)),
		MovedTo INT NOT NULL CONSTRAINT DF_b_ForumTopic_MovedTo  DEFAULT ((0)),
		FirstPostId bigINT NOT NULL CONSTRAINT DF_b_ForumTopic_FirstPostId  DEFAULT ((0)),
		LastPostId bigINT NOT NULL CONSTRAINT DF_b_ForumTopic_LastPostId  DEFAULT ((0)),
		LastPostDate DATETIME NULL,
		LastPosterId INT NOT NULL CONSTRAINT DF_b_ForumTopic_LastPosterId  DEFAULT ((0)),
		LastPosterName NVARCHAR(256) NULL,
		XmlId VARCHAR(255) NULL,
		CONSTRAINT PK_b_ForumTopic PRIMARY KEY (ID ASC),
		CONSTRAINT FK_ForumTopic_ForumId_Forum_Id FOREIGN KEY(ForumId) REFERENCES b_Forum (ID)
	)

GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_ForumTopic_TopicList')
	CREATE INDEX IX_b_ForumTopic_TopicList ON b_ForumTopic ( ForumId ASC, Approved ASC, StickyIndex ASC, LastPostDate ASC, ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_ForumTopic_TopicCount')
	CREATE INDEX IX_b_ForumTopic_TopicCount ON b_ForumTopic ( ForumId ASC, Approved ASC, MovedTo ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_ForumTopic_ForumId')
	CREATE INDEX IX_b_ForumTopic_ForumId ON b_ForumTopic ( ForumId ASC )
GO

IF OBJECT_ID (N'b_ForumPost', N'U') IS NULL
	CREATE TABLE b_ForumPost
	(
		ID BIGINT IDENTITY(1,1) NOT NULL,
		TopicId INT NOT NULL,
		ForumId INT NOT NULL,
		AuthorId INT NOT NULL CONSTRAINT DF_b_ForumPost_AuthorId  DEFAULT ((0)),
		ParentPostId bigINT NOT NULL CONSTRAINT DF_b_ForumPost_ParentPostId  DEFAULT ((0)),
		AuthorName NVARCHAR(256) NOT NULL,
		AuthorEmail NVARCHAR(256) NULL,
		AuthorIp VARCHAR(255) NULL,
		Post NVARCHAR(max) NOT NULL,
		Approved BIT NOT NULL CONSTRAINT DF_b_ForumPost_Approved  DEFAULT ((1)),
		DateCreate DATETIME NOT NULL CONSTRAINT DF_b_ForumPost_DateCreate  DEFAULT (getdate()),
		AllowSmiles BIT NOT NULL CONSTRAINT DF_b_ForumPost_AllowSmiles  DEFAULT ((1)),
		XmlId VARCHAR(255) NULL,
		CONSTRAINT PK_b_ForumPost PRIMARY KEY (ID ASC),
		CONSTRAINT FK_ForumPost_ForumId_Forum_Id FOREIGN KEY(ForumId) REFERENCES b_Forum(ID),
		CONSTRAINT FK_ForumPost_TopicId_ForumTopic_Id FOREIGN KEY(TopicId) REFERENCES b_ForumTopic(ID)
	)

GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_ForumPost_PostList')
	CREATE INDEX IX_b_ForumPost_PostList ON b_ForumPost ( TopicId ASC, ForumId ASC, Approved ASC, ID ASC, AuthorId ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_ForumPost_TopicId')
	CREATE INDEX IX_b_ForumPost_TopicId ON b_ForumPost ( TopicId ASC )
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_ForumPost_ForumId')
	CREATE INDEX IX_b_ForumPost_ForumId ON b_ForumPost ( ForumId ASC )
GO

IF OBJECT_ID (N'Forum_SynchronizeForum', N'P') IS NOT NULL
	DROP PROCEDURE Forum_SynchronizeForum
GO

CREATE PROCEDURE Forum_SynchronizeForum
	@ForumId int 
AS
BEGIN
	DECLARE @AllTopics int, @ApprovedTopics int, @Replies int, @QueuedReplies int

	SELECT
		@AllTopics = COUNT(TOPIC.ID), 
		@ApprovedTopics = SUM(CASE TOPIC.Approved WHEN 1 THEN 1 ELSE 0 END), 
		@Replies = SUM(CASE TOPIC.Approved WHEN 1 THEN TOPIC.Replies ELSE 0 END),
		@QueuedReplies = SUM(TOPIC.QueuedReplies)
	FROM 
		b_ForumTopic TOPIC
	WHERE 
		TOPIC.ForumId = @ForumId AND TOPIC.MovedTo = 0

	IF @ApprovedTopics IS NULL SET @ApprovedTopics = 0
	IF @Replies IS NULL SET @Replies = 0
	IF @QueuedReplies IS NULL SET @QueuedReplies = 0	
	
	DECLARE @LastPostDate datetime, @LastPostId int, @LastPosterId int, @LastPosterName nvarchar(256)

	SELECT TOP 1
		@LastPostDate = TOPIC.LastPostDate,
		@LastPostId = TOPIC.LastPostId, 
		@LastPosterId = TOPIC.LastPosterId, 
		@LastPosterName = TOPIC.LastPosterName
	FROM
		b_ForumTopic TOPIC
	WHERE
		TOPIC.ForumId = @ForumId AND TOPIC.MovedTo = 0 AND TOPIC.Approved = 1
	ORDER BY
		TOPIC.LastPostDate DESC

	IF @LastPostId IS NULL SET @LastPostId = 0
	IF @LastPosterId IS NULL SET @LastPosterId = 0	

	UPDATE 
		b_Forum
	SET
		Replies = @Replies,
		QueuedReplies = @QueuedReplies,
		Topics = @ApprovedTopics,
		QueuedTopics = @AllTopics - @ApprovedTopics,
		LastPostDate = @LastPostDate,
		LastPostId = @LastPostId, 
		LastPosterId = @LastPosterId, 
		LastPosterName = @LastPosterName
	WHERE
		ID = @ForumId
END
GO

IF OBJECT_ID (N'Forum_SynchronizeTopic', N'P') IS NOT NULL
	DROP PROCEDURE Forum_SynchronizeTopic
GO

CREATE PROCEDURE Forum_SynchronizeTopic 
	@TopicId int = 0
AS
BEGIN
	
	DECLARE @FirstPostId bigint
	SET @FirstPostId = 0
	
	SELECT
		@FirstPostId = FirstPostId
	FROM 
		b_ForumTopic
	WHERE 
		ID = @TopicId
	
	IF @@ROWCOUNT = 0
		RETURN
	
	DECLARE @ApprovedPosts int, @QueuedPosts int, @LastPostDate datetime, @LastPostId int, @LastPosterId int, @LastPosterName nvarchar(256)

	SELECT 
		@ApprovedPosts = SUM(CASE POST.Approved WHEN 1 THEN 1 ELSE 0 END),
		@QueuedPosts = SUM(CASE POST.Approved WHEN 0 THEN 1 ELSE 0 END)
	FROM 
		b_ForumPost POST
	WHERE 
		POST.TopicId = @TopicId AND POST.ID <> @FirstPostId  

	SELECT TOP 1
		@LastPostDate = POST.DateCreate,
		@LastPostId = POST.ID, 
		@LastPosterId = POST.AuthorId, 
		@LastPosterName = POST.AuthorName
	FROM
		b_ForumPost POST
	WHERE
		POST.TopicId = @TopicId AND POST.Approved = 1
	ORDER BY
		POST.ID DESC
	
	IF @ApprovedPosts IS NULL SET @ApprovedPosts = 0
	IF @QueuedPosts IS NULL SET @QueuedPosts = 0	
	IF @LastPostId IS NULL SET @LastPostId = 0
	IF @LastPosterId IS NULL SET @LastPosterId = 0	

	UPDATE 
		b_ForumTopic
	SET
		Replies = @ApprovedPosts,
		QueuedReplies = @QueuedPosts,
		LastPostDate = @LastPostDate,
		LastPostId = @LastPostId, 
		LastPosterId = @LastPosterId, 
		LastPosterName = @LastPosterName
	WHERE
		ID = @TopicId
END

GO


IF OBJECT_ID (N'Forum_SynchronizeUser', N'P') IS NOT NULL
	DROP PROCEDURE Forum_SynchronizeUser
GO

CREATE PROCEDURE Forum_SynchronizeUser
	@UsersId varchar(2000)
AS
BEGIN

	DECLARE @TranStarted bit
	SET @TranStarted = 0

	IF (@@TRANCOUNT = 0)
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @Pos int, @NextPos int, @Id nvarchar(15)
	DECLARE @Posts int
	SET @Pos = 1
	
	WHILE (@Pos <= LEN(@UsersId))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @UsersId,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@UsersId) + 1
			
		SELECT @Id = RTRIM(LTRIM(SUBSTRING(@UsersId, @Pos, @NextPos - @Pos)))
		
		SELECT	@Posts = COUNT(ID)
		FROM	b_ForumPost Post
		WHERE	Post.AuthorId = @Id AND 
			Post.Approved = 1 AND 
			Post.ForumId IN (SELECT ID FROM b_Forum WHERE Active = 1)
		
		UPDATE b_ForumUser SET Posts = @Posts WHERE ID = @Id
		IF @@ROWCOUNT = 0 AND NOT EXISTS(SELECT ID FROM b_ForumUser WHERE ID = @Id)
			INSERT INTO b_ForumUser (ID, Posts) VALUES(@Id, @Posts)
		
		SET @Pos = @NextPos + 1
	END

	IF (@TranStarted = 1)
		COMMIT TRANSACTION

	RETURN(0)
	
END
GO
