Tramp is tolerable, but it is absolutely not great. You went on to demonstrate that right after making that claim, where you manually (and insufficiently) hack around its issues to arrive at something that is only barely comparable to eg what vs code can do.
Forgive my ignorance, but what does VSCode do?
Download a copy of itself onto the remote, run it there, and allow interaction with that copy
Emacs can run as a server, and you can connect multiple local clients to it. I've tried various ways to have an emacs client connect to a remote emacs server (forwarding a socket over ssh, etc.) but never gotten it to work so there must be more to it than just the socket.