chore: remove yargs override in root package.json once all sub-packages are updated

### Background
In Node.js >= 24, certain older versions of `yargs` (used transitively across packages in the monorepo) cause runtime failures related to ES module scope and `require` resolution. To temporarily prevent ESM-scope/require-compat crashes in Node 24+ environments during CI test runs and installations, we introduced a hook in `.pnpmfile.cjs` (via #8460) that dynamically mutates any direct or indirect dependency on `yargs` to version `18.0.0` when the Node.js version is >= 24.

### Why this was needed
- Some of our older package dependencies/tools pulled in outdated versions of `yargs` (such as `yargs@16` or `yargs@17`).
- Node.js >= 24 implements stricter constraints and changed behaviors regarding ESM resolution and `require(esm)` integration, triggering loader/scope compatibility failures with these older `yargs` releases.
- Setting a global pnpm-file override was a safe, non-intrusive stopgap to stabilize the CI pipeline on newer Node.js versions without requiring a simultaneous, repo-wide upgrade of all individual packages.

### Impact of the PNPM Workspace PR (https://github.com/googleapis/google-cloud-node/pull/8973)
In our upcoming transition to a PNPM workspace (currently in progress in PR #8973):
- The programmatic `.pnpmfile.cjs` is deleted entirely.
- Instead of using a custom JS script to mutate dependencies during installation, the monorepo leverages PNPM's native, declarative overrides feature in the root `package.json` (`"pnpm": { "overrides": { "yargs": "^17.7.2" } }`).
- This means that once the PNPM workspace PR is merged, the code reviewer's commented line in `.pnpmfile.cjs` will be automatically cleaned up.
- However, the global override mechanism itself will still exist in the root `package.json` `"pnpm.overrides"` field.

### When & How to Fix
To completely remove the global override from the root `package.json`:
1. Identify all packages/tools in the repository that still depend on versions of `yargs` older than `17.7.2` / `18.0.0`.
2. Upgrade `yargs` to a consistent, modern version (e.g. `^18.0.0` or newer) across all sub-projects and tools.
3. Remove the `"yargs"` entry from `"pnpm.overrides"` in the root `package.json`.

Start with the root package.json and inspect the repository’s package declarations for dependencies on older yargs versions. Update the affected sub-packages or tools, then remove the yargs entry from pnpm.overrides. Confirm that installation and CI test runs on Node.js 24+ complete without the compatibility failures described in the issue.

Written by the indexing model from the issue text.