import glob
import csv
import os
from concurrent.futures import ProcessPoolExecutor
import json
import logging

import requests
from test_context import base_url, headers, get_auth_key, loopv2
from parse_cases import parse_cases, sort_csv, analysis


def setup_context(dbname: str):
    url = f"{base_url}/v2/dataSummaries"

    payload = {
        "database_uri": gen_sqlite_url(dbname),
        "creator": "jiajun",
        "reuse": True,
    }

    resp = requests.post(url, json=payload, auth=get_auth_key(), headers=headers)
    logging.info("create_data_summary %s got %s", payload, resp.text)
    result = resp.json()["result"]

    return result["data_summary_id"], result["job_id"]


def run_db_cases(dbname):
    cases_filename = "./spider/bird/dev.json"
    csv_filename = "cases_{}_st_sqlite.csv".format(dbname)

    data_context_id, job_id = setup_context(dbname)
    loopv2(job_id)

    with open(csv_filename, 'w', newline='') as csvfile:
        csv_header = ["database", "question", "sql", "generated_sql", "difficulty", "equal", "note", "bird_eval"]
        if os.getenv("DEBUG"):
            csv_header += ["time_cost", "clarified_task", "raw_generated_sql", "refine_note"]

        csvwriter = csv.writer(csvfile)
        csvwriter.writerow(csv_header)

        parse_cases(csvwriter, data_context_id, cases_filename, dbname)


def parse_bird_cases():
    with open("./spider/bird/dev.json") as f:
        data = json.load(f)
        return data


def gen_sqlite_url(db_name: str):
    # bird/dev_databases/formula_1/formula_1.sqlite
    return "bird://{}".format(db_name)


def run_cases_in_dbs(dbnames):
    max_workers = int(os.getenv("MAX_WORKERS", 4))
    with ProcessPoolExecutor(max_workers=max_workers) as pool:
        for i in dbnames:
            pool.submit(run_db_cases, i)


# for i in parse_bird_cases():
    # # {'question_id': 1532, 'db_id': 'debit_card_specializing', 'question': 'Which country had the gas station that sold the most expensive product id No.2 for one unit?', 'evidence': '', 'SQL': 'SELECT T2.Country FROM transactions_1k AS T1 INNER JOIN gasstations AS T2 ON T1.GasStationID = T2.GasStationID WHERE T1.ProductID = 2 ORDER BY T1.Price DESC LIMIT 1', 'difficulty': 'simple'}
    # print(i)


def filter_all_sqlites_has_data():
    for i in glob.glob("./spider/bird/dev_databases/**/*.sqlite"):

        yield i.split("/")[-2]


def run_all_cases():
    with ProcessPoolExecutor(max_workers=4) as pool:
        for i in filter_all_sqlites_has_data():
            pool.submit(run_db_cases, i)


if __name__ == "__main__":
    # run_cases_in_dbs(["california_schools", "card_games"])
    run_db_cases("card_games")
    # run_all_cases()