How do the auto- adjust tempo settings work? Does it factor in fatigue/ stamina of you and your opponent?

In my experience it is based on the score and time remaining in the game.  I doubt that fatigue or stamina are considered.

does anyone feel like they're a good idea?

I've used it.  I'm not sure I follow the "smarter than the computer" line zhawks, this is not like the positioning adjustment.  If you are way behind, the odds against a comeback are long and you need to up the number of possessions.  Likewise, if you are way in front, it is time to slow the game down and limit the remaining number of possessions (if you have ever blown a 20 point lead in the second half, you'll explore this option...).  In any case, your end of game settings take precedence (maybe that's what you meant zhawks?).

It means that I don't trust the HD engine to make the correct choice... lol

Yes I get where you are coming from and that makes sense I just prefer to set my game plan how I want (yes both regular and end game) and trust my own brain much more than the HD engine.