Built-in ServiceMonitor for PgBouncer and StatsD

### Official Helm Chart version

1.7.0 (latest released)

### Apache Airflow version

2.3.0

### Kubernetes Version

v1.24.x

### Helm Chart configuration

_No response_

### Docker Image customisations

_No response_

### What happened

The current version of the chart as is doesn't have support for built-in `ServiceMonitor`. Is this a deliberate omission because it's considered bloat(as it isn't really required by Airflow, and also as that would mean tighter integration with Prometheus- and we don't want to outright do that for our users?).

Or can this be added as a feature/enhancement to the upstream chart? Since, Prometheus is a major CNCF project which is one of the most widely used solution for monitoring.

I am willing to submit a PR, if this seems like a valid issue :)

### What you think should happen instead

_No response_

### How to reproduce

_No response_

### Anything else

_No response_

### Are you willing to submit PR?

- [X] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)

Read the existing eight-comment discussion and review the official Helm chart's current monitoring configuration, including how PgBouncer and StatsD are exposed. Confirm the intended ServiceMonitor scope and configuration with maintainers, then add documented chart support and validate the rendered manifests.

Written by the indexing model from the issue text.