[CT-2033] Move all tests into `tests/`, and ensure pre-commit checks are turned on for tests

### 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

Caveat: I did a _quick_ search of existing issues, so it is possible there is a pre-existing issue that wasn't able to catch with my search terms.

There is a comment within `.pre-commit-config.yaml` that exists across repos, and it most commonly looks like:
```python
# TODO: remove global exclusion of tests when testing overhaul is complete
exclude: '^tests/.*'
```

So once the testing overhaul is complete, it seems like we should... remove these lines as mentioned in the TODO.

### Acceptance criteria

The following acceptance criteria applies to dbt-core + dbt-* adapters:
- [x] Remove `exclude: '^test/.*'` exclusion (and its `# TODO` comment)
- [ ] Remove the [`interop` directory](https://github.com/dbt-labs/dbt-core/tree/main/test/interop)
- [ ] Afterwards, confirm that we're still running the structured logging test somehow
- [ ] Remove customizations within the `Makefile` related to the `interop` directory code (e.g., https://github.com/dbt-labs/dbt-core/pull/5763)
- [ ] Move all remaining tests into the `tests/` directory
- [ ] Delete the (now empty) `test` directory

### Instances
- https://github.com/dbt-labs/dbt-core/blob/b2ea2b8b256e5db1da0b712dfedd7973e1e50a37/.pre-commit-config.yaml#L4-L5
- https://github.com/dbt-labs/dbt-bigquery/blob/4fc8efd88942358b618ceb5c87416b19a29f4ed4/.pre-commit-config.yaml#L3-L4
- https://github.com/dbt-labs/dbt-redshift/blob/28d22bd8abf53882c7f4ddde6d4b611a5c924a9d/.pre-commit-config.yaml#L3-L4
- https://github.com/dbt-labs/dbt-snowflake/blob/5c8410b22592a0a248d44629580c5de331196d4f/.pre-commit-config.yaml#L3-L4
- https://github.com/dbt-labs/dbt-spark/blob/55d86d09b8630b2e0a2107c696d632721fd04bd4/.pre-commit-config.yaml#L3-L4

### Describe alternatives you've considered

_No response_

### Who will this benefit?

_No response_

### Are you interested in contributing this feature?

_No response_

### Anything else?

_No response_