declare @typenamespace varchar(200)
declare @everyone_ID int
declare @origin varchar(200)

set @typenamespace = '14cb48ab-e10a-4a66-a992-e5201c3f1083.group'
set @everyone_ID = (select id from TypeInstance where typeNamespace = @typenamespace and parentID = -1)
set @origin = 'BARIUM_BBE_GROUPS'

-- Get BBE groups
select id, parent_id, case when NoOfChildren = 0 then 0 else 1 end hasChildren, name, description into #groups from BariumRegistry..[Group]

-- Fill TypeInstance basic
insert into TypeInstance (parentID, typeNamespace, securityID, keyValue, hasChildren, origin, originKey)
select -1, @typenamespace, newid(), name, hasChildren, @origin, convert(varchar, id) from #groups

-- Update TypeInstance.parentId
update child
set parentID = case when g.id <> g.parent_id then parent.id else @everyone_ID end
from
	TypeInstance child
	inner join #groups g on child.originKey = convert(varchar, g.id)
	inner join TypeInstance parent on convert(varchar, g.parent_id) = parent.originKey
where
	child.origin = @origin and
	parent.origin = @origin

-- Fill TypeInstanceInfo
insert into TypeInstanceInfo (typeInstanceId, cultureID, name, description)
select ti.id, 'neutral', g.name, g.description
from
	#groups g
	inner join TypeInstance ti on convert(varchar, g.id) = ti.originKey
where
	ti.typeNamespace = @typeNamespace and
	ti.origin = @origin

-- Remove Properties without proper groups
delete P
from
	Property p
	left outer join #groups g on p.typeInstanceID = convert(varchar, g.id)
where
	p.typeNamespace = @typeNamespace and
	g.id is null

-- Update Property.typeInstanceID
update p
set typeInstanceID = ti.ID
from
	Property p
	inner join TypeInstance ti on p.typeInstanceID = ti.originKey
where
	p.typeNamespace = @typeNamespace and
	ti.origin = @origin

-- Update TypeInstance.hierarchyIndex
declare @lvl int
set @lvl = 1
select @everyone_ID id, @lvl lvl into #groupTree

while @@rowcount > 0
begin
	set @lvl = @lvl + 1

	update child
	set hierarchyIndex = parent.hierarchyIndex + ',' + convert(varchar, child.ID)
	from
		#groupTree g
		inner join TypeInstance parent on g.id = parent.ID
		inner join TypeInstance child on parent.ID = child.parentID
	where
		g.lvl = @lvl - 1 and
		(parent.origin = @origin or parent.ID = @everyone_ID) and
		child.origin = @origin

	insert into #groupTree (id, lvl)
	select child.id, @lvl
	from
		#groupTree g
		inner join TypeInstance child on g.id = child.parentID
	where
		g.lvl = @lvl - 1 and
		child.origin = @origin
end

--update TypeInstance set origin = null where origin = @origin
drop table #groups