i agree with the OP that the alliance system is fantastically broken, and whereas i usually tend to pursue alliances in other games, in civ 7 i stay as far from them as i can.
if they split alliances into defensive pacts (you only go to war if your ally is attacked) and full alliances (you join in wars your ally starts), that would be fine. but having only the latter as an option means the other civ is essentially making your foreign policy decisions for you. whoever has the itchiest trigger finger calls all the shots.
i agree with sly1foxy that the AIs will ALWAYS declare war if their ally does. i have never seen an exception to this. i've also had the OP's experience of thinking i had a great relationship with so-and-so only to have them attack me because they also had a good relationship with someone who hated me. as others have said, it would make sense if the AI would at least cancel an alliance if they're asked to go to war with someone they're willing to enter an alliance with, but this never happens. in real life, nations would not be so quick to torpedo centuries of diplomacy.
what all this tends to result in is blocks of civs that are just endlessly going to war with each other, because it only takes one of them to pull the trigger for the entire alliance network to be activated. i've seen this happen across ages. in my last game, both continents were locked in an endless cycle of continent-wide wars, excepting only me because i kept refusing alliances.
as for the penalty, if you turn down an alliance it's -30, and if you cancel an alliance it's -30, so there's no graceful way to avoid making someone who likes you dislike you.
all this makes sense only if you're trying to force everyone into eternal wars. it also scuttles any nuanced diplomacy because the AIs will tend to form alliances and this will mean they either love you or hate you depending on whether you join their wars or not.