[FEA] Refactor logic detecting compression type when AUTO behavior is enabled

**Is your feature request related to a problem? Please describe.**
This issue outlines recommendations for follow-up work from @bdice and @vuule to improve AUTO behavior.

**Describe the solution you'd like**
1. Refactor the detection logic for each compression type into helper functions to simplify the fallthrough logic in the switch statement. Consider alternative approaches using std::optional (returning “maybe the detected sizes, or nullopt”) or using a std::variant holding one of the possible compression metadata values (offering more choices for what metadata might be returned by each compression type).
2. Optimize all our code paths for exiting early based on the distribution of most common compression types. Both the JSON and CSV reader detect compression for file types based on the file extension which can be error-prone. Moreover, the compression for source types like host and device buffers need to be fixed before passing on the buffers to the reader.