CREATE OR REPLACE FUNCTION public.fn_admin_upsert_level(p_data jsonb)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_level_id       INT;
    v_course_id      INT;
    v_name           VARCHAR(100);
    v_desc           TEXT;
    v_zoom_room_name VARCHAR(100);
    v_quota          INT;
    v_order          SMALLINT;
    v_is_new         BOOLEAN;
BEGIN
    v_level_id       := (p_data->>'level_id')::INT;
    v_course_id      := COALESCE((p_data->>'course_id')::INT, 1);
    v_name           := TRIM(p_data->>'name');
    v_desc           := p_data->>'description';
    v_zoom_room_name := NULLIF(TRIM(p_data->>'zoom_room_name'), '');
    v_quota          := COALESCE((p_data->>'tutor_quota_limit')::INT, 10);
    v_order          := COALESCE((p_data->>'order_index')::SMALLINT, 0);

    IF v_name IS NULL OR v_name = '' THEN
        RETURN jsonb_build_object('success', false, 'error', 'El nombre del nivel es obligatorio.');
    END IF;
    IF v_quota < 1 OR v_quota > 9999 THEN
        RETURN jsonb_build_object('success', false, 'error', 'La cuota debe estar entre 1 y 9999 alumnos.');
    END IF;

    IF v_level_id IS NULL THEN
        v_is_new := true;
        SELECT COALESCE(MAX(level_id), 0) + 1 INTO v_level_id
        FROM   public.levels WHERE level_id > 0;

        INSERT INTO public.levels
               (level_id, course_id, name, description, zoom_room_name, tutor_quota_limit, order_index, active)
        VALUES (v_level_id, v_course_id, v_name, v_desc, v_zoom_room_name, v_quota, v_order, true);

    ELSIF v_level_id = 0 THEN
        v_is_new := false;
        UPDATE public.levels
        SET    tutor_quota_limit = v_quota,
               description       = COALESCE(v_desc, description),
               zoom_room_name    = v_zoom_room_name
        WHERE  level_id = 0;

    ELSE
        v_is_new := false;
        UPDATE public.levels
        SET    name              = v_name,
               description      = v_desc,
               zoom_room_name   = v_zoom_room_name,
               tutor_quota_limit = v_quota,
               order_index       = v_order
        WHERE  level_id = v_level_id AND course_id = v_course_id;

        IF NOT FOUND THEN
            RETURN jsonb_build_object('success', false, 'error', 'Nivel no encontrado.');
        END IF;
    END IF;

    RETURN jsonb_build_object(
        'success',  true,
        'action',   CASE WHEN v_is_new THEN 'created' ELSE 'updated' END,
        'level_id', v_level_id
    );
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$function$
;