/*
Add timer Job + Parameters
		- MSISDN Retriaval Report
*/

   SET IDENTITY_INSERT [dbo].[ConfigurationParameterTypes] ON 
	
	DECLARE @ConfigurationParameterTypes TABLE ([Id] int NOT NULL,[Title] [nvarchar](max) NOT NULL);

	INSERT into @ConfigurationParameterTypes ( Id, Title) VALUES ( 91, N'NextRunTime')
	INSERT into @ConfigurationParameterTypes ( Id, Title) VALUES ( 92, N'ExecutionInterval')

	MERGE INTO dbo.ConfigurationParameterTypes AS TARGET  
			USING @ConfigurationParameterTypes AS SOURCE  
			ON TARGET.Id  = SOURCE.Id
			WHEN MATCHED THEN    
					UPDATE SET Title=source.Title
			WHEN NOT MATCHED BY TARGET THEN   
					INSERT ( Id, Title) VALUES (Source.Id, SOURCE.Title)  
	OUTPUT $action, SOURCE.*;

	SET IDENTITY_INSERT [dbo].[ConfigurationParameterTypes] OFF 


	--04: ConfigurationGroups
	--	Create Instance of the group 
	DECLARE @ConfigurationGroups TABLE (EntityId int NOT NULL,GuidKey uniqueidentifier not null );

	--Job
	INSERT into @ConfigurationGroups (EntityId, GuidKey) VALUES ( 4,'94B46650-4591-439B-B093-43986EC29AF8')
	--Job parameters
	INSERT into @ConfigurationGroups (EntityId, GuidKey) VALUES ( 5,'46E35A0A-998C-48FD-A725-CABAD04B73C2')

	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 @configurationGroupIdJob int
	select @configurationGroupIdJob = id from ConfigurationGroups where guidkey='94B46650-4591-439B-B093-43986EC29AF8'

		declare @configurationGroupIdJobParameters int
	select @configurationGroupIdJobParameters = id from ConfigurationGroups where guidkey='46E35A0A-998C-48FD-A725-CABAD04B73C2'

	--05: ConfigurationGroupParameters
	--	Fill the group instance
	DECLARE @ConfigurationGroupParameters TABLE (GroupId int NOT NULL,ParameterId int not null, Value nvarchar(max));

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES ( @configurationGroupIdJob, 1, N'MSISDNRetrievalReportHandlerTimerJob')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES ( @configurationGroupIdJob, 16, N'MSISDNRetrievalReportTimerJob')
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES ( @configurationGroupIdJob, 17, N'1')

	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 1, N'MSISDNRetrievalReportHandler')	--Name
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 18, N'300') --Interval 5 minute
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 19, N'1')	--IntervalUnit
--	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 20, N'250')	--BulkSize
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 22, N'')	--DependantProcesses
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 23, N'-300')	--ProcessId
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 80, N'1')	--IsEnabled

	--2 new parameter for MSISDN report handler
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 91, N'2022-12-20 00:00')	--value is datetime - holds the exact time to execute logic
	INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) VALUES	( @configurationGroupIdJobParameters, 92, N'604800')	--value in seconds - time to be added to prev param, after the execution of the logic

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

	--06: ConfigurationEntityTree
	--	Set the identifier in the tree for the instance
	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 );

	--Job
	INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 4, @configurationGroupIdJob, N'MachineId', N'MSISDNReportJob', N'',N'', NULL, '201802E2-F271-4325-9B7D-03CD4EC3A3F3'

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


	-- delete values from temp table
	delete from @ConfigurationEntityTree
	
	declare @treeJobId int
	select @treeJobId = id from ConfigurationEntityTree where guidkey='201802E2-F271-4325-9B7D-03CD4EC3A3F3'

	--Job parameters
	INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 5, @configurationGroupIdJobParameters, N'MachineId', N'MSISDNReportJob', N'MSISDNReportHandlerTimerJob',N'', @treeJobId, 'E8A7D3BD-9C05-4841-8695-CED845878038'


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