I am not sure if this is a case unique to my situation, but I finally solved the issue.
The thing is like this : All these problems started happening after I installed a 2GB RAM module into my laptop. The laptop specification says that it can handle max 3GB RAM. I have 2 slots of RAM and the 2GB RAM was installed in the second slot and one of the existing 512MB RAM in first slot ( I had to swap out the other pre-installed 512MB ).
I tested my WiFi card on another laptop to verify if it was dead - it was indeed working.
Hence, I brought back my laptop hardware configuration back to what it had been ( using existing 512MB RAM cards ) and the WiFi card started to "work again" - Windows 7 installed drivers for "PCI-PCI Bridge" and then the AR5007 driver.
Before deciding to return the 2GB RAM, I just gave a last try and swapped out the 512MB in the first slot and inserted the 2G.
It's working even after multiple reboots.
I am not sure if I will face problems in the future ( previously, the issue was intermittent before the wifi went "dead" the last week ) - but I am hoping for the best.
Thankfully, I have some more information to work with, the next time, forbid, my wifi goes dead.
However - I am still stumped that there was no software that detected the wifi card ( might be because it was some kind of issue/address conflict deep in the motherboard itself at the hardware level? )