-- ============================================
-- PastPaper Master — Decouple course library papers from auth users
-- Version: 004
-- Date: 2026-03-24
-- ============================================

-- Course-library papers should not depend on a concrete auth.users row.
-- User-uploaded papers still keep user_id populated.
ALTER TABLE papers
  ALTER COLUMN user_id DROP NOT NULL;

-- Keep existing FK so user-owned papers can still reference auth.users,
-- while course-library rows simply use NULL.

-- Tighten the intended invariant with a check constraint:
-- - user_upload rows must have user_id
-- - course_library rows must not have user_id
ALTER TABLE papers
  DROP CONSTRAINT IF EXISTS papers_source_kind_user_id_check;

ALTER TABLE papers
  ADD CONSTRAINT papers_source_kind_user_id_check
  CHECK (
    (source_kind = 'user_upload' AND user_id IS NOT NULL)
    OR
    (source_kind = 'course_library' AND user_id IS NULL)
  );

-- Existing RLS policies continue to apply to user-owned rows.
-- Course-library rows are accessed through the backend service role.