import logging
import asyncio
import aiohttp
import uuid
from pydantic import BaseModel
from app.db import Scoped_Session
from app.main import get_db_instance_by_random


class SQLValidationRequest(BaseModel):
    external_request_id: str
    qa_content: str


qa_set = [
    "User Question: 这个SQL:xxxx 如何通过hint 方式执行走 hash join ?\n\nAnswer:\n\nTo force a SQL query to execute using a hash join in TiDB, you can use the `HASH_JOIN` optimizer hint. Here's how you can do it:\n\n1. Add the hint `/*+ HASH_JOIN(table_alias1, table_alias2) */` after the `SELECT` keyword in your query.\n2. Replace `table_alias1` and `table_alias2` with the aliases or names of the tables you want to join using the hash join algorithm.\n\nHere's the general syntax:\n\n```sql\nSELECT /*+ HASH_JOIN(t1, t2) */ ...\nFROM table1 t1\nJOIN table2 t2 ON t1.id = t2.id;\n```\n\nThe `HASH_JOIN` hint tells the optimizer to use the hash join algorithm for the specified tables. This algorithm allows the query to be executed concurrently with multiple threads, which can achieve a higher processing speed but may consume more memory[^1].\n\nExample SQL:\n\nLet's create two sample tables and demonstrate how to use the `HASH_JOIN` hint:\n\n```sql\n-- Create sample tables\nCREATE TABLE books (\n  id INT PRIMARY KEY,\n  title VARCHAR(100),\n  author VARCHAR(100),\n  published_at DATE\n);\n\nCREATE TABLE ratings (\n  id INT PRIMARY KEY,\n  book_id INT,\n  score DECIMAL(3,2),\n  FOREIGN KEY (book_id) REFERENCES books(id)\n);\n\n-- Insert some sample data\nINSERT INTO books VALUES \n  (1, 'Book A', 'Author X', '2023-01-01'),\n  (2, 'Book B', 'Author Y', '2023-02-01'),\n  (3, 'Book C', 'Author Z', '2023-03-01');\n\nINSERT INTO ratings VALUES \n  (1, 1, 4.5),\n  (2, 1, 4.0),\n  (3, 2, 3.5),\n  (4, 3, 5.0);\n\n-- Query using HASH_JOIN hint\nEXPLAIN SELECT /*+ HASH_JOIN(b, r) */ b.id, b.title, AVG(r.score) AS avg_score\nFROM books b\nJOIN ratings r ON b.id = r.book_id\nGROUP BY b.id, b.title;\n```\n\nIn this example, we're using the `HASH_JOIN` hint to force TiDB to use a hash join when joining the `books` and `ratings` tables. The `EXPLAIN` statement is used to show the execution plan, which should indicate that a hash join is being used.\n\nTo verify that the hash join is being used, look for `HashJoin` in the `operator info` column of the `EXPLAIN` output.\n\nRemember that while hints can be useful for optimizing specific queries, it's generally best to let the optimizer choose the join algorithm. Only use hints when you're certain that a particular join method will perform better for your specific use case[^2].\n\n[^1]: [Optimizer Hints | PingCAP Docs](https://docs.pingcap.com/tidb/stable/optimizer-hints#hash_joint1_name--tl_name-)\n[^2]: [SQL Optimization Process | PingCAP Docs](https://docs.pingcap.com/tidb/stable/sql-optimization-concepts)\n ",
    "User Question: What is TiDB?\n\nAnswer:\n\nTiDB is an open-source, distributed SQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads[^1]. It is designed to provide a scalable, highly available, and MySQL-compatible database solution for large-scale data management. Here are the key aspects of TiDB:\n\n1. Architecture:\nTiDB has a distributed architecture consisting of several key components[^2]:\n- TiDB Server: The SQL layer that handles query parsing and execution\n- TiKV: A distributed key-value storage engine for data storage\n- Placement Driver (PD): Manages cluster metadata, scheduling, and load balancing\n- TiFlash: A columnar storage engine for analytical processing\n\n2. Key Features:\n- Horizontal Scalability: TiDB can easily scale out or in for both computing and storage resources[^3].\n- High Availability: It uses multi-replica storage and the Multi-Raft protocol to ensure data consistency and availability[^3].\n- MySQL Compatibility: TiDB is compatible with the MySQL 5.7 protocol and most MySQL features[^3].\n- ACID Transactions: It supports distributed ACID transactions across the cluster[^2].\n- Real-time HTAP: TiDB can handle both transactional and analytical workloads simultaneously[^1].\n\n3. Scalability:\nTiDB achieves scalability by separating computing from storage. This design allows for independent scaling of TiDB servers (SQL layer) and TiKV servers (storage layer) as needed[^3].\n\n4. High Availability:\nTiDB ensures high availability through:\n- Data replication across multiple nodes\n- Automatic failover when a minority of replicas fail\n- The ability to schedule data across different availability zones[^3]\n\n5. Cloud-Native Design:\nTiDB is built for cloud environments, offering:\n- Flexible deployment options on various cloud platforms\n- Integration with Kubernetes via TiDB Operator\n- A fully managed service called TiDB Cloud[^3]\n\n6. Data Migration and Management:\nTiDB provides tools for data migration, replication, and backup, making it easier to transition from other database systems[^2].\n\nExample SQL:\n\nTo demonstrate TiDB's MySQL compatibility and some of its features, here's an example of creating a table, inserting data, and performing a simple query:\n\n```sql\n-- Create a table\nCREATE TABLE users (\n    id INT PRIMARY KEY,\n    name VARCHAR(50),\n    age INT,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Insert some data\nINSERT INTO users (id, name, age) VALUES \n(1, 'Alice', 30),\n(2, 'Bob', 25),\n(3, 'Charlie', 35);\n\n-- Perform a query\nSELECT name, age FROM users WHERE age > 25 ORDER BY age DESC;\n```\n\nThis example showcases TiDB's support for standard SQL operations, including table creation, data insertion, and querying with filtering and sorting.\n\n[^1]: [TiDB FAQ | PingCAP Docs](https://docs.pingcap.com/tidb/v7.1/tidb-faq)\n[^2]: [TiDB Architecture | PingCAP Docs](https://docs.pingcap.com/tidb/v7.1/tidb-architecture)\n[^3]: [TiDB Overview | PingCAP Docs](https://docs.pingcap.com/tidb/v7.5/overview)\n ",
]


async def create_job(session, url, i):
    request = SQLValidationRequest(
        external_request_id="test#" + str(uuid.uuid4()),
        qa_content=qa_set[i % len(qa_set)],
    )
    async with session.post(url, json=request.model_dump()) as response:
        if response.status == 200:
            data = await response.json()
            print(f"Job {i} created successfully. Job ID: {data['job_id']}")
        else:
            print(f"Failed to create job {i}. Status code: {response.status}")


async def main():
    url = "https://verify.tidb.ai/api/v1/sqls-validation"  # Replace with your actual server URL
    async with aiohttp.ClientSession() as session:
        tasks = [create_job(session, url, i) for i in range(100)]
        await asyncio.gather(*tasks)


if __name__ == "__main__":
    asyncio.run(main())