-- HollowCade OAuth completion (applied via Supabase MCP / SQL editor)
-- Adds username, improves signup trigger, leaderboard RLS, indexes, saves bucket.

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

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

update public.profiles
set username = 'user' || substr(replace(id::text, '-', ''), 1, 8)
where username is null or username = '';

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  uname text;
  dname text;
begin
  uname := coalesce(
    new.raw_user_meta_data->>'preferred_username',
    new.raw_user_meta_data->>'user_name',
    new.raw_user_meta_data->>'full_name',
    split_part(new.email, '@', 1),
    'player'
  );
  uname := lower(regexp_replace(uname, '[^a-z0-9_]+', '', 'g'));
  if length(uname) < 3 then
    uname := 'user' || substr(replace(new.id::text, '-', ''), 1, 8);
  end if;
  while exists (select 1 from public.profiles where username = uname) loop
    uname := uname || floor(random() * 10)::text;
  end loop;

  dname := coalesce(
    new.raw_user_meta_data->>'full_name',
    new.raw_user_meta_data->>'name',
    uname
  );

  insert into public.profiles (id, username, display_name)
  values (new.id, uname, dname)
  on conflict (id) do update set
    username = coalesce(public.profiles.username, excluded.username),
    display_name = case
      when public.profiles.display_name is null or public.profiles.display_name = ''
        then excluded.display_name
      else public.profiles.display_name
    end;
  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();

drop policy if exists "leaderboard_read_opted_in" on public.leaderboard_entries;
drop policy if exists "leaderboard_read_opted" on public.leaderboard_entries;
create policy "leaderboard_read_opted" on public.leaderboard_entries
  for select using (
    auth.uid() = user_id
    or exists (
      select 1 from public.profiles p
      where p.id = leaderboard_entries.user_id
        and p.competitive_opt_in = true
        and p.competitive_banned = false
    )
  );

create index if not exists play_history_user_id_idx on public.play_history (user_id);
create index if not exists favorites_user_id_idx on public.favorites (user_id);
create index if not exists achievements_user_id_idx on public.achievements (user_id);
create index if not exists leaderboard_entries_game_kind_idx
  on public.leaderboard_entries (game_slug, kind, value desc);

insert into storage.buckets (id, name, public)
values ('saves', 'saves', false)
on conflict (id) do nothing;

drop policy if exists "saves_own_read" on storage.objects;
drop policy if exists "saves_own_write" on storage.objects;
drop policy if exists "saves_own_update" on storage.objects;
drop policy if exists "saves_own_delete" on storage.objects;

create policy "saves_own_read" on storage.objects
  for select using (
    bucket_id = 'saves'
    and auth.uid()::text = (storage.foldername(name))[1]
  );
create policy "saves_own_write" on storage.objects
  for insert with check (
    bucket_id = 'saves'
    and auth.uid()::text = (storage.foldername(name))[1]
  );
create policy "saves_own_update" on storage.objects
  for update using (
    bucket_id = 'saves'
    and auth.uid()::text = (storage.foldername(name))[1]
  );
create policy "saves_own_delete" on storage.objects
  for delete using (
    bucket_id = 'saves'
    and auth.uid()::text = (storage.foldername(name))[1]
  );
