Welsh Fire turned the form table on its head to defeat The Hundred table-toppers Southern Brave in a last-ball thriller in Cardiff.