import mysql.connector
from mysql.connector import Error
import logging
import sys
import os
from dotenv import load_dotenv

# Carregar variáveis de ambiente
load_dotenv()

# Configurar logging
logging.basicConfig(
    level=logging.ERROR,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('db_errors.log'),
        logging.StreamHandler(sys.stdout)
    ]
)

class DatabaseConnection:
    """Classe para gerenciar conexão com MySQL"""
    
    def __init__(self):
        self.host = os.getenv('DB_HOST', 'localhost')
        self.database = os.getenv('DB_NAME', 'batalhao')
        self.user = os.getenv('DB_USER', 'root')
        self.password = os.getenv('DB_PASSWORD', 'joaopaulo262004')
        self.connection = None
        self.cursor = None
    
    def connect(self):
        """Estabelece conexão com o banco de dados"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                database=self.database,
                user=self.user,
                password=self.password,
                charset='utf8mb4',
                collation='utf8mb4_unicode_ci',
                autocommit=False
            )
            
            if self.connection.is_connected():
                self.cursor = self.connection.cursor(dictionary=True)
                return True
                
        except Error as e:
            logging.error(f"Erro ao conectar ao banco de dados: {e}")
            raise Exception("Erro ao conectar ao banco de dados")
        
        return False
    
    def execute_query(self, query, params=None):
        """Executa uma query SELECT e retorna os resultados"""
        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            
            return self.cursor.fetchall()
        
        except Error as e:
            logging.error(f"Erro ao executar query: {e}")
            raise Exception(f"Erro ao executar query: {e}")
    
    def execute_one(self, query, params=None):
        """Executa uma query SELECT e retorna um único resultado"""
        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            
            return self.cursor.fetchone()
        
        except Error as e:
            logging.error(f"Erro ao executar query: {e}")
            raise Exception(f"Erro ao executar query: {e}")
    
    def execute_update(self, query, params=None):
        """Executa uma query INSERT/UPDATE/DELETE"""
        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            
            self.connection.commit()
            return self.cursor.rowcount
        
        except Error as e:
            self.connection.rollback()
            logging.error(f"Erro ao executar update: {e}")
            raise Exception(f"Erro ao executar update: {e}")
    
    def close(self):
        """Fecha a conexão com o banco de dados"""
        if self.cursor:
            self.cursor.close()
        if self.connection and self.connection.is_connected():
            self.connection.close()
    
    def __enter__(self):
        """Permite uso com context manager (with)"""
        self.connect()
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        """Fecha conexão ao sair do context manager"""
        self.close()


def get_db_connection():
    """Função helper para obter uma nova conexão"""
    db = DatabaseConnection()
    db.connect()
    return db