Explain to me, what's the deal with the Steam Workshop?

From a user's point of view, it's absolutely fantastic. It's like Nexus Mods with a built-in mod manager that just works. It's absolutely phenomenal. I'm yet to play a game with Workshop integration that doesn't work well.

That being said... What's the deal with it? Why just 0.1% of the games have it "enabled"? Is it any costlier for the developer?

Also, why would the developer have to agree with it being enabled? Can't Valve just have it integrated by default, like it has with Cloud Saves and the Community Forum? I mean, people sharing artworks of the protagonist as waifus with giant breasts on your homepage is surely just as harmful to your IP than people modding some hairstyles into the game.

It baffles my mind. It's such a fantastic feature, good enough to drive Nexus Mods bankrupt...... Yet.......... It's straight up relegated, one uses it.