In any case, how bad is spirit prison supposed to be?
I would suppose that depends on what sort of life you lived before you died. I don't see any reason why it needs to be unpleasant, but there will be the feeling that you can't move on right away.
How many will undertake this process?
I can't give numbers obviously, that depends on free will.
How glorious is the Celestial Kingdom then?
Well, beyond the understanding of those whose understanding is beyond out understanding I suppose . . .
What happens to them? Hell?
Outer Darkness, ie being completely out of the presence of God.
And, what does this sin entail exactly?
Having a perfect knowledge of God (not just thinking that God's existence is logical, or even having spiritual experiences of God, but basically meeting God) and then rejecting Him.