import mysql.connector
from mysql.connector import Error
import re


connection = mysql.connector.connect(
    host="localhost",
    port="8889",
    database="autoconso-api",
    user="root",
    password="root"
)

cursor = connection.cursor(dictionary=True)

# Récupérer toutes les lignes
cursor.execute("SELECT id, name FROM car_engine")
rows = cursor.fetchall()

# Expression régulière mise à jour
pattern = re.compile(
    r'^([\d.]+\s*[^\d(]*)\s+(AT|MT|CVT)\s*(AWD|2WD|4WD)?\s*\((\d+)\s*cv\)', 
    re.IGNORECASE
)

# Parcours des lignes
for row in rows:
    match = pattern.match(row['name'])
    if match:
        engine = match.group(1).strip()                  # Exemple : "1.5 TSI"
        transmission = match.group(2).upper()            # Exemple : "MT"
        power = int(match.group(4))                      # Exemple : 110

        # Mise à jour dans la base
        cursor.execute("""
            UPDATE car_engine
            SET engine = %s, car_transmission = %s, power = %s
            WHERE id = %s
        """, (engine, transmission, power, row['id']))
    else:
        print(f"Format non reconnu : {row['name']} (id={row['id']})")

# Appliquer les changements
connection.commit()

cursor.close()
connection.close()

print("Nettoyage terminé.")