-- drop table file, folder;

CREATE TABLE "folder" (
    "id" INT PRIMARY key GENERATED ALWAYS AS IDENTITY,
    "name" VARCHAR NOT NULL,
    "parent_id" INTEGER REFERENCES "folder"("id")--,
    --constraint folder_name UNIQUE NULLS NOT DISTINCT (name, parent_id)
);

CREATE TABLE "file" (
    "id" INT PRIMARY key GENERATED ALWAYS AS IDENTITY,
    "name" VARCHAR NOT NULL,
    "parent_id" INTEGER REFERENCES "folder"("id"),
    "contents" TEXT--,
    --constraint folder_name UNIQUE NULLS NOT DISTINCT (name, parent_id)
);

insert into folder (name, parent_id) values
('dir1', null),
('dir2', null),
('dir3', null),
('dir4', null);



insert into folder (name, parent_id)
select t.name, folder.id
from (values 
('subdir1'),
('subdir2'),
('subdir3'),
('subdir4')
) AS t (name)
inner join folder on true;


insert into folder (name, parent_id)
select t.name, folder.parent_id
from (values 
('subsubdir1'),
('subsubdir2'),
('subsubdir3'),
('subsubdir4')
) AS t (name)
inner join folder on folder.parent_id is not null;

insert into file(name, parent_id, contents)
select t.name, folder.id, ''
from (values 
('file1.txt'),
('t.test')
) AS t (name)
inner join folder on true;

insert into file(name, parent_id, contents)
values ('root.txt', null, '');

update file
set name = id || '-' || name;


WITH RECURSIVE tree(id, parent_id, name) AS (
    select id, parent_id, array[name] from folder
  UNION ALL
    SELECT tree.id, folder.parent_id, folder.name || tree.name
    from tree
    inner join folder on folder.id = tree.parent_id 
)
select 
file.id,
coalesce(array_to_string(tree.name || file.name, '/'), file.name) as path
from file
left join tree on tree.parent_id is null and tree.id = file.parent_id
order by 2