IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Object_ID = Object_ID(N'Principal') AND Name = N'ActivePrincipal')
BEGIN
	ALTER TABLE [dbo].[Principal] ADD ActivePrincipal bit NOT NULL DEFAULT  1;
END;


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Object_ID = Object_ID(N'Principal') AND Name = N'EmailLanguageTag')
BEGIN
	ALTER TABLE [dbo].[Principal] ADD EmailLanguageTag nvarchar(12) NULL;
END;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[NBGetItems_V2] 
	@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,
	@ShowDeletedComments bit = 0,
	@ChannelIds NB_GetItems_ChannelIds readonly,
	@AuthorIds NB_GetItems_AuthorIds readonly,
	@TagIds NB_GetItems_TagIds readonly
AS 
BEGIN
SET NOCOUNT ON;

--if(@MinStartDate is null)
--BEGIN
--	SET @MinStartDate = CAST(-53690 as datetime)
--END
--if(@MinEndDate is null)
--BEGIN
--	SET @MinEndDate = CAST(-53690 as datetime)
--END

-- Get channelset
if(@channelset is null)
BEGIN
	SELECT @channelset = ChannelSetID FROM NB_View WHERE ViewID = @view
END

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

DECLARE @AllowedChannels TABLE (
	ChannelID INT INDEX IX1 CLUSTERED,
	RightsLevel int,
	CanOnlyEditItsOwn bit
);
DECLARE @principalIds TABLE(
	id int INDEX IX1 CLUSTERED
)
INSERT INTO @principalIds(id) VALUES(1)
INSERT INTO @principalIds(id) VALUES(@principal)
INSERT INTO @principalIds(id)
	SELECT Groups_PrincipalID as id FROM PrincipalGrouping WHERE Members_PrincipalID = @principal

IF(@channelset is not null) 
BEGIN
	INSERT INTO @AllowedChannels
	SELECT NB_ChannelSetChannel.ChannelID, MAX(NB_ChannelRights.Level), ISNULL(NB_Channel.CanOnlyEditItsOwn, 0)
	FROM NB_ChannelSetChannel
		INNER JOIN NB_Channel ON NB_Channel.ChannelID = NB_ChannelSetChannel.ChannelID
		INNER JOIN NB_ChannelRights
			ON NB_ChannelRights.ChannelID = NB_ChannelSetChannel.PermissionChannel or @isWizdomOrNoticeboardAdmin = 1
	--	LEFT JOIN (SELECT value as ID from STRING_SPLIT(@principalIds, ',')) as filteredPrincipals 
	--		ON NB_ChannelRights.PrincipalID = filteredPrincipals.ID
	WHERE
		NB_ChannelRights.PrincipalID IN (SELECT id from @principalIds) AND
		NB_ChannelSetChannel.ChannelSetId = @channelSet AND
		NB_ChannelSetChannel.PrincipalID IN (SELECT id from @principalIds) AND
		-- Type 2 is Suggested channels. So channel should either not be suggested, or if suggested then it should not be denied.
		(NB_ChannelSetChannel.Type <> 2 OR NOT EXISTS(select 1 from NB_SuggestedDeny where NB_SuggestedDeny.PrincipalID = @principal AND NB_SuggestedDeny.NB_ChannelSetID = @channelset AND NB_SuggestedDeny.NB_ChannelId = NB_ChannelSetChannel.ChannelID))
	GROUP BY NB_ChannelSetChannel.ChannelID, ISNULL(NB_Channel.CanOnlyEditItsOwn, 0)
END
ELSE
BEGIN
	INSERT INTO @AllowedChannels
	SELECT 
		NB_Channel.ChannelID, MAX(NB_ChannelRights.Level), ISNULL(NB_Channel.CanOnlyEditItsOwn, 0)
	FROM
		NB_Channel INNER JOIN NB_ChannelRights
			ON NB_ChannelRights.ChannelID = NB_Channel.PermissionChannel or @isWizdomOrNoticeboardAdmin = 1
	WHERE
		NB_ChannelRights.PrincipalID IN (SELECT id from @principalIds)
	GROUP BY NB_Channel.ChannelID, ISNULL(NB_Channel.CanOnlyEditItsOwn, 0)
END
-- FilterChannels
DECLARE @FilterChannelsEnabled bit
SET @FilterChannelsEnabled = CASE WHEN EXISTS(SELECT 1 FROM @ChannelIds) THEN 1 ELSE 0 END

DECLARE @FilteredChannels TABLE (
	ChannelID INT INDEX IX1 CLUSTERED
);

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

-- FilterAuthors
DECLARE @FilteredAuthors TABLE (
	AuthorId INT INDEX IX1 CLUSTERED
);
INSERT INTO @FilteredAuthors
SELECT AuthorId 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


DECLARE @FilteredIds TABLE (
	ItemID INT INDEX IX1 CLUSTERED
);

-- #### Get filtered nb item ids 
INSERT into @FilteredIds 
SELECT  NB_Item.ItemID
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 as FilteredAuthors ON FilteredAuthors.AuthorId = NB_Item.AuthorID or FilteredAuthors.AuthorId = NB_Item.AlternateAuthorID
	LEFT OUTER JOIN NB_Item_Tags on NB_Item_Tags.NB_Item_ItemID = NB_Item.ItemID
		LEFT OUTER JOIN @FilteredTags FT on NB_Item_Tags.NB_Tags_TermId = FT.TagId
where 
	exists (SELECT NB_Channel_Item.NB_Item_ItemID FROM NB_Channel_Item inner join @FilteredChannels as 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
	(
		(Startdate >= @MinStartDate) AND
		(Startdate <= @MaxStartDate)
	)
	and
	(
		(Enddate >= @MinEndDate) AND
		(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
	)
group by NB_Item.ItemID, NB_Item.Startdate
having
	@FilterTagsCount = COUNT(FT.TagId)
order by NB_Item.Startdate desc
offset (@skip) ROWS FETCH NEXT(@maxTotalCount) ROWS ONLY


DECLARE @FilteredIdsTotalCount TABLE (
	ItemID INT INDEX IX1 CLUSTERED
);

-- #### Get filtered nb item ids 
INSERT into @FilteredIdsTotalCount 
SELECT  NB_Item.ItemID
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 as FilteredAuthors ON FilteredAuthors.AuthorId = NB_Item.AuthorID or FilteredAuthors.AuthorId = NB_Item.AlternateAuthorID
	LEFT OUTER JOIN NB_Item_Tags on NB_Item_Tags.NB_Item_ItemID = NB_Item.ItemID
		LEFT OUTER JOIN @FilteredTags FT on NB_Item_Tags.NB_Tags_TermId = FT.TagId
where 
	exists (SELECT NB_Channel_Item.NB_Item_ItemID FROM NB_Channel_Item inner join @FilteredChannels as 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
	(
		(Startdate >= @MinStartDate) AND
		(Startdate <= @MaxStartDate)
	)
	and
	(
		(Enddate >= @MinEndDate) AND
		(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
	)
group by NB_Item.ItemID, NB_Item.Startdate
having
	@FilterTagsCount = COUNT(FT.TagId)
order by NB_Item.Startdate desc



-- Get Total count 
select count(*) as TotalCount from @FilteredIdsTotalCount

DECLARE @IdsToReturn TABLE (
	ItemID INT INDEX IX1 CLUSTERED
);

INSERT INTO @IdsToReturn
SELECT TOP (@take) NB_Item.ItemID FROM NB_Item inner join @FilteredIds Fids on Fids.ItemID = NB_Item.itemId ORDER BY NB_Item.Startdate desc


-- 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 and  [status] <> 2) as CommentTopLevelCount,
	
	(SELECT COUNT(*) from [Comment] inner join Comment sub on comment.CommentID = sub.ComputedCommentId
	where [Comment].ComputedNB_ItemId = NB_Item.ItemID and (comment.Status <> 2 and sub.Status <> 2))
		as CommentNestedCount
	
	from NB_Item
	INNER JOIN @IdsToReturn as IdsToReturn on IdsToReturn.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, ISNULL(AllowedChannels.CanOnlyEditItsOwn, 0) as CanOnlyEditItsOwn
			from NB_Channel_Item
			inner join @AllowedChannels as 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

DECLARE @CommentsIds TABLE (
	TopComment INT INDEX IX1,
	ReplyComment INT INDEX IX2
);

-- Find comment ids "Map"
IF(@CommentsToGet>0)
BEGIN
	INSERT INTO @CommentsIds
	SELECT TopComment, ReplyComment
	FROM 
		(SELECT * FROM @IdsToReturn) IdsToReturn
		OUTER APPLY (SELECT TOP (@CommentsToGet) Comment.CommentID as TopComment FROM Comment WHERE Comment.ComputedNB_ItemId = IdsToReturn.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 as 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 
		IdsToReturn.ItemID as NbItemId,
		Likes.LikeID,
		principal.PrincipalID,
		principal.DisplayName,
		principal.LoginName
	FROM 
		(SELECT * FROM @IdsToReturn) IdsToReturn
		CROSS APPLY (SELECT TOP (@LikesToGet) [Like].LikeID, [Like].PrincipalID FROM [Like] WHERE [Like].ComputedNB_ItemId = IdsToReturn.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 
		@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 
		@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