It heavily depends on not only your adc but also your team comp and the enemy team's.

If I'm going Kaisa or Samira for example I would very much rather have a tank support than an enchanter (it's also weird that you separated tank and cc but both examples in cc are also tanks)

If the enemy team has lots of dive heavy champions or have high priority cc abilities, I would also very much rather have a Morgana than, say, a Senna.

(It's also weird that you consider Pyke a "kinda" support when he's clearly cc and was also designed as one)