CREATE OR REPLACE FUNCTION public.fn_tutor_list_courses(p_tutor_id bigint)
 RETURNS TABLE(course_id integer, name text, language text, active boolean)
 LANGUAGE sql
 STABLE
AS $function$
  -- Returns only courses where the tutor has at least one student (partner or guest).
  -- Used for the course selector in the TUTOR tab; the enrollment modal uses fn_admin_list_courses.
  SELECT c.course_id, c.name::text, c.language::text, c.active
  FROM public.courses c
  WHERE c.active = true
    AND (
      EXISTS (
        SELECT 1 FROM public.student_profiles sp
        WHERE sp.tutor_id = p_tutor_id AND sp.course_id = c.course_id
      )
      OR EXISTS (
        SELECT 1 FROM public.guests g
        WHERE g.tutor_id = p_tutor_id AND g.course_id = c.course_id
      )
    )
  ORDER BY 2;
$function$
;