[I want a game] where a happy ending isn’t guaranteed in the slightest and the DM is fine to end the campaign in a total tragedy.

100% this. While I don’t think meat grinder campaigns are the only way to achieve this, I’m enamored by running a game like this. I’m currently thinking about my next game, and I’m torn between a VtM game set in Ukraine during the Russian revolution, or a PF2e game set in a civil war of my own creation, and I think that would be one of the best situations to run a game like this. I want there to be loss, and I think the brutality of civil war is perfect for it.