Document what the package revision activation policy is actually for

### What's Missing?

While working on the package manager @n3wscott noticed you can't actually activate an old revision. The top-level package controller forces all but the current revision to be inactive. The package's revision activation policy only controls whether the package controller will enable the current revision automatically.

I found this confusing at first and had to think for a while to remember why it works this way. There's a clue in the description of the `spec.revisionActivationPolicy` field:

> RevisionActivationPolicy specifies how the package controller should update from one revision to the next. Options are Automatic or Manual. Default is Automatic.

Notice it only mentions upgrading to new versions - not reverting to older ones.

If I recall correctly, the reason package revisions exist all is to guard against package OCI tags changing.

The revision activation policy effectively controls what'll happen if the package's OCI ref starts pointing to a new digest. The OCI ref could point to a new digest for two reasons:

1. You explicitly updated the package, e.g. to update from `xpkg.crossplane.io/crossplane-contrib/provider-nop:v1.0.0` to `v2.0.0`
2. Someone updates the `xpkg.crossplane.io/crossplane-contrib/provider-nop:v1.0.0` tag to point to a different digest in the OCI registry

Using the manual activation policy is pretty pointless in the first case. It just makes updating the package to use a new OCI tag a two-step process.

In the second case it defends against unexpected changes to package tags. If the tag started pointing to a new digest, Crossplane would create a new revision but not activate it until you explicitly told it to.

In most systems you guard against OCI tags changing by just not using them, and instead pinning to an explicit digest (i.e. sha256 hash). At the time we wrote the package manager we didn't support using OCI digests. This was because we required semantic version tags in order to solve dependencies. We did recently add digest support though (iirc at the expense of requiring manual dependency management).

I think we should update the documentation to explain why you might use a manual activation policy. Currently the documentation just states you can, and how, but not why.