[Story] Unify categorical support and implementation across engines

Currently the different components of cudf handle categorical data to different degrees and in different ways:
- cuDF classic maintains a categorical column as a column one one child that is a numerical column. The categories are stored in the dtype.
- cudf.pandas uses cuDF classic under the hood
- cudf-polars does not currently support categoricals
- Spark RAPIDS does not directly support categorical data as its own type
- libcudf supports storing data using [the Arrow dictionary type](https://arrow.apache.org/docs/format/Columnar.html#dictionary-encoded-layout), but these have seen very limited use and the set of support operations is also limited

Ideally, we would unify all of these and use a single representation. #8573 captures this for cuDF classic, specifically. However, now that we are supporting multiple front ends with zero-code-change as the goal, we need to consider what each of the corresponding host tools do.

- pandas [supports both ordered and unordered categoricals](https://pandas.pydata.org/pandas-docs/stable/user_guide/categorical.html). This feature is part of what has made it challenging to use the libcudf dictionary type in the past because that type is strictly unordered.
- [Polars supports two types of categoricals, Enum and Categorical](https://docs.pola.rs/user-guide/expressions/categorical-data-and-enums/). Under the hood, though, Polars also does not use the Arrow dictionary type in this case. Instead, Polars stores categorical data in an integer column. The categories are stored in the data type, while the column just contains the numerical values.
- Spark has no intrinsic concept of categoricals, so similar mappings to what Polars maintains internally must be maintained explicitly by the user

#8573 was written under the assumption that using libcudf's Dictionary type would lead to more performant and flexible code at the Python layer. With Polars also deviating from the Arrow type, however, it seems like we may need to go a different route anyway and define a custom categorical type based on numerical encodings that we can reuse across pandas and Polars. We should consider whether this approach would be preferable, and if so, close #8573. If we were to go this route, we would probably want to implement this categorical type using pylibcudf as an extension to pylibcudf's core and make that reusable for cudf.pandas and cudf-polars.