
declare @RoleAuthorizationGroups table(
	[RoleId] [int] NOT NULL,
	[GroupId] [int] NOT NULL,
	[Scope] [nvarchar](50) NULL
)

--'Subscriber',			
-- has no groups!

--'Warehouse technician',	
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='31EBDBC3-8596-4448-A063-1D462772118A' and g.guid in (
'ED65F06B-4DE4-4B3B-9E7B-AA3849362A16',
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'BC6EAC6C-0C91-4169-A2C2-33AE54902E60',
'80F67FCE-680C-4FD5-BA74-C91A4EBFFAD8',
'80EA898F-4712-48BF-A7BA-82FFA2905AA1',
'2CAE9C2D-802A-42B9-9ADA-45A1C5433A36',
'44E26B50-C07B-4480-9CBD-7C3576D7D014',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'C2E85DBA-D1EB-4FFE-89AF-01D07A4BD56D',
'6BDC7B82-1142-4327-97F0-40FCCAF795F1',
'BEBDA7CF-A6BF-4621-9E7A-0BEDDE55526C',
'FBF64DE8-BE89-40D4-9A3F-30567186AB36')

--'Technician',			
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='A508E668-94BC-4076-A8EE-972ADAD06E64' and g.guid in (
'ED65F06B-4DE4-4B3B-9E7B-AA3849362A16',
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'BC6EAC6C-0C91-4169-A2C2-33AE54902E60',
'80EA898F-4712-48BF-A7BA-82FFA2905AA1',
'44E26B50-C07B-4480-9CBD-7C3576D7D014',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'C2E85DBA-D1EB-4FFE-89AF-01D07A4BD56D',
'BEBDA7CF-A6BF-4621-9E7A-0BEDDE55526C',
'F6CA26B0-BA14-4681-8B15-EC1CC9804386',
'FBF64DE8-BE89-40D4-9A3F-30567186AB36')

--'Support',				
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='14665214-632A-4729-B22B-892CC31B1894' and g.guid in(
'ED65F06B-4DE4-4B3B-9E7B-AA3849362A16',
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'BC6EAC6C-0C91-4169-A2C2-33AE54902E60',
'80F67FCE-680C-4FD5-BA74-C91A4EBFFAD8',
'80EA898F-4712-48BF-A7BA-82FFA2905AA1',
'2CAE9C2D-802A-42B9-9ADA-45A1C5433A36',
'1DB93246-E71B-4004-ACAA-F0DC4EF94690',
'5A0FBC1D-C7DD-4C5B-801D-BCE7A0E276A1',
'44E26B50-C07B-4480-9CBD-7C3576D7D014',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'C2E85DBA-D1EB-4FFE-89AF-01D07A4BD56D',
'6BDC7B82-1142-4327-97F0-40FCCAF795F1',
'E4E43600-DA93-435F-8EE9-49DF3BEB89C7',
'BEBDA7CF-A6BF-4621-9E7A-0BEDDE55526C',
'F6CA26B0-BA14-4681-8B15-EC1CC9804386',
'FBF64DE8-BE89-40D4-9A3F-30567186AB36')

--'Admin',				
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='62FD0F19-43D8-4F1E-B7C7-57A8D17A0131' and g.guid in (
'572D45DE-1302-4940-AD3F-1F6556BC5F19',
'6E0A37F5-58FD-43D0-AC97-4FBC19FE664D',
'ED65F06B-4DE4-4B3B-9E7B-AA3849362A16',
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'BC6EAC6C-0C91-4169-A2C2-33AE54902E60',
'80F67FCE-680C-4FD5-BA74-C91A4EBFFAD8',
'80EA898F-4712-48BF-A7BA-82FFA2905AA1',
'2CAE9C2D-802A-42B9-9ADA-45A1C5433A36',
'1DB93246-E71B-4004-ACAA-F0DC4EF94690',
'5A0FBC1D-C7DD-4C5B-801D-BCE7A0E276A1',
'44E26B50-C07B-4480-9CBD-7C3576D7D014',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'C2E85DBA-D1EB-4FFE-89AF-01D07A4BD56D',
'6BDC7B82-1142-4327-97F0-40FCCAF795F1',
'E4E43600-DA93-435F-8EE9-49DF3BEB89C7',
'BEBDA7CF-A6BF-4621-9E7A-0BEDDE55526C',
'F6CA26B0-BA14-4681-8B15-EC1CC9804386',
'FBF64DE8-BE89-40D4-9A3F-30567186AB36')

--'Organization Admin',	
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='1BF4E983-EC11-4A4C-B02B-8F2F7631DAA9' and g.guid in (
'572D45DE-1302-4940-AD3F-1F6556BC5F19',
'6E0A37F5-58FD-43D0-AC97-4FBC19FE664D',
'ED65F06B-4DE4-4B3B-9E7B-AA3849362A16',
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'BC6EAC6C-0C91-4169-A2C2-33AE54902E60',
'80F67FCE-680C-4FD5-BA74-C91A4EBFFAD8',
'80EA898F-4712-48BF-A7BA-82FFA2905AA1',
'2CAE9C2D-802A-42B9-9ADA-45A1C5433A36',
'1DB93246-E71B-4004-ACAA-F0DC4EF94690',
'5A0FBC1D-C7DD-4C5B-801D-BCE7A0E276A1',
'44E26B50-C07B-4480-9CBD-7C3576D7D014',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'C2E85DBA-D1EB-4FFE-89AF-01D07A4BD56D',
'6BDC7B82-1142-4327-97F0-40FCCAF795F1',
'E4E43600-DA93-435F-8EE9-49DF3BEB89C7',
'BEBDA7CF-A6BF-4621-9E7A-0BEDDE55526C',
'F6CA26B0-BA14-4681-8B15-EC1CC9804386',
'FBF64DE8-BE89-40D4-9A3F-30567186AB36')

--'System admin',			
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='0E9CB844-06B6-4AEA-A0DC-0FC17EE71BE2' and g.guid in (
'572D45DE-1302-4940-AD3F-1F6556BC5F19',
'6E0A37F5-58FD-43D0-AC97-4FBC19FE664D',
'ED65F06B-4DE4-4B3B-9E7B-AA3849362A16',
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'BC6EAC6C-0C91-4169-A2C2-33AE54902E60',
'80F67FCE-680C-4FD5-BA74-C91A4EBFFAD8',
'80EA898F-4712-48BF-A7BA-82FFA2905AA1',
'2CAE9C2D-802A-42B9-9ADA-45A1C5433A36',
'1DB93246-E71B-4004-ACAA-F0DC4EF94690',
'5A0FBC1D-C7DD-4C5B-801D-BCE7A0E276A1',
'44E26B50-C07B-4480-9CBD-7C3576D7D014',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'C2E85DBA-D1EB-4FFE-89AF-01D07A4BD56D',
'6BDC7B82-1142-4327-97F0-40FCCAF795F1',
'E4E43600-DA93-435F-8EE9-49DF3BEB89C7',
'BEBDA7CF-A6BF-4621-9E7A-0BEDDE55526C',
'519B59BD-5E6A-4688-8159-E80AC7932A36',
'5323738B-E3AB-4F1B-8832-4DB062FDED08',
'F6CA26B0-BA14-4681-8B15-EC1CC9804386',
'FBF64DE8-BE89-40D4-9A3F-30567186AB36')

--'Caregiver',			
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='D0275602-E7DF-420F-9E29-77AF67A7B844' and g.guid in ('F6CA26B0-BA14-4681-8B15-EC1CC9804386','FBF64DE8-BE89-40D4-9A3F-30567186AB36')

insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,'AssociatedAccounts' from roles r , RoleAuthorizationGroupTypes g where r.guid='D0275602-E7DF-420F-9E29-77AF67A7B844' and g.guid in (
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'C2E85DBA-D1EB-4FFE-89AF-01D07A4BD56D',
'BEBDA7CF-A6BF-4621-9E7A-0BEDDE55526C')


--'Health proffesional',	
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='B7B24D8B-3ACE-4B6B-8581-6ABD4375470A' and g.guid='519B59BD-5E6A-4688-8159-E80AC7932A36'
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,'AssociatedAccounts' from roles r , RoleAuthorizationGroupTypes g where r.guid='B7B24D8B-3ACE-4B6B-8581-6ABD4375470A' and g.guid in ('B47D02CF-F595-4652-80C4-8F8C34DE8C2E','6ADC970A-7319-45DF-82C9-CF6AAAC5449C')

--'Physician',			
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='D4F9CE6E-C39F-4179-B2FB-A02F2E4C86B6' and g.guid in ('519B59BD-5E6A-4688-8159-E80AC7932A36','5323738B-E3AB-4F1B-8832-4DB062FDED08')
insert into @RoleAuthorizationGroups (roleid,groupid,scope)										  
select r.id,g.id,'AssociatedAccounts' from roles r , RoleAuthorizationGroupTypes g where r.guid='D4F9CE6E-C39F-4179-B2FB-A02F2E4C86B6' and g.guid in (
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'44E26B50-C07B-4480-9CBD-7C3576D7D014',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'C2E85DBA-D1EB-4FFE-89AF-01D07A4BD56D')

--'Medical admin',		
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='A6B2BFC3-D2E0-4496-81D0-224F77085DA5' and g.guid in (
'B47D02CF-F595-4652-80C4-8F8C34DE8C2E',
'5A0FBC1D-C7DD-4C5B-801D-BCE7A0E276A1',
'44E26B50-C07B-4480-9CBD-7C3576D7D014',
'6ADC970A-7319-45DF-82C9-CF6AAAC5449C',
'E4E43600-DA93-435F-8EE9-49DF3BEB89C7',
'BEBDA7CF-A6BF-4621-9E7A-0BEDDE55526C',
'519B59BD-5E6A-4688-8159-E80AC7932A36',
'5323738B-E3AB-4F1B-8832-4DB062FDED08')


MERGE INTO [dbo].RoleAuthorizationGroups AS TARGET
USING @RoleAuthorizationGroups AS SOURCE
	ON TARGET.roleid = SOURCE.roleid
	and TARGET.groupid= SOURCE.groupid
	WHEN MATCHED THEN    
			UPDATE SET scope=source.scope
	WHEN NOT MATCHED BY TARGET THEN   
			INSERT ( roleid,groupid,scope) VALUES (Source.roleid,Source.groupid,Source.scope)  
OUTPUT $action, SOURCE.*;
