XBMC is far and wide the best option for media management, scraping, and playback. Plus the new version (Frodo v12 currently in RC3 stage) will include PVR options in case you decide to keep your cable/satellite TV.
I'll fill you in with more details when I get the chance (still at the office right now).
UPDATE:
Alrighty, sorry for that extended delay.
Re-purposing an aging system as an HTPC is a fun project, I especially love seeing it perform with a flashy front-end interface. Getting teh hardware up and running is the first challenge. I highly recommend setting a wireless kit (Keyboard with trackpad, KB+mouse, or a remote control), that will definitely make it convenient to sit back and take control. On the software end, as I mentioned, XBMC is the best option all-round. Great interface and support for remote controls (plus in v12 RC3 there is support for gamepads as well).
The next challenge is managing your library. The critical part is making sure all your files are in the right places; basically keep TV shows, movies, and music separate. When you begin adding sources to your XBMC library it will automatically scrape for the info from online databases (defaults: TMDb for movies, TheTVDb for shows, and AllMusic or Last.fm for Music).
Aside from that, adding in some video add-ons will really enhance the experience. I don't know about you, but the Youtube website is usually a pain to deal with especially if I'm just using a keyboard; the YouTube add-on in XBMC makes it more TV-friendly too. The Vimeo and GiantBomb add-ons work fairly well too. I know there are some Sports video add-ons in XBMC but since I don't use them I can't really vouch for any.