CREATE OR REPLACE FUNCTION public.fn_tutor_update_student_status(p_student_id bigint, p_new_status text, p_tutor_id bigint, p_course_id integer, p_reason text DEFAULT 'Cambio manual por tutor'::text)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_prev_status   text;
    v_level_id      int;
    v_student_name  text;
    v_student_email text;
    v_tutor_name    text;
    v_tutor_email   text;
BEGIN
    SELECT sp.status::text, sp.level_id,
           ps.name || ' ' || ps.surname, ps.email::text
    INTO   v_prev_status, v_level_id, v_student_name, v_student_email
    FROM   public.student_profiles sp
    JOIN   public.partners ps ON ps.partner_id = sp.partner_id
    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_status = p_new_status THEN
        RETURN jsonb_build_object('error', 'El alumno ya tiene ese estado.');
    END IF;

    IF p_new_status NOT IN ('ACTIVE', 'PENDING', 'INACTIVE') THEN
        RETURN jsonb_build_object('error', 'Estado no válido: ' || p_new_status);
    END IF;

    IF p_new_status = 'ACTIVE' THEN
        IF public.fn_check_enrollment_quota(p_tutor_id, v_level_id, p_course_id) = 'PENDING' THEN
            RETURN jsonb_build_object('error', 'ERR_QUOTA_FULL');
        END IF;
    END IF;

    UPDATE public.student_profiles
    SET    status = p_new_status, 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_status, new_status, actor_id, actor_role, action_type, reason)
    VALUES
        (p_student_id, v_level_id, v_prev_status, p_new_status, p_tutor_id, 'TUTOR', 'STATUS_CHANGE', p_reason);

    SELECT pt.name || ' ' || pt.surname, pt.email::text
    INTO   v_tutor_name, v_tutor_email
    FROM   public.partners pt WHERE pt.partner_id = p_tutor_id;

    RETURN jsonb_build_object(
        'success',       true,
        'new_status',    p_new_status,
        'student_name',  v_student_name,
        'student_email', v_student_email,
        'tutor_name',    v_tutor_name,
        'tutor_email',   v_tutor_email
    );
END;
$function$
;