import pymysql
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
import os
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = "mysql+pymysql://root:@127.0.0.1:3306/c1_mesoddplan_db"

def test_connection():
    try:
        engine = create_engine(DATABASE_URL)
        with engine.connect() as connection:
            result = connection.execute(text("SELECT 1"))
            print("Connexion à la base de données réussie!")
            return True
    except Exception as e:
        print(f"Erreur de connexion: {e}")
        return False

def get_table_structure():
    """Afficher la structure de la table activity"""
    try:
        engine = create_engine(DATABASE_URL)
        with engine.connect() as connection:
            tables = connection.execute(text("SHOW TABLES LIKE 'activity'"))
            if not tables.fetchone():
                print("La table 'activity' n'existe pas dans la base de données")
                return False
            
            result = connection.execute(text("DESCRIBE activity"))
            columns = result.fetchall()
            print("-" * 60)
            for column in columns:
                print(f"{column[0]} | Type: {column[1]} | Null: {column[2]} | Key: {column[3]}")
            return True

    except Exception as e:
        print(f"Erreur lors de l'inspection de la table: {e}")
        return False

def get_all_activities():
    """Récupérer et afficher toutes les activités"""
    try:
        engine = create_engine(DATABASE_URL)
        with engine.connect() as connection:
            count_result = connection.execute(text("SELECT COUNT(*) FROM activity"))
            total_count = count_result.fetchone()[0]
            print(f"\nNombre total d'activités: {total_count}")
            if total_count == 0:
                print("Aucune activité trouvée dans la table")
                return
            
            result = connection.execute(text("SELECT * FROM activity LIMIT 10"))
            activities = result.fetchall()
            
            print("\n Activités (10 premières):")
            print("-" * 80)
            
            for activity in activities:
                print(f"ID: {activity[0]}")
                for i, value in enumerate(activity[1:], 1):
                    column_name = f"Column_{i}"
                    print(f"   {column_name}: {value}")
                print("-" * 40)
            
            return activities
            
    except Exception as e:
        print(f"Erreur lors de la récupération des activités: {e}")
        return None

def get_activities_with_sqlalchemy():
    """Utiliser SQLAlchemy ORM pour récupérer les activités"""
    try:
        from app.database import SessionLocal
        from app.models import Activity
        
        db = SessionLocal()
        activities = db.query(Activity).limit(10).all()
        
        print(f"\n Via SQLAlchemy ORM - Nombre d'activités récupérées: {len(activities)}")
        
        for activity in activities:
            print(f"ID: {activity.id}")
            if hasattr(activity, 'name'):
                print(f"   Nom: {activity.name}")
            if hasattr(activity, 'description'):
                print(f"   Description: {activity.description}")
            if hasattr(activity, 'status'):
                print(f"   Status: {activity.status}")
            print("-" * 40)
        
        db.close()
        return activities
        
    except Exception as e:
        print(f"Erreur avec SQLAlchemy ORM: {e}")
        return None

if __name__ == "__main__":
    print("Test de connexion à la base de données c1_mesoddplan_db")
    print("=" * 60)

    if not test_connection():
        exit(1)
    if not get_table_structure():
        exit(1)
    activities = get_all_activities()
    print("\n" + "=" * 60)
    print("Test avec SQLAlchemy ORM:")
    try:
        get_activities_with_sqlalchemy()
    except ImportError:
        print("QLAlchemy ORM non disponible (normal si les modules app ne sont pas dans le PATH)")