create extension if not exists pgcrypto;

create table if not exists public.app_users (
  id uuid primary key default gen_random_uuid(),
  username text not null unique,
  password_hash text not null,
  created_at timestamptz not null default now()
);

create table if not exists public.app_sessions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.app_users(id) on delete cascade,
  token_hash text not null unique,
  expires_at timestamptz not null,
  created_at timestamptz not null default now()
);

update public.scores
set created_at = now()
where created_at is null;

alter table public.scores
  alter column id set default gen_random_uuid();

alter table public.scores
  alter column created_at set default now();

alter table public.scores
  alter column created_at set not null;

alter table public.scores
  alter column user_id drop default;

alter table public.scores
  drop constraint if exists scores_user_id_fkey;

alter table public.scores
  add constraint scores_user_id_fkey
  foreign key (user_id) references public.app_users(id) on delete cascade;

create unique index if not exists scores_user_slug_idx
  on public.scores (user_id, slug);

create table if not exists public.score_artifacts (
  id uuid primary key default gen_random_uuid(),
  score_id uuid not null references public.scores(id) on delete cascade,
  user_id uuid not null references public.app_users(id) on delete cascade,
  artifact_type text not null,
  storage_provider text not null,
  object_key text not null,
  content_type text not null,
  size_bytes bigint not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists score_artifacts_score_user_type_idx
  on public.score_artifacts (score_id, user_id, artifact_type);

create index if not exists score_artifacts_user_score_idx
  on public.score_artifacts (user_id, score_id);