Add SignTransaction function to ScriptPubKeyMan and LegacyScriptPubKeyMan

Summary:
Backport of Core PR18115 part [2/9] : https://github.com/bitcoin/bitcoin/pull/18115/commits/d999dd588cab0ff479bc7bee8c9fc33880265ec6

Depends on D8094

Test Plan:

ninja all check-all

Reviewers: #bitcoin_abc, PiRK

Reviewed By: PiRK

Differential Revision: https://reviews.bitcoinabc.org/D8095