How did Dorne went from being the only Realm capable of withstanding the might of the Targaryens and keep independent for over a century to the second weakest region in Westeros?

Because they withstood the might of the Targaryens. That’s not something you do without cost:

“Queen Rhaenys led the first assault on Dorne, moving swiftly to seize Dornish seats as she approached Sunspear and burning the Planky Town on Meraxes…

The war against the Dornish entered a different phase after the release of Orys One-hand and the other handless lords, for King Aegon was by that time intent on revenge. The Targaryens unleashed their dragons, burning the defiant castles again and again…So again the Targaryens turned to their dragons, unleashing their fury upon Starfall and Skyreach and Hellholt.

…The two years that followed were later called the years of the Dragon’s Wroth. Grief-stricken at the death of their beloved sister, King Aegon and Queen Visenya set ablaze every castle, keep, and holdfast in Dorne at least once…Dorne was a blighted, burning ruin by this time, and still the Dornish hid and fought from the shadows, refusing to surrender. Even the smallfolk refused to yield, and the toll in lives was uncountable

…Lord Tyrell, whom Daeron had left in charge of Dorne, valiantly attempted to quell the fires of rebellion, traveling from castle to castle with each turn of the moon—punishing any supporters of the rebels with the noose, burning down the villages that harbored the outlaws, and so on…”

As I’ve said before, I think it’s quite possible that Dorne once had 50,000 men in its armies, but can today only muster 25,000.