database/db_connection.py

"""
Módulo de conexão com o banco de dados SQLite
"""

import sqlite3
import pandas as pd
from pathlib import Path
import sys

# Adicionar diretório pai ao path
sys.path.append(str(Path(__file__).parent.parent))
from config import DB_PATH


class DatabaseConnection:
    """Gerenciador de conexão com o banco de dados."""
    
    def __init__(self, db_path=None):
        self.db_path = db_path or DB_PATH
        self.conn = None
    
    def connect(self):
        """Estabelece conexão com o banco de dados."""
        self.conn = sqlite3.connect(self.db_path, check_same_thread=False)
        return self.conn
    
    def close(self):
        """Fecha a conexão com o banco de dados."""
        if self.conn:
            self.conn.close()
            self.conn = None
    
    def execute_query(self, query, params=None):
        """Executa uma query e retorna os resultados como DataFrame."""
        if not self.conn:
            self.connect()
        
        if params:
            return pd.read_sql_query(query, self.conn, params=params)
        return pd.read_sql_query(query, self.conn)
    
    def execute_raw(self, query, params=None):
        """Executa uma query e retorna os resultados brutos."""
        if not self.conn:
            self.connect()
        
        cursor = self.conn.cursor()
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        return cursor.fetchall()
    
    def __enter__(self):
        self.connect()
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()


# Instância global para uso em toda a aplicação
db = DatabaseConnection()


def get_all_teams():
    """Retorna lista de todos os times."""
    query = "SELECT DISTINCT team_id, team_name FROM teams ORDER BY team_name"
    return db.execute_query(query)


def get_all_maps():
    """Retorna lista de todos os mapas."""
    query = "SELECT DISTINCT map_id, map_name FROM maps ORDER BY map_name"
    return db.execute_query(query)


def get_all_players():
    """Retorna lista de todos os jogadores."""
    query = "SELECT DISTINCT player_steamid, player_name FROM players ORDER BY player_name"
    return db.execute_query(query)


def get_team_players(team_id):
    """Retorna jogadores de um time específico."""
    query = """
        SELECT DISTINCT p.player_steamid, p.player_name
        FROM players p
        JOIN match_players mp ON p.player_steamid = mp.player_steamid
        WHERE mp.team_id = ?
        ORDER BY p.player_name
    """
    return db.execute_query(query, (team_id,))


def get_matches_between_teams(team1_id, team2_id, map_id=None):
    """Retorna partidas entre dois times, opcionalmente filtrado por mapa."""
    if map_id:
        query = """
            SELECT m.*, t1.team_name as team1_name, t2.team_name as team2_name, 
                   maps.map_name, tw.team_name as winner_name
            FROM matches m
            JOIN teams t1 ON m.team1_id = t1.team_id
            JOIN teams t2 ON m.team2_id = t2.team_id
            JOIN maps ON m.map_id = maps.map_id
            LEFT JOIN teams tw ON m.winner_team_id = tw.team_id
            WHERE ((m.team1_id = ? AND m.team2_id = ?) OR (m.team1_id = ? AND m.team2_id = ?))
            AND m.map_id = ?
            ORDER BY m.match_date DESC
        """
        return db.execute_query(query, (team1_id, team2_id, team2_id, team1_id, map_id))
    else:
        query = """
            SELECT m.*, t1.team_name as team1_name, t2.team_name as team2_name, 
                   maps.map_name, tw.team_name as winner_name
            FROM matches m
            JOIN teams t1 ON m.team1_id = t1.team_id
            JOIN teams t2 ON m.team2_id = t2.team_id
            JOIN maps ON m.map_id = maps.map_id
            LEFT JOIN teams tw ON m.winner_team_id = tw.team_id
            WHERE (m.team1_id = ? AND m.team2_id = ?) OR (m.team1_id = ? AND m.team2_id = ?)
            ORDER BY m.match_date DESC
        """
        return db.execute_query(query, (team1_id, team2_id, team2_id, team1_id))


def get_team_matches(team_id, map_id=None, limit=None):
    """Retorna partidas de um time específico."""
    query = """
        SELECT m.*, t1.team_name as team1_name, t2.team_name as team2_name, 
               maps.map_name, tw.team_name as winner_name
        FROM matches m
        JOIN teams t1 ON m.team1_id = t1.team_id
        JOIN teams t2 ON m.team2_id = t2.team_id
        JOIN maps ON m.map_id = maps.map_id
        LEFT JOIN teams tw ON m.winner_team_id = tw.team_id
        WHERE m.team1_id = ? OR m.team2_id = ?
    """
    params = [team_id, team_id]
    
    if map_id:
        query += " AND m.map_id = ?"
        params.append(map_id)
    
    query += " ORDER BY m.match_date DESC"
    
    if limit:
        query += f" LIMIT {limit}"
    
    return db.execute_query(query, tuple(params))


def get_rounds_for_match(match_id):
    """Retorna todas as rodadas de uma partida."""
    query = """
        SELECT r.*, t.team_name as winner_name
        FROM rounds r
        LEFT JOIN teams t ON r.winner_team_id = t.team_id
        WHERE r.match_id = ?
        ORDER BY r.round_number
    """
    return db.execute_query(query, (match_id,))


def get_player_stats_for_match(match_id):
    """Retorna estatísticas dos jogadores em uma partida."""
    query = """
        SELECT mp.*, p.player_name, t.team_name
        FROM match_players mp
        JOIN players p ON mp.player_steamid = p.player_steamid
        JOIN teams t ON mp.team_id = t.team_id
        WHERE mp.match_id = ?
        ORDER BY mp.kills_total DESC
    """
    return db.execute_query(query, (match_id,))


def get_kill_events_for_match(match_id):
    """Retorna eventos de kill de uma partida."""
    query = """
        SELECT ke.*, 
               pa.player_name as attacker_name,
               pv.player_name as victim_name,
               pas.player_name as assister_name
        FROM kill_events ke
        LEFT JOIN players pa ON ke.attacker_steamid = pa.player_steamid
        LEFT JOIN players pv ON ke.victim_steamid = pv.player_steamid
        LEFT JOIN players pas ON ke.assister_steamid = pas.player_steamid
        WHERE ke.match_id = ?
        ORDER BY ke.tick
    """
    return db.execute_query(query, (match_id,))


def get_economy_for_match(match_id):
    """Retorna dados de economia de uma partida."""
    query = """
        SELECT e.*, p.player_name, t.team_name
        FROM economy e
        JOIN players p ON e.player_steamid = p.player_steamid
        JOIN teams t ON e.team_id = t.team_id
        WHERE e.match_id = ?
        ORDER BY e.round_id, e.team_id
    """
    return db.execute_query(query, (match_id,))