Vinraith: its still updated on occassion, and there is a healthy mod community. The mods are mostly balance related, but many of them also add content.
As for single-player, its possible and there are some mods out there that try to make the AI avoid some of the worse decisions it tends to make, but at least in my experience its still not that great. There are so many things the AI just won't do. Its still playable, and you can have some fights between giant armies, but the AI tends to not deal with mid and late game strategies well, as it really just likes to make large armies of dudes and send them to their death. I am not be the best person to answer that question though, as I really don't do much in single player besides test.