import json
import mysql.connector
from mysql.connector import Error
import geopandas as gpd
from shapely.geometry import Point
import pandas as pd

# Configuration de la base de données
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "_z7b6CJc",
    "database": "immo_new"
}

# Chemins vers les fichiers
GEOJSON_STOPS = "export_transport_commun.geojson"  # Vos arrêts
GEOJSON_COMMUNES = "communes-100m.geojson"         # Fichier des communes

# Charger les arrêts
def load_stops(file_path):
    with open(file_path, "r", encoding="utf-8") as f:
        data = json.load(f)
    stops = []
    for feature in data["features"]:
        coords = feature["geometry"]["coordinates"]
        stops.append({
            "name": feature["properties"].get("name", "unknown"),
            "category": "bus_stop" if feature["properties"].get("highway") == "bus_stop" else "unknown",
            "latitude": coords[1],
            "longitude": coords[0],
            "missions": feature["properties"].get("description")
        })
    return stops

# Charger les polygones des communes
def load_communes(file_path):
    gdf = gpd.read_file(file_path)
    gdf = gdf.rename(columns={"code": "code_insee", "nom": "nom_commune"})
    gdf = gdf[["code_insee", "nom_commune", "geometry"]]
    gdf = gdf.to_crs(epsg=4326)  # S'assurer que le CRS est en WGS84
    return gdf

# Créer un GeoDataFrame pour les arrêts
def create_stops_gdf(stops):
    geometry = [Point(stop["longitude"], stop["latitude"]) for stop in stops]
    return gpd.GeoDataFrame(stops, geometry=geometry, crs="EPSG:4326")

# Trouver le code INSEE pour chaque arrêt (version compatible)
def find_insee_codes(stops_gdf, communes_gdf):
    try:
        # Nouvelle syntaxe (geopandas >= 0.8)
        joined = gpd.sjoin(stops_gdf, communes_gdf, how="left", predicate="within")
    except TypeError:
        # Ancienne syntaxe (geopandas < 0.8)
        joined = gpd.sjoin(stops_gdf, communes_gdf, how="left")
    return joined[["name", "category", "latitude", "longitude", "missions", "code_insee", "nom_commune"]]

# Insérer en lot dans la base de données
def insert_stops_batch(connection, stops_with_insee, batch_size=1000):
    cursor = connection.cursor()
    query = """
    INSERT INTO public_services (
        service_name, category, address, postal_code, city, insee_code,
        phone, website, opening_hours, missions, latitude, longitude
    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    values = []
    inserted_count = 0

    for _, row in stops_with_insee.iterrows():
        if pd.isna(row["code_insee"]):
            continue  # Ignorer les arrêts sans code INSEE

        # Garder le code INSEE en chaîne de caractères (sans conversion en int)
        values.append((
            row["name"], row["category"], None, None, row["nom_commune"],
            str(row["code_insee"]), None, None, None, row["missions"],
            row["latitude"], row["longitude"]
        ))
        inserted_count += 1

        if len(values) >= batch_size:
            cursor.executemany(query, values)
            connection.commit()
            print(f"{inserted_count} arrêts insérés...")
            values = []

    if values:
        cursor.executemany(query, values)
        connection.commit()
        print(f"{inserted_count} arrêts insérés au total.")

# Fonction principale
def main():
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        stops = load_stops(GEOJSON_STOPS)
        stops_gdf = create_stops_gdf(stops)
        communes_gdf = load_communes(GEOJSON_COMMUNES)
        stops_with_insee = find_insee_codes(stops_gdf, communes_gdf)
        insert_stops_batch(connection, stops_with_insee)
    except Exception as e:
        print(f"Erreur : {e}")
    finally:
        if 'connection' in locals():
            connection.close()
        print("Connexion à la base de données fermée.")

if __name__ == "__main__":
    main()