def generate_departments_sql(input_file="FR.txt", output_file="departments_france_inserts.sql"):
    with open(input_file, "r", encoding="utf-8") as f_in, \
         open(output_file, "w", encoding="utf-8") as f_out:

        f_out.write("-- Requêtes SQL pour insérer les départements de France\n")
        f_out.write("-- Basé sur les données GeoNames\n\n")

        for line in f_in:
            columns = line.strip().split("\t")

            # Vérifier si c'est un département (feature class = 'A', feature code = 'ADM2')
            if len(columns) > 7 and columns[6] == "A" and columns[7] == "ADM2":
                name = columns[1].replace("'", "''")  # Échapper les apostrophes
                department_code = columns[11]  # admin2 code
                region_code = f"FRA-{columns[10]}"  # admin1 code
                latitude = columns[4]
                longitude = columns[5]

                # Écrire la requête SQL
                f_out.write(
                    f"INSERT INTO departments (department_code, department_name, region_code, latitude, longitude) VALUES\n"
                    f"('{department_code}', '{name}', '{region_code}', {latitude}, {longitude});\n"
                )

    print(f"Fichier généré : {output_file}")

# Exécuter le script
generate_departments_sql()