-- Comparativo Analítico Detalhado: Missão Separação (ID: :id_separacao) vs Carregamento (ID: :id_carregamento)
-- Compara item a item (agrupado por produto), incluindo ocorrências e status detalhado

WITH itens_separacao AS (
    SELECT 
        mli.produto_id,
        SUM(mli.quantidade) as qtd_prevista,
        SUM(mli.quantidade_bipada) as qtd_separada,
        SUM(mli.qtd_conferida) as qtd_conferida_sep,
        
        -- Contagem de ocorrências
        COUNT(CASE WHEN mli.status = 'ocorrencia' THEN 1 END) as total_ocorrencias,
        
        -- Agrupamento de tipos de ocorrência extraídos do JSON
        STRING_AGG(DISTINCT 
            CASE WHEN mli.status = 'ocorrencia' THEN 
                COALESCE(mli.dados_coletados::json->>'ocorrencia_tipo', 'Ocorrência')
            END, ', '
        ) as tipos_ocorrencia,
        
        -- Motivos das ocorrências
        STRING_AGG(DISTINCT 
            CASE WHEN mli.status = 'ocorrencia' THEN 
                mli.dados_coletados::json->>'motivo_problema'
            END, ' | '
        ) as motivos_ocorrencia,
        
        -- Quantidade em falta (soma)
        SUM(
            CASE WHEN mli.status = 'ocorrencia' THEN 
                COALESCE((mli.dados_coletados::json->>'quantidade_falta')::numeric, 0)
            ELSE 0 END
        ) as qtd_falta_sep,
        
        -- Outras ocorrências (pulado, removido, etc)
        STRING_AGG(DISTINCT 
            CASE 
                WHEN mli.pulado_vezes > 0 THEN 'Pulado ' || mli.pulado_vezes || 'x'
                WHEN mli.motivo_remocao IS NOT NULL THEN 'Removido: ' || mli.motivo_remocao
                ELSE NULL 
            END, ' | '
        ) as outras_ocorrencias_sep
        
    FROM missao_logistica_itens mli
    WHERE mli.missao_logistica_id = :id_separacao
    GROUP BY mli.produto_id
),
itens_carregamento AS (
    SELECT 
        mli.produto_id,
        SUM(mli.quantidade) as qtd_prevista,
        SUM(mli.quantidade_bipada) as qtd_carregada,
        SUM(mli.qtd_conferida) as qtd_conferida_car,
        
        -- Contagem de ocorrências
        COUNT(CASE WHEN mli.status = 'ocorrencia' THEN 1 END) as total_ocorrencias,
        
        -- Agrupamento de tipos de ocorrência
        STRING_AGG(DISTINCT 
            CASE WHEN mli.status = 'ocorrencia' THEN 
                COALESCE(mli.dados_coletados::json->>'ocorrencia_tipo', 'Ocorrência')
            END, ', '
        ) as tipos_ocorrencia,
        
        -- Motivos das ocorrências
        STRING_AGG(DISTINCT 
            CASE WHEN mli.status = 'ocorrencia' THEN 
                mli.dados_coletados::json->>'motivo_problema'
            END, ' | '
        ) as motivos_ocorrencia,
        
        -- Quantidade em falta
        SUM(
            CASE WHEN mli.status = 'ocorrencia' THEN 
                COALESCE((mli.dados_coletados::json->>'quantidade_falta')::numeric, 0)
            ELSE 0 END
        ) as qtd_falta_car,
        
        STRING_AGG(DISTINCT 
            CASE 
                WHEN mli.pulado_vezes > 0 THEN 'Pulado ' || mli.pulado_vezes || 'x'
                WHEN mli.motivo_remocao IS NOT NULL THEN 'Removido: ' || mli.motivo_remocao
                ELSE NULL 
            END, ' | '
        ) as outras_ocorrencias_car
        
    FROM missao_logistica_itens mli
    WHERE mli.missao_logistica_id = :id_carregamento
    GROUP BY mli.produto_id
)
SELECT 
    COALESCE(s.produto_id, c.produto_id) as produto_id,
    
    -- ====== SEPARAÇÃO ======
    COALESCE(s.qtd_prevista, 0) as sep_previsto,
    COALESCE(s.qtd_separada, 0) as sep_realizado,
    COALESCE(s.qtd_conferida_sep, 0) as sep_conferido,
    COALESCE(s.total_ocorrencias, 0) as sep_num_ocorrencias,
    s.tipos_ocorrencia as sep_tipos_ocorrencia,
    s.motivos_ocorrencia as sep_motivos,
    COALESCE(s.qtd_falta_sep, 0) as sep_qtd_falta,
    
    -- ====== CARREGAMENTO ======
    COALESCE(c.qtd_prevista, 0) as car_previsto,
    COALESCE(c.qtd_carregada, 0) as car_realizado,
    COALESCE(c.qtd_conferida_car, 0) as car_conferido,
    COALESCE(c.total_ocorrencias, 0) as car_num_ocorrencias,
    c.tipos_ocorrencia as car_tipos_ocorrencia,
    c.motivos_ocorrencia as car_motivos,
    COALESCE(c.qtd_falta_car, 0) as car_qtd_falta,
    
    -- ====== COMPARATIVO ======
    (COALESCE(s.qtd_separada, 0) - COALESCE(c.qtd_carregada, 0)) as diferenca_qtd,
    
    -- Flag de Inconsistência de Ocorrência
    CASE 
        WHEN COALESCE(s.total_ocorrencias, 0) > 0 AND COALESCE(c.total_ocorrencias, 0) = 0 THEN 
            'ATENÇÃO: Ocorrência na Separação, OK no Carregamento'
        WHEN COALESCE(s.total_ocorrencias, 0) = 0 AND COALESCE(c.total_ocorrencias, 0) > 0 THEN 
            'ATENÇÃO: OK na Separação, Ocorrência no Carregamento'
        WHEN COALESCE(s.total_ocorrencias, 0) > 0 AND COALESCE(c.total_ocorrencias, 0) > 0 THEN 
            'Ocorrência em AMBOS'
        ELSE NULL
    END as alerta_ocorrencia,
    
    -- Status Divergência Final
    CASE 
        -- Sem produto em uma das missões
        WHEN s.produto_id IS NULL THEN '🔴 NÃO CONSTA NA SEPARAÇÃO'
        WHEN c.produto_id IS NULL THEN '🔴 NÃO CONSTA NO CARREGAMENTO'
        
        -- OK
        WHEN COALESCE(s.qtd_separada, 0) = COALESCE(c.qtd_carregada, 0) 
             AND COALESCE(s.total_ocorrencias, 0) = 0 
             AND COALESCE(c.total_ocorrencias, 0) = 0 THEN '✅ OK'
             
        -- OK mas com ocorrências resolvidas
        WHEN COALESCE(s.qtd_separada, 0) = COALESCE(c.qtd_carregada, 0) THEN '⚠️ OK (COM OCORRÊNCIAS)'
        
        -- Divergências
        WHEN COALESCE(s.qtd_separada, 0) > COALESCE(c.qtd_carregada, 0) THEN '🟡 FALTA CARREGAR'
        WHEN COALESCE(s.qtd_separada, 0) < COALESCE(c.qtd_carregada, 0) THEN '🟠 CARREGOU A MAIS'
        
        ELSE '❓ VERIFICAR'
    END as status_final

FROM itens_separacao s
FULL OUTER JOIN itens_carregamento c ON c.produto_id = s.produto_id
ORDER BY 
    -- Priorizar problemas
    CASE 
        WHEN s.produto_id IS NULL OR c.produto_id IS NULL THEN 1
        WHEN COALESCE(s.total_ocorrencias, 0) != COALESCE(c.total_ocorrencias, 0) THEN 2
        WHEN COALESCE(s.qtd_separada, 0) != COALESCE(c.qtd_carregada, 0) THEN 3
        ELSE 4
    END,
    produto_id ASC;
