How bad or good is it really with the latest patch?
Not compared with any previous MOO game. On it's own, patched. Is the game effectively broken? Unplayable? So full with bugs that you end up swearing at it?
Yes it is different in GUI and in (in a way) scope, but I don't care about that.
How good or bad is it as a game on its own?

Well, I have no nostalgic memories of MoO1+2, nor bitter memories about MoO3, so I'll let you know after I've played for a week or so.

It is a good game, but you have to free your mind from the thought that YOU are the one who should determine all the details. You CAN, but this will get a very hard task very soon.
MOO3 is more a game of global (or, well, universal) political and economic strategy -- you think about long-term goals, let the AI carry it out for you, and make some corrections, if necessary.
I felt like a politician who determines the overall direction -- the real work is done by your staff, in this case the AI.
Of course, then it's bad that many players consider the AI assistents not very clever. However, I think the AI does a good job, but perhaps in a different way than some players would do.

Never having played the originals, i spent an afternoon playing MOO3 and just didnt get it. I didnt feel any decision i made really mattered or the game had much of an atmosphere. That being said i still wanna try it again (im a sucker) with the mods.

MOO3 was the first in the series I gave a serious try. I never got it fully. Opening up menu after menu and choosing things that I was not sure what it was actually going to do in the game.
Moving ships around, and knowing what to build and how to do it was a bit difficult.
I even asked on the original forums if someone could make a turn by turn manual on how to play the game. I cannot remember, but a person did to a degree but I still had too many unanswered questions that after about a month or so of trying off and on, I finally gave up.
One of the biggest things for me that really was a disappointment form the very start was the fuzzy in game text. There is a mod for it (or there was), but it really set the bar low when the game is more like a grouping of spreadsheets that do something, and they all have fuzzy, hard to read text. It was a game that to a degree was an adventure game in eye strain.
If it ever is on sale for like $4.49 or so I will pick it up, as I would like to say I figured it out, but there is no way this game is worth the $9.99 asking price; mods or not.
If you get it be prepared to load a few mods in order to play it. At least that is how it was years ago. I think I had about 6-8 different mods running to make the game playable, but I never did figure out how to play it!

It is complex, it is deep, it is very satisfying, as soon as you get it.
But fanmods are a must.

I'll echo what he said. There is much much less micromanagement, but much more emphasis on macromanagement (is that even a word?). Determining how advisors build your economy, setting tax rates, setting research sliders, and that sort of thing is what the game is all about; basically you put in charge of making all the overarching policy decisions. After I got my build plans set up (which is a little bit of work, but it is worth it), the only micromanagement I ever did is keeping track of my own colonization and designing ships.
The learning curve is actually a learning ]i]cliff[/i], but after 3 or 4 games i got the hang of it. Most of the trouble was getting my brain around the totally different approach. It is much more hands-off than other TBS titles (such as MoO, MoO2, MoM, the Civilzation series, etc), which is one of the big reasons for much of the derision in the reviews.
Yep. Install the game, install the Tropical patch, and give it a chance. It really is a fun game once one gets used to it. In many, many ways I actually like it BETTER that the other MoO games. The real-time tactical combat is really fun for me, for instance. Also, I actually like the strategic significance of the Warp Lanes (is that what they are called, it has been a while?) between systems rather than a wide open galaxy.

After having tried a few games, I agree with the above. It's actually a pretty fun game, if you enjoy the genre and grasp the different approach. Most certainly by no means a 1-2 star game. Most certainly not the worst game on GOG. Personally I'd give it four stars, but I can imagine it being 3 for someone less enthused by the macro-management approach.
Still, it should have been $6, considering the unfortunate reaction to the game.

Objectively, the game can still be a headache fully patched (as the GOG version is). As others have said, the fan mods are required for most people to enjoy the game. They generally improve the AI, rebalance most aspects of the game, fix bugs and hassles like leaders who die a turn after they have joined you... the list goes on.
However, even with the fan mods (I suggest Tropical 1.3.1 btw) your early games will be a struggle. Very little of the game is explained up-front, and there is a noticeable lack of tool-tips. The new turn reports by comparison can be a little cluttered, and I'm not entirely happy with the filtering options. Each individual technology will cause 4+ different messages over the course of its research, marking milestones and events. Likewise you might initially be confused as to the difference between messages about colony ships landing, colonies being established, and planets being colonized.
You will likely find your plans turned upside down because of issues that, while they make sense in hind-sight, will not seem exist before they come up. This will turn off players without a lot of patience or who don't want to invest hours merely learning how the game works.
Additionally, the UI was poor to begin with, and it has not aged well. While mods have worked towards fixing this, there is only so much they can do. A new player will likely have a heard time looking for the options/information they seek, and will most certainly wonder what a lot of the other information means.
All this combines to make this game difficult to get into, and goes a long way to explain the poor reviews it has received.
...
That said, a lot of things can be intuited(?) from the meager hints the game will give you, and for anything else, there are no shortage of forums/sites out there dedicated to Moo3 which you can mine for tons of helpful advice and answers. Once you get past the initial learning-curve and the rough presentation, the game is quite enjoyable. Mechanically, the game is very deep and rich. The game also, as stated, tries to take as much of the micromanagement as it can off your hands. I find it does a decent job of this (much improves with Tropical) which allows games to go longer without the bogging down that others in this genre will experience.
I've tried to be as objective as I can with this, but I'm not completely unbiased...