I love Stannis, (I remember Maester Cressen commenting about how unloved he was as a boy, it's a touching moment) but yeah, his arc seems to have been building towards a tragic end for a long time now. Stannis has been lead to believe his sword is the Lightbringer and he is Azor Ahai, but we know that neither is true - the sword doesn't work the way it aught.
But yeah. Another great moment for me with Stannis is when Davos confronts him on the logic of his own rebellion against the Targaryens and he actually admits to having been deeply conflicted about it, to choose blood over honour. That for me tells volumes about Stannis - not even Ned seems to have had any regrets in fighting the Mad King. Stannis's honour is harsh, brittle, cold, unloved, but constant - much like the man himself.
Ah, season two. Hell, A Dance With Dragons (where Stannis HAD BETTER PLAY A BIG PART). Can't wait.