It's really hard to say and I think it's a case by case basis. There are examples of games I like that cross the entire spectrum. Also, I feel like you missed one point of the triangle - it's story, gameplay/mechanics, and PORN.
I love AliceSoft games, even though they tend to go too far and have some elements I don't like. The fucking gameplay in Sengoku Rance and Daibanchou for example, is just amazingly good. Like they are as fun if not more fun than many regular non-porn games. And you get porn as a bonus lol. The stories are decent but the gameplay is the real deal maker in these games, at least for me.
Then there are games like Eternum, Love and Evil Things, Stray Incubus, etc... where they are basically VNs, but the story is fucking great, they manage to blend in humor, real human emotion, and get you to really feel invested in the characters. There is very little "game" they are practically just stories, but I still love them and feel engaged when playing them.
And finally there are games like Harem Hotel, A House in the Rift, The Headmaster, etc... where the story is honestly pretty retarded and the game mechanics are minimal/weak/non-existent - but the porn is so good lmao.
Variety is the spice of life and all that.