database.py

# ============================================================
# database.py - Data Access Layer pour MariaDB
# ============================================================

import pymysql
from settings import DB_CONFIG

# ─────────────────────────────────────────
# CONNEXION MariaDB
# ─────────────────────────────────────────

def get_connection():
    try:
        conn = pymysql.connect(
            host        = DB_CONFIG['host'],
            port        = DB_CONFIG['port'],
            user        = DB_CONFIG['user'],
            password    = DB_CONFIG['password'],
            database    = DB_CONFIG['database'],
        )
        return conn
    except pymysql.Error as e:
        print(f"[DB Error] Connexion impossible : {e}")
        return None


connection = get_connection()


def check_connection():
    global connection
    try:
        connection.ping(reconnect=True)
    except Exception:
        connection = get_connection()


# ─────────────────────────────────────────
# JOUEURS
# ─────────────────────────────────────────

def create_player(username, email=''):
    check_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                'INSERT INTO t_player (username, email) VALUES (%s, %s)',
                (username, email)
            )
            connection.commit()
            print(f"[DB] Joueur '{username}' cree (id={cursor.lastrowid})")
            return cursor.lastrowid
    except pymysql.IntegrityError:
        print(f"[DB] Joueur '{username}' existe deja.")
        return None
    except pymysql.Error as e:
        print(f"[DB Error] create_player: {e}")
        connection.rollback()
        return None


def get_player_by_name(username):
    check_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                'SELECT * FROM t_player WHERE username = %s',
                (username,)
            )
            result = cursor.fetchone()
            print(f"[DB] get_player_by_name('{username}') → {result}")
            return result
    except pymysql.Error as e:
        print(f"[DB Error] get_player_by_name: {e}")
        return None


def get_all_players():
    check_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                'SELECT * FROM t_player ORDER BY score_total DESC'
            )
            return cursor.fetchall()
    except pymysql.Error as e:
        print(f"[DB Error] get_all_players: {e}")
        return []


def update_player_score(player_id, points):
    check_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                'UPDATE t_player SET score_total = score_total + %s WHERE id = %s',
                (points, player_id)
            )
            connection.commit()
            print(f"[DB] Score joueur {player_id} += {points}")
    except pymysql.Error as e:
        print(f"[DB Error] update_player_score: {e}")
        connection.rollback()


# ─────────────────────────────────────────
# PARTIES
# ─────────────────────────────────────────

def save_game(player1_id, player2_id, winner_id,
              game_mode, difficulty, result, duration):
    check_connection()

    print("─" * 40)
    print(f"[DB] save_game() appelé")
    print(f"     player1_id = {player1_id}")
    print(f"     player2_id = {player2_id}")
    print(f"     winner_id  = {winner_id}")
    print(f"     game_mode  = {game_mode}")
    print(f"     difficulty = {difficulty}")
    print(f"     result     = {result}")
    print(f"     duration   = {duration}")
    print("─" * 40)

    try:
        with connection.cursor() as cursor:
            cursor.execute(
                '''INSERT INTO t_game
                   (player1_id, player2_id, winner_id,
                    game_mode, difficulty, result, duration)
                   VALUES (%s, %s, %s, %s, %s, %s, %s)''',
                (player1_id, player2_id, winner_id,
                 game_mode, difficulty, result, duration)
            )
            connection.commit()
            print(f"[DB] Partie sauvegardee ! ID={cursor.lastrowid}")
            return cursor.lastrowid
    except pymysql.Error as e:
        print(f"[DB Error] save_game: {e}")
        connection.rollback()
        return None


def get_games_by_player(player_id):
    check_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                '''SELECT g.id,
                          p1.username,
                          p2.username,
                          g.game_mode,
                          g.difficulty,
                          g.result,
                          g.duration,
                          g.played_at
                   FROM t_game g
                   JOIN t_player p1 ON g.player1_id = p1.id
                   JOIN t_player p2 ON g.player2_id = p2.id
                   WHERE g.player1_id = %s
                      OR g.player2_id = %s
                   ORDER BY g.played_at DESC''',
                (player_id, player_id)
            )
            results = cursor.fetchall()
            print(f"[DB] get_games_by_player({player_id}) → {len(results)} partie(s)")
            return results
    except pymysql.Error as e:
        print(f"[DB Error] get_games_by_player: {e}")
        return []


# ─────────────────────────────────────────
# STATISTIQUES
# ─────────────────────────────────────────

def get_stats_by_player(player_id):
    check_connection()
    try:
        with connection.cursor() as cursor:

            cursor.execute(
                '''SELECT COUNT(*) FROM t_game
                   WHERE player1_id = %s OR player2_id = %s''',
                (player_id, player_id)
            )
            total = cursor.fetchone()[0]

            cursor.execute(
                'SELECT COUNT(*) FROM t_game WHERE winner_id = %s',
                (player_id,)
            )
            wins = cursor.fetchone()[0]

            cursor.execute(
                '''SELECT COUNT(*) FROM t_game
                   WHERE (player1_id = %s OR player2_id = %s)
                   AND result = "nul"''',
                (player_id, player_id)
            )
            draws  = cursor.fetchone()[0]
            losses = total - wins - draws

            win_rate = round(wins / total * 100, 1) if total > 0 else 0

            cursor.execute(
                '''SELECT AVG(duration) FROM t_game
                   WHERE player1_id = %s OR player2_id = %s''',
                (player_id, player_id)
            )
            avg_dur = cursor.fetchone()[0] or 0

            return {
                'total'       : total,
                'wins'        : wins,
                'losses'      : losses,
                'draws'       : draws,
                'win_rate'    : win_rate,
                'avg_duration': round(float(avg_dur), 1)
            }

    except pymysql.Error as e:
        print(f"[DB Error] get_stats_by_player: {e}")
        return {
            'total': 0, 'wins': 0, 'losses': 0,
            'draws': 0, 'win_rate': 0, 'avg_duration': 0
        }


def get_ranking():
    check_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                '''SELECT username, score_total
                   FROM t_player
                   ORDER BY score_total DESC'''
            )
            return cursor.fetchall()
    except pymysql.Error as e:
        print(f"[DB Error] get_ranking: {e}")
        return []


def get_difficulty_distribution():
    check_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(
                '''SELECT difficulty, COUNT(*) AS nb
                   FROM t_game
                   WHERE game_mode = "JcIA"
                   GROUP BY difficulty'''
            )
            return cursor.fetchall()
    except pymysql.Error as e:
        print(f"[DB Error] get_difficulty_distribution: {e}")
        return []


# ─────────────────────────────────────────
# TEST
# ─────────────────────────────────────────

if __name__ == '__main__':
    print("Test connexion MariaDB...")
    if connection:
        print("[OK] Connecte !")
        print(f"     Host     : {DB_CONFIG['host']}")
        print(f"     Database : {DB_CONFIG['database']}")
        print()
        players = get_all_players()
        print(f"Joueurs ({len(players)}) :")
        for p in players:
            print(f"  - {p[1]} | score={p[4]}")
        print()
        print(f"Parties dans t_game :")
        games = get_games_by_player(1)
        for g in games:
            print(f"  {g}")
    else:
        print("[ERREUR] Connexion echouee !")