I have some good news and some bad news.
Good news is that three days passed after I replaced cables/port and my Seagate drive is working like a charm, so it looks like I don't have to purchase new drive after all.
I left it running 24/7, every parameters are looking very good and there are no issues with crashes or bad sectors, CrystalDiskInfo is showing healthy drive.
One thing which I'm fairly sure wasn't mentioned as yet... have you checked the actual voltages coming out of the PSU?
There are some utilities that might be able to display these values... but the most accurate way is to check using a multimeter and direct measuring the values.
A number of years ago, I had a PSU that was "dying" and was "occasionally" only providing ~10-11V on the the 12V rail... probably didn't help that I had a stupid number of HDDs installed in a stupid RAID array
[1]But I digress, the "less than ideal" voltage meant that I was getting weird issues with a drive "disappearing" randomly and/or straight up data corruption and r/w errors etc.
At first I suspected it might be the drive, but it came up clean in all tests... I changed cables etc... still got weird stuff happening. It wasn't until I checked the voltages and noticed that the 12V rail was sometimes dropping down to values nowhere near 12V that I was able to finally figure it out and solve the issue.
New PSU... "proper" (and stable) voltages... and the drive worked perfectly after that.
As for the "best hard drive for a node" question... I'm fairly happy with the blocks on (1TB) HDD and "chainstate" symlinked to SSD solution that I've been using.
Not sure what it would be like with an IBD, but I've got 2 copies of the blockchain at the moment... so I don't anticipate it being an issue. Even when I set up my Pi node using one of the "cheap" USB3.0 1TB Seagate external HDDs (firstly with RaspiBlitz and then Umbrel), I was able to shortcut that process quite substantially by simply copying all the required data from my other node instead of having to "suffer" through a full sync.
[1] - RAID10 with a bunch of old NAS drives I got gifted by a friend... because I could (Mobo had a built in RAID controller)... I had never used RAID before and felt like experimenting with stuff... and then realised I had too much data installed to "undo" it all