import csv
import glob


def get_dbname(filename):
    return filename.split("/")[-1].removeprefix("cases_new_").removesuffix("_st_sqlite.csv").removesuffix("_st_with_equal.csv")


def filter_all_cases20230731():
    for i in glob.glob("/home/jiajun/Downloads/spider/cases_20230731/cases_new*_st_sqlite.csv"):
        yield i


def filter_all_cases20230723():
    for i in glob.glob("/home/jiajun/Downloads/cases_20230731_regen_20230724/cases_new*_st_with_equal.csv"):
        yield i


def get_dbnames(gen):
    s = set()

    for filename in gen:
        with open(filename) as f:
            dbname = get_dbname(filename)
            for line in csv.DictReader(f):
                s.add(dbname + ":" + line["question"])

    return s


def read_cases_by_csv(csv_filename):
    results = {}

    with open(csv_filename) as f:
        for i in csv.DictReader(f):
            results[i["question"]] = i

    return results


def compare():
    old = get_dbnames(filter_all_cases20230723())
    new = get_dbnames(filter_all_cases20230731())

    dbname_set = set()
    for i in (new - old):
        dbname_set.add(i.split(":")[0])

    print(dbname_set)


def compare_cases_in_detail():
    with open("analysis_in_detail.csv", "w") as f:
        writer = csv.writer(f)
        writer.writerow(["dbname", "difficulty", "question", "std", "old", "new"])

        old_mapper, new_mapper = {}, {}
        for i in filter_all_cases20230723():
            dbname = get_dbname(i)
            old_mapper[dbname] = i

        for i in filter_all_cases20230731():
            dbname = get_dbname(i)
            new_mapper[dbname] = i

        for k, v in old_mapper.items():
            new_v = new_mapper[k]
            dbname = get_dbname(v)

            old_cases = read_cases_by_csv(v)
            new_cases = read_cases_by_csv(new_v)

            for kk, vv in old_cases.items():
                if vv["equal"] in ("True", "TRUE"):
                    new_equal = new_cases.get(kk, {}).get("equal")
                    if new_equal in ("False", "FALSE"):
                        writer.writerow([dbname, vv["difficulty"], vv["question"], vv["sql"], vv["generated_sql"], new_cases[kk]["generated_sql"]])


if __name__ == "__main__":
    compare_cases_in_detail()