With little to lose and much to gain, I spent the remaining of the weekend after ordering a new desktop computer to replace the one that died on me trying to fix it.
By chance I noticed a red LED stayed on on the motherboard through the transparent side panel during one of the failed boot up attempts. Remembered something from work about such thing, I went to dig out the motherboard manual. Found the relevant section in the manual which says it's memory module related issue.
Luckily I have a spare 2GB DDR3 module from old retired computer that I could use to replace the 8GB on the motherboard. The system booted to Windows after that! Slow as snail but at least it booted to Windows and stayed on without issue.
My first priority is to get to my data and to back them up since my last back up was in January. But I could not log in to my user profile.
After a lot of Google searches and frustration from trying solutions that don't work. I switched my train of thought to look up fixes for corrupted files instead of corrupted user profile. The thing that finally let me get my data back is the combination of the 'System File Checker: Scannow' and the check disk 'CHKDSK' (/f /r fix and recover) commands, which took hours to run that I fell asleep and woke up middle of the night to turn off the computer when it's finally done.
Can't use my old user profile anymore but at least I got my files out from the corrupted profile, if not all the settings. For which I am grateful.
I switched the 8GB RAM back and it works as well, so it should not be the memory module, unless it's temperamental. I have also gradually reconnected the additional drives and the graphics card to the bare minimum system. I am not sure of the exact cause of the issue but I already did a thorough cleaning, unplugged and re-plugged in of all connectors the day before...
My guess is temperature related trigger of power shut down (or a dying power supply unit) leading to corrupted files, data, profile and bad sectors. Oh, and possibly an interrupted Windows update that caused all sort of problems.
So far the computer has been behaving, I am keeping my fingers crossed until my new computer arrives.