schema.sql

-- ==============================================================================
-- INNOVISION 2026 · COMPLETE FRESH START DATABASE SCHEMA
-- Drops all existing tables, triggers, and functions, then recreates clean schema
-- ==============================================================================

-- 0. CLEAN DROP OF EXISTING TABLES, TRIGGERS & FUNCTIONS (OPTIONAL FRESH START)
-- Uncomment the block below if you want to wipe everything and restart completely:
-- DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
-- DROP FUNCTION IF EXISTS public.handle_new_user() CASCADE;
-- DROP FUNCTION IF EXISTS public.is_admin(UUID) CASCADE;
-- DROP FUNCTION IF EXISTS public.is_staff(UUID) CASCADE;
-- DROP FUNCTION IF EXISTS public.get_user_role(UUID) CASCADE;
-- DROP FUNCTION IF EXISTS public.protect_profile_role() CASCADE;

-- DROP TABLE IF EXISTS public.user_sessions CASCADE;
-- DROP TABLE IF EXISTS public.registrations CASCADE;
-- DROP TABLE IF EXISTS public.profiles CASCADE;
-- DROP TABLE IF EXISTS public.events CASCADE;
-- DROP TABLE IF EXISTS public.tickets CASCADE;

-- id_card_url belongs to an earlier college ID card upload (public URLs). ID cards are now private files in the
-- 'id-cards' bucket (section 19, id_card_path). To delete the old column (and any stored ID-card URLs) from a database
-- created with an earlier version of this script, uncomment:
-- ALTER TABLE public.registrations DROP COLUMN IF EXISTS id_card_url;


-- 1. CREATE PROFILES TABLE (Safe IF NOT EXISTS)
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  phone TEXT,
  student_type TEXT DEFAULT 'external' CHECK (student_type IN ('internal', 'external')),
  role TEXT DEFAULT 'user' CHECK (role IN ('user', 'it-team', 'registration-team', 'viewer', 'admin')),
  enrollment_no TEXT,
  created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_profiles_email ON public.profiles(email);
CREATE INDEX IF NOT EXISTS idx_profiles_role ON public.profiles(role);


-- 2. CREATE REGISTRATIONS TABLE (Safe IF NOT EXISTS)
CREATE TABLE IF NOT EXISTS public.registrations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  registration_id TEXT UNIQUE NOT NULL,
  user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  college TEXT NOT NULL,
  phone TEXT NOT NULL,
  enrollment_no TEXT,
  student_type TEXT NOT NULL CHECK (student_type IN ('internal', 'external')),
  gender TEXT NOT NULL, -- 'male' | 'female' | 'others' (see registrations_gender_check)
  payment_proof_path TEXT, -- object path in the private 'payment-proofs' Storage bucket ('<user_id>/<uuid>.<ext>')
  utr TEXT,
  amount NUMERIC DEFAULT 0,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'rejected')),
  reviewed_by UUID REFERENCES public.profiles(id),
  reviewed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Upgrade tables created by earlier versions of this script.
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS gender TEXT;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS payment_proof_path TEXT;
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'registrations' AND column_name = 'id_card_url'
  ) THEN
    ALTER TABLE public.registrations ALTER COLUMN id_card_url DROP NOT NULL;
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_registrations_user_id ON public.registrations(user_id);
CREATE INDEX IF NOT EXISTS idx_registrations_status ON public.registrations(status);
CREATE INDEX IF NOT EXISTS idx_registrations_reg_id ON public.registrations(registration_id);
-- GET /api/register looks a user up by id OR email on every poll.
CREATE INDEX IF NOT EXISTS idx_registrations_email ON public.registrations(email);
-- The admin list reads in (created_at, id) order, a page at a time.
CREATE INDEX IF NOT EXISTS idx_registrations_created_at_id ON public.registrations(created_at, id);

-- The old ITER - SOA college CHECK: trg_refuse_iter_soa_registration (section 18) refuses them now, without blocking
-- staff from reviewing a registration made before the ban.
ALTER TABLE IF EXISTS public.registrations DROP CONSTRAINT IF EXISTS registrations_no_iter_soa;


-- Field validation in the database itself, so it also applies to direct Supabase API writes that skip /api/register.
-- NOT VALID: enforced for new/updated rows without failing on legacy data.
DO $$
BEGIN
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_id_card_url_check;
  -- NULL is tolerated only on legacy rows created before the column existed (so staff can still review them);
  -- trg_require_registration_gender makes it mandatory for every new registration.
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_gender_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_gender_check
    CHECK (gender IS NULL OR gender IN ('male', 'female', 'others'));
  -- A payment proof is a path inside the registrant's OWN folder of the private bucket, never a URL, and never
  -- another user's file (even through the service role).
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_payment_proof_path_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_payment_proof_path_check
    CHECK (payment_proof_path IS NULL OR (
      payment_proof_path ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}/[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\.(jpg|png|webp|gif|heic)$'
      AND split_part(payment_proof_path, '/', 1) = user_id::text
    ));
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_name_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_name_check
    CHECK (char_length(name) BETWEEN 1 AND 100) NOT VALID;
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_college_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_college_check
    CHECK (char_length(college) BETWEEN 1 AND 200) NOT VALID;
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_phone_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_phone_check
    CHECK (phone ~ '^[6-9][0-9]{9}$') NOT VALID;
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_utr_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_utr_check
    CHECK (utr IS NULL OR utr ~ '^[0-9]{12}$') NOT VALID;
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_enrollment_no_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_enrollment_no_check
    CHECK (enrollment_no IS NULL OR char_length(enrollment_no) <= 50) NOT VALID;
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_registration_id_format_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_registration_id_format_check
    CHECK (registration_id ~ '^IV26-[0-9]{4}$') NOT VALID;

  -- Profiles created before the 'registration-team' role existed still carry the old role CHECK (CREATE TABLE IF NOT
  -- EXISTS never updates it), which would refuse every assignment of the new role. Same list as the table definition.
  ALTER TABLE public.profiles DROP CONSTRAINT IF EXISTS profiles_role_check;
  ALTER TABLE public.profiles ADD CONSTRAINT profiles_role_check
    CHECK (role IN ('user', 'it-team', 'registration-team', 'viewer', 'admin'));
  ALTER TABLE public.profiles DROP CONSTRAINT IF EXISTS profiles_full_name_check;
  ALTER TABLE public.profiles ADD CONSTRAINT profiles_full_name_check
    CHECK (full_name IS NULL OR char_length(full_name) <= 120) NOT VALID;
  ALTER TABLE public.profiles DROP CONSTRAINT IF EXISTS profiles_avatar_url_check;
  ALTER TABLE public.profiles ADD CONSTRAINT profiles_avatar_url_check
    CHECK (avatar_url IS NULL OR avatar_url = '' OR (avatar_url ~* '^https://[^\s"''<>]+$' AND char_length(avatar_url) <= 2048)) NOT VALID;
  ALTER TABLE public.profiles DROP CONSTRAINT IF EXISTS profiles_phone_check;
  ALTER TABLE public.profiles ADD CONSTRAINT profiles_phone_check
    CHECK (phone IS NULL OR char_length(phone) <= 20) NOT VALID;
  ALTER TABLE public.profiles DROP CONSTRAINT IF EXISTS profiles_enrollment_no_check;
  ALTER TABLE public.profiles ADD CONSTRAINT profiles_enrollment_no_check
    CHECK (enrollment_no IS NULL OR char_length(enrollment_no) <= 50) NOT VALID;
END $$;


-- Gender is required on every new registration, whoever inserts it (API with service role or a direct API call).
CREATE OR REPLACE FUNCTION public.require_registration_gender() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.gender IS NULL THEN
    RAISE EXCEPTION 'gender is required (male, female or others)' USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SET search_path = public;

DROP TRIGGER IF EXISTS trg_require_registration_gender ON public.registrations;
CREATE TRIGGER trg_require_registration_gender
  BEFORE INSERT ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.require_registration_gender();

-- LEGACY payment_screenshot_url (ImageKit proof URLs from before the move to private Supabase Storage).
-- Fresh databases never get this column. On databases that still have it, it is kept read-only until
-- scripts/migrate-payment-proofs.mjs has copied and verified every proof into the payment-proofs bucket; then
-- supabase/manual/drop_legacy_payment_screenshot_url.sql removes it. Until then: no new URL can be written
-- (insert or update); a reference can only be kept as is or cleared.
CREATE OR REPLACE FUNCTION public.reject_payment_proof_urls() RETURNS TRIGGER AS $$
BEGIN
  IF COALESCE(NEW.payment_screenshot_url, '') <> ''
     AND (TG_OP = 'INSERT' OR NEW.payment_screenshot_url IS DISTINCT FROM OLD.payment_screenshot_url) THEN
    RAISE EXCEPTION 'payment_screenshot_url is no longer accepted: store the private payment_proof_path instead'
      USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SET search_path = public;

DROP TRIGGER IF EXISTS trg_reject_payment_proof_urls ON public.registrations;
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'registrations' AND column_name = 'payment_screenshot_url'
  ) THEN
    ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_payment_screenshot_url_check;
    ALTER TABLE public.registrations ADD CONSTRAINT registrations_payment_screenshot_url_check
      CHECK (payment_screenshot_url IS NULL OR payment_screenshot_url = '' OR (payment_screenshot_url ~* '^https://[^\s"''<>]+$' AND char_length(payment_screenshot_url) <= 2048)) NOT VALID;
    CREATE TRIGGER trg_reject_payment_proof_urls
      BEFORE INSERT OR UPDATE ON public.registrations
      FOR EACH ROW EXECUTE FUNCTION public.reject_payment_proof_urls();
  ELSE
    DROP FUNCTION IF EXISTS public.reject_payment_proof_urls();
  END IF;
END $$;


-- 3. HELPER FUNCTIONS FOR SECURITY (SECURITY DEFINER to avoid RLS recursion)
-- They only ever answer for the CALLER (uid must equal auth.uid()): the policies and triggers always pass auth.uid(),
-- and a signed-in user calling them via /rest/v1/rpc can't probe other users' roles (e.g. to find the admins).
CREATE OR REPLACE FUNCTION public.get_user_role(uid UUID) RETURNS TEXT AS $$
  SELECT role FROM public.profiles WHERE id = uid AND uid = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE SET search_path = public;

CREATE OR REPLACE FUNCTION public.is_admin(uid UUID) RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles WHERE id = uid AND uid = auth.uid() AND role = 'admin'
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE SET search_path = public;

CREATE OR REPLACE FUNCTION public.is_event_staff(uid UUID) RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles WHERE id = uid AND uid = auth.uid() AND role IN ('admin', 'it-team')
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE SET search_path = public;

CREATE OR REPLACE FUNCTION public.is_registration_staff(uid UUID) RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles WHERE id = uid AND uid = auth.uid() AND role IN ('admin', 'registration-team')
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE SET search_path = public;

-- Who may READ every registration: the registration team and admins, plus the read-only 'viewer' role. Writes stay on
-- is_registration_staff, so a viewer can look at registrations but never review them.
CREATE OR REPLACE FUNCTION public.can_view_registrations(uid UUID) RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles WHERE id = uid AND uid = auth.uid() AND role IN ('admin', 'registration-team', 'viewer')
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE SET search_path = public;


-- 4. AUTO-HANDLE NEW SIGNUPS FROM GOOGLE AUTH
CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS TRIGGER AS $$
DECLARE
  v_student_type TEXT;
  v_full_name TEXT;
  v_avatar TEXT;
  v_phone TEXT;
BEGIN
  IF NEW.email ILIKE '%@nitrkl.ac.in' THEN
    v_student_type := 'internal';
  ELSE
    v_student_type := 'external';
  END IF;

  v_full_name := left(COALESCE(
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'name',
    split_part(NEW.email, '@', 1)
  ), 120);

  v_avatar := COALESCE(
    NEW.raw_user_meta_data->>'avatar_url',
    NEW.raw_user_meta_data->>'picture',
    ''
  );
  IF v_avatar !~* '^https://[^\s"''<>]+$' OR char_length(v_avatar) > 2048 THEN
    v_avatar := '';
  END IF;

  v_phone := left(COALESCE(
    NEW.raw_user_meta_data->>'phone',
    NEW.phone,
    NULL
  ), 20);

  INSERT INTO public.profiles (
    id,
    email,
    full_name,
    avatar_url,
    phone,
    student_type,
    role
  ) VALUES (
    NEW.id,
    NEW.email,
    v_full_name,
    v_avatar,
    v_phone,
    v_student_type,
    'user'
  )
  ON CONFLICT (id) DO UPDATE SET
    email = EXCLUDED.email,
    full_name = COALESCE(public.profiles.full_name, EXCLUDED.full_name),
    avatar_url = COALESCE(NULLIF(public.profiles.avatar_url, ''), EXCLUDED.avatar_url),
    updated_at = now();

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

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT OR UPDATE ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();


-- 5. ROW LEVEL SECURITY (RLS) POLICIES
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.registrations ENABLE ROW LEVEL SECURITY;

-- Profiles Policies
DROP POLICY IF EXISTS "Users can view own profile or staff can view all" ON public.profiles;
DROP POLICY IF EXISTS "Users can view own profile or admins can view all" ON public.profiles;
CREATE POLICY "Users can view own profile or admins can view all" ON public.profiles FOR
SELECT TO authenticated USING (auth.uid() = id OR public.is_admin(auth.uid()));

DROP POLICY IF EXISTS "Users can insert own profile" ON public.profiles;
CREATE POLICY "Users can insert own profile" ON public.profiles FOR
INSERT TO authenticated WITH CHECK (auth.uid() = id AND role = 'user');

DROP POLICY IF EXISTS "Users can update own details" ON public.profiles;
CREATE POLICY "Users can update own details" ON public.profiles FOR
UPDATE TO authenticated USING (auth.uid() = id OR public.is_admin(auth.uid()))
WITH CHECK (auth.uid() = id OR public.is_admin(auth.uid()));

-- Protect privileged profile columns from end-user API calls (role, email, student_type, id, created_at).
-- Rules mirrored from /api/admin/users:
--   * regular users can never change role / email / student_type;
--   * admins may switch other users between 'user', 'it-team', and 'registration-team' only;
--   * the 'admin' role can only be granted or revoked directly in the database.
-- Writes from the Supabase Dashboard / SQL Editor / service_role backend (auth.role() <> 'authenticated') are trusted.
DROP TRIGGER IF EXISTS trg_protect_profile_role ON public.profiles;
DROP FUNCTION IF EXISTS public.protect_profile_role();

CREATE OR REPLACE FUNCTION public.protect_profile_fields() RETURNS TRIGGER AS $$
DECLARE
  v_email TEXT;
BEGIN
  IF COALESCE(auth.role(), '') <> 'authenticated' THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'INSERT' THEN
    SELECT email INTO v_email FROM auth.users WHERE id = NEW.id;
    NEW.role := 'user';
    NEW.email := COALESCE(v_email, NEW.email);
    NEW.student_type := CASE WHEN lower(COALESCE(v_email, '')) LIKE '%@nitrkl.ac.in' THEN 'internal' ELSE 'external' END;
    RETURN NEW;
  END IF;

  NEW.id := OLD.id;
  NEW.created_at := OLD.created_at;
  NEW.email := OLD.email;
  NEW.student_type := OLD.student_type;

  IF NEW.role IS DISTINCT FROM OLD.role THEN
    IF NOT public.is_admin(auth.uid())
       OR OLD.role = 'admin'
       OR NEW.role = 'admin' THEN
      NEW.role := OLD.role;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_protect_profile_fields ON public.profiles;
CREATE TRIGGER trg_protect_profile_fields
  BEFORE INSERT OR UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.protect_profile_fields();

-- Registrations Policies
DROP POLICY IF EXISTS "Users can view own registrations or staff can view all" ON public.registrations;
CREATE POLICY "Users can view own registrations or staff can view all" ON public.registrations FOR
SELECT TO authenticated USING (user_id = auth.uid() OR public.can_view_registrations(auth.uid()));

-- No direct INSERT for end users: every registration is created by /api/register (service role), which applies the
-- rules the database can't (proof object really uploaded by the same user, rate limits). The INSERT privilege itself is revoked in section 11; the insert trigger below stays as defense in depth.
DROP POLICY IF EXISTS "Users can create their registration" ON public.registrations;

DROP POLICY IF EXISTS "Staff can review pending registrations" ON public.registrations;
CREATE POLICY "Staff can review pending registrations" ON public.registrations FOR
UPDATE TO authenticated USING (public.is_registration_staff(auth.uid())) WITH CHECK (public.is_registration_staff(auth.uid()));

-- Registrations inserted directly through the Supabase API by an end user can't choose their own status, fee,
-- student type or email: these are derived from the verified auth account exactly like /api/register does.
-- There is no free tier: every registration starts 'pending' at 999 whatever the email domain; student_type is only
-- a reporting label (classifyStudentType in src/lib/security.ts).
CREATE OR REPLACE FUNCTION public.enforce_registration_insert() RETURNS TRIGGER AS $$
DECLARE
  v_email TEXT;
  v_confirmed TIMESTAMPTZ;
BEGIN
  IF COALESCE(auth.role(), '') <> 'authenticated' THEN
    RETURN NEW;
  END IF;

  SELECT lower(email), email_confirmed_at INTO v_email, v_confirmed FROM auth.users WHERE id = auth.uid();

  NEW.user_id := auth.uid();
  NEW.email := COALESCE(v_email, '');
  NEW.registration_id := 'IV26-' || (1000 + (('x' || substr(md5(gen_random_uuid()::text), 1, 7))::bit(28)::int % 9000))::text;
  NEW.reviewed_by := NULL;
  NEW.reviewed_at := NULL;
  NEW.student_type := CASE WHEN v_email LIKE '%@nitrkl.ac.in' AND v_confirmed IS NOT NULL THEN 'internal' ELSE 'external' END;
  NEW.status := 'pending';
  NEW.amount := 999;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_enforce_registration_insert ON public.registrations;
CREATE TRIGGER trg_enforce_registration_insert
  BEFORE INSERT ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.enforce_registration_insert();

-- Staff reviewing through the Supabase API may only move a PENDING registration to confirmed/rejected
-- ("once done cannot be altered"); every other column stays as submitted, except the reason given with a rejection
-- (rejection_reason, section 17).
CREATE OR REPLACE FUNCTION public.enforce_registration_review() RETURNS TRIGGER AS $$
DECLARE
  v_status TEXT;
  v_reason TEXT;
BEGIN
  IF COALESCE(auth.role(), '') <> 'authenticated' THEN
    RETURN NEW;
  END IF;

  IF OLD.user_id = auth.uid() THEN
    RAISE EXCEPTION 'Staff cannot review their own registration' USING ERRCODE = '42501';
  END IF;

  IF OLD.status <> 'pending' OR NEW.status NOT IN ('confirmed', 'rejected') THEN
    RAISE EXCEPTION 'Registration % is already % and cannot be altered', OLD.registration_id, OLD.status
      USING ERRCODE = '42501';
  END IF;

  v_status := NEW.status;
  v_reason := CASE WHEN NEW.status = 'rejected' THEN NEW.rejection_reason END;
  NEW := OLD;
  NEW.status := v_status;
  NEW.rejection_reason := v_reason;
  NEW.reviewed_by := auth.uid();
  NEW.reviewed_at := now();
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_enforce_registration_review ON public.registrations;
CREATE TRIGGER trg_enforce_registration_review
  BEFORE UPDATE ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.enforce_registration_review();

-- One active (pending or confirmed) registration per user, and one registration per UPI transaction (UTR). Rejected
-- registrations are left out of both: a rejected student may register again, with a new screenshot and the same or a
-- new UTR (section 17 caps the attempts). Replaces uq_registrations_user_id (one registration per user, ever).
-- Wrapped so the script still runs if legacy duplicate rows exist; clean them up and re-run to add the index.
DROP INDEX IF EXISTS public.uq_registrations_user_id;
DO $$
BEGIN
  CREATE UNIQUE INDEX IF NOT EXISTS uq_registrations_active_user_id ON public.registrations(user_id)
    WHERE status <> 'rejected';
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'uq_registrations_active_user_id not created: a user has two active registrations';
END $$;

DO $$
BEGIN
  CREATE UNIQUE INDEX IF NOT EXISTS uq_registrations_utr ON public.registrations(utr)
    WHERE utr IS NOT NULL AND status <> 'rejected';
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'uq_registrations_utr not created: duplicate UTR rows exist in registrations';
END $$;


-- 6. USER SESSIONS TABLE (Stores ONLY refresh_token in database; access_token is in cookies)
CREATE TABLE IF NOT EXISTS public.user_sessions (
  user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  refresh_token TEXT NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

ALTER TABLE public.user_sessions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view own sessions" ON public.user_sessions;
CREATE POLICY "Users can view own sessions" ON public.user_sessions FOR
SELECT TO authenticated USING (user_id = auth.uid());

-- Session rows are written only by /api/auth/session and /api/auth/logout (service role); clients can't write them.
DROP POLICY IF EXISTS "Users can insert own sessions" ON public.user_sessions;
DROP POLICY IF EXISTS "Users can update own sessions" ON public.user_sessions;
DROP POLICY IF EXISTS "Users can delete own sessions" ON public.user_sessions;

-- 7. EVENTS TABLE
CREATE TABLE IF NOT EXISTS public.events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  poster_url TEXT NOT NULL,
  brochure_url TEXT, -- Optional Google Drive link
  category TEXT NOT NULL CHECK (category IN ('flagship events', 'standout events', 'main events', 'dts events', 'fun events')),
  format TEXT DEFAULT 'Solo / Team',
  duration TEXT DEFAULT 'TBA',
  venue TEXT DEFAULT 'NIT Rourkela',
  sort_order INTEGER NOT NULL DEFAULT 0, -- Position within its category, set by staff in the admin Events tab
  created_by UUID REFERENCES public.profiles(id),
  updated_by UUID REFERENCES public.profiles(id),
  created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_events_created_at ON public.events(created_at DESC);

-- Ensure check constraint on category is up to date if table already exists
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'events' AND table_schema = 'public') THEN
    ALTER TABLE public.events ALTER COLUMN brochure_url DROP NOT NULL;
    ALTER TABLE public.events ALTER COLUMN format DROP NOT NULL;
    ALTER TABLE public.events ALTER COLUMN duration DROP NOT NULL;
    ALTER TABLE public.events ALTER COLUMN venue DROP NOT NULL;
    ALTER TABLE public.events ADD COLUMN IF NOT EXISTS sort_order INTEGER NOT NULL DEFAULT 0;
    ALTER TABLE public.events DROP CONSTRAINT IF EXISTS events_category_check;
    ALTER TABLE public.events ADD CONSTRAINT events_category_check CHECK (category IN ('flagship events', 'standout events', 'main events', 'dts events', 'fun events'));
    -- Brochure links are rendered as public hrefs: only Google Drive/Docs URLs (never javascript:/data:).
    ALTER TABLE public.events DROP CONSTRAINT IF EXISTS events_poster_url_check;
    ALTER TABLE public.events ADD CONSTRAINT events_poster_url_check
      CHECK (poster_url ~* '^https://[^\s"''<>]+$') NOT VALID;
    ALTER TABLE public.events DROP CONSTRAINT IF EXISTS events_brochure_url_check;
    ALTER TABLE public.events ADD CONSTRAINT events_brochure_url_check
      CHECK (brochure_url IS NULL OR brochure_url ~* '^https?://([a-z0-9-]+\.)*(drive|docs)\.google\.com/.+') NOT VALID;
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_events_category_sort ON public.events(category, sort_order);

ALTER TABLE public.events ENABLE ROW LEVEL SECURITY;

-- Anyone can view events (public and authenticated)
DROP POLICY IF EXISTS "Public can view events" ON public.events;
CREATE POLICY "Public can view events" ON public.events FOR
SELECT USING (true);

-- Authorized IT team member and admin can insert events
DROP POLICY IF EXISTS "Staff can insert events" ON public.events;
CREATE POLICY "Staff can insert events" ON public.events FOR
INSERT TO authenticated WITH CHECK (public.is_event_staff(auth.uid()));

-- Authorized IT team member and admin can update events
DROP POLICY IF EXISTS "Staff can update events" ON public.events;
CREATE POLICY "Staff can update events" ON public.events FOR
UPDATE TO authenticated USING (public.is_event_staff(auth.uid())) WITH CHECK (public.is_event_staff(auth.uid()));

-- Authorized IT team member and admin can delete events
DROP POLICY IF EXISTS "Staff can delete events" ON public.events;
CREATE POLICY "Staff can delete events" ON public.events FOR
DELETE TO authenticated USING (public.is_event_staff(auth.uid()));


-- 8. GALLERY TABLE
CREATE TABLE IF NOT EXISTS public.gallery (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT, -- Optional title for gallery photo
  image_url TEXT NOT NULL,
  file_id TEXT, -- ImageKit file ID
  created_by UUID REFERENCES public.profiles(id),
  created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_gallery_created_at ON public.gallery(created_at DESC);

ALTER TABLE public.gallery DROP CONSTRAINT IF EXISTS gallery_image_url_check;
ALTER TABLE public.gallery ADD CONSTRAINT gallery_image_url_check
  CHECK (image_url ~* '^https://[^\s"''<>]+$') NOT VALID;

ALTER TABLE public.gallery ENABLE ROW LEVEL SECURITY;

-- Anyone can view gallery images
DROP POLICY IF EXISTS "Public can view gallery" ON public.gallery;
CREATE POLICY "Public can view gallery" ON public.gallery FOR
SELECT USING (true);

-- Authorized IT team member and admin can insert gallery images
DROP POLICY IF EXISTS "Staff can insert gallery" ON public.gallery;
CREATE POLICY "Staff can insert gallery" ON public.gallery FOR
INSERT TO authenticated WITH CHECK (public.is_event_staff(auth.uid()));

-- Authorized IT team member and admin can update gallery images
DROP POLICY IF EXISTS "Staff can update gallery" ON public.gallery;
CREATE POLICY "Staff can update gallery" ON public.gallery FOR
UPDATE TO authenticated USING (public.is_event_staff(auth.uid())) WITH CHECK (public.is_event_staff(auth.uid()));

-- Authorized IT team member and admin can delete gallery images
DROP POLICY IF EXISTS "Staff can delete gallery" ON public.gallery;
CREATE POLICY "Staff can delete gallery" ON public.gallery FOR
DELETE TO authenticated USING (public.is_event_staff(auth.uid()));


-- 9. FUNCTION PRIVILEGES
-- The role-helper functions are SECURITY DEFINER; don't let anonymous callers probe arbitrary users' roles via RPC.
-- (authenticated keeps EXECUTE because the RLS policies above call is_admin / is_event_staff / is_registration_staff /
-- can_view_registrations.)
-- is_staff() was split into is_event_staff() and is_registration_staff(). Databases created before the split still
-- have it, and the oldest version answers for ANY uid with EXECUTE left to anon (anyone could probe who is staff).
-- No policy above uses it any more, so remove it; if something added outside this script still depends on it, keep
-- it but make sure nobody can call it.
DO $$
BEGIN
  DROP FUNCTION IF EXISTS public.is_staff(UUID);
EXCEPTION WHEN dependent_objects_still_exist THEN
  RAISE NOTICE 'public.is_staff(uuid) kept (other objects depend on it); EXECUTE revoked from everyone';
  REVOKE EXECUTE ON FUNCTION public.is_staff(UUID) FROM PUBLIC, anon, authenticated;
END $$;
REVOKE EXECUTE ON FUNCTION public.get_user_role(UUID) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.is_admin(UUID) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.is_event_staff(UUID) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.is_registration_staff(UUID) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.can_view_registrations(UUID) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.is_admin(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.is_event_staff(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.is_registration_staff(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.can_view_registrations(UUID) TO authenticated;
-- get_user_role isn't used by any policy or by the app.
REVOKE EXECUTE ON FUNCTION public.get_user_role(UUID) FROM authenticated;


-- 10. AUTHORSHIP STAMPING FOR STAFF CONTENT
-- created_by / updated_by on events and gallery come from the caller's JWT; they can't be forged through the API.
CREATE OR REPLACE FUNCTION public.stamp_content_author() RETURNS TRIGGER AS $$
BEGIN
  IF COALESCE(auth.role(), '') <> 'authenticated' THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'INSERT' THEN
    NEW.created_by := auth.uid();
  ELSE
    NEW.created_by := OLD.created_by;
    NEW.created_at := OLD.created_at;
    IF TG_TABLE_NAME = 'gallery' THEN
      -- Only the title is editable; the image and its storage file id are fixed at upload time.
      NEW.image_url := OLD.image_url;
      NEW.file_id := OLD.file_id;
    END IF;
  END IF;

  IF TG_TABLE_NAME = 'events' THEN
    NEW.updated_by := auth.uid();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_stamp_events_author ON public.events;
CREATE TRIGGER trg_stamp_events_author
  BEFORE INSERT OR UPDATE ON public.events
  FOR EACH ROW EXECUTE FUNCTION public.stamp_content_author();

DROP TRIGGER IF EXISTS trg_stamp_gallery_author ON public.gallery;
CREATE TRIGGER trg_stamp_gallery_author
  BEFORE INSERT OR UPDATE ON public.gallery
  FOR EACH ROW EXECUTE FUNCTION public.stamp_content_author();


-- 11. TABLE PRIVILEGES (defense in depth on top of RLS)
-- Supabase grants every table to anon/authenticated by default; RLS is then the only barrier. Remove what no client
-- needs so a future policy mistake can't expose private data. TRUNCATE/REFERENCES/TRIGGER are never needed by clients.
REVOKE ALL ON public.profiles, public.registrations, public.user_sessions FROM anon;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON public.events, public.gallery FROM anon;
REVOKE TRUNCATE, REFERENCES, TRIGGER
  ON public.profiles, public.registrations, public.user_sessions, public.events, public.gallery
  FROM authenticated;
REVOKE DELETE ON public.profiles, public.registrations FROM authenticated;
-- Registrations are created only by /api/register, session rows only by the auth API routes (both service role).
REVOKE INSERT ON public.registrations FROM authenticated;
REVOKE INSERT, UPDATE, DELETE ON public.user_sessions FROM authenticated;


-- 12. PRIVATE STORAGE BUCKET FOR PAYMENT PROOFS
-- Payment screenshots live in a PRIVATE bucket: no public URLs exist. Objects are written only by /api/upload (service
-- role) and named '<user_id>/<uuid>.<ext>' from the verified session. Re-running this script also forces the bucket back to private.
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES ('payment-proofs', 'payment-proofs', false, 1048576,
        ARRAY['image/jpeg', 'image/png', 'image/webp', 'image/gif', 'image/heic'])
ON CONFLICT (id) DO UPDATE SET
  public = false,
  file_size_limit = EXCLUDED.file_size_limit,
  allowed_mime_types = EXCLUDED.allowed_mime_types;

-- Uploads: NO INSERT policy for users. A signed-in user's JWT plus the public key could otherwise upload straight to
-- Storage, skipping /api/upload's rate limit, magic-byte check and per-user limits (unbounded junk files that the
-- owner can't even delete). /api/upload writes with the service role after all its checks: server-chosen
-- '<user_id>/<uuid>.<ext>' name, and only while the user has no active registration (none yet, or only rejected ones
-- with attempts left, section 17). Dropped here so re-running this script
-- also removes the policy from databases created by an earlier version.
-- No UPDATE/DELETE policies either: a submitted proof can't be replaced or removed.
DROP POLICY IF EXISTS "Payment proofs: owners upload into their own folder" ON storage.objects;

-- Owners may see their own files (used by /api/register to confirm the upload exists). There is deliberately NO staff
-- policy: staff view proofs only through short-lived signed URLs minted server-side by
-- /api/admin/registrations/payment-proof, so they can't list the bucket or sign long-lived URLs themselves.
DROP POLICY IF EXISTS "Payment proofs: owners read their own files" ON storage.objects;
CREATE POLICY "Payment proofs: owners read their own files" ON storage.objects FOR
SELECT TO authenticated USING (
  bucket_id = 'payment-proofs'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

-- Guard rail: a RESTRICTIVE policy is ANDed with every other policy, so even a broad storage policy added later in
-- the dashboard (e.g. "authenticated can read all objects") can never expose another user's payment proof.
DROP POLICY IF EXISTS "Payment proofs: never outside the owner's folder" ON storage.objects;
CREATE POLICY "Payment proofs: never outside the owner's folder" ON storage.objects AS RESTRICTIVE FOR
ALL TO anon, authenticated
USING (bucket_id <> 'payment-proofs' OR (storage.foldername(name))[1] = auth.uid()::text)
WITH CHECK (bucket_id <> 'payment-proofs' OR (storage.foldername(name))[1] = auth.uid()::text);


-- 13. ENTRY PASSES AND CHECK-IN AT THE VENUE
-- Every confirmed registration has one QR entry pass. At the venue the registration team scans it, compares the
-- details with the person's college ID and checks them in: once per registration, ever.
-- Naming: "check-in" everywhere in the database and the code. The scanner's button reads "Approve", but approval
-- of a REGISTRATION (payment review) is a different thing and is untouched here.
--
-- The QR holds only an opaque random token (16 CSPRNG bytes, base64url, minted by POST /api/pass). The database
-- stores its SHA-256 (token_hash) and an AES-256-GCM ciphertext (token_ciphertext) that only the server can decrypt
-- (ENTRY_PASS_ENCRYPTION_KEY), so the owner sees the same QR again on any device.
-- Clients never write these tables: every change goes through the SECURITY DEFINER functions below, which check
-- the caller's role themselves and log to entry_audit_log.

CREATE TABLE IF NOT EXISTS public.entry_passes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  registration_id UUID NOT NULL REFERENCES public.registrations(id) ON DELETE CASCADE,
  token_hash TEXT UNIQUE, -- hex SHA-256 of the QR token; NULL until the owner first opens the pass
  token_ciphertext TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')),
  issued_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at TIMESTAMPTZ,
  revoke_reason TEXT,
  CONSTRAINT entry_passes_token_hash_check CHECK (token_hash IS NULL OR token_hash ~ '^[0-9a-f]{64}$'),
  CONSTRAINT entry_passes_token_pair_check CHECK ((token_hash IS NULL) = (token_ciphertext IS NULL)),
  CONSTRAINT entry_passes_token_ciphertext_check CHECK (token_ciphertext IS NULL OR char_length(token_ciphertext) <= 512),
  CONSTRAINT entry_passes_revoked_check CHECK ((status = 'revoked') = (revoked_at IS NOT NULL))
);

-- Never more than one active pass per registration (approving twice or re-running the backfill can't add one).
CREATE UNIQUE INDEX IF NOT EXISTS uq_entry_passes_active_registration
  ON public.entry_passes(registration_id) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS idx_entry_passes_registration ON public.entry_passes(registration_id);

CREATE TABLE IF NOT EXISTS public.entry_checkins (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  registration_id UUID NOT NULL REFERENCES public.registrations(id) ON DELETE CASCADE,
  pass_id UUID REFERENCES public.entry_passes(id) ON DELETE SET NULL,
  desk TEXT NOT NULL CHECK (char_length(desk) BETWEEN 1 AND 40),
  checked_in_by UUID NOT NULL REFERENCES public.profiles(id),
  scan_id UUID NOT NULL, -- chosen by the scanner per scan; a retry with the same scan_id is answered CHECKED_IN
  method TEXT NOT NULL CHECK (method IN ('qr', 'manual')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  voided_at TIMESTAMPTZ,
  voided_by UUID REFERENCES public.profiles(id),
  void_reason TEXT CHECK (void_reason IS NULL OR char_length(void_reason) BETWEEN 1 AND 200),
  CONSTRAINT entry_checkins_void_check
    CHECK ((voided_at IS NULL) = (voided_by IS NULL) AND (voided_at IS NULL) = (void_reason IS NULL))
);

-- One check-in per registration, whichever QR (old or reissued) was shown. This index, not application code, is what
-- makes two simultaneous Approves produce exactly one row. A voided check-in no longer counts.
CREATE UNIQUE INDEX IF NOT EXISTS uq_entry_checkins_registration
  ON public.entry_checkins(registration_id) WHERE voided_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_entry_checkins_created_at ON public.entry_checkins(created_at DESC);

-- Append-only trail of every scan, manual lookup, check-in, rejected attempt, void and reissue. No foreign keys:
-- the log must outlive deleted accounts and registrations.
CREATE TABLE IF NOT EXISTS public.entry_audit_log (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  action TEXT NOT NULL CHECK (action IN ('lookup', 'manual_lookup', 'check_in', 'reject', 'void', 'reissue')),
  actor UUID NOT NULL,
  registration_id UUID,
  pass_id UUID,
  checkin_id UUID,
  detail JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_entry_audit_log_created_at ON public.entry_audit_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_entry_audit_log_action ON public.entry_audit_log(action, created_at DESC);

ALTER TABLE public.entry_passes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.entry_checkins ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.entry_audit_log ENABLE ROW LEVEL SECURITY;

-- Owners see their own pass; admins see all. No INSERT/UPDATE/DELETE policies on any of the three tables.
DROP POLICY IF EXISTS "Owners can view their entry pass, admins all" ON public.entry_passes;
CREATE POLICY "Owners can view their entry pass, admins all" ON public.entry_passes FOR
SELECT TO authenticated USING (
  public.is_admin(auth.uid())
  -- Qualified: registrations has its own registration_id (the text IV26-NNNN code).
  OR EXISTS (SELECT 1 FROM public.registrations r WHERE r.id = entry_passes.registration_id AND r.user_id = auth.uid())
);

DROP POLICY IF EXISTS "Admins can view check-ins" ON public.entry_checkins;
CREATE POLICY "Admins can view check-ins" ON public.entry_checkins FOR
SELECT TO authenticated USING (public.is_admin(auth.uid()));

DROP POLICY IF EXISTS "Admins can view the entry audit log" ON public.entry_audit_log;
CREATE POLICY "Admins can view the entry audit log" ON public.entry_audit_log FOR
SELECT TO authenticated USING (public.is_admin(auth.uid()));

REVOKE ALL ON public.entry_passes, public.entry_checkins, public.entry_audit_log FROM anon;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
  ON public.entry_passes, public.entry_checkins, public.entry_audit_log FROM authenticated;
GRANT SELECT ON public.entry_passes, public.entry_checkins, public.entry_audit_log TO authenticated;


-- Pass lifecycle: a registration becoming 'confirmed' issues its pass; leaving 'confirmed' revokes it. A trigger, so it
-- runs in the same transaction as the status change whoever makes it (/api/admin/registrations, the API, SQL).
CREATE OR REPLACE FUNCTION public.issue_entry_pass(p_registration UUID) RETURNS UUID AS $$
DECLARE
  v_id UUID;
BEGIN
  INSERT INTO public.entry_passes (registration_id)
  SELECT r.id FROM public.registrations r WHERE r.id = p_registration AND r.status = 'confirmed'
  ON CONFLICT (registration_id) WHERE status = 'active' DO NOTHING
  RETURNING id INTO v_id;
  RETURN v_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION public.sync_entry_pass() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'confirmed' THEN
    PERFORM public.issue_entry_pass(NEW.id);
  ELSE
    UPDATE public.entry_passes
    SET status = 'revoked', revoked_at = now(), revoke_reason = 'registration ' || COALESCE(NEW.status, 'unknown')
    WHERE registration_id = NEW.id AND status = 'active';
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_entry_pass_lifecycle ON public.registrations;
CREATE TRIGGER trg_entry_pass_lifecycle
  AFTER INSERT OR UPDATE OF status ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.sync_entry_pass();

-- Issues passes for registrations confirmed before this section existed. Idempotent: returns how many it issued,
-- 0 on a second run. Run from the SQL editor (supabase/manual/backfill_entry_passes.sql); no API role may call it.
CREATE OR REPLACE FUNCTION public.backfill_entry_passes() RETURNS INTEGER AS $$
DECLARE
  v_count INTEGER;
BEGIN
  INSERT INTO public.entry_passes (registration_id)
  SELECT r.id FROM public.registrations r WHERE r.status = 'confirmed'
  ON CONFLICT (registration_id) WHERE status = 'active' DO NOTHING;
  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;


-- What the scanner shows for a registration: VALID (with the details staff compare against the college ID),
-- ALREADY_CHECKED_IN (when, by whom, at which desk), or INVALID (a reason code, never personal data).
CREATE OR REPLACE FUNCTION public.entry_state(p_registration UUID, p_pass UUID, p_invalid_reason TEXT)
RETURNS JSONB AS $$
DECLARE
  r public.registrations%ROWTYPE;
  c RECORD;
BEGIN
  IF p_invalid_reason IS NOT NULL THEN
    RETURN jsonb_build_object('state', 'INVALID', 'reason', p_invalid_reason);
  END IF;

  SELECT * INTO r FROM public.registrations WHERE id = p_registration;
  IF NOT FOUND OR r.status <> 'confirmed' THEN
    RETURN jsonb_build_object('state', 'INVALID', 'reason', 'not_confirmed');
  END IF;

  SELECT ch.id, ch.created_at, ch.desk, ch.method, pr.full_name AS staff_name INTO c
  FROM public.entry_checkins ch LEFT JOIN public.profiles pr ON pr.id = ch.checked_in_by
  WHERE ch.registration_id = r.id AND ch.voided_at IS NULL;
  IF FOUND THEN
    RETURN jsonb_build_object(
      'state', 'ALREADY_CHECKED_IN',
      'registration', r.id, 'registration_id', r.registration_id, 'name', r.name,
      'checkin_id', c.id, 'checked_in_at', c.created_at, 'checked_in_by', COALESCE(c.staff_name, 'Staff'),
      'desk', c.desk, 'method', c.method
    );
  END IF;

  RETURN jsonb_build_object(
    'state', 'VALID',
    'registration', r.id, 'registration_id', r.registration_id, 'name', r.name,
    'enrollment_no', r.enrollment_no, 'college', r.college, 'phone', r.phone, 'utr', r.utr
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;

-- Resolves a scanned token to its pass and registration, or an invalid reason. Malformed and unknown tokens are
-- both 'unknown'.
CREATE OR REPLACE FUNCTION public.entry_resolve_token(
  p_token TEXT, OUT pass_id UUID, OUT registration_id UUID, OUT invalid_reason TEXT
) AS $$
DECLARE
  p public.entry_passes%ROWTYPE;
BEGIN
  IF p_token IS NULL OR p_token !~ '^[A-Za-z0-9_-]{22}$' THEN
    invalid_reason := 'unknown';
    RETURN;
  END IF;
  SELECT * INTO p FROM public.entry_passes WHERE token_hash = encode(sha256(convert_to(p_token, 'UTF8')), 'hex');
  IF NOT FOUND THEN
    invalid_reason := 'unknown';
    RETURN;
  END IF;
  pass_id := p.id;
  registration_id := p.registration_id;
  IF p.status <> 'active' THEN
    invalid_reason := 'revoked';
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;

CREATE OR REPLACE FUNCTION public.entry_require_registration_staff() RETURNS VOID AS $$
BEGIN
  IF auth.uid() IS NULL OR NOT public.is_registration_staff(auth.uid()) THEN
    RAISE EXCEPTION 'Registration team or admin access required' USING ERRCODE = '42501';
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;

CREATE OR REPLACE FUNCTION public.entry_require_admin() RETURNS VOID AS $$
BEGIN
  IF auth.uid() IS NULL OR NOT public.is_admin(auth.uid()) THEN
    RAISE EXCEPTION 'Admin access required' USING ERRCODE = '42501';
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;

-- The one place a check-in row is written (QR and manual paths alike).
CREATE OR REPLACE FUNCTION public.entry_check_in(p_pass UUID, p_scan_id UUID, p_desk TEXT, p_method TEXT)
RETURNS JSONB AS $$
DECLARE
  v_actor UUID := auth.uid();
  v_desk TEXT := btrim(COALESCE(p_desk, ''));
  v_reg public.registrations%ROWTYPE;
  v_id UUID;
  v_at TIMESTAMPTZ;
  v_existing RECORD;
  v_result JSONB;
BEGIN
  IF p_scan_id IS NULL THEN
    RAISE EXCEPTION 'scan_id is required' USING ERRCODE = '22023';
  END IF;
  IF v_desk !~ '^[A-Za-z0-9][A-Za-z0-9 ._#/()-]{0,39}$' THEN
    RAISE EXCEPTION 'Invalid desk name' USING ERRCODE = '22023';
  END IF;

  -- Re-validate everything; never trust an earlier lookup. FOR SHARE: a concurrent reissue waits for this check-in.
  SELECT r.* INTO v_reg
  FROM public.entry_passes p JOIN public.registrations r ON r.id = p.registration_id
  WHERE p.id = p_pass AND p.status = 'active' AND r.status = 'confirmed'
  FOR SHARE OF p;
  IF NOT FOUND THEN
    INSERT INTO public.entry_audit_log (action, actor, pass_id, detail)
    VALUES ('check_in', v_actor, p_pass, jsonb_build_object('result', 'INVALID', 'method', p_method, 'desk', v_desk));
    RETURN jsonb_build_object('state', 'INVALID', 'reason', 'revoked');
  END IF;

  IF v_reg.user_id = v_actor THEN
    RAISE EXCEPTION 'You cannot check in your own registration' USING ERRCODE = '42501';
  END IF;

  INSERT INTO public.entry_checkins (registration_id, pass_id, desk, checked_in_by, scan_id, method)
  VALUES (v_reg.id, p_pass, v_desk, v_actor, p_scan_id, p_method)
  ON CONFLICT (registration_id) WHERE voided_at IS NULL DO NOTHING
  RETURNING id, created_at INTO v_id, v_at;

  IF v_id IS NOT NULL THEN
    INSERT INTO public.entry_audit_log (action, actor, registration_id, pass_id, checkin_id, detail)
    VALUES ('check_in', v_actor, v_reg.id, p_pass, v_id,
            jsonb_build_object('result', 'CHECKED_IN', 'method', p_method, 'desk', v_desk, 'scan_id', p_scan_id));
    RETURN jsonb_build_object(
      'state', 'CHECKED_IN', 'registration', v_reg.id, 'registration_id', v_reg.registration_id, 'name', v_reg.name,
      'checkin_id', v_id, 'checked_in_at', v_at, 'desk', v_desk
    );
  END IF;

  -- Already checked in, or lost the race. The conflicting row is committed by now (ON CONFLICT waited for it).
  SELECT ch.id, ch.created_at, ch.desk, ch.scan_id, ch.checked_in_by INTO v_existing
  FROM public.entry_checkins ch WHERE ch.registration_id = v_reg.id AND ch.voided_at IS NULL;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Check-in changed concurrently, please retry' USING ERRCODE = '40001';
  END IF;

  IF v_existing.scan_id = p_scan_id AND v_existing.checked_in_by = v_actor THEN
    -- The same Approve retried after a lost response: the earlier attempt did succeed.
    RETURN jsonb_build_object(
      'state', 'CHECKED_IN', 'registration', v_reg.id, 'registration_id', v_reg.registration_id, 'name', v_reg.name,
      'checkin_id', v_existing.id, 'checked_in_at', v_existing.created_at, 'desk', v_existing.desk
    );
  END IF;

  v_result := public.entry_state(v_reg.id, p_pass, NULL);
  INSERT INTO public.entry_audit_log (action, actor, registration_id, pass_id, checkin_id, detail)
  VALUES ('check_in', v_actor, v_reg.id, p_pass, v_existing.id,
          jsonb_build_object('result', 'ALREADY_CHECKED_IN', 'method', p_method, 'desk', v_desk));
  RETURN v_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;


-- ---- Called by the app (POST /api/admin/entry/*) with the caller's own JWT ----

-- Scan: what the registration team sees before deciding. Changes nothing except the audit log.
CREATE OR REPLACE FUNCTION public.lookup_entry_pass(p_token TEXT) RETURNS JSONB AS $$
DECLARE
  t RECORD;
  v_result JSONB;
BEGIN
  PERFORM public.entry_require_registration_staff();
  SELECT * INTO t FROM public.entry_resolve_token(p_token);
  v_result := public.entry_state(t.registration_id, t.pass_id, t.invalid_reason);
  INSERT INTO public.entry_audit_log (action, actor, registration_id, pass_id, detail)
  VALUES ('lookup', auth.uid(), t.registration_id, t.pass_id, jsonb_build_object('result', v_result->>'state'));
  RETURN v_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Damaged or missing QR: exact registration ID (IV26-NNNN) or roll number (enrollment_no).
CREATE OR REPLACE FUNCTION public.lookup_entry_manual(p_query TEXT) RETURNS JSONB AS $$
DECLARE
  v_query TEXT := btrim(COALESCE(p_query, ''));
  v_ids UUID[];
  v_pass UUID;
  v_result JSONB;
BEGIN
  PERFORM public.entry_require_registration_staff();
  IF char_length(v_query) NOT BETWEEN 1 AND 50 THEN
    RAISE EXCEPTION 'Enter a registration ID or roll number' USING ERRCODE = '22023';
  END IF;

  IF upper(v_query) ~ '^IV26-[0-9]{4}$' THEN
    SELECT array_agg(id) INTO v_ids FROM public.registrations WHERE registration_id = upper(v_query);
  ELSE
    SELECT array_agg(id) INTO v_ids FROM public.registrations
    WHERE lower(btrim(enrollment_no)) = lower(v_query) AND status = 'confirmed';
  END IF;

  IF COALESCE(cardinality(v_ids), 0) = 0 THEN
    v_result := jsonb_build_object('state', 'INVALID', 'reason', 'not_found');
  ELSIF cardinality(v_ids) > 1 THEN
    v_result := jsonb_build_object('state', 'AMBIGUOUS', 'count', cardinality(v_ids));
  ELSE
    SELECT id INTO v_pass FROM public.entry_passes WHERE registration_id = v_ids[1] AND status = 'active';
    v_result := public.entry_state(v_ids[1], v_pass, CASE WHEN v_pass IS NULL THEN 'not_confirmed' END);
  END IF;

  INSERT INTO public.entry_audit_log (action, actor, registration_id, pass_id, detail)
  VALUES ('manual_lookup', auth.uid(), CASE WHEN cardinality(v_ids) = 1 THEN v_ids[1] END, v_pass,
          jsonb_build_object('query', v_query, 'result', v_result->>'state'));
  RETURN v_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- "Approve" on a scanned QR.
CREATE OR REPLACE FUNCTION public.check_in_entry_pass(p_token TEXT, p_scan_id UUID, p_desk TEXT) RETURNS JSONB AS $$
DECLARE
  t RECORD;
BEGIN
  PERFORM public.entry_require_registration_staff();
  SELECT * INTO t FROM public.entry_resolve_token(p_token);
  IF t.invalid_reason IS NOT NULL THEN
    INSERT INTO public.entry_audit_log (action, actor, registration_id, pass_id, detail)
    VALUES ('check_in', auth.uid(), t.registration_id, t.pass_id, jsonb_build_object('result', 'INVALID', 'method', 'qr'));
    RETURN jsonb_build_object('state', 'INVALID', 'reason', t.invalid_reason);
  END IF;
  RETURN public.entry_check_in(t.pass_id, p_scan_id, p_desk, 'qr');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- "Approve" after a manual lookup: the registration's current active pass, through the same entry_check_in.
CREATE OR REPLACE FUNCTION public.check_in_entry_registration(p_registration UUID, p_scan_id UUID, p_desk TEXT)
RETURNS JSONB AS $$
DECLARE
  v_pass UUID;
BEGIN
  PERFORM public.entry_require_registration_staff();
  SELECT id INTO v_pass FROM public.entry_passes WHERE registration_id = p_registration AND status = 'active';
  IF v_pass IS NULL THEN
    INSERT INTO public.entry_audit_log (action, actor, registration_id, detail)
    VALUES ('check_in', auth.uid(), p_registration, jsonb_build_object('result', 'INVALID', 'method', 'manual'));
    RETURN jsonb_build_object('state', 'INVALID', 'reason', 'not_confirmed');
  END IF;
  RETURN public.entry_check_in(v_pass, p_scan_id, p_desk, 'manual');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- "Reject": the details didn't match the college ID, or there was none. Changes nothing on the pass, which stays
-- usable so the case can be resolved later; only the attempt is logged.
CREATE OR REPLACE FUNCTION public.reject_entry_attempt(p_token TEXT, p_registration UUID, p_reason TEXT, p_note TEXT)
RETURNS JSONB AS $$
DECLARE
  v_note TEXT := NULLIF(btrim(COALESCE(p_note, '')), '');
  v_reg UUID;
  v_pass UUID;
  v_invalid TEXT;
  v_state JSONB;
  t RECORD;
BEGIN
  PERFORM public.entry_require_registration_staff();
  IF p_reason IS NULL OR p_reason NOT IN ('id_mismatch', 'no_id', 'other') THEN
    RAISE EXCEPTION 'Invalid reason' USING ERRCODE = '22023';
  END IF;
  IF p_reason = 'other' AND v_note IS NULL THEN
    RAISE EXCEPTION 'Describe the reason' USING ERRCODE = '22023';
  END IF;
  IF char_length(COALESCE(v_note, '')) > 200 THEN
    RAISE EXCEPTION 'Note is too long' USING ERRCODE = '22023';
  END IF;
  IF (p_token IS NULL) = (p_registration IS NULL) THEN
    RAISE EXCEPTION 'Give either a token or a registration' USING ERRCODE = '22023';
  END IF;

  IF p_token IS NOT NULL THEN
    SELECT * INTO t FROM public.entry_resolve_token(p_token);
    v_reg := t.registration_id;
    v_pass := t.pass_id;
    v_invalid := t.invalid_reason;
  ELSE
    v_reg := p_registration;
    SELECT id INTO v_pass FROM public.entry_passes WHERE registration_id = p_registration AND status = 'active';
    IF v_pass IS NULL THEN v_invalid := 'not_confirmed'; END IF;
  END IF;

  v_state := public.entry_state(v_reg, v_pass, v_invalid);
  IF v_state->>'state' <> 'VALID' THEN
    RETURN v_state; -- nothing to reject: the scanner shows this state instead
  END IF;

  INSERT INTO public.entry_audit_log (action, actor, registration_id, pass_id, detail)
  VALUES ('reject', auth.uid(), v_reg, v_pass, jsonb_build_object(
    'reason', p_reason, 'note', v_note, 'method', CASE WHEN p_token IS NULL THEN 'manual' ELSE 'qr' END));
  RETURN jsonb_build_object('state', 'REJECTED', 'registration_id', v_state->>'registration_id', 'reason', p_reason);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Admin: undo a wrong check-in. Never deletes; the registration can then be checked in again.
CREATE OR REPLACE FUNCTION public.void_entry_checkin(p_checkin_id UUID, p_reason TEXT) RETURNS JSONB AS $$
DECLARE
  v_reason TEXT := btrim(COALESCE(p_reason, ''));
  v_reg UUID;
  v_pass UUID;
BEGIN
  PERFORM public.entry_require_admin();
  IF char_length(v_reason) NOT BETWEEN 1 AND 200 THEN
    RAISE EXCEPTION 'A reason (up to 200 characters) is required' USING ERRCODE = '22023';
  END IF;

  UPDATE public.entry_checkins
  SET voided_at = now(), voided_by = auth.uid(), void_reason = v_reason
  WHERE id = p_checkin_id AND voided_at IS NULL
  RETURNING registration_id, pass_id INTO v_reg, v_pass;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Check-in not found or already voided' USING ERRCODE = 'P0002';
  END IF;

  INSERT INTO public.entry_audit_log (action, actor, registration_id, pass_id, checkin_id, detail)
  VALUES ('void', auth.uid(), v_reg, v_pass, p_checkin_id, jsonb_build_object('reason', v_reason));
  RETURN jsonb_build_object('state', 'VOIDED', 'checkin_id', p_checkin_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Admin: new QR for a registration (lost or leaked pass); the old QR stops working at once. Blocked after check-in.
CREATE OR REPLACE FUNCTION public.reissue_entry_pass(p_registration UUID) RETURNS JSONB AS $$
DECLARE
  v_old UUID;
  v_new UUID;
BEGIN
  PERFORM public.entry_require_admin();
  IF NOT EXISTS (SELECT 1 FROM public.registrations WHERE id = p_registration AND status = 'confirmed') THEN
    RAISE EXCEPTION 'Only a confirmed registration has a pass' USING ERRCODE = 'P0002';
  END IF;

  -- FOR UPDATE waits for a check-in in progress on this pass, which the next statement then sees.
  SELECT id INTO v_old FROM public.entry_passes WHERE registration_id = p_registration AND status = 'active' FOR UPDATE;
  IF EXISTS (SELECT 1 FROM public.entry_checkins WHERE registration_id = p_registration AND voided_at IS NULL) THEN
    RAISE EXCEPTION 'Already checked in: the pass can no longer be reissued' USING ERRCODE = '55000';
  END IF;

  UPDATE public.entry_passes SET status = 'revoked', revoked_at = now(), revoke_reason = 'reissued' WHERE id = v_old;
  v_new := public.issue_entry_pass(p_registration);

  INSERT INTO public.entry_audit_log (action, actor, registration_id, pass_id, detail)
  VALUES ('reissue', auth.uid(), p_registration, v_new, jsonb_build_object('old_pass', v_old));
  RETURN jsonb_build_object('state', 'REISSUED', 'pass', v_new);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- ---- Called by POST /api/pass for the signed-in participant ----

-- The caller's own registration and pass. token_ciphertext is useless without the server's key.
CREATE OR REPLACE FUNCTION public.my_entry_pass() RETURNS JSONB AS $$
DECLARE
  r public.registrations%ROWTYPE;
  p public.entry_passes%ROWTYPE;
BEGIN
  IF auth.uid() IS NULL THEN
    RAISE EXCEPTION 'Sign in required' USING ERRCODE = '42501';
  END IF;
  SELECT * INTO r FROM public.registrations WHERE user_id = auth.uid() ORDER BY created_at DESC LIMIT 1;
  IF NOT FOUND THEN
    RETURN jsonb_build_object('registration_status', 'none', 'pass_status', 'none');
  END IF;
  SELECT * INTO p FROM public.entry_passes WHERE registration_id = r.id
  ORDER BY (status = 'active') DESC, issued_at DESC LIMIT 1;
  RETURN jsonb_build_object(
    'registration_status', r.status, 'registration_id', r.registration_id, 'name', r.name,
    'pass', p.id, 'pass_status', COALESCE(p.status, 'none'), 'token_ciphertext', p.token_ciphertext,
    'checked_in', EXISTS (SELECT 1 FROM public.entry_checkins WHERE registration_id = r.id AND voided_at IS NULL)
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;

-- Stores the token the server minted for the caller's own active pass, unless one is already stored (two tabs opening
-- the pass at once: the first wins and both get its ciphertext back).
CREATE OR REPLACE FUNCTION public.claim_entry_pass_token(p_pass UUID, p_hash TEXT, p_ciphertext TEXT) RETURNS TEXT AS $$
DECLARE
  v_cipher TEXT;
BEGIN
  IF auth.uid() IS NULL THEN
    RAISE EXCEPTION 'Sign in required' USING ERRCODE = '42501';
  END IF;
  IF p_hash IS NULL OR p_hash !~ '^[0-9a-f]{64}$' OR p_ciphertext IS NULL OR char_length(p_ciphertext) > 512 THEN
    RAISE EXCEPTION 'Invalid token data' USING ERRCODE = '22023';
  END IF;

  UPDATE public.entry_passes p SET token_hash = p_hash, token_ciphertext = p_ciphertext
  FROM public.registrations r
  WHERE p.id = p_pass AND p.status = 'active' AND p.token_hash IS NULL
    AND r.id = p.registration_id AND r.user_id = auth.uid() AND r.status = 'confirmed';

  SELECT p.token_ciphertext INTO v_cipher
  FROM public.entry_passes p JOIN public.registrations r ON r.id = p.registration_id
  WHERE p.id = p_pass AND p.status = 'active' AND r.user_id = auth.uid() AND r.status = 'confirmed';
  IF v_cipher IS NULL THEN
    RAISE EXCEPTION 'No active pass' USING ERRCODE = 'P0002';
  END IF;
  RETURN v_cipher;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;


-- Admin dashboard views. security_invoker: they run with the caller's rights, so the admin-only RLS on the entry
-- tables applies. Counts are per registration, over active passes and non-voided check-ins only.
DROP VIEW IF EXISTS public.entry_overview;
CREATE VIEW public.entry_overview WITH (security_invoker = true) AS
SELECT
  r.id AS registration, r.registration_id, r.name, r.enrollment_no, r.college,
  p.id AS pass_id, p.issued_at AS pass_issued_at, (p.token_hash IS NOT NULL) AS pass_opened,
  c.id AS checkin_id, c.created_at AS checked_in_at, c.desk, c.method, pr.full_name AS checked_in_by
FROM public.registrations r
LEFT JOIN public.entry_passes p ON p.registration_id = r.id AND p.status = 'active'
LEFT JOIN public.entry_checkins c ON c.registration_id = r.id AND c.voided_at IS NULL
LEFT JOIN public.profiles pr ON pr.id = c.checked_in_by
WHERE r.status = 'confirmed';

DROP VIEW IF EXISTS public.entry_checkin_feed;
CREATE VIEW public.entry_checkin_feed WITH (security_invoker = true) AS
SELECT
  c.id, c.created_at, c.desk, c.method, c.voided_at, c.void_reason,
  r.id AS registration, r.registration_id, r.name, r.enrollment_no,
  pr.full_name AS checked_in_by, pv.full_name AS voided_by
FROM public.entry_checkins c
JOIN public.registrations r ON r.id = c.registration_id
LEFT JOIN public.profiles pr ON pr.id = c.checked_in_by
LEFT JOIN public.profiles pv ON pv.id = c.voided_by;

DROP VIEW IF EXISTS public.entry_audit_feed;
CREATE VIEW public.entry_audit_feed WITH (security_invoker = true) AS
SELECT
  a.id, a.created_at, a.action, a.detail, a.registration_id AS registration, r.registration_id, r.name,
  pr.full_name AS actor_name, pr.email AS actor_email
FROM public.entry_audit_log a
LEFT JOIN public.registrations r ON r.id = a.registration_id
LEFT JOIN public.profiles pr ON pr.id = a.actor;

-- Admin dashboard tiles, from the same view (so they always agree with the list).
CREATE OR REPLACE FUNCTION public.entry_overview_counts() RETURNS JSONB AS $$
BEGIN
  PERFORM public.entry_require_admin();
  RETURN (
    SELECT jsonb_build_object(
      'confirmed', count(*), 'checked_in', count(checkin_id), 'not_checked_in', count(*) - count(checkin_id)
    )
    FROM public.entry_overview
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;

REVOKE ALL ON public.entry_overview, public.entry_checkin_feed, public.entry_audit_feed FROM anon;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
  ON public.entry_overview, public.entry_checkin_feed, public.entry_audit_feed FROM authenticated;
GRANT SELECT ON public.entry_overview, public.entry_checkin_feed, public.entry_audit_feed TO authenticated;

-- Function privileges: the internal helpers can't be called through the API by anyone; the entry points only by
-- signed-in users, and each one checks the caller's role itself.
REVOKE EXECUTE ON FUNCTION public.issue_entry_pass(UUID) FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.sync_entry_pass() FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.backfill_entry_passes() FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.entry_state(UUID, UUID, TEXT) FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.entry_resolve_token(TEXT) FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.entry_require_registration_staff() FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.entry_require_admin() FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.entry_check_in(UUID, UUID, TEXT, TEXT) FROM PUBLIC, anon, authenticated;

REVOKE EXECUTE ON FUNCTION public.lookup_entry_pass(TEXT) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.lookup_entry_manual(TEXT) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.check_in_entry_pass(TEXT, UUID, TEXT) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.check_in_entry_registration(UUID, UUID, TEXT) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.reject_entry_attempt(TEXT, UUID, TEXT, TEXT) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.void_entry_checkin(UUID, TEXT) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.reissue_entry_pass(UUID) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.my_entry_pass() FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.claim_entry_pass_token(UUID, TEXT, TEXT) FROM PUBLIC, anon;
REVOKE EXECUTE ON FUNCTION public.entry_overview_counts() FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.entry_overview_counts() TO authenticated;
GRANT EXECUTE ON FUNCTION public.lookup_entry_pass(TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION public.lookup_entry_manual(TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION public.check_in_entry_pass(TEXT, UUID, TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION public.check_in_entry_registration(UUID, UUID, TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION public.reject_entry_attempt(TEXT, UUID, TEXT, TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION public.void_entry_checkin(UUID, TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION public.reissue_entry_pass(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.my_entry_pass() TO authenticated;
GRANT EXECUTE ON FUNCTION public.claim_entry_pass_token(UUID, TEXT, TEXT) TO authenticated;


-- 14. REFERRALS
-- Every registration gets its own shareable referral_code when it is created (trg_assign_referral_code below); the
-- registrant sees it on their pass. Entering someone else's code while registering is optional: /api/register looks
-- it up and stores the referrer's registration in referred_by. A referral counts for the referrer once the referred
-- registration is confirmed (payment approved); pending ones are shown apart, rejected ones never count.
-- Both columns are fixed once written: staff reviewing through the API can only change status
-- (trg_enforce_registration_review copies every other column back from OLD).
-- Codes are 6 characters from an alphabet without look-alikes (no 0/O, no 1/I); the form upper-cases what is typed.
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS referral_code TEXT;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS referred_by UUID REFERENCES public.registrations(id) ON DELETE SET NULL;

CREATE OR REPLACE FUNCTION public.new_referral_code() RETURNS TEXT AS $$
DECLARE
  v_alphabet CONSTANT TEXT := 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
  v_bytes BYTEA;
  v_code TEXT;
BEGIN
  LOOP
    -- The first 6 bytes of a v4 UUID are all CSPRNG output, and 256 is a multiple of 32, so every character is uniform.
    v_bytes := uuid_send(gen_random_uuid());
    v_code := '';
    FOR i IN 0..5 LOOP
      v_code := v_code || substr(v_alphabet, 1 + get_byte(v_bytes, i) % 32, 1);
    END LOOP;
    EXIT WHEN NOT EXISTS (SELECT 1 FROM public.registrations WHERE referral_code = v_code);
  END LOOP;
  RETURN v_code;
END;
$$ LANGUAGE plpgsql VOLATILE SET search_path = public;

-- Always generated by the database, whoever inserts (a code in the insert is replaced). A trigger rather than a column
-- default: SECURITY DEFINER, so no API role needs EXECUTE on new_referral_code().
CREATE OR REPLACE FUNCTION public.assign_referral_code() RETURNS TRIGGER AS $$
BEGIN
  NEW.referral_code := public.new_referral_code();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_assign_referral_code ON public.registrations;
CREATE TRIGGER trg_assign_referral_code
  BEFORE INSERT ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.assign_referral_code();

-- Codes for registrations created before referrals existed. One row per statement, so each new code is checked
-- against the ones already handed out.
DO $$
DECLARE
  v_id UUID;
BEGIN
  FOR v_id IN SELECT id FROM public.registrations WHERE referral_code IS NULL ORDER BY created_at, id LOOP
    UPDATE public.registrations SET referral_code = public.new_referral_code() WHERE id = v_id;
  END LOOP;
END $$;

ALTER TABLE public.registrations ALTER COLUMN referral_code SET NOT NULL;

DO $$
BEGIN
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_referral_code_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_referral_code_check
    CHECK (referral_code ~ '^[A-HJ-NP-Z2-9]{6}$');
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_referred_by_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_referred_by_check
    CHECK (referred_by IS NULL OR referred_by <> id);
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS uq_registrations_referral_code ON public.registrations(referral_code);
CREATE INDEX IF NOT EXISTS idx_registrations_referred_by ON public.registrations(referred_by);

-- Leaderboard for the admin dashboard (admins and the registration team, through GET /api/admin/referrals).
-- One row per referrer with at least one confirmed or pending referral; a rejected referrer is left out.
-- rank counts confirmed referrals only (ties share a rank) and is computed over the whole board, before any filter
-- the API adds, so searching never renumbers it.
-- security_invoker: it runs with the caller's rights, so the registrations RLS decides what is counted (a regular
-- user sees nothing: they can't read the rows of the people who used their code).
DROP VIEW IF EXISTS public.referral_leaderboard;
CREATE VIEW public.referral_leaderboard WITH (security_invoker = true) AS
SELECT
  rank() OVER (ORDER BY c.confirmed DESC)::INT AS rank,
  r.id, r.registration_id, r.name, r.email, r.college, r.referral_code, r.status, r.created_at,
  c.confirmed::INT AS confirmed_referrals,
  c.pending::INT AS pending_referrals
FROM (
  SELECT
    referred_by,
    count(*) FILTER (WHERE status = 'confirmed') AS confirmed,
    count(*) FILTER (WHERE status = 'pending') AS pending
  FROM public.registrations
  WHERE referred_by IS NOT NULL
  GROUP BY referred_by
) c
JOIN public.registrations r ON r.id = c.referred_by
WHERE r.status <> 'rejected' AND c.confirmed + c.pending > 0;

REVOKE ALL ON public.referral_leaderboard FROM anon;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON public.referral_leaderboard FROM authenticated;
GRANT SELECT ON public.referral_leaderboard TO authenticated;

-- Internal: only the insert trigger and the backfill above use these.
REVOKE EXECUTE ON FUNCTION public.new_referral_code() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.assign_referral_code() FROM PUBLIC, anon, authenticated, service_role;


-- 15. PASS EXPERIENCE (durable one-time approval presentation)
-- Apply once before deploying the pass reveal. Also included in schema.sql for new installs.
-- No payment or registration fields are written by these functions.
CREATE TABLE IF NOT EXISTS public.pass_experiences (
  registration_id UUID PRIMARY KEY REFERENCES public.registrations(id) ON DELETE CASCADE,
  seen_at TIMESTAMPTZ,
  claim_id UUID,
  claim_until TIMESTAMPTZ
);
ALTER TABLE public.pass_experiences ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.pass_experiences FROM PUBLIC, anon, authenticated;

CREATE OR REPLACE FUNCTION public.claim_pass_experience(p_claim UUID) RETURNS TEXT AS $$
DECLARE
  r public.registrations%ROWTYPE;
  e public.pass_experiences%ROWTYPE;
BEGIN
  IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Sign in required' USING ERRCODE = '42501'; END IF;
  IF p_claim IS NULL THEN RAISE EXCEPTION 'Claim required' USING ERRCODE = '22023'; END IF;
  -- Lock the registration to serialize claims, acknowledgement and staff status changes.
  SELECT * INTO r FROM public.registrations WHERE user_id = auth.uid() ORDER BY created_at DESC LIMIT 1 FOR UPDATE;
  IF NOT FOUND OR r.status <> 'confirmed' OR NOT EXISTS (
    SELECT 1 FROM public.entry_passes WHERE registration_id = r.id AND status = 'active'
  ) THEN RETURN 'ineligible'; END IF;
  INSERT INTO public.pass_experiences(registration_id) VALUES (r.id) ON CONFLICT DO NOTHING;
  SELECT * INTO e FROM public.pass_experiences WHERE registration_id = r.id FOR UPDATE;
  IF e.seen_at IS NOT NULL THEN RETURN 'seen'; END IF;
  IF e.claim_until > clock_timestamp() THEN RETURN 'busy'; END IF;
  UPDATE public.pass_experiences SET claim_id = p_claim, claim_until = clock_timestamp() + interval '30 seconds'
    WHERE registration_id = r.id;
  RETURN 'claimed';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION public.acknowledge_pass_experience(p_claim UUID) RETURNS BOOLEAN AS $$
DECLARE
  r public.registrations%ROWTYPE;
BEGIN
  IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Sign in required' USING ERRCODE = '42501'; END IF;
  SELECT * INTO r FROM public.registrations WHERE user_id = auth.uid() ORDER BY created_at DESC LIMIT 1 FOR UPDATE;
  IF NOT FOUND OR r.status <> 'confirmed' THEN RETURN false; END IF;
  UPDATE public.pass_experiences SET seen_at = COALESCE(seen_at, clock_timestamp())
    WHERE registration_id = r.id AND claim_id = p_claim;
  RETURN FOUND;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

REVOKE ALL ON FUNCTION public.claim_pass_experience(UUID), public.acknowledge_pass_experience(UUID) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.claim_pass_experience(UUID), public.acknowledge_pass_experience(UUID) TO authenticated;


-- 16. CAMPUS AMBASSADORS
-- Any signed-in student can become a campus ambassador: GET MY CODE on the home page (POST /api/ambassador/code)
-- creates their row, matched on the verified email of their session, the first time they ask. The section's APPLY
-- button only opens the organisers' Google Form (src/config/ambassador.ts); nothing here depends on it.
-- Each ambassador gets one permanent code, 'CA' + 6 characters from the referral alphabet (shown as CA-K7QM2X), that
-- any number of registrants may enter in the registration form's referral field. An ambassador doesn't have to
-- register. If they do, their registration also gets its own 6-character referral_code (section 14), which keeps the
-- normal referral rules (it counts on the referral leaderboard, never here) and never affects their ambassador code.
--
-- A registration names at most one referrer: a registrant's code (referred_by) or an ambassador's (ambassador_id),
-- never both, never the registrant's own ambassador code and never a revoked one. ambassador_id never changes once
-- written, whoever writes (trg_guard_registration_ambassador: trg_enforce_registration_review only binds staff calling
-- the Supabase API, not the service role the app's routes use).
-- An ambassador's registrations count once confirmed (payment approved); pending ones are shown apart, rejected ones
-- never count. Staff can revoke an ambassador (abuse): their code is then refused for new registrations, and the ones
-- already made stay counted. An ambassador whose code has been used can't be deleted (ON DELETE RESTRICT).
--
-- No client role can read or write any of this, an ambassador's own row included: every access goes through server
-- routes that verify the caller first (service role): POST /api/ambassador/code, POST /api/register/check-code,
-- POST /api/register and the admin dashboard's AMBASSADORS tab (/api/admin/ambassadors, admins and the registration
-- team only).
CREATE TABLE IF NOT EXISTS public.campus_ambassadors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  college TEXT,
  code TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  user_id UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

DO $$
BEGIN
  ALTER TABLE public.campus_ambassadors DROP CONSTRAINT IF EXISTS campus_ambassadors_email_check;
  ALTER TABLE public.campus_ambassadors ADD CONSTRAINT campus_ambassadors_email_check
    CHECK (email = lower(email) AND char_length(email) <= 254 AND email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');
  ALTER TABLE public.campus_ambassadors DROP CONSTRAINT IF EXISTS campus_ambassadors_name_check;
  ALTER TABLE public.campus_ambassadors ADD CONSTRAINT campus_ambassadors_name_check
    CHECK (char_length(name) BETWEEN 1 AND 120);
  ALTER TABLE public.campus_ambassadors DROP CONSTRAINT IF EXISTS campus_ambassadors_college_check;
  ALTER TABLE public.campus_ambassadors ADD CONSTRAINT campus_ambassadors_college_check
    CHECK (college IS NULL OR char_length(college) <= 200);
  ALTER TABLE public.campus_ambassadors DROP CONSTRAINT IF EXISTS campus_ambassadors_code_check;
  ALTER TABLE public.campus_ambassadors ADD CONSTRAINT campus_ambassadors_code_check
    CHECK (code ~ '^CA[A-HJ-NP-Z2-9]{6}$');
  ALTER TABLE public.campus_ambassadors DROP CONSTRAINT IF EXISTS campus_ambassadors_status_check;
  ALTER TABLE public.campus_ambassadors ADD CONSTRAINT campus_ambassadors_status_check
    CHECK (status IN ('active', 'revoked'));
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS uq_campus_ambassadors_email ON public.campus_ambassadors(email);
CREATE UNIQUE INDEX IF NOT EXISTS uq_campus_ambassadors_code ON public.campus_ambassadors(code);
CREATE UNIQUE INDEX IF NOT EXISTS uq_campus_ambassadors_user_id ON public.campus_ambassadors(user_id);

CREATE OR REPLACE FUNCTION public.new_ambassador_code() RETURNS TEXT AS $$
DECLARE
  v_alphabet CONSTANT TEXT := 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
  v_bytes BYTEA;
  v_code TEXT;
BEGIN
  LOOP
    -- Built like new_referral_code() (section 14): 6 uniform characters from CSPRNG bytes, after the 'CA' prefix.
    v_bytes := uuid_send(gen_random_uuid());
    v_code := 'CA';
    FOR i IN 0..5 LOOP
      v_code := v_code || substr(v_alphabet, 1 + get_byte(v_bytes, i) % 32, 1);
    END LOOP;
    EXIT WHEN NOT EXISTS (SELECT 1 FROM public.campus_ambassadors WHERE code = v_code);
  END LOOP;
  RETURN v_code;
END;
$$ LANGUAGE plpgsql VOLATILE SET search_path = public;

-- The code is generated by the database on insert (a code sent with the insert is replaced) and never changes; the
-- email is stored trimmed and lower-cased. Changing the email unlinks the account that owned the old one.
CREATE OR REPLACE FUNCTION public.protect_campus_ambassador() RETURNS TRIGGER AS $$
BEGIN
  NEW.email := lower(btrim(NEW.email));
  IF TG_OP = 'INSERT' THEN
    NEW.code := public.new_ambassador_code();
    NEW.created_at := now();
  ELSE
    NEW.id := OLD.id;
    NEW.code := OLD.code;
    NEW.created_at := OLD.created_at;
    IF NEW.email IS DISTINCT FROM OLD.email THEN
      NEW.user_id := NULL;
    END IF;
  END IF;
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_protect_campus_ambassador ON public.campus_ambassadors;
CREATE TRIGGER trg_protect_campus_ambassador
  BEFORE INSERT OR UPDATE ON public.campus_ambassadors
  FOR EACH ROW EXECUTE FUNCTION public.protect_campus_ambassador();

ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS ambassador_id UUID
  REFERENCES public.campus_ambassadors(id) ON DELETE RESTRICT;

DO $$
BEGIN
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_one_referrer_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_one_referrer_check
    CHECK (referred_by IS NULL OR ambassador_id IS NULL);
END $$;

CREATE INDEX IF NOT EXISTS idx_registrations_ambassador_id ON public.registrations(ambassador_id);

-- On insert: the ambassador must be active and must not be the registrant (same email or the same account). The row
-- is locked while the registration is written, so a revoke running at the same moment waits for it.
-- On update: ambassador_id can't change, for every role.
-- /api/register checks the same rules first and shows these errors on the referral field.
CREATE OR REPLACE FUNCTION public.guard_registration_ambassador() RETURNS TRIGGER AS $$
DECLARE
  v_amb public.campus_ambassadors%ROWTYPE;
BEGIN
  IF TG_OP = 'UPDATE' THEN
    IF NEW.ambassador_id IS DISTINCT FROM OLD.ambassador_id THEN
      RAISE EXCEPTION 'The campus ambassador of registration % cannot be changed', OLD.registration_id
        USING ERRCODE = '42501';
    END IF;
    RETURN NEW;
  END IF;

  IF NEW.ambassador_id IS NOT NULL THEN
    SELECT * INTO v_amb FROM public.campus_ambassadors WHERE id = NEW.ambassador_id FOR SHARE;
    IF FOUND AND v_amb.status <> 'active' THEN
      RAISE EXCEPTION 'This campus ambassador code is no longer active' USING ERRCODE = '23514';
    END IF;
    IF FOUND AND (v_amb.email = lower(NEW.email) OR v_amb.user_id = NEW.user_id) THEN
      RAISE EXCEPTION 'A campus ambassador cannot use their own campus ambassador code' USING ERRCODE = '23514';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_guard_registration_ambassador ON public.registrations;
CREATE TRIGGER trg_guard_registration_ambassador
  BEFORE INSERT OR UPDATE ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.guard_registration_ambassador();

-- Staff leaderboard (GET /api/admin/ambassadors): one row per ambassador, revoked ones included but unranked.
-- rank counts confirmed registrations among active ambassadors (ties share a rank) and is computed over the whole
-- board, before any filter the API adds. No contact details: the API adds the email (this table) and the phone (the
-- ambassador's profile) for staff only.
-- security_invoker, and no privilege for any client role: only the service role (server routes) can read it.
DROP VIEW IF EXISTS public.ambassador_leaderboard;
CREATE VIEW public.ambassador_leaderboard WITH (security_invoker = true) AS
SELECT
  (CASE WHEN a.status = 'active'
    THEN rank() OVER (PARTITION BY a.status = 'active' ORDER BY COALESCE(c.confirmed, 0) DESC) END)::INT AS rank,
  a.id, a.code, a.name, a.college, a.status, a.created_at,
  COALESCE(c.confirmed, 0)::INT AS confirmed_registrations,
  COALESCE(c.pending, 0)::INT AS pending_registrations
FROM public.campus_ambassadors a
LEFT JOIN (
  SELECT
    ambassador_id,
    count(*) FILTER (WHERE status = 'confirmed') AS confirmed,
    count(*) FILTER (WHERE status = 'pending') AS pending
  FROM public.registrations
  WHERE ambassador_id IS NOT NULL
  GROUP BY ambassador_id
) c ON c.ambassador_id = a.id;

ALTER TABLE public.campus_ambassadors ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.campus_ambassadors FROM PUBLIC, anon, authenticated;
REVOKE ALL ON public.ambassador_leaderboard FROM PUBLIC, anon, authenticated;

-- Internal: only the triggers above use these.
REVOKE EXECUTE ON FUNCTION public.new_ambassador_code() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.protect_campus_ambassador() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.guard_registration_ambassador() FROM PUBLIC, anon, authenticated, service_role;


-- 17. SEAT COUNTER AND REGISTERING AGAIN AFTER A REJECTION
-- Seat counter: the registration form shows "<seats_left> / <total_seats> seats left" (public GET /api/seats). Admins
-- set both numbers in the admin dashboard (PATCH /api/admin/seats). The count then moves by itself: every new
-- registration takes a seat (pending ones too: they have already paid), a rejection gives it back, approving changes
-- nothing. It never goes below 0 or above total_seats. When it reaches 0 the form stops before the payment step; a
-- submit from someone who was already paying is still accepted (it leaves the count at 0).
-- One row (id = 1). No client role can read or write it: the routes above use the service role, and the trigger below
-- is SECURITY DEFINER, so the count moves whoever changes a registration (the app, the Supabase API or the SQL editor).
CREATE TABLE IF NOT EXISTS public.registration_counter (
  id SMALLINT PRIMARY KEY DEFAULT 1,
  total_seats INTEGER NOT NULL,
  seats_left INTEGER NOT NULL,
  -- The admin who last set the numbers by hand, and when (automatic moves don't change these).
  updated_by UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

DO $$
BEGIN
  ALTER TABLE public.registration_counter DROP CONSTRAINT IF EXISTS registration_counter_single_row_check;
  ALTER TABLE public.registration_counter ADD CONSTRAINT registration_counter_single_row_check CHECK (id = 1);
  -- 10000 matches MAX_SEATS in src/lib/seats.ts.
  ALTER TABLE public.registration_counter DROP CONSTRAINT IF EXISTS registration_counter_seats_check;
  ALTER TABLE public.registration_counter ADD CONSTRAINT registration_counter_seats_check
    CHECK (total_seats BETWEEN 0 AND 10000 AND seats_left BETWEEN 0 AND total_seats);
END $$;

-- First run: 1300 seats, less the registrations that already hold one. Re-running keeps the current numbers.
INSERT INTO public.registration_counter (id, total_seats, seats_left)
SELECT 1, 1300, GREATEST(1300 - count(*), 0)::INT
FROM public.registrations WHERE status IN ('pending', 'confirmed')
ON CONFLICT (id) DO NOTHING;

-- A registration holds a seat while it is pending or confirmed. The count moves when that changes: insert (-1),
-- rejection (+1), a rejection undone in the SQL editor (-1), a deleted registration that held a seat (+1).
CREATE OR REPLACE FUNCTION public.move_registration_counter() RETURNS TRIGGER AS $$
DECLARE
  v_held BOOLEAN := false;
  v_holds BOOLEAN := false;
BEGIN
  IF TG_OP <> 'INSERT' THEN
    v_held := COALESCE(OLD.status IN ('pending', 'confirmed'), false);
  END IF;
  IF TG_OP <> 'DELETE' THEN
    v_holds := COALESCE(NEW.status IN ('pending', 'confirmed'), false);
  END IF;
  IF v_holds AND NOT v_held THEN
    UPDATE public.registration_counter SET seats_left = GREATEST(seats_left - 1, 0) WHERE id = 1;
  ELSIF v_held AND NOT v_holds THEN
    UPDATE public.registration_counter SET seats_left = LEAST(seats_left + 1, total_seats) WHERE id = 1;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_move_registration_counter ON public.registrations;
CREATE TRIGGER trg_move_registration_counter
  AFTER INSERT OR UPDATE OF status OR DELETE ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.move_registration_counter();

ALTER TABLE public.registration_counter ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.registration_counter FROM PUBLIC, anon, authenticated;

-- Registering again: a rejected student may submit a new registration (uq_registrations_active_user_id, section 5,
-- only allows one pending or confirmed registration per user), up to 3 registrations in all: after a third rejection
-- the account can't register again. The rejected rows stay, with who rejected them and when.
-- Staff give a reason with every rejection made in the app (PATCH /api/admin/registrations); the student sees it on
-- their pass. Older rejections have none. The values match REJECTION_REASONS in src/lib/rejections.ts.
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS rejection_reason TEXT;

DO $$
BEGIN
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_rejection_reason_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_rejection_reason_check
    CHECK (rejection_reason IS NULL OR (
      status = 'rejected'
      AND rejection_reason IN ('unclear_screenshot', 'utr_mismatch', 'wrong_amount', 'duplicate_payment')
    ));
END $$;

-- Refuses a fourth registration, whoever inserts. 3 matches MAX_REGISTRATION_ATTEMPTS in src/lib/rejections.ts, and
-- /api/register looks for this message.
CREATE OR REPLACE FUNCTION public.guard_registration_attempts() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.user_id IS NOT NULL
     AND (SELECT count(*) FROM public.registrations WHERE user_id = NEW.user_id AND status = 'rejected') >= 3 THEN
    RAISE EXCEPTION 'Registration attempts used up: this account has been rejected 3 times' USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_guard_registration_attempts ON public.registrations;
CREATE TRIGGER trg_guard_registration_attempts
  BEFORE INSERT ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.guard_registration_attempts();

-- Internal: only the triggers above use these.
REVOKE EXECUTE ON FUNCTION public.move_registration_counter() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.guard_registration_attempts() FROM PUBLIC, anon, authenticated, service_role;


-- ==============================================================================
-- 18. NO ITER - SOA REGISTRATIONS
-- ==============================================================================
-- Students of ITER (Institute of Technical Education and Research) and SOA (Siksha 'O' Anusandhan) may not register.
-- True for any spelling of either name, whatever the case or punctuation: ITER, I.T.E.R., iter-soa, SOA University,
-- Institute of Technical Education & Research, Shiksha O Anushandhan... Must match isIterSoaCollege in
-- src/lib/validation.ts.
CREATE OR REPLACE FUNCTION public.is_iter_soa_college(p_college TEXT) RETURNS BOOLEAN AS $$
DECLARE
  v_spaced TEXT;
BEGIN
  IF p_college IS NULL THEN
    RETURN FALSE;
  END IF;
  -- "I.T.E.R., S.O.A" -> "i t e r s o a": accents and full-width letters folded, everything but a-z and 0-9 a space.
  v_spaced := btrim(regexp_replace(
    lower(regexp_replace(normalize(p_college, NFKD), '[\u0300-\u036f]', '', 'g')) COLLATE "C",
    '[^a-z0-9]+', ' ', 'g'
  ));
  RETURN v_spaced ~ '(^| )(iter|soa|soau|i t e r|s o a|s o a u)( |$)'
      OR replace(v_spaced, ' ', '') ~ '(itersoa|soaiter|technicaleducationa?n?d?research|sh?ik?s?h?a+o?a*nush?andh?a+n)';
END;
$$ LANGUAGE plpgsql IMMUTABLE SET search_path = public;

-- Refuses a registration from ITER - SOA, by its college or by an ITER / SOA email (@soa.ac.in, @iter.ac.in and their
-- subdomains, as isIterSoaEmail), whoever inserts: /api/register (service role) included. Registrations made before
-- the ban stay, and staff can still confirm or reject them: only a new row, or a change to its college or email, is
-- checked. /api/register looks for this message.
CREATE OR REPLACE FUNCTION public.refuse_iter_soa_registration() RETURNS TRIGGER AS $$
BEGIN
  IF public.is_iter_soa_college(NEW.college) OR lower(btrim(COALESCE(NEW.email, ''))) ~ '@([a-z0-9-]+\.)*(soa|iter)\.' THEN
    RAISE EXCEPTION 'ITER / SOA registrations are not allowed' USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SET search_path = public;

DROP TRIGGER IF EXISTS trg_refuse_iter_soa_registration ON public.registrations;
CREATE TRIGGER trg_refuse_iter_soa_registration
  BEFORE INSERT OR UPDATE OF college, email ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.refuse_iter_soa_registration();

-- Internal: only the trigger above uses it.
REVOKE EXECUTE ON FUNCTION public.refuse_iter_soa_registration() FROM PUBLIC, anon, authenticated, service_role;


-- ==============================================================================
-- 19. COLLEGE ID CARDS
-- ==============================================================================
-- Every registration carries a photo of the front of the registrant's college ID card, in the PRIVATE 'id-cards'
-- bucket (same rules as payment proofs, section 12: written only by the server with the service role, named
-- '<user_id>/<uuid>.<ext>', no public URLs, staff view it through short-lived signed URLs).
-- New registrations upload it in the form (/api/register requires it). Registrations made before this section have
-- none: the student uploads it from MY PASS (POST /api/id-card), even after registrations close.
-- Staff can't confirm a registration without one (trg_require_id_card_for_approval). A card can't be swapped by the
-- student; staff may ask for a new one (POST /api/admin/registrations/id-card), which clears id_card_path and stores
-- why in id_card_reupload_reason (values match ID_CARD_REUPLOAD_REASONS in src/lib/id-cards.ts). The old file stays.
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS id_card_path TEXT;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS id_card_uploaded_at TIMESTAMPTZ;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS id_card_reupload_reason TEXT;

DO $$
BEGIN
  -- Like registrations_payment_proof_path_check: a path inside the registrant's OWN folder, never a URL.
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_id_card_path_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_id_card_path_check
    CHECK (id_card_path IS NULL OR (
      id_card_path ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}/[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\.(jpg|png|webp|heic)$'
      AND split_part(id_card_path, '/', 1) = user_id::text
    ));
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_id_card_reupload_reason_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_id_card_reupload_reason_check
    CHECK (id_card_reupload_reason IS NULL OR (
      id_card_path IS NULL
      AND id_card_reupload_reason IN ('unclear_photo', 'not_an_id_card', 'details_mismatch')
    ));
END $$;

-- Refuses confirming a registration that has no ID card, whoever updates: the service role included.
-- /api/admin/registrations looks for this message.
CREATE OR REPLACE FUNCTION public.require_id_card_for_approval() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'confirmed' AND OLD.status IS DISTINCT FROM 'confirmed' AND NEW.id_card_path IS NULL THEN
    RAISE EXCEPTION 'ID card required before approval' USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SET search_path = public;

DROP TRIGGER IF EXISTS trg_require_id_card_for_approval ON public.registrations;
CREATE TRIGGER trg_require_id_card_for_approval
  BEFORE UPDATE OF status ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.require_id_card_for_approval();

-- Internal: only the trigger above uses it.
REVOKE EXECUTE ON FUNCTION public.require_id_card_for_approval() FROM PUBLIC, anon, authenticated, service_role;

-- The private bucket. Re-running this script also forces it back to private. 2 MiB: the form shrinks large photos
-- before uploading (src/lib/compress-image.ts); must match ID_CARD_MAX_BYTES in src/lib/security.ts.
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES ('id-cards', 'id-cards', false, 2097152,
        ARRAY['image/jpeg', 'image/png', 'image/webp', 'image/heic'])
ON CONFLICT (id) DO UPDATE SET
  public = false,
  file_size_limit = EXCLUDED.file_size_limit,
  allowed_mime_types = EXCLUDED.allowed_mime_types;

-- No INSERT, UPDATE or DELETE policy for users: only /api/id-card writes, with the service role, after its checks.
-- Owners may see their own files (used by /api/register to confirm the upload exists). No staff policy: staff get
-- short-lived signed URLs from /api/admin/registrations/id-card.
DROP POLICY IF EXISTS "ID cards: owners read their own files" ON storage.objects;
CREATE POLICY "ID cards: owners read their own files" ON storage.objects FOR
SELECT TO authenticated USING (
  bucket_id = 'id-cards'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

-- Guard rail, as for payment proofs: even a broad storage policy added later can't expose another user's ID card.
DROP POLICY IF EXISTS "ID cards: never outside the owner's folder" ON storage.objects;
CREATE POLICY "ID cards: never outside the owner's folder" ON storage.objects AS RESTRICTIVE FOR
ALL TO anon, authenticated
USING (bucket_id <> 'id-cards' OR (storage.foldername(name))[1] = auth.uid()::text)
WITH CHECK (bucket_id <> 'id-cards' OR (storage.foldername(name))[1] = auth.uid()::text);


-- ==============================================================================
-- 20. REGISTRATION PHASES
-- ==============================================================================
-- Registrations sell in phases, one at a time. registration_counter holds the current phase and its fee. A phase is
-- open until closed_at is set, which happens by itself the moment seats_left reaches 0, however it got there. Only
-- start_registration_phase() (an admin, POST /api/admin/seats/phase) opens the next one: editing the numbers never does.
ALTER TABLE public.registration_counter ADD COLUMN IF NOT EXISTS phase INTEGER NOT NULL DEFAULT 1;
ALTER TABLE public.registration_counter ADD COLUMN IF NOT EXISTS fee_inr INTEGER NOT NULL DEFAULT 999;
ALTER TABLE public.registration_counter ADD COLUMN IF NOT EXISTS closed_at TIMESTAMPTZ;

-- phase: the phase whose fee it was charged at. submitted_late: submitted after its phase closed and after the time
-- allowed for paying, so staff must check the payment date against pay_by on the bank statement. allowed_by: the staff
-- member who allowed it through the helpdesk. amount: the full fee owed. amount_due: the part still to be paid (asked
-- for by staff when someone paid too little: there are no refunds, so they pay the difference rather than the whole
-- fee again), and top_up_utr is the UTR of that second payment.
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS phase INTEGER NOT NULL DEFAULT 1;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS submitted_late BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS pay_by TIMESTAMPTZ;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS allowed_by UUID REFERENCES public.profiles(id) ON DELETE SET NULL;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS amount_due INTEGER NOT NULL DEFAULT 0;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS top_up_utr TEXT;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS top_up_recorded_by UUID REFERENCES public.profiles(id) ON DELETE SET NULL;
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS top_up_recorded_at TIMESTAMPTZ;

DO $$
BEGIN
  -- 100000 matches MAX_FEE in src/lib/seats.ts.
  ALTER TABLE public.registration_counter DROP CONSTRAINT IF EXISTS registration_counter_phase_check;
  ALTER TABLE public.registration_counter ADD CONSTRAINT registration_counter_phase_check
    CHECK (phase >= 1 AND fee_inr BETWEEN 1 AND 100000);
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_amount_due_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_amount_due_check
    CHECK (amount_due BETWEEN 0 AND 100000);
  ALTER TABLE public.registrations DROP CONSTRAINT IF EXISTS registrations_top_up_utr_check;
  ALTER TABLE public.registrations ADD CONSTRAINT registrations_top_up_utr_check
    CHECK (top_up_utr IS NULL OR top_up_utr ~ '^[0-9]{12}$');
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS uq_registrations_top_up_utr ON public.registrations(top_up_utr)
  WHERE top_up_utr IS NOT NULL;

-- Closes the phase the moment the count reaches 0: in the same statement, so simultaneous registrations can't slip past.
CREATE OR REPLACE FUNCTION public.close_registration_phase_at_zero() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.seats_left = 0 AND NEW.closed_at IS NULL THEN
    NEW.closed_at := now();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SET search_path = public;

DROP TRIGGER IF EXISTS trg_close_registration_phase_at_zero ON public.registration_counter;
CREATE TRIGGER trg_close_registration_phase_at_zero
  BEFORE INSERT OR UPDATE OF seats_left ON public.registration_counter
  FOR EACH ROW EXECUTE FUNCTION public.close_registration_phase_at_zero();

UPDATE public.registration_counter SET closed_at = now() WHERE seats_left = 0 AND closed_at IS NULL;

-- Replaces section 17's version. The count only moves for a registration of the current phase while that phase is open:
-- a rejection after closing can't reopen sales, and a late or older-phase registration doesn't touch the new phase's
-- count. The WHERE clause does the check, so it holds even if a phase closes at the same moment.
CREATE OR REPLACE FUNCTION public.move_registration_counter() RETURNS TRIGGER AS $$
DECLARE
  v_held BOOLEAN := false;
  v_holds BOOLEAN := false;
  v_phase INTEGER;
BEGIN
  IF TG_OP <> 'INSERT' THEN
    v_held := COALESCE(OLD.status IN ('pending', 'confirmed'), false);
    v_phase := OLD.phase;
  END IF;
  IF TG_OP <> 'DELETE' THEN
    v_holds := COALESCE(NEW.status IN ('pending', 'confirmed'), false);
    v_phase := NEW.phase;
  END IF;
  IF v_holds AND NOT v_held THEN
    UPDATE public.registration_counter SET seats_left = GREATEST(seats_left - 1, 0)
    WHERE id = 1 AND phase = v_phase AND closed_at IS NULL;
  ELSIF v_held AND NOT v_holds THEN
    UPDATE public.registration_counter SET seats_left = LEAST(seats_left + 1, total_seats)
    WHERE id = 1 AND phase = v_phase AND closed_at IS NULL;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Replaces section 5's version: the fee is the current phase's, and the phase columns can't be chosen by the inserter.
CREATE OR REPLACE FUNCTION public.enforce_registration_insert() RETURNS TRIGGER AS $$
DECLARE
  v_email TEXT;
  v_confirmed TIMESTAMPTZ;
BEGIN
  IF COALESCE(auth.role(), '') <> 'authenticated' THEN
    RETURN NEW;
  END IF;

  SELECT lower(email), email_confirmed_at INTO v_email, v_confirmed FROM auth.users WHERE id = auth.uid();

  NEW.user_id := auth.uid();
  NEW.email := COALESCE(v_email, '');
  NEW.registration_id := 'IV26-' || (1000 + (('x' || substr(md5(gen_random_uuid()::text), 1, 7))::bit(28)::int % 9000))::text;
  NEW.reviewed_by := NULL;
  NEW.reviewed_at := NULL;
  NEW.student_type := CASE WHEN v_email LIKE '%@nitrkl.ac.in' AND v_confirmed IS NOT NULL THEN 'internal' ELSE 'external' END;
  NEW.status := 'pending';
  SELECT fee_inr, phase INTO NEW.amount, NEW.phase FROM public.registration_counter WHERE id = 1;
  NEW.submitted_late := false;
  NEW.pay_by := NULL;
  NEW.allowed_by := NULL;
  NEW.amount_due := 0;
  NEW.top_up_utr := NULL;
  NEW.top_up_recorded_by := NULL;
  NEW.top_up_recorded_at := NULL;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Refuses approval while part of the fee is still due, whoever updates: the service role included.
-- /api/admin/registrations looks for this message.
CREATE OR REPLACE FUNCTION public.require_amount_paid_for_approval() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'confirmed' AND OLD.status IS DISTINCT FROM 'confirmed' AND NEW.amount_due > 0 THEN
    RAISE EXCEPTION 'Amount due before approval' USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SET search_path = public;

DROP TRIGGER IF EXISTS trg_require_amount_paid_for_approval ON public.registrations;
CREATE TRIGGER trg_require_amount_paid_for_approval
  BEFORE UPDATE OF status ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.require_amount_paid_for_approval();

-- One UPI transaction pays for one thing: a top-up's UTR can't be any registration's UTR, in either direction. The same
-- code and wording as uq_registrations_utr, so /api/register answers it the same way.
CREATE OR REPLACE FUNCTION public.guard_top_up_utr() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.top_up_utr IS NOT NULL AND (
    NEW.top_up_utr = NEW.utr
    OR EXISTS (SELECT 1 FROM public.registrations r WHERE r.utr = NEW.top_up_utr AND r.status <> 'rejected' AND r.id <> NEW.id)
  ) THEN
    RAISE EXCEPTION 'This utr is already used by a registration' USING ERRCODE = '23505';
  END IF;
  IF NEW.utr IS NOT NULL
     AND EXISTS (SELECT 1 FROM public.registrations r WHERE r.top_up_utr = NEW.utr AND r.id <> NEW.id) THEN
    RAISE EXCEPTION 'This utr is already used by a top-up' USING ERRCODE = '23505';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SET search_path = public;

DROP TRIGGER IF EXISTS trg_guard_top_up_utr ON public.registrations;
CREATE TRIGGER trg_guard_top_up_utr
  BEFORE INSERT OR UPDATE OF utr, top_up_utr ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.guard_top_up_utr();

-- A payment pass is the server's record of the fee quoted to a student. The form shows payment details only with a pass
-- and until pay_by; the student may submit at that fee until submit_by, even after the phase closes or the fee rises.
-- issued_by is set when staff allowed a payment through the helpdesk (POST /api/admin/registrations/payment-pass); note
-- says why. used_at is set once it is turned into a registration, or replaced by a newer pass. Only the server reads
-- and writes these.
CREATE TABLE IF NOT EXISTS public.payment_passes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  phase INTEGER NOT NULL,
  amount INTEGER NOT NULL CHECK (amount BETWEEN 1 AND 100000),
  pay_by TIMESTAMPTZ NOT NULL,
  submit_by TIMESTAMPTZ NOT NULL,
  issued_by UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
  note TEXT CHECK (note IS NULL OR char_length(note) <= 300),
  used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_payment_passes_user ON public.payment_passes(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_payment_passes_in_progress ON public.payment_passes(pay_by) WHERE used_at IS NULL;

ALTER TABLE public.payment_passes ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.payment_passes FROM PUBLIC, anon, authenticated;
GRANT ALL ON public.payment_passes TO service_role;

-- The registration a student may re-register from without paying again: their latest, rejected for a reason that doesn't
-- question the payment itself, at a fee that isn't the current open one (otherwise they just get the current fee).
CREATE OR REPLACE FUNCTION public.inherited_registration_payment(p_user_id UUID, p_counter public.registration_counter)
RETURNS public.registrations AS $$
DECLARE
  r public.registrations%ROWTYPE;
BEGIN
  SELECT * INTO r FROM public.registrations WHERE user_id = p_user_id ORDER BY created_at DESC LIMIT 1;
  IF FOUND AND r.status = 'rejected' AND r.rejection_reason IN ('unclear_screenshot', 'utr_mismatch') AND r.amount > 0
     AND (p_counter.closed_at IS NOT NULL OR r.phase <> p_counter.phase) THEN
    RETURN r;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- POST /api/register/start-payment. Answers:
--   state 'pay': a pass, show the payment details until pay_by.
--   state 'already_paid': a pass, but the time to pay has passed: no payment details, only "already paid? continue".
--     current_fee is set when a phase is open, so the student can pay that instead.
--   state 'closed': no pass and no phase open.
-- p_fresh: the student hasn't paid and wants the current fee, replacing any older pass. Ignored while closed.
-- FOR SHARE orders this against a registration closing the phase: a pass issued a moment before closing stands.
CREATE OR REPLACE FUNCTION public.issue_payment_pass(p_user_id UUID, p_fresh BOOLEAN DEFAULT false)
RETURNS JSONB AS $$
DECLARE
  c public.registration_counter%ROWTYPE;
  p public.payment_passes%ROWTYPE;
  r public.registrations%ROWTYPE;
  v_open BOOLEAN;
  v_state TEXT := 'pay';
BEGIN
  SELECT * INTO c FROM public.registration_counter WHERE id = 1 FOR SHARE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'registration_counter has no row';
  END IF;
  v_open := c.closed_at IS NULL;

  IF NOT (p_fresh AND v_open) THEN
    SELECT * INTO p FROM public.payment_passes
    WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now()
    ORDER BY created_at DESC LIMIT 1;
    IF FOUND THEN
      IF p.pay_by <= now() THEN
        v_state := 'already_paid';
      END IF;
      -- The student's own pass in the phase still open simply renews below, at the same fee.
      IF p.pay_by > now() OR p.issued_by IS NOT NULL OR NOT v_open OR p.phase <> c.phase THEN
        RETURN jsonb_build_object(
          'state', v_state, 'amount', p.amount, 'phase', p.phase,
          'pay_seconds', GREATEST(EXTRACT(EPOCH FROM p.pay_by - now()), 0)::INT,
          'current_fee', CASE WHEN v_open THEN c.fee_inr END);
      END IF;
    ELSE
      r := public.inherited_registration_payment(p_user_id, c);
      IF r.id IS NOT NULL THEN
        INSERT INTO public.payment_passes (user_id, phase, amount, pay_by, submit_by)
        VALUES (p_user_id, r.phase, r.amount::INT, now(), now() + interval '24 hours')
        RETURNING * INTO p;
        RETURN jsonb_build_object(
          'state', 'already_paid', 'amount', p.amount, 'phase', p.phase, 'pay_seconds', 0,
          'current_fee', CASE WHEN v_open THEN c.fee_inr END);
      END IF;
    END IF;
  END IF;

  IF NOT v_open THEN
    RETURN jsonb_build_object('state', 'closed');
  END IF;

  UPDATE public.payment_passes SET used_at = now() WHERE user_id = p_user_id AND used_at IS NULL;
  -- 15 minutes to pay, 24 hours to submit. After that: the current fee, or the helpdesk.
  INSERT INTO public.payment_passes (user_id, phase, amount, pay_by, submit_by)
  VALUES (p_user_id, c.phase, c.fee_inr, now() + interval '15 minutes', now() + interval '24 hours')
  RETURNING * INTO p;
  RETURN jsonb_build_object(
    'state', 'pay', 'amount', p.amount, 'phase', p.phase,
    'pay_seconds', GREATEST(EXTRACT(EPOCH FROM p.pay_by - now()), 0)::INT,
    'current_fee', c.fee_inr);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- POST /api/register: what this submit is charged and whether it may go ahead at all. Never reads the request: a pass
-- first, then a payment re-submitted after a rejection, then the current fee if a phase is open. Otherwise refused.
CREATE OR REPLACE FUNCTION public.resolve_registration_payment(p_user_id UUID)
RETURNS JSONB AS $$
DECLARE
  c public.registration_counter%ROWTYPE;
  p public.payment_passes%ROWTYPE;
  r public.registrations%ROWTYPE;
BEGIN
  SELECT * INTO c FROM public.registration_counter WHERE id = 1 FOR SHARE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'registration_counter has no row';
  END IF;

  SELECT * INTO p FROM public.payment_passes
  WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now()
  ORDER BY created_at DESC LIMIT 1;
  IF FOUND THEN
    RETURN jsonb_build_object(
      'allowed', true, 'amount', p.amount, 'phase', p.phase, 'pass_id', p.id, 'pay_by', p.pay_by,
      'allowed_by', p.issued_by,
      'late', (c.closed_at IS NOT NULL OR p.phase <> c.phase) AND now() > p.pay_by);
  END IF;

  r := public.inherited_registration_payment(p_user_id, c);
  IF r.id IS NOT NULL THEN
    RETURN jsonb_build_object('allowed', true, 'amount', r.amount, 'phase', r.phase, 'late', true);
  END IF;

  IF c.closed_at IS NULL THEN
    RETURN jsonb_build_object('allowed', true, 'amount', c.fee_inr, 'phase', c.phase, 'late', false);
  END IF;
  RETURN jsonb_build_object('allowed', false);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- POST /api/admin/seats/phase. The only way to reopen. Refused while a phase is open, so a double click or a stale
-- screen can't skip a phase or change the fee under students who are paying.
CREATE OR REPLACE FUNCTION public.start_registration_phase(p_fee INTEGER, p_total INTEGER, p_left INTEGER, p_admin UUID)
RETURNS VOID AS $$
DECLARE
  c public.registration_counter%ROWTYPE;
BEGIN
  SELECT * INTO c FROM public.registration_counter WHERE id = 1 FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'registration_counter has no row';
  END IF;
  IF c.closed_at IS NULL THEN
    RAISE EXCEPTION 'Phase % is still open', c.phase USING ERRCODE = '55000';
  END IF;
  IF p_left < 1 THEN
    RAISE EXCEPTION 'A new phase needs at least one seat' USING ERRCODE = '23514';
  END IF;
  UPDATE public.registration_counter
  SET phase = c.phase + 1, fee_inr = p_fee, total_seats = p_total, seats_left = p_left, closed_at = NULL,
      updated_by = p_admin, updated_at = now()
  WHERE id = 1;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Only the server may call these; the triggers need no grant.
REVOKE EXECUTE ON FUNCTION public.close_registration_phase_at_zero() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.require_amount_paid_for_approval() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.guard_top_up_utr() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.inherited_registration_payment(UUID, public.registration_counter) FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.issue_payment_pass(UUID, BOOLEAN) FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.resolve_registration_payment(UUID) FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.start_registration_phase(INTEGER, INTEGER, INTEGER, UUID) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.issue_payment_pass(UUID, BOOLEAN) TO service_role;
GRANT EXECUTE ON FUNCTION public.resolve_registration_payment(UUID) TO service_role;
GRANT EXECUTE ON FUNCTION public.start_registration_phase(INTEGER, INTEGER, INTEGER, UUID) TO service_role;


-- ==============================================================================
-- 21. FEMALE SEAT CAP
-- ==============================================================================
-- Female registrations that are pending or confirmed, in any phase, count towards registration_counter.female_cap.
-- Others doesn't count towards it, but is closed along with Female at the cap. Separate from the seat counter: Male
-- registrations are never affected. A rejection frees the seat. Only the server reads and writes these.
ALTER TABLE public.registration_counter ADD COLUMN IF NOT EXISTS female_cap INTEGER NOT NULL DEFAULT 350;

-- The gender a payment pass was issued for. Set only while Female and Others were open to this student: 'female' holds a
-- seat until pay_by, and 'female' or 'others' lets the student submit at the cap until submit_by.
ALTER TABLE public.payment_passes ADD COLUMN IF NOT EXISTS gender TEXT;

DO $$
BEGIN
  ALTER TABLE public.registration_counter DROP CONSTRAINT IF EXISTS registration_counter_female_cap_check;
  ALTER TABLE public.registration_counter ADD CONSTRAINT registration_counter_female_cap_check
    CHECK (female_cap BETWEEN 0 AND 10000);
  ALTER TABLE public.payment_passes DROP CONSTRAINT IF EXISTS payment_passes_gender_check;
  ALTER TABLE public.payment_passes ADD CONSTRAINT payment_passes_gender_check
    CHECK (gender IS NULL OR gender IN ('male', 'female', 'others'));
END $$;

-- Seats taken: female registrations, plus female students on the payment step (without this hold, everyone paying at 349
-- would get in). p_user_id leaves out that student's own hold.
CREATE OR REPLACE FUNCTION public.female_seats_taken(p_user_id UUID DEFAULT NULL)
RETURNS INTEGER AS $$
  SELECT (SELECT count(*) FROM public.registrations WHERE gender = 'female' AND status IN ('pending', 'confirmed'))::INT
       + (SELECT count(*) FROM public.payment_passes
          WHERE gender = 'female' AND used_at IS NULL AND pay_by > now() AND user_id IS DISTINCT FROM p_user_id)::INT;
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public;

-- Who may still choose Female or Others at the cap, because they may already have paid: a live pass issued for one of
-- them before the cap, a payment staff allowed, or a registration rejected for a reason that doesn't question the payment.
CREATE OR REPLACE FUNCTION public.female_seat_kept(p_user_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.payment_passes
    WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now()
      AND (issued_by IS NOT NULL OR gender IN ('female', 'others'))
  ) OR COALESCE((
    SELECT r.status = 'rejected' AND r.gender IN ('female', 'others')
      AND r.rejection_reason IN ('unclear_screenshot', 'utr_mismatch') AND r.amount > 0
    FROM public.registrations r WHERE r.user_id = p_user_id ORDER BY r.created_at DESC LIMIT 1
  ), false);
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public;

-- GET /api/seats, GET /api/admin/seats and GET /api/register/female-access.
CREATE OR REPLACE FUNCTION public.female_seats_status(p_user_id UUID DEFAULT NULL)
RETURNS JSONB AS $$
  SELECT jsonb_build_object(
    'cap', c.female_cap,
    'registered', (SELECT count(*) FROM public.registrations WHERE gender = 'female' AND status IN ('pending', 'confirmed')),
    'full', public.female_seats_taken() >= c.female_cap,
    'keeps_access', p_user_id IS NOT NULL AND public.female_seat_kept(p_user_id))
  FROM public.registration_counter c WHERE c.id = 1;
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public;

-- POST /api/register/start-payment: issue_payment_pass (section 20) with the cap in front of it. Adds one answer:
--   state 'female_full': no pass and no payment details for this gender.
-- FOR UPDATE, for Female and Others only, makes students reaching for the last seat take turns.
CREATE OR REPLACE FUNCTION public.start_payment_for_gender(p_user_id UUID, p_fresh BOOLEAN, p_gender TEXT)
RETURNS JSONB AS $$
DECLARE
  c public.registration_counter%ROWTYPE;
  p public.payment_passes%ROWTYPE;
  r public.registrations%ROWTYPE;
  v_capped BOOLEAN := COALESCE(p_gender IN ('female', 'others'), false);
  v_result JSONB;
BEGIN
  IF v_capped THEN
    SELECT * INTO c FROM public.registration_counter WHERE id = 1 FOR UPDATE;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'registration_counter has no row';
    END IF;
  END IF;

  SELECT * INTO p FROM public.payment_passes
  WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now()
  ORDER BY created_at DESC LIMIT 1;

  IF v_capped AND public.female_seats_taken(p_user_id) >= c.female_cap THEN
    IF public.female_seat_kept(p_user_id) THEN
      -- "I haven't paid" while the seats are full: nothing to honour.
      IF p_fresh AND c.closed_at IS NULL THEN
        RETURN jsonb_build_object('state', 'female_full');
      END IF;
      -- May already have paid, so they may continue with their proof, but aren't shown a way to pay again.
      IF p.id IS NOT NULL AND p.pay_by <= now() AND p.issued_by IS NULL THEN
        RETURN jsonb_build_object('state', 'already_paid', 'amount', p.amount, 'phase', p.phase, 'pay_seconds', 0, 'current_fee', NULL);
      END IF;
      IF p.id IS NULL THEN
        SELECT * INTO r FROM public.registrations WHERE user_id = p_user_id ORDER BY created_at DESC LIMIT 1;
        INSERT INTO public.payment_passes (user_id, phase, amount, pay_by, submit_by, gender)
        VALUES (p_user_id, r.phase, r.amount::INT, now(), now() + interval '24 hours', p_gender)
        RETURNING * INTO p;
        RETURN jsonb_build_object('state', 'already_paid', 'amount', p.amount, 'phase', p.phase, 'pay_seconds', 0, 'current_fee', NULL);
      END IF;
    -- With no phase open and no pass, the closed answer below is the one that matters.
    ELSIF c.closed_at IS NULL OR p.id IS NOT NULL THEN
      RETURN jsonb_build_object('state', 'female_full');
    END IF;
  END IF;

  v_result := public.issue_payment_pass(p_user_id, p_fresh);

  IF v_capped THEN
    UPDATE public.payment_passes SET gender = p_gender
    WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now();
  -- Another gender chosen on a renewed pass: a student who paid and picked wrongly must still be able to go back.
  ELSIF NOT p_fresh AND p.gender IN ('female', 'others') THEN
    UPDATE public.payment_passes SET gender = p.gender
    WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now() AND gender IS NULL;
  END IF;
  RETURN v_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- The last word, whoever inserts. /api/register looks for this message.
CREATE OR REPLACE FUNCTION public.guard_female_seats() RETURNS TRIGGER AS $$
DECLARE
  v_cap INTEGER;
BEGIN
  IF NEW.user_id IS NULL OR NEW.gender IS NULL OR NEW.gender NOT IN ('female', 'others')
     OR NEW.status NOT IN ('pending', 'confirmed') THEN
    RETURN NEW;
  END IF;
  SELECT female_cap INTO v_cap FROM public.registration_counter WHERE id = 1 FOR UPDATE;
  IF public.female_seats_taken(NEW.user_id) >= v_cap AND NOT public.female_seat_kept(NEW.user_id) THEN
    RAISE EXCEPTION 'Female and Others seats are full' USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_guard_female_seats ON public.registrations;
CREATE TRIGGER trg_guard_female_seats
  BEFORE INSERT ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.guard_female_seats();

-- Only the server may call these; the helpers and the trigger need no grant.
REVOKE EXECUTE ON FUNCTION public.guard_female_seats() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.female_seats_taken(UUID) FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.female_seat_kept(UUID) FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.female_seats_status(UUID) FROM PUBLIC, anon, authenticated;
REVOKE EXECUTE ON FUNCTION public.start_payment_for_gender(UUID, BOOLEAN, TEXT) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.female_seats_status(UUID) TO service_role;
GRANT EXECUTE ON FUNCTION public.start_payment_for_gender(UUID, BOOLEAN, TEXT) TO service_role;

-- ==============================================================================
-- 22. ACCOMMODATION
-- ==============================================================================
-- Registrations made before this section keep accommodation. From here on, every new Female and Others registration is
-- without it, and Male is optional until an admin closes it (registration_counter.male_accommodation_open). The fee is the
-- same either way, and every registration still takes a seat on the counter. registration_counter.female_cap no longer
-- stops anyone: it only counts Female registrations with accommodation. Only the server reads and writes these.
ALTER TABLE public.registrations ADD COLUMN IF NOT EXISTS accommodation BOOLEAN NOT NULL DEFAULT true;

-- What the student was told when they started paying: true or false. NULL means the pass came before this section, or is
-- a payment staff allowed that the student hasn't continued yet.
ALTER TABLE public.payment_passes ADD COLUMN IF NOT EXISTS accommodation BOOLEAN;

ALTER TABLE public.registration_counter ADD COLUMN IF NOT EXISTS male_accommodation_open BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE public.registration_counter ADD COLUMN IF NOT EXISTS male_accommodation_updated_by UUID REFERENCES public.profiles(id) ON DELETE SET NULL;
ALTER TABLE public.registration_counter ADD COLUMN IF NOT EXISTS male_accommodation_updated_at TIMESTAMPTZ;

-- Replaces section 21's version: only accommodation counts towards the cap.
CREATE OR REPLACE FUNCTION public.female_seats_taken(p_user_id UUID DEFAULT NULL)
RETURNS INTEGER AS $$
  SELECT (SELECT count(*) FROM public.registrations WHERE gender = 'female' AND accommodation AND status IN ('pending', 'confirmed'))::INT
       + (SELECT count(*) FROM public.payment_passes
          WHERE gender = 'female' AND accommodation IS DISTINCT FROM false AND used_at IS NULL AND pay_by > now()
            AND user_id IS DISTINCT FROM p_user_id)::INT;
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public;

-- Replaces section 21's version, for GET /api/admin/seats: only accommodation counts as registered.
CREATE OR REPLACE FUNCTION public.female_seats_status(p_user_id UUID DEFAULT NULL)
RETURNS JSONB AS $$
  SELECT jsonb_build_object(
    'cap', c.female_cap,
    'registered', (SELECT count(*) FROM public.registrations WHERE gender = 'female' AND accommodation AND status IN ('pending', 'confirmed')),
    'full', public.female_seats_taken() >= c.female_cap,
    'keeps_access', p_user_id IS NOT NULL AND public.female_seat_kept(p_user_id))
  FROM public.registration_counter c WHERE c.id = 1;
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public;

-- POST /api/register/start-payment: issue_payment_pass (section 20), deciding accommodation in front of it and saving the
-- decision on the pass. Replaces start_payment_for_gender, which stays only for the code deployed while this is being run.
--   state 'accommodation_closed': a Male student asked for accommodation after it closed. No pass, no payment details.
-- Every other answer gains 'accommodation': what the form shows above the payment details.
-- A Male student quoted accommodation before it closed keeps it: they may already have paid, and UPI can't be refunded.
-- "I haven't paid" (p_fresh, while a phase is open) gives that up.
CREATE OR REPLACE FUNCTION public.start_payment_with_accommodation(p_user_id UUID, p_fresh BOOLEAN, p_gender TEXT, p_no_accommodation BOOLEAN)
RETURNS JSONB AS $$
DECLARE
  c public.registration_counter%ROWTYPE;
  p public.payment_passes%ROWTYPE;
  v_accommodation BOOLEAN;
  v_result JSONB;
BEGIN
  SELECT * INTO c FROM public.registration_counter WHERE id = 1 FOR SHARE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'registration_counter has no row';
  END IF;

  SELECT * INTO p FROM public.payment_passes
  WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now()
  ORDER BY created_at DESC LIMIT 1;

  IF p_gender IS DISTINCT FROM 'male' THEN
    v_accommodation := false;
  ELSIF NOT c.male_accommodation_open AND p.id IS NOT NULL AND p.accommodation IS DISTINCT FROM false
        AND (NOT p_fresh OR c.closed_at IS NOT NULL) THEN
    v_accommodation := true;
  ELSIF p_no_accommodation THEN
    v_accommodation := false;
  ELSIF c.male_accommodation_open THEN
    v_accommodation := true;
  ELSE
    RETURN jsonb_build_object('state', 'accommodation_closed');
  END IF;

  v_result := public.issue_payment_pass(p_user_id, p_fresh);
  IF v_result->>'state' = 'closed' THEN
    RETURN v_result;
  END IF;

  UPDATE public.payment_passes SET accommodation = v_accommodation, gender = p_gender
  WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now();
  RETURN v_result || jsonb_build_object('accommodation', v_accommodation);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Replaces section 20's version. Adds what /api/register needs to decide accommodation: the decision saved on the pass,
-- and whether Male accommodation is open.
CREATE OR REPLACE FUNCTION public.resolve_registration_payment(p_user_id UUID)
RETURNS JSONB AS $$
DECLARE
  c public.registration_counter%ROWTYPE;
  p public.payment_passes%ROWTYPE;
  r public.registrations%ROWTYPE;
BEGIN
  SELECT * INTO c FROM public.registration_counter WHERE id = 1 FOR SHARE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'registration_counter has no row';
  END IF;

  SELECT * INTO p FROM public.payment_passes
  WHERE user_id = p_user_id AND used_at IS NULL AND submit_by > now()
  ORDER BY created_at DESC LIMIT 1;
  IF FOUND THEN
    RETURN jsonb_build_object(
      'allowed', true, 'amount', p.amount, 'phase', p.phase, 'pass_id', p.id, 'pay_by', p.pay_by,
      'allowed_by', p.issued_by,
      'late', (c.closed_at IS NOT NULL OR p.phase <> c.phase) AND now() > p.pay_by,
      'pass_accommodation', p.accommodation, 'male_accommodation_open', c.male_accommodation_open);
  END IF;

  r := public.inherited_registration_payment(p_user_id, c);
  IF r.id IS NOT NULL THEN
    RETURN jsonb_build_object('allowed', true, 'amount', r.amount, 'phase', r.phase, 'late', true,
      'male_accommodation_open', c.male_accommodation_open);
  END IF;

  IF c.closed_at IS NULL THEN
    RETURN jsonb_build_object('allowed', true, 'amount', c.fee_inr, 'phase', c.phase, 'late', false,
      'male_accommodation_open', c.male_accommodation_open);
  END IF;
  RETURN jsonb_build_object('allowed', false);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- The last word, whoever inserts. Replaces section 21's trg_guard_female_seats. /api/register looks for these messages. A
-- row that doesn't say otherwise means accommodation, so the Female and Others message is also what the code deployed
-- while this is being run gets, and it already shows that as full.
CREATE OR REPLACE FUNCTION public.guard_registration_accommodation() RETURNS TRIGGER AS $$
BEGIN
  IF NOT NEW.accommodation OR NEW.user_id IS NULL OR NEW.status NOT IN ('pending', 'confirmed') THEN
    RETURN NEW;
  END IF;
  IF NEW.gender IN ('female', 'others') THEN
    RAISE EXCEPTION 'Female and Others seats are full' USING ERRCODE = '23514';
  END IF;
  IF NOT (SELECT male_accommodation_open FROM public.registration_counter WHERE id = 1) AND NOT EXISTS (
    SELECT 1 FROM public.payment_passes
    WHERE user_id = NEW.user_id AND used_at IS NULL AND submit_by > now() AND accommodation IS DISTINCT FROM false
  ) THEN
    RAISE EXCEPTION 'Male accommodation is closed' USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS trg_guard_female_seats ON public.registrations;
DROP FUNCTION IF EXISTS public.guard_female_seats();
DROP TRIGGER IF EXISTS trg_guard_registration_accommodation ON public.registrations;
CREATE TRIGGER trg_guard_registration_accommodation
  BEFORE INSERT ON public.registrations
  FOR EACH ROW EXECUTE FUNCTION public.guard_registration_accommodation();

-- Replaces section 13's version: the scanner's details also say whether accommodation is included.
CREATE OR REPLACE FUNCTION public.entry_state(p_registration UUID, p_pass UUID, p_invalid_reason TEXT)
RETURNS JSONB AS $$
DECLARE
  r public.registrations%ROWTYPE;
  c RECORD;
BEGIN
  IF p_invalid_reason IS NOT NULL THEN
    RETURN jsonb_build_object('state', 'INVALID', 'reason', p_invalid_reason);
  END IF;

  SELECT * INTO r FROM public.registrations WHERE id = p_registration;
  IF NOT FOUND OR r.status <> 'confirmed' THEN
    RETURN jsonb_build_object('state', 'INVALID', 'reason', 'not_confirmed');
  END IF;

  SELECT ch.id, ch.created_at, ch.desk, ch.method, pr.full_name AS staff_name INTO c
  FROM public.entry_checkins ch LEFT JOIN public.profiles pr ON pr.id = ch.checked_in_by
  WHERE ch.registration_id = r.id AND ch.voided_at IS NULL;
  IF FOUND THEN
    RETURN jsonb_build_object(
      'state', 'ALREADY_CHECKED_IN',
      'registration', r.id, 'registration_id', r.registration_id, 'name', r.name,
      'checkin_id', c.id, 'checked_in_at', c.created_at, 'checked_in_by', COALESCE(c.staff_name, 'Staff'),
      'desk', c.desk, 'method', c.method
    );
  END IF;

  RETURN jsonb_build_object(
    'state', 'VALID',
    'registration', r.id, 'registration_id', r.registration_id, 'name', r.name,
    'enrollment_no', r.enrollment_no, 'college', r.college, 'phone', r.phone, 'utr', r.utr,
    'accommodation', r.accommodation
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;

-- Only the server may call these; the helpers and the trigger need no grant.
REVOKE EXECUTE ON FUNCTION public.guard_registration_accommodation() FROM PUBLIC, anon, authenticated, service_role;
REVOKE EXECUTE ON FUNCTION public.start_payment_with_accommodation(UUID, BOOLEAN, TEXT, BOOLEAN) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.start_payment_with_accommodation(UUID, BOOLEAN, TEXT, BOOLEAN) TO service_role;