[FEA] Support compression="infer" in to_csv

**Is your feature request related to a problem? Please describe.**

`cudf.DataFrame.to_csv` gained ZSTD support in #21518, but the codec has to be named explicitly. `df.to_csv("out.csv.zst")` silently writes uncompressed CSV bytes under a `.zst` name, whereas pandas defaults `to_csv(compression="infer")` and compresses based on the extension. The read direction already infers: `read_csv` defaults to `compression="infer"`, which maps to `CompressionType.AUTO` and is resolved from the file extension by `infer_compression_type` in `cpp/src/io/functions.cpp`. So the two directions are asymmetric today.

**Describe the solution you'd like**

Accept `compression="infer"` in `to_csv` and resolve it from the output path's extension, matching what `read_csv` accepts and what pandas does. Only codecs the writer supports would be inferable (currently `.zst`/`.zstd`); an extension whose codec the writer does not support should raise rather than silently write plain bytes. Changing the default to `"infer"` for pandas parity is a separate, breaking decision worth discussing alongside this.

**Describe alternatives you've considered**

Doing the inference in libcudf instead, by allowing `compression_type::AUTO` in `csv_writer_options` and resolving it from `sink_info`. This is less appealing: `sink_info` is not always a filepath, and the existing `infer_compression_type` is keyed on `source_info`. Keeping inference in the Python layer, where the path is already known, is simpler.

The status quo is also an option: `to_csv` documents that the codec is not inferred and that `'infer'` is unsupported, so the behavior is at least not surprising for anyone who reads the docstring.

**Additional context**

Raised in review of #21518: https://github.com/NVIDIA/cudf/pull/21518#discussion_r3866001947

Start with the Python cuDF implementation of DataFrame.to_csv and trace how compression values reach the writer; compare that path with infer_compression_type in cpp/src/io/functions.cpp and the existing read_csv handling. Done means compression="infer" resolves supported .zst/.zstd output extensions, rejects unsupported inferred codecs, and preserves explicit compression behavior.

Written by the indexing model from the issue text.