Liga-System/data/data_api.py

import random
from wood import logger
from pymysql.cursors import DictCursor
from data.database import db_connection


# -----------------------------------------------------
# User / Session
# -----------------------------------------------------

def validate_user_session(db_id, discord_id):
    """Checks if the cookie still matches the current database."""
    connection = db_connection()
    cursor = connection.cursor()

    cursor.execute("SELECT discord_id FROM players WHERE id = %s", (db_id,))
    result = cursor.fetchone()
    cursor.close()
    connection.close()

    if result is None:
        logger.log(f"Player not found in database. Discord:{discord_id}")
        return False

    if str(result[0]) != str(discord_id):
        logger.log(f"Player with false cookies logged in! {discord_id} does not belong to {db_id}")
        return False

    return True


def change_display_name(player_id, new_name):
    connection = db_connection()
    cursor = connection.cursor()

    cursor.execute("UPDATE players SET display_name = %s WHERE id = %s", (new_name, player_id))

    connection.commit()
    cursor.close()
    connection.close()


def generate_silly_name():
    adjectives = ["Confused", "Blind", "Howling", "Angry", "Chaos",
                  "Desperate", "Shouting", "Stumbling", "Sweating"]
    nouns = ["Grot", "Cultist", "Servitor", "Snotling", "Guardmen",
             "DiceLover", "RuleBreaker", "MetaChaser", "SlimeSniffer"]
    return f"{random.choice(adjectives)} {random.choice(nouns)}"


def get_or_create_player(discord_id, discord_name, avatar_url):
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    cursor.execute(
        "SELECT id, discord_name, display_name, discord_avatar_url FROM players WHERE discord_id = %s",
        (discord_id,)
    )
    player = cursor.fetchone()

    if player is None:
        silly_name = generate_silly_name()
        cursor.execute(
            "INSERT INTO players (discord_id, discord_name, display_name, discord_avatar_url) "
            "VALUES (%s, %s, %s, %s)",
            (discord_id, discord_name, silly_name, avatar_url)
        )
        connection.commit()
        logger.log(f"new player added. Discord:{discord_name}")

        cursor.execute(
            "SELECT id, discord_name, display_name, discord_avatar_url FROM players WHERE discord_id = %s",
            (discord_id,)
        )
        player = cursor.fetchone()
    else:
        cursor.execute(
            "UPDATE players SET discord_name = %s, discord_avatar_url = %s WHERE discord_id = %s",
            (discord_name, avatar_url, discord_id)
        )
        connection.commit()

        cursor.execute(
            "SELECT id, discord_name, display_name, discord_avatar_url FROM players WHERE discord_id = %s",
            (discord_id,)
        )
        player = cursor.fetchone()

    cursor.close()
    connection.close()
    return player


# -----------------------------------------------------
# Player Lists
# -----------------------------------------------------

def get_all_players():
    """All players – basic data."""
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    cursor.execute(
        "SELECT id, display_name, discord_name, discord_avatar_url FROM players "
        "ORDER BY display_name ASC"
    )
    rows = cursor.fetchall()

    cursor.close()
    connection.close()
    return rows


def get_all_players_from_system(system_name):
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
        SELECT DISTINCT 
            p.id AS player_id, 
            p.display_name, 
            p.discord_name
        FROM players p
        JOIN player_game_statistic stat ON p.id = stat.player_id
        JOIN gamesystems sys ON stat.gamesystem_id = sys.id
        WHERE sys.name = %s
        ORDER BY p.display_name ASC
    """
    cursor.execute(query, (system_name,))
    rows = cursor.fetchall()

    cursor.close()
    connection.close()
    return rows


def get_gamesystem_id_by_name(system_name):
    connection = db_connection()
    cursor = connection.cursor()

    cursor.execute("SELECT id FROM gamesystems WHERE name = %s", (system_name,))
    row = cursor.fetchone()

    cursor.close()
    connection.close()

    return row[0] if row else None


# -----------------------------------------------------
# Player Statistics
# -----------------------------------------------------

def get_player_statistics(player_id):
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
        SELECT 
            sys.id AS gamesystem_id,
            sys.name AS gamesystem_name,
            sys.*, 
            stat.mmr,
            stat.games_in_system,
            stat.points,
            stat.avv_points,
            stat.last_played,
            stat.win_rate,
            stat.trend,
            stat.tyrann_id,
            stat.pushover_id,
            stat.nemesis_id
        FROM gamesystems sys
        LEFT JOIN player_game_statistic stat 
            ON sys.id = stat.gamesystem_id AND stat.player_id = %s
    """
    cursor.execute(query, (player_id,))
    rows = cursor.fetchall()

    cursor.close()
    connection.close()
    return rows


def get_player_statistic(player_id, system_id, season_id=None):
    """
    - If season_id is provided: Data for this season.
    - If season_id is None: Aggregated total statistics.
    """
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    if season_id:
        query = """
            SELECT 
                sys.name AS gamesystem_name,
                stat.mmr, stat.games_in_system, stat.points, 
                stat.avv_points, stat.last_played, stat.win_rate,
                stat.trend, stat.tyrann_id, stat.pushover_id, stat.nemesis_id,
                (
                    SELECT COUNT(*) + 1
                    FROM player_game_statistic rank_stat
                    WHERE rank_stat.gamesystem_id = %s
                      AND rank_stat.season_id = %s
                      AND rank_stat.games_in_system > 0
                      AND rank_stat.mmr > COALESCE(stat.mmr, 0)
                ) AS rank
            FROM gamesystems sys
            LEFT JOIN player_game_statistic stat 
                ON sys.id = stat.gamesystem_id 
                AND stat.player_id = %s 
                AND stat.season_id = %s
            WHERE sys.id = %s
        """
        cursor.execute(query, (system_id, season_id, player_id, season_id, system_id))
    else:
        query = """
            SELECT 
                sys.name AS gamesystem_name,
                SUM(stat.mmr) / COUNT(stat.season_id) AS mmr, -- Average MMR over seasons
                SUM(stat.games_in_system) AS games_in_system,
                SUM(stat.points) AS points,
                SUM(stat.points) / SUM(stat.games_in_system) AS avv_points,
                MAX(stat.last_played) AS last_played,
                NULL AS rank
            FROM gamesystems sys
            LEFT JOIN player_game_statistic stat 
                ON sys.id = stat.gamesystem_id 
                AND stat.player_id = %s
            WHERE sys.id = %s
            GROUP BY sys.id
        """
        cursor.execute(query, (player_id, system_id))

    row = cursor.fetchone()
    cursor.close()
    connection.close()

    # Sicherstellen dass rank nie None ist wenn season_id gesetzt
    if row and season_id and row.get('rank') is None:
        row['rank'] = 1

    return row


def get_seasons_by_system(system_id):

    """Retrieves all seasons of a game system, sorted by number descending."""
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
        SELECT id, name, season_number 
        FROM seasons 
        WHERE gamesystem_id = %s 
        ORDER BY season_number DESC
    """

    cursor.execute(query, (system_id,))
    seasons = cursor.fetchall()

    cursor.close()
    connection.close()
    return seasons


def get_player_rank(player_id, system_id, season_id):
    """Retrieves the rank of a player in a system for a specific season."""
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
        SELECT COUNT(*) + 1 AS rank
        FROM player_game_statistic stat
        WHERE stat.gamesystem_id = %s
          AND stat.season_id = %s
          AND stat.games_in_system > 0
          AND stat.mmr > (
              SELECT stat2.mmr
              FROM player_game_statistic stat2
              WHERE stat2.player_id = %s
                AND stat2.gamesystem_id = %s
                AND stat2.season_id = %s
          )
    """
    # Parameters: gamesystem, season, player, gamesystem, season
    cursor.execute(query, (system_id, season_id, player_id, system_id, season_id))
    row = cursor.fetchone()

    cursor.close()
    connection.close()

    return row['rank'] if row and row['rank'] is not None else 1



def join_league(player_id, gamesystem_id):
    try:
        # 1. Get the correct season for this system
        season_id = get_current_season_id(gamesystem_id)

        connection = db_connection()
        cursor = connection.cursor()

        query = """
            INSERT INTO player_game_statistic 
            (player_id, gamesystem_id, season_id) 
            VALUES (%s, %s, %s)
        """
        cursor.execute(query, (player_id, gamesystem_id, season_id))
        connection.commit()

        logger.log(f"Success: Player {player_id} joined System {gamesystem_id} in Season {season_id}")

    except Exception as e:
        logger.log(f"ERROR joining league: {e}")
        raise
    finally:
        # Here is a small check if the connection actually exists
        if 'connection' in locals() and connection:
            cursor.close()
            connection.close()



def get_current_season_id(system_id):
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
        SELECT id FROM seasons 
        WHERE gamesystem_id = %s 
        ORDER BY start_date DESC 
        LIMIT 1
    """
    cursor.execute(query, (system_id,))
    result = cursor.fetchone()

    cursor.close()
    connection.close()

    if result:
        return result['id']
    else:
        # Here we know exactly which game system has no season
        raise RuntimeError(f"No season found for {gamesystem_id}!")


# -----------------------------------------------------
# Matches: Reading
# -----------------------------------------------------

def get_recent_matches_for_player(player_id):
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
        SELECT 
            m.id AS match_id,
            sys.name AS gamesystem_name,
            m.player1_id,
            p1.display_name AS p1_display,
            p1.discord_name AS p1_discord,
            m.score_player1,
            m.player2_id,
            p2.display_name AS p2_display,
            p2.discord_name AS p2_discord,
            m.score_player2,
            m.played_at
        FROM matches m
        JOIN gamesystems sys ON m.gamesystem_id = sys.id
        JOIN players p1 ON m.player1_id = p1.id
        JOIN players p2 ON m.player2_id = p2.id
        WHERE m.player1_id = %s OR m.player2_id = %s
        ORDER BY m.played_at DESC
        LIMIT 10
    """
    cursor.execute(query, (player_id, player_id))
    rows = cursor.fetchall()

    cursor.close()
    connection.close()
    return rows


def add_new_match(system_name, player1_id, player2_id, score_p1, score_p2):
    connection = None
    try:
        connection = db_connection()
        cursor = connection.cursor()

        cursor.execute("SELECT id FROM gamesystems WHERE name = %s", (system_name,))
        sys_row = cursor.fetchone()

        if not sys_row:
            raise ValueError(f"Game system '{system_name}' not found.")

        sys_id = sys_row[0]

        query = """
            INSERT INTO matches (gamesystem_id, player1_id, player2_id, score_player1, score_player2)
            VALUES (%s, %s, %s, %s, %s)
        """
        cursor.execute(query, (sys_id, player1_id, player2_id, score_p1, score_p2))
        new_match_id = cursor.lastrowid

        logger.log(
            f"{get_player_name(player1_id)}:({score_p1}) posted Match. "
            f"System: {system_name}, {get_player_name(player2_id)}:({score_p2})"
        )

        connection.commit()
        return new_match_id

    except Exception as e:
        if connection:
            connection.rollback()
        raise  # Propagate the exception

    finally:
        if connection:
            cursor.close()
            connection.close()



def save_calculated_match(calc_results: dict):
    connection = db_connection()
    cursor = connection.cursor()

    try:
        match_id  = calc_results["match_id"]
        winner_id = calc_results["winner_id"]
        looser_id = calc_results["looser_id"]

        cursor.execute(
            "SELECT player1_id, player2_id, gamesystem_id FROM matches WHERE id = %s",
            (match_id,)
        )
        row = cursor.fetchone()
        if not row:
            raise ValueError(f"Match ID {match_id} not found in the database.")

        player1_id, player2_id, gamesystem_id = row

        p1 = calc_results[player1_id]
        p2 = calc_results[player2_id]

        match_query = """
            UPDATE matches
            SET
                player1_base_change  = %s, player1_khorne  = %s, player1_slaanesh  = %s,
                player1_tzeentch     = %s, player1_mmr_change = %s,
                player2_base_change  = %s, player2_khorne  = %s, player2_slaanesh  = %s,
                player2_tzeentch     = %s, player2_mmr_change = %s,
                rust_factor          = %s, elo_factor       = %s, point_inflation  = %s,
                match_is_counted     = 1
            WHERE id = %s
        """
        cursor.execute(match_query, (
            p1["base"], p1["khorne"], p1["slaanesh"], p1["tzeentch"], p1["total"],
            p2["base"], p2["khorne"], p2["slaanesh"], p2["tzeentch"], p2["total"],
            calc_results["rust_factor"], calc_results["elo_factor"], calc_results["point_inflation"],
            match_id
        ))

        cursor.execute(
            "SELECT score_player1, score_player2 FROM matches WHERE id = %s",
            (match_id,)
        )
        score_row = cursor.fetchone()
        score_p1, score_p2 = score_row if score_row else (0, 0)

        stat_query = """
            UPDATE player_game_statistic
            SET
                mmr              = mmr + %s,
                games_in_system  = games_in_system + 1,
                points           = points + %s,
                avv_points       = (points + %s) / (games_in_system + 1),
                last_played      = CURRENT_TIMESTAMP
            WHERE player_id = %s AND gamesystem_id = %s
        """

        cursor.execute(stat_query, (
            p1["total"], score_p1, score_p1, player1_id, gamesystem_id
        ))
        cursor.execute(stat_query, (
            p2["total"], score_p2, score_p2, player2_id, gamesystem_id
        ))

        connection.commit()
        logger.log(f"Match ID:{match_id} calculated.")
        return True

    except Exception as e:
        connection.rollback()
        logger.log(f"CRITICAL ERROR saving the match: {e}")
        return False

    finally:
        cursor.close()
        connection.close()


# -----------------------------------------------------
# Get Data Functions
# -----------------------------------------------------

def get_gamesystem_data(system_id):
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    cursor.execute("SELECT * FROM gamesystems WHERE id = %s", (system_id,))
    row = cursor.fetchone()

    cursor.close()
    connection.close()
    return row


def get_gamesystems_data():
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    cursor.execute("SELECT * FROM gamesystems")
    rows = cursor.fetchall()

    cursor.close()
    connection.close()
    return rows



def get_leaderboard(system_name):
    """Retrieves all players of a system sorted by MMR for the leaderboard (current season only)."""
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
    SELECT p.id, p.display_name, p.discord_name, p.discord_id, p.discord_avatar_url,
           stat.mmr, stat.games_in_system, stat.wins, stat.loss, stat.avv_points
    FROM players p
    JOIN player_game_statistic stat ON p.id = stat.player_id
    JOIN gamesystems sys ON stat.gamesystem_id = sys.id
    JOIN seasons s ON stat.season_id = s.id
    WHERE sys.name = %s
      AND s.end_date IS NULL
      AND stat.games_in_system > 0
    ORDER BY stat.mmr DESC
    """
    cursor.execute(query, (system_name,))
    rows = cursor.fetchall()

    cursor.close()
    connection.close()
    return rows


def get_match_by_id(match_id: int) -> dict | None:
    """Returns all match data including game system name as a dictionary."""
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    try:
        cursor.execute("""
            SELECT
                m.id,
                m.gamesystem_id,
                g.name AS gamesystem_name,
                m.player1_id,
                m.score_player1,
                m.player2_id,
                m.score_player2,
                m.played_at
            FROM matches m
            JOIN gamesystems g ON m.gamesystem_id = g.id
            WHERE m.id = %s
        """, (match_id,))

        return cursor.fetchone()

    except Exception as e:
        logger.log(f"Error loading the match: {e}")
        return None

    finally:
        cursor.close()
        connection.close()


def get_player_name(player_id):
    """Returns the name of a player in the format 'Display Name (Discord Name)'."""
    if player_id is None:
        return "Unknown Player"

    connection = db_connection()
    cursor = connection.cursor()

    cursor.execute("SELECT display_name, discord_name FROM players WHERE id = %s", (player_id,))
    row = cursor.fetchone()

    cursor.close()
    connection.close()

    if row:
        return f"{row[0]} ({row[1]})"
    return "Deleted Player"


def get_system_name_by_id(sys_id):
    if sys_id is None:
        return "Unknown System"

    connection = db_connection()
    cursor = connection.cursor()

    cursor.execute("SELECT name FROM gamesystems WHERE id = %s", (sys_id,))
    row = cursor.fetchone()

    cursor.close()
    connection.close()

    return row[0] if row else "Deleted System"


def get_days_since_last_system_game(player_id, gamesystem_id):
    """Returns how many days the last game in a specific system was."""
    connection = db_connection()
    cursor = connection.cursor()

    # MariaDB: DATEDIFF instead of julianday()
    query = """
        SELECT DATEDIFF(NOW(), last_played) 
        FROM player_game_statistic 
        WHERE player_id = %s AND gamesystem_id = %s
    """
    cursor.execute(query, (player_id, gamesystem_id))
    result = cursor.fetchone()

    cursor.close()
    connection.close()

    if result and result[0] is not None:
        return int(result[0])
    return 0


# -----------------------------------------------------
# Matches Confirming, Deleting, Calculating, ...
# -----------------------------------------------------

def get_open_matches(player_id):
    """Retrieves all open matches (player2_check = 0)."""
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
        SELECT m.id AS match_id,
               m.score_player1, m.score_player2,
               m.played_at,
               sys.name AS system_name,
               p1.display_name AS player1_name,
               p2.display_name AS player2_name,
               CASE WHEN m.player1_id = %s THEN 1 ELSE 0 END AS is_submitter
        FROM matches m
        LEFT JOIN gamesystems sys ON m.gamesystem_id = sys.id
        LEFT JOIN players p1 ON m.player1_id = p1.id
        LEFT JOIN players p2 ON m.player2_id = p2.id
        WHERE m.player2_check = 0
          AND (m.player1_id = %s OR m.player2_id = %s)
    """
    cursor.execute(query, (player_id, player_id, player_id))
    rows = cursor.fetchall()

    cursor.close()
    connection.close()

    submitted = []
    to_confirm = []

    for row in rows:
        if row.pop('is_submitter'):
            submitted.append(row)
        else:
            to_confirm.append(row)

    return {"submitted": submitted, "to_confirm": to_confirm}




def delete_match(match_id, player_id):
    """Deletes a match completely from the database by its ID."""
    connection = db_connection()
    cursor = connection.cursor()

    cursor.execute("DELETE FROM matches WHERE id = %s", (match_id,))
    logger.log(f"Match ID{match_id} deleted from user {get_player_name(player_id)}(ID:{player_id})")
    connection.commit()

    cursor.close()
    connection.close()


def confirm_match(match_id):
    connection = db_connection()
    cursor = connection.cursor()

    cursor.execute("UPDATE matches SET player2_check = 1 WHERE id = %s", (match_id,))
    logger.log(f"Match with ID{match_id} confirmed by Player2.")
    connection.commit()

    cursor.close()
    connection.close()


def set_match_counted(match_id):
    """Sets the flag (1) that the match was successfully incorporated into the MMR."""
    connection = db_connection()
    cursor = connection.cursor()

    cursor.execute("UPDATE matches SET match_is_counted = 1 WHERE id = %s", (match_id,))
    connection.commit()

    cursor.close()
    connection.close()



def get_match_history_log(player_id):
    """Retrieves ALL matches of a player including MMR changes and factors."""
    connection = db_connection()
    cursor = connection.cursor(cursor=DictCursor)

    query = """
        SELECT m.played_at, sys.name AS gamesystem_name,
               m.player1_id, p1.display_name AS p1_display, p1.discord_name AS p1_discord,
               m.score_player1, m.player1_mmr_change,
               m.player1_khorne, m.player1_tzeentch, m.player1_slaanesh, m.player1_base_change,
               m.player2_id, p2.display_name AS p2_display, p2.discord_name AS p2_discord,
               m.score_player2, m.player2_mmr_change,
               m.player2_khorne, m.player2_tzeentch, m.player2_slaanesh, m.player2_base_change,
               m.elo_factor, m.rust_factor,
               m.player2_check, m.match_is_counted
        FROM matches m
        JOIN gamesystems sys ON m.gamesystem_id = sys.id
        JOIN players p1 ON m.player1_id = p1.id
        JOIN players p2 ON m.player2_id = p2.id
        WHERE m.player1_id = %s OR m.player2_id = %s
        ORDER BY m.played_at DESC
    """
    cursor.execute(query, (player_id, player_id))
    rows = cursor.fetchall()

    cursor.close()
    connection.close()
    return rows