import mysql.connector
import logging
from datetime import datetime

# Configuration de la connexion à la base de données
db_config = {
    "host": "localhost",
    "user": "root",
    "password": "_z7b6CJc",  # Remplacez par votre mot de passe
    "database": "immo_new"
}

# Configuration des logs
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler("update_counts.log"),
        logging.StreamHandler()
    ]
)

# Mappage des valeurs de 'category' vers les colonnes de 'loc_cities'
category_groups = {
    "alimentation_count": ["supermarket", "bakery", "butcher", "greengrocer", "restaurant", "cafe", "fast_food"],
    "commerce_count": ["convenience", "clothing_store", "hardware", "book_store", "mall"],
    "sante_bien_etre_count": ["hospital", "clinic", "doctor", "dentist"],
    "loisirs_culture_count": ["park", "cinema", "gym", "library", "theatre", "museum"],
    "education_formation_count": ["school", "kindergarten", "university"],
    "transports_mobilite_count": ["bus_stop", "train_station", "taxi", "bicycle_rental"],
    "services_publics_count": ["post_office", "town_hall", "police"],
    "services_reparations_count": ["laundry", "bank", "hairdresser"]
}

def update_counts():
    try:
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        logging.info("Connexion à la base de données établie.")

        # Vérification et création de l'index
        cursor.execute("""
        SELECT COUNT(1) AS index_exists
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE table_name = 'data_services'
        AND index_name = 'idx_insee_category';
        """)
        result = cursor.fetchone()

        if result["index_exists"] == 0:
            cursor.execute("CREATE INDEX idx_insee_category ON data_services(insee_code, category);")
            conn.commit()
            logging.info("Index 'idx_insee_category' créé.")
        else:
            logging.info("Index 'idx_insee_category' existe déjà.")

        for column, categories in category_groups.items():
            start_time = datetime.now()
            logging.info(f"Début de la mise à jour pour {column}...")

            # Construction de la requête pour regrouper les catégories
            placeholders = ', '.join(['%s'] * len(categories))
            count_query = f"""
            SELECT
                ds.insee_code,
                COUNT(*) as count
            FROM
                data_services ds
            WHERE
                ds.category IN ({placeholders})
            GROUP BY
                ds.insee_code;
            """

            cursor.execute(count_query, categories)
            counts = cursor.fetchall()

            if len(counts) == 0:
                logging.warning(f"Aucune donnée trouvée pour les catégories {categories}. Mise à jour ignorée.")
                continue

            for row in counts:
                insee_code = row["insee_code"]
                count = row["count"]
                logging.info(f"Mise à jour de {insee_code} avec {count} pour {column}")

                update_query = f"""
                UPDATE loc_cities
                SET {column} = %s
                WHERE insee_code = %s;
                """
                cursor.execute(update_query, (count, insee_code))

            conn.commit()
            logging.info(f"Mise à jour de {column} validée en {datetime.now() - start_time}.")

        logging.info("Toutes les colonnes ont été traitées.")

    except mysql.connector.Error as err:
        logging.error(f"Erreur MySQL : {err}")
        conn.rollback()
        logging.info("Rollback effectué.")

    except Exception as e:
        logging.error(f"Erreur inattendue : {e}")
        conn.rollback()
        logging.info("Rollback effectué.")

    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
            logging.info("Connexion à la base de données fermée.")

if __name__ == "__main__":
    update_counts()