Let's talk about the Detective

[Kotlol]

As stated by the OP, the problem seems to be that detectives are highly sought after mindhack targets because they come with a free, highly effective gun and loads of ammo everywhere, in both lethal and non lethal varieties, and that they're the only security member who can be mind hacked. Technically the HoP can be mind hacked too but thats a different story - no one usually questions why the Detective is rummaging around Security.

So I'm going to assume the overall idea here is this: what role should the detective play on station? Should they have the ability to kill people so effectively?

If you think that's bad with the mindhacking.

TRY ANTAGONIST DETECTIVES.
Double the ammo, double the gun and more.

I do love playing as one on RP as I know how much power I have, so I am just a rough cop or I pretend to be like JUDGE DREDD (even though HoS has the right gun for it).

But... in classic. I can see that as a recipe to go home. Set all the security to arrest in a corner, grab your guns and kill people. If they undo the arrests, hide and set them back to arrest (they are slower). Shoot some more.

It's why I want to nerf so many things of the detective. Not cause the detective alone is INSANE. It's cause Det + antag = Security will not have a good time.
A whole security team would be needed to be constantly undoing the detectives damage.