It can’t be both. Either the CCC is correct (one unforgiven mortal sin and it’s hell time) or common sense is correct (God is merciful and good God fearing people are judged by the entirety of their actions, not just their last one). It just seems counter-intuitive for God to say “You were good all your life. Too bad you died before you had a chance to visit the confessional.”
I think you are thinking of this as a points game. Being good scores good points and being bad scores bad points or somesuch.
Think of it rather as a light switch. When it’s on, you are in a state of friendships with God and would freely choose Him as He has chosen you upon death. When it’s off, you are in a state opposed to God, of preferring yourself to Him and even if given the option would not join with God in heaven.
Baptism switches it on. Confession and perfect contrition switch it on.
Mortal sin switches it off. This is what defines mortal sin. This is what “grave matter, full knowledge, and full consent of will”
mean. It means that knowing very well what you were doing and knowing that it was directly opposed to what God wants, you still choose of your own free will to act as though your own desires are the more important than Love, than Truth, than God. You reach out and switch it off. You consciously choose to reject God for yourself. You choose, in fact, Hell. Now, you may not have chosen Hell in the sense of saying “I want to be miserable for the rest of eternity,” but you have rejected and discarded God’s friendship saying that you prefer your own path to God’s and do not want to be united with Him. Separation from God is Hell.
This is what mortal sin is. A person who dies in such a situation would not go to heaven if given the choice, because going to heaven means joining in perfect union with God and this person exists in a state that does not desire that union.
Mortal sin leads to Hell. One mortal sin. This is the definition of Mortal Sin. That is why, in fact, it is called “Mortal” and not just “slightly more annoying than venial”. It is deadly.
We cannot turn the switch back on ourselves. But God can. And He offers to do so. And we
ordinarily accept this offer through confession or perfect contrition. But God is God, and if He knows that we would accept this offer through ordinary means if we could but we are prevented from doing so, then He can allow us to accept this offer through extra-ordinary means.
But they are not ordinary. They cannot be counted on. If they could be counted on, then they’d be ordinary.
The take away is to avoid mortal sin like you would avoid death because it is in fact death. When we sin anyway, use the ordinary means to flip the switch back on and go back to avoiding sin like death. And hope and pray that should you die with the switch off but wanting it back on, that God will extend the extra ordinary ways.
But while we can hope and pray, we cannot demand. God is God, and by very definition one mortal sin is enough that we deserve Hell. Anything else is God’s mercy and should be cherished as a gift, not demanded or expected as a right.