game-scoreboard/supabase/migrations/0006_screen_items.sql

-- Game Night Live — things a game can put on the scoreboard: questions, sounds and videos.
-- Run after 0005_cohosts.sql.
--
-- A game has an ordered list of items. The host puts one on screen at a time from the control
-- room; which one is a column on the event, like the timer. A question shows above the
-- standings, a sound plays through the scoreboard's speakers, a video (karaoke) takes over
-- the screen.
--
-- Media is either a link (YouTube, or a file hosted elsewhere) or a file uploaded to the
-- private "game-media" bucket. The scoreboard never reads the bucket itself: its API hands out
-- a short-lived signed URL, and only for the item that is on screen.

create type game_item_kind as enum ('question', 'sound', 'video');

create table game_items (
  id            uuid primary key default gen_random_uuid(),
  game_id       uuid not null references games(id) on delete cascade,
  kind          game_item_kind not null,
  -- The question's text, or the host's name for a sound or video (never sent to the scoreboard).
  title         text not null check (length(title) between 1 and 500),
  -- Secret until the host reveals it.
  answer        text check (length(answer) between 1 and 500),
  media_url     text check (media_url ~ '^https://' and length(media_url) <= 2000),
  -- "{event id}/{random uuid}.{ext}" in the game-media bucket.
  storage_path  text unique check (storage_path ~ '^[0-9a-f-]{36}/[0-9a-f-]{36}\.[a-z0-9]{1,5}$'),
  sort_order    int not null default 0,
  -- Last time it went on screen; "pick a random one" prefers items without it.
  played_at     timestamptz,
  created_at    timestamptz not null default now(),
  constraint game_items_one_source check (media_url is null or storage_path is null),
  constraint game_items_media_needed
    check (kind = 'question' or media_url is not null or storage_path is not null)
);
create index on game_items(game_id, sort_order);

-- What is on the scoreboard right now. Only counts while the item's game is the current game.
alter table events
  add column screen_item_id      uuid references game_items(id) on delete set null,
  -- Changes every time an item is put on screen, so showing the same one again restarts it.
  add column screen_shown_at     timestamptz,
  add column screen_answer_shown boolean not null default false,
  add column screen_paused       boolean not null default false;

-- ---------- RLS ----------
-- Same rule as every other table: the event's admins only, anon has no policies.

alter table game_items enable row level security;

create policy admin_all on game_items for all to authenticated
  using (is_event_admin(game_event(game_id))) with check (is_event_admin(game_event(game_id)));

-- ---------- storage ----------
-- Private bucket, 50 MB per file (the free plan's ceiling).

insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values ('game-media', 'game-media', false, 52428800, array['audio/*', 'video/*', 'image/*'])
on conflict (id) do nothing;

-- The event a file belongs to, from the first folder of its path; null for any other shape.
create or replace function media_event(object_name text) returns uuid
language sql immutable as $$
  select case
    when object_name ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}/'
    then left(object_name, 36)::uuid
  end
$$;

-- Admins upload from the browser with their own session. Still nothing for anon.
create policy game_media_read   on storage.objects for select to authenticated
  using (bucket_id = 'game-media' and is_event_admin(media_event(name)));
create policy game_media_insert on storage.objects for insert to authenticated
  with check (bucket_id = 'game-media' and is_event_admin(media_event(name)));
create policy game_media_delete on storage.objects for delete to authenticated
  using (bucket_id = 'game-media' and is_event_admin(media_event(name)));