I can speak on the ADHD aspect of that at least. As for the other things you've described - those might be more anxiety-adjacent issues, and aren't something I can speak to. I've still dealt with depression a few times - but each time that had it's root in untreated ADHD or autism spectrum issues.
Personally, the issue for the longest time was that I didn't understand my own issues. I went way too long without seeking a diagnosis, because I mistakenly believed that medical treatment wouldn't be helpful or attainable as an adult. Turns out medical treatment (psychiatrist - not a therapist) helped me a whole lot. So did doing detailed research on ADHD. The work of Dr. Russel Barkley on ADHD as an executive functioning disorder was particularly informative.
As for trying to reconcile my ADHD with a (healthy) sense of motivation - the best thing I found was actually from some random blog post that basically summarized the information in this article (https://www.additudemag.com/secrets-of-the-adhd-brain/ ). The brief summary: most people without ADHD have behaviors which are motivated by the concepts of Importance, Rewards, and Consequences. People with ADHD are aware of all of those things existing, and often do "care" about them - but if you look at the behavior of people with ADHD - those aren't the things which actually motivate us to action on a regular basis. Instead, we are motivated by Interest, Challenge, Novelty, and Urgency. It's not ideal, but even with medication, I've realized that is true for me - and I can't change it.
For some jobs, like my first post-college job working as a Support Engineer for Amazon AWS - those motivators were actually great. Tickets have really short-term SLAs and are often urgent, AWS was a challenging and interesting work environment, and for the the first year it everything was very novel. However, when I progressed in my in career towards more of a DevOps role, I started working somewhere which practiced two week sprints for everything - which was a total disaster. Kanban worked a good bit better, but no matter where I worked - the novelty, interest, and challenge of the work eventually wore off - and relying on crunch time to motivate myself was causing burnout.
I did think the consequence of guilt or shame from disappointing my teammates was a motivator and felt stress related to that. But in truth, that feeling was something that I mostly felt after I had forgot to do something or ran out of time - and not something that drove me to get work done.
So now I'm just leaning in to the whole thing, risks be damned. I left a pretty cushy remote-work salary job to build a startup, and I've started doing some freelance work on the side for extra income. I pick what I work on, so I'm super interested, I'm challenged frequently, it's novel (for now) and the freelance stuff is urgent because I don't get paid til I deliver. For stuff that is boring and executive functioning related (paying bills, scheduling meetings) - I try to automate as much as possible and be sure notifications for things go to both me and my non-ADHD co-founder who can nag me if I miss something. If I see a lot of success, I would like to pay a personal assistant or secretary to keep things organized / handle boring communications on my behalf. I spent a few years working for an executive who clearly had ADHD, and having an assistant was incredibly helpful to him.
I'm aware this could all crash and burn - but my daily needs are being met and I'm enjoying my life a lot more. It does feel like I'm working smarter. Though I am technically working harder too.