IF NOT EXISTS  (
SELECT *
FROM sys.columns 
WHERE OBJECT_ID('NB_Tags') is not null)
BEGIN
	CREATE TABLE [dbo].[NB_Tags] (
	[TermId] uniqueidentifier NOT NULL,
	[Labels] nvarchar(max)  NULL,
	[TermSetId] uniqueidentifier NOT NULL,
	PRIMARY KEY CLUSTERED ([TermId] ASC)
);
END

IF NOT EXISTS  (
SELECT *
FROM sys.columns 
WHERE OBJECT_ID('NB_Item_Tags') is not null)
BEGIN
	CREATE TABLE [NB_Item_Tags] (
		[NB_Item_ItemID] int  NOT NULL,
		[NB_Tags_TermId] uniqueidentifier  NOT NULL
	FOREIGN KEY ([NB_Item_ItemID])
		REFERENCES [NB_Item]([ItemID]),
	FOREIGN KEY ([NB_Tags_TermID])
		REFERENCES [NB_Tags]([TermID])
);
END

GO

IF TYPE_ID('NB_GetItems_TagIds') IS NULL
CREATE TYPE NB_GetItems_TagIds AS TABLE
(
	TagId uniqueIdentifier
)

GO

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,
	@TagIds NB_GetItems_TagIds readonly
AS 
BEGIN
SET NOCOUNT ON;

SELECT AuthorId INTO #FilteredAuthors FROM @AuthorIds

-- FilterTags
DECLARE @FilterTagsCount int
SELECT @FilterTagsCount = count(*) FROM @TagIds

DECLARE @FilteredTags TABLE (
                TagId uniqueIdentifier INDEX IX1 CLUSTERED
);
INSERT INTO @FilteredTags
SELECT TagId FROM @TagIds


-- Contenttypes
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

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

-- FilterChannels
DECLARE @FilterChannelsEnabled bit
SET @FilterChannelsEnabled = CASE WHEN EXISTS(SELECT 1 FROM @ChannelIds) THEN 1 ELSE 0 END

SELECT #AllowedChannels.ChannelID
INTO #FilteredChannels
FROM #AllowedChannels
left outer join @ChannelIds myChannels on myChannels.ChannelId = #AllowedChannels.ChannelID
where @FilterChannelsEnabled = 0 or myChannels.ChannelId is not null

-- #### 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 or #FilteredAuthors.AuthorId = NB_Item.AlternateAuthorID
where 
	(
		@FilterTagsCount = 0 
		OR
		(SELECT COUNT(*) FROM NB_Item_Tags inner join @FilteredTags FT on NB_Item_Tags.NB_Tags_TermId = FT.TagId WHERE NB_Item_Tags.NB_Item_ItemID = NB_Item.ItemID) = @FilterTagsCount -- by comparing to FilterTagsCount, there is effectively AND between all tags. If we want OR just compare against >0
    )
	and exists (SELECT NB_Channel_Item.NB_Item_ItemID FROM NB_Channel_Item inner join #FilteredChannels on #FilteredChannels.ChannelID = NB_Channel_Item.NB_Channel_ChannelID where NB_Channel_Item.NB_Item_ItemID = NB_Item.ItemID)
	and
	(
		@isWizdomOrNoticeboardAdmin = 1 or
		Startdate < GETUTCDATE() or 
		NB_Item.AuthorID = @principal or 
		NB_Item.AlternateAuthorID = @principal		
	)
	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
	)
order by NB_Item.Startdate desc
offset (@skip) ROWS FETCH NEXT(@maxTotalCount) ROWS ONLY

-- Get Total count 
select count(*) as TotalCount from #FilteredIds

-- Return data for the filtered items
select
	Channels.Channels,
	Tags.Tags,
	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,
	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
	CROSS APPLY (
		select (	
			SELECT t.*
			FROM NB_Tags t
			INNER JOIN NB_Item_Tags i ON i.NB_Tags_TermId = t.TermId
			WHERE i.NB_Item_ItemId = NB_Item.ItemID
			for XML PATH, root ('rows')
		) Tags
	) Tags

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