CREATE PROCEDURE [dbo].[ContentGetPersonalView] 
	@view uniqueidentifier,
	@principalId int,
	@Language nvarchar(10)
AS
BEGIN
	--declare @view uniqueidentifier = '00000000-0000-0000-0000-000000003000';
	--declare @principalId int = 1007;

	WITH GroupAudiences as -- Find all Group audiences for the view context. Returns bit indicating if the user is in one of them.
	(
		SELECT        
			ContentAudience.ContentId, 
			--Count(PrincipalGrouping.Members_PrincipalID) AudienceCount, 
			max(case when PrincipalGrouping.Members_PrincipalID = @principalId then 1 else 0 end) as HasCurrentPrincipalInGroupAudience
		FROM            ContentAudience LEFT OUTER JOIN
							 PrincipalGrouping ON ContentAudience.PrincipalId = PrincipalGrouping.Groups_PrincipalID
		WHERE        (ContentAudience.ContextView = @view)
		GROUP BY ContentAudience.ContentId
	),
	PersonalAudiences as -- Find all the contentIds for the view context, where the current user is personal audience
	(
		SELECT        ContentAudience.ContentId
		FROM            ContentAudience 	
		WHERE        (ContentAudience.ContextView = @view and ContentAudience.PrincipalId = @principalId)
	),
	Tree as -- Get all the nodes for the Tree for the selected view, Recursive
	(
		SELECT        ContentParentChildRelations.*
		FROM            ContentParentChildRelations 
		WHERE        (ContentParentChildRelations.ParentId = @view)

		UNION ALL

		select child.* 
		from Tree parent 
		inner join ContentParentChildRelations child on Parent.ChildId = child.ParentId 
	),
	TreeWithAudienceColumns as -- join the audiences with the tree
	(
		select 
			Tree.*, 
			case when PersonalAudiences.ContentId is null then 0 else 1 end as IsPersonalAudience,
			GroupAudiences.HasCurrentPrincipalInGroupAudience
		from tree
		left outer join PersonalAudiences on tree.ChildId = PersonalAudiences.ContentId
		left outer join GroupAudiences on tree.ChildId = GroupAudiences.ContentId
	)
	-- execute the CTE into #AllowedChilds, which will now contain a list of all the allowed childs in the tree
    SELECT ChildId INTO #AllowedChilds FROM TreeWithAudienceColumns where IsPersonalAudience = 1 or ISNULL(HasCurrentPrincipalInGroupAudience, 1) = 1
	;

            --New CTE Tree joining the allowed childs with the recursive tree retrival.A new CTE is needed together with the Temperary table #AllowedChilds, cause the recursive part of a CTE, cant contain outer joins. This will allow us to use inner joins instead (;
	with tree as(
    SELECT        ContentParentChildRelations.*
        FROM            ContentParentChildRelations
        inner join #AllowedChilds on #AllowedChilds.ChildId = ContentParentChildRelations.ChildId
		WHERE        (ContentParentChildRelations.ParentId = @view)

        UNION ALL

        select child.*
        from Tree parent
        inner
        join ContentParentChildRelations child on Parent.ChildId = child.ParentId
        inner
        join #AllowedChilds on #AllowedChilds.ChildId = child.ChildId
	)
	select 
		tree.ParentId,
		tree.SortOrder,
		content.*, 
		ContentTranslation.Value as TranslatedValue,
		ContentTranslation.Title as TranslatedTitle
	from 
		tree 
			inner join content on content.Id = tree.ChildId 
			left outer join ContentTranslation on content.Id = ContentTranslation.ContentId and ContentTranslation.Language = @Language

   --drop table #AllowedChilds
END