Persistent Queries Schemas hard delete with DROP statement

**Is your feature request related to a problem? Please describe.**
Right now, when a DROP statement is executed, the schema associated with that STREAM/TABLE (at Schema Registry) is Soft Deleted. I won't argue against, by default, a soft delete is playing safe. However i think that should be given the possibility to force an hard delete when dropping streams/tables.

I'm facing a situation when i noticed that i was reaching my 1000 schemas limit on Schema Registry. At first i didn't understood what's happening but then i noticed that it was because the DROP statement was doing soft deletes at schema registry instead an hard delete.

So, allow me to give you an example for better understanding.

Let's assume that i created a stream named `staging-bq-user-sessions`:

```sql
CREATE OR REPLACE STREAM `staging-bq-companies`
WITH (VALUE_FORMAT='AVRO', PARTITIONS=1) AS
SELECT
c._id as id,
AS_VALUE(c._id) as _id,
c.owner_id as owner_id,
c.company_licence as company_licence_id,
c.name as name,
c.status as status,
FROM `staging-sk-companies-cdc` c
LEFT OUTER JOIN `staging-licences` l ON l._id = COALESCE(cl.licence, 'null')
PARTITION BY c._id;
```

With this stream a topic named `staging-bq-companies` will be created and also an AVRO Schema with a subject name equals to `staging-bq-companies-value`.

Additionally a couple of ksql internal topics and schemas will be created because of the Join made in the query.

Topics:
- `_confluent-ksql-staging-pipelinequery_CSAS_STAGING-BQ-COMPANIES_4333-KafkaTopic_L_Right-Reduce-changelog`
- `_confluent-ksql-staging-pipelinequery_CSAS_STAGING-BQ-COMPANIES_4333-L_Join-repartition`

Schemas:
- `_confluent-ksql-staging-pipelinequery_CSAS_STAGING-BQ-COMPANIES_4333-KafkaTopic_L_Right-Reduce-changelog-value`
- `_confluent-ksql-staging-pipelinequery_CSAS_STAGING-BQ-COMPANIES_4333-L_Join-repartition-value`

From the data above, we can easily get that we have a persistent query running with id 4333.

So, speaking a little more about my use case. As we know, stream/table joins (i have a lot of them) upgrades are not supported yet. The workaround that we, at skeeled, did is drop the streams/table and create it again. What happens with schemas and subjects in this case?

1. Schema Subject `staging-bq-companies-value` is reused and it's version incremented. Good job here, this happens because the Soft Delete, we can reuse past schemas/subjects.
2. Lets assume that the new persistent query has now te id 5555. What happens is that we will mantain the two existing subjects that have id 4333, they were soft deleted so they keeping occupying one of the 1000 slots in schema registry, but now we have two more subjects with if 5555. Imagine if the have to drop and create again this stream about 5 times, we end with 8 soft deleted subjects/schemas and just two active, the one that matter.

We could end with tons of unused schemas.

**Describe the solution you'd like**
I think that schemas/subjects internally created (the ones that starts with **_confluent-ksql-**), e.g. schemas for topics that deals with Joins, should be hard deleted.

This way we won't lost control about Schema Registry limits.

**Describe alternatives you've considered**
I considered to propose to add a option to the DROP command, something like `DROP stream DELETE SCHEMA`, but this isn't viable because we only want to delete schemas/subjects that are associated with internal topics that manages joins, repartitions, etc.

**Additional context**
Had a little discussion on slack about this with @colinhicks and he agreed that maybe could be a nice enhancement and asked me to open a github issue.

If i weren't clear at some point, feel free to ask me any clarification.

No files or tests are named. Start by tracing DROP statement handling and cleanup for internally generated join and repartition topics, then inspect how their Schema Registry subjects are deleted. Done means internal subjects are hard-deleted while user-created subjects retain the existing soft-delete behavior, avoiding unused subjects consuming registry limits.

Written by the indexing model from the issue text.