There are a few parts of this PR that need re-working but are implementation details and not specific to how silent payments work. I decided to open the PR as-is so that folks reviewing the BIP have an implementation to look at and to get some suggestions on how to better implement this in the wallet. Specifically, I'm looking for feedback on how to improve the following:

Adds support for existing wallets to send to silent payment addresses
Adds support to the Bitcoin Core wallet for receiving silent payments

The following items are not covered in this PR and are intended for follow-up PRs:

Adding labels for the receiver wallet
Creating multiple outputs for the same silent payment address when sending
Full RPC coverage (only send is covered in this PR)
Light client support (vending the tweak data per block, either in an index or to serve to an indexer, such as electrum server)
Add benchmarks to validate that there are no DoS concerns for doing silent payment verification for transactions in the mempool
More unit / functional test coverage

This PR is a continuation of the work done in #24897, where most of the follow-ups I mention above have already been fully or partially implemented.
For the silent payments specification, see bitcoin/bips#1458
Notes for the reviewers
There are a few parts of this PR that need re-working but are implementation details and not specific to how silent payments work. I decided to open the PR as-is so that folks reviewing the BIP have an implementation to look at and to get some suggestions on how to better implement this in the wallet. Specifically, I'm looking for feedback on how to improve the following:

The sp() descriptor should be updated to implement the BIP and independently derive the scan key (currently it's just a hash of the spend key)
Both the spend and scan key are kept in their own object, it would be nice to have them only accessed through the descriptor. Another idea would be allow scanning even if the wallet is encrypted, so that the spend key is not accessible unless spending
Each silent payment output found is added to the wallet as a rawtr descriptor. Ideally, we have a new descriptor that takes the spend key and then stores the auxiliary data needed to construct the private key (e.g sp(spend_key, <one of hash(ecdh_shared_secret>)
Some of the methods could probably be better organized (e.g implemented on the SPKMan, when it could be a wallet method)