There are scriptures that have a contradictory view. These 2 verses from Manu Smriti proove that Sati wasn't enforced or even common during those days.
He who takes care of his deceased brother's estate and of his
widow , shall after raising up a son for his brother, give that
property even to that son. (Manu 9.146)
If the widow of a man who died without leaving issue, raises up to him
a son by a member of a family, she shall deliver to that son the whole
property which belonged to the deceased. (Manu 9.190)
Acharya Medhatithi maintains that this process is contradictory to Vedas and Mahanirvana Tantra is also against this practise.
As in the case of men, so in that of women also suicide is forbidden.
As for what Aṅgiras has said—‘they should die after their
husband’,—this also is not an obligatory act, and so it is not that it
must be done. From this it is clear that the act of killing herself
after her husband is clearly forbidden for the woman. Further, in view
of the distinct Vedic text—‘one shall not die before the span of his
life is run out’—being contradicted by the Smṛti-text of Aṅgiras, this
latter is open to bring assumed to have some other meaning.(Medhatithi
on Manu 5.155)
O Kuleshani, a wife should never be burnt with her dead husband. Every
woman is your image – you reside, concealed in the forms of all women
in this world.That woman who, in delusion ascends her husband’s
funeral pyre shall surely go to hell. (Mahanirvana Tantra 10.79-80)
The source of the title is Maa Sati, who burned herself for hearing insult of her husband in front of her. This was out of pure love for her husband, Lord Shiva.
By Sati, we basically means a chaste woman. Every shastra praises a chaste woman.
As you already give references in the question, the first two references you give in the question, i.e., from Parashar Smriti and Agni Puran, here they talked about woman who loves her husband so dearly that can't live without him and burns herself with his funeral pile. Woman like this will go to heaven.
Now, talking about one from Mahanirvana tantra, here it may be about that woman, who by desiring of heaven, burns herself with her husband's funeral pile, she goes to hell. See again the verse, there is important word written there, 'Delusion'.
This 'Delusion' here refers to greed of going to heaven. She is not doing this because of her love for her husband but maybe because of other reasons. Other reasons can be many like just by being inspired from other woman or for wanting heaven or for brainwashed by someone to do so.
Such a woman goes to hell because it is committing suicide. Suicide is always mentioned in hinduism as sin.
While in first case, it is because of pure love for her husband. In that case, it means that she completely surrendered to her husband and wants to accompany him even after his death.
Surrendering completely whether to god, your parents, husband, is mentioned in shastras as giver of moksha.
In my opinion, ten million years in parashar smriti is symbolism for moksha.
it seems English translation has so many mistakes and also the context is very different here.
First of all verse 79 itself depicts that context is when child is died and then it's saying women not to go Sati. that means both child and father died and its in that context. also verse 80 is depicting that sati is optional and one should not go sati for greed and instead one can go sati for her love for her husband.
The real meaning and context can be only decoded by Shankaracharyas. but it is for sure that Sati is in history everywhere. even Shri Krishnas wife went for Sati. it means If Rukhminiji is going for sati then for sure godess rukhmaniji is not doing it for Hell.
MahaBharata mausala parva section 7 - Rukmini, the princess of Gandhara, Saivya, Haimavati, and queen Jamvabati ascended the funeral pyre. Satyabhama and other dear wives of Krishna entered the woods, O king, resolved to set themselves to the practice of penances. They began to live on fruits and roots and pass their time in the contemplation of Hari. Going beyond the Himavat, they took up their abode in a place called Kalpa.
Like any library, Hinduism Stack Exchange shares great information, but does not offer personalized advice, and does not take the place of seeking such advice from any Acharya, Pundit, astrologer, Guru or other trustworthy Counselor.