
	--01
	DECLARE @NotificationMethods TABLE ([Id] [int] NOT NULL,[NotificationMethodsType] [nvarchar](50) NOT NULL,[NotificationMethodsDescription] [nvarchar](300) NOT NULL);

	INSERT @NotificationMethods (Id, NotificationMethodsType, NotificationMethodsDescription) VALUES (1, N'EMAIL', N'Send e-mail to user')
	INSERT @NotificationMethods (Id, NotificationMethodsType, NotificationMethodsDescription) VALUES (2, N'SMS', N'Send sms to user')
	INSERT @NotificationMethods (Id, NotificationMethodsType, NotificationMethodsDescription) VALUES (3, N'Push Notification', N'Send notification to SmartPhone')

	MERGE INTO dbo.NotificationMethods AS TARGET  
	USING @NotificationMethods AS SOURCE  
	ON TARGET.[Id] = SOURCE.[Id]
	WHEN MATCHED THEN    
	UPDATE SET 
		Id = SOURCE.Id,
		NotificationMethodsType = SOURCE.NotificationMethodsType,
		NotificationMethodsDescription = SOURCE.NotificationMethodsDescription
	WHEN NOT MATCHED BY TARGET THEN   
	INSERT (Id, NotificationMethodsType, NotificationMethodsDescription)  
	VALUES (SOURCE.Id, SOURCE.NotificationMethodsType, SOURCE.NotificationMethodsDescription)   
	OUTPUT $action, SOURCE.*;
	
	--02
	DBCC CHECKIDENT (EventGroupType, reseed, 0) WITH NO_INFOMSGS
	
	SET IDENTITY_INSERT dbo.EventGroupType ON
	DECLARE @EventGroupType TABLE 
	(
		Id INT NOT NULL,
		EventGroupTypeType NVARCHAR(50) NOT NULL, 
		EventGroupTypeDescription NVARCHAR  NULL, 
		ProductFamily INT NOT NULL
	);

	INSERT @EventGroupType ( Id,EventGroupTypeType, EventGroupTypeDescription, ProductFamily) VALUES (1,'Standard','',2)
	INSERT @EventGroupType ( Id,EventGroupTypeType, EventGroupTypeDescription, ProductFamily) VALUES (2,'Comfort','',2)
	INSERT @EventGroupType ( Id,EventGroupTypeType, EventGroupTypeDescription, ProductFamily) VALUES (3,'Advanced','',2)
	INSERT @EventGroupType ( Id,EventGroupTypeType, EventGroupTypeDescription, ProductFamily) VALUES (4,'Safety And Security','',1)
    INSERT @EventGroupType ( Id,EventGroupTypeType, EventGroupTypeDescription, ProductFamily) VALUES (5,'System','',1)
    INSERT @EventGroupType ( Id,EventGroupTypeType, EventGroupTypeDescription, ProductFamily) VALUES (6,'Comfort', '',1)
	
		
	MERGE INTO dbo.EventGroupType AS TARGET
	USING @EventGroupType AS SOURCE
	ON SOURCE.Id = TARGET.Id 
	   AND SOURCE.EventGroupTypeType = TARGET.EventGroupTypeType 
	   AND SOURCE.ProductFamily = TARGET.ProductFamily
	WHEN MATCHED THEN    
	UPDATE SET EventGroupTypeDescription = SOURCE.EventGroupTypeDescription,
			   ProductFamily = SOURCE.ProductFamily
	WHEN NOT MATCHED BY TARGET THEN
			INSERT (Id, EventGroupTypeType, EventGroupTypeDescription, ProductFamily)
			VALUES (SOURCE.Id ,SOURCE.EventGroupTypeType,SOURCE.EventGroupTypeDescription,SOURCE.ProductFamily)
	OUTPUT $ACTION, SOURCE.*;
	SET IDENTITY_INSERT dbo.EventGroupType OFF
	
	--03
	DECLARE @EventGroups TABLE 
	(
		EventGroupTypeId INT NOT NULL, 
		EventId INT NOT NULL
	);
		
	INSERT @EventGroups( EventGroupTypeId, EventId )
	SELECT NM.Id, CDE.Id
	FROM NotificationProfileEvent  NPE
		JOIN CD_Event CDE
			ON CDE.Event_Code = NPE.EventCode
		JOIN  NotificationMethods NM
			ON NM.Id = NPE.NotificationProfileId
				AND NM.Id in (1,2,3)
	WHERE NPE.NotificationProfileId <> 255
	GROUP BY NM.Id, CDE.Id
	
	MERGE INTO dbo.EventGroups AS TARGET
	USING @EventGroups AS SOURCE
	ON  SOURCE.EventGroupTypeId = TARGET.EventGroupTypeId 
		AND SOURCE.EventId = TARGET.EventId
	WHEN MATCHED THEN    
	UPDATE SET EventGroupTypeId = SOURCE.EventGroupTypeId,
			   EventId = SOURCE.EventId
	WHEN NOT MATCHED BY TARGET THEN
			INSERT ( EventGroupTypeId, EventId)
			VALUES (SOURCE.EventGroupTypeId,SOURCE.EventId)
	OUTPUT $ACTION, SOURCE.*;
	
	--04
	WITH EventGroupNotificationValuesEmail ( EventGroupId,NotificationMethodId )
	AS
	(
		SELECT EG.Id , (SELECT Id FROM NotificationMethods WHERE NotificationMethodsType = 'EMAIL' ) AS NotificationMethodId
		FROM NotificationProfileEvent NPE
			JOIN CD_Event CDE
				ON CDE.Event_Code = NPE.EventCode
			JOIN EventGroups EG
				ON EG.EventId = CDE.Id
		WHERE NPE.IsEmail = 1
	),
	EventGroupNotificationValuesSMS ( EventGroupId ,NotificationMethodId ) AS
	(

		SELECT EG.Id , (SELECT Id FROM NotificationMethods WHERE NotificationMethodsType = 'SMS' ) AS NotificationMethodId
		FROM NotificationProfileEvent NPE
			JOIN CD_Event CDE
				ON CDE.Event_Code = NPE.EventCode
			JOIN EventGroups EG
				ON EG.EventId = CDE.Id
		WHERE NPE.IsSms = 1
	),
	EventGroupNotificationValuesPUSH( EventGroupId ,NotificationMethodId ) AS
	(
		SELECT EG.Id , (SELECT Id FROM NotificationMethods WHERE NotificationMethodsType = 'Push Notification' ) AS NotificationMethodId
		FROM NotificationProfileEvent NPE
			JOIN CD_Event CDE
				ON CDE.Event_Code = NPE.EventCode
			JOIN EventGroups EG
				ON EG.EventId = CDE.Id
		WHERE NPE.IsPush = 1

	)
	INSERT 	dbo.EventGroupNotificationValues (EventGroupId, NotificationMethodId)
	SELECT * FROM EventGroupNotificationValuesEmail
	UNION
	SELECT * FROM EventGroupNotificationValuesSMS
	UNION
	SELECT * FROM EventGroupNotificationValuesPUSH
	
	