[FlightRPC] Async API overhaul (tracking issue)

### Describe the enhancement requested

Flight is mostly based around synchronous APIs. This is convenient, but doesn't scale as well. There have been various issues filed and ML questions asked over the years about asynchronous APIs. We should consider adding a set of asynchronous APIs (and hopefully to reduce maintenance, rewriting the synchronous APIs as a layer on top of the asynchronous ones). This will also fit better with recommended usage of gRPC (in Java) and UCX (in C++).

Also, this would be a chance to tackle some API improvements:

- [ ] apache/arrow#34251 - do not mix error handling and RPC status codes
- [ ] Standardizing error code mappings between implementations and related APIs
- [ ] Ensuring cancellation and timeouts work everywhere
- [ ] Ensuring timeouts can work on a per-message basis for long streaming operations
- [ ] Ensuring Ctrl+C works reliably
- [ ] Ensuring backpressure and queueing works properly
- [ ] Integration with frameworks and language features like asyncio/anyio (Python), virtual threads (Java), coroutines (C++), etc. and concepts like structured concurrency
- [ ] Ensuring APIs are more uniform to reduce code duplication/special code paths (timeouts, cancellation, etc. are often handled differently for different APIs)
- [ ] Ensuring we properly flag "the write failed, but the RPC did not necessarily error"
- [ ] Properly expose headers/trailers in both directions

Related issues:

- [ ] apache/arrow-java#212
- [ ] apache/arrow#34221
- [ ] apache/arrow-java#362
- [x] apache/arrow#16604

### Implementation

- [x] apache/arrow#36512
- [x] apache/arrow#37093
- [ ] apache/arrow#37343
- [ ] apache/arrow#37344
- TBD...

### Component(s)

FlightRPC