On the Franchise Management Console if I choose to have the AI Automatically manage the offense and pitching staff of the lower minor leagues (for instance Low A and Rookie League) will the AI completely ruin things (i.e. not play the best prospects, overuse players, etc)? Or is it a good way to allow me to focus on the major league roster and high minor leagues without spending half my time balancing rookie league pitching staffs?

They will do a decent job. I'd still check once a week or so just to keep an eye on things.

They will do alright, assuming you have an appropriate roster... (SPs with enough stamina, plenty of RPs, bench players to rest starters in blow-outs, etc)
Injuries are the thing that can mess that up. I let the AI handle injuries at rookie level, but NEVER above that. The AI may auto-promote guys to fill positions of need when there is an AI DL stint. You probably don't want that.

What outlaw said. I let them handle everything except injuries.

Yep, remember that the AI always does what's best for the team. In my case, I usually have younger minor league players (24 and under, still with hope to develop) starting, and veteran minor leaguers as backups. This often means the backups have higher Overall ratings than the younger players. The AI would prefer the higher Overalls as starters instead. So if one of my younger guys gets shuffled out due to rest or injury, the AI might not put him back in when he's ready to go again.

I always let the AI handle my minor leagues. Otherwise, it's too much to handle if I have 4 or more teams at once.