/*
The organizational chart would look like this as a directed graph:

Albert (1,12)
/        /
/            /
Bert (2,3)    Chuck (4,11)
/    |   /
/      |     /
/        |       /
/          |         /
Donna (5,6)  Eddie (7,8)  Fred (9,10)
*/

-- Transform recursion into non-recursion
-- Pre-Order tree traverse

-- 6. To convert an adjacency list model into a nested set model, use a push down stack algorithm.  Assume that we have these tables:

declare @tree table(emp varchar(30), boss varchar(30))
insert @tree
select 'Albert', NULL
union all select 'Bert', 'Albert'
union all select 'Chuck', 'Albert'
union all select 'Donna', 'Chuck'
union all select 'Eddie', 'Chuck'
union all select 'Fred', 'Chuck'

-- stack hold the info about nested_set
-- stack_index < 0 means the entry is pop out of the stack
declare @stack table(stack_index int, emp varchar(30), lft int, rgt int)

declare @counter int, @max_counter int, @current_top int
select @counter = 2, @max_counter = 2 * (select count(*) from @tree), @current_top = 1

insert @stack select 1, emp, 1, NULL from @tree where boss is null
delete from @tree where boss is null

select [@counter] = 1, [@current_top] = 1, * from @stack

-- check weather stack is empty (get rid of the variable @max_counter)
-- while exists (select * from @stack where stack_index > 0)
while @counter <= (@max_counter)
begin
-- does the employee(stack_index = @current_top) has any subordinate ??
if exists(select * from @tree t where (t.boss = (select emp from @stack where stack_index = @current_top)))
begin
-- push when top has subordinates and set lft value
insert @stack select (@current_top+1), min(t.emp), @counter, NULL
from @tree t where (t.boss = (select emp from @stack where stack_index = @current_top))
-- delete the tree entry corresponding to the aforementioned subordinate
delete from @tree where emp = (select emp from @stack where stack_index = @current_top + 1)

select @current_top = @current_top + 1
end
else
begin -- pop the stack and set rgt value
update @stack set rgt = @counter, stack_index = -stack_index /*pop stack */ where stack_index = @current_top
select @current_top = @current_top - 1
end

select [@counter] = @counter, [@current_top] = @current_top, * from @stack

select @counter = @counter + 1
end
select * from @stack