-- 020 — Añade alternative_hosts a fn_professor_create_session
-- Devuelve emails de todos los profesores activos como co-anfitriones Zoom.
-- La cuenta host es la cuenta API (S2S OAuth), no el profesor que crea la sesión,
-- por lo que todos los profesores (incluido el creador) deben ser alternative_hosts.

CREATE OR REPLACE FUNCTION public.fn_professor_create_session(
    p_professor_id bigint,
    p_course_id    int,
    p_title        text,
    p_scheduled_at timestamptz,
    p_duration_min int
)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_session_id   int;
    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)
    VALUES (p_course_id, p_professor_id::int, p_title, p_scheduled_at, p_duration_min, 'SCHEDULED')
    RETURNING session_id INTO v_session_id;

    WITH
    partner_students AS (
        SELECT
            sp.partner_id::int                                            AS partner_id,
            (p.name || ' ' || COALESCE(p.surname, ''))::text             AS full_name,
            p.email::text                                                 AS email,
            'STUDENT'::text                                               AS participant_type,
            COALESCE(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'
    ),
    guest_students AS (
        SELECT
            NULL::int                                                      AS partner_id,
            (g.name || ' ' || COALESCE(g.surname, ''))::text              AS full_name,
            g.email::text                                                  AS email,
            'STUDENT'::text                                                AS participant_type,
            COALESCE(
                (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
    ),
    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::int                                              AS partner_id,
            (p.name || ' ' || COALESCE(p.surname, ''))::text              AS full_name,
            p.email::text                                                  AS email,
            'TUTOR'::text                                                  AS participant_type,
            COALESCE(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
    )
    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
    ) all_participants;

    -- Todos los profesores activos como co-anfitriones (comma-separated)
    SELECT string_agg(p.email::text, ',')
    INTO v_alt_hosts
    FROM public.professor_profiles pp
    JOIN public.partners p ON p.partner_id = pp.partner_id
    WHERE pp.status = 'ACTIVE';

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

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