Need a new game to sink hours in.

alan wake 2 . you not gonna regret it

as a fellow ADHD person...
Path of Exile... its a dopamine generator, slaying thousands of monsters in minutes, valuable loot drops, etc...
truly F2P... but you will want to spend a good $20-60 if you get into heavily for stash tabs/some cosmetics

I always recommend going into ARPGs blind for the first character, after that following a guide is highly encouraged(especially in POE)

if you state the titles you didn't like and what you didn't like about them, it will give a better parameter to help.

Having played some of the games you mentioned (Amalur, Fable, some Assassin's Creed) as well as ADHD, I think I get what you mean.
The only thing that comes to mind right now is Prototype, the first one especially, but also Prototype 2. They're more action than adventure but I found them really action-packed, the movement and skills are great fun and it doesn't get boring very quick. I've last played them many years ago but I remember them fondly.

As you love Wolfensteins and they are shooter with a good story, maybe you should have a look at Bioshock or Borderland games series .
Maybe also Far Cry series as it is like Assasin Creed with gun.

The Shadow of Mordor games and Batman/Arkham franchise are two good AAA series that can be had pretty cheap and are alot of fun.

Fallout 4 is another good one. The series has other good games (some would say better) but 4 is a more polished, user friendly experience.

The most recent Hitman series was a blast to play but might not be everyone's cup of tea.

As for something a little different I'm always a fan of promoting Ring of Pain when given the chance. I put over 200 hours into that unassuming little game that I won on here. Its just so easy to play a few runs, put it down and come back later for more.

EDIT - will also recommend the suggestion of the user above me of the Borderlands series. If you just pick one, probably go with Borderlands 2.

Play Super Hexagon. You don't need more than a minute per level, so short attention span should not be an issue - and it's really fun! :)

Max payne series specially max payne 3.
Mass effect series specially 2 and 3.
Splinter cell conviction and blacklisted.
Skyrim legendary edition( its a very long game)
Saints row 3 and 4
Tom clancy's Ghost recon series from future soldier to breakpoint
Watch dogs series.
Battlefied series specially battlefield 1(2016)
GTA V.
Spec ops: The line.
Red dead redemption.
Fallout 4.
Counter strike 2.(multiplayer game)
Shadow of war and shadow of mordor
Madmax.
Vampyr.
These are my personal favourites.

I wonder if you might like Torchlight and Torchlight 2. They are reliable dungeon adventurers in the Diablo vein, and very popular. I recently came back to Torchlight after years away and am digging it once again. Fun, lots of action and rewards, easy to get into. Third person solo play, maybe watch the trailers and see if they appeal. I don't know how ADHD would make them feel, just tossing them in as an idea.

Perfect game for you, bioshock collection, and trust me it is NOT overhyped like I came in thinking, i was spoiled atleast 6yrs before i tried the series and boy was it so good even with that knowledge, if I had come in with 0 knowledge I know itd be the best game ever to me. I love recommending it to people who haven't tried it yet because some games really do live to that hype

Ever tried the newer DOOM games? They're mostly demon slaying action with a splash of story.
The Metro 2033 series is also pretty good. More story than DOOM and a bit more of an immersive post-apoc adventure-y shooter.

It's a tough one indeed... You might enjoy Cruelty Squad, since it's weird enough to basically force you to learn the rules on the go. You might also enjoy Post Void, it's not adventure but it's a short, fast fps that will hold your attention. Last but not least, Hotline Miami 1 and 2. Ultraviolent, fast and fun, but top down. I'm not sure if they cover how much adventure-y you'd like your game to be, and each one is fairly short but cheap so you can jump from one to another

I really liked Control. It's really fun to beat up guards with furniture/concrete pieces, while collecting and reading lore about X-files/SCP-like items and how they work. I felt really at home in the slightly Lynch-inspired world and story.

Witchhand is worth $12 for a good 8~16 hours of shuffling cards.

Chinese Parents - easy gameplay, collecting traits is dopamine inducing.
Rain World - hard game, but satisfying to explore it. You have to survive in an post-apocalyptic eco-system as a tiny creature.
Binding of Isaac: Rebirth - tons of achievements, tons of mods, 600+ hours of gameplay to unlock everything.

Witcher 2, 3,
Fallout 3, New vegas,4,
Cyberpunk,
Skyrim,
Mass effect,
Watch dogs,
Deus ex human revolution, mankind divided
Spider man

life.

DOOM (2016) and DOOM Eternal are both amazing. I enjoyed 2016 more, but I think I'm in the minority with that opinion.

If you have a Playstation, you can't go wrong with the first 3 Uncharted games. First one hasn't aged as well as the rest, but it has a special place in my heart. 2 & 3 are a lot better than 4 & Lost Legacy.

Far Cry 3 and 4 are both a lot of fun and very easy to get lost in.

Horizon Zero Dawn is another one that's easy to get lost in due to the amazing world and fun gameplay.

NFRA

If you get bored easily of too hard games, then maybe you should try something like "easy to play, hard to master" f2p. I personally played World of Tanks since 2010 till 2012-13. I can say it is fun, was at least, and have a very good depth as you need to gradually learn about different tanks and their specifics. I even more liked to play it for free, than when i paid for a 1 month premium and all that month i played this game not taking it seriously, since i was not punished for losses. I dropped it when i understood that it is taking too much of my time, and the fun started to fade.
Maybe as a difference - i heard War Thunder also good in that genre. GL to you and have fun!

I also suggest Witcher 3 -- just make sure NOT to go for every little cache in the map.

I would recommend getting an additional hard drive and installing multiple games on it that you might enjoy. Then rotate so you don't get bored.
Its what I've always done. Since its just for games you don't need an expensive SSD. Any 7,200 rpm hdd with a good cache will do.

For 1st person I would recommend:
Generation Zero,
Skyrim,
Far Cry 3, 4, 5,
Deus Ex HR & MD,
Dishonored series,
Metro Series,
Bioshock Series,
Mirror's Edge series
Dying Light
All are single player games you can enjoy at your own pace.

Wild Hearts- amazing game, addictive gameplay, ok story, creative monsters, Beautiful everything.

you're a lot like me in terms of the games you like. i would highly suggest project Project Zomboid

It's a pre-release indie game with hours of high-octane content and it's fully moddable with a steam workshop. it's still releasing big updates and is a singleplayer and very difficult open world survival game.

Maybe it would be the wisest if only other people ADHD made suggestions. I, for example, would recommend the Yakuza series. Not very complicated gameplay, tons of optional subquests and great characters and stories. But I have no idea how this would suit you.