import os
import pandas as pd

if __name__ == '__main__':

    column_names = ["geonameid", "name", "asciiname", "alternatenames", "latitude", "longitude",
                    "feature class", "feature code", "country_code", "cc2",
                    "admin1 code", "admin2 code", "admin3 code", "admin4 code",
                    "population", "elevation", "dem", "timezone", "modification date",
                    ]
    top_city_df = pd.read_csv(os.path.join("geonames/cities15000/cities15000.txt"),
                              sep="\t",
                              names=column_names,
                              encoding="utf-8",
                              usecols=["asciiname", "alternatenames", "country_code", "population"])

    iso_df_1 = pd.read_csv(os.path.join("data/iso_3166_1.csv"),
                           names=["Name", "Code"])

    iso_df_2 = pd.read_csv(os.path.join("data/iso_3166_2.csv"),
                           names=["country_code", "subdivision_name", "code"])

    nation_df = pd.read_csv(os.path.join("data/nation.csv"), names=["ChineseName", "en"])

    merger = top_city_df.merge(iso_df_1, left_on=top_city_df.country_code, right_on=iso_df_1.Code)
    merger.drop(["key_0", "Code"], axis=1, inplace=True)
    merger = merger.merge(nation_df, left_on=merger.Name, right_on=nation_df.en)

    merger.drop(["key_0", "en"], axis=1, inplace=True)
    merger = merger.sort_values(by=['Name'], ascending=True)
    # merger = merger[["code", "subdivision_name", "country_code", "Name", "ChineseName"]]
    print(merger)
    merger.to_csv("city15000_spellings.csv")
    """
    ,CityName,CityAlternateNames,CountryCode,Population,CountryName,ChineseName
    """

    # while True:
    #     target = input()
    #     if target == "\n":
    #         break
    #     try:
    #         print(merger[merger["en"].str.contains(target, na=False)]["cn"].tolist()[0])
    #     except IndexError:
    #         print(target)