[Feature] Prometheus alerts installed by operator chart

Following up the [comment](https://github.com/cloudnative-pg/charts/issues/192#issuecomment-2401657173) from @itay-grudev, I now create a new issue, as I didn't found any other issue describing my feature request.

Automated alerts for cluster issues would be extremely useful to improve the safety of a CNPG cluster.

Currently I'm using the following workaround:

```sh
curl https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/main/docs/src/samples/monitoring/prometheusrule.yaml | kubectl -n NAMESPACE apply -f -
```

Also related to https://github.com/cloudnative-pg/charts/issues/100

Side note: This is a duplicate of #452. Since there was never an answer and the stale bot just closed it, I'm now opening a new issue. I'm happy to get an answer of a non bot (even if it is the same, i. e. "Not planned"). Thanks in advance :)