FancyWordle/supabase/schema.sql

create extension if not exists pgcrypto;

create table if not exists public.wordle_words (
  position integer primary key,
  word text not null unique check (word ~ '^[a-z]{5}$')
);

create table if not exists public.wordle_dictionary (
  word text primary key check (word ~ '^[a-z]{5}$')
);

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  username text,
  display_name text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table public.profiles
  add column if not exists username text;

create unique index if not exists profiles_username_key
  on public.profiles (lower(username))
  where username is not null;

create table if not exists public.wordle_rounds (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  word text not null check (word ~ '^[a-z]{5}$'),
  hour_start timestamptz not null,
  started_at timestamptz not null default now(),
  next_playable_at timestamptz not null,
  completed_at timestamptz,
  won boolean,
  guess_count integer check (guess_count between 1 and 6),
  created_at timestamptz not null default now()
);

alter table public.wordle_rounds
  add column if not exists hour_start timestamptz,
  add column if not exists next_playable_at timestamptz;

update public.wordle_rounds
set hour_start = date_trunc('hour', started_at)
where hour_start is null;

update public.wordle_rounds
set next_playable_at = hour_start + interval '1 hour'
where next_playable_at is null;

alter table public.wordle_rounds
  alter column hour_start set not null,
  alter column next_playable_at set not null;

create table if not exists public.wordle_guesses (
  id uuid primary key default gen_random_uuid(),
  round_id uuid not null references public.wordle_rounds(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  guess text not null check (guess ~ '^[a-z]{5}$'),
  row_index integer not null check (row_index between 1 and 6),
  states jsonb not null,
  created_at timestamptz not null default now(),
  unique (round_id, row_index)
);

create index if not exists wordle_rounds_user_started_idx
  on public.wordle_rounds (user_id, started_at desc);

create unique index if not exists wordle_rounds_user_hour_idx
  on public.wordle_rounds (user_id, hour_start);

create index if not exists wordle_guesses_round_row_idx
  on public.wordle_guesses (round_id, row_index);

alter table public.wordle_words enable row level security;
alter table public.wordle_dictionary enable row level security;
alter table public.profiles enable row level security;
alter table public.wordle_rounds enable row level security;
alter table public.wordle_guesses enable row level security;

drop policy if exists "Users can read their own profile" on public.profiles;
create policy "Users can read their own profile"
  on public.profiles
  for select
  using (auth.uid() = id);

drop policy if exists "Users can update their own profile" on public.profiles;
create policy "Users can update their own profile"
  on public.profiles
  for update
  using (auth.uid() = id)
  with check (auth.uid() = id);

drop policy if exists "Users can read their own rounds" on public.wordle_rounds;
create policy "Users can read their own rounds"
  on public.wordle_rounds
  for select
  using (auth.uid() = user_id);

drop policy if exists "Users can read their own guesses" on public.wordle_guesses;
create policy "Users can read their own guesses"
  on public.wordle_guesses
  for select
  using (auth.uid() = user_id);

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, username, display_name)
  values (
    new.id,
    nullif(lower(regexp_replace(coalesce(new.raw_user_meta_data->>'username', split_part(new.email, '@', 1)), '[^a-z0-9_]', '', 'g')), ''),
    coalesce(new.raw_user_meta_data->>'display_name', new.raw_user_meta_data->>'username', split_part(new.email, '@', 1))
  )
  on conflict (id) do nothing;

  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

create or replace function public.score_guess(guess text, answer text)
returns jsonb
language plpgsql
immutable
as $$
declare
  states text[] := array['wrong', 'wrong', 'wrong', 'wrong', 'wrong'];
  remaining_letters text := '';
  guess_letter text;
  answer_letter text;
  match_index integer;
  index integer;
begin
  for index in 1..5 loop
    guess_letter := substr(guess, index, 1);
    answer_letter := substr(answer, index, 1);

    if guess_letter = answer_letter then
      states[index] := 'correct';
    else
      remaining_letters := remaining_letters || answer_letter;
    end if;
  end loop;

  for index in 1..5 loop
    if states[index] = 'correct' then
      continue;
    end if;

    guess_letter := substr(guess, index, 1);
    match_index := strpos(remaining_letters, guess_letter);

    if match_index > 0 then
      states[index] := 'wrong-position';
      remaining_letters := overlay(remaining_letters placing '' from match_index for 1);
    end if;
  end loop;

  return to_jsonb(states);
end;
$$;

create or replace function public.get_hourly_word(hour_start timestamptz)
returns text
language plpgsql
stable
security definer
set search_path = public
as $$
declare
  word_count integer;
  word_offset integer;
  hourly_word text;
begin
  select count(*) into word_count from public.wordle_words;

  if word_count = 0 then
    raise exception 'No hourly words are configured';
  end if;

  word_offset := (floor(extract(epoch from hour_start) / 3600)::bigint % word_count)::integer;

  select public.wordle_words.word
    into hourly_word
  from public.wordle_words
  order by position
  limit 1 offset word_offset;

  return hourly_word;
end;
$$;

drop function if exists public.start_hourly_round(text);

create or replace function public.start_hourly_round()
returns table (
  round_id uuid,
  hour_start timestamptz,
  started_at timestamptz,
  next_playable_at timestamptz,
  completed_at timestamptz,
  won boolean,
  guess_count integer,
  server_now timestamptz,
  is_existing boolean,
  revealed_word text,
  guesses jsonb
)
language plpgsql
security definer
set search_path = public
as $$
declare
  existing_round public.wordle_rounds%rowtype;
  new_round public.wordle_rounds%rowtype;
  current_hour timestamptz := date_trunc('hour', now());
  hourly_word text := public.get_hourly_word(date_trunc('hour', now()));
begin
  if auth.uid() is null then
    raise exception 'Authentication required';
  end if;

  select *
    into existing_round
  from public.wordle_rounds
  where user_id = auth.uid()
    and public.wordle_rounds.hour_start = current_hour
  order by started_at desc
  limit 1;

  if found then
    return query select
      existing_round.id,
      existing_round.hour_start,
      existing_round.started_at,
      existing_round.next_playable_at,
      existing_round.completed_at,
      existing_round.won,
      existing_round.guess_count,
      now(),
      true,
      case when existing_round.completed_at is null then null else existing_round.word end,
      coalesce((
        select jsonb_agg(jsonb_build_object(
          'guess', wordle_guesses.guess,
          'rowIndex', wordle_guesses.row_index,
          'states', wordle_guesses.states
        ) order by wordle_guesses.row_index)
        from public.wordle_guesses
        where wordle_guesses.round_id = existing_round.id
      ), '[]'::jsonb);
    return;
  end if;

  insert into public.wordle_rounds (user_id, word, hour_start, next_playable_at)
  values (auth.uid(), hourly_word, current_hour, current_hour + interval '1 hour')
  returning * into new_round;

  return query select
    new_round.id,
    new_round.hour_start,
    new_round.started_at,
    new_round.next_playable_at,
    new_round.completed_at,
    new_round.won,
    new_round.guess_count,
    now(),
    false,
    null::text,
    '[]'::jsonb;
end;
$$;

drop function if exists public.submit_guess(uuid, text);

create or replace function public.submit_guess(target_round_id uuid, submitted_guess text)
returns table (
  guess text,
  row_index integer,
  states jsonb,
  completed boolean,
  won boolean,
  guess_count integer,
  revealed_word text,
  next_playable_at timestamptz,
  server_now timestamptz
)
language plpgsql
security definer
set search_path = public
as $$
declare
  active_round public.wordle_rounds%rowtype;
  normalized_guess text := lower(trim(submitted_guess));
  next_row integer;
  scored_states jsonb;
  did_win boolean;
  did_complete boolean;
begin
  if auth.uid() is null then
    raise exception 'Authentication required';
  end if;

  if normalized_guess !~ '^[a-z]{5}$' then
    raise exception 'Guess must be five letters';
  end if;

  if not exists (select 1 from public.wordle_dictionary where word = normalized_guess) then
    raise exception 'Not in word list';
  end if;

  select *
    into active_round
  from public.wordle_rounds rounds
  where rounds.id = target_round_id
    and rounds.user_id = auth.uid()
  limit 1;

  if not found then
    raise exception 'Round not found';
  end if;

  if active_round.completed_at is not null then
    raise exception 'Round already complete';
  end if;

  select (count(*) + 1)::integer
    into next_row
  from public.wordle_guesses
  where wordle_guesses.round_id = active_round.id;

  if next_row > 6 then
    raise exception 'No guesses remaining';
  end if;

  scored_states := public.score_guess(normalized_guess, active_round.word);
  did_win := normalized_guess = active_round.word;
  did_complete := did_win or next_row = 6;

  insert into public.wordle_guesses (round_id, user_id, guess, row_index, states)
  values (active_round.id, auth.uid(), normalized_guess, next_row, scored_states);

  if did_complete then
    update public.wordle_rounds
    set completed_at = now(),
        won = did_win,
        guess_count = next_row
    where id = active_round.id;
  end if;

  return query select
    normalized_guess,
    next_row,
    scored_states,
    did_complete,
    did_win,
    case when did_complete then next_row else null end,
    case when did_complete then active_round.word else null end,
    active_round.next_playable_at,
    now();
end;
$$;

create or replace function public.get_user_stats()
returns table (
  played integer,
  wins integer,
  current_streak integer,
  max_streak integer,
  distribution integer[]
)
language plpgsql
security definer
set search_path = public
as $$
declare
  round_record record;
  running_streak integer := 0;
  previous_hour timestamptz;
begin
  if auth.uid() is null then
    raise exception 'Authentication required';
  end if;

  played := 0;
  wins := 0;
  current_streak := 0;
  max_streak := 0;
  distribution := array[0, 0, 0, 0, 0, 0];

  for round_record in
    select won, guess_count, hour_start
    from public.wordle_rounds
    where user_id = auth.uid()
      and completed_at is not null
    order by hour_start asc
  loop
    played := played + 1;

    if round_record.won then
      wins := wins + 1;
      running_streak := case
        when running_streak > 0 and previous_hour is not null and round_record.hour_start = previous_hour + interval '1 hour'
          then running_streak + 1
        else 1
      end;
      max_streak := greatest(max_streak, running_streak);

      if round_record.guess_count between 1 and 6 then
        distribution[round_record.guess_count] := distribution[round_record.guess_count] + 1;
      end if;
    else
      running_streak := 0;
    end if;

    previous_hour := round_record.hour_start;
  end loop;

  previous_hour := null;

  for round_record in
    select won, hour_start
    from public.wordle_rounds
    where user_id = auth.uid()
      and completed_at is not null
    order by hour_start desc
  loop
    if not round_record.won then
      exit;
    end if;

    if previous_hour is not null and round_record.hour_start <> previous_hour - interval '1 hour' then
      exit;
    end if;

    current_streak := current_streak + 1;
    previous_hour := round_record.hour_start;
  end loop;

  return next;
end;
$$;

create or replace function public.is_username_available(candidate_username text)
returns boolean
language plpgsql
security definer
set search_path = public
as $$
declare
  normalized_username text := nullif(lower(regexp_replace(trim(candidate_username), '[^a-z0-9_]', '', 'g')), '');
begin
  if normalized_username is null then
    return false;
  end if;

  return not exists (
    select 1
    from public.profiles
    where lower(username) = normalized_username
  );
end;
$$;

create or replace function public.get_player_current_streak(target_user_id uuid)
returns integer
language plpgsql
security definer
set search_path = public
as $$
declare
  round_record record;
  streak integer := 0;
  previous_hour timestamptz;
begin
  for round_record in
    select won, hour_start
    from public.wordle_rounds
    where user_id = target_user_id
      and completed_at is not null
    order by hour_start desc
  loop
    if not round_record.won then
      exit;
    end if;

    if previous_hour is not null and round_record.hour_start <> previous_hour - interval '1 hour' then
      exit;
    end if;

    streak := streak + 1;
    previous_hour := round_record.hour_start;
  end loop;

  return streak;
end;
$$;

create or replace function public.get_hourly_leaderboard()
returns table (
  username text,
  won boolean,
  guess_count integer,
  completed_at timestamptz,
  rank integer
)
language plpgsql
security definer
set search_path = public
as $$
begin
  return query
    select
      coalesce(profiles.username, profiles.display_name, 'player') as username,
      rounds.won,
      rounds.guess_count,
      rounds.completed_at,
      row_number() over (
        order by
          rounds.won desc,
          rounds.guess_count asc nulls last,
          rounds.completed_at asc
      )::integer as rank
    from public.wordle_rounds rounds
    left join public.profiles profiles on profiles.id = rounds.user_id
    where rounds.hour_start = date_trunc('hour', now())
      and rounds.completed_at is not null
    order by rank
    limit 25;
end;
$$;

create or replace function public.get_leaderboard(board_scope text default 'hour')
returns table (
  username text,
  won boolean,
  guess_count integer,
  completed_at timestamptz,
  rank integer,
  current_streak integer,
  is_current_user boolean,
  wins integer,
  played integer,
  average_guesses numeric
)
language plpgsql
security definer
set search_path = public
as $$
declare
  normalized_scope text := lower(coalesce(board_scope, 'hour'));
begin
  if normalized_scope = 'today' then
    return query
      with aggregate_rows as (
        select
          rounds.user_id,
          coalesce(profiles.username, profiles.display_name, 'player') as username,
          count(*)::integer as played,
          count(*) filter (where rounds.won)::integer as wins,
          round(avg(rounds.guess_count) filter (where rounds.won), 2) as average_guesses,
          min(rounds.completed_at) filter (where rounds.won) as first_win_at,
          max(rounds.completed_at) as latest_completed_at
        from public.wordle_rounds rounds
        left join public.profiles profiles on profiles.id = rounds.user_id
        where rounds.completed_at is not null
          and rounds.completed_at >= date_trunc('day', now())
        group by rounds.user_id, profiles.username, profiles.display_name
      ), ranked as (
        select
          aggregate_rows.*,
          row_number() over (
            order by aggregate_rows.wins desc, aggregate_rows.average_guesses asc nulls last, aggregate_rows.first_win_at asc nulls last
          )::integer as row_rank
        from aggregate_rows
      )
      select
        ranked.username,
        ranked.wins > 0,
        null::integer,
        ranked.latest_completed_at,
        ranked.row_rank,
        public.get_player_current_streak(ranked.user_id),
        auth.uid() = ranked.user_id,
        ranked.wins,
        ranked.played,
        ranked.average_guesses
      from ranked
      where ranked.row_rank <= 25 or auth.uid() = ranked.user_id
      order by ranked.row_rank;
    return;
  end if;

  if normalized_scope = 'all' then
    return query
      with aggregate_rows as (
        select
          rounds.user_id,
          coalesce(profiles.username, profiles.display_name, 'player') as username,
          count(*)::integer as played,
          count(*) filter (where rounds.won)::integer as wins,
          round(avg(rounds.guess_count) filter (where rounds.won), 2) as average_guesses,
          min(rounds.completed_at) filter (where rounds.won) as first_win_at,
          max(rounds.completed_at) as latest_completed_at
        from public.wordle_rounds rounds
        left join public.profiles profiles on profiles.id = rounds.user_id
        where rounds.completed_at is not null
        group by rounds.user_id, profiles.username, profiles.display_name
      ), ranked as (
        select
          aggregate_rows.*,
          row_number() over (
            order by aggregate_rows.wins desc, public.get_player_current_streak(aggregate_rows.user_id) desc, aggregate_rows.average_guesses asc nulls last, aggregate_rows.first_win_at asc nulls last
          )::integer as row_rank
        from aggregate_rows
      )
      select
        ranked.username,
        ranked.wins > 0,
        null::integer,
        ranked.latest_completed_at,
        ranked.row_rank,
        public.get_player_current_streak(ranked.user_id),
        auth.uid() = ranked.user_id,
        ranked.wins,
        ranked.played,
        ranked.average_guesses
      from ranked
      where ranked.row_rank <= 25 or auth.uid() = ranked.user_id
      order by ranked.row_rank;
    return;
  end if;

  return query
    with ranked as (
      select
        rounds.user_id,
        coalesce(profiles.username, profiles.display_name, 'player') as username,
        rounds.won,
        rounds.guess_count,
        rounds.completed_at,
        row_number() over (
          order by rounds.won desc, rounds.guess_count asc nulls last, rounds.completed_at asc
        )::integer as row_rank
      from public.wordle_rounds rounds
      left join public.profiles profiles on profiles.id = rounds.user_id
      where rounds.hour_start = date_trunc('hour', now())
        and rounds.completed_at is not null
    )
    select
      ranked.username,
      ranked.won,
      ranked.guess_count,
      ranked.completed_at,
      ranked.row_rank,
      public.get_player_current_streak(ranked.user_id),
      auth.uid() = ranked.user_id,
      case when ranked.won then 1 else 0 end,
      1,
      ranked.guess_count::numeric
    from ranked
    where ranked.row_rank <= 25 or auth.uid() = ranked.user_id
    order by ranked.row_rank;
end;
$$;

drop function if exists public.get_player_history(integer);

create or replace function public.get_hourly_summary()
returns table (
  completed_count integer,
  win_rate integer,
  average_guesses numeric
)
language plpgsql
security definer
set search_path = public
as $$
begin
  return query
    select
      count(*)::integer,
      coalesce(round((count(*) filter (where won)::numeric / nullif(count(*), 0)) * 100), 0)::integer,
      round(avg(guess_count) filter (where won), 2)
    from public.wordle_rounds
    where hour_start = date_trunc('hour', now())
      and completed_at is not null;
end;
$$;

drop function if exists public.get_email_for_username(text);

create or replace function public.complete_hourly_round(
  round_id uuid,
  did_win boolean,
  guess_total integer
)
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
  raise exception 'complete_hourly_round is deprecated; use submit_guess';
end;
$$;

revoke all on public.wordle_words from anon, authenticated;
revoke all on public.wordle_dictionary from anon, authenticated;
revoke all on public.profiles from anon, authenticated;
revoke all on public.wordle_rounds from anon, authenticated;
revoke all on public.wordle_guesses from anon, authenticated;

grant select, update on public.profiles to authenticated;
grant select on public.wordle_rounds to authenticated;
grant select on public.wordle_guesses to authenticated;
grant execute on function public.is_username_available(text) to anon, authenticated;
grant execute on function public.start_hourly_round() to authenticated;
grant execute on function public.submit_guess(uuid, text) to authenticated;
grant execute on function public.get_user_stats() to authenticated;
grant execute on function public.get_hourly_leaderboard() to anon, authenticated;
grant execute on function public.get_leaderboard(text) to anon, authenticated;
grant execute on function public.get_hourly_summary() to anon, authenticated;
grant execute on function public.complete_hourly_round(uuid, boolean, integer) to authenticated;