Are there any other "big name" actors like Bruce Willis that had a very tragic and surreal end to their careers?

I don't think that Val Kilmer will ever be able to make a full-scale comeback (at least as a leading man in major productions) given that his voice has been severely compromised due to his cancer ordeal. I think that in his last few films, his voice needed to be dubbed because his natural speaking voice has been reduced to a near unintelligible rasp.

I always heard that Sean Connery was the first choice to play Gandalf in Lord of the Rings but he turned it down because he apparently didn't understand the script and/or he didn't want to commit to such a time consuming project. Sean Connery would've made a lot of money had he taken it.

So he winds up doing The League of Extraordinary Gentlemen. He apparently, didn't really understand the concept with that either but figured, that he would look like a dummy had he turned down another potential blockbuster. Of course, we all know the rest, it was pretty much the movie that convinced him to retire.

Gene Hackman is 93 and doing what many in their 90s do, living a quiet life in retirement.

https://nypost.com/2023/03/07/reclusive-gene-hackman-93-looks-fit-as-he-pumps-gas-performs-yard-work/

Sean Connery had finally enough money and likely also got sour that he rejected Gandalf for the turd of "League of extraordinary Gentlemen", but he had such hit and miss always in his career. He mostly retired, because he was old and didn't need to act in movies anymore and had his pet project of Scottish independence. But in his later years he had also dementia, so maybe he got also the diagnose and decided to retire because of that. But he definitely had 10 more good years before dementia showed its really ugly face. His last years were like any other poor dementia person (something I don't wish anyone!)

I can never get over the supreme irony of arguably, the definitive live-action Superman portrayer becoming a quadriplegic in like, the last decade or so in his life. It's just like I get depressed seeing Val Kilmer in his current state after remembering him for his arguably underrated portrayal of Batman.