Welsh Fire clinched a last-ball victory over Southern Brave in a thrilling match, defying odds with a standout all-round performance.