# Utilidades para conexión a BD - Reutilizado y ajustado de database.py v1
import pyodbc
from App.Config.GeneralConfig import ConfigDatabase  # Ajusta si GeneralConfig tiene otra estructura

class DatabaseManager:
    def __init__(self, database, databaseID):
        self.db_database = database
        self.db_user = ConfigDatabase.db_user
        self.db_password = ConfigDatabase.db_password
        self.db_host = ConfigDatabase.db_host
        self.db_databaseID = databaseID

    def _connect(self):
        try:
            self.conn_str = (
                'DRIVER={ODBC Driver 18 for SQL Server};'
                f'SERVER={self.db_host};'
                f'DATABASE={self.db_database};'
                f'UID={self.db_user};'
                f'PWD={self.db_password};'
                f'Encrypt=yes;'
                f'TrustServerCertificate=yes;'
            )
            #print(self.conn_str)
            self.connection = pyodbc.connect(self.conn_str)
        except pyodbc.Error as e:
            print(f"Error al conectar a la base de datos: {e}")
            self.connection = None

    def _disconnect(self):
        if self.connection:
            self.connection.close()

        # Función para ejecutar queries que retornan valores
    def get_execute_query(self, query):
        self._connect()
        if not self.connection:
            return None, "Error de conexión"

        
        self.cursor = self.connection.cursor()
        self.cursor.execute(query)
        columns = [col[0] for col in self.cursor.description]
        data = self.cursor.fetchall()
        results = [dict(zip(columns, row)) for row in data]
        self.cursor.close()
        self._disconnect()
        return results


    # Función para ejecutar queries que retornan valores
    def get_execute_query2(self, query):
        self._connect()
        if not self.connection:
            return None, "Error de conexión"

        try:
            self.cursor = self.connection.cursor()
            self.cursor.execute(query)
            columns = [col[0] for col in self.cursor.description]
            data = self.cursor.fetchall()
            results = [dict(zip(columns, row)) for row in data]
            self.cursor.close()
            return results, None
        except Exception as e:
            print(f"Error al ejecutar la consulta SELECT: {e} \n {query}")
            return None, str(e)
        finally:
            self._disconnect()

    # Función para ejecutar queries que no retornan valores
    def set_execute_query(self, query):
        self._connect()
        if not self.connection:
            return False

        try:
            cursor = self.connection.cursor()
            cursor.execute(query)
            self.connection.commit()
            cursor.close()
            return True
        except Exception as e:
            print(f"Error al ejecutar la consulta de modificación: {e}")
            return False
        finally:
            self._disconnect()

    def log_execution(self, procedure_id, procedure_name, status, message, executed_by, extra_info, target_date, date,
                      procedure_type='PYTHON_APP', source='PYTHON_TRANSFERDATA_APP'):
        self._connect()
        if not self.connection:
            return
        self.cursor = self.connection.cursor()

        sql = """
        DECLARE @ExecID VARCHAR(50), @FieldID VARCHAR(50);
        EXEC dbo.InsertExecutionLog
            @ProcedureID = ?,
            @ProcedureName = ?,
            @ProcedureType = ?,
            @Date = ?,
            @TargetDate = ?,
            @Status = ?,
            @ExecutedBy = ?,
            @MessageDescription = ?,
            @Source = ?,
            @ExtraInfo = ?,
            @ExecutionIDGenerated = @ExecID OUTPUT,
            @FieldIDGenerated = @FieldID OUTPUT;
        SELECT @ExecID AS ExecutionID, @FieldID AS FieldID;
        """

        try:
            self.cursor.execute(sql, (
                procedure_id, procedure_name, procedure_type,
                date, target_date, status,
                executed_by, message, source, extra_info
            ))
            row = self.cursor.fetchone()
            self.connection.commit()
        except Exception as e:
            print(f"Error en log_execution: {e}")
        finally:
            self._disconnect()




# Función simple para compatibilidad (usa DatabaseManager internamente)
def get_db_connection(database='tu_db', databaseID='default'):
    manager = DatabaseManager(database, databaseID)
    manager._connect()
    return manager.connection  # Devuelve la conexión cruda para pandas, etc.