I can definitely think of quite a few non-live-service games with an "end game" that I've enjoyed:
(Older) Pokémon games with their battle towers, where putting together a flexible team with as few weaknesses as possible is the aim.
Loot games like Borderlands, Grim Dawn and Last Epoch where I want to make new builds and test their limits against harder and harder challenges.
Factorio, where I want to optimise my factory. Although there's absolutely an argument to be made that that is the game, but I think it becomes more about player-set goals once you've launched the rocket.
All of them are either offline or have offline modes available. All of them have potentially infinite "content" if you're the sort of person who like optimising, or just being able to set yourself new targets. They're all enjoyable to play for their "campaigns" alone, but they also have very strong sandboxes that players can continue to engage with even after the game stops giving them objectives.
I don't necessarily disagree with your overall sentiment, though. I think MMO-style "end games" where you login for your daily, time-gated quests and do the same thing you always do with no variation or sense of progression (be it narrative, emotional, build-related or some other kind of progression) isn't necessarily healthy. And I dislike the way "end games" have tended to move away from being optional post-game content for people who aren't ready to finish playing yet and instead are often viewed as the main game that you have to get through the sorry excuse for a campaign/story to access.