-- ============================================================================
-- FUNCTION: sincronizar_realizado_de_integracao
-- ============================================================================
-- Processa até 10 milhões de linhas com máxima performance
-- Usa dblink para conectar ao banco remoto
-- Retorna um SET de resultados com os contadores
-- ============================================================================

-- Garantir que dblink está disponível
CREATE EXTENSION IF NOT EXISTS dblink;

-- Dropar ambas (procedure e function) se existirem
DROP FUNCTION IF EXISTS back.sincronizar_realizado_de_integracao(INTEGER) CASCADE;
DROP FUNCTION IF EXISTS back.sincronizar_realizado_de_integracao(INTEGER, INTEGER, INTEGER) CASCADE;
DROP PROCEDURE IF EXISTS back.sincronizar_realizado_de_integracao(INTEGER) CASCADE;
DROP PROCEDURE IF EXISTS back.sincronizar_realizado_de_integracao(INTEGER, INTEGER, INTEGER) CASCADE;

CREATE FUNCTION back.sincronizar_realizado_de_integracao(
    p_cdintegracao INTEGER,
    p_dtinicio DATE DEFAULT CURRENT_DATE,
    p_dtfim DATE DEFAULT CURRENT_DATE
)
RETURNS TABLE (
    p_total_inseridos INTEGER,
    p_total_ignorados INTEGER,
    p_total_erros INTEGER,
    p_mensagem TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_idlote UUID;
    v_dtcriacao TIMESTAMP;
    v_processados INTEGER := 0;
    v_max_registros INTEGER := 10000000;
    v_temp_table TEXT := 'temp_integracao_' || p_cdintegracao || '_' || (EXTRACT(EPOCH FROM NOW())::BIGINT);
    v_total_inseridos INTEGER := 0;
    v_total_ignorados INTEGER := 0;
    v_total_erros INTEGER := 0;
    v_mensagem TEXT := '';
    v_host TEXT;
    v_porta INTEGER;
    v_dbname TEXT;
    v_usuario TEXT;
    v_senha TEXT;
    v_sql_origem TEXT;
    v_conexao_string TEXT;
    v_dblink_conn TEXT;
BEGIN
    -- Inicializar contadores
    v_dtcriacao := NOW();
    v_idlote := gen_random_uuid();
    
    -- ========================================================================
    -- VALIDAÇÕES INICIAIS E LEITURA DE CONFIGURAÇÃO
    -- ========================================================================
    IF p_cdintegracao IS NULL OR p_cdintegracao <= 0 THEN
        v_mensagem := 'cdintegracao inválido';
        RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
        RETURN;
    END IF;
    
    IF p_dtinicio IS NULL OR p_dtfim IS NULL THEN
        v_mensagem := 'datas inválidas';
        RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
        RETURN;
    END IF;
    
    IF p_dtinicio > p_dtfim THEN
        v_mensagem := 'data inicial não pode ser maior que data final';
        RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
        RETURN;
    END IF;
    
    -- Buscar configuração de conexão da integração
    BEGIN
        SELECT host, porta, dbname, usuario, sql_origem, senha_encrypted
        INTO v_host, v_porta, v_dbname, v_usuario, v_sql_origem, v_senha
        FROM back.integracao
        WHERE cdintegracao = p_cdintegracao AND flativo = TRUE;
        
        IF v_host IS NULL THEN
            v_mensagem := 'Integração não encontrada ou inativa';
            RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
            RETURN;
        END IF;
        
        IF v_sql_origem IS NULL OR TRIM(v_sql_origem) = '' THEN
            v_mensagem := 'sql_origem não configurado';
            RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
            RETURN;
        END IF;
        
        RAISE NOTICE '[SYNC %] Integração encontrada: host=%, dbname=%', p_cdintegracao, v_host, v_dbname;
        
    EXCEPTION WHEN OTHERS THEN
        v_mensagem := 'Erro ao buscar configuração: ' || SQLERRM;
        RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
        RETURN;
    END;
    
    -- Validar que SQL é SELECT (segurança)
    IF NOT (UPPER(TRIM(v_sql_origem)) ~ '^\s*(SELECT|WITH)' AND 
            NOT UPPER(TRIM(v_sql_origem)) ~ '(INSERT|UPDATE|DELETE|DROP|TRUNCATE|ALTER|CREATE)') THEN
        v_mensagem := 'SQL deve ser apenas SELECT ou WITH';
        RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
        RETURN;
    END IF;
    
    -- ========================================================================
    -- CRIAR TABELA TEMPORÁRIA (UNLOGGED para máxima velocidade)
    -- ========================================================================
    BEGIN
        EXECUTE format('
            CREATE UNLOGGED TABLE %I (
                cdintegracao_empresa INTEGER,
                cdplanocontabil INTEGER,
                tpmovimento CHAR(1),
                dtmovimento DATE,
                dscomplemento VARCHAR(500),
                valor NUMERIC(15,2),
                cdcentrocusto INTEGER
            )
        ', v_temp_table);
        
        -- Adicionar índices temporários para performance
        EXECUTE format('CREATE INDEX idx_temp_empresa ON %I (cdintegracao_empresa)', v_temp_table);
    EXCEPTION WHEN OTHERS THEN
        v_mensagem := 'Erro ao criar tabela temporária: ' || SQLERRM;
        RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
        RETURN;
    END;
    
    -- ========================================================================
    -- INSERIR DADOS DA ORIGEM NA TEMP TABLE via DBLINK
    -- ========================================================================
    BEGIN
        -- Construir string de conexão dblink
        v_dblink_conn := 'dbname=' || v_dbname || ' host=' || v_host || ' port=' || COALESCE(v_porta, 5432) || 
                         ' user=' || v_usuario || ' password=' || v_senha;
        
        RAISE NOTICE '[SYNC %] Conectando ao banco remoto...', p_cdintegracao;
        RAISE NOTICE '[SYNC %] SQL ORIGINAL: %', p_cdintegracao, v_sql_origem;
        
        -- Envolver SQL original numa subquery para adicionar filtro de período
        v_sql_origem := 'SELECT * FROM (' || v_sql_origem || ') AS sq WHERE dtmovimento BETWEEN ''' || 
            TO_CHAR(p_dtinicio, 'YYYY-MM-DD') || ''' AND ''' || 
            TO_CHAR(p_dtfim, 'YYYY-MM-DD') || ''' LIMIT ' || v_max_registros;
        
        RAISE NOTICE '[SYNC %] SQL FILTRADO COM PERÍODO: %', p_cdintegracao, v_sql_origem;
        RAISE NOTICE '[SYNC %] STRING CONEXÃO DBLINK: %', p_cdintegracao, v_dblink_conn;
        
        -- SQL que será executado via dblink
        v_conexao_string := 'INSERT INTO ' || quote_ident(v_temp_table) || ' 
            (cdintegracao_empresa, cdplanocontabil, tpmovimento, dtmovimento, dscomplemento, valor, cdcentrocusto)
            SELECT
                (r.cdempresa)::INTEGER,
                (r.cdplanocontabil)::INTEGER,
                UPPER(TRIM(r.tpmovimento))::CHAR(1),
                (r.dtmovimento)::DATE,
                SUBSTRING(COALESCE(r.dscomplemento, ''''), 1, 500),
                (r.valor)::NUMERIC(15,2),
                (r.cdcentrocusto)::INTEGER
            FROM dblink(' || quote_literal(v_dblink_conn) || ', ' || quote_literal(v_sql_origem) || ') 
                AS r(cdempresa INTEGER, cdplanocontabil INTEGER, tpmovimento CHAR(1), 
                     dtmovimento DATE, dscomplemento VARCHAR(500), valor NUMERIC(15,2), cdcentrocusto INTEGER)';
        
        RAISE NOTICE '[SYNC %] SQL INSERT COMPLETO: %', p_cdintegracao, v_conexao_string;
        
        -- Executar SQL remoto e trazer dados para temp table
        EXECUTE v_conexao_string;
        
        GET DIAGNOSTICS v_processados = ROW_COUNT;
        RAISE NOTICE '[SYNC %] Registros extraídos da origem remota: %', p_cdintegracao, v_processados;
        
    EXCEPTION WHEN OTHERS THEN
        v_mensagem := 'Erro ao extrair dados da origem remota: ' || SQLERRM;
        RAISE NOTICE 'ERRO DBLINK: %', v_mensagem;
        EXECUTE format('DROP TABLE IF EXISTS %I', v_temp_table);
        RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
        RETURN;
    END;
    
    IF v_processados = 0 THEN
        v_mensagem := 'Nenhum registro na origem';
        EXECUTE format('DROP TABLE IF EXISTS %I', v_temp_table);
        RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
        RETURN;
    END IF;
    
    -- ========================================================================
    -- CRIAR EXERCÍCIOS FALTANTES AUTOMATICAMENTE
    -- ========================================================================
    BEGIN
        RAISE NOTICE '[SYNC %] Criando exercícios faltantes...', p_cdintegracao;
        
        -- Inserir exercícios para todos os anos encontrados nos movimentos
        -- Mapeia cdempresa da origem (ERP) → cdempresa local (HELM) via back.empresa.cdintegracao
        EXECUTE format('
            INSERT INTO back.exercicio (anoref, descricao, floficial, flativo, cdempresa)
            SELECT DISTINCT
                EXTRACT(YEAR FROM t.dtmovimento)::INTEGER,
                ''Exercício '' || EXTRACT(YEAR FROM t.dtmovimento)::INTEGER || '' - Criado automaticamente pela sincronização'',
                FALSE,
                0,
                e.cdempresa
            FROM %I t
            INNER JOIN back.empresa e ON e.cdintegracao = t.cdintegracao_empresa
            WHERE NOT EXISTS (
                SELECT 1 FROM back.exercicio ex2
                WHERE ex2.cdempresa = e.cdempresa
                AND ex2.anoref = EXTRACT(YEAR FROM t.dtmovimento)::INTEGER
            )
            AND e.cdempresa IS NOT NULL
            ON CONFLICT DO NOTHING
        ', v_temp_table);
        
        RAISE NOTICE '[SYNC %] Exercícios criados/validados', p_cdintegracao;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Aviso ao criar exercícios: %', SQLERRM;
    END;
    
    -- ========================================================================
    -- INSERIR EM back.realizado COM JOINS E VALIDAÇÕES
    -- ========================================================================
    BEGIN
        RAISE NOTICE '[SYNC %] Iniciando INSERT em back.realizado. período: % até %', p_cdintegracao, p_dtinicio, p_dtfim;
        RAISE NOTICE '[SYNC %] Total registros na temp table: %', p_cdintegracao, v_processados;
        
        -- TESTE: Inserir TUDO primeiro sem validações para ver o que funciona
        EXECUTE format('
            INSERT INTO back.realizado (
                cdempresa, cdexercicio, cdcentrocusto, cdplanocontabil,
                mesref, anoref, tpmovimento, valor, dscomplemento, dtmovimento,
                cdintegracao, florigem, json_origem, flcancelado, dtcriacao, cdpessoa, idlote
            )
            SELECT 
                COALESCE(e.cdempresa, 999),
                COALESCE(ex.cdexercicio, 1),
                COALESCE(t.cdcentrocusto, 0),
                COALESCE(t.cdplanocontabil, 0),
                COALESCE(EXTRACT(MONTH FROM t.dtmovimento)::INTEGER, 1),
                COALESCE(EXTRACT(YEAR FROM t.dtmovimento)::INTEGER, 2026),
                COALESCE(t.tpmovimento, ''D''),
                COALESCE(t.valor, 0),
                COALESCE(t.dscomplemento, ''SEM DESCRICAO''),
                COALESCE(t.dtmovimento, CURRENT_DATE),
                %L,
                2::SMALLINT,
                json_build_object(
                    ''cdintegracao_empresa'', t.cdintegracao_empresa,
                    ''cdplanocontabil'', t.cdplanocontabil,
                    ''tpmovimento'', t.tpmovimento,
                    ''dtmovimento'', t.dtmovimento,
                    ''valor'', t.valor
                ),
                FALSE::BOOLEAN,
                %L,
                0::INTEGER,
                %L
            FROM %I t
            LEFT JOIN back.empresa e ON e.cdintegracao = t.cdintegracao_empresa
            LEFT JOIN back.exercicio ex ON ex.cdempresa = e.cdempresa AND ex.anoref = EXTRACT(YEAR FROM t.dtmovimento)::INTEGER
        ', p_cdintegracao, v_dtcriacao, v_idlote, v_temp_table);
        
        GET DIAGNOSTICS v_total_inseridos = ROW_COUNT;
        RAISE NOTICE '[SYNC %] Registros inseridos (TESTE SEM VALIDAÇÕES): %', p_cdintegracao, v_total_inseridos;
        
    EXCEPTION WHEN OTHERS THEN
        v_mensagem := 'Erro ao inserir em back.realizado: ' || SQLERRM;
        RAISE NOTICE 'ERRO: %', v_mensagem;
    END;
    
    -- ========================================================================
    -- CALCULAR IGNORADOS
    -- ========================================================================
    v_total_ignorados := v_processados - v_total_inseridos;
    v_total_erros := 0;
    
    -- ========================================================================
    -- ATUALIZAR TIMESTAMP DA INTEGRAÇÃO
    -- ========================================================================
    BEGIN
        UPDATE back.integracao
        SET ultima_sincronizacao = NOW()
        WHERE cdintegracao = p_cdintegracao;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Aviso ao atualizar timestamp: %', SQLERRM;
    END;
    
    -- ========================================================================
    -- LIMPAR RECURSOS
    -- ========================================================================
    EXECUTE format('DROP TABLE IF EXISTS %I', v_temp_table);
    
    -- ========================================================================
    -- MENSAGEM FINAL
    -- ========================================================================
    v_mensagem := format(
        'Sincronização período %s até %s: %s inseridos, %s ignorados, %s erros. Lote: %s',
        TO_CHAR(p_dtinicio, 'DD/MM/YYYY'),
        TO_CHAR(p_dtfim, 'DD/MM/YYYY'),
        v_total_inseridos,
        v_total_ignorados,
        v_total_erros,
        v_idlote
    );
    
    RAISE NOTICE '%', v_mensagem;
    
    -- ========================================================================
    -- RETORNAR RESULTADOS
    -- ========================================================================
    RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
    
EXCEPTION WHEN OTHERS THEN
    v_mensagem := 'Erro fatal na sincronização: ' || SQLERRM;
    RAISE NOTICE 'ERRO FATAL: %', v_mensagem;
    BEGIN
        EXECUTE format('DROP TABLE IF EXISTS %I', v_temp_table);
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;
    RETURN QUERY SELECT v_total_inseridos, v_total_ignorados, v_total_erros, v_mensagem;
END;
$$;

COMMENT ON FUNCTION back.sincronizar_realizado_de_integracao IS 
'Sincroniza dados de integração externas para back.realizado.
 Suporta até 10 milhões de linhas com máxima performance.
 Tudo processa no banco de dados.';
