-- Game Night Live — team buzzers.
-- Each team gets its own buzzer link; a game opts in with buzzer_enabled.
-- Run after 0002_timer.sql.
--
-- A buzz is one row per (game, team). The database's clock stamps it, so the order never
-- depends on a phone's clock or on which server instance handled the request.
-- Clearing the buzzers for the next question = deleting the game's rows.

alter table games
  add column buzzer_enabled boolean not null default false;

-- new_token() is volatile, so every existing team gets its own token here.
alter table teams
  add column buzzer_token text not null unique default new_token();

create table buzzes (
  id         uuid primary key default gen_random_uuid(),
  game_id    uuid not null references games(id) on delete cascade,
  team_id    uuid not null references teams(id) on delete cascade,
  -- clock_timestamp(), not now(): the real moment of the insert, not the transaction's start.
  buzzed_at  timestamptz not null default clock_timestamp(),
  constraint buzzes_one_per_team unique (game_id, team_id)
);

-- the team must be in the same event as the game
create or replace function validate_buzz() returns trigger
language plpgsql as $$
begin
  if (select event_id from teams where id = new.team_id)
     is distinct from (select event_id from games where id = new.game_id) then
    raise exception 'team does not belong to the game''s event';
  end if;
  return new;
end $$;

create trigger buzzes_validate
before insert or update on buzzes
for each row execute function validate_buzz();

-- Same rule as every other table: owner only, anon has no policies.
alter table buzzes enable row level security;

create policy owner_all on buzzes for all to authenticated
  using (is_event_owner(game_event(game_id))) with check (is_event_owner(game_event(game_id)));