For all its reputation as a forward-looking, optimistic series about the future of humanity, why is it that Star Trek is consistently best when it goes into truly dark places?

In all seriousness, this seems to be an issue: the best of the original Trek movies is generally agreed to have been Star Trek: The Wrath of Khan, which was a bloody revenge story, a recapitulation of Moby Dick in space.

The consistently-chosen fan favorite episode of the Original Series is “The City On The Edge Of Forever,” which affirms that sometimes war is the only way to solve a problem, that addiction to peace at any price is dangerous, and that doing the right thing may involve accepting the death of what you love.

This doesn’t change in the Next Generation, either: “Yesterday’s Enterprise” is the fan favorite there, a trip into an alternate timeline in which the Klingon Empire is slowly destroying the Federation, and the Enterprise-D is destroyed saving the Enterprise-C and the original timeline.*
ETA: An Alert Reader pointed out that I had these backward. And no, I don’t, but I was very unclear. So, to explicate and thus restore my lost nerd-cred: The Enterprise-D, in the War Timeline, sacrifices itself to allow the Enterprise-C to return through the temporal discontinuity so that it (the Enterprise-C) can sacrifice ITSELF to save the original timeline.

Gosh, I wonder why Star Trek didn’t do more time-travel episodes.

None of these stories are without hope, of course, but they are consistently darker than Roddenberry’s vision, and certainly in opposition to his (much-derided) dream for first-season Next Generation of a future in which human interpersonal conflicts had pretty much been transcended.

My own feeling is that Roddenberry’s vision simply took too little account for what people demand in a good story, and far from inspiring people, ended up looking rather insipid, while what the fans wanted were stories in which our heroes laid it all on the line, sacrificing all that they were or wanted in order to save what really mattered. In the end, you cannot “transcend” these things. They are themselves transcendence.