-- 019 — Funciones SQL para el panel del profesor (sesiones + participantes)
-- Backup previo obligatorio antes de ejecutar.

CREATE OR REPLACE FUNCTION public.fn_professor_list_sessions(
    p_professor_id bigint,
    p_course_id    int
)
RETURNS TABLE(
    session_id        int,
    title             text,
    scheduled_at      timestamptz,
    duration_min      int,
    status            session_status,
    zoom_meeting_id   text,
    zoom_join_url     text,
    created_at        timestamptz,
    participant_count bigint
)
LANGUAGE sql STABLE
AS $function$
    SELECT
        s.session_id,
        s.title,
        s.scheduled_at,
        s.duration_min,
        s.status,
        s.zoom_meeting_id,
        s.zoom_join_url,
        s.created_at,
        COUNT(sp.id) AS participant_count
    FROM public.sessions s
    LEFT JOIN public.session_participants sp ON s.session_id = sp.session_id
    WHERE s.created_by = p_professor_id::int
      AND s.course_id  = p_course_id
    GROUP BY s.session_id
    ORDER BY s.scheduled_at DESC
    LIMIT 100;
$function$;

-- ─────────────────────────────────────────────────────────────────────────────

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

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

-- ─────────────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION public.fn_professor_save_session_zoom(
    p_session_id      int,
    p_zoom_meeting_id text,
    p_zoom_join_url   text,
    p_participants    jsonb
)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE public.sessions
    SET zoom_meeting_id = p_zoom_meeting_id,
        zoom_join_url   = p_zoom_join_url
    WHERE session_id = p_session_id;

    INSERT INTO public.session_participants
        (session_id, participant_type, partner_id, email, full_name,
         breakout_room, zoom_registrant_id, zoom_join_url)
    SELECT
        p_session_id,
        (p->>'participant_type')::participant_type,
        (p->>'partner_id')::integer,
        p->>'email',
        p->>'full_name',
        p->>'breakout_room',
        NULLIF(p->>'zoom_registrant_id', ''),
        NULLIF(p->>'zoom_join_url', '')
    FROM jsonb_array_elements(p_participants) AS p
    ON CONFLICT (session_id, email) DO UPDATE
        SET zoom_registrant_id = EXCLUDED.zoom_registrant_id,
            zoom_join_url      = EXCLUDED.zoom_join_url;

    RETURN jsonb_build_object('success', true);
END;
$function$;

-- ─────────────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION public.fn_professor_cancel_session(
    p_professor_id bigint,
    p_session_id   int
)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_zoom_meeting_id text;
BEGIN
    UPDATE public.sessions
    SET status = 'CANCELLED'
    WHERE session_id = p_session_id
      AND created_by = p_professor_id::int
      AND status    != 'CANCELLED'
    RETURNING zoom_meeting_id INTO v_zoom_meeting_id;

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

    RETURN jsonb_build_object('success', true, 'zoom_meeting_id', v_zoom_meeting_id);
END;
$function$;

-- ─────────────────────────────────────────────────────────────────────────────
-- Permisos

GRANT EXECUTE ON FUNCTION public.fn_professor_list_sessions(bigint, int)                          TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_professor_create_session(bigint, int, text, timestamptz, int) TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_professor_save_session_zoom(int, text, text, jsonb)           TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_professor_cancel_session(bigint, int)                         TO "elfi1953_stc-eu";
GRANT SELECT, INSERT, UPDATE ON public.sessions             TO "elfi1953_stc-eu";
GRANT SELECT, INSERT, UPDATE ON public.session_participants TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.sessions_session_id_seq             TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.session_participants_id_seq         TO "elfi1953_stc-eu";