db/schema/001_users.sql

-- 001_users.sql
-- The `users` table that mirrors auth.users and stores the display name
-- used by the leaderboard and the certificate PDF. The original schema
-- is created by Supabase auth; this migration only adds the columns
-- the backend reads (xp, completed_trainings).

BEGIN;

CREATE TABLE IF NOT EXISTS public.users (
  id         uuid PRIMARY KEY,                    -- mirrors auth.users.id
  email      text UNIQUE,
  name       text NOT NULL DEFAULT '',
  xp         integer NOT NULL DEFAULT 0,
  completed_trainings integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_users_xp ON public.users (xp DESC);

ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Open read users" ON public.users;
CREATE POLICY "Open read users" ON public.users
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "Service role upsert users" ON public.users;
CREATE POLICY "Service role upsert users" ON public.users
  FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "Service role update users" ON public.users;
CREATE POLICY "Service role update users" ON public.users
  FOR UPDATE USING (true);

-- Public leaderboard view used by /api/leaderboard.
DROP VIEW IF EXISTS public.leaderboard;
CREATE VIEW public.leaderboard AS
  SELECT id, name, xp, completed_trainings
    FROM public.users
   ORDER BY xp DESC;

GRANT SELECT ON public.leaderboard TO anon, authenticated;

COMMIT;