Well, for water mods, I use W.A.T.E.R. and the add-on for Dragonborn.Wet and Cold, Enhanced Lights and FX, Climates of Tamriel, Ruins Clutter improved, Skyrim HD - 2K Textures, Realistic Lighting Overhaul, and the Static Mesh Improvement Mod (SIMM) are all good as well.

I also highly recommend looking into the ENB stuff as well, if you have a powerful machine. You can make Skyrim look pretty damn amazing. I *think* this is the ENB mod I use: http://skyrim.nexusmods.com/mods/23765

Hope that helps!