It is never used as an insult (you cannot call someone a "foda"); however, it generally considered to be a very rude word.
In Brazil, "foda" has a much broader range of meanings, and does not carry such a negative connotation. Please refer to a brazilian definition of this word for more details on its usage.
Mandei-lhe uma ganda foda!, disse o Tiago - I fucked her brains out, said Tiago.
Estás com falta de foda. - What you need is to get laid.