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

INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (10, '0B964813-A123-4D79-BAA4-F4554A0C0910') --RulePolicy
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (11, '063E2D8E-6B1B-454C-868B-CC9BCC463A24') --RulePolicyDefinitionType-rules
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (11, '388FB316-EFD0-4D8A-A32E-C58E0B706102') --RulePolicyDefinitionType-periods
INSERT into @ConfigurationGroups (EntityId,GuidKey) VALUES (12, '21CB5990-7DC7-48D3-BE43-2E6031A4D0B1') --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'RulePolicyProDefault' from ConfigurationGroups where guidkey='0B964813-A123-4D79-BAA4-F4554A0C0910'
--MaximumNumberOfRules
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 43, N'128' from ConfigurationGroups where guidkey='0B964813-A123-4D79-BAA4-F4554A0C0910'
--MaximumNumberOfPeriods
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 44, N'16' from ConfigurationGroups where guidkey='0B964813-A123-4D79-BAA4-F4554A0C0910'

--RulePolicyDefinitionType--
--Rules--
--Name
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 1, N'RulePolicyDefinitionType-rules' from ConfigurationGroups where guidkey='063E2D8E-6B1B-454C-868B-CC9BCC463A24'
--Type
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 7, N'Rule' from ConfigurationGroups where guidkey='063E2D8E-6B1B-454C-868B-CC9BCC463A24'
--Type
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 39, N'Maximum' from ConfigurationGroups where guidkey='063E2D8E-6B1B-454C-868B-CC9BCC463A24'
--Manual
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 45, N'20' from ConfigurationGroups where guidkey='063E2D8E-6B1B-454C-868B-CC9BCC463A24'

--RulePolicyDefinitionType--
--Periods--
--Name
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 1, N'RulePolicyDefinitionType-periods' from ConfigurationGroups where guidkey='388FB316-EFD0-4D8A-A32E-C58E0B706102'
--Type
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 7, N'Period' from ConfigurationGroups where guidkey='388FB316-EFD0-4D8A-A32E-C58E0B706102'
--Type
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 39, N'Maximum' from ConfigurationGroups where guidkey='388FB316-EFD0-4D8A-A32E-C58E0B706102'
--Manual
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 45, N'8' from ConfigurationGroups where guidkey='388FB316-EFD0-4D8A-A32E-C58E0B706102'

--RulePolicyRuleType--
--Name
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 1, N'RuleType-Maximum' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--Type
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 7, N'Rule' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--Group
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 39, N'Maximum' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--ExcessiveNumOfDetections
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 52 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--Inactivity
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 62 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--OutOfHome
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 48 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--NoActivityDetected
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 60 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--Wandering
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 47 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--HighNumOfVisits
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 54 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--ShortStayBedroom
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 57 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--Absent
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 61 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--LowNumOfDetections
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 59 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--UnexpectedPresence
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 53 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--ShortStay
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 56 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--LowNumOfVisits
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 58 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--LongStay
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 55 , N'0' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--Presence
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 51 , N'1' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'
--UnexpectedEntryExit
INSERT into @ConfigurationGroupParameters ( GroupId, ParameterId, Value) select id, 49 , N'1' from ConfigurationGroups where guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1'


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'RulePolicyProDefault', N'RulePolicy', NULL, 'A8253AAE-D7B1-49C3-8EC0-61666DBF9022' from ConfigurationGroups where guidkey='0B964813-A123-4D79-BAA4-F4554A0C0910' --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'RulePolicyProDefaultRuleType', N'', t.id, 'EE0D53BF-E817-4BCB-A222-B997A7A2BC1D' from ConfigurationGroups c,ConfigurationEntityTree t where c.guidkey='21CB5990-7DC7-48D3-BE43-2E6031A4D0B1' and t.guidkey='A8253AAE-D7B1-49C3-8EC0-61666DBF9022' --RulePolicyRuleType
--RulePolicyDefinitionType-Rules
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 11, c.id, N'MachineId', N'Policies', N'RulePolicyDefinitionType', N'', t.id, '7F41EF8B-09A0-4420-9D63-7339009CD295' from ConfigurationGroups c,ConfigurationEntityTree t where c.guidkey='063E2D8E-6B1B-454C-868B-CC9BCC463A24' and t.guidkey='A8253AAE-D7B1-49C3-8EC0-61666DBF9022' --RulePolicyDefinitionType
--RulePolicyDefinitionType-Periods
INSERT into @ConfigurationEntityTree ( EntityTypeId, GroupId, MachineName, ServiceName, ServiceId, ModuleIdentifier, ParentEntityId, GuidKey) select 11, c.id, N'MachineId', N'Policies', N'RulePolicyDefinitionType', N'', t.id, 'DD6A0638-0143-498C-A4BE-2792846449DE' from ConfigurationGroups c,ConfigurationEntityTree t where c.guidkey='388FB316-EFD0-4D8A-A32E-C58E0B706102' and t.guidkey='A8253AAE-D7B1-49C3-8EC0-61666DBF9022' --RulePolicyDefinitionType


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