-- Room cookies are verified by the API; browser roles cannot access chat directly.
create table public.multiplayer_messages (
  id bigint generated always as identity primary key,
  room_id uuid not null references public.multiplayer_rooms(id) on delete cascade,
  player_id uuid not null,
  client_id uuid not null,
  display_name text not null,
  body text not null check (length(btrim(body)) between 1 and 500
    and body !~ '[[:cntrl:]]' and body !~ U&'[\0080-\009F\202A-\202E\2066-\2069]'),
  created_at timestamptz not null default clock_timestamp(),
  foreign key(room_id,player_id) references public.multiplayer_players(room_id,id) on delete cascade,
  unique(room_id,player_id,client_id)
);
create index multiplayer_messages_history on public.multiplayer_messages(room_id,id desc);
create index multiplayer_messages_rate on public.multiplayer_messages(room_id,player_id,created_at desc);
alter table public.multiplayer_messages enable row level security;
revoke all on public.multiplayer_messages from public,anon,authenticated;
revoke all on sequence public.multiplayer_messages_id_seq from public,anon,authenticated;
grant all on public.multiplayer_messages to service_role;
grant usage,select on sequence public.multiplayer_messages_id_seq to service_role;

create function public.multiplayer_chat(p_room uuid,p_access_hash text,p_text text default null,p_client_id uuid default null)
returns jsonb language plpgsql security invoker set search_path='' as $$
declare room public.multiplayer_rooms; player public.multiplayer_players;
begin
  -- Match the room action lock order, including kicks/leaves/closure. Duplicate
  -- sends and rate checks are serialized without touching game timers or guesses.
  select * into room from public.multiplayer_rooms where id=p_room for update;
  if not found then raise exception 'Room not found'; end if;
  select * into player from public.multiplayer_players
    where room_id=p_room and access_hash=p_access_hash and not has_left;
  if not found then raise exception 'Chat permission requires joining this room'; end if;
  if room.status='closed' or room.created_at < clock_timestamp()-interval '24 hours' then
    raise exception 'Chat is unavailable in a closed or expired room';
  end if;
  if p_text is not null or p_client_id is not null then
    if p_text is null or p_client_id is null or length(btrim(p_text)) not between 1 and 500
      or p_text ~ '[[:cntrl:]]' or p_text ~ U&'[\0080-\009F\202A-\202E\2066-\2069]' then
      raise exception 'Invalid chat message';
    end if;
    if not exists(select 1 from public.multiplayer_messages
      where room_id=p_room and player_id=player.id and client_id=p_client_id) then
      if exists(select 1 from public.multiplayer_messages where room_id=p_room and player_id=player.id
        and created_at > clock_timestamp()-interval '1 second')
        or (select count(*) from public.multiplayer_messages where room_id=p_room and player_id=player.id
          and created_at > clock_timestamp()-interval '1 minute') >= 30 then
        raise exception 'Too many chat messages. Wait a moment and try again.';
      end if;
      insert into public.multiplayer_messages(room_id,player_id,client_id,display_name,body)
        values(p_room,player.id,p_client_id,player.display_name,btrim(p_text));
    end if;
  end if;
  return jsonb_build_object('messages',coalesce((select jsonb_agg(jsonb_build_object(
    'id',m.id::text,'playerId',m.player_id,'name',m.display_name,'text',m.body,'createdAt',m.created_at
  ) order by m.id) from (select * from public.multiplayer_messages where room_id=p_room order by id desc limit 100) m),'[]'::jsonb));
end; $$;
revoke all on function public.multiplayer_chat(uuid,text,text,uuid) from public,anon,authenticated;
grant execute on function public.multiplayer_chat(uuid,text,text,uuid) to service_role;