It seems to me that using sex as a curse is basically an experience of humiliation toward the person the word is directed at. Sex is seen as something crude and something that evokes negative connotations, similar to other curses that mention feces or bodily secretions or unpleasant animals, where when a person calls someone else by those terms the intention is to demean him.

You still didn’t answer why sometimes people use it as praise.

Sometimes there’s also an element of intensity in it. Crudeness is violent, so sometimes people say it as an expression of admiration.

There is a psychoanalytic interpretation that sees this as the result of hidden envy, so the person doesn’t say “well done” but uses “humor” to hide the envy or hatred. As Freud said about the phrase “I feel like eating this child up” as a violent expression coming from the unconscious part that wants to attack the child. But most likely this is individual, and you can’t say it in a general way; each person has his own psychological mechanism.