Now, games with extensive singleplayer modes are great; you can play them through and really enjoy the story and setting. Until you finish, that is.
After that, you either need to buy a new game, or consider Mods. Some mods are small, some cover entire continents in game - but which games are easy to mod...
There are some games that immediately come to mind when you mention mods: Skyrim, and Minecraft. Both games (although completely different) have incredible communities that give out huge mods, completely free. The main place to find them is TES Nexus for Skyrim (and the previous Elder Scrolls games), or the Minecraft Mod Forums. There are of course other games that can be modded.
Half Life 2 is (although dated) also rather heavily modded, and many of these mods have been made into full games - take a look at Natural Selection 2, which retains its modding roots. Many though are just interesting, different games; take something like Hidden: Source, a survival game with one player as a nearly invisible hunter and some marines trying to kill it. Or something like Suicide Survival, a hilarious game in which you play a kamakaze shrubbery attempting to kill book-lobbing librarians (no, really). This is of course not to mention Garry's Mod, the game which is just a platform for crazy creations.
Other action games support modding to various degrees, such as Torchlight 2, with it's huge Synergies mod, or the Grand Theft Auto series. ARMA is also a series that is easily moddable, and includes an in game scenario creator - remember of course that this game is a realistic simulator rather than a game. Naturally we have to mention DayZ if we're talking about ARMA mods (Come play with GD Warriors, find the server info on the DayZ game page).
Moving away from the action games actually makes the mods even bigger and far reaching. Strategy games are very common, with games like Civilisation and Crusader Kings enjoying entire overhauls, for free. Other popular games include the Total War series, and occasionally a bit of Command and Conquer; or, for those who want a mix of first person combat and strategy, Mount and Blade has a huge selection of mods.
Here is a list of mods we think are awesome (dont forget to post the ones you love in the comments section below)
Skyrim - Tropical Skyrim
Crusader Kings II - The Elder Kings
Minecraft - Tekkit
Freelancer - Crossfire
Rome: Total War - SPQR
I'll be honest, I could go on for a long time. If you're interested in modding your games, there's a couple of places to start looking: Nexus Mods, the people behind TES Nexus among lots of others, have a large collection of games and files, and even have their own downloading software. Similarly, ModDB is a popular aggregator, including mods for a lot of older or uncommon games.
So GD, here's the question: You've played a lot of games, tried a lot of mods, and you've come out the other side. So tell us, which is the best game to mod?