CREATE OR REPLACE FUNCTION public.fn_professor_list_sessions(p_professor_id bigint, p_course_id integer)
 RETURNS TABLE(session_id integer, title text, scheduled_at timestamp with time zone, duration_min integer, status session_status, zoom_meeting_id text, zoom_join_url text, created_at timestamp with time zone, participant_count bigint, created_by_name text, attendance_synced_at timestamp with time zone)
 LANGUAGE sql
 STABLE
AS $function$
  SELECT
    s.session_id,
    s.title,
    s.scheduled_at,
    s.duration_min,
    s.status,
    s.zoom_meeting_id,
    s.zoom_join_url,
    s.created_at,
    COUNT(sp.id)                                        AS participant_count,
    (p.name || ' ' || COALESCE(p.surname, ''))::text   AS created_by_name,
    s.attendance_synced_at
  FROM public.sessions s
  JOIN public.partners p ON p.partner_id = s.created_by
  LEFT JOIN public.session_participants sp ON s.session_id = sp.session_id
  WHERE s.course_id = p_course_id
  GROUP BY s.session_id, p.name, p.surname
  ORDER BY s.scheduled_at DESC
  LIMIT 100;
$function$
;