There are, fundamentally, 3 types of evolution:
1: Evolution that lets a mon stay at the appropriate point on the power curve (Starters, Pidgey, etc)
2: Evolution that gives a major power boost too early and invalidates a lot of the incoming enemies (Kadabra-Alakazam, Magikarp)
3: Evolution that comes too late and renders a poke irrelevant for the bulk of the game (everything in Gen V)
It's not really quite that simple imo.
Type 1 is basically the bulk of three-stage Pokemon, including the starters themselves, the regional birds, and Stoutland, Ampharos, Luxray, etc. but this is more or less the bulk of three-stage evo lines barring pseudo-legendaries who are deliberately meant to be late bloomers. Weak early stage who is good for early game, gets a power boost mid-game to keep up, then again at the mid-late game so it will continue to be a strong ally.
Beyond the other two types you listed, there is one other type of evolution. The early bloomer. This is most early route bugs like Butterfree, Beedrill, and the kin, as well as rodents like Raticate, Watchog, etc. These Pokemon reach their full potential early and the peak of their power, and can take on incoming enemies very well compared to the other options you will have at that time, but they are only powerful enough for the early-mid game and as the power curve increases they don't keep up anymore and will inevitably become more of a burden than an asset because they reach their full potential early but fail to keep up with the power curve and will need to be replaced as your available options increase.
As for the other types you mentioned, Type 3 depends on the position the mon was originally at, and in many cases this applies to two-stage evolution lines. Several Gen V mons evolve very late, but it was workable because said mons were also
obtained late as well. So you don't have to spend too long in the unevolved state because you only need to spend a few level or so before evolution. Take something like Pawniard, Rufflet, or Vullaby, for example, who are found around the high 30s/low 40s in BW1, which means you spend around 10 or so levels until you evolve them. Not too bad of a hassle in that context. Or Rhyhorn or Ponyta in Gen 1, where they were high evo levels that evolve in the low 40s. Ponyta is found around Level 28. Rhyhorn around Level 26. Around 16 levels to raise Rhyhorn, 12 for Ponyta. However, this backfires in the event of future availability in the event that said mon that placed in an earlier location and the evolution levels aren't fine tuned around it. This creates an awkward position especially when you have Noibat, Rufflet, and Vullaby available extremely early in the game in the Gen 7 games at around Level 10 but still need to drag them for around 40 levels to evolve them. Or even Gen 4 Ponyta, which is available very early but still needs to be dragged all the way to Level 40 just to evolve it. If you're going to introduce the mon earlier, the evo levels need to be fine tuned accordingly. Dreepy and Deino also fall into this category and we've beaten those horses quite a few times by now.
Type 2 is what you mainly addressed and yes, they don't really balance the evo method availability very well at all sadly when it comes to the more arcane methods. This is an even bigger microcosm of what happens with Type 3, except it's now with the stone/trade/location based evos which is entirely up to the game itself: in some cases, it works well in the context of the debut game it started in, but it works horrible for the future if said method is either non-existent or is available too late to be of use.
Really it all boils down to Game Freak more often than not only thinking in the context of the game that each Pokemon is created in, and not thinking ahead for the future. As such, many evo methods, and in many cases, evo levels, are not future compatible or future games just do not balance themselves accordingly with what the debut games intended: sometimes not even in the debut game itself like in Gen 7. Game Freak doesn't really think ahead, and more often than not doesn't tweak evo methods accordingly.
from what I remind the dens that are active are the usual timestamp-based ones
