import pymssql
from lib.criptografia_senha import descriptografar
from pathlib import Path
from lib.echo import echo

class CassiniLib():
    def __init__(self, dadosBanco, endereco_sp_whoisactive_sql) -> None:
        self.dadosBanco = dadosBanco
        self.endereco_sp_whoisactive_sql = endereco_sp_whoisactive_sql

    def obter_conexao(self):

        try:
            conn = pymssql.connect(
                self.dadosBanco['host'], 
                self.dadosBanco['usuario'], 
                self.dadosBanco['senha'],
                self.dadosBanco['nomeBanco']
            )
        except Exception as e:
            echo ("Falha ao realizar o login no banco {banco}, host {host}, usuario {usuario}".format(
                host = self.dadosBanco['host'], 
                usuario = self.dadosBanco['usuario'], 
                banco = self.dadosBanco['nomeBanco']
            ))

            raise e

        return conn

   

    def whoisactive_existe(self):

        try:
            with self.obter_conexao() as conn:
                with conn.cursor() as cursor:
                    cursor.execute("select object_id('sp_whoisactive')")
                    row = cursor.fetchone() 
                    
                    return row != None and row[0] != None
        except Exception as e:
            echo ("Falha ao verificar se sp_whoisactive existe no banco {banco}. Verifique as permissoes do usuario".format(
                banco = self.dadosBanco['nomeBanco']
            ))

            raise e
            
    def criar_sp_whoisactive(self):
        if not Path(self.endereco_sp_whoisactive_sql).exists():
            raise Exception(f"Arquivo {self.endereco_sp_whoisactive_sql} não existe")
        
        try:
            with self.obter_conexao() as conn:
                with conn.cursor() as cursor:

                    cursor.execute("""
                        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_WhoIsActive')
                            EXEC ('CREATE PROC dbo.sp_WhoIsActive AS SELECT ''stub version, to be replaced''')
                    """)

                    with open(self.endereco_sp_whoisactive_sql, 'r', encoding='utf8') as arquivo:
                        sql = arquivo.read()

                        cursor.execute(sql)
                        conn.commit()
        except Exception as e:
            echo ("Falha ao criar sp_whoisactive no banco {banco}. O usuario {usuario} deve ter permissao para criar procedures. Ou entao crie manualmente a procedure a partir do arquivo C:\microuniversoWeb\scriptsImplantacao\cassini\sp_WhoIsActive-original.sql".format(
                banco = self.dadosBanco['nomeBanco'],
                usuario = self.dadosBanco['usuario']
            ))

            raise e
        
    def executar_whoisactive(self):
        try:
            with self.obter_conexao() as conn:
                with conn.cursor() as cursor:
                    cursor.execute("exec sp_whoisactive")
                    cursor.fetchone() 
                    
                    
                
        except Exception as e:
            echo ("Falha ao executar sp_whoisactive no banco {banco}. Verifique as permissoes do usuario. O usuario deve ter as permissoes: EXECUTE, VIEW SERVER PERFORMANCE STATE".format(
                banco = self.dadosBanco['nomeBanco']
            ))

            raise e
