Agreed but if that point can be reached, surely there must be some 'emergency red button' which just disables all automation and "hands back manual control" from whatever law or state the system is currently in/resets all warnings.
That misunderstands the nature of the problem. Yes you can effectively get back some degree of manual control. But you get in a problematic situation, in an instrument flying scenario, and you have an automation system that is giving you tons of warnings, it is far harder to see what is wrong so you can take corrective action.
Think of it this way: For unsophisticated automation, you go a little from expected and you hand to the human while things are still a ways from critical, and the human can react and correct it. For sophisticated automation you do a better and better job until when you hand it to the human, the human has to be super-human in terms of filtering out what information is relevant in order to recover.
For example, suppose you get a stick-shaker and an overspeed warning at the same time. Which do you follow? If you have thought about it ahead of time, maybe you know that the stick-shaker should take precedence. But now imagine you have 5-10 errors and you need to figure out which ones you need to focus on, and you have to do so quickly, having just made a major context switch.
So it goes way beyond "just hand control back to the human." That's what AF447 effectively did, but with all the warnings, that didn't do the humans much good.