-- HollowCade cloud schema (reference dump).
-- Live migrations: supabase/migrations/*.sql (applied to project).

create table if not exists public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  username text,
  display_name text not null default 'Player',
  avatar_id text not null default 'default',
  bio text,
  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 unique index if not exists profiles_username_key
  on public.profiles (username)
  where username is not null;

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)
);

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();

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;

-- Policies are created in migrations; see 001_profiles.sql + 002_oauth_complete.sql

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