USE ManageSoft

-- Add resources for SPM role management
IF NOT EXISTS (SELECT * FROM Resource WHERE ResourceName = 'SpmAnalysis')
BEGIN
	INSERT INTO Resource (ResourceName, PartitionTypeID) VALUES ('SpmAnalysis', 1)
END
IF NOT EXISTS (SELECT * FROM Resource WHERE ResourceName = 'SpmAllocations')
BEGIN
	INSERT INTO Resource (ResourceName) VALUES ('SpmAllocations')
END
GO

-- Get MGS groups from registry
DECLARE @admingroup NVARCHAR(256)
DECLARE @reportgroup NVARCHAR(256)
SET @admingroup = 'MGS Administrators'
SET @reportgroup = 'MGS Report Users'
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\ManageSoft Corp\ManageSoft\Warehouse\CurrentVersion', 'ReportUsersGroup', @reportgroup OUTPUT
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\ManageSoft Corp\ManageSoft\Warehouse\CurrentVersion', 'AdministratorsGroup', @admingroup OUTPUT

-- Add MGS Administrators right to access SPM reports and allocation
-- Add MGS Report Users right to access SPM reports
IF NOT EXISTS (
	SELECT	*
	FROM	[Right] AS ri,
		[Group] AS g,
		Resource As re
	WHERE	ri.GroupID = g.GroupID
	  AND	ri.ResourceID = re.ResourceID
	  AND	re.ResourceName = 'SpmAnalysis'
	  AND	g.GroupCN = @admingroup
)
BEGIN
	INSERT	INTO [Right]
		SELECT	g.GroupID, re.ResourceID, 1, 0, 0
		FROM	[Group] AS g,
			Resource As re
		WHERE	re.ResourceName = 'SpmAnalysis'
		  AND	g.GroupCN = @admingroup
END
IF NOT EXISTS (
	SELECT	*
	FROM	[Right] AS ri,
		[Group] AS g,
		Resource As re
	WHERE	ri.GroupID = g.GroupID
	  AND	ri.ResourceID = re.ResourceID
	  AND	re.ResourceName = 'SpmAllocations'
	  AND	g.GroupCN = @admingroup
)
BEGIN
	INSERT	INTO [Right]
		SELECT	g.GroupID, re.ResourceID, 2, 0, 0
		FROM	[Group] AS g,
			Resource As re
		WHERE	re.ResourceName = 'SpmAllocations'
		  AND	g.GroupCN = @admingroup
END
IF NOT EXISTS (
	SELECT	*
	FROM	[Right] AS ri,
		[Group] AS g,
		Resource As re
	WHERE	ri.GroupID = g.GroupID
	  AND	ri.ResourceID = re.ResourceID
	  AND	re.ResourceName = 'SpmAnalysis'
	  AND	g.GroupCN = @reportgroup
)
BEGIN
	INSERT	INTO [Right]
		SELECT	g.GroupID, re.ResourceID, 1, 0, 0
		FROM	[Group] AS g,
			Resource As re
		WHERE	re.ResourceName = 'SpmAnalysis'
		  AND	g.GroupCN = @reportgroup
END
GO