--new Queue PanelCommandManagerJpbs

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

INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (1, '2A8A9548-1983-4AF1-BB21-01777694C62F') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (2, '02A5D8A3-9958-4409-9D5F-91B6E5C05A00') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (2, '5F7C45DA-FE12-4680-9DB3-30A6673C9AE6') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (3, '22B3D431-6894-480F-9B73-F0C5C11C0B52') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (3, '7C310595-CE98-4E8D-A232-164A49262313') 

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='2A8A9548-1983-4AF1-BB21-01777694C62F'
select @settingTypeIdExchange = Id from ConfigurationGroups where guidkey='02A5D8A3-9958-4409-9D5F-91B6E5C05A00'
select @settingTypeIdExchangeDLX = Id from ConfigurationGroups where guidkey='5F7C45DA-FE12-4680-9DB3-30A6673C9AE6'
select @settingTypeIdQueue = Id from ConfigurationGroups where guidkey='22B3D431-6894-480F-9B73-F0C5C11C0B52'
select @settingTypeIdQueueDlx = Id from ConfigurationGroups where guidkey='7C310595-CE98-4E8D-A232-164A49262313'

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 1, N'PanelCommandManagerConnection')
	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'ExPanelCommandManager')
	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'ExDlxPanelCommandManager')
	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'QueuePanelCommandManager')
	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'PanelCommandManager')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 15, N'ExDlxPanelCommandManager')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 21, N'5')--instances

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 1, N'QueueDlxPanelCommandManager')
	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 );

--A
----Subscriber
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 1, @settingTypeIdConnection, N'MachineId', N'PanelCommandManagerSubscriber', N'PanelCommandManagerSubscriberService', N'ServiceBroker', NULL,'63FDE785-C324-41D7-B63C-F493AE7AC38C')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchange,  N'MachineId', N'PanelCommandManagerSubscriber', N'PanelCommandManagerSubscriberService', N'ServiceBroker', NULL,'97D43FD2-35AF-4EFF-814F-6927A14C5695')
----Publisher
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 1, @settingTypeIdConnection, N'MachineId', N'PanelCommandManagerPublisher', N'PanelCommandManagerPublisherService', N'ServiceBroker', NULL,'92952448-EEA4-43BE-85AB-A28562FB76BE')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchange,  N'MachineId', N'PanelCommandManagerPublisher', N'PanelCommandManagerPublisherService', N'ServiceBroker', NULL,'F1B9AEA0-9151-4CD0-B494-78E19B75DFC6')

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

--B
----Subscriber
declare @EntityIdQExchangeSubscriber int
select @EntityIdQExchangeSubscriber = Id from  dbo.ConfigurationEntityTree where guidkey='97D43FD2-35AF-4EFF-814F-6927A14C5695'

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueue, N'MachineId', N'PanelCommandManagerSubscriber', N'PanelCommandManagerSubscriberService', N'ServiceBroker', @EntityIdQExchangeSubscriber,'AC56E1CC-EABE-4DB5-BC9E-BD5292C80360')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchangeDLX, N'MachineId', N'PanelCommandManagerSubscriber', N'PanelCommandManagerSubscriberService', N'ServiceBroker', @EntityIdQExchangeSubscriber,'BD5A2633-523A-4EA4-B104-7E0C42D54626')

----Publisher
declare @EntityIdQExchangePublisher int
select @EntityIdQExchangePublisher = Id from  dbo.ConfigurationEntityTree where guidkey='F1B9AEA0-9151-4CD0-B494-78E19B75DFC6'

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueue, N'MachineId', N'PanelCommandManagerPublisher', N'PanelCommandManagerPublisherService', N'ServiceBroker', @EntityIdQExchangePublisher,'AC9220E8-A264-496A-8B5E-BCB2F833AD61')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchangeDLX, N'MachineId', N'PanelCommandManagerPublisher', N'PanelCommandManagerPublisherService', N'ServiceBroker', @EntityIdQExchangePublisher,'18881EB6-F2B9-44D0-A8DA-C551ABFAF57E')

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

--C
----Subscriber
declare @EntityIdQExchangeSubscriberDLX int
select @EntityIdQExchangeSubscriberDLX = Id from  dbo.ConfigurationEntityTree where guidkey='BD5A2633-523A-4EA4-B104-7E0C42D54626'

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueueDlx, N'MachineId', N'PanelCommandManagerSubscriber', N'PanelCommandManagerSubscriberService', N'ServiceBroker', @EntityIdQExchangeSubscriberDLX,'E317D1E5-E4AC-44EF-B303-059164309E4E')

----Subscriber
declare @EntityIdQExchangePublisherDLX int
select @EntityIdQExchangePublisherDLX = Id from  dbo.ConfigurationEntityTree where guidkey='18881EB6-F2B9-44D0-A8DA-C551ABFAF57E'

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueueDlx, N'MachineId', N'PanelCommandManagerPublisher', N'PanelCommandManagerPublisherService', N'ServiceBroker', @EntityIdQExchangePublisherDLX,'E43A276F-B29C-4D2F-880D-1E8AE43A1B0C')


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

