--USE commserv
-- NOTE the contents of the file taken from App_SCGClientGroupDependency.script file.
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

DECLARE @ErrorTag NVARCHAR(20) 
SET			@ErrorTag = '###ERROR###'

DECLARE @WarningTag NVARCHAR(20) 
SET			@WarningTag = '###WARNING###'

DECLARE @AdviceTag NVARCHAR(20) 
SET			@AdviceTag = '###ADVICE###'

DECLARE @HeaderTag NVARCHAR(20) 
SET			@HeaderTag = '###HEADER###'

DECLARE @DataTag NVARCHAR(20) 
SET			@DataTag = '###DATA###'

DECLARE @DescTag NVARCHAR(20) 
SET	@DescTag = '###DESCRIPTION###'

PRINT @DescTag + ' Check for Automatic Client Group Circular Dependencies'

BEGIN

	IF OBJECT_ID('tempdb.dbo.#DepSCGRuleCheck') IS NOT NULL
		DROP TABLE #DepSCGRuleCheck
	CREATE TABLE #DepSCGRuleCheck (
		scgId		INT,
		cgId		INT,
		depCGId		INT,
		PRIMARY KEY (cgId, depCGID, scgId)
	)

	IF OBJECT_ID('tempdb.dbo.#SCGClientGroupDependency') IS NOT NULL
		DROP TABLE #SCGClientGroupDependency
	CREATE TABLE #SCGClientGroupDependency (
		associatedGrpId		INT,
		ownerGrpId			INT
	)

	CREATE CLUSTERED INDEX SCGClientGroupDependency_idx1 on #SCGClientGroupDependency(associatedGrpId);;
	INSERT INTO #DepSCGRuleCheck (scgId, cgId, depCGID)
		SELECT DISTINCT
			r.scgId,
			r.cgId,
			dg.value('@val', 'INT') depCGId
		FROM (
				SELECT
					gr.id scgId,
					gr.scgId cgId,
					CAST(r.value('@value', 'VARCHAR(MAX)') AS XML) depGroups
				FROM 
					APP_SCGRule gr WITH(NOLOCK)
					CROSS APPLY gr.ruleXml.nodes('//rules/rule') d(r)	-- find node at any level of tree
				WHERE
					r.value('@propID', 'int') IS NOT NULL
					AND r.value('@propID', 'int') = 71	-- SCG_PKID_CLIENT_GROUPLIST Only want client group listing rule property since they were missed configured for group dependency when created
			) r
			CROSS APPLY r.depGroups.nodes('//id') d(dg)

	-- V10 Upgrades will not have any SCG Property 71 but have old link list of circular dependency list
	--DECLARE @rcnt	INT = @@ROWCOUNT
	--IF (@rcnt = 0)
	--BEGIN
	--	-- nothing to do
	--	GOTO END_OF_GRPDEPBLK
	--END

	INSERT INTO #SCGClientGroupDependency (associatedGrpId, ownerGrpId)
		SELECT
			g.depCGId,
			g.cgId
		FROM #DepSCGRuleCheck g
			LEFT OUTER JOIN App_SCGClientGroupDependency gd ON
				g.cgId = gd.ownerGrpId
				AND g.depCGId = gd.associatedGrpId
		WHERE
			gd.ownerGrpId IS NULL		-- not found insert it!

		INSERT INTO #SCGClientGroupDependency (associatedGrpId, ownerGrpId)
			SELECT
				d.associatedGrpId,
				d.ownerGrpId
			FROM App_SCGClientGroupDependency d

END_OF_GRPDEPBLK:

	-- Now check for any circular loops in the grouping chains as a precaution.  If any found throw exception!
	DECLARE @loopxml XML = NULL
	;WITH SCGFindLoop AS
	(
		SELECT 
			p.ownerGrpId parentId,
			p.associatedGrpId childId, 
			(
				SELECT
					p.ownerGrpId '@parentId',
					p.associatedGrpId 'Node/@id'
				FOR XML PATH('Path'), TYPE
			) path,
			1 distance,
			0 loop
		FROM #SCGClientGroupDependency p WITH(NOLOCK)

		UNION ALL

		SELECT 
			p.ownerGrpId parentId, 
			c.childId,
			CASE CAST(c.path.exist('//Node[@id = sql:column("p.associatedGrpId")]') AS INT)
				WHEN 1 THEN
					(
						SELECT
							c.path.value('(/Path/@parentId)[1]', 'INT') '@parentId',
							c.path.query('/Path/Node'),
							p.associatedGrpId 'NodeLoop/@id'
						FOR XML PATH('Path'), TYPE
					)
				ELSE
					(
						SELECT
							c.path.value('(/Path/@parentId)[1]', 'INT') '@parentId',
							c.path.query('/Path/Node'),
							p.associatedGrpId 'Node/@id'
						FOR XML PATH('Path'), TYPE
					)
			END path,
			c.distance + 1,
			CAST(c.path.exist('//Node[@id = sql:column("p.associatedGrpId")]') AS INT) loop
		FROM #SCGClientGroupDependency p 
			INNER JOIN SCGFindLoop c ON 
				c.parentId = p.associatedGrpId 
				AND p.ownerGrpId <> p.associatedGrpId 
		WHERE
			c.loop = 0
	 )
		SELECT @loopxml = (
			SELECT
				l.parentId '@parentId',
				l.childId '@childId',
				l.loop '@loop',
				l.distance '@distance',
				l.path.query('.')
			FROM SCGFindLoop l
			WHERE
				l.loop <> 0
			FOR XML PATH('SCGLoopDetected'), ROOT('GroupPath'), TYPE
		)

END

-- Detect if any SCG has a dependency on itself
DECLARE @scgDepOnSelf TINYINT = 0

SELECT TOP 1
	@scgDepOnSelf = 1
FROM App_SCGClientGroupDependency d WITH(NOLOCK)
WHERE
	d.associatedGrpId = d.ownerGrpId

DECLARE @output nvarchar(max)
SET @output = ''
SET @output = @ErrorTag + ' Circular Dependency detected in Automatic Client Group Associations. ' + char(13) 
			+ @AdviceTag + ' Please contact Tech Support to help resolve before upgrading. ' + char(13)

IF @loopxml IS NULL AND @scgDepOnSelf = 0
BEGIN 
	PRINT 'There are no Automatic Client Group Circular Dependencies.'
END 
ELSE
BEGIN
	PRINT @output
END

CX_EXIT: 

IF  @loopxml IS NOT NULL OR @scgDepOnSelf = 1
	SELECT 2
ELSE
	SELECT 0

	
RETURN

