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

if(@channelset is null)
BEGIN
	SELECT @channelset = ChannelSetID from NB_View where ViewID = @view
END

select Groups_PrincipalID into #mygroups  from PrincipalGrouping where Members_PrincipalID = @principal
insert into #mygroups values(@principal)
insert into #mygroups values(1) -- everyone


;WITH Mandatory as( -- get mandatory channels for this channelset
	SELECT DISTINCT(NB_Mandatory.ChannelID)
	FROM NB_Mandatory 
		inner join #mygroups on #mygroups.Groups_PrincipalID = NB_Mandatory.PrincipalID
	WHERE ChannelSetID = @channelset 
)
,Suggested as ( -- get suggested channels
	SELECT DISTINCT(NB_Suggested.ChannelID)
	FROM NB_Suggested 
		inner join #mygroups on #mygroups.Groups_PrincipalID = NB_Suggested.PrincipalID
		LEFT OUTER JOIN NB_SuggestedDeny ON NB_Suggested.ChannelSetID = NB_SuggestedDeny.NB_ChannelSetID AND NB_Suggested.ChannelID = NB_SuggestedDeny.NB_ChannelId AND NB_SuggestedDeny.PrincipalID = @principal
	WHERE ChannelSetID = @channelset and NB_SuggestedDeny.PrincipalID is null 
) 
, Personal as ( -- get personally selected channels
	SELECT ChannelID
	from NB_Personal
	where PrincipalID = @principal and ChannelSetID = @channelset
)
, ChannelRightsMap as (
	SELECT 
		ChannelID, 
		CanOnlyEditItsOwn,
		ChannelID as RightsChannel
	from NB_Channel where BreakRightsInherit = 1
	
	UNION ALL
	
	SELECT 
		NB_Channel.ChannelID, 
		NB_Channel.CanOnlyEditItsOwn,
		ChannelRightsMap.RightsChannel
	from NB_Channel
	inner join ChannelRightsMap on ChannelRightsMap.ChannelID = NB_Channel.ParentChannelId
	where NB_Channel.BreakRightsInherit = 0 

),
SelectedChannels as ( -- filters the rightsmap to only the mandatory, suggested and personal selectedChannels
	select 
		ChannelRightsMap.* 
	from 
		ChannelRightsMap
		LEFT OUTER JOIN Mandatory on Mandatory.ChannelID = ChannelRightsMap.ChannelID
		LEFT OUTER JOIN Suggested on Suggested.ChannelID = ChannelRightsMap.ChannelID
		LEFT OUTER JOIN Personal on Personal.ChannelID = ChannelRightsMap.ChannelID
	where 
		Mandatory.ChannelID is not null or 
		Suggested.ChannelID is not null or
		Personal.ChannelID is not null
),
AllowedChannels as ( -- filtered by read rights for the current @principal
	select 
		ChannelRightsMap.ChannelID, 
		MAX(NB_ChannelRights.Level) as RightsLevel,
		ChannelRightsMap.CanOnlyEditItsOwn
	from 
		ChannelRightsMap
		LEFT OUTER JOIN NB_ChannelRights on NB_ChannelRights.ChannelID = ChannelRightsMap.RightsChannel
		LEFT OUTER JOIN #mygroups on NB_ChannelRights.PrincipalID = #mygroups.Groups_PrincipalID
	where
		(
			@isWizdomOrNoticeboardAdmin = 1 or -- is admin
			#mygroups.Groups_PrincipalID is not null
		)
	group by ChannelRightsMap.ChannelID, ChannelRightsMap.CanOnlyEditItsOwn
)
SELECT AllowedChannels.*
INTO #AllowedChannels
--FROM SelectedChannels inner join AllowedChannels on SelectedChannels.ChannelID = AllowedChannels.ChannelID  
FROM AllowedChannels left outer join SelectedChannels  on SelectedChannels.ChannelID = AllowedChannels.ChannelID
WHERE SelectedChannels.ChannelID is not null or @channelset is null

-- #### 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
	,(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].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
