[v2 Bug] dbt deps --add-package doesn't support --source

### Is this a new bug in dbt v2.x compared to the latest version of dbt 1.x?

- [x] I believe this is a new bug in dbt v2.x
- [x] I have searched the existing issues and could not find a duplicate

### Current Behavior

In core v2, `dbt deps --add-package` doesn't support the `--source` flag. Instead it operates on the assumption that it can determine the source based on parsing the package name. Unfortunately this creates another bug where Hub package names can be incorrectly parsed as local packages, which is why it was a good idea to have a `--source` flag in the first place.

Current error:
```
>dbt deps --add-package brooklyn_data/dbt-artifacts
dbt-fusion 2.0.0-preview.202
Adding brooklyn_data/dbt-artifacts
Failed brooklyn_data/dbt-artifacts

========================================= Errors and Warnings ==========================================
[error] [InvalidConfig (dbt1005)]: Local package path does not exist: brooklyn_data/dbt-artifacts
```

### Expected Behavior

1. Deps should have the `--source` flag that defaults to Hub
2. Deps should return an error if a Hub package is passed in without a version

Error in core v1.11 (although we should use a nice error for a missing arg):
```
>dbt-core deps --add-package brooklyn_data/dbt-artifacts
01:33:49 Running with dbt=1.11.2
01:33:50 Encountered an error:
Version is required in --add-package when a package when source is hub
01:33:50 Traceback (most recent call last):
File "/Users/chaya/.cache/uv/archive-v0/B3Rg03Q2pjNb-HfLAa10v/lib/python3.11/site-packages/dbt/cli/requires.py", line 182, in wrapper
result, success = func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/Users/chaya/.cache/uv/archive-v0/B3Rg03Q2pjNb-HfLAa10v/lib/python3.11/site-packages/dbt/cli/requires.py", line 128, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/Users/chaya/.cache/uv/archive-v0/B3Rg03Q2pjNb-HfLAa10v/lib/python3.11/site-packages/dbt/cli/requires.py", line 258, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/Users/chaya/.cache/uv/archive-v0/B3Rg03Q2pjNb-HfLAa10v/lib/python3.11/site-packages/dbt/cli/requires.py", line 321, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/Users/chaya/.cache/uv/archive-v0/B3Rg03Q2pjNb-HfLAa10v/lib/python3.11/site-packages/dbt/cli/main.py", line 456, in deps
raise BadOptionUsage(
click.exceptions.BadOptionUsage: Version is required in --add-package when a package when source is hub
```

### Steps To Reproduce

1. run `dbt deps --add-package brooklyn_data/dbt-artifacts` - note that it's interpreted as a local package
2. run `dbt deps --add-package brooklyn_data/dbt-artifacts --source local` - error that source is not supported

### Relevant log output

```shell

```

### Environment

```markdown
- OS:
- CPU: (x86 or ARM)
- dbt distribution and version: (`dbt --version`)
```

### Which database adapter are you using?

_No response_

### Is this a discrepancy vs. dbt 1.x?

- [x] Yes — this works in dbt 1.x but not in dbt v2.x

### Additional Context

_No response_