Wow, thanks for sharing. Comprehensive post!
I can definitely relate to some of the games you mentioned:
Red Alert 2 - I put a lot of hours into this game and had a blast playing multiplayer in university. Loved the cringey cutscene videos!
Rome Total War - I started with the original Shogun, but I remember the jump to 3D being quite something. The only issue I had was I felt the AI deteriorated compared to the 2D games. On my first playthrough of RTW, I conquered most of Europe and never felt like I was close to losing a battle. Agree that playing with mods is the only way to get the proper total war experience. Some great ones out there. I had a lot of fun with Roma Surrectum.
Darkest Hour - Great game. Put a lot of time into the Hearts of Iron games but this one is very solid. Much more difficult to conquer Russia as the Germans than the base game.
I've always wanted to give the Gary Grigsby games a go, but the huge complexity has always put me off. I feel like I'd need to watch hours of youtube tutorials on it before I'd even be confident to load the game!
Good on you for experimenting with modding. Can add countless hours of fun to a game and increase its longevity.