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


--new Queue ADLParserAlertHandlerService

------------New GROUPS for ADLParserAlertHandlerService queue-----------------------------------------------------
DECLARE @ConfigurationGroups TABLE (EntityId int NOT NULL,GuidKey uniqueidentifier not null );

INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (1, '58842499-6327-4625-8719-4DB14F8788BF') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (2, 'B1D37F32-0366-4539-A325-BECFDF0739AE') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (2, '4882BAF5-2EE8-4AEB-BB5B-45BBED52165D') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (3, '451A5CD5-38DD-40AC-8BCC-FECC06B77452') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (3, '2102C4BA-2C90-4415-8B2C-57C584695E4B') 

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 @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'

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 1, N'ADLParserAlertHandlerConnection')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 2, N'x.x.x.x')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 3, N'Mozart')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 4, N'123123')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 5, N'test')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 6, N'-1')

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdExchange, 1, N'Ex02ParserAlertHandler')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdExchange, 7, N'Topic')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdExchange, 8, N'false')

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdExchangeDLX, 1, N'zExDlx02ParserAlertHandler')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdExchangeDLX, 7, N'Fanout')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdExchangeDLX, 8, N'false')

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 1, N'Queue02ParserAlertHandler')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 9, N'DefaultBlWrapperManager')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 10, N'true')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 11, N'false')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 12, N'false')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 13, N'')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 14, N'ADLAlertHandler')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 15, N'zExDlx02ParserAlertHandler')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 21, N'3')--instances
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 93, N'3')--priority

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 1, N'zQueueDlx02ParserAlertHandler')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 9, N'')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 10, N'true')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 11, N'false')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 12, N'false')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 13, N'')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 14, N'')


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

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

--TCPListener
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 1, @settingTypeIdConnection, N'MachineId', N'AlertHandler', N'ADLParserAlertHandlerService', N'ServiceBroker', NULL,'9AC8B860-B2AA-4C14-8618-198FD0E28FFB')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 2, @settingTypeIdExchange,  N'MachineId', N'AlertHandler', N'ADLParserAlertHandlerService', N'ServiceBroker', NULL,'56F45BAE-BB8C-4165-AE02-8D25706FA902')
--Parser																															 
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 1, @settingTypeIdConnection,  N'MachineId', N'Parser', N'ADLParserAlertHandlerService', N'ServiceBroker', NULL,'F04EAD34-D15B-487C-B1D2-E10637DCA295')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 2, @settingTypeIdExchange, N'MachineId', N'Parser', N'ADLParserAlertHandlerService', N'ServiceBroker', NULL,'FD16C220-DC4A-4C6F-A0FA-3D99E7D785F1')

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 @EntityIdQExchangeTCPListener int
declare @EntityIdQExchangeParser int

select @EntityIdQExchangeTCPListener = Id from  dbo.ConfigurationEntityTree where guidkey='56F45BAE-BB8C-4165-AE02-8D25706FA902'
select @EntityIdQExchangeParser = Id from  dbo.ConfigurationEntityTree where guidkey='FD16C220-DC4A-4C6F-A0FA-3D99E7D785F1'

--TCPListener
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 3, @settingTypeIdQueue, N'MachineId', N'AlertHandler', N'ADLParserAlertHandlerService', N'ServiceBroker', @EntityIdQExchangeTCPListener,'4E5D44AF-0431-45BB-81B7-FD92C277D2A2')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 2, @settingTypeIdExchangeDLX, N'MachineId', N'AlertHandler', N'ADLParserAlertHandlerService', N'ServiceBroker', @EntityIdQExchangeTCPListener,'F2711855-6E00-47DE-8B67-69971002BA89')
--Parser
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 3, @settingTypeIdQueue, N'MachineId', N'Parser', N'ADLParserAlertHandlerService', N'ServiceBroker', @EntityIdQExchangeParser,'13BA1671-C0B5-49BC-898A-F3D9C14353B0')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 2, @settingTypeIdExchangeDLX, N'MachineId', N'Parser', N'ADLParserAlertHandlerService', N'ServiceBroker', @EntityIdQExchangeParser,'4A707505-F922-4362-AA8E-163ED8A0CD73')

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 @EntityIdQExchangeDLXTCPListener int
select @EntityIdQExchangeDLXTCPListener = Id from  dbo.ConfigurationEntityTree where guidkey='F2711855-6E00-47DE-8B67-69971002BA89'

declare @EntityIdQExchangeDLXParser int
select @EntityIdQExchangeDLXParser = Id from  dbo.ConfigurationEntityTree where guidkey='4A707505-F922-4362-AA8E-163ED8A0CD73'

--TCPListener
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) 
VALUES ( 3, @settingTypeIdQueueDlx, N'MachineId', N'AlertHandler', N'ADLParserAlertHandlerService', N'ServiceBroker', @EntityIdQExchangeDLXTCPListener,'457EA7AB-D054-42A9-A004-237217D84A4C')
--Parser
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey)
VALUES ( 3, @settingTypeIdQueueDlx, N'MachineId', N'Parser', N'ADLParserAlertHandlerService', N'ServiceBroker', @EntityIdQExchangeDLXParser,'2D70B62E-D3C2-42F7-8F3C-DCF8918B4BA3')

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