--new Queue AlertHandlerNotifications

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

INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (1, '234AE29C-34F8-4821-B7AC-F8BD253A79CB') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (2, '20D0504A-4499-40F1-A46F-C5D49FC53BA1') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (2, 'FDA831E1-18B7-4887-AED6-16BD326EE7AB') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (3, 'A25672D7-BD12-408B-8E93-9BF8F34D8AB8') 
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (3, 'B75A5F25-9829-4777-A2CD-1AF35D07FD98') 

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='234AE29C-34F8-4821-B7AC-F8BD253A79CB'
select @settingTypeIdExchange = Id from ConfigurationGroups where guidkey='20D0504A-4499-40F1-A46F-C5D49FC53BA1'
select @settingTypeIdExchangeDLX = Id from ConfigurationGroups where guidkey='FDA831E1-18B7-4887-AED6-16BD326EE7AB'
select @settingTypeIdQueue = Id from ConfigurationGroups where guidkey='A25672D7-BD12-408B-8E93-9BF8F34D8AB8'
select @settingTypeIdQueueDlx = Id from ConfigurationGroups where guidkey='B75A5F25-9829-4777-A2CD-1AF35D07FD98'

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdConnection, 1, N'AlertHandlerNotificationsConnection')
	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'ExAlertHandlerNotifications')
	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'ExDlxAlertHandlerNotifications')
	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'QueueAlertHandlerNotifications')
	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'NotificationHandler')--MessageNotificationHandlerConsumer.cs
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 15, N'ExDlxAlertHandlerNotifications')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES (@settingTypeIdQueue, 21, N'5')--instances

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

INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 1, @settingTypeIdConnection, N'MachineId', N'AlertHandlerNotifications', N'AlertHandlerNotificationsService', N'ServiceBroker', NULL,'28B77138-0A1F-4934-A65D-A0606DBF9E21')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchange,  N'MachineId', N'AlertHandlerNotifications', N'AlertHandlerNotificationsService', N'ServiceBroker', NULL,'9EFF9A7B-35F6-4504-B4AB-B04C377FD7FB')

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 @EntityIdQExchangeAlertHandlerNotifications int
select @EntityIdQExchangeAlertHandlerNotifications = Id from  dbo.ConfigurationEntityTree where guidkey='9EFF9A7B-35F6-4504-B4AB-B04C377FD7FB'


INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueue, N'MachineId', N'AlertHandlerNotifications', N'AlertHandlerNotificationsService', N'ServiceBroker', @EntityIdQExchangeAlertHandlerNotifications,'6DCDAC14-6A2D-48A0-8E60-9C2F37AB3750')
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 2, @settingTypeIdExchangeDLX, N'MachineId', N'AlertHandlerNotifications', N'AlertHandlerNotificationsService', N'ServiceBroker', @EntityIdQExchangeAlertHandlerNotifications,'9C333CF7-2D76-4D85-B231-EA18AFC67E37')

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 @EntityIdQExchangeDLX int
select @EntityIdQExchangeDLX = Id from  dbo.ConfigurationEntityTree where guidkey='9C333CF7-2D76-4D85-B231-EA18AFC67E37'


INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) VALUES ( 3, @settingTypeIdQueueDlx, N'MachineId', N'AlertHandlerNotifications', N'AlertHandlerNotificationsService', N'ServiceBroker', @EntityIdQExchangeDLX,'D4EC5AEC-60C1-40DB-9634-B4A0285C0115')

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