Indeed, I consider the Space Shuttle an incredible huge engineering triumph. Basically built in the 70ies, it up to date is the most capable space vehicle built so far. Large crew compartment, huge cargo compartment, can fly space missions for weeks, allows extra vehicular activities, can take cargo down to earth and most importantly, can land on any large airport.
The problem was, while all of this was achieved, the resulting design was a bit too brittle (heat shields) and definitely too expensive to maintain between flights, so the prices were just off and the desired flight frequencies were never met.
While the engineering of the first edition of the shuttle was just great - especially considering some of the disputable design requirements - the failure rather lies in the fact that it never got a meaningful update. There were ambitious projects to produce a "next gen" shuttle with fixes "all" problems with the first gen, but they all failed. What was missing was updates and iteration. Just build a new shuttle which improves some aspects and keeps the rest of the design. Like a new heat shield, or just optimizing internal structures. That might have driven costs down and produced versions which still could be used. As, for example, we lost the capability to service the Hubble telescope.