-- Tables, data, and functions to test a nested interval implementation

-- create table and data for testing the ni_* functions

create table ni_data (
      node ni_path not null unique
    , numer integer not null
    , denom integer not null
    , unique (numer,denom)
);

insert into ni_data (node, numer, denom) values ('1',3,2);
insert into ni_data (node, numer, denom) values ('1.1',7,4);
insert into ni_data (node, numer, denom) values ('1.1.1',15,8);
insert into ni_data (node, numer, denom) values ('1.1.1.1',31,16);
insert into ni_data (node, numer, denom) values ('1.1.2',27,16);
insert into ni_data (node, numer, denom) values ('1.1.3',51,32);
insert into ni_data (node, numer, denom) values ('1.2',11,8);
insert into ni_data (node, numer, denom) values ('1.2.1',23,16);
insert into ni_data (node, numer, denom) values ('1.3',19,16);

insert into ni_data (node, numer, denom) values ('2',3,4);
insert into ni_data (node, numer, denom) values ('2.1',7,8);
insert into ni_data (node, numer, denom) values ('2.1.1',15,16);
insert into ni_data (node, numer, denom) values ('2.2',11,16);
insert into ni_data (node, numer, denom) values ('2.3',19,32);

insert into ni_data (node, numer, denom) values ('3',3,8);
insert into ni_data (node, numer, denom) values ('3.1',7,16);
insert into ni_data (node, numer, denom) values ('3.2',11,32);

-- folders table and associated functions

create table folders (
    folder_name text not null unique
    , num integer not null
    , den integer not null
    , unique (num,den)
);

-- initialize the tree
insert into folders (folder_name, num, den) values ('root', 3,2);

create or replace function insert_folder (
      text -- parent_folder_name
    , text -- new_folder_name
    ) returns void
    language plpgsql as '
    declare
        parent_folder_name alias for $1;
        new_folder_name alias for $2;

        parent_folder_rational record;
        new_child_number integer;
    begin
        select num, den
            into parent_folder_rational
        from folders where folder_name = parent_folder_name;
        
        select (count(folder_name) + 1)::integer into new_child_number
        from folders
        where ni_parent_numer(num, den) = parent_folder_rational.num
            and ni_parent_denom(num,den) = parent_folder_rational.den;

        insert into folders (folder_name, num, den) values (
            new_folder_name
            , ni_child_numer(
                parent_folder_rational.num
                , parent_folder_rational.den
                , new_child_number
                )
            , ni_child_denom(
                parent_folder_rational.num
                , parent_folder_rational.den
                , new_child_number
                )
            );
       return;
    end;
    ';

select insert_folder('root','usr');
select insert_folder('usr','local');
select insert_folder('local','src');
select insert_folder('local','pgsql');
select insert_folder('pgsql','bin');
select insert_folder('pgsql','data');
select insert_folder('pgsql','doc');
select insert_folder('doc','html');
select insert_folder('pgsql','include');
select insert_folder('pgsql','lib');
select insert_folder('pgsql','share');

create or replace view folder_hierarchy as 
select 
      folder_name
    , num
    , den
    , num || '/' || den as folder_rational
    , repeat('  ',ni_distance(num,den, 3,2)) 
        || folder_name as folder_name_
    , ni_path(num,den) as folder_path
from folders 
order by folder_path;

create or replace function delete_folder (
    text -- folder_name
    ) returns boolean
    language plpgsql as '
    declare
        old_folder_name alias for $1;
        old_folder_child_number integer;
        sibling_count integer;
        old_folder record;
        parent_folder record;

        old_folder_child_count integer;
        old_folder_document_count integer;
        
        old_child record;
        new_child record;
        
    begin
        select num, den
            into old_folder
        from folders where folder_name = old_folder_name;

        select (count(folder_name))::integer 
            into old_folder_child_count
        from folders
        where ni_parent_numer(num, den) = old_folder.num
            and ni_parent_denom(num, den) = old_folder.den;
        
        -- check to make sure the folder is empty (it has no children)
        -- and it is not the root folder, raising exceptions in either case

        if old_folder_child_count = 0 
            and old_folder.num <> 3 
            and old_folder.den <> 2 then
            select 
                ni_parent_numer(old_folder.num,old_folder.den) as num
                , ni_parent_denom(old_folder.num,old_folder.den) as den
                into parent_folder;

            select (count(folder_name))::integer into sibling_count
            from folders
            where ni_parent_numer(num, den) = parent_folder.num
                and ni_parent_denom(num,den) = parent_folder.den;

            old_folder_child_number := 
                ni_sibling_number(old_folder.num, old_folder.den);

            delete from folders where folder_name = old_folder_name;
            
            if (sibling_count - old_folder_child_number) > 1 then  
                -- There are younger siblings of the deleted folder.
                -- Promote each by one child number to fill the
                -- hole left by the deleted folder
                for i in (old_folder_child_number + 1)..sibling_count loop

                    select 
                        ni_child_numer(parent_folder.num, parent_folder.den, i) as num
                        , ni_child_denom (parent_folder.num, parent_folder.den, i) as den
                    into old_child;

                    select 
                        ni_child_numer(parent_folder.num, parent_folder.den, i - 1) as num
                        , ni_child_denom (parent_folder.num, parent_folder.den, i - 1) as den
                    into new_child;
                    
                    update folders set 
                        num = ni_new_numer(
                            num, den 
                            , old_child.num, old_child.den
                            , new_child.num, new_child.den
                            )
                        , den = ni_new_denom(
                            num, den 
                            , old_child.num, old_child.den
                            , new_child.num, new_child.den
                            )
                    where ni_distance( num,den, old_child.num,old_child.den) >= 0;
                end loop;
            end if;
            return true;
        elsif (old_folder_child_count) <> 0
            then raise exception ''Cannot delete non-empty folder.'';
        elsif (old_folder.num = 3 and old_folder.den = 2) 
            then raise exception ''Cannot delete root folder.'';
        end if;
    end;
    ';