CREATE OR REPLACE PROCEDURE INIT_PTT
IS
    r NUMBER(10,0) := 0 ;
    l NUMBER(10,0) := 1 ;
    PROCEDURE branches(lvl IN NUMBER, p_id IN NUMBER, lft IN NUMBER, rgt OUT NUMBER) 
    IS
        l1 NUMBER(10,0) := lft;
        lvl1 NUMBER(10,0) := lvl+1;
	idx NUMBER(10,0) := 0 ;
    BEGIN
         rgt := lft + 1  ;
        FOR rec IN (SELECT id, parent_id, name FROM t_data WHERE parent_id = p_id ORDER BY name)
        LOOP
            branches(lvl1, rec.id, l1+1, rgt) ;
            UPDATE t_data set lvl = lvl1, left = (l1+1), right = rgt, sibling_idx = idx WHERE id = rec.id ;
	    idx := idx + 1;
            l1 := rgt ;
            rgt := rgt + 1;
        END LOOP ;
    END ;
BEGIN
 
       FOR rec IN (SELECT id FROM t_data WHERE parent_id is NULL ORDER BY name)
    LOOP
        branches(0, rec.id, l, r) ;
        UPDATE t_data  set lvl = 0, left = l, right = r, sibling_idx = 0 WHERE id = rec.id ;
        l := r + 1 ;
    END LOOP ;
 
       COMMIT ;
END ;

WITH hdata(lvl, id, name, parent_id, root_id) AS (
    select 0, id, name, parent_id, id from t_data d
    where parent_id is null
    union all
	select h.lvl+1, d.id, d.name, d.parent_id, h.root_id
	from t_data d
        join hdata h on d.parent_id = h.id
)
SEARCH DEPTH FIRST BY parent_id SET rn,
sdata(lvl, id, name, parent_id, root_id) as (
    select 0, id, name, parent_id, id from t_data d
    union all
	select h.lvl+1, d.id, d.name, d.parent_id, h.root_id
	from t_data d
        join sdata h on d.parent_id = h.id
),
summing as(
	select d.lvl, d.id, d.name, d.parent_id, h.cnt 
	from hdata d
	join (
		select root_id, count(id)-1 as cnt from sdata
		group by root_id
	)
	h on d.id = h.root_id
),
precalc as (
	select s.*, h.rn, lag(s.lvl,1) over(order by h.rn) as prev_lvl,
		(select count(s.id) from hdata h1 where h1.lvl = h.lvl and h1.rn < h.rn and (h1.parent_id = h.parent_id
			or (h1.parent_id is null and h.parent_id is null))
		) as n_leftbrothers,
		lag(h.rn,1) over(partition by s.lvl, s.parent_id order by h.rn) as prev_brother_rn
	from summing s
	join hdata h on h.id = s.id
)
select p.rn, p.id, p.name, p.parent_id, p.left, p.right, p.lvl, p.sibling_idx, p.done, p.hasrun, p.lim,d.*
from (
	select rn, id, name, parent_id, left, right, lvl, sibling_idx, done, hasrun, lim
	from precalc 
	model 
		dimension by(rn)
		measures(id as id, name as name, cast(null as number) as left,  cast(null as number) as right, 0 as up, lvl as lvl, prev_lvl as prev_lvl, 
			n_leftbrothers, 
			cnt as cnt, prev_brother_rn as prev_brother_rn, parent_id as parent_id,
			0 as sibling_idx,
			0 as done, 0 as hasrun, (select max(rn) from precalc) as lim)
		rules 
			ITERATE(4294967295) UNTIL( hasrun[ITERATION_NUMBER+1] = lim[1])
		(
			up[ANY] = case when prev_lvl[cv()] > lvl[cv()] or up[cv()-1] = 1 then 1 else 0 end,
			
			-- first root
			left[1] = 1,
			right[1] = 2*cnt[1]+2,
			sibling_idx[1] = 0,
			
			-- others
			left[rn > 1] order by rn asc = 
				case when lvl[cv()] = 0 then right[prev_brother_rn[cv()]] + 1 
				else
					case when prev_lvl[cv()] < lvl[cv()] then
						-- we are going down
				 
						case when n_leftbrothers[cv()] = 0 then 
							-- we are the leftmost child of our father
							case when up[cv()] = 0 then
								-- our father is also the leftmost
								cv(rn) 
							else
								left[cv()-1] + 1
							end
						else 
							-- this should move to the next left because depends on the next right
							right[prev_brother_rn[cv()]]
						end
					when prev_lvl[cv()] > lvl[cv()] then 
						-- we are going up
						case when n_leftbrothers[cv()] = 0 then 
							cv(rn) -- we are the leftmost child
						else 
							right[prev_brother_rn[cv()]] + 1 
						end
					else
						-- we are going to brother
						right[cv()-1] + 1
					end
				end,
							right[rn > 1] order by rn asc = 
				case when lvl[cv()] = 0 then left[cv()] + 2*cnt[cv()] + 1 
				else
					2*cnt[cv()] + 1 +
					case when prev_lvl[cv()] < lvl[cv()] then
						-- we are going down
		 
						case when n_leftbrothers[cv()] = 0 then 
							-- we are the leftmost child of our father
							case when up[cv()] = 0 then
								-- our father is also the leftmost
								cv(rn) 
							else
								left[cv()-1] + 1
							end
						else 
							-- this should move to the next left because dep
ends on the next right
							right[prev_brother_rn[cv()]]						end
					when prev_lvl[cv()] > lvl[cv()] then 
						-- we are going up
						case when n_leftbrothers[cv()] = 0 then 
							cv(rn) -- we are the leftmost child
						else 
							right[prev_brother_rn[cv()]] + 1 
						end
					else
						-- we are going to brother
						right[cv()-1] + 1
					end
				end,
			
			sibling_idx[rn > 1] order by rn asc = nvl(sibling_idx[prev_brother_rn[cv()]],-1) + 1,
			
			done[ANY] order by rn asc = nvl(done[cv()-1],0) + case when left[cv()] is not null and right[cv()] is not null then 1 else 0 end,
			hasrun[ITERATION_NUMBER+1] = done[lim[1]]
		)
) p
;