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) 

-- #### Get filtered nb item ids 
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.SearchableText 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_Item.ExternalID,
	NB_ContentType.Name ContentTypeName,
	NB_ContentType.ExtendedProperties ContentTypeMetaData,
	NB_ContentType.Active ContentTypeIsActive,
	NB_Item.TranslatedValues,
	Channels.Channels,
	CASE WHEN NB_Read.ItemID is null THEN 0 ELSE 1 END as ReadByUser,
	CASE WHEN [Like].PrincipalID is null THEN 0 ELSE 1 END as LikedByCurrentUser,
	(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 [Comment] where ComputedNB_ItemId = NB_Item.ItemID) as CommentTopLevelCount,
	(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
	LEFT OUTER JOIN [Like] on [Like].ComputedNB_ItemId = NB_Item.ItemID AND [Like].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

order by NB_Item.Startdate desc
offset (0) ROWS FETCH NEXT(@take) ROWS ONLY

-- Find comment ids "Map"
IF(@CommentsToGet>0)
BEGIN
	SELECT *
	INTO #CommentsIds
	FROM 
		(SELECT TOP (@take) * FROM #FilteredIds) FilteredIds
		OUTER APPLY (SELECT TOP (@CommentsToGet) Comment.CommentID as TopComment FROM Comment WHERE Comment.ComputedNB_ItemId = FilteredIds.ItemID ORDER BY Comment.CommentID desc) as TopComments
		OUTER APPLY (SELECT Comment.CommentID as ReplyComment FROM Comment WHERE Comment.ComputedCommentId = TopComments.TopComment) as ReplyComments

	SELECT -- SELECT Comments, use targetID and TargetType to match with noticeboard items and comment
		CommentID, 
		Content,
		DatePosted, 
		DateModified,
		Picture,
		ExtendedProperties,
		Status,
		Comment.TargetID,
		Comment.TargetType,
		principal.PrincipalID,
		principal.DisplayName,
		principal.LoginName,
		case when [Like].PrincipalID is null then 0 else 1 end as LikedByCurrentUser,
		(SELECT COUNT(*) from [Like] where ComputedCommentId = [Comment].commentID) as LikeCount
	FROM Comment 
		INNER JOIN #CommentsIds on #CommentsIds.TopComment = CommentID OR #CommentsIds.ReplyComment = CommentID
		INNER JOIN principal on principal.principalId = [Comment].AuthorId
		LEFT OUTER JOIN [Like] on [Like].ComputedCommentId = CommentID AND [Like].PrincipalID = @principal 
	GROUP BY
		CommentID, 
		Content,
		DatePosted, 
		DateModified,
		Picture,
		ExtendedProperties,
		Status,
		Comment.TargetID,
		Comment.TargetType,
		principal.PrincipalID,
		principal.DisplayName,
		principal.LoginName,
		[Like].PrincipalID
	ORDER BY Comment.datePosted
END

IF(@LikesToGet>0)
BEGIN
	-- SELECT likes, for nb items
	SELECT 
		FilteredIds.ItemID as NbItemId,
		Likes.LikeID,
		principal.PrincipalID,
		principal.DisplayName,
		principal.LoginName
	FROM 
		(SELECT TOP (@take) * FROM #FilteredIds) FilteredIds
		CROSS APPLY (SELECT TOP (@LikesToGet) [Like].LikeID, [Like].PrincipalID FROM [Like] WHERE [Like].ComputedNB_ItemId = FilteredIds.ItemID ORDER BY [Like].LikeID desc) as Likes
		INNER JOIN principal on principal.principalId = Likes.PrincipalID
END

IF(@CommentsToGet>0 AND @LikesToGet>0)
BEGIN
	-- SELECT likes, for top level comments
	SELECT 
		CommentsIds.TopComment,
		Likes.LikeID,
		principal.PrincipalID,
		principal.DisplayName,
		principal.LoginName
	FROM 
		(SELECT TOP (@take) * FROM #CommentsIds) CommentsIds
		CROSS APPLY (SELECT TOP (@LikesToGet) [Like].LikeID, [Like].PrincipalID FROM [Like] WHERE [Like].ComputedCommentId = CommentsIds.TopComment ORDER BY [Like].LikeID desc) as Likes
		INNER JOIN principal on principal.principalId = Likes.PrincipalID
	GROUP BY
		CommentsIds.TopComment,
		Likes.LikeID,
		principal.PrincipalID,
		principal.DisplayName,
		principal.LoginName

	-- SELECT likes, for reply comments
	SELECT 
		CommentsIds.ReplyComment,
		Likes.LikeID,
		principal.PrincipalID,
		principal.DisplayName,
		principal.LoginName
	FROM 
		(SELECT TOP (@take) * FROM #CommentsIds) CommentsIds
		CROSS APPLY (SELECT TOP (@LikesToGet) [Like].LikeID, [Like].PrincipalID FROM [Like] WHERE [Like].ComputedCommentId = CommentsIds.ReplyComment ORDER BY [Like].LikeID desc) as Likes
		INNER JOIN principal on principal.principalId = Likes.PrincipalID
END

END