-- Añade campo country al JSONB de participantes en fn_professor_create_session
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,
      p.country::text                                                                AS country
    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,
      g.country::text                                                                AS country
    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,
      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)
  ),
  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,
      CASE WHEN cpl.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.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
    LEFT JOIN public.levels l ON l.level_id = cpl.level_id AND l.course_id = p_course_id
    WHERE cpl.course_id = p_course_id
      AND pp.status     = 'ACTIVE'
      AND 'PROFESSOR'   = ANY(p_participant_roles)
    ORDER BY pp.partner_id, cpl.level_id
  )
  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 * FROM partner_students
    UNION ALL
    SELECT * FROM guest_students
    UNION ALL
    SELECT * FROM tutor_participants
    UNION ALL
    SELECT * FROM professor_participants
  ) all_participants;

  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;
$$;

-- Añade campo country al JSONB de participantes en fn_professor_cancel_session
CREATE OR REPLACE FUNCTION public.fn_professor_cancel_session(
  p_professor_id bigint,
  p_session_id   integer,
  p_reason       text DEFAULT ''
)
RETURNS jsonb
LANGUAGE plpgsql AS $$
DECLARE
  v_zoom_meeting_id text;
  v_scheduled_at    timestamptz;
  v_course_id       integer;
  v_course_name     text;
  v_course_language text;
  v_participants    jsonb;
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;

  UPDATE public.sessions
  SET status = 'CANCELLED'
  WHERE session_id = p_session_id
    AND status    != 'CANCELLED'
  RETURNING zoom_meeting_id, scheduled_at, course_id
  INTO v_zoom_meeting_id, v_scheduled_at, v_course_id;

  IF NOT FOUND THEN
    RETURN jsonb_build_object('error', 'Sesión no encontrada o ya cancelada.');
  END IF;

  INSERT INTO public.session_log (session_id, action, performed_by, reason)
  VALUES (p_session_id, 'CANCELLED', p_professor_id, NULLIF(trim(p_reason), ''));

  SELECT name, language INTO v_course_name, v_course_language
  FROM public.courses WHERE course_id = v_course_id;

  SELECT jsonb_agg(jsonb_build_object(
    'email',     sp.email,
    'full_name', sp.full_name,
    'country',   COALESCE(pa.country::text, g.country::text)
  ))
  INTO v_participants
  FROM public.session_participants sp
  LEFT JOIN public.partners pa ON pa.partner_id = sp.partner_id
  LEFT JOIN public.guests   g  ON g.email = sp.email AND sp.partner_id IS NULL
  WHERE sp.session_id = p_session_id;

  RETURN jsonb_build_object(
    'success',         true,
    'zoom_meeting_id', v_zoom_meeting_id,
    'scheduled_at',    v_scheduled_at,
    'course_id',       v_course_id,
    'course_name',     v_course_name,
    'course_language', v_course_language,
    'participants',    COALESCE(v_participants, '[]'::jsonb)
  );
END;
$$;

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