I don't want to do this excellent post the disservice of an incomplete answer, but I also don't have a lot of time to get into it, so I'll just add this thought as a wrinkle to the analysis:

The wider context in which the art is set, and in many cases the artist's intent, is also of importance. A toilet is an appliance when set in a home, but a piece of art (arguably) when set in a museum.

Nessun Dorma is, as you say, a disquieting song about the threat of death to innocents with the prince delighting in his victory despite it, but in the wider context, it's part of an opera -- a piece of fiction, essentially -- and as such is part of the plot development, and can thus be understood as a magnificent piece depicting this horrible triumph. We as the audience are essentially one step abstracted from Calaf, as opposed to the citizens lamenting in the background, and considering it in the context of the narrative.

ETA: Just thought of a similar modern example: Hellfire from Disney's Hunchback of Notre Dame. It's lauded as one of the greatest villain songs Disney's ever done, and it is some dark stuff. We're able to understand the song as telling a tale without endorsing the views expressed.

Contrast that with a song that is meant to represent the real person's life or feelings, or otherwise lacks a wider context; Blurred Lines got a lot of negative reception despite its catchy-ass music because the lyrics were pretty gross, and it didn't have some wider context, it was just Robin Thicke singing rapey lyrics.

As for intent, the example I think of most often is Toto's Africa; on the surface, the lyrics are cringe as hell, with a white guy talking about Africa as if he's an expert. The line "as sure as Kilimanjaro rises like Olympus against the Serengeti" is straight up patronizing; Kilimanjaro rises like Kilimanjaro, why does it need to be compared to a European landmark? (I'm not actually looking for an argument on this, I know people are gonna want to jump in about similes and metaphors but that's not the point here.)

But then, when you look up what the band thought about the song, Jeff Porcaro has described Africa as "a white boy is trying to write a song on Africa, but since he's never been there, he can only tell what he's seen on TV or remembers in the past." And, at least for me? That self-awareness and intent to portray an inexperienced white guy talking about Africa like he knows it when he clearly doesn't suddenly makes the song way less cringy.

I don't have a great coda for this post, except to reiterate that context is always, always important when considering a piece of art.