create or replace function test_schema() returns void language plpgsql as $$
declare
  usr_id_foo int;
  usr_id_bar int;
  usr_id_baz int;
  comm_id_ask_dnim int;
  comm_id_pics int;
begin
  insert into public.usr
    ( tag      -- public.usr_tag
    , password -- public.hashed_text
    , email    -- public.email
    )
  values
    (usr_tag_of_string('foo'), hash_text('password123'), email_of_string('foo@gmail.com'))
  , (usr_tag_of_string('bar'), hash_text('password243'), email_of_string('bar@gmail.com'))
  , (usr_tag_of_string('baz'), hash_text('hunter2'), email_of_string('baz@gmail.com'))
  ;

  insert into public.community
    ( tag -- public.community_tag
    )
  values
    (community_tag_of_string('ask_dnim'))
  , (community_tag_of_string('pics'));

  insert into public.community_member_role (community, title, description)
  select comm.id, role_.title, role_.description
  from community comm
     , ( values ('owner', 'community owner')
              , ('member', 'community member')
       ) as role_ (title, description)
  ;

  insert into public.community_member_role_scope (role_, scope)
  select role.id, s.scope
    from public.community_member_role as role
       , ( values (authz_scope_of_string('threads.read'))
                , (authz_scope_of_string('threads.create'))
                , (authz_scope_of_string('threads.update'))
                , (authz_scope_of_string('threads.delete'))
                , (authz_scope_of_string('members.read'))
                , (authz_scope_of_string('members.create'))
                , (authz_scope_of_string('members.delete'))
                , (authz_scope_of_string('roles.read'))
                , (authz_scope_of_string('roles.create'))
                , (authz_scope_of_string('roles.update'))
                , (authz_scope_of_string('roles.delete'))
                , (authz_scope_of_string('update'))
                , (authz_scope_of_string('delete'))
         ) as s (scope)
    where role.title = 'owner';

  insert into public.community_member_role_scope (role_, scope)
  select role.id, s.scope
    from public.community_member_role as role
       , ( values (authz_scope_of_string('threads.read'))
                , (authz_scope_of_string('threads.create'))
                , (authz_scope_of_string('members.read'))
         ) as s (scope)
    where role.title = 'member';

  insert into public.community_member (community, usr, role_)
  select comm.id, u.id, role_.id
  from community comm
  inner join usr u on u.tag = usr_tag_of_string('foo')
  inner join community_member_role role_ on role_.community = comm.id and role_.title = 'owner'
  where comm.tag = community_tag_of_string('ask_dnim');

  insert into public.community_member (community, usr, role_)
  select comm.id, u.id, role_.id
  from community comm
  inner join usr u on u.tag = usr_tag_of_string('bar')
  inner join community_member_role role_ on role_.community = comm.id and role_.title = 'member'
  where comm.tag = community_tag_of_string('ask_dnim');

  insert into public.community_member (community, usr, role_)
  select comm.id, u.id, role_.id
  from community comm
  inner join usr u on u.tag = usr_tag_of_string('baz')
  inner join community_member_role role_ on role_.community = comm.id and role_.title = 'member'
  where comm.tag = community_tag_of_string('ask_dnim');
end;
$$;

select test_schema();
drop function test_schema;