Migrate Cassandra driver from legacy gocql v1.7.0 to apache/cassandra-gocql-driver/v2

Is your feature request related to a problem?

The server currently depends on github.com/gocql/gocql v1.7.0 (go.mod) which supported Go 1.22/1.23. Following gocql's donation to the ASF, v1.7.0 was the final release published under the old module path — it was a transitional release with
the package name unchanged. All ongoing development, bug fixes, and any future security patches ship exclusively undergithub.com/apache/cassandra-gocql-driver/v2.

• Temporal's build has kept moving: the Go SDK already requires Go 1.24+, and the server toolchain tracks similarly. So Temporal is compiling and running gocql v1.7.0 under Go toolchains the driver has never been tested against and never will be.
• There are no known CVEs against v1.7.0 today, but the v1 line is effectively unmaintained: if a vulnerability or driver bug is found, no fix will be published on the current import path.

Describe the solution you'd like

Adopt github.com/apache/cassandra-gocql-driver/v2.

Scope should be contained: the driver is only imported directly by the wrapper package at common/persistence/nosql/nosqlplugin/cassandra/gocql; the persistence stores go through the wrapper's Session/Query/Batch interfaces. The v2 upgrade guide documents the breaking changes, notably:

• Query/Batch objects are immutable during execution (no more pooling semantics) — may simplify or affect the wrapper's reuse patterns
• Compressor packages moved into the main module
• Some deprecated APIs removed (e.g. TimeoutLimit)
• Protocol version auto-negotiation is now the recommended default

Additional context

Upgrade guide:
https://github.com/apache/cassandra-gocql-driver/blob/trunk/UPGRADE_GUIDE.md

Start with go.mod and the imports in common/persistence/nosql/nosqlplugin/cassandra/gocql, then read the linked v2 UPGRADE_GUIDE.md. Check the wrapper's query, batch, compressor, and timeout usage against the v2 API. Done means the dependency and import path are migrated, breaking changes are handled, and the Cassandra persistence tests and build pass.