from domain.core.mysql_db import get_connection

CITIES = [
    "Wasquehal", "Marcq-en-Barœul", "Lille", "Croix", "Roubaix", "Tourcoing",
    "Villeneuve-d'Ascq", "Lezennes", "Mons-en-Barœul", "Hem", "Saint-André-lez-Lille",
    "Mouvaux", "Lambersart", "Marquette-lez-Lille", "Wambrechies", "Linselles", "Quesnoy-sur-Deûle"
]
PROPERTY_TYPE = "Maison"

placeholders = ", ".join(["%s"] * len(CITIES))

query = f"""
    SELECT
        annee,
        code_postal,
        nb_transactions,
        ROUND(prix_m2_median) AS prix_m2_median,
        ROUND(prix_m2_moyen) AS prix_m2_moyen,
        ROUND(surface_moyenne) AS surface_moy_m2,
        ROUND(prix_median) AS prix_median,
        nom_commune,
        evolution_m2_pct
    FROM prix_evolution
    WHERE nom_commune IN ({placeholders})
      AND type_local = %s
    ORDER BY annee
"""

try:
    conn = get_connection()
    with conn.cursor() as cursor:
        params = (*CITIES, PROPERTY_TYPE)
        cursor.execute(query, params)
        results = cursor.fetchall()
    conn.close()

    if not results:
        print("No data found for the specified criteria.")
    else:
        header = f"{'Year':<6} | {'CP':<6} | {'nb_transactions.':<8} | {'€/m²':<8} | {'Median Price'} | {'nom_commune'} | {'evolution_m2_pct'}"
        print(header)
        print("-" * len(header))
        for row in results:
            print(f"{row['annee']:<6} | {row['code_postal']:<6} | {row['nb_transactions']:<8} | {row['prix_m2_median']:<8} | {row['prix_median']:,.0f}€ | {row['nom_commune']} | {row['evolution_m2_pct']}")

except Exception as e:
    print(f"Database error: {e}")