CREATE OR REPLACE FUNCTION public.fn_admin_list_levels(p_course_id integer DEFAULT NULL::integer)
 RETURNS TABLE(level_id integer, course_id integer, course_name character varying, name character varying, description text, zoom_room_name character varying, tutor_quota_limit integer, order_index smallint, active boolean, enrolled_count bigint)
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN
    RETURN QUERY
    SELECT
        l.level_id,
        l.course_id,
        c.name::VARCHAR          AS course_name,
        l.name,
        l.description,
        l.zoom_room_name,
        l.tutor_quota_limit,
        l.order_index,
        l.active,
        COUNT(sp.partner_id)     AS enrolled_count
    FROM   public.levels l
    JOIN   public.courses c  ON c.course_id = l.course_id
    LEFT JOIN public.student_profiles sp
           ON  sp.level_id  = l.level_id
           AND sp.course_id = l.course_id
           AND sp.status    = 'ACTIVE'
    WHERE  (p_course_id IS NULL OR l.course_id = p_course_id)
    GROUP BY l.level_id, l.course_id, c.name, l.name, l.description,
             l.zoom_room_name, l.tutor_quota_limit, l.order_index, l.active
    ORDER BY l.course_id, l.order_index, l.level_id;
END;
$function$
;