a common characteristic of the ADHD brain “In an emergency or a situation which calls for focused action, my brain works with crystal clear focus effortlessly.”
For an answer with a neurological perspective I would consider the research on the reduced dopamine levels in the ADHD brain and the inverted-U shaped task performance curve. We can develop a reasonable explanation for what is going on for what you describe.
The situations that are an emergency or fully engaging create a stress that increases the dopamine level. With the increased dopamine level the ADHD brain’s frontal lobe and striata are then able to perform well with working memory and executive functioning tasks. This performance is your focused and effortless you describe.
This is an illustration of the dopamine performance curve with shifts in the baseline levels. It is from a paper on the effects of drugs on dopamine levels. If we start of with a deficit of dopamine, the endorphins created from physical activity or the adrenaline from stress it illustrates that ADHDers enter a optimum task performance of working memory and executive functioning.
from: Inverted-U-shaped dopamine actions on human working memory and cognitive control.
Stress isn’t always bad or an emergency situation as illustrated with this performance grid.

The psychological aspects of seeking to increase dopamine explains the thrill-seeking behavior and athletics. On the downside of your brain longing for dopamine can be addition from caffeine to meth and habits that create stress like waiting till the last minute to complete tasks or intentionally starting arguments.
Your brain longing to be fully engaged can be a productive and valuable trait, as long as it doesn’t control you. I agree it is magical and think of it as a superpower. However, just like any superpower if not focused and used for good it can hurt others and destroy the person.
Normal changes in life situations can bring about the impairment of ADHD in adulthood. If you get away from competitive athletics and do not find something to fill that vacuum you may find increased impulsiveness or not being able to concentrate. The desire for an emergency room environment, or MEDVAC helicopter pilot duty as in my case, can lead you on a transition to a fulfilling career.
I have devoted the rest of my life helping adults deal with all this and recognizing these traits as a Superpower. Some of my clients don’t have an ADHD diagnosis but possess the Superpower. I coax out the talents, develop the skills to use the power and have them reach goals that they haven’t even imagined.
Thank you for the opportunity to share my perspective with this well written question.