-- Comparativo de Missão de Separação vs Carregamento (por Romaneio)
-- Compara o previsto vs realizado em cada etapa

WITH separacao_stats AS (
    SELECT 
        mli.romaneio_id,
        COUNT(DISTINCT mli.id) as total_itens,
        SUM(mli.quantidade_esperada) as qtd_prevista,
        SUM(mli.quantidade_bipada) as qtd_separada
    FROM missao_logistica_itens mli
    JOIN missao_logistica ml ON ml.id = mli.missao_logistica_id
    JOIN tipos_missao_logistica tml ON tml.id = ml.tipo_missao_id
    WHERE tml.codigo = 'SEPARACAO'
      AND mli.romaneio_id IS NOT NULL
      -- Opcional: filtrar por status ou data
      -- AND ml.status != 'cancelada'
    GROUP BY mli.romaneio_id
),
carregamento_stats AS (
    SELECT 
        mli.romaneio_id,
        MIN(ml.id) as missao_id, -- Apenas para referência
        SUM(mli.quantidade_esperada) as qtd_prevista,
        SUM(mli.quantidade_bipada) as qtd_conferida
    FROM missao_logistica_itens mli
    JOIN missao_logistica ml ON ml.id = mli.missao_logistica_id
    JOIN tipos_missao_logistica tml ON tml.id = ml.tipo_missao_id
    WHERE tml.codigo = 'CARREGAMENTO'
      AND mli.romaneio_id IS NOT NULL
      -- AND ml.status != 'cancelada'
    GROUP BY mli.romaneio_id
)
SELECT 
    s.romaneio_id,
    -- Status Separação
    ROUND(s.qtd_separada, 2) || '/' || ROUND(s.qtd_prevista, 2) as separacao_progresso,
    CASE 
        WHEN s.qtd_separada >= s.qtd_prevista THEN '100%'
        WHEN s.qtd_prevista > 0 THEN ROUND((s.qtd_separada / s.qtd_prevista) * 100, 1) || '%'
        ELSE '0%'
    END as separacao_pct,
    
    -- Status Carregamento
    COALESCE(ROUND(c.qtd_conferida, 2), 0) || '/' || COALESCE(ROUND(c.qtd_prevista, 2), 0) as carregamento_progresso,
    CASE 
        WHEN c.qtd_conferida >= c.qtd_prevista THEN '100%'
        WHEN c.qtd_prevista > 0 THEN ROUND((c.qtd_conferida / c.qtd_prevista) * 100, 1) || '%'
        ELSE '0%'
    END as carregamento_pct,
    
    -- Comparativo Direto (O que foi separado vs O que foi carregado)
    ROUND(s.qtd_separada, 2) as qtd_separada_total,
    COALESCE(ROUND(c.qtd_conferida, 2), 0) as qtd_carregada_total,
    (ROUND(s.qtd_separada, 2) - COALESCE(ROUND(c.qtd_conferida, 2), 0)) as diferenca_separado_carregado

FROM separacao_stats s
LEFT JOIN carregamento_stats c ON c.romaneio_id = s.romaneio_id
ORDER BY s.romaneio_id DESC;
