If I remember correctly, another game with a similar music system is Deus Ex from 2000. It is pretty approachable. If you own a copy, open any of the s3m music files in your favorite mod tracker editor. Each song file contains multiple versions of song sequences, depending on the mood (idle, battle, ...).

Games today feature dynamic music with loops and transitions and individual stems that can be remixed at runtime. One prominent example (to me, at least) is "Take Control" playing over the Ashtray Maze in Control. This sounds like an absolutely seamless prog metal song while playing, but it is actually highly reactive to the gameplay - the rapid-fire sequence of battle arenas and fast paced corridors. The player stays in absolute control of the pacing the whole time.

X-Wing just had great music. Even the original stuff was great. The music for the training run was perfect.

Modern games have similar reactive music systems but I've never heard one I felt was better than X-Wing's. They got it right on the first try.

CD and other formats create trade-offs vs MIDI event sequences - it's a simple playback method offering a lot of fidelity but in exchange, you're tied to having either "one track at a time and the CD spins up in between" (Redbook CD), cueing uncompressed sampled tracks(feasible but memory intensive) or cueing one or more lossy-compressed streams(which added performance or hardware-specific considerations at the time, and in many formats, also limits your ability to seek to a particular point during playback or do fine-grained alterations with DSP). So as a dynamic music system it tends to lend itself to brief "stings" like the Half-Life 1 soundtrack, or simple explore/combat loops that crossfade or overlay on each other. Tempo and key changes have been off the table, at least up until recently(and even then, it really impacts sound quality). DJ software offers the best examples of what can be done when combining prerecorded material live and there are some characteristic things about how DJs perform transitions and mashups which are musically compelling but won't work everywhere for all material.

MIDI isn't really that much better, though - it's a compatibility-centric protocol, so it doesn't get at the heart of the issue with dynamic audio of "how do I coordinate this". All it is responsible for is an abstract "channel, patch number, event" system, leaving the details involved in coordinating multiple MIDI sequences and triggering appropriate sounds to be worked out in implementation. An implementation that does everything a DAW does with MIDI sequences has to also implement all the DSP effects and configuration surfaces, which is out of scope for most projects, although FMOD does enable something close to that.

I think the best approach for exploring dynamic and interactive right now is really to make use of systems that allow for live coding - Pure Data, Supercollider, etc. These untangle the principal assumptions of "either audio tracks or event sequences" and allow choice, making it more straightforward to coordinate everything centrally, do some synthesis or processing, some sequencing, adopt novel methods of notation. The downside is that these are big runtimes with a lot of deployment footprint, so they aren't something that people just drop into game engines.