The UNITAI values do several things.
First, they are used by the AI when deciding which units to build depending on the AI's current war status (peace, limited war, attacking, defending, etc). The AI will, for instance, prefer a CITY_DEFENSE unit over an ATTACK_CITY (assuming they have similar combat strengths) if it is building the unit in a city that has a nearby threat. Since many units have multiple UNIT_AI values defined it can get complicated.
Second, they are used to gauge the threat in an area. The AI will evaluate enemy units and weigh their threat based on their UNITAI and combat strength.
Third, they are used when the AI assembles stacks of units. Depending on the 'head unit' UNITAI value it will assign other units to the stack as needed. This results in things like CITY_DEFENSE units escorting settlers but not showing up in assault stacks. Again, since many units have multiple UNITAI values you will get some variety.
Finally, they are used to determine the 'mission' of a stack. This also depends on the current state of war and whether the AI is attacking or defending.
COUNTER units, for example, may stay and defend a city or they may join attack stacks depending on whether the AI is attack or defending and whether or not they perceive a threat at the city where the COUNTER unit currently is. There is a bit of redundancy in this part which makes it hard to say exactly what the AI will do with a specific unit, compunded by the fact that many units have multiple UNITAI values.
RESERVE units can defend cities, escort settlers & workers, defend forts, defend future city sites, and protect other units (including those in attack stacks).
CITY_COUNTER is very similar to CITY_DEFENSE with different threshold for determining whether or not it will leave the city to attack the enemy.
It's hard to come up with a lit of what they each do exactly. Think of them a guidelines, not specific instructions or limitations and then their names should make a bit more sense. The exception here is with NOT_UNITAI values, units with those will not be used by the AI for those functions. So a unit with a NOT_UNIT_AI setting for UNITAI_ATTACK_CITY will never be used by the AI in a city attack stack but it may be used in an attack or pillage stack that makes an impromptu decsion to attack a city.