Apocalypse is the best spell pack I know of. Although it makes alteration a bit OP with Ocato's recital, the rest is fairly good. Forgotten magic and Lost magic are two extremely good spell packages too. One of them, I always forget which one, has got spells that level up the more you use them, which is an interesting twist on the mechanics of vanilla skyrim.
Then there's balanced magic, which makes spell effect scale with skill level in each school of magic : damage for destruction, duration for alteration, max level for illusion, etc...
I use spell sneak v2 which adds sneaking bonus to spells ( three times the damage if sneaking, six times on a rune, and spells emit less light as you get good at sneaking)
Spellmaking in Skyrim is a very good, if a bit hard questline, with very interesting rewards : custom spells. Definitely a must have in my opinions.
And then there is spectraverse : magic of the magna-ge and dwemerverse, two small questlines with a bunch of pretty destruction spells as rewards.
I've yet to find a mod which adds those new spells to the opponents, so you feel a bit OP as a modded mage. If you find one, go for it immediately. I use improved AI : revenge of the enemies to have a more interesting challenge.