-- Migration: 00016_get_waitlist_position
-- Description: RPC to compute waitlist position for the current user (1-based)
-- Date: 2025-12-21

CREATE OR REPLACE FUNCTION get_waitlist_position(target_user_id UUID)
RETURNS INTEGER AS $$
DECLARE
  my_score INTEGER;
  my_joined TIMESTAMPTZ;
  ahead_count INTEGER;
BEGIN
  -- Only allow a user to query their own position.
  IF auth.uid() IS NULL OR auth.uid() <> target_user_id THEN
    RAISE EXCEPTION 'Not authorized';
  END IF;

  SELECT we.priority_score, we.base_joined_at
  INTO my_score, my_joined
  FROM waitlist_entries we
  JOIN users u ON u.id = we.user_id
  WHERE we.user_id = target_user_id
    AND we.admitted_at IS NULL
    AND u.access_status = 'idv_verified_waitlisted';

  IF my_joined IS NULL THEN
    RETURN NULL;
  END IF;

  SELECT COUNT(*) INTO ahead_count
  FROM waitlist_entries we
  JOIN users u ON u.id = we.user_id
  WHERE we.admitted_at IS NULL
    AND u.access_status = 'idv_verified_waitlisted'
    AND (
      we.priority_score > my_score
      OR (we.priority_score = my_score AND we.base_joined_at < my_joined)
    );

  RETURN ahead_count + 1;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

GRANT EXECUTE ON FUNCTION get_waitlist_position(UUID) TO authenticated;


