CREATE OR REPLACE FUNCTION public.fn_tutor_promote_student(p_tutor_id bigint, p_student_id bigint, p_course_id integer, p_new_level_id integer, p_reason_id integer, p_comments text DEFAULT NULL::text)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_prev_level_id int;
    v_level_name    text;
    v_reason_label  text;
BEGIN
    SELECT sp.level_id INTO v_prev_level_id
    FROM public.student_profiles sp
    WHERE sp.partner_id = p_student_id
      AND sp.tutor_id   = p_tutor_id
      AND sp.course_id  = p_course_id;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('error', 'Alumno no encontrado o no pertenece a este tutor.');
    END IF;

    IF v_prev_level_id = p_new_level_id THEN
        RETURN jsonb_build_object('error', 'El alumno ya está en ese nivel.');
    END IF;

    SELECT name INTO v_level_name
    FROM public.levels
    WHERE level_id = p_new_level_id AND course_id = p_course_id AND active = true;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('error', 'El nivel destino no existe o está inactivo.');
    END IF;

    SELECT label INTO v_reason_label
    FROM public.promotion_reasons
    WHERE reason_id = p_reason_id AND active = true;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('error', 'Motivo de promoción no válido.');
    END IF;

    UPDATE public.student_profiles
    SET level_id = p_new_level_id, updated_at = NOW()
    WHERE partner_id = p_student_id AND tutor_id = p_tutor_id AND course_id = p_course_id;

    INSERT INTO public.student_lifecycle_log
        (student_id, level_id, previous_level_id, new_level_id,
         actor_id, actor_role, action_type, reason, reason_id, comments)
    VALUES
        (p_student_id, v_prev_level_id, v_prev_level_id, p_new_level_id,
         p_tutor_id, 'TUTOR', 'LEVEL_CHANGE', v_reason_label, p_reason_id, p_comments);

    RETURN jsonb_build_object(
        'success',        true,
        'new_level_id',   p_new_level_id,
        'new_level_name', v_level_name
    );
END;
$function$
;