-- Hosting always belongs to an account. The browser cannot choose the host name.
create or replace function public.create_map_room(p_dataset uuid,p_difficulty text,p_name text,p_limit integer,p_access_hash text,p_owner uuid)
returns uuid language plpgsql security invoker set search_path='' as $$
declare rid uuid; revision uuid; pid uuid:=gen_random_uuid(); rev public.revisions; vp jsonb; n integer:=0; account_name text;
begin
  select display_name into account_name from public.profiles where id=p_owner;
  if not found then raise exception 'Log in to create a multiplayer room'; end if;
  if p_limit is null or p_limit not between 2 and 16 then raise exception 'Choose between 2 and 16 players'; end if;
  if p_difficulty is null or p_difficulty not in ('easy','normal','hard') then raise exception 'Invalid difficulty'; end if;
  revision:=public.playable_map_revision(p_dataset,p_owner);
  select * into rev from public.revisions where id=revision;
  if not found then raise exception 'Map unavailable'; end if;
  if coalesce(jsonb_array_length(rev.manifest->'viewpoints'),0)<5 then raise exception 'Map has fewer than five viewpoints'; end if;
  insert into public.multiplayer_rooms(revision_id,host_player_id,difficulty,player_limit) values(rev.id,pid,p_difficulty,p_limit) returning id into rid;
  insert into public.multiplayer_players(id,room_id,access_hash,display_name,account_id) values(pid,rid,p_access_hash,account_name,p_owner);
  for vp in select value from jsonb_array_elements(rev.manifest->'viewpoints') order by random() limit 5 loop
    insert into public.multiplayer_rounds(room_id,round_number,viewpoint) values(rid,n,vp); n:=n+1;
  end loop;
  return rid;
end; $$;

-- Link signed-in joins within the room lock, before a concurrent host can leave.
create function public.join_multiplayer_room(p_room uuid,p_access_hash text,p_owner uuid,p_name text)
returns jsonb language plpgsql security invoker set search_path='' as $$
declare account_name text; result jsonb;
begin
  perform 1 from public.multiplayer_rooms where id=p_room for update;
  if p_owner is not null then
    select display_name into account_name from public.profiles where id=p_owner;
    if not found then raise exception 'Account not found'; end if;
  else account_name:=p_name;
  end if;
  result:=public.multiplayer_action(p_room,p_access_hash,'join',jsonb_build_object('name',account_name));
  if p_owner is not null then
    update public.multiplayer_players set account_id=p_owner
      where room_id=p_room and access_hash=p_access_hash and account_id is null and not has_left;
  end if;
  return result;
end; $$;

-- A departing host can hand the party to another signed-in player, never a guest.
create function public.ensure_account_party_host() returns trigger
language plpgsql security invoker set search_path='' as $$
declare replacement uuid;
begin
  if new.host_player_id is not distinct from old.host_player_id or new.status='closed' then return new; end if;
  if exists(select 1 from public.multiplayer_players p join public.profiles a on a.id=p.account_id
    where p.id=new.host_player_id and p.room_id=new.id and not p.has_left) then return new; end if;
  select p.id into replacement from public.multiplayer_players p join public.profiles a on a.id=p.account_id
    where p.room_id=new.id and not p.has_left order by p.joined_at,p.id limit 1;
  if replacement is null then
    new.status:='closed'; new.transition_at:=null;
  else new.host_player_id:=replacement;
  end if;
  return new;
end; $$;
create trigger ensure_account_party_host before update of host_player_id on public.multiplayer_rooms
  for each row execute function public.ensure_account_party_host();

-- Capture a close even when its status was set by the host-transfer trigger.
create trigger close_transferred_party_discord after update of host_player_id on public.multiplayer_rooms
  for each row when (new.status='closed') execute function public.close_party_discord();

revoke all on function public.join_multiplayer_room(uuid,text,uuid,text),public.ensure_account_party_host() from public,anon,authenticated;
grant execute on function public.join_multiplayer_room(uuid,text,uuid,text),public.ensure_account_party_host() to service_role;