(aws-kinesisfirehose-alpha): RedshiftDestination support

### Describe the feature

[RedshiftDestinationConfigurationProperty](https://docs.aws.amazon.com/cdk/api/v2/docs/aws-cdk-lib.aws_kinesisfirehose.CfnDeliveryStream.RedshiftDestinationConfigurationProperty.html#secretsmanagerconfiguration) (and others) takes a `secretsManagerConfiguration` object that contains the ARN of the secret.

It would be a better user experience if we could pass a Secret construct to it instead.

### Use Case

See above. I'm from AWS Secrets Manager and have had customers reach out to me about the UX of this CDK construct.

### Proposed Solution

Write L2 constructs for these resources.

### Other Information

_No response_

### Acknowledgements

- [ ] I may be able to implement this feature request
- [ ] This feature might incur a breaking change

### CDK version used

2.147.1

### Environment details (OS name and version, etc.)

environment-independent.

Start by reading the linked RedshiftDestinationConfigurationProperty documentation and the AWS CDK context for aws-kinesisfirehose-alpha. Identify which destination properties need L2 support and define how a Secret construct should replace the secret ARN; done means the requested L2 constructs support this user experience with clear validation and coverage.

Written by the indexing model from the issue text.