How do I get a fleet to actually attack enemies when they're patrolling a planet or a system?
I micro manage them to attack. Pirates don't last forever, and once you gain some control, their attacks disappear...
Also, when my ships are injured (most of the pirates seem to use rail guns, so I get lots of injured ships, even when they haven't lost shields), I tell those ships to repair at the nearest shipyard. But then, when I give the fleet a command to attack a pirate ship, all those injured ships turn around and come back again.
Giving a fleet an order gives that order to every ship. Which makes your repairers get a new order as well.
What do I do? I micromanage... I sometimes select the attackers by dragging a box around them, which gives a bunch of single ships attack order. Another solution is to move repairing ships to a separate fleet. If you base it at the colony they are defending, they should move there automatically after repairs. Just keep in mind that once they are "back" they will head for the fleet leader, which better not be in repairs still...
The key is getting a spaceport built on the colony. That way defenders can repair there, and the spaceport also is quite good at killing pirates.
Finally, how do you manage retrofitting? If I approve a suggesting from the AI, then every ship heads off to retrofit at once, leaving my empire completely undefended. But if I don't approve it, then I have to visit each one of my bases and each fleet separately to see if they can be retrofitted. (In fact, I'd much rather not retrofit a whole fleet at once, but if I have to assign each individual ship manually, it's even worse.)
Approving retrofitting is usually quite stupid. It will for instance make the explorer exploring 7 sectors away head home for repairs.
The fast way to do manual retrofit is to use multi select on the ship list. After sorting it in some useful way. For instance I often use cruisers and carriers (and capitals) in my main battle fleets. Destroyers or frigates are in smaller fleets usually in some defensive spots. All bases, destroyers, and frigates can be retrofitted by a few good selections in the ship list. Leave the cruisers, carriers, capitals out. The same for explorers and possibly construction ships.
I then worry about organizing a retrofit for my main fleets. These are the ones I don't want to retrofit at a small spaceport, and not all at once. If I got some destroyer fleet on a special mission, it is fair enough to do the above, then do the order for the fleet again.
And yes, with a large empire it could be quite some work to make sure that all are on the latest design. But does it really matter if they are not? You usually remember the important fleets...