SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.OmniaSetting', 'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[OmniaSetting](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [OmniaAdminUrl] nvarchar(MAX) NULL,
        [OmniaUrl] nvarchar(MAX) NULL,
        [ExtendedProperties] nvarchar(MAX) NULL
    )
END

IF COL_LENGTH('[dbo].[NB_Channel]', 'DeletedAt') IS NULL
BEGIN
ALTER TABLE [dbo].[NB_Channel]
ADD [DeletedAt] datetimeoffset(7)
DEFAULT NULL
END

/****** Object:  StoredProcedure [dbo].[NBGetItems_V2]    Script Date: 7/28/2025 1:13:13 PM ******/
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,
@CommentsSortBy nvarchar(255) = null,
--@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,
@PinnedIds nvarchar(max) = null,
@IsPinnedItemOnly bit = 0
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
NB_Channel.DeletedAt IS NULL 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)
AND NB_Channel.DeletedAt IS NULL								
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
);

if @PinnedIds is not null AND @IsPinnedItemOnly = 1
BEGIN
	INSERT into @FilteredIds
	SELECT DISTINCT NB_Item.ItemID FROM NB_Item 
				inner join NB_Channel_Item on NB_Item.ItemID = NB_Channel_Item.NB_Item_ItemID
				inner join NB_Channel on NB_Channel.ChannelID = NB_Channel_Item.NB_Channel_ChannelID
	WHERE NB_Channel.ChannelID in (select ChannelID from @AllowedChannels)
	AND NB_Item.ItemID in (SELECT value FROM STRING_SPLIT(@PinnedIds, ','))
END

ELSE
BEGIN
-- #### 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
)
and NB_Item.ItemID not in (select cast(value as int) from string_split(@PinnedIds, ','))
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

END

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
--(CASE
--   WHEN @CommentsSortBy='MostReplies' THEN (SELECT COUNT(*) from [Comment] sub where sub.ComputedCommentId = [Comment].commentID)
--   WHEN @CommentsSortBy<>'MostReplies' THEN (SELECT 0)
--END) as RepliesCount
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.TargetType,
CASE
       WHEN @CommentsSortBy='MostLikes' THEN (SELECT COUNT(*) from [Like] where ComputedCommentId = [Comment].commentID)
       WHEN @CommentsSortBy='MostReplies' THEN (SELECT COUNT(*) from [Comment] sub where sub.ComputedCommentId = [Comment].commentID)
END DESC,
CASE
  WHEN @CommentsSortBy='Newest' THEN [Comment].DatePosted
END DESC,
CASE
  WHEN @CommentsSortBy='Oldest' THEN [Comment].DatePosted
END
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
