Musk is definitely not an idiot. His success with SpaceX proves that. That doesn't preclude making mistakes, even big ones, but it should give one pause before jumping to conclusions.
My problems with that are several.
Firstly, Musk doesn't run SpaceX, Gwynne Shotwell runs SpaceX. Musk provides some vision and helps to raise capital but the day to day running is done by her. The engineering, on the other hand, is done by talented engineers, of which Musk isn't one.
Secondly, we don't know how successful SpaceX really is because it's a private company. It's doing very well in the space launch market in terms of volume but we don't know how profitable each launch is. Musk says that Falcon 9 is very cheap, but we only have his word for it that that is true. There hasn't been a year since its founding that SpaceX has not sought external funding, sometimes to the tune of billions of dollars. It seems suspicious to me that a profitable company would need an injection of a billion or two dollars each year.
Thirdly, there are two current projects that each may sink SpaceX: Starship and Starlink. Starship is currently going nowhere very fast. They were supposed to be sending two of them to Mars in 2022, but so far, it hasn't even flown. The business case for Starlink was always questionable and if the numbers Musk gave to the government with respect to the Ukraine dishes are correct, there's no way it is remotely profitable.
One obvious possibility is that this isn't about the money, that he isn't doing this to try to get richer. He'll still be unimaginably wealthy even if Twitter closes its doors tomorrow. Bezos didn't buy the Washington Post because it was a profit center. Twitter is obviously a much more expensive purchase than WP, but Musk likes to upstage Bezos.
If Twitter fails, there is a scenario that bankrupts Musk. His unimaginable wealth is just a number based on the stock price of Tesla. If he is forced to start selling Tesla stock (say because of a margin call on a loan taken out to buy a failed company), its price will tank which means he'll have to sell even more stock. Quite a lot of Musk's stock in Tesla is already leveraged for personal loans. He may find he gets to a point where he can't service his debt. It's unlikely, but not impossible. Then there's Saudi Arabia. If Twitter fails, he might be called to an embassy meeting.
I think Musk knows he made a big mistake in agreeing to buy Twitter. That's why he spent so much time trying to get out of the deal. This is not some genius move or even a vanity purchase (although it may have started that way). This is Musk being cornered into following through on a purchase he didn't want. This is Musk panicking because he is screwed.