-- 021 — Log de sesiones + visibilidad global + motivo de cancelación
-- - Nueva tabla session_log
-- - fn_professor_list_sessions: todos los profesores ven todas las sesiones
-- - fn_professor_create_session: registra CREATED en log
-- - fn_professor_cancel_session: acepta motivo, cualquier profesor puede cancelar, registra CANCELLED

-- ─── Tabla de log ────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS public.session_log (
    id           serial      PRIMARY KEY,
    session_id   int         NOT NULL REFERENCES public.sessions(session_id),
    action       text        NOT NULL,   -- CREATED | CANCELLED
    performed_by int         NOT NULL,   -- partner_id del profesor
    reason       text,
    created_at   timestamptz NOT NULL DEFAULT now()
);

-- ─── fn_professor_list_sessions ──────────────────────────────────────────────
-- Todos los profesores ven todas las sesiones del curso (sin filtro created_by)
-- Añade created_by_name para mostrar quién la creó

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,
    created_by_name   text
)
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,
        (p.name || ' ' || COALESCE(p.surname, ''))::text       AS created_by_name
    FROM public.sessions s
    JOIN public.partners p ON p.partner_id = s.created_by
    LEFT JOIN public.session_participants sp ON s.session_id = sp.session_id
    WHERE s.course_id = p_course_id
    GROUP BY s.session_id, p.name, p.surname
    ORDER BY s.scheduled_at DESC
    LIMIT 100;
$function$;

-- ─── fn_professor_create_session ─────────────────────────────────────────────

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;

    -- Log de creación
    INSERT INTO public.session_log (session_id, action, performed_by)
    VALUES (v_session_id, 'CREATED', p_professor_id::int);

    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;

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

-- ─── fn_professor_cancel_session ─────────────────────────────────────────────
-- Cualquier profesor activo puede cancelar. Requiere motivo. Registra en log.

CREATE OR REPLACE FUNCTION public.fn_professor_cancel_session(
    p_professor_id bigint,
    p_session_id   int,
    p_reason       text DEFAULT ''
)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_zoom_meeting_id text;
BEGIN
    -- Verificar que el profesor tiene perfil activo
    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 INTO v_zoom_meeting_id;

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

    -- Log de cancelación con motivo
    INSERT INTO public.session_log (session_id, action, performed_by, reason)
    VALUES (p_session_id, 'CANCELLED', p_professor_id::int, NULLIF(trim(p_reason), ''));

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

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

GRANT SELECT, INSERT ON public.session_log TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.session_log_id_seq TO "elfi1953_stc-eu";
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_cancel_session(bigint, int, text)                   TO "elfi1953_stc-eu";