import time
import requests
import mysql.connector

def importer_actions():
    API_KEY = "6a6235c8a742b2.89238466"
    MYSQL = {
        "host": "db",
        "port": 3306,
        "user": "root",
        "password": "sysadm-1963",
        "database": "bourse"
    }
    
    EXCHANGES = [
        "PA", "US", "BR", "KLSE", "LS", "XETRA", "LU", "OL", "ST", "CO", "IR", "KO", "AU", "SHE", "SHG", "AT",
        "F", "STU", "MU", "HA", "DU", "HM", "VI", "SW", "TO", "AU", "HK", "TWO", "MC"
    ]

    print("Connexion à la base de données MySQL...")
    try:
        db_conn = mysql.connector.connect(**MYSQL)
        cursor = db_conn.cursor()
        print("Connecté avec succès à la base MySQL : [bourse]")
    except Exception as e:
        print(f"Erreur de connexion MySQL : {e}")
        return

    print("Vidage de la table les_actions...")
    try:
        cursor.execute("TRUNCATE TABLE les_actions;")
        db_conn.commit()
    except Exception as e:
        print(f"Erreur lors du TRUNCATE : {e}")

    sql = """
        INSERT INTO les_actions (isin, ticker, name, exchange, pays, devise)
        VALUES (%s, %s, %s, %s, %s, %s)
    """
    total_global = 0

    try:
        for exchange in EXCHANGES:
            url = f"https://eodhd.com/api/exchange-symbol-list/{exchange}?api_token={API_KEY}&fmt=json"
            print(f"\n--- Traitement de la bourse : {exchange} ---")
            
            req_timeout = 300 if exchange == "US" else 60
            
            try:
                r = requests.get(url, timeout=req_timeout)
                if r.status_code != 200:
                    print(f"[{exchange}] Erreur HTTP {r.status_code}")
                    continue
                data = r.json()
            except Exception as err:
                print(f"[{exchange}] Erreur requête ou timeout : {err}")
                continue

            if not isinstance(data, list):
                print(f"[{exchange}] Le format reçu n'est pas une liste.")
                continue

            print(f"[{exchange}] {len(data)} éléments reçus. Insertion en cours...")
            
            lignes = []
            for item in data:
                isin = item.get("ISIN") or item.get("isin")
                ticker = item.get("Code") or item.get("code")
                name = item.get("Name") or item.get("name")
                exch = item.get("Exchange") or item.get("exchange") or exchange
                pays = item.get("Country") or item.get("country")
                devise = item.get("Currency") or item.get("currency")

                if not ticker:
                    continue

                lignes.append((
                    str(isin)[:12] if isin else None,
                    str(ticker)[:20] if ticker else None,
                    str(name)[:150] if name else None,
                    str(exch)[:50] if exch else None,
                    str(pays)[:50] if pays else None,
                    str(devise)[:10] if devise else None
                ))

            if lignes:
                # Utilisation d'executemany par paquets pour un maximum de performance
                batch_size = 5000
                for i in range(0, len(lignes), batch_size):
                    batch = lignes[i:i + batch_size]
                    cursor.executemany(sql, batch)
                    db_conn.commit()
                
                total_global += len(lignes)
                print(f"-> Succès : {len(lignes)} actions insérées pour {exchange}.")

            time.sleep(1)

        # Vérification finale du nombre de lignes dans la table
        cursor.execute("SELECT COUNT(*) FROM les_actions;")
        count = cursor.fetchone()[0]
        print(f"\nTerminé ! Total global traité : {total_global} lignes. Comptage réel en base : {count} lignes.")

    except Exception as e:
        print(f"Erreur générale : {e}")
    finally:
        cursor.close()
        db_conn.close()
        print("Connexion fermée.")

if __name__ == "__main__":
    importer_actions()