Can this ability be activated multiple times per turn?
Yes, as long as you pay 1 mana for each time you wish to activate it.
If I pay to gain water affinity, does this count as a water site this turn?
No, activating this ability means the affinity is granted directly to you for the turn, not applied to the site.
