I use Gimp as my primary editor. It is powerful and can do a lot. Also even if it may sound unexpected, I like the interface of some of its tools (e.g. I like its curves tool).
What Gimp can do
What I am trying to do: Ideally I would like to manipulate pictures to enhance them,
Levels, curves, brightness, contrast? — yes, Gimp can do it. Sharpen? — check (see Unsharp mask and Refocus, edge only sharpening is also available). Denoise? — check (not the best in the world, but Wavelet denoise is very usable). Remove red eyes? — check. Fix colors? — check (not the strongest side of Gimp, though, it's better to have a RAW file around).
Yes, Gimp can do it. Also quality resizing, rotation, perspective correction etc.
and also have tools to merge new backgrounds
Yes, there are several ways to remove or replace background. There is also a special Foreground Select tool.
and tools to transform a pictures completely, e.g. transforming the picture so it looks like a painting.
Yes, sure. I never use them, but there are many filters like this and tutorials how to do it manually. See how to reate an oil painting from a photo in Gimp and Sketch effect.
What Gimp cannot do yet
Current version of Gimp (2.6) doesn't support color spaces different from RGB (e.g. no CMYK or Lab images). But you can decompose the image into separate compontents, process them and re-compose them back. The future versions of Gimp will have a much more flexible color model: Plates. It will allow for arbitrary color representations.
Current version of Gimp doesn't support color depth beyond 8 bit per channel. In practice this means that if you try to pull shadows up with the curves tool (and the curves tool itself is very good in Gimp), you may notice color banding and will see a spiky histogram. It's unlikely to be implemented before Gimp 3.0, but the work has started. I suppose contributions and donations are welcome.