Not interested in MP games so after reading game really shines as CO-OP or MP I wander if the single player experience is that bad? I enjoyed the last Sid Pirates.

So far it very much reminds me of the old Sid Meier's Pirates game without the captain dueling bit or the romancing bits. You can sail about hunting pirates, taking over towns, or just be a peaceful type trader doing quests and trade runs in your peaceful area. Although in order to expand your area you need to sail to pirate infested waters and take over the towns. I started on a large map so I have not gotten anywhere near the other factions yet, only pirates.

It do seem a bit grindy trying to make enough money to upgrade me ship though, but so far I'm having fun sailing around like I used to in Pirates Gold hunting pirates. Other than the random type quests each town will give you I do not see any storyline to follow, so SP may tend to get boring after a while, but if you don't mind having no main storyline to follow, then it's fun.

The game is as fun as the GTA2 if you not follow the story and just play free. You can do pretty much everything what you can do online (except the player interaction of course). Making new random worlds, and participate in big wars between the factions. You can trade for reinforcing NPC towns, and such on. And of course you always can start a new character.

Sadly I not played the full version yet. But I want to buy it here when it get some price discount. :D

I'm also curious as to how a single player experience with windward would play out.

I didn't enjoy too much the latest pirates game (too much repetition and didn't like the dancing mini games), but the rest was ok.

Also, is the game moddable? Could we expect someone to make single player campaigns someday?

The game use the unity engine, it is very moddable. The Steam even has a tutorial how to mod the game. Personally I enjoy the game alone, never tried it online (the alpha demo was not able to play online at all).