db/schema/008_certificates.sql

-- Migration 011: Certificates — real eligibility + rich payload
-- The existing `certificates` table is:
--   id, user_id, category, issue_date, verify_code, details
-- This migration:
--   1. Adds the columns the new PDF flow needs (user_name, title).
--   2. Ensures the (user_id, category) unique index exists so the
--      issue endpoint can do an idempotent check without racing.
--   3. Enables RLS with permissive (anon-readable) policies matching
--      the rest of the public pool tables.
--
-- The certificate is issued when the trainee has completed at least
-- 50 challenges in the same category, AND no certificate exists yet
-- for that (user, category) pair. The 50-threshold check lives in
-- the backend (main.py), enforced against `user_completions`.

BEGIN;

-- 1) Add columns the new flow needs (no-op if they already exist).
DO $$
BEGIN
  BEGIN
    ALTER TABLE public.certificates ADD COLUMN IF NOT EXISTS user_name text NOT NULL DEFAULT '';
  EXCEPTION WHEN duplicate_column THEN NULL; END;
  BEGIN
    ALTER TABLE public.certificates ADD COLUMN IF NOT EXISTS title text NOT NULL DEFAULT '';
  EXCEPTION WHEN duplicate_column THEN NULL; END;
END $$;

-- 2) Idempotency index — a user can only ever have ONE cert per category.
CREATE UNIQUE INDEX IF NOT EXISTS idx_certificates_user_category
  ON public.certificates (user_id, category);

CREATE UNIQUE INDEX IF NOT EXISTS idx_certificates_verify_code
  ON public.certificates (verify_code);

-- 3) RLS — open read for anon, anon write allowed (backend is the gatekeeper
--    via the anon key + the 50-completion eligibility check).
ALTER TABLE public.certificates ENABLE ROW LEVEL SECURITY;

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

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

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

COMMIT;