I feel like since the Gamecube the series has really struggled with narrative (as far as plot and character writing is concerned). Ironically, I feel this is where they started trying to put a bigger emphasis on that and leaning into it in their game direction.
I feel in some ways Wind Waker was one of the stronger entries as it added a bigger emphasis on plot but still kept it pretty light with simple universal themes grounding the whole experience (much like Link's Awakening and the N64 entries). It is also the first entry where I felt they tried to make the narrative more ambitious by trying to have links back to prior entries and character-based plot moments. I feel though that it is still the entry where the plot started getting in the way a bit. I think my biggest issue with the story in Wind Waker is how they handled Tetra as a character. I understand that they were trying to replicate the impact of "Sheik is Zelda" from Ocarina of Time but it just fell flat here. Especially with the weird white washing of her character and the way it completely undermined her agency by having a character who was established to be brave, smart and resourceful become damselled the moment she found out her true identiy felt really off to me, even as a kid.
Twilight Princess, I think conceptually was ambitious but it is so so messy in execution. This was definitely the entry in which the inspiration from JRPGs and Anime became most apparent as they started adding more NPCS implicated to the main plot with little arcs and tried to add more twists and shocking points to the narrative. The problem though is that they really handled this poorly and most of the side characters are really poorly defined with hardly any characterization at all. Even if I were to ask the most hardcore fans of the game to describe to me what characters like Auru, Asha or Renado are like (without using physical descripters), I feel they would be unable to do it. Likewise, major plot points that could have been bold and shocking always ended up resolving themselves in ways that undermined their initial impact (namely Zelda dying early on, only to inexplicably come back at the end of the game). I do feel Midna having an arc at all was a step-up for the writing but was still rooted way too much in anime tropes for my tastes and is a little overhyped by Zelda fans (though credit where credit is due).
Skyward Sword, I feel just told a really uninteresting story that took up way too much of the experience. I feel it was a step up in regards to characters like Groose (who actually had a satisfying arc) and the focus on Zelda and Link's relationship (which much like Midna, was pretty standard and the quality of which was blown out of proportion by the fanbase but was still better than what came before). However, the actual plot was all over the place and I think undermined the pacing of the game and is part of the reason the vibe is way off. Link's goals are constantly reactive and the reason he is never able to fully achieve them throughout the story feel completely arbitrary and out of the player's control. As a result, I know a lot of players drop off because it feels so meandering and pointless. I think giving the player a simple goal (stop ganon, stop the moon from falling, etc) works best for Zelda as the narrative strength of the game is the actions the player takes (dungeons, side quests, etc) on their way to their goal over character or plot moments (with entries like Twilight Princess succeeding in these metrics but failing through the more traditional narrative elements).
I think that because of this, Breath of the Wild was much better for me. I know this may be controversial here but I feel that Breath of the Wild's story was a step in the right direction. The focus on building out the history of the world over specific plot elements really landed for me and added to the sense of adventure and discovery. It wasn't perfect and I felt some of the revelations were a little underwhelming but it did feel like a step in the right direction.
Tears of the Kingdom however was not it. I feel in a lot of ways it may have the worst plot in the whole series and easily has the most embarrassing writing. While Twilight Princess may be messy and Skyward Sword overwritten and meandering, neither game made me cringe as much as Tears of the Kingdom. The writing and delivery was just awful and was so reliant on contrivance that it did honestly impact my enjoyment of the game. Everytime I heard them talk about their secret stones, I couldn't help but laugh. Honestly, while Breath of the Wild already leaned in this direction, I feel like the tone leaned way too modern anime for my tastes in writing, voice direction, etc - just a lot of people yapping about nonsense and power levels with very little subtlety. Likewise, the fact that the structure of the main adventure so closely resembled that of BOTW, undermined the narrative as gameplay angle slightly. With that said, it has a lot of great moments (namely the master sword, adventures to the dungeons, end fight and post-game scene with Zelda) but they feel kind of like empty spectacle to me. So for me, I think I would go Tears of the Kingdom though Skyward Sword and Twilight Princess are pretty close contenders.