IF OBJECT_ID (N'b_BlogCategory', N'U') IS NULL
	CREATE TABLE b_BlogCategory
	(
		ID INT IDENTITY(1,1) NOT NULL,
		Name NVARCHAR(256) NOT NULL,
		Sort INT NOT NULL,
		XmlId VARCHAR(256) NULL,
		CONSTRAINT PK_b_BlogCategory PRIMARY KEY(ID ASC)
	)
GO

IF OBJECT_ID (N'b_BlogCategoryInSite', N'U') IS NULL
	CREATE TABLE b_BlogCategoryInSite
	(
		CategoryId INT NOT NULL,
		SiteId VARCHAR(50) NOT NULL,
		CONSTRAINT PK_b_BlogCategoryInSite PRIMARY KEY (CategoryId ASC, SiteId ASC),
		CONSTRAINT FK_b_BlogCategoryInSite_b_BlogCategory FOREIGN KEY(CategoryId) REFERENCES b_BlogCategory (ID),
		CONSTRAINT FK_b_BlogCategoryInSite_b_Site FOREIGN KEY(SiteId) REFERENCES b_Site (ID)
	)
GO

IF OBJECT_ID (N'b_BlogUser', N'U') IS NULL
	CREATE TABLE b_BlogUser
	(
		ID INT NOT NULL,
		PostCount INT NOT NULL,
		CONSTRAINT PK_b_BlogUser PRIMARY KEY (ID ASC),
		CONSTRAINT FK_b_BlogUser_b_Users FOREIGN KEY(ID) REFERENCES b_Users (ID)
	) 
GO

IF OBJECT_ID (N'b_Blog', N'U') IS NULL
	CREATE TABLE b_Blog
	(
		ID INT IDENTITY(1,1) NOT NULL,
		Name NVARCHAR(256) NOT NULL,
		Description NVARCHAR(2048) NULL,
		OwnerId INT NOT NULL,
		Active BIT NOT NULL,
		DateCreated DATETIME NOT NULL,
		DateLastPosted DATETIME NOT NULL,
		Slug NVARCHAR(128) NOT NULL,
		Sort INT NOT NULL,
		NotifyOfComments BIT NOT NULL,
		XmlId VARCHAR(256) NULL,
		PostCount INT NULL,
		CommentCount INT NULL,
		CONSTRAINT PK_b_Blog PRIMARY KEY (ID ASC),
		CONSTRAINT FK_b_Blog_b_BlogUser FOREIGN KEY(OwnerId) REFERENCES b_BlogUser (ID)
	)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_Blog_OwnerId')
	CREATE INDEX IX_b_Blog_OwnerId ON b_Blog (OwnerId ASC, Active ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_Blog_Active')
	CREATE INDEX IX_b_Blog_Active ON b_Blog (Active ASC, ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_Blog_PostCountList')
	CREATE INDEX IX_b_Blog_PostCountList ON b_Blog (PostCount DESC, Name ASC, Active ASC) 
	INCLUDE (ID, Description, OwnerId, DateCreated, DateLastPosted, Slug, Sort, NotifyOfComments, XmlId, CommentCount)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_Blog_NewBlogList')
	CREATE INDEX IX_b_Blog_NewBlogList ON b_Blog (DateCreated DESC, Name ASC, Active ASC) 
	INCLUDE (ID, Description, OwnerId, DateLastPosted, Slug, Sort, NotifyOfComments, XmlId, PostCount, CommentCount)
GO

IF OBJECT_ID (N'b_BlogInCategory', N'U') IS NULL
	CREATE TABLE b_BlogInCategory
	(
		BlogId INT NOT NULL,
		CategoryId INT NOT NULL,
		CONSTRAINT PK_b_BlogInCategory PRIMARY KEY (BlogId ASC, CategoryId ASC),
		CONSTRAINT FK_b_BlogInCategory_b_Blog FOREIGN KEY(BlogId) REFERENCES b_Blog (ID),
		CONSTRAINT FK_b_BlogInCategory_b_BlogCategory FOREIGN KEY(CategoryId) REFERENCES b_BlogCategory (ID)
	)
GO

IF OBJECT_ID (N'b_BlogPost', N'U') IS NULL
	CREATE TABLE b_BlogPost
	(
		ID INT IDENTITY(1,1) NOT NULL,
		BlogId INT NOT NULL,
		AuthorId INT NOT NULL,
		AuthorName NVARCHAR(256) NOT NULL,
		AuthorEmail NVARCHAR(320) NULL,
		AuthorIP VARCHAR(64) NULL,
		Title NVARCHAR(512) NULL,
		Contents NVARCHAR(max) NULL,
		DateCreated DATETIME NOT NULL,
		IsPublished BIT NOT NULL,
		DatePublished DATETIME NOT NULL,
		NotifyOfComments BIT NOT NULL,
		CommentCount INT NOT NULL,
		ViewCount INT NOT NULL,
		XmlId VARCHAR(256) NULL,
		CONSTRAINT PK_b_BlogPost PRIMARY KEY (ID ASC),
		CONSTRAINT FK_b_BlogPost_b_Blog FOREIGN KEY(BlogId) REFERENCES b_Blog (ID),
		CONSTRAINT FK_b_Post_b_BlogUser FOREIGN KEY(AuthorId) REFERENCES b_BlogUser (ID)
	)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogPost_PostList')
	CREATE INDEX IX_b_BlogPost_PostList ON b_BlogPost (BlogId ASC, IsPublished ASC, DatePublished ASC) INCLUDE (ID)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogPost_NewPosts')
	CREATE INDEX IX_b_BlogPost_NewPosts ON b_BlogPost (IsPublished ASC, DatePublished ASC, BlogId ASC, ID ASC) 
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogPost_PopularPosts')
	CREATE INDEX IX_b_BlogPost_PopularPosts ON b_BlogPost (ViewCount ASC, DatePublished ASC, IsPublished ASC, ID ASC, BlogId ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogPost_CommentedPosts')
	CREATE INDEX IX_b_BlogPost_CommentedPosts ON b_BlogPost (IsPublished ASC, DatePublished ASC, CommentCount ASC, BlogId ASC, ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogPost_BlogId')
	CREATE INDEX IX_b_BlogPost_BlogId ON b_BlogPost (BlogId ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogPost_AuthorId')
	CREATE INDEX IX_b_BlogPost_AuthorId ON b_BlogPost (AuthorId ASC)
GO

IF OBJECT_ID (N'b_BlogComment', N'U') IS NULL
	CREATE TABLE b_BlogComment
	(
		ID INT IDENTITY(1,1) NOT NULL,
		PostId INT NOT NULL,
		BlogId INT NOT NULL,
		ParentId INT NOT NULL,
		AuthorId INT NOT NULL,
		AuthorName NVARCHAR(256) NOT NULL,
		AuthorEmail NVARCHAR(320) NULL,
		AuthorWebsite NVARCHAR(2048) NULL,
		AuthorIP VARCHAR(64) NULL,
		Contents NVARCHAR(max) NULL,
		DateCreated DATETIME NOT NULL,
		DateModified DATETIME NOT NULL,
		IsApproved BIT NOT NULL,
		MarkedForDelete BIT NOT NULL,
		LeftMargin INT NOT NULL,
		RightMargin INT NOT NULL,
		DepthLevel INT NOT NULL,
		RootNodeIndex INT NOT NULL,
		LiveRootNodeIndex INT NULL,
		XmlId VARCHAR(256) NULL,
		AuthorBlogId INT NOT NULL,
		CONSTRAINT PK_b_BlogComment PRIMARY KEY (ID ASC),
		CONSTRAINT FK_b_BlogComment_b_Blog FOREIGN KEY(BlogId) REFERENCES b_Blog (ID),
		CONSTRAINT FK_b_BlogComment_b_BlogPost FOREIGN KEY(PostId) REFERENCES b_BlogPost (ID)
	) 
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogComment_CommentList')
	CREATE INDEX IX_b_BlogComment_CommentList ON b_BlogComment (PostId ASC, MarkedForDelete ASC, ID ASC) INCLUDE (ParentId, LiveRootNodeIndex) 
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogComment_CommentTree')
	CREATE INDEX IX_b_BlogComment_CommentTree ON b_BlogComment (PostId ASC, LiveRootNodeIndex ASC, AuthorId ASC, AuthorBlogId ASC, LeftMargin ASC)
	INCLUDE (
		ID, 
		BlogId, 
		ParentId, 
		AuthorName, 
		AuthorEmail, 
		AuthorWebsite, 
		AuthorIP, 
		Contents, 
		DateCreated, 
		DateModified, 
		IsApproved,
		MarkedForDelete,
		RightMargin,
		DepthLevel,
		RootNodeIndex,
		XmlId
	)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogComment_BlogId')
	CREATE INDEX IX_b_BlogComment_BlogId ON b_BlogComment (MarkedForDelete ASC, BlogId ASC)
GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_b_BlogComment_AuthorBlogId')
	CREATE INDEX IX_b_BlogComment_AuthorBlogId ON b_BlogComment (AuthorBlogId ASC)
GO

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

CREATE PROCEDURE Blog_SynchronizeBlog 
	@BlogId int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @DateLastPosted datetime, @Now datetime, @PostCount int, @CommentCount int;	
	SET @Now = GETDATE(); 
	
	IF (@BlogId IS NULL) RETURN;

	SELECT @PostCount = COUNT(ID) FROM b_BlogPost WHERE BlogId = @BlogId AND IsPublished = 1 AND DatePublished <= @Now;
	IF(@PostCount > 0)
	BEGIN
		SELECT TOP 1 @DateLastPosted = DatePublished FROM b_BlogPost WHERE BlogId = @BlogId AND IsPublished = 1 AND DatePublished <= @Now ORDER BY DatePublished DESC;
		SELECT @CommentCount = COUNT(ID) FROM b_BlogComment WHERE BlogId = @BlogId AND MarkedForDelete = 0;
		UPDATE b_Blog SET DateLastPosted = @DateLastPosted, PostCount = @PostCount, CommentCount = @CommentCount WHERE ID = @BlogId;
	END
	ELSE
	BEGIN
		UPDATE b_Blog SET DateLastPosted = DateCreated, PostCount = 0, CommentCount = 0 WHERE ID = @BlogId;
	END	
END
GO

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

CREATE PROCEDURE BlogCategory_DeleteBlogInCategory
	@CategoryId int
AS
BEGIN
	DELETE FROM b_BlogInCategory WHERE CategoryId = @CategoryId;	
END
GO

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

CREATE PROCEDURE BlogComment_SynchronizeNestedSets
	@Id int,
	@Mode int = 1
AS
BEGIN
SET NOCOUNT ON;
/*
@Mode = 1 - process addition;
@Mode = 2 - process deletion;
@Mode = 3 - process modification;
*/
declare @postId int, @currentLeft int, @currentRight int, @currentDepth int, @currentParentId int, @currentRootIndex int, @currentLiveRootIndex int, @currentMarkedForDelete bit, @currentLiveNestedCount int, @parentLeft int, @parentRight int, @parentDepth int, @parentRootIndex int, @parentLiveRootIndex int, @parentMarkedForDelete bit, @parentParentId int;


if @Mode < 1 OR @Mode > 3 return;
--set @currentLiveRootIndex = 0;
--set @currentLiveNestedCount = 0;
if @Mode = 1 --addition
begin
	select top(1) @postId = c.PostId, @currentMarkedForDelete = c.MarkedForDelete, @currentParentId = c.ParentId from [b_BlogComment] c where c.ID = @Id;

	if @currentParentId = 0
	begin
		set @currentDepth = 1;
		
		select top(1) @currentLeft = c.RightMargin + 1, @currentRootIndex = c.RootNodeIndex + 1 from [b_BlogComment] c where c.ID <> @Id and c.LeftMargin > 0 AND c.DepthLevel = 1 and c.PostId = @postId order by c.RightMargin desc;
				
		if @currentLeft is NULL
			set @currentLeft = 1;

		if @currentRootIndex is NULL
			set @currentRootIndex = 1;

		set @currentRight = @currentLeft + 1;
		
		--LiveRootNodeIndex
		if @currentMarkedForDelete = 0
			select top(1) @currentLiveRootIndex = c.LiveRootNodeIndex + 1 from [b_BlogComment] c where c.ID <> @Id and c.DepthLevel = 1 and c.PostId = @postId and c.MarkedForDelete = 0 order by c.RightMargin desc;		

		if(@currentLiveRootIndex is NULL)
			set @currentLiveRootIndex = 1;			
	end
	else
	begin
		select top(1) @parentLeft = c.LeftMargin, @parentRight = c.RightMargin, @parentDepth = c.DepthLevel, @parentRootIndex = c.RootNodeIndex, @parentLiveRootIndex = c.LiveRootNodeIndex from [b_BlogComment] c where c.ID = @currentParentId and c.PostId = @postId;	
		set @currentDepth = @parentDepth + 1;
		
		set @currentLeft = @parentRight;
		set @currentRight = @currentLeft + 1;
		set @currentRootIndex = @parentRootIndex;
		set @currentLiveRootIndex = @parentLiveRootIndex;
		
		
		update [b_BlogComment] set LeftMargin = LeftMargin + 2 where LeftMargin > @parentRight and PostId= @postId;
		update [b_BlogComment] set RightMargin = RightMargin + 2 where RightMargin >= @parentRight and PostId= @postId;	
	end

	update [b_BlogComment] set LeftMargin = @currentLeft, RightMargin = @currentRight, DepthLevel = @currentDepth, RootNodeIndex = @currentRootIndex, LiveRootNodeIndex = @currentLiveRootIndex where ID = @Id;
end
else if @Mode = 2 --deletion
begin
	if not exists (select c.ID from [b_BlogComment] c where c.ID = @Id)
		return;
		
	select top(1) @postId = c.PostId, @currentMarkedForDelete = c.MarkedForDelete, @currentLeft = c.LeftMargin, @currentRight = c.RightMargin, @currentDepth = c.DepthLevel, @currentRootIndex = c.RootNodeIndex, @currentLiveRootIndex = c.LiveRootNodeIndex, @currentParentId = c.ParentId  from [b_BlogComment] c where c.ID = @Id;	
	if @postId = 0 or @currentLeft = 0 or @currentRight = 0 or @currentDepth = 0 or @currentRootIndex = 0
		return;

	update [b_BlogComment] set LeftMargin = 0, RightMargin = 0, DepthLevel = 0, RootNodeIndex = 0 where ID = @Id;

	--RootNodeIndex
	--process inner sets (deletion nested sests)
	--update [b_BlogComment] set LeftMargin = 0, RightMargin = 0, DepthLevel = 0, RootNodeIndex = 0 where LeftMargin > @currentLeft and RightMargin < @currentRight and PostId = @postId;
	delete from [b_BlogComment] where LeftMargin > @currentLeft and RightMargin < @currentRight and PostId = @postId;
	--process outer sets
	declare @shift int;
	set @shift = @currentRight - @currentLeft + 1;
	update [b_BlogComment] set LeftMargin = LeftMargin - @shift, RightMargin = RightMargin - @shift where LeftMargin > @currentRight and RightMargin > @currentRight and PostId = @postId;
	update [b_BlogComment] set RightMargin = RightMargin - @shift where LeftMargin < @currentLeft and RightMargin > @currentRight and PostId = @postId;	
	--root node index shift
	if @currentDepth = 1
		update [b_BlogComment] set RootNodeIndex = RootNodeIndex - 1 where RootNodeIndex > @currentRootIndex and PostId = @postId;		
		
	--LiveRootNodeIndex
	if @currentLiveRootIndex > 0
	begin
		if @currentDepth = 1
			update [b_BlogComment] set LiveRootNodeIndex = LiveRootNodeIndex - 1 where LiveRootNodeIndex > @currentLiveRootIndex and PostId = @postId;
		else
		begin
			if @currentParentId is null 
				set @currentParentId = 0;
			
			while @currentParentId > 0
			begin
				set @parentParentId = null;
				select top(1) @parentParentId = c.ParentId from [b_BlogComment] c where c.ID = @currentParentId and c.PostId = @postId;			
				if @parentParentId is null
					set @currentParentId = 0;
				else
				begin
					set @Id = @currentParentId;
					set @currentParentId = @parentParentId;		
				end
			end
			
			select top(1) @currentLeft = c.LeftMargin, @currentRight = c.RightMargin, @currentLiveRootIndex = c.LiveRootNodeIndex from [b_BlogComment] c where c.ID = @Id;
			
			update [b_BlogComment] set LiveRootNodeIndex = 0 where ID = @Id;
			update [b_BlogComment] set LiveRootNodeIndex = 0 where LeftMargin > @currentLeft and RightMargin < @currentRight and PostId = @postId;
			update [b_BlogComment] set LiveRootNodeIndex = LiveRootNodeIndex - 1 where LiveRootNodeIndex > @currentLiveRootIndex and PostId = @postId;						
		end
	end			
end
else --modification
begin
	--print 'modification mode'
	select top(1) @postId = c.PostId, @currentParentId = c.ParentId from [b_BlogComment] c where c.ID = @Id;
	
	if @postId is null return;	
	if @currentParentId is null 
		set @currentParentId = 0;
	
	while @currentParentId > 0
	begin
		set @parentParentId = null;
		select top(1) @parentParentId = c.ParentId from [b_BlogComment] c where c.ID = @currentParentId and c.PostId = @postId;			
		if @parentParentId is null
			set @currentParentId = 0;
		else
		begin
			set @Id = @currentParentId;
			set @currentParentId = @parentParentId;		
		end
	end
	
	--print '@Id = ' + cast(@Id as varchar(20));				
	--print '@currentParentId = ' + cast(@currentParentId as varchar(20));				

	select top(1) @currentLeft = c.LeftMargin, @currentRight = c.RightMargin, @currentMarkedForDelete = c.MarkedForDelete, @currentLiveRootIndex = c.LiveRootNodeIndex from [b_BlogComment] c where c.ID = @Id;
	--print '@currentLiveRootIndex = ' + cast(@currentLiveRootIndex as varchar(20));

	--LiveRootNodeIndex
	if @currentMarkedForDelete = 1
	begin
		select @currentLiveNestedCount = count(c.ID) from [b_BlogComment] c where c.PostId = @postId and c.LeftMargin > @currentLeft and c.RightMargin < @currentRight and c.MarkedForDelete = 0;					
		--if(@currentLiveNestedCount is NULL)
		--	set @currentLiveNestedCount = 0;			
	end	
	
	if(@currentLiveNestedCount is NULL)
		set @currentLiveNestedCount = 0;
		
	--print '@currentMarkedForDelete = ' + cast(@currentMarkedForDelete as varchar(20));				
	--print '@currentLiveNestedCount = ' + cast(@currentLiveNestedCount as varchar(20));


	if @currentMarkedForDelete = 1 and (@currentLiveNestedCount is null or @currentLiveNestedCount = 0)
	begin
		if @currentLiveRootIndex > 0
		begin
			--print 'LiveRootIndex to 0';

			update [b_BlogComment] set LiveRootNodeIndex = 0 where ID = @Id;
			update [b_BlogComment] set LiveRootNodeIndex = 0 where LeftMargin > @currentLeft and RightMargin < @currentRight and PostId = @postId;
			update [b_BlogComment] set LiveRootNodeIndex = LiveRootNodeIndex - 1 where LiveRootNodeIndex > @currentLiveRootIndex and PostId = @postId;			
		end
	end
	else if @currentLiveRootIndex = 0
	begin
		with NewLiveRootIndexes as (select c.ID, c.Contents, c.RootNodeIndex, c.LiveRootNodeIndex, ROW_NUMBER() over(order by c.RootNodeIndex asc) NewLiveRootNodeIndex from b_BlogComment c where c.PostId = @postId and c.DepthLevel = 1 and (c.MarkedForDelete = 0 or exists(select ID from b_BlogComment where MarkedForDelete = 0 and LeftMargin > c.LeftMargin and RightMargin < c.RightMargin)))
			update b_BlogComment set LiveRootNodeIndex = NewLiveRootIndexes.NewLiveRootNodeIndex from NewLiveRootIndexes where NewLiveRootIndexes.ID = b_BlogComment.ID;
		with NewNestedLiveRootIndexes as (select c.ID, c.PostId, c.RootNodeIndex, c.LiveRootNodeIndex from b_BlogComment c where c.PostId = @postId and c.DepthLevel = 1 and (c.MarkedForDelete = 0 or exists(select ID from b_BlogComment where MarkedForDelete = 0 and LeftMargin > c.LeftMargin and RightMargin < c.RightMargin)))
			update b_BlogComment set b_BlogComment.LiveRootNodeIndex = NewNestedLiveRootIndexes.LiveRootNodeIndex from NewNestedLiveRootIndexes where b_BlogComment.DepthLevel > 1 and b_BlogComment.PostId = NewNestedLiveRootIndexes.PostId and b_BlogComment.RootNodeIndex = NewNestedLiveRootIndexes.RootNodeIndex;		
	end	
end
END
GO

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

CREATE PROCEDURE BlogPost_SynchronizePost
	@PostId int,
	@BlogId int = NULL	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @CommentCount int;
	IF (@PostId IS NULL) RETURN;
	IF (@BlogId IS NULL) SELECT @BlogId = BlogId FROM b_BlogPost WHERE ID = @PostId;
	IF (@BlogId IS NULL) RETURN;
	SELECT @CommentCount = COUNT(ID) FROM b_BlogComment WHERE PostId = @PostId AND MarkedForDelete = 0;
	UPDATE b_BlogPost SET CommentCount = @CommentCount WHERE ID = @PostId AND BlogId = @BlogId;
END
GO

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

CREATE PROCEDURE BlogUser_Synchronize
	@UserId int,
	@BlogId int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @PostCount int;
	SELECT @PostCount = COUNT(p.ID) FROM [b_BlogPost] p WHERE p.AuthorId = @UserId;
	
	UPDATE  [b_BlogUser] SET PostCount = @PostCount WHERE ID = @UserId;
END
GO