# coding=utf-8
# Copyright 2021 The HuggingFace Datasets Authors and the current dataset script contributor.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
"""Spider: A Large-Scale Human-Labeled Dataset for Text-to-SQL Tasks"""

import os
import json
from third_party.spider.preprocess.get_tables import dump_db_json_schema

import datasets


logger = datasets.logging.get_logger(__name__)


_CITATION = """\
@article{yu2018spider,
  title={Spider: A large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-sql task},
  author={Yu, Tao and Zhang, Rui and Yang, Kai and Yasunaga, Michihiro and Wang, Dongxu and Li, Zifan and Ma, James and Li, Irene and Yao, Qingning and Roman, Shanelle and others},
  journal={arXiv preprint arXiv:1809.08887},
  year={2018}
}
"""

_DESCRIPTION = """\
Spider is a large-scale complex and cross-domain semantic parsing and text-toSQL dataset annotated by 11 college students
"""

_HOMEPAGE = "https://yale-lily.github.io/spider"

_LICENSE = "CC BY-SA 4.0"


_URL = "http://sstosql.oss-cn-hangzhou-zmf.aliyuncs.com/spider/spider.zip"


class Spider(datasets.GeneratorBasedBuilder):
    VERSION = datasets.Version("1.0.0")

    BUILDER_CONFIGS = [
        datasets.BuilderConfig(
            name="spider",
            version=VERSION,
            description="Spider: A Large-Scale Human-Labeled Dataset for Text-to-SQL Tasks",
        ),
    ]

    def __init__(self, *args, writer_batch_size=None, **kwargs):
        super().__init__(*args, writer_batch_size=writer_batch_size, **kwargs)
        self.schema_cache = dict()

    def _info(self):
        features = datasets.Features(
            {
                "query": datasets.Value("string"),
                "question": datasets.Value("string"),
                "db_id": datasets.Value("string"),
                "db_path": datasets.Value("string"),
                "db_table_names": datasets.features.Sequence(datasets.Value("string")),
                "db_column_names": datasets.features.Sequence(
                    {
                        "table_id": datasets.Value("int32"),
                        "column_name": datasets.Value("string"),
                    }
                ),
                "db_column_types": datasets.features.Sequence(datasets.Value("string")),
                "db_primary_keys": datasets.features.Sequence({"column_id": datasets.Value("int32")}),
                "db_foreign_keys": datasets.features.Sequence(
                    {
                        "column_id": datasets.Value("int32"),
                        "other_column_id": datasets.Value("int32"),
                    }
                ),
                "graph_idx": datasets.features.Value("int32"),
                "raw_question_toks": datasets.features.Sequence(datasets.Value("string")),
            }
        )
        return datasets.DatasetInfo(
            description=_DESCRIPTION,
            features=features,
            supervised_keys=None,
            homepage=_HOMEPAGE,
            license=_LICENSE,
            citation=_CITATION,
        )

    def _split_generators(self, dl_manager):
        # downloaded_filepath = dl_manager.download_and_extract(_URL)
        downloaded_filepath = './data_all_in/data'

        return [
            datasets.SplitGenerator(
                name=datasets.Split.TRAIN,
                gen_kwargs={
                    "data_filepath": downloaded_filepath + "/train_syntax.json",
                    "db_path": downloaded_filepath + "/spider/database",
                },
            ),
            datasets.SplitGenerator(
                name=datasets.Split.VALIDATION,
                gen_kwargs={
                    "data_filepath": downloaded_filepath + "/dev_syntax.json",
                    "db_path": downloaded_filepath + "/spider/database",
                },
            ),
        ]

    def _generate_examples(self, data_filepath, db_path):
        """This function returns the examples in the raw (text) form."""
        logger.info("generating examples from = %s", data_filepath)
        with open(data_filepath, encoding="utf-8") as f:
            spider = json.load(f)
            for idx, sample in enumerate(spider):
                db_id = sample["db_id"]
                if db_id not in self.schema_cache:
                    self.schema_cache[db_id] = dump_db_json_schema(
                        db_path + "/" + db_id + "/" + db_id + ".sqlite", db_id
                    )
                schema = self.schema_cache[db_id]
                yield idx, {
                    "query": sample["query"],
                    "question": sample["question"],
                    "db_id": db_id,
                    "db_path": db_path,
                    "db_table_names": schema["table_names_original"],
                    "db_column_names": [
                        {"table_id": table_id, "column_name": column_name}
                        for table_id, column_name in schema["column_names_original"]
                    ],
                    "db_column_types": schema["column_types"],
                    "db_primary_keys": [{"column_id": column_id} for column_id in schema["primary_keys"]],
                    "db_foreign_keys": [
                        {"column_id": column_id, "other_column_id": other_column_id}
                        for column_id, other_column_id in schema["foreign_keys"]
                    ],
                    "graph_idx": sample["graph_idx"],
                    "raw_question_toks": sample["raw_question_toks"],
                }


# class Spider(datasets.GeneratorBasedBuilder):
#     VERSION = datasets.Version("1.0.0")

#     BUILDER_CONFIGS = [
#         datasets.BuilderConfig(
#             name="spider",
#             version=VERSION,
#             description="Spider: A Large-Scale Human-Labeled Dataset for Text-to-SQL Tasks",
#         ),
#     ]

#     def __init__(self, *args, writer_batch_size=None, **kwargs):
#         super().__init__(*args, writer_batch_size=writer_batch_size, **kwargs)
#         self.schema_cache = dict()

#     def _info(self):
#         features = datasets.Features(
#             {
#                 "query": datasets.Value("string"),
#                 "question": datasets.Value("string"),
#                 "db_id": datasets.Value("string"),
#                 "db_path": datasets.Value("string"),
#                 "db_table_names": datasets.features.Sequence(datasets.Value("string")),
#                 "db_column_names": datasets.features.Sequence(
#                     {
#                         "table_id": datasets.Value("int32"),
#                         "column_name": datasets.Value("string"),
#                     }
#                 ),
#                 "db_column_types": datasets.features.Sequence(datasets.Value("string")),
#                 "db_primary_keys": datasets.features.Sequence({"column_id": datasets.Value("int32")}),
#                 "db_foreign_keys": datasets.features.Sequence(
#                     {
#                         "column_id": datasets.Value("int32"),
#                         "other_column_id": datasets.Value("int32"),
#                     }
#                 ),
#                 # "graph_idx": datasets.features.Value("int32"),
#                 "raw_question_toks": datasets.features.Sequence(datasets.Value("string")),
#             }
#         )
#         return datasets.DatasetInfo(
#             description=_DESCRIPTION,
#             features=features,
#             supervised_keys=None,
#             homepage=_HOMEPAGE,
#             license=_LICENSE,
#             citation=_CITATION,
#         )

#     def _split_generators(self, dl_manager):
#         # downloaded_filepath = dl_manager.download_and_extract(_URL)
#         downloaded_filepath = '.'

#         return [
#             datasets.SplitGenerator(
#                 name=datasets.Split.TRAIN,
#                 gen_kwargs={
#                     "data_filepath": downloaded_filepath + "/spider-realistic/dev_syntax.json",
#                     "db_path": downloaded_filepath + "/spider-realistic/database",
#                 },
#             ),
#             datasets.SplitGenerator(
#                 name=datasets.Split.VALIDATION,
#                 gen_kwargs={
#                     "data_filepath": downloaded_filepath + "/spider-realistic/dev_syntax.json",
#                     "db_path": downloaded_filepath + "/spider-realistic/database",
#                 },
#             ),
#         ]

#     def _generate_examples(self, data_filepath, db_path):
#         """This function returns the examples in the raw (text) form."""
#         logger.info("generating examples from = %s", data_filepath)
#         with open(data_filepath, encoding="utf-8") as f:
#             spider = json.load(f)
#             for idx, sample in enumerate(spider):
#                 db_id = sample["db_id"]
#                 if db_id not in self.schema_cache:
#                     self.schema_cache[db_id] = dump_db_json_schema(
#                         db_path + "/" + db_id + "/" + db_id + ".sqlite", db_id
#                     )
#                 schema = self.schema_cache[db_id]
#                 yield idx, {
#                     "query": sample["query"],
#                     "question": sample["question"],
#                     "db_id": db_id,
#                     "db_path": db_path,
#                     "db_table_names": schema["table_names_original"],
#                     "db_column_names": [
#                         {"table_id": table_id, "column_name": column_name}
#                         for table_id, column_name in schema["column_names_original"]
#                     ],
#                     "db_column_types": schema["column_types"],
#                     "db_primary_keys": [{"column_id": column_id} for column_id in schema["primary_keys"]],
#                     "db_foreign_keys": [
#                         {"column_id": column_id, "other_column_id": other_column_id}
#                         for column_id, other_column_id in schema["foreign_keys"]
#                     ],
#                     # "graph_idx": sample["graph_idx"],
#                     "raw_question_toks": sample["raw_question_toks"],
#                 }
# '''template'''
# class Spider(datasets.GeneratorBasedBuilder):
#     VERSION = datasets.Version("1.0.0")

#     BUILDER_CONFIGS = [
#         datasets.BuilderConfig(
#             name="spider",
#             version=VERSION,
#             description="Spider: A Large-Scale Human-Labeled Dataset for Text-to-SQL Tasks",
#         ),
#     ]

#     def __init__(self, *args, writer_batch_size=None, **kwargs):
#         super().__init__(*args, writer_batch_size=writer_batch_size, **kwargs)
#         self.schema_cache = dict()

#     def _info(self):
#         features = datasets.Features(
#             {
#                 "query": datasets.Value("string"),
#                 "question": datasets.Value("string"),
#                 "db_id": datasets.Value("string"),
#                 "db_path": datasets.Value("string"),
#                 "db_table_names": datasets.features.Sequence(datasets.Value("string")),
#                 "db_column_names": datasets.features.Sequence(
#                     {
#                         "table_id": datasets.Value("int32"),
#                         "column_name": datasets.Value("string"),
#                     }
#                 ),
#                 "db_column_types": datasets.features.Sequence(datasets.Value("string")),
#                 "db_primary_keys": datasets.features.Sequence({"column_id": datasets.Value("int32")}),
#                 "db_foreign_keys": datasets.features.Sequence(
#                     {
#                         "column_id": datasets.Value("int32"),
#                         "other_column_id": datasets.Value("int32"),
#                     }
#                 ),
#                 # "graph_idx": datasets.features.Value("int32"),
#                 # "raw_question_toks": datasets.features.Sequence(datasets.Value("string")),
#             }
#         )
#         return datasets.DatasetInfo(
#             description=_DESCRIPTION,
#             features=features,
#             supervised_keys=None,
#             homepage=_HOMEPAGE,
#             license=_LICENSE,
#             citation=_CITATION,
#         )

#     def _split_generators(self, dl_manager):
#         # downloaded_filepath = dl_manager.download_and_extract(_URL)
#         downloaded_filepath = '.'

#         return [
#             datasets.SplitGenerator(
#                 name=datasets.Split.TRAIN,
#                 gen_kwargs={
#                     "data_filepath": downloaded_filepath + "/compositional/template/template_train_graph.json",
#                     "db_path": downloaded_filepath + "/compositional/database",
#                 },
#             ),
#             datasets.SplitGenerator(
#                 name=datasets.Split.VALIDATION,
#                 gen_kwargs={
#                     "data_filepath": downloaded_filepath + "/compositional/template/template_dev_graph.json",
#                     "db_path": downloaded_filepath + "/compositional/database",
#                 },
#             ),
#         ]

#     def _generate_examples(self, data_filepath, db_path):
#         """This function returns the examples in the raw (text) form."""
#         logger.info("generating examples from = %s", data_filepath)
#         with open(data_filepath, encoding="utf-8") as f:
#             spider = json.load(f)
#             for idx, sample in enumerate(spider):
#                 db_id = sample["db_id"]
#                 if db_id not in self.schema_cache:
#                     self.schema_cache[db_id] = dump_db_json_schema(
#                         db_path + "/" + db_id + "/" + db_id + ".sqlite", db_id
#                     )
#                 schema = self.schema_cache[db_id]
#                 yield idx, {
#                     "query": sample["query"],
#                     "question": sample["question"],
#                     "db_id": db_id,
#                     "db_path": db_path,
#                     "db_table_names": schema["table_names_original"],
#                     "db_column_names": [
#                         {"table_id": table_id, "column_name": column_name}
#                         for table_id, column_name in schema["column_names_original"]
#                     ],
#                     "db_column_types": schema["column_types"],
#                     "db_primary_keys": [{"column_id": column_id} for column_id in schema["primary_keys"]],
#                     "db_foreign_keys": [
#                         {"column_id": column_id, "other_column_id": other_column_id}
#                         for column_id, other_column_id in schema["foreign_keys"]
#                     ],
#                     # "graph_idx": sample["graph_idx"],
#                     # "raw_question_toks": sample["raw_question_toks"],
#                 }

# '''template'''
# class Spider(datasets.GeneratorBasedBuilder):
#     VERSION = datasets.Version("1.0.0")

#     BUILDER_CONFIGS = [
#         datasets.BuilderConfig(
#             name="spider",
#             version=VERSION,
#             description="Spider: A Large-Scale Human-Labeled Dataset for Text-to-SQL Tasks",
#         ),
#     ]

#     def __init__(self, *args, writer_batch_size=None, **kwargs):
#         super().__init__(*args, writer_batch_size=writer_batch_size, **kwargs)
#         self.schema_cache = dict()

#     def _info(self):
#         features = datasets.Features(
#             {
#                 "query": datasets.Value("string"),
#                 "question": datasets.Value("string"),
#                 "db_id": datasets.Value("string"),
#                 "db_path": datasets.Value("string"),
#                 "db_table_names": datasets.features.Sequence(datasets.Value("string")),
#                 "db_column_names": datasets.features.Sequence(
#                     {
#                         "table_id": datasets.Value("int32"),
#                         "column_name": datasets.Value("string"),
#                     }
#                 ),
#                 "db_column_types": datasets.features.Sequence(datasets.Value("string")),
#                 "db_primary_keys": datasets.features.Sequence({"column_id": datasets.Value("int32")}),
#                 "db_foreign_keys": datasets.features.Sequence(
#                     {
#                         "column_id": datasets.Value("int32"),
#                         "other_column_id": datasets.Value("int32"),
#                     }
#                 ),
#                 # "graph_idx": datasets.features.Value("int32"),
#                 # "raw_question_toks": datasets.features.Sequence(datasets.Value("string")),
#             }
#         )
#         return datasets.DatasetInfo(
#             description=_DESCRIPTION,
#             features=features,
#             supervised_keys=None,
#             homepage=_HOMEPAGE,
#             license=_LICENSE,
#             citation=_CITATION,
#         )

#     def _split_generators(self, dl_manager):
#         # downloaded_filepath = dl_manager.download_and_extract(_URL)
#         downloaded_filepath = '.'

#         return [
#             datasets.SplitGenerator(
#                 name=datasets.Split.TRAIN,
#                 gen_kwargs={
#                     "data_filepath": downloaded_filepath + "/compositional/length/length_train_graph.json",
#                     "db_path": downloaded_filepath + "/compositional/database",
#                 },
#             ),
#             datasets.SplitGenerator(
#                 name=datasets.Split.VALIDATION,
#                 gen_kwargs={
#                     "data_filepath": downloaded_filepath + "/compositional/length/length_dev_graph.json",
#                     "db_path": downloaded_filepath + "/compositional/database",
#                 },
#             ),
#         ]

#     def _generate_examples(self, data_filepath, db_path):
#         """This function returns the examples in the raw (text) form."""
#         logger.info("generating examples from = %s", data_filepath)
#         with open(data_filepath, encoding="utf-8") as f:
#             spider = json.load(f)
#             for idx, sample in enumerate(spider):
#                 db_id = sample["db_id"]
#                 if db_id not in self.schema_cache:
#                     self.schema_cache[db_id] = dump_db_json_schema(
#                         db_path + "/" + db_id + "/" + db_id + ".sqlite", db_id
#                     )
#                 schema = self.schema_cache[db_id]
#                 yield idx, {
#                     "query": sample["query"],
#                     "question": sample["question"],
#                     "db_id": db_id,
#                     "db_path": db_path,
#                     "db_table_names": schema["table_names_original"],
#                     "db_column_names": [
#                         {"table_id": table_id, "column_name": column_name}
#                         for table_id, column_name in schema["column_names_original"]
#                     ],
#                     "db_column_types": schema["column_types"],
#                     "db_primary_keys": [{"column_id": column_id} for column_id in schema["primary_keys"]],
#                     "db_foreign_keys": [
#                         {"column_id": column_id, "other_column_id": other_column_id}
#                         for column_id, other_column_id in schema["foreign_keys"]
#                     ],
#                     # "graph_idx": sample["graph_idx"],
#                     # "raw_question_toks": sample["raw_question_toks"],
#                 }
