import csv
import mysql.connector

# 1. Connexion à MySQL
db = mysql.connector.connect(
     host="localhost",
        user="root",
        password="_z7b6CJc",
        database="immo_new"
)
cursor = db.cursor()
# 2. Lire le fichier CSV et insérer les données
with open('fr-en-annuaire-education.csv', 'r', encoding='utf-8-sig') as csvfile:
    reader = csv.DictReader(csvfile, delimiter=';')
    batch = []
    for row in reader:
        # Nettoyage des champs
        name = row['Nom_etablissement'].replace("'", "\\'")
        uai = row['Identifiant_de_l_etablissement']
        insee_code = row['Code_commune']
        lat = float(row['latitude'].replace(',', '.')) if row['latitude'] else None
        lon = float(row['longitude'].replace(',', '.')) if row['longitude'] else None
        address = f"{row['Adresse_1']} {row['Adresse_2']} {row['Adresse_3']}".strip().replace("'", "\\'")
        postal_code = row['Code_postal']
        city = row['Nom_commune'].replace("'", "\\'")
        phone = row['Telephone'] if row['Telephone'] else None
        website = row['Web'] if row['Web'] else None

        # Construction des missions
        missions = []
        if row['Ecole_maternelle'] == '1':
            missions.append("maternelle")
        if row['Ecole_elementaire'] == '1':
            missions.append("élémentaire")
        if row['Voie_generale'] == '1':
            missions.append("lycée général")
        if row['Voie_technologique'] == '1':
            missions.append("lycée technologique")
        if row['Voie_professionnelle'] == '1':
            missions.append("lycée professionnel")
        missions_str = ", ".join(missions) if missions else "inconnu"

        # Requête paramétrée (évite les injections SQL et gère les NULL)
        sql = (
            "INSERT INTO public_services "
            "(service_name, category, address, postal_code, city, phone, website, missions, latitude, longitude, insee_code) "
            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        )
        values = (
            name, 'school', address, postal_code, city,
            phone, website, missions_str, lat, lon, insee_code
        )
        batch.append(values)

        # Exécution par lots de 1000
        if len(batch) >= 1000:
            cursor.executemany(sql, batch)
            db.commit()
            batch = []

    # Exécution du dernier batch
    if batch:
        cursor.executemany(sql, batch)
        db.commit()

print("Import terminé : 60 000 écoles insérées !")
cursor.close()
db.close()