-- Añade columna participant_roles a sessions para auditoría
ALTER TABLE public.sessions
  ADD COLUMN IF NOT EXISTS participant_roles text[] NOT NULL DEFAULT '{STUDENT,TUTOR}';

-- Recrea la función con el nuevo parámetro p_participant_roles
DROP FUNCTION IF EXISTS public.fn_professor_create_session(bigint, integer, text, timestamptz, integer);

CREATE OR REPLACE FUNCTION public.fn_professor_create_session(
  p_professor_id      bigint,
  p_course_id         integer,
  p_title             text,
  p_scheduled_at      timestamptz,
  p_duration_min      integer,
  p_participant_roles text[] DEFAULT '{STUDENT,TUTOR}'
)
RETURNS jsonb
LANGUAGE plpgsql AS $$
DECLARE
  v_session_id integer;
  v_participants jsonb;
  v_alt_hosts   text;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM public.professor_profiles
    WHERE partner_id = p_professor_id AND status = 'ACTIVE'
  ) 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
  partner_students AS (
    SELECT
      sp.partner_id::bigint                                                         AS partner_id,
      (p.name || ' ' || COALESCE(p.surname, ''))::text                              AS full_name,
      p.email::text                                                                  AS email,
      'STUDENT'::text                                                                AS participant_type,
      COALESCE(l.zoom_room_name, l.name, 'Nivel ' || sp.level_id::text)::text       AS breakout_room
    FROM public.student_profiles sp
    JOIN public.partners p ON sp.partner_id = p.partner_id
    LEFT JOIN public.levels l ON l.level_id = sp.level_id AND l.course_id = p_course_id
    WHERE sp.course_id = p_course_id
      AND sp.status    = 'ACTIVE'
      AND 'STUDENT'    = ANY(p_participant_roles)
  ),
  guest_students AS (
    SELECT
      NULL::bigint                                                                   AS partner_id,
      (g.name || ' ' || COALESCE(g.surname, ''))::text                              AS full_name,
      g.email::text                                                                  AS email,
      'STUDENT'::text                                                                AS participant_type,
      COALESCE(
        (SELECT zoom_room_name FROM public.levels WHERE level_id = 0 AND course_id = p_course_id LIMIT 1),
        (SELECT name           FROM public.levels WHERE level_id = 0 AND course_id = p_course_id LIMIT 1),
        'Nivel 0'
      )::text                                                                        AS breakout_room
    FROM public.guests g
    WHERE g.course_id = p_course_id
      AND 'STUDENT'   = ANY(p_participant_roles)
  ),
  tutor_min_levels AS (
    SELECT tutor_id, MIN(lvl) AS min_level
    FROM (
      SELECT tutor_id, level_id AS lvl
      FROM public.student_profiles
      WHERE course_id = p_course_id AND status = 'ACTIVE'
      UNION ALL
      SELECT tutor_id, 0 AS lvl
      FROM public.guests
      WHERE course_id = p_course_id
    ) all_students
    GROUP BY tutor_id
  ),
  tutor_participants AS (
    SELECT
      tml.tutor_id::bigint                                                           AS partner_id,
      (p.name || ' ' || COALESCE(p.surname, ''))::text                              AS full_name,
      p.email::text                                                                  AS email,
      'TUTOR'::text                                                                  AS participant_type,
      COALESCE(l.zoom_room_name, l.name, 'Nivel ' || tml.min_level::text)::text     AS breakout_room
    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)
  ),
  professor_participants AS (
    SELECT DISTINCT ON (pp.partner_id)
      pp.partner_id::bigint                                                          AS partner_id,
      (p.name || ' ' || COALESCE(p.surname, ''))::text                              AS full_name,
      p.email::text                                                                  AS email,
      'PROFESSOR'::text                                                              AS participant_type,
      NULL::text                                                                     AS breakout_room
    FROM public.course_professor_levels cpl
    JOIN public.professor_profiles pp ON pp.partner_id = cpl.professor_id
    JOIN public.partners p ON p.partner_id = cpl.professor_id
    WHERE cpl.course_id = p_course_id
      AND pp.status     = 'ACTIVE'
      AND 'PROFESSOR'   = ANY(p_participant_roles)
  )
  SELECT jsonb_agg(
    jsonb_build_object(
      'partner_id',       partner_id,
      'full_name',        full_name,
      'email',            email,
      'participant_type', participant_type,
      'breakout_room',    breakout_room
    )
  ) INTO v_participants
  FROM (
    SELECT * FROM partner_students
    UNION ALL
    SELECT * FROM guest_students
    UNION ALL
    SELECT * FROM tutor_participants
    UNION ALL
    SELECT * FROM professor_participants
  ) all_participants;

  -- Alternative hosts: profesores activos del curso distintos del creador
  SELECT string_agg(DISTINCT p.email::text, ',')
  INTO v_alt_hosts
  FROM public.course_professor_levels cpl
  JOIN public.professor_profiles pp ON pp.partner_id = cpl.professor_id
  JOIN public.partners p ON p.partner_id = cpl.professor_id
  WHERE cpl.course_id      = p_course_id
    AND pp.status          = 'ACTIVE'
    AND cpl.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;
$$;

GRANT EXECUTE ON FUNCTION public.fn_professor_create_session(bigint, integer, text, timestamptz, integer, text[]) TO "elfi1953_stc-eu";