--04: ConfigurationGroups
--	Create Instance of the group 
	DECLARE @ConfigurationGroups TABLE (EntityId int NOT NULL,GuidKey uniqueidentifier not null );

	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (9, '3A967B68-782D-4D43-A4A1-3C969D090674') --User policy default

	MERGE INTO dbo.ConfigurationGroups AS TARGET  
		USING @ConfigurationGroups AS SOURCE  
		ON upper(TARGET.GuidKey) = upper(SOURCE.GuidKey)
		WHEN MATCHED THEN    
				UPDATE SET entityid = SOURCE.entityid
		WHEN NOT MATCHED BY TARGET THEN   
				INSERT (EntityId,GuidKey) VALUES (Source.EntityId,upper(source.GuidKey)) 
	OUTPUT $action, SOURCE.*;

	declare @configurationGroupId int
	select @configurationGroupId = id from ConfigurationGroups where guidkey='3A967B68-782D-4D43-A4A1-3C969D090674'

--05: ConfigurationGroupParameters
--	Fill the group instance
	DECLARE @ConfigurationGroupParameters TABLE (GroupId int NOT NULL,ParameterId int not null, Value nvarchar(max));

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 1, N'UserPolicyDefault'	--name
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 41, N'2'	--MasterCaregiver
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 42, N'8'	--StandardCaregiver


	MERGE INTO dbo.ConfigurationGroupParameters AS TARGET  
			USING @ConfigurationGroupParameters AS SOURCE  
			ON TARGET.GroupId  = SOURCE.GroupId
			AND TARGET.ParameterId =  SOURCE.ParameterId
			WHEN MATCHED THEN    
					UPDATE SET Value=source.Value
			WHEN NOT MATCHED BY TARGET THEN   
					INSERT ( GroupId,ParameterId,Value) VALUES (Source.GroupId, SOURCE.ParameterId,source.Value)  
	OUTPUT $action, SOURCE.*;

--06: ConfigurationEntityTree
--	Set the identifier in the tree for the instance
	DECLARE @ConfigurationEntityTree TABLE (EntityTypeId int not null,GroupId int NOT NULL, MachineName nvarchar(max), ServiceName nvarchar(max), ServiceId nvarchar(max), 
											ModuleIdentifier nvarchar(max),ParentEntityId int null,GuidKey uniqueidentifier not null );


	INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 9, @configurationGroupId, N'MachineId', N'Policies', N'UserPolicyDefault',N'UserPolicy', NULL, 'EE79AD57-9B72-42CA-B733-12EE730BE783'

	MERGE INTO dbo.ConfigurationEntityTree AS TARGET  
			USING @ConfigurationEntityTree AS SOURCE  
			ON upper(TARGET.GuidKey) = upper(SOURCE.GuidKey)
             
			WHEN MATCHED THEN    
					UPDATE SET  MachineName=source.MachineName, ServiceName=source.ServiceName, ServiceId=source.ServiceId, 
					ModuleIdentifier=source.ModuleIdentifier, ParentEntityId=source.ParentEntityId
			WHEN NOT MATCHED BY TARGET THEN   
					INSERT ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId,GuidKey) 
					VALUES ( source.EntityTypeId, source.GroupId, source.MachineName, source.ServiceName, source.ServiceId, source.ModuleIdentifier, source.ParentEntityId, upper(source.GuidKey))
	OUTPUT $action, SOURCE.*;
