I've just re-discovered Star Trek:Bridge Commander.

For a game that's seven years old, it holds up pretty well, thanks to a very strong and talented modding community.

Much of the stuff can be found here: http://bridgecommander.filefront.com/

Be sure to pay special attention to version numbers and readmes - not all mods are cross-compatible.

The two biggest ones (which do work together!) are probably the Kobayashi Maru mod and the DS9FX mod. Careful with the latter, it has some other mandatory and optional pre-requisite mods.

With these you can set up a Federation assualt on a Borg Cube, play a Romulan vs Cardassian "What If?" - loads of possibilities.