-- Tabela de controle de NSU (UMA POR CNPJ)
CREATE TABLE controle_nsu (
    cnpj VARCHAR(14) PRIMARY KEY,
    ult_nsu VARCHAR(20) NOT NULL DEFAULT '0',
    max_nsu_conhecido VARCHAR(20),
    ultima_consulta TIMESTAMP,
    em_consulta BOOLEAN DEFAULT FALSE,
    erros_consecutivos INT DEFAULT 0,
    bloqueado_ate TIMESTAMP
);

-- Tabela principal de documentos fiscais
CREATE TABLE documentos_fiscais (
    id BIGSERIAL PRIMARY KEY,
    cnpj_destinatario VARCHAR(14) NOT NULL,
    nsu VARCHAR(20) NOT NULL,
    chave_acesso VARCHAR(44),
    tipo_documento VARCHAR(10),
    schema_xml VARCHAR(20),
    xml_documento TEXT NOT NULL,
    json_dados JSONB,
    data_emissao DATE,
    data_recebimento TIMESTAMP DEFAULT NOW(),
    cnpj_emitente VARCHAR(14),
    razao_social_emitente VARCHAR(200),
    valor_total NUMERIC(15,2),
    UNIQUE(cnpj_destinatario, nsu)
);

-- Índices para performance
CREATE INDEX idx_docs_cnpj_data ON documentos_fiscais(cnpj_destinatario, data_recebimento);
CREATE INDEX idx_docs_chave ON documentos_fiscais(chave_acesso);
CREATE INDEX idx_docs_tipo ON documentos_fiscais(tipo_documento);
CREATE INDEX idx_docs_emitente ON documentos_fiscais(cnpj_emitente);

-- Tabela de log para auditoria
CREATE TABLE log_consultas (
    id BIGSERIAL PRIMARY KEY,
    cnpj VARCHAR(14) NOT NULL,
    data_consulta TIMESTAMP DEFAULT NOW(),
    ult_nsu_consultado VARCHAR(20),
    max_nsu_retornado VARCHAR(20),
    documentos_recebidos INT DEFAULT 0,
    tempo_resposta_ms INT,
    erro TEXT,
    tentativas INT DEFAULT 1
);

função consultar_distribuicao_dfe(cnpj):
    // 1. Obter lock para evitar concorrência
    lock = adquirir_lock_banco("consulta_dfe_" + cnpj, timeout=60s)
    se lock falhou:
        retornar erro("CNPJ em consulta, aguarde")
    
    tentar:
        // 2. Recuperar último NSU processado
        ult_nsu = banco.consultar_valor(
            "SELECT ult_nsu FROM controle_nsu WHERE cnpj = ?", cnpj
        )
        se não encontrou:
            inserir em controle_nsu valores (cnpj, ult_nsu='0')
            ult_nsu = '0'
        
        // 3. Verificar bloqueio por erros
        se bloqueado_ate > agora():
            retornar erro("Consulta bloqueada temporariamente")
        
        // 4. Loop de consulta até esgotar documentos
        total_processado = 0
        repita:
            // 4.1 Montar XML de requisição
            xml = montar_xml_consulta(cnpj, ult_nsu)
            
            // 4.2 Assinar com certificado
            xml_assinado = assinar_xml(xml, certificado)
            
            // 4.3 Enviar para SEFAZ via SOAP
            resposta = enviar_soap(
                url=url_sefaz_uf(cnpj),
                soap_action="NFeDistribuicaoDFe",
                xml=xml_assinado,
                timeout=60
            )
            
            // 4.4 Validar resposta
            se resposta.erro:
                incrementar erros_consecutivos
                se erros_consecutivos >= 3:
                    bloquear_por(horas=2)
                retornar erro(resposta.mensagem_erro)
            
            // 4.5 Extrair dados
            max_nsu = resposta.extrair_campo("maxNSU")
            documentos = resposta.extrair_documentos()
            
            // 4.6 Processar documentos em transação
            iniciar_transação():
                para cada doc em documentos:
                    // Evitar duplicados
                    se não existe_documento(cnpj, doc.nsu):
                        dados = parse_xml(doc.xml)
                        inserir em documentos_fiscais(
                            cnpj, doc.nsu, dados.chave,
                            dados.tipo, dados.schema,
                            doc.xml, dados.json, ...
                        )
                        total_processado++
                
                // 4.7 Atualizar controle com último NSU processado
                se documentos não está vazio:
                    ultimo_nsu = documentos[último].nsu
                    atualizar controle_nsu 
                    SET ult_nsu = ultimo_nsu,
                        max_nsu_conhecido = max_nsu,
                        erros_consecutivos = 0
                    WHERE cnpj = cnpj
            fim_transação()
            
            // 4.8 Registrar log
            inserir em log_consultas(cnpj, ult_nsu, max_nsu, len(documentos))
            
            // 4.9 Verificar se há mais documentos
            se len(documentos) == 50:
                ult_nsu = ultimo_nsu  // Continua do último recebido
            senão:
                sair do loop  // Todos os documentos foram processados
        
        retornar sucesso(total_processado)
    
    capturar qualquer erro:
        registrar_log_erro(cnpj, erro)
        incrementar erros_consecutivos
        retornar erro(erro.mensagem)
    
    finalmente:
        liberar_lock(cnpj)
fim_função