--Create New Parameters
	DECLARE @ConfigurationParameterTypes TABLE (Id int NOT NULL,Title nvarchar(max) NOT NULL);

	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (80, N'IsEnabled')
	INSERT into @ConfigurationParameterTypes (Id, Title) VALUES (81, N'StoreProcName')

	SET IDENTITY_INSERT dbo.ConfigurationParameterTypes ON

	MERGE INTO dbo.ConfigurationParameterTypes AS TARGET  
		USING @ConfigurationParameterTypes AS SOURCE  
		ON TARGET.Id = SOURCE.Id
		WHEN MATCHED THEN    
			UPDATE SET Title = SOURCE.Title
		WHEN NOT MATCHED BY TARGET THEN   
			INSERT (Id, Title) VALUES (SOURCE.Id, SOURCE.Title)  
	OUTPUT $action, SOURCE.*;

	SET IDENTITY_INSERT dbo.ConfigurationParameterTypes OFF

	
/**
Create Groups
**/

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

--ActivityGateTableJob
	declare @ActivityGateTableJob_GUID uniqueidentifier = '9697E97A-18A0-4EF6-9EAE-E619FB77EFE3', @ActivityGateTableJob_tj_Id int
	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (4, @ActivityGateTableJob_GUID) --TimerJob
	declare @ActivityGateTableJob_p_GUID uniqueidentifier = '6AE9F7B6-4F68-41E9-BE57-D3180FC2C708',  @ActivityGateTableJob_tjp_Id int
	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (5, @ActivityGateTableJob_p_GUID) --TimerJobParameters
--PoSAGateTableJob																					 
	declare @PoSAGateTableJob_GUID uniqueidentifier = 'F630EFDA-A2F6-409E-A33C-AB9F5C23BA2E', @PoSAGateTableJob_tj_Id int
	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (4, @PoSAGateTableJob_GUID) --TimerJob
	declare @PoSAGateTableJob_p_GUID uniqueidentifier = 'DC104AC7-CDBE-4A19-AB0E-294D76CEF811', @PoSAGateTableJob_tjp_Id int
	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (5, @PoSAGateTableJob_p_GUID) --TimerJobParameters
 --ProcFileTblCleaning																				 
	 declare @ProcFileTblCleaning_GUID uniqueidentifier = '032C87FB-8776-4383-8E89-F126F8A13E9E', @ProcFileTblCleaning_tj_Id int
	 INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (4, @ProcFileTblCleaning_GUID) --TimerJob
	 declare @ProcFileTblCleaning_p_GUID uniqueidentifier = '6A9F7469-7D96-48EE-815A-178093701CA5', @ProcFileTblCleaning_tjp_Id int
	 INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (5, @ProcFileTblCleaning_p_GUID) --TimerJobParameters
--UncompletedPoSaHandleJob
	declare @UncompletedPoSaHandleJob_GUID uniqueidentifier = '1231B7A0-D646-47A2-97B2-0F26F4860181', @UncompletedPoSaHandleJob_tj_Id int
	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (4, @UncompletedPoSaHandleJob_GUID) --TimerJob
	declare @UncompletedPoSaHandleJob_p_GUID uniqueidentifier = '5A88EDA5-C209-4E7A-B12C-AD3312486D81',  @UncompletedPoSaHandleJob_tjp_Id int
	INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (5, @UncompletedPoSaHandleJob_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 @ActivityGateTableJob_tj_Id=id from ConfigurationGroups where guidkey=@ActivityGateTableJob_GUID
	select @PoSAGateTableJob_tj_Id=id from ConfigurationGroups where guidkey=@PoSAGateTableJob_GUID
	select @ProcFileTblCleaning_tj_Id=id from ConfigurationGroups where guidkey=@ProcFileTblCleaning_GUID
	select @UncompletedPoSaHandleJob_tj_Id=id from ConfigurationGroups where guidkey=@UncompletedPoSaHandleJob_GUID 

	select @ActivityGateTableJob_tjp_Id=id from ConfigurationGroups where guidkey=@ActivityGateTableJob_p_GUID
	select @PoSAGateTableJob_tjp_Id=id from ConfigurationGroups where guidkey=@PoSAGateTableJob_p_GUID
	select @ProcFileTblCleaning_tjp_Id=id from ConfigurationGroups where guidkey=@ProcFileTblCleaning_p_GUID
	select @UncompletedPoSaHandleJob_tjp_Id=id from ConfigurationGroups where guidkey=@UncompletedPoSaHandleJob_p_GUID

	-- select  @ActivityGateTableJob_tj_Id,@PoSAGateTableJob_tj_Id,@ProcFileTblCleaning_tj_Id,@UncompletedPoSaHandleJob_tj_Id
	-- select  @ActivityGateTableJob_tjp_Id,@PoSAGateTableJob_tjp_Id,@ProcFileTblCleaning_tjp_Id,@UncompletedPoSaHandleJob_tjp_Id

/**
Create group parameter
**/

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

--ActivityGateTableJob
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ActivityGateTableJob_tj_Id, 1, N'JobStoreProc_ActivityGateTable'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ActivityGateTableJob_tj_Id, 16, N'StoreProcTimerJob'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ActivityGateTableJob_tj_Id, 17, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ActivityGateTableJob_tjp_Id, 1, N'ParametersJobStoreProc_ActivityGateTable'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ActivityGateTableJob_tjp_Id, 18, N'300'	--once a day
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ActivityGateTableJob_tjp_Id, 19, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ActivityGateTableJob_tjp_Id, 80, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ActivityGateTableJob_tjp_Id, 81, N'job_ActivityGateTable'
--PoSAGateTableJob																					 
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @PoSAGateTableJob_tj_Id, 1, N'JobStoreProc_PoSAGateTable'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @PoSAGateTableJob_tj_Id, 16, N'StoreProcTimerJob'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @PoSAGateTableJob_tj_Id, 17, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @PoSAGateTableJob_tjp_Id, 1, N'ParametersJobStoreProc_PoSAGateTable'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @PoSAGateTableJob_tjp_Id, 18, N'300'	--once a day
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @PoSAGateTableJob_tjp_Id, 19, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @PoSAGateTableJob_tjp_Id, 80, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @PoSAGateTableJob_tjp_Id, 81, N'job_PoSAGateTable'
 --ProcFileTblCleaning																				 
	 INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ProcFileTblCleaning_tj_Id, 1, N'JobStoreProc_ProcFileTblCleaning'
	 INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ProcFileTblCleaning_tj_Id, 16, N'StoreProcTimerJob'
	 INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ProcFileTblCleaning_tj_Id, 17, N'1'
	 INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ProcFileTblCleaning_tjp_Id, 1, N'ParametersJobStoreProc_ProcFileTblCleaning'
	 INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ProcFileTblCleaning_tjp_Id, 18, N'3600'	--once a day
	 INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ProcFileTblCleaning_tjp_Id, 19, N'1'
	 INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ProcFileTblCleaning_tjp_Id, 80, N'1'
	 INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @ProcFileTblCleaning_tjp_Id, 81, N'job_ProcFileTblCleaning'
--UncompletedPoSaHandleJob
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @UncompletedPoSaHandleJob_tj_Id, 1, N'JobStoreProc_UncompletedPoSaHandle'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @UncompletedPoSaHandleJob_tj_Id, 16, N'StoreProcTimerJob'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @UncompletedPoSaHandleJob_tj_Id, 17, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @UncompletedPoSaHandleJob_tjp_Id, 1, N'ParametersJobStoreProc_UncompletedPoSaHandle'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @UncompletedPoSaHandleJob_tjp_Id, 18, N'86400'	--once a day
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @UncompletedPoSaHandleJob_tjp_Id, 19, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @UncompletedPoSaHandleJob_tjp_Id, 80, N'1'
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select @UncompletedPoSaHandleJob_tjp_Id, 81, N'job_UncompletedPoSaHandle'
	
	
	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 );

--ActivityGateTableJob
	INSERT into @ConfigurationEntityTree1 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 4, @ActivityGateTableJob_tj_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_ActivityGateTable', N'ServiceBroker', NULL, @ActivityGateTableJob_GUID	--TimerJob
--PoSAGateTableJob																					 
	INSERT into @ConfigurationEntityTree1 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 4, @PoSAGateTableJob_tj_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_PoSAGateTable', N'ServiceBroker', NULL, @PoSAGateTableJob_GUID	--TimerJob
--ProcFileTblCleaning
	INSERT into @ConfigurationEntityTree1 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 4, @ProcFileTblCleaning_tj_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_ProcFileTblCleaning', N'ServiceBroker', NULL, @ProcFileTblCleaning_GUID	--TimerJob
--UncompletedPoSaHandleJob
	INSERT into @ConfigurationEntityTree1 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 4, @UncompletedPoSaHandleJob_tj_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_UncompletedPoSaHandle', N'ServiceBroker', NULL, @UncompletedPoSaHandleJob_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 );

--ActivityGateTableJob
	INSERT into @ConfigurationEntityTree2 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 5, @ActivityGateTableJob_tjp_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_ActivityGateTable', N'ServiceBroker', t.id, @ActivityGateTableJob_p_GUID from ConfigurationEntityTree t where t.guidkey=@ActivityGateTableJob_GUID --TimerJobParameters			
--PoSAGateTableJob																					 
	INSERT into @ConfigurationEntityTree2 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 5, @PoSAGateTableJob_tjp_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_PoSAGateTable', N'ServiceBroker', t.id, @PoSAGateTableJob_p_GUID from ConfigurationEntityTree t where t.guidkey=@PoSAGateTableJob_GUID --TimerJobParameters			
--ProcFileTblCleaning																				 
	INSERT into @ConfigurationEntityTree2 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 5, @ProcFileTblCleaning_tjp_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_ProcFileTblCleaning', N'ServiceBroker', t.id, @ProcFileTblCleaning_p_GUID from ConfigurationEntityTree t where t.guidkey=@ProcFileTblCleaning_GUID --TimerJobParameters			
--UncompletedPoSaHandleJob
	INSERT into @ConfigurationEntityTree2 ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 5, @UncompletedPoSaHandleJob_tjp_Id, N'MachineId', N'StoreProcJobs', N'JobStoreProc_UncompletedPoSaHandle', N'ServiceBroker', t.id, @UncompletedPoSaHandleJob_p_GUID from ConfigurationEntityTree t where t.guidkey=@UncompletedPoSaHandleJob_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.*;



