Yes, this has a big impact.
The main impact is in changing buildings that give health or unhealth bonuses, either directly or through resources.
Many existing buildings give health bonuses that could be perceived as providing water (like the Quanat or whatever), but many others (hospitals, Suk doctors, etc.) currently give health. These buildings would need to be cut or amended.
We have to think out the implications from the design standpoint a little more. For example, one key source of health in vanilla is bonuses, and then buildings which amplify the benefit. Like banana -> plantation -> grocer. If we use water as health, it seems that plant bonuses should *cost* water.
I'm not sure about this.
I'd think of it this way; a food bonus resource basically represents a naturally occurring area of some crop, with its own water supply. Its perfectly possible that you could harvest the crop without needing to irrigate it more, or even to tap into their water supply for your own uses. Basically a bonus food resource represents a water supply; think about oases in deserts, plants grow there because there happens to be a natural water supply, like a spring or aquifer. So the bonus food growth at that oasis really represents the water supply, not that some random plants happen to be there; the better agriculture is possible BECAUSE of the water supply.
Or imagine a bunch of cacti growing in the desert; if you set up an operation to harvest them, you could eat them, or you could extract water out of them, or both.
So its perfectly possible that some food resources could still provide positive health (ie water) from a building, or at least do no harm, because they're using their own water source.
Definitely though you could make the highest level farm improvements require water:
So you have drip farms (0 food, +1 with fresh water, +1 with bonus resource)
Growth complex (1 food, +1 with fresh water, +1 with bonus resource)
And Mark III farm (don't know what name) that gives 2 food +1 with fresh water, +1 with bonus resource, but also gives +1 unhealth to the nearby city, because it takes some water to irrigate intensive agrictulture.
Or you could make the mark 2 farms give +1 unhealth and the mark 3 farm give +2 unhealth.
But I WOULD make city improvements that require lots of water have unhealth penalties.
This could include gardens, urban plantations, industrial plants, spice factories and refining plants, etc.
As far as the AI goes, I think it would mostly handle it.
The AI will still build windtraps whenever possible because of their tile yields, even though it didn't understand how they provided fresh water.
It would still build wells because these, like pastures/plantations, activate the bonus resource, and will give tile yields.
It will still build buildings that give health bonuses if its city is low on health.
It will still build farms where these give positive food bonuses (ie where they have fresh water).
Some of these changes would even improve the relative performance of the AI; the AI only really chooses improvements based on the tile yields of particular tiles (and I think to some extent of the needs of the nearby city that uses them). Its never really been any good at understanding how long chains of irrigation can work; in vanilla civ the AI often does badly on maps that have few sources of fresh water, because it doesn't understand how to build long chains of farms out to other cities so that farms can be built there too.
This problem goes away if drip farms etc. do not in fact spread fresh water. The AI will build wells and windtraps because of their tile bonuses, and then build farms on the tiles around them where a farm has a positive yield, and then build cottages and turbines on the other tiles.
(This will be even better if you require drip farms to require fresh water access in order to be built; so you can build them only on a fresh water resource, or adjacent to a windtrap or well.)
The one thing the AI won't understand is that it should choose to place its windtraps adjacent to bonus resources that get farms built on them, but this is probably managable.
You could also make the city windtraps give some other bonus or make sure they have a sufficiently high AI weighting so that the AI always builds them.