CREATE PROCEDURE [dbo].[ContentGetWithAudiencesAndTranslationsExcludingPersonalNodes]
	@view uniqueidentifier,
	@StartingNode uniqueidentifier,
	@MaxDepth int = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	;
	-- Get tree nodes, including personalNodes, med en kolonne som indikere om en given node er Personlig eller ej. 
	WITH EntireTreeIncludingPersonalNodes as(
		select 
			CAST(null as uniqueidentifier) as ParentId, 
			@view as ChildId, 
			0 as SortOrder, 
			CAST(0 as int) as IsPersonal,
			case when @view = @StartingNode then 1 else 0 end as PartOfStartingNode,
			1 as level

		UNION ALL

		select 
			child.*, 
			ISNULL(Principal.Type,ISNULL(parent.IsPersonal,0)) as IsPersonal, --Type is filtered by type 1 which is personal
			case when child.ChildId = @StartingNode or parent.PartOfStartingNode = 1 then 1 else 0 end as PartOfStartingNode,
			Parent.level + 1 as level
		from EntireTreeIncludingPersonalNodes parent 
		inner join ContentParentChildRelations child on Parent.ChildId = child.ParentId
		OUTER APPLY (
			SELECT Principal.Type 
			FROM ContentAudience inner join
				Principal on Principal.PrincipalID = ContentAudience.PrincipalId
			WHERE ContentAudience.ContextView = @view AND ContentAudience.ContentId = child.ChildId and Principal.Type = 1 -- user type
			) as Principal
	)
	select *
	into #EntireTreeIncludingPersonalNodes 
	from EntireTreeIncludingPersonalNodes -- execute the CTE into a temp table, so we can reuse it
	where PartOfStartingNode = 1

	-- find the startingnode level, which is used for maxDepth
	declare @StartingNodeLevel int
	select @StartingNodeLevel = level from #EntireTreeIncludingPersonalNodes where ChildId = @StartingNode

	--select @StartingNodeLevel as startLevel

	-- Get tree nodes, without the personal nodes. Men en kolonne som indikere hvor mange Personlige subnodes som findes til hver node
	select 
		tree.ParentId, 
		tree.ChildId, 
		tree.SortOrder,
		isnull(sum(child.IsPersonal),0) as PersonalSubnodeCount,
		tree.level
	into #TreeWithoutPersonalNodes
	from #EntireTreeIncludingPersonalNodes tree
		left outer join #EntireTreeIncludingPersonalNodes child on child.ParentId = tree.ChildId
	where tree.IsPersonal=0 and (@MaxDepth is null or tree.level <= @MaxDepth + @StartingNodeLevel)
	Group by tree.ParentId, tree.ChildId, tree.SortOrder, tree.level

	-- SELECT Tree with data from content, for output
	select tree.ParentId, tree.SortOrder, Tree.PersonalSubnodeCount, content.* from #TreeWithoutPersonalNodes tree 
	inner join content on content.Id = tree.ChildId
	order by tree.level

	-- SELECT audiences. (kunne også have været en query med både data om bindingen og principal. Har valgt at lave det som 2, for at reducere datamængden som returneres)
	select Principal.PrincipalID, Principal.DisplayName, Principal.GroupId -- vi behøver ikke hverken type eller loginName da det altid er group audiences
	from ContentAudience 
	inner join #TreeWithoutPersonalNodes tree on tree.ChildId = ContentAudience.ContentId and ContentAudience.ContextView = @view
	inner join principal on principal.PrincipalID = ContentAudience.PrincipalId
	group by Principal.PrincipalID, Principal.DisplayName, Principal.GroupId

	select ContentAudience.ContentId, ContentAudience.PrincipalId 
	from ContentAudience 
	inner join #TreeWithoutPersonalNodes tree on tree.ChildId = ContentAudience.ContentId and ContentAudience.ContextView = @view 

	-- SELECT Translations
	select 
		ContentTranslation.*
	from ContentTranslation 
		inner join #TreeWithoutPersonalNodes tree on tree.ChildId = ContentTranslation.ContentId
END