I think "ruining and destroying its value" is a bit hyperbolic...
Most mods are harmless, and in some cases, necessary. Some janky PC games are almost unplayable without fan-made mods to fix their technical issues, or otherwise improve the game on an objective level.
Some people will
mutilate a game with mods to absurd extents, but I typically mod a game to:
1) Improve its graphics, with textures or other mods that usually maintain the base game's art style, just with higher fidelity
2) Fix issues with gameplay and make the game more fun (e.g. improving game balance, altering mechanics to be more rewarding, removing unfair or unfun elements, etc.)
3) Add content to the game that I wish was there, but isn't (e.g. skins in Smash that "add" new characters, guns/armor in Fallout/Skyrim that I love, etc.)
On the subject of Smash, Project M is a great, quality mod that rebalances the gameplay and adds cut Melee characters, and was even going to add some new characters before the higher-ups shut things down so they could make a version of Melee that was otherwise one of the most generic things ever.