Thats really an loaded question!
The type of mod depends on your prefered style of game play and system specs of course.
The mods which appear to be the most popular in no particular order are RFB (Real Fleet Boat) and TMO (Trigger Maru Overhaled). A lot of people couple these with RSRDC (Run silent, Run Deep campaign). RFB & TMO are supermods which inclue as far as I am aware enviromental and graphic upgrades, skins, different commands a totally different way the game works and looks. RSRDC changes the campaign layers of the game so it becomes totally different. There is so much more that these offer try them to see.
Really the best way to go is to read the threads on the mods and take it from there.
Others that are popular are Fall of the Rising Sun, operation monsoon and loads of others. Check out the download section to see them all.
There has been so much hard work gone into making these mods, I am amazed at the skill and dedication of the people who work on them, so sorry for any that I haven't mentioned
but you could go on and on.