		
	--1
	DECLARE @ConfigurationGroups TABLE (EntityId int NOT NULL,GuidKey uniqueidentifier not null );

	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (6, '8C745605-62B9-43E9-B986-9745442677EA') --Connection

	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='8C745605-62B9-43E9-B986-9745442677EA'

	declare @applicationId nvarchar(max)
	set @applicationId='com.esc.umbrella'

	--2
	DECLARE @ConfigurationGroupParameters TABLE (GroupId int NOT NULL,ParameterId int not null, Value nvarchar(max));

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 1, N'ApnsPushNotification'	--name
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 5, N''	--host
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 17, N'1'	--isActive
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 26, N''	--SoundFile
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 27, @applicationId	--ApplicationId
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 28, N''	--ServerKey
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 29, N''	--AlertButtonText
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 30, N''	--AlertBadge
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 31, N'C:\Program Files\Essence Connect Server\Resources\Certificates\umbrella_prod.p12'	--Path
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @configurationGroupId, 32, N''	--CertificatePassword

	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.*;

	--3
	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 6, @configurationGroupId, N'MachineId', N'apns', @applicationId,N'PushNotificationConfiguration', NULL, 'EDA8A59B-0DD6-4DD7-BF27-BB6E9AF18023'

	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.*;

