CREATE OR REPLACE FUNCTION public.fn_tutor_next_session_for_student(p_partner_id bigint, p_course_id integer, p_session_id integer DEFAULT NULL::integer)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
  v_session  record;
  v_student  record;
  v_enrolled boolean;
BEGIN
  -- Find session: specific (p_session_id) or auto-select next; 90-min cutoff always applies
  SELECT session_id, zoom_meeting_id, scheduled_at
  INTO v_session
  FROM public.sessions
  WHERE course_id    = p_course_id
    AND status       = 'SCHEDULED'
    AND scheduled_at > NOW() + INTERVAL '90 minutes'
    AND (p_session_id IS NULL OR session_id = p_session_id)
  ORDER BY scheduled_at ASC
  LIMIT 1;

  IF NOT FOUND THEN
    RETURN jsonb_build_object(
      'error',   'NO_SESSION',
      'message', CASE
        WHEN p_session_id IS NOT NULL
          THEN 'La sesión no está disponible o comienza en menos de 90 minutos.'
          ELSE 'No hay sesiones disponibles (la próxima comienza en menos de 90 minutos o no hay sesiones programadas).'
      END
    );
  END IF;

  -- Already enrolled in this session?
  SELECT EXISTS (
    SELECT 1 FROM public.session_participants
    WHERE session_id = v_session.session_id AND partner_id = p_partner_id
  ) INTO v_enrolled;

  IF v_enrolled THEN
    RETURN jsonb_build_object('error', 'ALREADY_ENROLLED', 'message', 'El alumno ya está inscrito en la próxima sesión.');
  END IF;

  -- Student info + level for breakout room assignment (same logic as fn_professor_create_session)
  SELECT
    p.partner_id,
    p.email::text                                                          AS email,
    TRIM(p.name || ' ' || COALESCE(p.surname, ''))                         AS full_name,
    COALESCE(p.country::text, 'ES')                                        AS country,
    COALESCE(l.zoom_room_name, l.name, 'Nivel ' || sp.level_id::text)     AS breakout_room
  INTO v_student
  FROM public.partners p
  JOIN public.student_profiles sp ON sp.partner_id = p.partner_id AND sp.course_id = p_course_id
  LEFT JOIN public.levels l       ON l.level_id = sp.level_id
  WHERE p.partner_id = p_partner_id;

  IF NOT FOUND THEN
    RETURN jsonb_build_object('error', 'STUDENT_NOT_FOUND', 'message', 'Alumno no encontrado en este curso.');
  END IF;

  RETURN jsonb_build_object(
    'session_id',      v_session.session_id,
    'zoom_meeting_id', v_session.zoom_meeting_id,
    'scheduled_at',    v_session.scheduled_at,
    'breakout_room',   v_student.breakout_room,
    'email',           v_student.email,
    'full_name',       v_student.full_name,
    'country',         v_student.country,
    'partner_id',      v_student.partner_id
  );
END;
$function$
;