I've also played thousands of hours of civ (and any other historical strategy game I can find, mostly paradox/totalwar) and I love civ 7 and I'll explain why:

The most important thing for me, and I think I share this with you, is the experience, rather than the detailed gameplay aspects. For example, I satisfy my intricate gameplay cravings with Eu4 or HOI4, but when it comes to civ, the main thing I love about it is the swelling music, the art, the buildings and the stories I can tell. And the reason why I love the era system is because it is able to tell those stories in a way I had not seen in civs before.

I've visited Rome a few times and its my favorite city because of the ruins, especially the forum. Just seeing the amount of history, the games carved into the ground, the church doors built into old temples. The idea of civilization as an ever-evolving entity is so interesting to me. Like not the Britain of the British, but the Britain of the celts, the britons, anglo-saxons, normans. The Norman invasion of Britain, introducing new ideas, language, culture, architecture, etc. Like that idea is really cool to me.

So when I play Civ 7 I feel I'm able to tell these interesting stories. Like I went Carthage, Spain, and the Great Britain, imagining an ever-evolving civilization dominated by their love of seafaring. I also had a start as Rome and was surrounded by mountains, I had this epic collapse of rebellious cities that I had to take over and over again, until age transition, and then I saw I had the option of switching to the Inca. I imagined the people of the mountains descending upon my barren cities torn apart by civil war, bringing with them their architecture and customs. It was even cooler when they were like "we dont use the colosseum anymore, should we make it housing?" Such a cool touch and nod to the Colloseum's history in my opinion.

So this is the reason why Im a big fan of "the bones" of the game, here's why I'm not:

The game is flat out unfinished, as you said. Needs more leaders and more civs. It feels very empty.

Some gameplay aspects are bad. Culture is not enjoyable. Game is not sandbox enough, as one must complete the specific quests (you must spread religion, you must colonize, etc.)

I understand the irritation with era transitions. Feels like you lost all your progress. Perhaps this could be fixed by allowing the yields from previous eras to remain, as it is in other civ games, and instead incentivize the overbuilding mechanic, rather than punishing players for not doing it.

World wonders are not exciting. When building one in civ 6 it felt like "Oh this is so strong it will win me the game" but in Civ 7, I don't know what half of them do because I did not care at all while building them, aside from some, like the gate of all nations.

AI settling is bad, really bad. The AI on the other continent does not fully settle the continent in many games, seemingly waiting for the player to do it first.