-- Migration: 00013_users_protected_fields_access_waitlist
-- Description: Extend users protections to cover access_status/waitlist/referral fields
-- Date: 2025-12-21

-- Replace normalize_and_enforce_users() to include new server-controlled fields.
CREATE OR REPLACE FUNCTION normalize_and_enforce_users()
RETURNS TRIGGER AS $$
BEGIN
  -- Allow privileged/admin contexts (no JWT claims) and service role.
  IF auth.role() IS NULL OR auth.role() = 'service_role' THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'INSERT' THEN
    -- Force server-controlled fields to safe defaults on client insert.
    NEW.email_verified := FALSE;
    NEW.phone_verified := FALSE;
    NEW.id_verified := FALSE;
    NEW.id_verification_status := 'not_started';
    NEW.id_verified_at := NULL;

    NEW.subscription_tier := 'free';
    NEW.subscription_expires_at := NULL;
    NEW.stripe_customer_id := NULL;

    NEW.is_banned := FALSE;
    NEW.ban_reason := NULL;
    NEW.banned_at := NULL;

    -- Access/waitlist/referrals are server-controlled.
    NEW.access_status := 'onboarding';
    NEW.waitlisted_at := NULL;
    NEW.activated_at := NULL;
    NEW.referred_by_user_id := NULL;
    NEW.referral_count_verified := 0;
    NEW.priority_score := 0;
    -- referral_code is set by trigger; allow null here.

    RETURN NEW;
  END IF;

  -- Enforce that the profile email always matches the authenticated email.
  IF NEW.email IS DISTINCT FROM OLD.email AND NEW.email IS DISTINCT FROM (auth.jwt() ->> 'email') THEN
    RAISE EXCEPTION 'users.email must match authenticated email';
  END IF;

  -- Prevent updates to protected fields from non-service-role clients.
  IF NEW.email_verified IS DISTINCT FROM OLD.email_verified
    OR NEW.phone_verified IS DISTINCT FROM OLD.phone_verified
    OR NEW.id_verified IS DISTINCT FROM OLD.id_verified
    OR NEW.id_verification_status IS DISTINCT FROM OLD.id_verification_status
    OR NEW.id_verified_at IS DISTINCT FROM OLD.id_verified_at
    OR NEW.subscription_tier IS DISTINCT FROM OLD.subscription_tier
    OR NEW.subscription_expires_at IS DISTINCT FROM OLD.subscription_expires_at
    OR NEW.stripe_customer_id IS DISTINCT FROM OLD.stripe_customer_id
    OR NEW.is_banned IS DISTINCT FROM OLD.is_banned
    OR NEW.ban_reason IS DISTINCT FROM OLD.ban_reason
    OR NEW.banned_at IS DISTINCT FROM OLD.banned_at
    OR NEW.access_status IS DISTINCT FROM OLD.access_status
    OR NEW.waitlisted_at IS DISTINCT FROM OLD.waitlisted_at
    OR NEW.activated_at IS DISTINCT FROM OLD.activated_at
    OR NEW.referral_code IS DISTINCT FROM OLD.referral_code
    OR NEW.referred_by_user_id IS DISTINCT FROM OLD.referred_by_user_id
    OR NEW.referral_count_verified IS DISTINCT FROM OLD.referral_count_verified
    OR NEW.priority_score IS DISTINCT FROM OLD.priority_score
  THEN
    RAISE EXCEPTION 'Not allowed to update protected user fields';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;


