Finish release automation - version bump, changelogs, testing

### Housekeeping

- [X] I am a maintainer of dbt-common

### Short description

Sync with dbt-adapters to finish automating the release. They're already doing work to mirror the release we use with. dbt-core plus adapters (dbt-snowflake etc)

This differs from the `dbt-core` release because `dbt-common` uses hatch.

As of now https://github.com/dbt-labs/dbt-common/blob/main/.github/workflows/release.yml just releases to PyPI.

### Acceptance criteria

- changelog generation is automated with release
- version bump is automated with release
- release to github and post changelog as release notes
- clean up the changelog so that what's currently "unreleased" lines up with actual releases.

extra but not required
- cleanup branches from test releases (see https://github.com/dbt-labs/dbt-core/blob/main/.github/workflows/repository-cleanup.yml)

### Suggested Tests

This workflow should have an option to run as a test
This workflow should run nightly as a test release to ensure the release works

### Impact to Other Teams

None

### Will backports be required?

No

### Context

dbt-adapters doing something similar in https://github.com/dbt-labs/dbt-adapters/issues/4, we can largely draw from that work once its done.