BUG: CategoricalIndex allowed reindexing duplicate sources
For consistency with normal indexes, `CategoricalIndex.reindex` should allow targets to be duplicated but not the sources. However, currently in allows the source to be duplicated but not the targets, which is exactly the wrong behaviour.

Most of the work here is fixing the tests, which in many cases explicitly check for the incorrect behaviour.

Fixes #25459 

(I can't run the full testsuite on my machine but the relevant categorical tests do seem to pass. Hoping that the GitHub CI infrastructure will pick up any other failures.)