-- HollowCade cloud profiles (run in Supabase SQL editor)

create table if not exists public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  display_name text not null default '',
  avatar_id text not null default 'default',
  bio text default '',
  preferred_ui text not null default 'classic',
  needs_ui_setup boolean not null default true,
  competitive_opt_in boolean not null default false,
  competitive_opted_at timestamptz,
  competitive_banned boolean not null default false,
  role text not null default 'user' check (role in ('user', 'admin')),
  settings jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.play_history (
  user_id uuid not null references public.profiles (id) on delete cascade,
  game_slug text not null,
  sessions int not null default 0,
  total_seconds int not null default 0,
  last_played_at timestamptz,
  primary key (user_id, game_slug)
);

create table if not exists public.favorites (
  user_id uuid not null references public.profiles (id) on delete cascade,
  game_slug text not null,
  favorited_at timestamptz not null default now(),
  primary key (user_id, game_slug)
);

create table if not exists public.achievements (
  user_id uuid not null references public.profiles (id) on delete cascade,
  achievement_id text not null,
  unlocked_at timestamptz not null default now(),
  primary key (user_id, achievement_id)
);

create table if not exists public.leaderboard_entries (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles (id) on delete cascade,
  display_name text not null,
  game_slug text not null,
  kind text not null check (kind in ('score', 'play_time', 'achievements')),
  value numeric not null default 0,
  updated_at timestamptz not null default now(),
  unique (user_id, game_slug, kind)
);

alter table public.profiles enable row level security;
alter table public.play_history enable row level security;
alter table public.favorites enable row level security;
alter table public.achievements enable row level security;
alter table public.leaderboard_entries enable row level security;

create policy "profiles_select_own"
  on public.profiles for select using (auth.uid() = id);
create policy "profiles_update_own"
  on public.profiles for update using (auth.uid() = id);
create policy "profiles_insert_own"
  on public.profiles for insert with check (auth.uid() = id);

create policy "play_history_own"
  on public.play_history for all using (auth.uid() = user_id) with check (auth.uid() = user_id);
create policy "favorites_own"
  on public.favorites for all using (auth.uid() = user_id) with check (auth.uid() = user_id);
create policy "achievements_own"
  on public.achievements for all using (auth.uid() = user_id) with check (auth.uid() = user_id);

create policy "leaderboard_read_opted_in"
  on public.leaderboard_entries for select
  using (
    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 policy "leaderboard_write_own"
  on public.leaderboard_entries for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Auto-create profile row on signup
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, display_name, avatar_id)
  values (
    new.id,
    coalesce(new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'name', split_part(new.email, '@', 1), 'Player'),
    'default'
  )
  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();

-- Storage bucket for emulator saves (create via dashboard or:)
-- insert into storage.buckets (id, name, public) values ('saves', 'saves', false);
