-- Migration 022: fn_professor_list_courses
-- Returns all courses a professor is assigned to, with access level per course.

CREATE OR REPLACE FUNCTION public.fn_professor_list_courses(p_professor_id bigint)
 RETURNS TABLE(course_id integer, name text, language text, active boolean, access_level text)
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
  v_is_superadmin boolean := false;
BEGIN
  SELECT vr.is_superadmin INTO v_is_superadmin
  FROM public.vw_user_identity_roles vr
  WHERE vr.partner_id = p_professor_id;

  -- Superadmin sees all courses with full access
  IF v_is_superadmin THEN
    RETURN QUERY
      SELECT c.course_id, c.name::text, c.language::text, c.active, 'superadmin'::text
      FROM public.courses c
      ORDER BY c.name;
    RETURN;
  END IF;

  -- Professor sees only courses they are assigned to
  RETURN QUERY
    SELECT
      c.course_id,
      c.name::text,
      c.language::text,
      c.active,
      CASE
        WHEN ca.professor_id IS NOT NULL THEN 'course_admin'
        ELSE 'professor'
      END::text AS access_level
    FROM public.course_professor_levels cpl
    JOIN public.courses c ON c.course_id = cpl.course_id
    LEFT JOIN public.course_admins ca
           ON ca.professor_id = p_professor_id
          AND ca.course_id    = cpl.course_id
    WHERE cpl.professor_id = p_professor_id
    GROUP BY c.course_id, c.name, c.language, c.active, ca.professor_id
    ORDER BY c.name;
END;
$function$;

GRANT EXECUTE ON FUNCTION public.fn_professor_list_courses(bigint) TO "elfi1953_stc-eu";