  
 --update previous queue to OLD 
 update  ConfigurationEntityTree set ServiceName=ServiceName+'_old',ServiceId=ServiceId+'_old' where servicename in ('PushConfig') and serviceid='ADLPushConfigAlertHandlerService' and guidkey is null


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

declare @settingTypeIdConnection int
declare @settingTypeIdExchange int
declare @settingTypeIdExchangeDLX int
declare @settingTypeIdQueue int
declare @settingTypeIdQueueDlx int

select @settingTypeIdConnection = Id from ConfigurationGroups where guidkey='58842499-6327-4625-8719-4DB14F8788BF'
select @settingTypeIdExchange = Id from ConfigurationGroups where guidkey='B1D37F32-0366-4539-A325-BECFDF0739AE'
select @settingTypeIdExchangeDLX = Id from ConfigurationGroups where guidkey='4882BAF5-2EE8-4AEB-BB5B-45BBED52165D'
select @settingTypeIdQueue = Id from ConfigurationGroups where guidkey='451A5CD5-38DD-40AC-8BCC-FECC06B77452'
select @settingTypeIdQueueDlx = Id from ConfigurationGroups where guidkey='2102C4BA-2C90-4415-8B2C-57C584695E4B'

---------------Fill ConfigurationEntityTree-------------------------------------------------------------

--tree
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 );

--Pushconfig
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 1, @settingTypeIdConnection,  N'MachineId', N'PushConfig', N'ADLPushConfigAlertHandlerService', N'ServiceBroker', NULL,'80A5CC61-DA66-4169-9FBB-7E1B714F1C52')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 2, @settingTypeIdExchange, N'MachineId', N'PushConfig', N'ADLPushConfigAlertHandlerService', N'ServiceBroker', NULL,'769B04E6-6A28-4FB7-BD92-F5614BFCC013')

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

--clean to use previous as Parent
delete from @ConfigurationEntityTree

declare @EntityIdQExchangePushconfig int

select @EntityIdQExchangePushconfig = Id from  dbo.ConfigurationEntityTree where guidkey='769B04E6-6A28-4FB7-BD92-F5614BFCC013'

--Pushconfig
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 3, @settingTypeIdQueue, N'MachineId', N'PushConfig', N'ADLPushConfigAlertHandlerService', N'ServiceBroker', @EntityIdQExchangePushconfig,'2B64AD76-8B6F-41AD-B751-0083A63A7C76')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 2, @settingTypeIdExchangeDLX, N'MachineId', N'PushConfig', N'ADLPushConfigAlertHandlerService', N'ServiceBroker', @EntityIdQExchangePushconfig,'BFD5CF4A-619E-42A3-87E1-77E67F1F568E')

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

--clean to use previous as Parent
delete from @ConfigurationEntityTree

declare @EntityIdQExchangeDLXPushconfig int
select @EntityIdQExchangeDLXPushconfig = Id from  dbo.ConfigurationEntityTree where guidkey='BFD5CF4A-619E-42A3-87E1-77E67F1F568E'

--Pushconfig
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 3, @settingTypeIdQueueDlx, N'MachineId', N'PushConfig', N'ADLPushConfigAlertHandlerService', N'ServiceBroker', @EntityIdQExchangeDLXPushconfig,'26C9B44F-3DA6-42AF-B31A-DDA90FBC6D43')

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