DECLARE @ConfigurationGroups TABLE (EntityId int NOT NULL,GuidKey uniqueidentifier not null );

INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (10, '2C089671-15F1-4837-93DE-C7B67ECBC38C') --RulePolicy
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (12, 'A3A6547B-5DE7-493B-A96C-6D0A811626E9') --RulePolicyRuleType

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));

--RulePolicy--
--Name
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 1, N'RulePolicyFamilyDefault' from ConfigurationGroups where guidkey='2C089671-15F1-4837-93DE-C7B67ECBC38C'
--MaximumNumberOfRules
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 43, N'128' from ConfigurationGroups where guidkey='2C089671-15F1-4837-93DE-C7B67ECBC38C'
--MaximumNumberOfPeriods
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 44, N'16' from ConfigurationGroups where guidkey='2C089671-15F1-4837-93DE-C7B67ECBC38C'

--RulePolicyRuleType--
--Name
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 1, N'RuleType-Maximum' from ConfigurationGroups where guidkey='A3A6547B-5DE7-493B-A96C-6D0A811626E9'
--Group
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 39, N'Maximum' from ConfigurationGroups where guidkey='A3A6547B-5DE7-493B-A96C-6D0A811626E9'
--Wandering
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 47, N'1' from ConfigurationGroups where guidkey='A3A6547B-5DE7-493B-A96C-6D0A811626E9'
--OutOfHome
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 48, N'1' from ConfigurationGroups where guidkey='A3A6547B-5DE7-493B-A96C-6D0A811626E9'
--UnexpectedEntryExit
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 49, N'1' from ConfigurationGroups where guidkey='A3A6547B-5DE7-493B-A96C-6D0A811626E9'
--Inactivity
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 62, N'10' from ConfigurationGroups where guidkey='A3A6547B-5DE7-493B-A96C-6D0A811626E9'


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


--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 );

--RulePolicy
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 10, id, N'MachineId', N'Policies', N'RulePolicyFamilyDefault', N'RulePolicy', NULL, 'CDB6E566-8069-4EAF-B126-4DC95D494C72' from ConfigurationGroups where guidkey='2C089671-15F1-4837-93DE-C7B67ECBC38C' --RulePolicy

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 from @ConfigurationEntityTree


--RulePolicyRuleType
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 12, c.id, N'MachineId', N'Policies', N'RulePolicyFamilyDefaultRuleType', N'', t.id, '5C3B53AA-E881-4A15-A1E6-EDFFDC779E5E' from ConfigurationGroups c,ConfigurationEntityTree t where c.guidkey='A3A6547B-5DE7-493B-A96C-6D0A811626E9' and t.guidkey='CDB6E566-8069-4EAF-B126-4DC95D494C72' --RulePolicyRuleType

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