Feature request / discussion: consolidate cache in DuckDB core

Hi team, nowadays we have cache implementation scattered in both [httpfs extension](https://github.com/duckdb/duckdb-httpfs/blob/2a6481d0bfc2cbf1a1f15d8076fe09b9d326d8d4/src/include/http_state.hpp#L25-L30) and [core](https://github.com/duckdb/duckdb/tree/main/src/include/duckdb/storage/external_file_cache), which
- Could lead to double caching
- Maintenance overhead, for example, there's deadlock bug in httpfs impl: https://github.com/duckdb/duckdb-httpfs/pull/335

I'm wondering if it's possible to make httpfs stateless as possible, which only responsible for IO communication with storage backend, and move all high-level features (like data caching) into core? Thank you!

Start by comparing the cache implementation in http_state.hpp with DuckDB core's src/include/duckdb/storage/external_file_cache, then read the linked httpfs pull request about the deadlock. The issue would be done only when the project agrees on and implements a clear consolidation design, including the intended httpfs/core responsibilities.

Written by the indexing model from the issue text.