import mysql.connector
from mysql.connector import Error
import math

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

def create_connection():
    """Établit la connexion MySQL et la renvoie."""
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            print("Connexion à la base de données réussie.")
            return connection
    except Error as e:
        print(f"Erreur de connexion à MySQL : {e}")
    return None

def create_and_fill_temp_tables(connection):
    """Crée et remplit les tables temporaires."""
    if not connection.is_connected():
        print("La connexion à la base de données n'est plus active.")
        return False

    try:
        with connection.cursor(dictionary=True) as cursor:
            # Créer des tables temporaires pour stocker les comptages par catégorie
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_alimentation_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_commerce_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_services_reparations_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_sante_bien_etre_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_loisirs_culture_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_education_formation_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_transports_mobilite_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_services_publics_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)
            cursor.execute("""
                CREATE TEMPORARY TABLE IF NOT EXISTS temp_autres_services_counts (
                    insee_code VARCHAR(9),
                    count INT,
                    PRIMARY KEY (insee_code)
                );
            """)

            # Remplir les tables temporaires avec les comptages en utilisant original_category
            cursor.execute("""
                INSERT INTO temp_alimentation_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('post_office', 'supermarket', 'bakery', 'greengrocer', 'butcher', 'deli', 'pastry', 'chocolate', 'seafood', 'cheese', 'wine', 'alcohol', 'beverages', 'coffee', 'tea', 'ice_cream', 'food_court', 'snack', 'confectionery', 'fruit', 'vegetables', 'cider', 'olive_oil', 'honey', 'flour', 'pasta', 'spices', 'cannery', 'meat', 'fishmonger', 'dairy', 'farm', 'grocery', 'convenience', 'cafe', 'bar', 'fast_food', 'restaurant', 'pub', 'nightclub', 'crepes', 'biscuit', 'cake', 'juice_bar', 'popcorn', 'donuts', 'galette', 'cookies', 'breakfast', 'food', 'caterer', 'brewery', 'distillery', 'winery')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            cursor.execute("""
                INSERT INTO temp_commerce_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('clothes', 'shoes', 'jewelry', 'furniture', 'electronics', 'hardware', 'cosmetics', 'perfumery', 'toys', 'books', 'garden_centre', 'gift', 'bag', 'antiques', 'art', 'craft', 'souvenir', 'stationery', 'mobile_phone', 'computer', 'music', 'sports', 'musical_instrument', 'houseware', 'kitchenware', 'tableware', 'cutlery', 'glassware', 'fabric', 'leather', 'watch', 'optician', 'pharmacy', 'tobacco', 'newsagent', 'variety_store', 'department_store', 'mall', 'shopping_centre', 'marketplace', 'second_hand', 'fashion_accessories', 'baby_goods', 'pet', 'flower', 'chocolatier', 'boutique', 'beauty', 'hairdresser', 'fashion', 'accessories', 'junk_yard', 'car_parts', 'bicycle', 'motorcycle', 'outdoor', 'hunting', 'fishing', 'weapons', 'games', 'video_games', 'anime', 'manga', 'trophy')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            cursor.execute("""
                INSERT INTO temp_services_reparations_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('car_repair', 'car_wash', 'truck_repair', 'motorcycle_repair', 'bicycle_repair', 'computer_repair', 'mobile_phone_repair', 'appliance_repair', 'furniture_repair', 'shoe_repair', 'watchmaker', 'locksmith', 'electrician', 'plumber', 'hvac', 'painter', 'tiler', 'glaziery', 'roofer', 'carpenter', 'builder', 'mason', 'cleaner', 'laundry', 'dry_cleaning', 'tailor', 'sewing', 'key_cutter', 'engraver', 'print_shop', 'photo_laboratory', 'clockmaker', 'jeweller', 'mechanic', 'welding', 'tyres', 'autoecole', 'car_rental', 'bicycle_rental', 'tool_hire', 'repair', 'technician', 'handyman', 'security', 'alarm', 'insurance', 'bank', 'money_transfer', 'pawnbroker')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            cursor.execute("""
                INSERT INTO temp_sante_bien_etre_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('pharmacy', 'optician', 'hearing_aids', 'medical_supply', 'dentist', 'massage', 'beauty', 'hairdresser', 'tattoo', 'piercing', 'spa', 'wellness', 'therapist', 'orthopaedics', 'medical', 'veterinary', 'psychic', 'alternative_medicine', 'nutrition_supplements', 'dietetic', 'herbalist')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            cursor.execute("""
                INSERT INTO temp_loisirs_culture_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('cinema', 'theatre', 'art_gallery', 'museum', 'library', 'music_venue', 'dancing_school', 'sports', 'gym', 'swimming_pool', 'golf', 'tennis', 'horseback_riding', 'shooting_stand', 'archery', 'scuba_diving', 'canoe', 'sailing', 'water_ski', 'ski', 'ski_school', 'surf', 'kitesurfing', 'hiking', 'camping', 'amusement_park', 'casino', 'gambling', 'toys', 'games', 'video_games', 'hobby', 'craft', 'photographer', 'art_school', 'cultural_centre', 'tourism')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            cursor.execute("""
                INSERT INTO temp_education_formation_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('school', 'college', 'university', 'driving_school', 'language_school', 'music_school', 'training', 'educational_institution', 'kindergarten', 'tutoring', 'educational_support')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            cursor.execute("""
                INSERT INTO temp_transports_mobilite_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('fuel', 'car', 'motorcycle', 'bicycle', 'truck', 'car_rental', 'bicycle_rental', 'taxi', 'bus_station', 'train_station', 'airport', 'parking', 'car_wash', 'car_inspection', 'moving_company', 'transport', 'logistics', 'travel_agency', 'boat_rental', 'boat_repair', 'airplane', 'helicopter')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            cursor.execute("""
                INSERT INTO temp_services_publics_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('post_office', 'townhall', 'police', 'fire_station', 'hospital', 'clinic', 'pharmacy', 'government', 'courthouse', 'embassy', 'public_service', 'association', 'ngo', 'religion', 'place_of_worship', 'cemetery', 'funeral_hall', 'recycling', 'public_toilet', 'public_building', 'community_centre', 'social_facility', 'employment_agency', 'public_transport', 'park', 'playground')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            cursor.execute("""
                INSERT INTO temp_autres_services_counts (insee_code, count)
                SELECT insee_code, COUNT(*)
                FROM data_services
                WHERE original_category IN ('estate_agent', 'rental', 'real_estate', 'lawyer', 'accountant', 'consulting', 'advertising', 'marketing', 'event', 'wedding', 'funeral_directors', 'auction', 'printing', 'graphic_design', 'web_design', 'it', 'telecommunication', 'internet_cafe', 'coworking', 'office', 'business', 'financial', 'insurance', 'travel_agency', 'lottery', 'betting', 'charity', 'volunteering', 'environmental', 'animal_shelter', 'pet_grooming', 'pet_boarding', 'agricultural', 'farming', 'forestry', 'mining', 'industrial', 'manufacturing', 'wholesale', 'storage', 'warehouse', 'factory', 'workshop', 'laboratory', 'research', 'scientific', 'military', 'security', 'cleaning', 'gardener', 'landscape', 'pest_control', 'waste_management', 'recycling', 'energy', 'water', 'gas', 'electricity', 'sanitation', 'post_box', 'parcel_locker', 'lost_and_found', 'tourist_info')
                AND original_category IS NOT NULL
                GROUP BY insee_code
                ON DUPLICATE KEY UPDATE count = VALUES(count);
            """)
            connection.commit()
            return True
    except Error as e:
        connection.rollback()
        print(f"Erreur lors de la création et du remplissage des tables temporaires : {e}")
        return False

def normalize_score(value, max_value):
    """Normalise une valeur en utilisant une échelle logarithmique."""
    if value <= 0 or max_value <= 0:
        return 0
    # Utiliser une échelle logarithmique pour normaliser les scores
    log_value = math.log10(value + 1)
    log_max = math.log10(max_value + 1)
    normalized_score = (log_value / log_max) * 100
    return min(normalized_score, 100)

def update_scores_by_batch(connection, batch_size=100):
    """Met à jour les scores dans la table data_services_scores."""
    if not connection.is_connected():
        print("La connexion à la base de données n'est plus active.")
        return

    # Créer et remplir les tables temporaires
    if not create_and_fill_temp_tables(connection):
        return

    try:
        with connection.cursor(dictionary=True) as cursor:
            # Supprimer les données existantes pour 2025
            cursor.execute("DELETE FROM data_services_scores WHERE data_year = 2025;")
            cursor.fetchall()
            connection.commit()
            print("Données existantes pour 2025 supprimées.")

            # Traiter les villes par lots
            offset = 0
            while True:
                cursor.execute("""
                    SELECT insee_code, city_name, population
                    FROM loc_cities
                    LIMIT %s OFFSET %s;
                """, (batch_size, offset))
                cities = cursor.fetchall()
                if not cities:
                    break
                for city in cities:
                    insee_code = city['insee_code']
                    city_name = city['city_name']
                    population = city['population']
                    if population is None or population <= 0:
                        continue  # Ignorer les villes sans population définie

                    # Récupérer les données nécessaires pour le calcul des scores
                    cursor.execute("""
                        SELECT
                            COALESCE(ac.count, 0) AS alimentation_count,
                            COALESCE(cc.count, 0) AS commerce_count,
                            COALESCE(src.count, 0) AS services_reparations_count,
                            COALESCE(sbc.count, 0) AS sante_bien_etre_count,
                            COALESCE(lcc.count, 0) AS loisirs_culture_count,
                            COALESCE(efc.count, 0) AS education_formation_count,
                            COALESCE(tmc.count, 0) AS transports_mobilite_count,
                            COALESCE(spc.count, 0) AS services_publics_count,
                            COALESCE(asvc.count, 0) AS autres_services_count
                        FROM loc_cities lc
                        LEFT JOIN temp_alimentation_counts ac ON lc.insee_code = ac.insee_code
                        LEFT JOIN temp_commerce_counts cc ON lc.insee_code = cc.insee_code
                        LEFT JOIN temp_services_reparations_counts src ON lc.insee_code = src.insee_code
                        LEFT JOIN temp_sante_bien_etre_counts sbc ON lc.insee_code = sbc.insee_code
                        LEFT JOIN temp_loisirs_culture_counts lcc ON lc.insee_code = lcc.insee_code
                        LEFT JOIN temp_education_formation_counts efc ON lc.insee_code = efc.insee_code
                        LEFT JOIN temp_transports_mobilite_counts tmc ON lc.insee_code = tmc.insee_code
                        LEFT JOIN temp_services_publics_counts spc ON lc.insee_code = spc.insee_code
                        LEFT JOIN temp_autres_services_counts asvc ON lc.insee_code = asvc.insee_code
                        WHERE lc.insee_code = %s;
                    """, (insee_code,))
                    city_data = cursor.fetchone()
                    if city_data:
                        # Calcul des densités de services par 1000 habitants
                        alimentation_density = (city_data['alimentation_count'] / population) * 1000
                        commerce_density = (city_data['commerce_count'] / population) * 1000
                        services_reparations_density = (city_data['services_reparations_count'] / population) * 1000
                        sante_bien_etre_density = (city_data['sante_bien_etre_count'] / population) * 1000
                        loisirs_culture_density = (city_data['loisirs_culture_count'] / population) * 1000
                        education_formation_density = (city_data['education_formation_count'] / population) * 1000
                        transports_mobilite_density = (city_data['transports_mobilite_count'] / population) * 1000
                        services_publics_density = (city_data['services_publics_count'] / population) * 1000
                        autres_services_density = (city_data['autres_services_count'] / population) * 1000

                        # Définir des densités maximales par catégorie (par 1000 habitants)
                        max_densities = {
                            'alimentation': 10,  # Réduire les densités maximales pour obtenir des scores plus élevés
                            'commerce': 8,
                            'services_reparations': 5,
                            'sante_bien_etre': 4,
                            'loisirs_culture': 6,
                            'education_formation': 5,
                            'transports_mobilite': 4,
                            'services_publics': 4,
                            'autres_services': 5
                        }

                        # Calcul des scores normalisés en utilisant une échelle logarithmique
                        alimentation_score = normalize_score(alimentation_density, max_densities['alimentation'])
                        commerce_score = normalize_score(commerce_density, max_densities['commerce'])
                        services_reparations_score = normalize_score(services_reparations_density, max_densities['services_reparations'])
                        sante_bien_etre_score = normalize_score(sante_bien_etre_density, max_densities['sante_bien_etre'])
                        loisirs_culture_score = normalize_score(loisirs_culture_density, max_densities['loisirs_culture'])
                        education_formation_score = normalize_score(education_formation_density, max_densities['education_formation'])
                        transports_mobilite_score = normalize_score(transports_mobilite_density, max_densities['transports_mobilite'])
                        services_publics_score = normalize_score(services_publics_density, max_densities['services_publics'])
                        autres_services_score = normalize_score(autres_services_density, max_densities['autres_services'])

                        # Calcul du total_score avec des poids équilibrés
                        total_score = (
                            alimentation_score * 0.15 +
                            commerce_score * 0.15 +
                            services_reparations_score * 0.10 +
                            sante_bien_etre_score * 0.10 +
                            loisirs_culture_score * 0.15 +
                            education_formation_score * 0.15 +
                            transports_mobilite_score * 0.10 +
                            services_publics_score * 0.10 +
                            autres_services_score * 0.10
                        )

                        # Insérer ou mettre à jour les scores
                        cursor.execute("""
                            INSERT INTO data_services_scores (
                                insee_code, city, data_year,
                                alimentation_score, commerce_score, services_reparations_score,
                                sante_bien_etre_score, loisirs_culture_score, education_formation_score,
                                transports_mobilite_score, services_publics_score, autres_services_score,
                                total_score
                            )
                            VALUES (%s, %s, 2025, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                            ON DUPLICATE KEY UPDATE
                                alimentation_score = VALUES(alimentation_score),
                                commerce_score = VALUES(commerce_score),
                                services_reparations_score = VALUES(services_reparations_score),
                                sante_bien_etre_score = VALUES(sante_bien_etre_score),
                                loisirs_culture_score = VALUES(loisirs_culture_score),
                                education_formation_score = VALUES(education_formation_score),
                                transports_mobilite_score = VALUES(transports_mobilite_score),
                                services_publics_score = VALUES(services_publics_score),
                                autres_services_score = VALUES(autres_services_score),
                                total_score = VALUES(total_score);
                        """, (
                            insee_code, city_name,
                            round(alimentation_score, 2), round(commerce_score, 2),
                            round(services_reparations_score, 2), round(sante_bien_etre_score, 2),
                            round(loisirs_culture_score, 2), round(education_formation_score, 2),
                            round(transports_mobilite_score, 2), round(services_publics_score, 2),
                            round(autres_services_score, 2), round(total_score, 2)
                        ))
                connection.commit()
                print(f"Lot traité : {offset + 1} à {offset + len(cities)}")
                offset += batch_size
            print("Tous les lots ont été traités avec succès.")
    except Error as e:
        connection.rollback()
        print(f"Erreur lors de la mise à jour des scores : {e}")

def main():
    connection = create_connection()
    if not connection:
        return
    try:
        update_scores_by_batch(connection, batch_size=100)
    finally:
        connection.close()

if __name__ == "__main__":
    main()