I promise you, Twilight Princess has some great moments, some of the greatest in the series in my opinion. If you ask me, it has one of the best Hyrule Field themes, the best Link tunic designs, the best partner character in the series, fantastic boss themes, and some truly epic showdowns with some of the major antagonists, especially Ganondorf at the end.
However, it's all held back by the agonizingly long intro. Every time I play it from the start, I'm just thinking to myself "I just want to get to the cool parts! Why do I have to be a goat farmer for THREE. FUCKING. HOURS?"