--new Queue FtpmanagerJpbs

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

INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (1, '4983A59A-9B6B-4C83-A665-D909CB18CE52') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (2, 'CF13483D-4B8D-45B6-A3B9-3D56DF83D067') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (2, '596BB61C-8822-44AA-B9C6-9B71C1F03E07') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (3, 'A02C3844-F0FA-44FB-B0C1-6900C2364D9C') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (3, '96405C91-9E70-4B26-9422-F5F6518EB7A4') 

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='4983A59A-9B6B-4C83-A665-D909CB18CE52'
select @settingTypeIdExchange = Id from ConfigurationGroups where guidkey='CF13483D-4B8D-45B6-A3B9-3D56DF83D067'
select @settingTypeIdExchangeDLX = Id from ConfigurationGroups where guidkey='596BB61C-8822-44AA-B9C6-9B71C1F03E07'
select @settingTypeIdQueue = Id from ConfigurationGroups where guidkey='A02C3844-F0FA-44FB-B0C1-6900C2364D9C'
select @settingTypeIdQueueDlx = Id from ConfigurationGroups where guidkey='96405C91-9E70-4B26-9422-F5F6518EB7A4'

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 1, N'FtpManagerConnection')
	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'ExFtpManager')
	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'ExDlxFtpManager')
	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'QueueFtpManager')
	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'FtpManager')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 15, N'ExDlxFtpManager')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 21, N'6')--instances

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueueDlx, 1, N'QueueDlxFtpManager')
	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'FtpManagerSubscriber', N'FtpManagerSubscriberService', N'ServiceBroker', NULL,'5B1800C8-4E7A-4CE1-A9F8-E37C29AC4C16')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchange,  N'MachineId', N'FtpManagerSubscriber', N'FtpManagerSubscriberService', N'ServiceBroker', NULL,'E6D1B866-BE0C-496D-AED1-D76F1B9B1104')
----Publisher
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 1, @settingTypeIdConnection, N'MachineId', N'FtpManagerPublisher', N'FtpManagerPublisherService', N'ServiceBroker', NULL,'A67898C2-10F4-4BCA-8AA9-C31A3DAE0CA2')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchange,  N'MachineId', N'FtpManagerPublisher', N'FtpManagerPublisherService', N'ServiceBroker', NULL,'339984C1-0411-4C2F-A6E2-029BB0F9E092')

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='E6D1B866-BE0C-496D-AED1-D76F1B9B1104'

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueue, N'MachineId', N'FtpManagerSubscriber', N'FtpManagerSubscriberService', N'ServiceBroker', @EntityIdQExchangeSubscriber,'F89B4989-84E2-47D2-AC13-F754DB48A52C')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchangeDLX, N'MachineId', N'FtpManagerSubscriber', N'FtpManagerSubscriberService', N'ServiceBroker', @EntityIdQExchangeSubscriber,'ABA4DDAD-E427-4C93-8937-BEE9C38F4DB2')

----Publisher
declare @EntityIdQExchangePublisher int
select @EntityIdQExchangePublisher = Id from  dbo.ConfigurationEntityTree where guidkey='339984C1-0411-4C2F-A6E2-029BB0F9E092'

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueue, N'MachineId', N'FtpManagerPublisher', N'FtpManagerPublisherService', N'ServiceBroker', @EntityIdQExchangePublisher,'E9270DC0-67C3-4093-85EE-19D9339A9657')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchangeDLX, N'MachineId', N'FtpManagerPublisher', N'FtpManagerPublisherService', N'ServiceBroker', @EntityIdQExchangePublisher,'290CFAE7-5454-4FD3-8C4A-AB3009956C11')

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='ABA4DDAD-E427-4C93-8937-BEE9C38F4DB2'

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueueDlx, N'MachineId', N'FtpManagerSubscriber', N'FtpManagerSubscriberService', N'ServiceBroker', @EntityIdQExchangeSubscriberDLX,'D5B4447D-B48D-4FBC-98D0-5917F594AD9C')

----Subscriber
declare @EntityIdQExchangePublisherDLX int
select @EntityIdQExchangePublisherDLX = Id from  dbo.ConfigurationEntityTree where guidkey='290CFAE7-5454-4FD3-8C4A-AB3009956C11'

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueueDlx, N'MachineId', N'FtpManagerPublisher', N'FtpManagerPublisherService', N'ServiceBroker', @EntityIdQExchangePublisherDLX,'428628C3-959C-4AF1-A6FD-7EA372606948')


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

