ALTER PROCEDURE [dbo].[NBGetItems] 
	@principal int,
	@channelset int = null,
	@view uniqueidentifier = null,
	@itemId int = null,
	@isWizdomOrNoticeboardAdmin bit,
	@take int,
	@skip int,
	@maxTotalCount int, --skip + take must be <= to maxPageableIds
	@LikesToGet int,
	@CommentsToGet int,
	@FilterArchived bit,
	@FilterCurrent bit,
	@FilterFuture bit,
	@FilterMine bit,
	@FilterUnread bit,
	@SearchTerm nvarchar(max) = null
AS
BEGIN

SELECT ChannelID, RightsLevel, CanOnlyEditItsOwn
INTO #AllowedChannels
FROM NB_GetMyChannels(@principal,@isWizdomOrNoticeboardAdmin,@channelset,@view) 

-- #### output pageableIds 
SELECT  NB_Item.ItemID
into #FilteredIds
from NB_Item
	INNER JOIN NB_ContentType on NB_Item.ContentTypeID = NB_ContentType.ContentTypeID
	LEFT OUTER JOIN NB_Read on NB_Read.ItemID = NB_Item.ItemID and NB_Read.PrincipalID = @principal
	CROSS APPLY (
		select (	
			select NB_Channel_Item.NB_Channel_ChannelID id, NB_Channel.name, #AllowedChannels.RightsLevel
			from NB_Channel_Item
			inner join #AllowedChannels on #AllowedChannels.ChannelID = NB_Channel_Item.NB_Channel_ChannelID
			inner join NB_Channel on NB_Channel.ChannelID = NB_Channel_Item.NB_Channel_ChannelID
			where NB_Channel_Item.NB_Item_ItemID = NB_Item.ItemID
			for XML PATH, root ('rows')
		) Channels
	) Channels
where 
	Channels is not null 
	and 
	(
		Startdate < GETUTCDATE() or 
		AuthorID = @principal or 
		AlternateAuthorID = @principal or 
		@isWizdomOrNoticeboardAdmin = 1
	)
	and 
	(
		(@FilterArchived = 0 and @FilterCurrent = 0 and @FilterFuture = 0)  or
		(@FilterArchived = 1 and Enddate < GETUTCDATE()) or
		(@FilterCurrent = 1 and Startdate < GETUTCDATE() AND GETUTCDATE()<Enddate) or
		(@FilterFuture = 1 and GETUTCDATE() < Startdate)
	)
	and
	(
		@FilterMine = 0 or @FilterMine = 1 and (AuthorID = @principal or AlternateAuthorID = @principal)
	)
	and 
	(
		@SearchTerm is null or 
		NB_Item.Heading Like '%' + @SearchTerm + '%'  or 
		NB_Item.Summary Like '%' + @SearchTerm + '%' or 
		NB_Item.Content Like '%' + @SearchTerm + '%' or 
		NB_Item.TranslatedValues Like '%' + @SearchTerm + '%'
	)
	and
	(
		@FilterUnread = 0 or NB_Read.ItemID is null
	)
	and
	(
		@itemId is null or NB_Item.ItemID = @itemId
	)
order by NB_Item.Startdate desc
offset (@skip) ROWS FETCH NEXT(@maxTotalCount) ROWS ONLY

select count(*) as TotalCount from #FilteredIds

select
	NB_Item.ItemID,
	NB_Item.Startdate,
	NB_Item.Enddate,
	NB_Item.Heading,
	NB_Item.Content,
	NB_Item.Summary,
	NB_Item.Modified,
	NB_Item.Created,
	NB_Item.ExtendedProperties,
	NB_Item.ContentTypeID,
	NB_ContentType.Name ContentTypeName,
	NB_ContentType.ExtendedProperties ContentTypeMetaData,
	NB_Item.TranslatedValues,
	Channels.Channels,
	Likes.Likes,
	Comments.Comments,
	CASE WHEN NB_Read.ItemID is null THEN 0 ELSE 1 END as ReadByUser,
	(SELECT PrincipalID id, DisplayName, LoginName  FROM principal WHERE principal.PrincipalID = NB_Item.AuthorID FOR XML PATH, root ('rows')) AS Author,
	(SELECT PrincipalID id, DisplayName, LoginName  FROM principal WHERE principal.PrincipalID = NB_Item.AlternateAuthorID FOR XML PATH, root ('rows')) AS AlternateAuthor
	,(SELECT COUNT(*) from [Like] where ComputedNB_ItemId = NB_Item.ItemID) as LikeCount
	,(SELECT COUNT(*) from [Like] where ComputedNB_ItemId = NB_Item.ItemID AND [Like].PrincipalID = @principal) as LikedByUser
	,(SELECT COUNT(*) from [Comment] where ComputedNB_ItemId = NB_Item.ItemID) as CommentTopLevelCount

	,0 as CommentNestedCount
	--,(SELECT COUNT(*) from [Comment] inner join Comment sub on comment.CommentID = sub.ComputedCommentId where [Comment].ComputedNB_ItemId = NB_Item.ItemID) as CommentNestedCount		
from NB_Item
	INNER JOIN #FilteredIds on #FilteredIds.ItemID = NB_Item.ItemID
	INNER JOIN NB_ContentType on NB_Item.ContentTypeID = NB_ContentType.ContentTypeID
	LEFT OUTER JOIN NB_Read on NB_Read.ItemID = NB_Item.ItemID and NB_Read.PrincipalID = @principal
	CROSS APPLY (
		select (	
			select NB_Channel_Item.NB_Channel_ChannelID id, NB_Channel.name, #AllowedChannels.RightsLevel, #AllowedChannels.CanOnlyEditItsOwn
			from NB_Channel_Item
			inner join #AllowedChannels on #AllowedChannels.ChannelID = NB_Channel_Item.NB_Channel_ChannelID
			inner join NB_Channel on NB_Channel.ChannelID = NB_Channel_Item.NB_Channel_ChannelID
			where NB_Channel_Item.NB_Item_ItemID = NB_Item.ItemID
			for XML PATH, root ('rows')
		) Channels
	) Channels
	CROSS APPLY (
		select (
			select top (@LikesToGet) [Like].LikeID id, principal.principalID, principal.displayName, principal.loginName
			from [Like]
				inner join principal on principal.principalId = [Like].PrincipalID
			where [Like].ComputedNB_ItemId = NB_Item.ItemID
			order by LikeID desc
			for XML PATH, root ('rows')
		) Likes
	) Likes
	CROSS APPLY (
		select (
			select top (@CommentsToGet) 
				[Comment].commentID,
				[Comment].content, 
				[Comment].datePosted, 
				[Comment].dateModified, 
				[Comment].picture,
				[Comment].extendedProperties,
				[Comment].status, 
				Likes.likes,
				--subComments.subComments as replies,
				principal.principalID, 
				principal.displayName, 
				principal.loginName
				,(SELECT COUNT(*) from [Like] where ComputedCommentId = [Comment].commentID) as LikeCount
				,(SELECT COUNT(*) from [Like] where ComputedCommentId = [Comment].commentID AND [Like].PrincipalID = @principal) as LikedByUser
			from [Comment]
				inner join principal on principal.principalId = [Comment].AuthorId
				CROSS APPLY (
					select (
						select top (@LikesToGet) [Like].LikeID id, principal.principalID, principal.displayName, principal.loginName
						from [Like]
							inner join principal on principal.principalId = [Like].PrincipalID
						where [Like].ComputedCommentId = [Comment].commentID
						order by LikeID desc
						for XML PATH, root ('rows')
					) Likes
				) Likes
				--CROSS APPLY (
				--	SELECT(
				--		SELECT 
				--			subComment.commentID,
				--			subComment.content, 
				--			subComment.datePosted, 
				--			subComment.dateModified, 
				--			subComment.picture, 
				--			subComment.status, 
				--			Likes.likes,
				--			subCommentAuthor.principalID, 
				--			subCommentAuthor.displayName, 
				--			subCommentAuthor.loginName
				--			,(SELECT COUNT(*) from [Like] where ComputedCommentId = subComment.commentID) as LikeCount
				--			,(SELECT COUNT(*) from [Like] where ComputedCommentId = subComment.commentID AND [Like].PrincipalID = @principal) as LikedByUser
				--		FROM 
				--			[Comment] subComment
				--			inner join principal as subCommentAuthor on subCommentAuthor.principalId = subComment.AuthorId
				--			CROSS APPLY (
				--				select (
				--					select top (@LikesToGet) [Like].LikeID id, principal.principalID, principal.displayName, principal.loginName
				--					from [Like]
				--						inner join principal on principal.principalId = [Like].PrincipalID
				--					where [Like].ComputedCommentId = subComment.commentID
				--					order by LikeID desc
				--					for XML PATH, root ('rows')
				--				) Likes
				--			) Likes
				--		WHERE 
				--			subComment.ComputedCommentId = [Comment].CommentID
				--		order by subComment.datePosted
				--		for XML PATH, root ('rows')
				--	) subComments
				--) subComments
			where [Comment].ComputedNB_ItemId = NB_Item.ItemID
			order by [Comment].datePosted
			for XML PATH, root ('rows')
		) Comments
	) Comments
order by NB_Item.Startdate desc
offset (0) ROWS FETCH NEXT(@take) ROWS ONLY

END