"""
Gestion des utilisateurs, notes, matières et UE.
Compatible SQLite et MariaDB via la couche d'abstraction.
"""

from typing import Dict, List, Optional, Tuple

from bdd.database import get_database

##############################
#                            #
#  Gestion des utilisateurs  #
#                            #
##############################


def verifierUtilisateur(login: str, password: str) -> Optional[Dict]:
    """
    Vérifie les identifiants d'un utilisateur.
    Retourne un dict avec les infos de l'utilisateur si OK, None sinon.
    """
    with get_database() as db:
        ph = db.backend.get_placeholder()
        result = db.fetchone(
            f"SELECT id, login, role, nom, prenom FROM Users WHERE login={ph} AND password={ph}",
            (login, password),
        )

        if result:
            return {
                "id": result[0],
                "login": result[1],
                "role": result[2],
                "nom": result[3],
                "prenom": result[4],
            }
        return None


def isLoginAvalaible(login: str) -> bool:
    """Vérifie si un login est disponible"""
    with get_database() as db:
        ph = db.backend.get_placeholder()
        result = db.fetchone(f"SELECT id FROM Users WHERE login={ph}", (login,))
        return result is None


def creerUtilisateur(
    login: str, password: str, role: str = "eleve", nom: str = "", prenom: str = ""
) -> bool:
    """
    Crée un nouvel utilisateur.
    Role: 'prof' ou 'eleve'
    """
    if role not in ["prof", "eleve"]:
        return False

    with get_database() as db:
        ph = db.backend.get_placeholder()
        db.execute(
            f"INSERT INTO Users (login, password, role, nom, prenom) VALUES ({ph}, {ph}, {ph}, {ph}, {ph})",
            (login, password, role, nom, prenom),
        )
        db.commit()
        return True


def getUserByLogin(login: str) -> Optional[Dict]:
    """Récupère les informations d'un utilisateur par son login"""
    with get_database() as db:
        ph = db.backend.get_placeholder()
        result = db.fetchone(
            f"SELECT id, login, role, nom, prenom FROM Users WHERE login={ph}", (login,)
        )

        if result:
            return {
                "id": result[0],
                "login": result[1],
                "role": result[2],
                "nom": result[3],
                "prenom": result[4],
            }
        return None


def getUserById(user_id: int) -> Optional[Dict]:
    """Récupère les informations d'un utilisateur par son ID"""
    with get_database() as db:
        ph = db.backend.get_placeholder()
        result = db.fetchone(
            f"SELECT id, login, role, nom, prenom FROM Users WHERE id={ph}", (user_id,)
        )

        if result:
            return {
                "id": result[0],
                "login": result[1],
                "role": result[2],
                "nom": result[3],
                "prenom": result[4],
            }
        return None


##############################
#                            #
#  Fonctionnalités Élèves    #
#                            #
##############################


def afficherNotes(login: str) -> List[Dict]:
    """
    Affiche toutes les notes d'un élève avec les informations de matière et UE
    """
    user = getUserByLogin(login)
    if not user:
        return []

    with get_database() as db:
        ph = db.backend.get_placeholder()
        results = db.fetchall(
            f"""
            SELECT n.id, n.note, m.nom as matiere, m.code as matiere_code,
                   m.coefficient, ue.nom as ue_nom, ue.code as ue_code, ue.id as ue_id,
                   n.date_creation
            FROM Notes n
            JOIN Matieres m ON n.matiere_id = m.id
            JOIN UE ue ON m.ue_id = ue.id
            WHERE n.eleve_id = {ph}
            ORDER BY ue.code, m.nom
        """,
            (user["id"],),
        )

        notes = []
        for row in results:
            notes.append(
                {
                    "id": row[0],
                    "note": float(row[1]),
                    "matiere": row[2],
                    "matiere_code": row[3],
                    "coefficient": float(row[4]),
                    "ue_nom": row[5],
                    "ue_code": row[6],
                    "ue_id": row[7],
                    "date": row[8],
                }
            )

        return notes


def calculerMoyennesParUE(login: str) -> Dict[str, Dict]:
    """
    Calcule les moyennes par UE pour un élève.
    Retourne un dict: {ue_code: {'nom': ..., 'moyenne': ..., 'nb_notes': ...}}
    """
    notes = afficherNotes(login)

    if not notes:
        return {}

    # Regrouper par UE
    ue_data = {}
    for note in notes:
        ue_code = note["ue_code"]
        if ue_code not in ue_data:
            ue_data[ue_code] = {"nom": note["ue_nom"], "notes": [], "coefficients": []}
        ue_data[ue_code]["notes"].append(note["note"])
        ue_data[ue_code]["coefficients"].append(note["coefficient"])

    # Calculer les moyennes pondérées
    moyennes = {}
    for ue_code, data in ue_data.items():
        notes_list = data["notes"]
        coeffs = data["coefficients"]

        somme_ponderee = sum(n * c for n, c in zip(notes_list, coeffs))
        somme_coeffs = sum(coeffs)

        moyenne = somme_ponderee / somme_coeffs if somme_coeffs > 0 else 0

        moyennes[ue_code] = {
            "nom": data["nom"],
            "moyenne": round(moyenne, 2),
            "nb_notes": len(notes_list),
        }

    return moyennes


##############################
#                            #
#  Fonctionnalités Profs     #
#                            #
##############################


def listerEleves(search: str = "") -> List[Dict]:
    """
    Liste tous les élèves. Si search est fourni, filtre par nom/prénom/login.
    """
    with get_database() as db:
        ph = db.backend.get_placeholder()

        if search:
            search_pattern = f"%{search}%"
            results = db.fetchall(
                f"""
                SELECT id, login, nom, prenom
                FROM Users
                WHERE role = 'eleve'
                  AND (login LIKE {ph} OR nom LIKE {ph} OR prenom LIKE {ph})
                ORDER BY nom, prenom
            """,
                (search_pattern, search_pattern, search_pattern),
            )
        else:
            results = db.fetchall("""
                SELECT id, login, nom, prenom
                FROM Users
                WHERE role = 'eleve'
                ORDER BY nom, prenom
            """)

        eleves = []
        for row in results:
            eleves.append(
                {"id": row[0], "login": row[1], "nom": row[2], "prenom": row[3]}
            )

        return eleves


def listerMatieres(search: str = "") -> List[Dict]:
    """
    Liste toutes les matières avec leur UE. Si search est fourni, filtre par nom/code.
    """
    with get_database() as db:
        ph = db.backend.get_placeholder()

        if search:
            search_pattern = f"%{search}%"
            results = db.fetchall(
                f"""
                SELECT m.id, m.code, m.nom, m.coefficient, ue.id, ue.code, ue.nom
                FROM Matieres m
                JOIN UE ue ON m.ue_id = ue.id
                WHERE m.nom LIKE {ph} OR m.code LIKE {ph}
                ORDER BY ue.code, m.nom
            """,
                (search_pattern, search_pattern),
            )
        else:
            results = db.fetchall("""
                SELECT m.id, m.code, m.nom, m.coefficient, ue.id, ue.code, ue.nom
                FROM Matieres m
                JOIN UE ue ON m.ue_id = ue.id
                ORDER BY ue.code, m.nom
            """)

        matieres = []
        for row in results:
            matieres.append(
                {
                    "id": row[0],
                    "code": row[1],
                    "nom": row[2],
                    "coefficient": float(row[3]),
                    "ue_id": row[4],
                    "ue_code": row[5],
                    "ue_nom": row[6],
                }
            )

        return matieres


def listerUE() -> List[Dict]:
    """Liste toutes les UE"""
    with get_database() as db:
        results = db.fetchall("""
            SELECT id, code, nom, description
            FROM UE
            ORDER BY code
        """)

        ues = []
        for row in results:
            ues.append(
                {"id": row[0], "code": row[1], "nom": row[2], "description": row[3]}
            )

        return ues


def creerEleve(login: str, password: str, nom: str, prenom: str) -> bool:
    """Crée un compte élève (réservé aux profs)"""
    return creerUtilisateur(login, password, "eleve", nom, prenom)


def creerUE(code: str, nom: str, description: str = "") -> bool:
    """Crée une nouvelle UE"""
    with get_database() as db:
        ph = db.backend.get_placeholder()
        try:
            db.execute(
                f"INSERT INTO UE (code, nom, description) VALUES ({ph}, {ph}, {ph})",
                (code, nom, description),
            )
            db.commit()
            return True
        except:
            return False


def creerMatiere(code: str, nom: str, ue_id: int, coefficient: float = 1.0) -> bool:
    """Crée une nouvelle matière"""
    with get_database() as db:
        ph = db.backend.get_placeholder()
        try:
            db.execute(
                f"INSERT INTO Matieres (code, nom, ue_id, coefficient) VALUES ({ph}, {ph}, {ph}, {ph})",
                (code, nom, ue_id, coefficient),
            )
            db.commit()
            return True
        except:
            return False


def ajouterNote(eleve_login: str, matiere_code: str, note: float) -> bool:
    """
    Ajoute une note pour un élève dans une matière.
    Compatible avec l'ancienne version (pour la transition)
    """
    # Récupérer l'ID de l'élève
    eleve = getUserByLogin(eleve_login)
    if not eleve or eleve["role"] != "eleve":
        return False

    # Récupérer l'ID de la matière
    with get_database() as db:
        ph = db.backend.get_placeholder()
        matiere = db.fetchone(
            f"SELECT id FROM Matieres WHERE code = {ph}", (matiere_code,)
        )

        if not matiere:
            return False

        matiere_id = matiere[0]

        # Vérifier que la note est valide
        if note < 0 or note > 20:
            return False

        # Ajouter la note
        db.execute(
            f"INSERT INTO Notes (eleve_id, matiere_id, note) VALUES ({ph}, {ph}, {ph})",
            (eleve["id"], matiere_id, note),
        )
        db.commit()
        return True


def ajouterNoteById(eleve_id: int, matiere_id: int, note: float) -> bool:
    """Ajoute une note pour un élève dans une matière (version avec IDs)"""
    if note < 0 or note > 20:
        return False

    with get_database() as db:
        ph = db.backend.get_placeholder()
        try:
            db.execute(
                f"INSERT INTO Notes (eleve_id, matiere_id, note) VALUES ({ph}, {ph}, {ph})",
                (eleve_id, matiere_id, note),
            )
            db.commit()
            return True
        except:
            return False


def getNotesEleve(eleve_id: int) -> List[Dict]:
    """Récupère toutes les notes d'un élève par ID"""
    with get_database() as db:
        ph = db.backend.get_placeholder()
        results = db.fetchall(
            f"""
            SELECT n.id, n.note, m.nom as matiere, m.code,
                   ue.nom as ue_nom, ue.code as ue_code
            FROM Notes n
            JOIN Matieres m ON n.matiere_id = m.id
            JOIN UE ue ON m.ue_id = ue.id
            WHERE n.eleve_id = {ph}
            ORDER BY ue.code, m.nom
        """,
            (eleve_id,),
        )

        notes = []
        for row in results:
            notes.append(
                {
                    "id": row[0],
                    "note": float(row[1]),
                    "matiere": row[2],
                    "matiere_code": row[3],
                    "ue_nom": row[4],
                    "ue_code": row[5],
                }
            )

        return notes


def supprimerNote(note_id: int) -> bool:
    """Supprime une note"""
    with get_database() as db:
        ph = db.backend.get_placeholder()
        try:
            db.execute(f"DELETE FROM Notes WHERE id = {ph}", (note_id,))
            db.commit()
            return True
        except:
            return False