I've been wasting my time on Battlefield 2 for the last few days. The release date is coming up soon, so i strongly suggest you get it. But if your pc isn't up to scratch, you'll probably have to run it on the lowest settings (i do, and it's no big deal anyway).

I also recommend HL2, but i finished it quickly and it kind of got old...
That said, there are a number of mods floating about.