CREATE FUNCTION [dbo].[NB_GetMyChannels]
(	
	-- Add the parameters for the function here
	@principal int,
	@isWizdomOrNoticeboardAdmin bit,
	@channelset int = null,
	@view uniqueidentifier = null
)

RETURNS 
@AllowedChannels TABLE
(
        ChannelID int,
        Name nvarchar(max),
		RightsChannel int,
		RightsLevel int,
		CanOnlyEditItsOwn bit,
		BreakRightsInherit bit,
		CanContainItems bit,
		SortOrder int,
		ParentChannelID int
)
AS
BEGIN
	-- get channelset
  	if(@channelset is null)
	BEGIN
		SELECT @channelset = ChannelSetID FROM NB_View WHERE ViewID = @view
	END
	
	DECLARE @NB_GetMyChannels_MyGroups AS NB_GetMyChannels_MyGroups 

	-- get user pricipalids (groups, everyone and the principal itself)
	INSERT INTO @NB_GetMyChannels_MyGroups SELECT Groups_PrincipalID FROM PrincipalGrouping WHERE Members_PrincipalID = @principal
	INSERT INTO @NB_GetMyChannels_MyGroups VALUES(@principal)
	INSERT INTO @NB_GetMyChannels_MyGroups VALUES(1) -- everyone

	;WITH Mandatory as( -- get mandatory channels for this channelset
		SELECT DISTINCT(NB_Mandatory.ChannelID)
		FROM NB_Mandatory 
			inner join @NB_GetMyChannels_MyGroups as NB_GetMyChannels_MyGroups on NB_GetMyChannels_MyGroups.Groups_PrincipalID = NB_Mandatory.PrincipalID
		WHERE ChannelSetID = @channelset 
	)
	,Suggested as ( -- get suggested channels
		SELECT DISTINCT(NB_Suggested.ChannelID)
		FROM NB_Suggested 
			inner join @NB_GetMyChannels_MyGroups as NB_GetMyChannels_MyGroups on NB_GetMyChannels_MyGroups.Groups_PrincipalID = NB_Suggested.PrincipalID
			LEFT OUTER JOIN NB_SuggestedDeny ON NB_Suggested.ChannelSetID = NB_SuggestedDeny.NB_ChannelSetID AND NB_Suggested.ChannelID = NB_SuggestedDeny.NB_ChannelId AND NB_SuggestedDeny.PrincipalID = @principal
		WHERE ChannelSetID = @channelset and NB_SuggestedDeny.PrincipalID is null 
	) 
	,Personal as ( -- get personally selected channels
		SELECT ChannelID
		from NB_Personal
		where PrincipalID = @principal and ChannelSetID = @channelset
	), 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 

	),
	SelectedChannels as ( -- filters the rightsmap to only the mandatory, suggested and personal selectedChannels
		select 
			ChannelRightsMap.* 
		from 
			ChannelRightsMap
			LEFT OUTER JOIN Mandatory on Mandatory.ChannelID = ChannelRightsMap.ChannelID
			LEFT OUTER JOIN Suggested on Suggested.ChannelID = ChannelRightsMap.ChannelID
			LEFT OUTER JOIN Personal on Personal.ChannelID = ChannelRightsMap.ChannelID
		where 
			Mandatory.ChannelID is not null or 
			Suggested.ChannelID is not null or
			Personal.ChannelID is not null
	),
	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 @NB_GetMyChannels_MyGroups as NB_GetMyChannels_MyGroups on NB_ChannelRights.PrincipalID = NB_GetMyChannels_MyGroups.Groups_PrincipalID
		where
			(
				@isWizdomOrNoticeboardAdmin = 1 or -- is admin
				NB_GetMyChannels_MyGroups.Groups_PrincipalID is not null
			)
		group by 
			ChannelRightsMap.ChannelID, 
			ChannelRightsMap.RightsChannel, 
			ChannelRightsMap.ParentChannelId,
			ChannelRightsMap.Name,
			ChannelRightsMap.SortOrder,
			ChannelRightsMap.CanContainItems,
			ChannelRightsMap.CanOnlyEditItsOwn,
			ChannelRightsMap.BreakRightsInherit
	)
	INSERT INTO @AllowedChannels 
	SELECT 
		AllowedChannels.ChannelID, 
		AllowedChannels.Name,
		AllowedChannels.RightsChannel,
		AllowedChannels.RightsLevel,
		AllowedChannels.CanOnlyEditItsOwn,
		AllowedChannels.BreakRightsInherit,
		AllowedChannels.CanContainItems,
		AllowedChannels.SortOrder,
		AllowedChannels.ParentChannelID
	FROM AllowedChannels
	LEFT OUTER JOIN SelectedChannels  on SelectedChannels.ChannelID = AllowedChannels.ChannelID
	WHERE SelectedChannels.ChannelID is not null or @channelset is null

	RETURN
END