/**
Create Groups
**/

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

--RemoveResidentsForVendorWithRequestJob
	declare @RemoveResidentsForVendorWithRequestJob_GUID uniqueidentifier = '98A16F70-5DE1-42B2-B9B5-217729A3A700', @RemoveResidentsForVendorWithRequestJob_tj_Id int
	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (4, @RemoveResidentsForVendorWithRequestJob_GUID) --TimerJob
	declare @RemoveResidentsForVendorWithRequestJob_p_GUID uniqueidentifier = 'A53D295D-DE69-4478-83A7-BB90A1E74606',  @RemoveResidentsForVendorWithRequestJob_tjp_Id int
	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (5, @RemoveResidentsForVendorWithRequestJob_p_GUID) --TimerJobParameters

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

	select @RemoveResidentsForVendorWithRequestJob_tj_Id=id from ConfigurationGroups where guidkey=@RemoveResidentsForVendorWithRequestJob_GUID
	select @RemoveResidentsForVendorWithRequestJob_tjp_Id=id from ConfigurationGroups where guidkey=@RemoveResidentsForVendorWithRequestJob_p_GUID

/**
Create group parameter 
**/

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

--RemoveResidentsForVendorWithRequestJob
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @RemoveResidentsForVendorWithRequestJob_tj_Id, 1, N'JobStoreProc_RemoveResidentsForVendorWithRequest'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @RemoveResidentsForVendorWithRequestJob_tj_Id, 16, N'StoreProcTimerJob'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @RemoveResidentsForVendorWithRequestJob_tj_Id, 17, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @RemoveResidentsForVendorWithRequestJob_tjp_Id, 1, N'ParametersJobStoreProc_RemoveResidentsForVendorWithRequest'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @RemoveResidentsForVendorWithRequestJob_tjp_Id, 18, N'3600'	--once a day
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @RemoveResidentsForVendorWithRequestJob_tjp_Id, 19, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @RemoveResidentsForVendorWithRequestJob_tjp_Id, 80, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @RemoveResidentsForVendorWithRequestJob_tjp_Id, 81, N'job_RemoveResidentsForVendorWithRequest'
	
	
	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.*;


/**
Create Tree for job
**/
	--ConfigurationEntityTree - Job
	DECLARE @ConfigurationEntityTree1 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 );

--RemoveResidentsForVendorWithRequestJob
	INSERT into @ConfigurationEntityTree1 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 4, @RemoveResidentsForVendorWithRequestJob_tj_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_RemoveResidentsForVendorWithRequest', N'ServiceBroker', NULL, @RemoveResidentsForVendorWithRequestJob_GUID	--TimerJob

	MERGE INTO dbo.ConfigurationEntityTree AS TARGET  
			USING @ConfigurationEntityTree1 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.*;

/**
Create Tree for job parameters
**/
	--ConfigurationEntityTree- Job parameters
	DECLARE @ConfigurationEntityTree2 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 );

--RemoveResidentsForVendorWithRequestJob
	INSERT into @ConfigurationEntityTree2 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 5, @RemoveResidentsForVendorWithRequestJob_tjp_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_RemoveResidentsForVendorWithRequest', N'ServiceBroker', t.id, @RemoveResidentsForVendorWithRequestJob_p_GUID from ConfigurationEntityTree t where t.guidkey=@RemoveResidentsForVendorWithRequestJob_GUID --TimerJobParameters			

	MERGE INTO dbo.ConfigurationEntityTree AS TARGET  
			USING @ConfigurationEntityTree2 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.*;



