import csv

input_file = "FR.txt"
output_file = "/var/lib/mysql-files/villes.sql"

# Dictionnaire pour mapper admin1_code vers region_code
region_mapping = {
    '84': 'FRA-ARA',  # Auvergne-Rhône-Alpes
    '27': 'FRA-BFC',  # Bourgogne-Franche-Comté
    '53': 'FRA-PDL',  # Pays de la Loire
    '24': 'FRA-CVL',  # Centre-Val de Loire
    '93': 'FRA-PAC',  # Provence-Alpes-Côte d'Azur
    '44': 'FRA-GES',  # Grand Est
    '32': 'FRA-HDF',  # Hauts-de-France
    '28': 'FRA-NOR',  # Normandie
    '75': 'FRA-NAQ',  # Nouvelle-Aquitaine
    '76': 'FRA-OCC',  # Occitanie
    '52': 'FRA-PDL',  # Pays de la Loire (doublon avec 53, à vérifier)
    '94': 'FRA-COR',  # Corse
    '11': 'FRA-IDF',  # Île-de-France
    '2': 'FRA-GUA',   # Guadeloupe
    '3': 'FRA-GUF',   # Guyane
    '1': 'FRA-MTQ',   # Martinique
    '4': 'FRA-REU',   # La Réunion
    '6': 'FRA-MAY'    # Mayotte
}

# Dictionnaire pour stocker une seule entrée par code INSEE
communes = {}

with open(input_file, "r", encoding="utf-8") as infile:
    reader = csv.reader(infile, delimiter="\t")
    for row in reader:
        feature_class = row[6]
        insee_code = row[13]

        # On ne garde que les villes/communes (feature_class = 'P')
        if feature_class == 'P' and insee_code != '00' and len(insee_code) <= 5:
            if insee_code not in communes:
                name = row[1].replace("'", "''")
                latitude = row[4]
                longitude = row[5]
                department_code = row[11]
                admin1_code = row[10]  # Code région GeoNames

                # Récupérer le region_code à partir du mapping
                region_code = region_mapping.get(admin1_code, 'FRA-IDF')  # Valeur par défaut : Île-de-France

                communes[insee_code] = (
                    insee_code, name, latitude, longitude, department_code, region_code
                )

# Écrire les requêtes SQL dans le fichier de sortie
with open(output_file, "w", encoding="utf-8") as outfile:
    for insee_code, data in communes.items():
        sql = f"""
        INSERT INTO cities (
            insee_code, city_name, latitude, longitude, department_code, region_code
        ) VALUES (
            '{data[0]}', '{data[1]}', {data[2]}, {data[3]}, '{data[4]}', '{data[5]}'
        );
        """
        outfile.write(sql + "\n")

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