We have a lot of problems in my county with copper pipes leaking. There are entire plumbing companies dedicated to finding the leaks and repairing them. There are still more companies dedicated to repiping.
I know of developments where they epoxy coated all the piping because they were getting so many leaks.
You can read about the huge class action lawsuits going on involving the local water utility (who is being blamed for water chemistry causing the issues. They point fingers elsewhere, obviously).
I’ve seen a copper pipe where in the middle of the pipe there is a pinhole leak on the top side of the pipe (too far for turbulence, can’t be cause by sediment).
If you want the most durable, I would think it would be either pex-a or perhaps 316 stainless steel (which is a valid code compliant option). But no one really knows, and it’s probably water chemistry dependent. It’s being argued in the courts now (for whatever that’s worth).