[CT-2140] [Feature] Parse versions with specific version standards

### Is this your first time submitting a feature request?

- [X] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [X] I have searched the existing issues, and I could not find an existing issue for this feature
- [X] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

Currently, in [semver.py](https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/semver.py) we have a single function to parse both dbt version ([PEP 440 compliant](https://peps.python.org/pep-0440/)) and the package versions ([SemVer 2.0 compliant](https://semver.org/)). It's both confusing and broken.

The only versions that are complant for both standards do not contain any pre-release or dev info:
- `1.5.2`
- `10.5.22`

We also allow strictly PEP440 compliant versions:
- `1.5.0rc1`
- `1.5.0rc1.dev1234`

As well as SemVer 2.0 compliant versions
- `1.5.0-rc1`

Some PEP440 compliant but SemVer 2.0 uncomplianty versions fail
- `1.5.0.dev1232`

And some versions that are not compliant by either standard pass
- `1.5.0-rc1b2`

The first thing that should happen is to pull apart checking bopth PEP440 compliance and SemVer 2.0 compliance in the same place. Be explicit about which is expected and audit for it specifically. This will involve looking into everywhere this logic is called to be sure the right functionality get invoked.

Instead of using our own homegrown solution here, we should use `packaging.version.Version` (same thing we're using in the [`parse-semver`](https://github.com/dbt-labs/actions/tree/main/parse-semver) action) to check the version of dbt projects against PEP440. Possibly use the [`semvver`](https://pypi.org/project/semver/) package for checking package versions against SemVer 2.0.

Note: Adapters also do some version parsing in setup.py. It's worth exploring if this solution could work for them as well to remove as much manual regex we have to maintain as possible.

### Describe alternatives you've considered

Tweaking the current regex to work for all version patterns but this is undesirable since we're already in the code.

### Who will this benefit?

Maintainers for ease of readability of what's happening as well as package maintainers and adapter maintainers as it would allow the use of full versioning instead of a restricted subset.

### Are you interested in contributing this feature?

_No response_

### Anything else?

Would allowing packages fuller versioning have an effect on the package hub?