ALTER PROCEDURE [dbo].[NBGetChannels] 
	@principal int,
	@isWizdomOrNoticeboardAdmin bit,
	@FilterArchived bit,
	@FilterCurrent bit,
	@FilterFuture bit,
	@FilterMine bit,
	@FilterUnread bit,
	@SearchTerm nvarchar(max) = null
AS
BEGIN

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


;WITH ChannelRightsMap as (
	SELECT 
		ChannelID, 
		ParentChannelId,
		ChannelID as RightsChannel, 
		NB_Channel.Name,
		NB_Channel.SortOrder,
		NB_Channel.CanContainItems,
		NB_Channel.CanOnlyEditItsOwn,
		NB_Channel.BreakRightsInherit
	from NB_Channel where BreakRightsInherit = 1
	
	UNION ALL
	
	SELECT 
		NB_Channel.ChannelID,
		NB_Channel.ParentChannelId,
		ChannelRightsMap.RightsChannel,
		NB_Channel.Name,
		NB_Channel.SortOrder,
		NB_Channel.CanContainItems,
		NB_Channel.CanOnlyEditItsOwn,
		NB_Channel.BreakRightsInherit
	from NB_Channel
	inner join ChannelRightsMap on ChannelRightsMap.ChannelID = NB_Channel.ParentChannelId
	where NB_Channel.BreakRightsInherit = 0 

),
AllowedChannels as ( -- filtered by read rights for the current @principal
	select 
		ChannelRightsMap.ChannelID, 
		case -- if user is admin, he'll get write access
			when @isWizdomOrNoticeboardAdmin = 1 then 2  
			else MAX(NB_ChannelRights.Level)
		end as RightsLevel, 
		ChannelRightsMap.RightsChannel, 
		ChannelRightsMap.ParentChannelId,
		ChannelRightsMap.Name,
		ChannelRightsMap.SortOrder,
		ChannelRightsMap.CanContainItems,
		ChannelRightsMap.CanOnlyEditItsOwn,
		ChannelRightsMap.BreakRightsInherit
	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.RightsChannel, 
		ChannelRightsMap.ParentChannelId,
		ChannelRightsMap.Name,
		ChannelRightsMap.SortOrder,
		ChannelRightsMap.CanContainItems,
		ChannelRightsMap.CanOnlyEditItsOwn,
		ChannelRightsMap.BreakRightsInherit
)
SELECT AllowedChannels.*
INTO #AllowedChannels
FROM AllowedChannels 

SELECT  
	#AllowedChannels.ChannelID,
	#AllowedChannels.RightsLevel,
	#AllowedChannels.RightsChannel,
	#AllowedChannels.ParentChannelID,
	#AllowedChannels.Name,
	#AllowedChannels.SortOrder,
	#AllowedChannels.CanContainItems,
	#AllowedChannels.CanOnlyEditItsOwn,
	(
		SELECT COUNT(NB_Channel_Item.NB_Item_ItemID) 
		from NB_Channel_Item 
			inner join NB_Item on NB_Item.ItemID = NB_Channel_Item.NB_Item_ItemID
			LEFT OUTER JOIN NB_Read on NB_Read.ItemID = NB_Item.ItemID and NB_Read.PrincipalID = @principal
		where NB_Channel_Item.NB_Channel_ChannelID = #AllowedChannels.ChannelID
			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
			)
		) as itemCount,
		Rights.Rights,
		#AllowedChannels.BreakRightsInherit
FROM #AllowedChannels
	CROSS APPLY (
		select (	
			select 
				NB_ChannelRights.Level, 
				principal.principalID, 
				principal.displayName, 
				principal.loginName,
				principal.GroupId,
				Principal.Type
			from NB_ChannelRights
			inner join Principal on Principal.PrincipalID = NB_ChannelRights.PrincipalID
			where #AllowedChannels.ChannelID = NB_ChannelRights.ChannelID AND #AllowedChannels.BreakRightsInherit = 1 AND @isWizdomOrNoticeboardAdmin = 1
			for XML PATH, root ('rows')
		) Rights
	) Rights
GROUP by 
	#AllowedChannels.ChannelID, 
	#AllowedChannels.RightsLevel, 
	#AllowedChannels.RightsChannel, 
	#AllowedChannels.ParentChannelID,
	#AllowedChannels.Name,
	#AllowedChannels.SortOrder,
	#AllowedChannels.CanContainItems,
	#AllowedChannels.CanOnlyEditItsOwn,
	#AllowedChannels.BreakRightsInherit,
	Rights.Rights
ORDER BY ChannelID

END