WITH RECURSIVE
	project_subtree(uuid) as (
--	values ('x1u39-j7d0g-rufsjii9txtu2xg')
--	values ('x1u39-tpzed-fr97h9t4m5jffxs')
	values ('x1u39-tpzed-3kz0nwtjehhl0u4')
	union
	select groups.uuid from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
	)
	select uuid from project_subtree;

WITH RECURSIVE edges(tail_uuid, head_uuid) as (
	  select groups.owner_uuid, groups.uuid from groups
	  union
	  select links.tail_uuid, links.head_uuid from links
	   where links.link_class='permission'
	),
	project_subtree(uuid) as (
--	values ('x1u39-j7d0g-rufsjii9txtu2xg')
--	values ('x1u39-tpzed-fr97h9t4m5jffxs')
	values ('x1u39-tpzed-3kz0nwtjehhl0u4')
	union
	(select edges.head_uuid from edges join project_subtree on (edges.tail_uuid = project_subtree.uuid)
	))
	select uuid from project_subtree;


WITH RECURSIVE perm_value(name, val) AS (
         VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)
        ),
        edges(tail_uuid, head_uuid, val) as (
	  select groups.owner_uuid, groups.uuid, (3) from groups
	  union
	  select links.tail_uuid, links.head_uuid, pv.val from links
	  JOIN perm_value pv ON ((pv.name = (links.name)::text))
	   where links.link_class='permission'
	),
	project_subtree(target_uuid, val) as (
--	values ('x1u39-j7d0g-rufsjii9txtu2xg')
--	values ('x1u39-tpzed-fr97h9t4m5jffxs')
	values ('x1u39-tpzed-3kz0nwtjehhl0u4', 3)
	union
	(select edges.head_uuid, least(edges.val, project_subtree.val) from edges join project_subtree
	on (edges.tail_uuid = project_subtree.target_uuid)
	))
	select target_uuid, val from project_subtree;

create table computed_permissions (
user_uuid character varying (27), target_uuid character varying (27), perm_level smallint);
create unique index computed_permissions_index on computed_permissions (user_uuid, target_uuid);

-- unique index on (user, target_uuid)

-- Permission in model:
-- tail (owner_uuid)
-- permission level
-- head (or uuid of new project or group)

-- Adding a permission
-- with recursive search from head, get affected nodes
-- next, select (users, permission) with target_uuid = tail (or owner_uuid)
-- insert \forall users \forall nodes (user, node, least(user perm, node perm))
--   on conflict update set greatest(existing perm, new perm)

-- Removing a permission
-- with recursive search from head, get affected nodes
-- delete all permissions with target_uuid in affected nodes
-- select permission links where
--   head is in the affected nodes
--   tail is in computed permissions
-- re-add each link (via adding a permission, above)


begin;
create TEMPORARY table affected_perms on commit drop
as WITH RECURSIVE perm_value(name, val)  AS (
         VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)
        ),
        edges(tail_uuid, head_uuid, val) as (
	  select groups.owner_uuid, groups.uuid, (3) from groups
	  union
	  select links.tail_uuid, links.head_uuid, pv.val from links
	  JOIN perm_value pv ON ((pv.name = (links.name)::text))
	   where links.link_class='permission' and links.head_uuid not like '%-tpzed-%'
	),
	project_subtree(target_uuid, val) as (
--	values ('x1u39-j7d0g-rufsjii9txtu2xg')
--	values ('x1u39-tpzed-fr97h9t4m5jffxs')
--	values ('x1u39-j7d0g-qjt4uv6kq0ntm3y', 3)
	values ('x1u39-tpzed-3kz0nwtjehhl0u4', 3)
	union
	(select edges.head_uuid, least(edges.val, project_subtree.val) from edges join project_subtree
	on (edges.tail_uuid = project_subtree.target_uuid)))
	select target_uuid, val from project_subtree;

	select * from affected_perms;

	insert into computed_permissions select 'x1u39-tpzed-3kz0nwtjehhl0u4', target_uuid, val from affected_perms;

	delete from computed_permissions
	where exists
	(select 1 from affected_perms where
	 computed_permissions.user_uuid='x1u39-tpzed-3kz0nwtjehhl0u4'
	 and computed_permissions.target_uuid=affected_perms.target_uuid
	 and computed_permissions.val=affected_perms.val);

	create TEMPORARY table links_to_recompute on commit drop
	as WITH perm_value(name, val)  AS (
         VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)
        )
	select links.tail_uuid, links.head_uuid, pv.val from links
	  JOIN affected_perms on (links.head_uuid = affected_perms.target_uuid)
	  JOIN perm_value pv ON ((pv.name = (links.name)::text))
	  where links.link_class='permission';

	select * from links_to_recompute;

commit;

create or replace function project_subtree (starting_uuid varchar(27))
returns table (target_uuid varchar(27))
STABLE
language SQL
as $$
WITH RECURSIVE
	project_subtree(uuid) as (
	values (starting_uuid)
	union
	select groups.uuid from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
	)
	select uuid from project_subtree;
$$;


create or replace function search_permission_graph (starting_uuid varchar(27), starting_perm smallint)
returns table (target_uuid varchar(27), val smallint)
STABLE
language SQL
as $$
WITH RECURSIVE perm_value(name, val)  AS (
         VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)
        ),
        edges(tail_uuid, head_uuid, val) as (
	  select groups.owner_uuid, groups.uuid, (3) from groups
	  union
	  select links.tail_uuid, links.head_uuid, pv.val from links
	  JOIN perm_value pv ON ((pv.name = (links.name)::text))
	   where links.link_class='permission'
	),
	project_subtree(target_uuid, val) as (
	values (starting_uuid, starting_perm)
	union
	(select edges.head_uuid, least(edges.val, project_subtree.val)::smallint from edges join project_subtree
	on (edges.tail_uuid = project_subtree.target_uuid)
	where edges.tail_uuid like '_____-j7d0g-_______________'
	     or edges.tail_uuid = starting_uuid))
	select target_uuid, max(val) from project_subtree
	group by (target_uuid) ;
$$;

create or replace function add_permission (tail_uuid varchar(27), head_uuid varchar(27), perm_value smallint)
returns table(user_uuid character varying (27), target_uuid character varying (27), val smallint)
VOLATILE
language SQL
as $$
with
  affected_nodes(target_uuid, val)
    as (select target_uuid, val from search_permission_graph(head_uuid, perm_value)),
  affected_users(user_uuid, val)
    as (select user_uuid, perm_level from computed_permissions where target_uuid = tail_uuid),
  new_perms(user_uuid, target_uuid, val)
    as (select affected_users.user_uuid, affected_nodes.target_uuid, least(affected_nodes.val, affected_users.val)
      from affected_nodes cross join affected_users)
  insert into computed_permissions (user_uuid, target_uuid, perm_level)
    (select user_uuid, target_uuid, val from new_perms)
    on conflict (user_uuid, target_uuid) do update set perm_level=greatest(computed_permissions.perm_level, excluded.perm_level)
    returning *;
$$;

create or replace function clear_permission (head_uuid varchar(27))
returns table(user_uuid character varying (27), target_uuid character varying (27), val smallint)
VOLATILE
language SQL
as $$
with
  affected_nodes(target_uuid)
    as (select target_uuid from search_permission_graph(head_uuid, 0::smallint))
  delete from computed_permissions where
    target_uuid in (select target_uuid from affected_nodes where target_uuid <> user_uuid)
    returning *;
$$;


create or replace function remove_permission_helper (starting_uuid varchar(27))
returns table(link_uuid character varying (27), tail_uuid character varying (27), head_uuid character varying (27), val smallint)
VOLATILE
language SQL
as $$
with perm_value(name, val)  AS (
    VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)),
  removed_rows(user_uuid, target_uuid, val) as (
    select user_uuid, target_uuid, val from clear_permission(starting_uuid))
  select links.uuid, links.tail_uuid, links.head_uuid, pv.val::smallint from links
      JOIN perm_value pv ON ((pv.name = (links.name)::text))
      where links.link_class='permission' and
        exists (select 1 from computed_permissions where
	  links.tail_uuid=computed_permissions.target_uuid) and
        exists (select 1 from removed_rows where links.head_uuid=removed_rows.target_uuid)
$$;

create or replace function delete_permission_link (link_uuid varchar(27), link_head_uuid varchar(27))
returns table(tail_uuid character varying (27), head_uuid character varying (27), val smallint)
VOLATILE
language SQL
as $$
with links_to_recompute(link_uuid, tail_uuid, head_uuid, val) as
    (select * from remove_permission_helper(link_head_uuid)),
  _delete_link as (delete from links where links.uuid=link_uuid)
  select ap.user_uuid, ap.target_uuid, ap.val
    from links_to_recompute,
      lateral add_permission(links_to_recompute.tail_uuid, links_to_recompute.head_uuid, links_to_recompute.val::smallint) ap
    where links_to_recompute.link_uuid <> link_uuid
$$;

create or replace function delete_group (group_uuid varchar(27))
returns table(tail_uuid character varying (27), head_uuid character varying (27), val smallint)
VOLATILE
language SQL
as $$
with links_to_recompute(link_uuid, tail_uuid, head_uuid, val) as
    (select * from remove_permission_helper(group_uuid)),
  _delete_group as (delete from groups where groups.uuid=group_uuid)
  select ap.user_uuid, ap.target_uuid, ap.val
    from links_to_recompute,
      lateral add_permission(links_to_recompute.tail_uuid, links_to_recompute.head_uuid, links_to_recompute.val::smallint) ap
$$;

create or replace function move_group (group_uuid varchar(27), new_owner_uuid varchar(27))
returns table(tail_uuid character varying (27), head_uuid character varying (27), val smallint)
VOLATILE
language SQL
as $$
with links_to_recompute(link_uuid, tail_uuid, head_uuid, val) as
    (values ('', new_owner_uuid, group_uuid, 3::smallint)
    union
    select link_uuid, tail_uuid, head_uuid, val from remove_permission_helper(group_uuid)),
  update_group as (update groups set owner_uuid=new_owner_uuid where groups.uuid=group_uuid)
  select ap.user_uuid, ap.target_uuid, ap.val
    from links_to_recompute,
      lateral add_permission(links_to_recompute.tail_uuid, links_to_recompute.head_uuid, links_to_recompute.val::smallint) ap
$$;



insert into computed_permissions (user_uuid, target_uuid, perm_level) values ('x1u39-tpzed-3kz0nwtjehhl0u4', 'x1u39-tpzed-3kz0nwtjehhl0u4', 3);

select * from add_permission('x1u39-tpzed-3kz0nwtjehhl0u4', 'x1u39-tpzed-3kz0nwtjehhl0u4', 3::smallint);

-- x1u39-j7d0g-sh5mwu9t8b71al6 -> Project under old user (owned by x1u39-j7d0g-7mj64tj4fe3snmo)
-- x1u39-j7d0g-qjt4uv6kq0ntm3y -> Foghord (can_write x1u39-j7d0g-7mj64tj4fe3snmo) (can_read x1u39-tpzed-3kz0nwtjehhl0u4)
-- x1u39-j7d0g-sij00wivb8v3hkt -> Thinger holder (owned by x1u39-tpzed-3kz0nwtjehhl0u4)
-- x1u39-j7d0g-7mj64tj4fe3snmo -> Data from old user
-- x1u39-tpzed-3kz0nwtjehhl0u4 -> c c (can_manage x1u39-j7d0g-qjt4uv6kq0ntm3y)
