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,
	@FilterNews int,
	@FilterVideo int,
	@FilterRSS int,
	@FilterOffice365Video int,
	@FilterCorporateNews int,
	@MinStartDate datetimeoffset = null,
	@MaxStartDate datetimeoffset = null,
	@MinEndDate datetimeoffset = null,
	@MaxEndDate datetimeoffset = null,
	@SearchTerm nvarchar(max) = null,
	@ChannelIds NB_GetItems_ChannelIds readonly,
	@AuthorIds NB_GetItems_AuthorIds readonly
AS 
BEGIN
SET NOCOUNT ON;

DECLARE @FilterChannelsEnabled bit
SELECT ChannelId INTO #FilteredChannels FROM @ChannelIds
SELECT AuthorId INTO #FilteredAuthors FROM @AuthorIds
SET @FilterChannelsEnabled = CASE WHEN EXISTS(SELECT 1 FROM #FilteredChannels) THEN 1 ELSE 0 END

DECLARE @AnyContentTypeFiltersEnabled bit
SET @AnyContentTypeFiltersEnabled = CASE WHEN 1 in (@FilterNews, @FilterVideo, @FilterRSS, @FilterOffice365Video, @FilterCorporateNews) THEN 1 ELSE 0 END
--SET @FilterNews= CASE WHEN @FilterNews = 1 THEN 1 ELSE 0 END
SET @FilterVideo= CASE WHEN @FilterVideo = 1 THEN 2 ELSE 0 END
SET @FilterRSS= CASE WHEN @FilterRSS = 1 THEN 3 ELSE 0 END
SET @FilterOffice365Video= CASE WHEN @FilterOffice365Video = 1 THEN 4 ELSE 0 END
SET @FilterCorporateNews= CASE WHEN @FilterCorporateNews = 1 THEN 5 ELSE 0 END

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

-- #### 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
	--left join #FilteredAuthors on #FilteredAuthors.AuthorId = NB_Item.AuthorID
	--left join #FilteredAuthors as #FilteredAlternateAuthors on #FilteredAlternateAuthors.AuthorId = NB_Item.AlternateAuthorID
	left join #FilteredAuthors on #FilteredAuthors.AuthorId = NB_Item.AuthorID or #FilteredAuthors.AuthorId = NB_Item.AlternateAuthorID
	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
			left join #FilteredChannels on #FilteredChannels.ChannelId = NB_Channel_Item.NB_Channel_ChannelID
			where NB_Channel_Item.NB_Item_ItemID = NB_Item.ItemID
			and 
			(
				#FilteredChannels.ChannelId is not null or 
				@FilterChannelsEnabled = 0
			)
			for XML PATH, root ('rows')
		) Channels
	) Channels
where 
	Channels is not null 
	and 
	(
		@isWizdomOrNoticeboardAdmin = 1 or
		Startdate < GETUTCDATE() or 
		NB_Item.AuthorID = @principal or 
		NB_Item.AlternateAuthorID = @principal		
	)
	--and 
	--(
	--	(@FilterArchived = 0 or Enddate < GETUTCDATE()) AND
	--	(@FilterCurrent = 0 or (Startdate < GETUTCDATE() AND GETUTCDATE()<Enddate)) AND
	--	(@FilterFuture = 0 or GETUTCDATE() < Startdate)
		
	--	--(@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 NB_Item.AuthorID = @principal or NB_Item.AlternateAuthorID = @principal
	)
	and
	(
		(@MinStartDate is null or Startdate >= @MinStartDate) AND
		(@MaxStartDate is null or Startdate <= @MaxStartDate)
	)
	and
	(
		(@MinEndDate is null or Enddate >= @MinEndDate) AND
		(@MaxEndDate is null or Enddate <= @MaxEndDate)
	)
	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
	)
	and
	(
		@AnyContentTypeFiltersEnabled = 0 or
		NB_Item.ContentTypeID in (@FilterNews, @FilterVideo, @FilterRSS, @FilterOffice365Video, @FilterCorporateNews)
	)
	and 
	(
		not exists (select top 1 AuthorId from #FilteredAuthors)
		or
		#FilteredAuthors.AuthorId is not null
		--or 
		--#FilteredAlternateAuthors.AuthorId is not null
	)
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