CREATE OR REPLACE FUNCTION public.fn_professor_create_session(p_professor_id bigint, p_course_id integer, p_title text, p_scheduled_at timestamp with time zone, p_duration_min integer, p_participant_roles text[] DEFAULT '{STUDENT,TUTOR}'::text[])
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
  v_session_id integer;
  v_participants jsonb;
  v_alt_hosts   text;
BEGIN
  IF NOT public.fn_is_active_professor(p_professor_id) THEN
    RETURN jsonb_build_object('error', 'No tienes perfil de profesor activo.');
  END IF;

  INSERT INTO public.sessions (course_id, created_by, title, scheduled_at, duration_min, status, participant_roles)
  VALUES (p_course_id, p_professor_id, p_title, p_scheduled_at, p_duration_min, 'SCHEDULED', p_participant_roles)
  RETURNING session_id INTO v_session_id;

  INSERT INTO public.session_log (session_id, action, performed_by)
  VALUES (v_session_id, 'CREATED', p_professor_id);

  WITH
  -- Active partner students; level 0 → main room (NULL breakout)
  partner_students AS (
    SELECT
      e.student_id::bigint                                             AS partner_id,
      public.fn_full_name(e.student_name, e.student_surname)          AS full_name,
      e.student_email                                                  AS email,
      'STUDENT'::text                                                  AS participant_type,
      CASE WHEN e.level_id = 0 THEN NULL
           ELSE e.zoom_room_name
      END::text                                                        AS breakout_room,
      e.student_country::text                                          AS country
    FROM public.vw_enrolled_student e
    WHERE e.course_id          = p_course_id
      AND e.enrollment_status  = 'ACTIVE'
      AND 'STUDENT'            = ANY(p_participant_roles)
      AND e.student_id         < 9000000000
  ),
  -- Guest students are always at level 0 → main room (NULL breakout)
  guest_students AS (
    SELECT
      NULL::bigint                                                     AS partner_id,
      public.fn_full_name(g.name, g.surname)                          AS full_name,
      g.guest_email                                                    AS email,
      'STUDENT'::text                                                  AS participant_type,
      NULL::text                                                       AS breakout_room,
      g.country::text                                                  AS country
    FROM public.vw_guest_with_tutor g
    WHERE g.course_id = p_course_id
      AND 'STUDENT'   = ANY(p_participant_roles)
      AND g.guest_email NOT LIKE '%stctest%'
      AND g.guest_email NOT LIKE '%stc-dev%'
  ),
  -- Tutors go to the room of their lowest-level active student; level 0 → main room (NULL)
  tutor_min_levels AS (
    SELECT tutor_id, MIN(level_id) AS min_level
    FROM public.vw_student_management_master
    WHERE course_id = p_course_id AND enrollment_status = 'ACTIVE'
    GROUP BY tutor_id
  ),
  tutor_participants AS (
    SELECT
      tml.tutor_id::bigint                                             AS partner_id,
      public.fn_full_name(p.name, p.surname)                          AS full_name,
      p.email::text                                                    AS email,
      'TUTOR'::text                                                    AS participant_type,
      CASE WHEN tml.min_level = 0 THEN NULL
           ELSE COALESCE(l.zoom_room_name, l.name)
      END::text                                                        AS breakout_room,
      p.country::text                                                  AS country
    FROM tutor_min_levels tml
    JOIN public.partners p ON p.partner_id = tml.tutor_id
    LEFT JOIN public.levels l ON l.level_id = tml.min_level AND l.course_id = p_course_id
    WHERE 'TUTOR'      = ANY(p_participant_roles)
      AND p.partner_id < 9000000000
  ),
  -- Professors assigned to this course (active only); level 0 → main room (NULL)
  professor_participants AS (
    SELECT DISTINCT ON (acp.professor_id)
      acp.professor_id::bigint                                         AS partner_id,
      acp.full_name,
      acp.email::text                                                  AS email,
      'PROFESSOR'::text                                                AS participant_type,
      CASE WHEN acp.level_id = 0 THEN NULL
           ELSE COALESCE(l.zoom_room_name, l.name)
      END::text                                                        AS breakout_room,
      p.country::text                                                  AS country
    FROM public.vw_active_course_professor acp
    JOIN public.partners p ON p.partner_id = acp.professor_id
    LEFT JOIN public.levels l ON l.level_id = acp.level_id AND l.course_id = p_course_id
    WHERE acp.course_id    = p_course_id
      AND 'PROFESSOR'      = ANY(p_participant_roles)
      AND acp.professor_id < 9000000000
    ORDER BY acp.professor_id, acp.level_id
  )
  -- Deduplicate by email with role priority: PROFESSOR > TUTOR > STUDENT
  SELECT jsonb_agg(
    jsonb_build_object(
      'partner_id',       partner_id,
      'full_name',        full_name,
      'email',            email,
      'participant_type', participant_type,
      'breakout_room',    breakout_room,
      'country',          country
    )
  ) INTO v_participants
  FROM (
    SELECT DISTINCT ON (email) *
    FROM (
      SELECT * FROM partner_students
      UNION ALL
      SELECT * FROM guest_students
      UNION ALL
      SELECT * FROM tutor_participants
      UNION ALL
      SELECT * FROM professor_participants
    ) all_parts
    ORDER BY email,
      CASE participant_type WHEN 'PROFESSOR' THEN 1 WHEN 'TUTOR' THEN 2 ELSE 3 END
  ) all_participants;

  -- Alternative Zoom hosts: other active professors on this course
  SELECT string_agg(DISTINCT acp.email::text, ',')
  INTO v_alt_hosts
  FROM public.vw_active_course_professor acp
  WHERE acp.course_id    = p_course_id
    AND acp.professor_id != p_professor_id;

  RETURN jsonb_build_object(
    'session_id',        v_session_id,
    'participants',      COALESCE(v_participants, '[]'::jsonb),
    'alternative_hosts', COALESCE(v_alt_hosts, '')
  );
END;
$function$
;