Skyward Sword Zelda is such a tragic figure in my opinion. Just put yourself in her shoes and imagine this.
It's the best day of your life. Your dearest friend, dork that he is, has finally become a knight. It's what every kid on Skyloft works towards and he finally did it. You're so proud of him. When you fly together, you muster up the courage to tell him you love him.
You never get the chance.
Instead you're whisked away into a world you believed was left behind, and saved by a woman who declares that she is your guardian, chosen by you. You have never met her before. You didn't even know there were people like you who lived down here, in the Surface.
"You must purify yourself if you are to transcend time and hold the seal," the mysterious woman with the painted tear remarks as she shepherds you through strange destinations unlike anything your books have ever taught you, "it was your will." No matter how many times she tells you this, in every iteration the language could allow, it doesn't make sense. Why would a goddess need to turn human? What could you do, that she could not?
Where does divinity and humanity diverge?
Connection.
A goddess is revered by her people who pray, in spite of her silence, for her benevolence and guidance. She is their unwavering stone, a higher power to rely on. But a girl? A girl is loved. She is someone tangible, a figure who people will see, and know, and care about, and fight for.
And that's when it clicks. Your friend isn't really your friend at all, but a hero, a pawn, who was intended to be used against an enemy of yours you no longer recognize.
You're using him. You've been using him all this time. It's sickening.
With each prayer, with each goddess damned spring you rush to, you are faced with your own marbled reflection, a testament to the fact your humanity is only a pretense, carefully timed to ensnare your friend into a hero's fate.
He doesn't seem to understand that though. He keeps running after you like the fool he is, hoping to save Zelda, his precious Zelda, that you no longer are. The smile you wear becomes harder to hold. You were Hylia first, and that is all you will ever be.
You play into the charade anyways. After all, Zelda was the reason why he went through his trials. To tell him now that she was gone would mean to destroy everything you had worked for. So you tell him everything he wants to hear: that you're your father's daughter, that you're his friend, that you're his Zelda.
And when you close your eyes, smiling from within the amber and ignoring the dull thuds of his fist against its surface, you wonder if you look anything like the statue you and your love had stood upon on the best day of your life.
"Maybe all of this is a dream," you wonder while drifting in between millennia. Time passes like the waterfalls in Skyloft, rapid, yet everlasting. Maybe you'd wake up in your bed in the Academy again. Your love would have been sleeping in (again) and everything would be how it used to be. You could be Zelda once more. And most importantly, Hylia would be nothing beyond a giant statue for you to ignore for the rest of your days.
... There's something to be said about how you fall again once you wake up.
"What kind of goddess am I," you think crudely, "to sever my own wings?"
But this time, your love is there to catch you. And he does. In that moment you pray, in your own name, he doesn't let you go.
