Most of the time it’s used as a compliment. I’ve used it myself when referring to a friend or relative that has been especially nice or helpful.
But it can also be used as a “left-handed” compliment or insult and is usually said to a person who is not very bright that has done something stupid or ridiculous without realizing it. So when it’s said to those kinds of people, it is really an insult, without having to actually say that you think the person is an idiot or foolish. Most likely the person, because they are not that bright, will not realize that it’s an insult, and take it as a compliment.