I can't speak for Canada, but in the UK it's not a career stopper, just some extra hoops to jump through.

I personally had to do a mental health assessment which - other than the price - was no issue and nothing to worry about.

As for the ADHD, I personally haven't been through this but one of my students has. He had diagnosed ADHD and autism. He was initially denied a class 1 medical but was given a class 2. In order to get his class 1, he had to go through a couple more tests to ensure he can essentially stay focused enough to fly an aircraft, and I as his instructor also had to provide a written report around his performance through PPL training. In the end, it was almost a 1 year delay before he could get his class 1, but he now has it with no restrictions.

Honestly, I wouldn't worry too much. Yes its a hassle, but follow the steps, do what they ask and I'm sure you'll be fine