Agreed with those here, Blender is amazing. Wish it had existed when I started out, but by now it has become an amazingly versatile program well worth learning.
I used to use Lightwave for pretty much everything back around the 2000s which gives you a sense of how old I am now.
Most 3d tools of any kind are, and historically have been, simply really expensive, and Blender isn't.
As for texturing, it is key to realistic 3d models so at some point you are going to want to have a good way to texture your stuff, whether it is an Adobe workflow with Photoshop or Substance Sampler/Painter or doing what many now are looking towards as alternative options to avoid the subscription and other issues people have with Adobe now, Affinity Suite or Gimp which is freeware, or the Corel Paint Shop Pro setup.
Using some sort of texture sourcing that is photography based is super valuable, it is simply reality that procedural textures don't usually look as good as things based off reality. Though procgen now is clearly improving fast when augmented with AI tools. I have a giant sale right now (for Labor Day 2024) with 2000+ texture image files and a bunch of other stuff which is available as a bundle for under $2, but that is just an option and you can build a useful collection of texture materials from a ton of places. Here is my sale.
Having decals, a collection of custom brushes has proven helpful for detailing, just being able to paint on the unwrapped UV map or on an output of the 3d mesh itself directly (that is what Substance Painter is great at!) and there are cheap alternatives that currently are a bit jankier like Armory3d, Blacksmith 3d and so on, apps that can be used to paint onto 3d models for around $10-50.
Ultimately though, it isn't just tools for modelling and texturing that make a model look great. Sometimes it is just an understanding of environment art and art in general as a broader discipline. The aim for 'realism' isn't everything, it has to be well optimized and run well in a game engine, it has to serve its purpose in the world and fit into the setting and the scene as a whole, and stand out visually in a logical way if players need to interact with it and it isn't just set dressing. There are a ton of factors but basically when you realize storytelling and playability are both often a focus in the design of an environment that is helpful. Guiding players through so they don't miss important elements, basically, and so they have a sense of what to do and where to get to next. But doing it subtly, tastefully enough that it doesn't undermine the credibility of the setting.
Game mechanics, narrative and audiovisual aesthetics ideally all support each other in reasonable ways. It can make a game really compelling to immerse yourself in when this works well. There is a lot to learn in making game art and moreover making it fit together in a complete game. Good luck!
-Matthew Hornbostel, matthornb.itch.io