So what does the future of space hold? A fun thought experiment is to go back to 1969 after we landed on the Moon. At that point NASA already had big plans for getting to Mars, and they were viable. But Nixon chose to defacto cancel the human space program in 1972. He felt that a catastrophe was inevitable and that it'd harm his political ambitions. But what would have happened if we had carried on with NASA's plans?
Surely you'll agree that it's, at the minimum, viable that we'd have succeeded there. And that would entail the construction of substantial interplanetary logistics, outposts, and more. To say nothing of the endless discoveries and 'unknown unknowns' that we would have made along the way. The potential opportunity and advances there are difficult to overstate. And this would have been quite a modest prediction of the future if you asked somebody in 1969 what 50 years later would hold. After all we had just gone from 'nothing' to man on the Moon in 7 years.
Instead, we turned to robotics. And what do we have to show for it after 60 years? We finally learned that Mars surface regolith has significant quantities of water. Yay? On top of the severely crippled progress, there's also the human loss. Right now NASA's Artemis Program is basically just trying to recreate 1969, and doing a poor job of it. Decades of skill/labor atrophy mean we're left trying to 'relearn' what we knew in the 60s, but doing so in an environment where everything costs much more, moves much more slowly, and where human talent is focused in fields like ads and finance, instead of anything of value (well I suppose LLMs are changing that, but that's another topic).
Basically space is a frontier that opens the door to practically infinite resources, land, and even knowledge. If post scarcity is possible, the only way there is through space. And the only way we're really going to advance along that frontier is with humans.