import re
import json

#generic = importlib.import_module('generic')
from generic.db_integracao import db_integracao


class guarani(db_integracao):

    def __init__(self, argv: list):
        self.guarani_request = False
        super(guarani, self, ).__init__(argv)

    def json_to_sql(self, data):
        value = ''
        for item in data:
            value = self.process_sql_item(item)
            # print(item.get('coalesce'))
            #value += f"( SELECT COALESCE({process}), {item.get('coalesce')})" if item.get('coalesce') else process

        return value
        
    def get_envirioment_config(self):
        self.environment_config = {}
        
    def process_sql_item(self, item):
        item_type = item.get('type')

        processed_item = f"'{item.get('to')}'"
        if item_type:
            processed_item = self.call(f"process_sql_item_{item_type}", item)

        if 'coalesce' in item:
            processed_item = f"(SELECT COALESCE({processed_item}, {item.get('coalesce')}))"

        _as = item.get('as', '')
        table = item.get('table', '')
        join = item.get('join', '')
        where = item.get('where', '')
        groupby = item.get('groupby', '')
        having = item.get('having', '')
        orderby = item.get('orderby', '')

        if _as:
            print('')

        processed_item = processed_item.format(
            table=f"FROM {table}" if table else '',   _as=f" AS {_as} " if _as else '',   join=f"{join}",   where=f"{where}",   groupby=f"{groupby}",   having=f"{having}",   orderby=f"{orderby}"
        )

        #    processed_item     =   f"{processed_item} AS {_as}" if _as else ''

        # process =  if item.get('coalesce') else process
        return processed_item

    def process_sql_item_arrayagg(self, item):
        return f"(SELECT JSON_ARRAYAGG({self.json_to_sql(item.get('to'))}) {{_as}} {{table}} {{join}} {{where}} {{groupby}} {{having}} {{orderby}})"

    def process_sql_item_object(self, item):
        dict_to = item.get('to')
        _list = ()
        for to in dict_to:
            _from = to.get('from', None)

            _from = _from if not _from else f"(SELECT COALESCE({_from},  ''))"

            if 'custom_obj' in to:
                _from = self.call(
                    f"process_sql_item_custom_{to.get('custom_obj')}", _from, item)

            _name = to.get('name', None)
            _name = _name if not _name else f"'{_name}'"
            _dado = self.process_sql_item(to)

            _list += (_name, _dado, _from)

        return f"JSON_OBJECT({','.join(filter(lambda a: not(not a), _list))})"

    def process_sql_item_array(self, item):
        return f"(SELECT JSON_ARRAY({self.json_to_sql(item.get('to'))}) {{_as}} {{table}} {{join}} {{where}} {{groupby}} {{having}} {{orderby}})"

    def process_sql_item_custom_clear(self, value, item):
        return f"(SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE({value},'/',''), '-', ''), '.','') ,'(','') , ')', '') ,' ','') )"

    def process_sql_item_custom_ddd(self, value, item):
        return f"(SELECT SUBSTRING({self.process_sql_item_custom_clear(value,item)}  , 1,2) )"

    def process_sql_item_custom_telefone(self, value, item):
        return f"(SELECT SUBSTRING({self.process_sql_item_custom_clear(value,item)}  , 3,10) )"

    def process_sql_item_custom_tipopessoa(self, value, item):
        return f"""(SELECT IF( LENGTH(TRIM({self.process_sql_item_custom_clear(value, item)})) >= 12 ,'J' , 'F' ) AS VLCPFCNPJ )"""

    def process_sql_item_custom_date(self, value, item):
        return f"(SELECT DATE_FORMAT(STR_TO_DATE({value}, '%d/%m/%Y'), '%Y-%m-%d'))"
        
        
        
    def process_sql_item_custom_usuario_softdib(self, value, item):
        return f"""
        (SELECT 
            CONCAT(
                    CAST( CHAR(
                                                                        
                            CONVERT(
                                
                                    (SELECT
                                        LEFT(
                                            {value},
                                            2
                                        )
                                    ), SIGNED INTEGER
                            )
                    ) AS CHAR),
                    SUBSTRING(
                        {value},
                        3,
                        CHAR_LENGTH({value})
                    )
            )
        )"""
            
            
        """CONCAT(REPLACE(
            REPLACE(
                REPLACE(REPLACE( LEFT({value},3), "103", "C"), "118", "R"),
                "121",
                "U"
            ),
            "122",
            "V"), RIGHT({value},5))
        )"""
        """ REPLACE()+RIGHT(MyCol,3) """

    def save_status(self, retorno_cobol):
        super().save_status(retorno_cobol)
        
        #self.current_order
        self.getDb().update('AND_HISTPEDCAB',
            {
                "HPC_IMPORTADO": "S"
            },
            
            [f"HPC_NUMPEDIDOEMP ='{retorno_cobol[-1]['CDPEDIDOEXTERNO']}'"]
        )
        
        

    def get_handler(self):
        data = self.atualWs.get('sql')
        result = self.json_to_sql(data)

        select = f"SELECT *  FROM({result}) TMP"

        try:
            sql_result, = self.select(select)
            pedidos = sql_result.get('json')

            if not pedidos:
                return
            
            pedidos = json.loads(pedidos)

            use_softdib_api = self.atualWs.get('use_softdib_api')

            # or self.atualWs['useSoftdibApi'] == 'true' :
            if use_softdib_api == None or use_softdib_api:
                self.define_sd_request()

            #self.call(self.atualWs['handler'] + "_handler", pedidos)

            for pedido in pedidos:
                self.current_order = pedido
                
                pedido_dados, =  self.current_order.get('pedido','')
                codigo_cliente = pedido_dados.get('CDCLIENTE')
                
                if not self.exists('CDCLIENTE','TBCLIENTE',[f'CDCLIENTE="{codigo_cliente}"']):
                    codigo_cliente = self.salvar_cliente_softdib(pedido_dados)
                    self.current_order['pedido'][0]['CDCLIENTE'] = codigo_cliente
                    
                
                self.salvar_pedido_softdib(self.current_order)

        except Exception as error:
            self.createLog("999", str(error))
            exit()

        # return super().get_handler()
        
        
    def handle_db_data(self,each):
        
        fields = self.atualWs.get('fields')
        
        update_dict = {field.get('para'): each.get(field.get('para'))  
                        for field in fields if field.get('type') == 'field' }
        where = [f"{field.get('para')}={each.get(field.get('para'))}" 
                    for field in fields if  field.get('type') == 'where']
        
        table_update = self.atualWs.get('rote')
        doUpdate, error = self.getDb().update(table_update,update_dict, where )
        if not error:
            func_rote = self.fixed_rote()
            func = f"afterSend_{func_rote}"
            self.call(func, each )
            
        
    def afterSend_AND_HISTPEDCAB(self, data):
        table_origin = self.atualWs.get('table')
        self.getDb().delete(table_origin, [f"IDTBPEDIDOSSTATUS={data.get('IDTBPEDIDOSSTATUS')}"])

    def setup(self, argv):
        
        self.apiName     = argv[1] # Nome da api
        
        self.method      = argv[2] # metodo chamado (GET POST PUT DELETE)
        self.rote        = argv[3] # Rota chamada, se ALL, fazer todos
        self.nmbanco     = argv[4] # Banco de Dados   
        self.nmlkgrupo   = argv[5] # posicionamento de grupo   
        self.cdempresa   = argv[6] # posicionamento de empresa 
        self.cdfilial    = argv[7] # posicionamento de filial  
        self.saida       = argv[8] # Arquivo de saida api
        self.saida_cobol = argv[9] # Arquivo de saida cobol
        self.debug     = True if  argv[-1] == 'debug' else False  
        
        suffix =  '_app_pedidos3' if not self.hasAttr('suffix', self) else self.suffix
        self.database_db     = f"{self.nmbanco.lower()}{suffix}"#.format(self.nmbanco.lower(), suffix)
        
        
        
        #print(sql)
        setup = self.select(
            f"""SELECT * FROM  TBEMPRESA WHERE 1=1
                AND NMLKGRUPO = "{self.nmlkgrupo }"
                AND CDEMPRESA = "{self.cdempresa }"
                AND CDFILIAL  = "{self.cdfilial  }";
            """
        )
        """ .format(
                NMLKGRUPO = self.nmlkgrupo 
            ,   CDEMPRESA = self.cdempresa 
            ,   CDFILIAL  = self.cdfilial   
        ) """
        
        if not setup :
            self.createLog("404","Base de dados sem dados, checar empresa e filial")
            exit()

        self.get_environment_api()
            
        return setup


""" 
    IP 187.32.47.244 // 187.72.211.243
    PORTA 33306
    usuario dashboard
    senha GL0b@L
    
    
    
    
    
            SELECT
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'pedidos',
                        (
                            SELECT
                                COALESCE(
                                    (
                                        SELECT
                                            JSON_ARRAY(
                                                JSON_OBJECT (
                                                    'CDCLIENTE',
                                                    HPC_CODIGOCLIENTE,
                                                    'CDVENDEDOR',
                                                    HPC_REPRESENTANTE,
                                                    'CDCONDPGTO',
                                                    HPC_CODCONDPGTO,
                                                    'CDFRETE',
                                                    HPC_DESCRICAOFRETE,
                                                    'DTPEDIDO',
                                                    HPC_DTPEDIDO,
                                                    'HRPEDIDO',
                                                    HPC_DTPEDIDO,
                                                    'IDTBPEDIDO',
                                                    HPC_SEQUENCIAL,
                                                    'CDSEQPEDIDOEXTERNO',
                                                    HPC_NUMPEDIDOEMP,
                                                    'VLTOTAL',
                                                    HPC_VALORPEDIDO,
                                                    'VLFRETE',
                                                    HPC_FRETEVALOR,
                                                    'VLDESCONTO',
                                                    HPC_VALORDESCONTO,
                                                    'DSOBSPEDIDO',
                                                    HPC_OBS_INTERNA,
                                                    'CDTRANSPORTADOR',
                                                    HPC_TRANSPORTADORA
                                                )
                                            )
                                    ),
                                    JSON_ARRAY()
                                )
                        ),
                        'itenscarrinho',
                        (
                            SELECT
                                COALESCE(
                                    (
                                        SELECT
                                            JSON_ARRAYAGG(
                                                JSON_OBJECT (
                                                    'CDPRODUTO',
                                                    HPI_CODIGOPRODUTO,
                                                    'IDTBPEDIDO',
                                                    HPI_NUMPEDIDOEMP,
                                                    'VLQUANTIDADE',
                                                    HPI_QTDEVENDIDA,
                                                    'VLPRECOFINAL',
                                                    HPI_VALORBRUTO,
                                                    'VLPRECOUNITARIO',
                                                    HPI_VALORTOTAL,
                                                    'VLPERCDESCONTO',
                                                    HPI_VALORDESCONTO
                                                )
                                            )
                                        FROM
                                            AND_HISTPEDITENS
                                        WHERE
                                            HPI_NUMPEDIDOEMP = HPC_NUMPEDIDOEMP
                                    ),
                                    JSON_ARRAY()
                                )
                        )
                    )
                ) as json
            FROM
                AND_HISTPEDCAB
        
"""