# Isolamento dos filtros da consulta de saídas de expedição

## Context

A consulta de saídas de expedição aplica filtros seletivos em `stock_ios`, mas
combina o resultado com uma subconsulta que calcula o progresso de tarefas para
todas as pré-faturas. Como essa subconsulta não possui uma barreira de
materialização, o PostgreSQL pode incorporá-la ao plano principal e escolher
estratégias diferentes conforme suas estimativas de cardinalidade.

No caso analisado, com faturamento entre 27/07/2026 e 05/08/2026:

| Situação | Plano atual | Protótipo com CTE materializada |
| --- | ---: | ---: |
| Iniciada | 11.146 ms | 5,3 ms |
| Separada | 749 ms | 60 ms |
| Não iniciada/iniciada, sem data | não medido | 409 ms com cache frio |

Para a situação Iniciada, o PostgreSQL estimou uma saída, encontrou 21 e
executou a agregação global de progresso 21 vezes. O plano acessou mais de 16
milhões de blocos. Para Separada, escolheu um `Hash Join` e executou a mesma
agregação apenas uma vez.

Cliente, NUMANE e NUMPFA são dados específicos de integrações e não devem ser
projetados em `stock_ios`, que é compartilhada por outros fluxos. Número do
pedido também não pode ser representado por uma coluna escalar: existem 11.830
saídas com múltiplos pedidos e até 70 pedidos em uma única saída.

## Goals

- Aplicar primeiro todos os filtros da consulta e as regras de visibilidade.
- Calcular produtos, vínculos e tarefas apenas para as saídas candidatas.
- Evitar que o custo dependa de estimativas de cardinalidade do otimizador.
- Manter a semântica atual de status de tarefa, cancelamentos, ordenação e
  paginação por cursor.
- Manter cliente, NUMANE, NUMPFA e número do pedido nas estruturas atuais.
- Atingir tempo de resposta p95 de até 1,5 segundo em base representativa.
- Executar toda a listagem em um único snapshot do PostgreSQL.

## Non-Goals

- Adicionar atributos específicos da expedição em `stock_ios`.
- Criar arrays de pedidos ou contadores derivados em `stock_ios`.
- Alterar contratos da API ou filtros do frontend.
- Forçar globalmente estratégias do PostgreSQL, como desabilitar `Nested Loop`.
- Resolver nesta alteração outras consultas que usam `stock_ios`.

## Recommended Approach

Usar uma única instrução SQL composta por CTEs explicitamente materializadas.
A primeira CTE, `filtered_outputs`, contém somente as saídas autorizadas que
atendem aos filtros recebidos. Todas as agregações posteriores devem começar
por esse conjunto.

Uma CTE sem `MATERIALIZED` não é suficiente: versões atuais do PostgreSQL podem
incorporá-la novamente à consulta principal e reproduzir o plano instável.
O `with()` do Eloquent também não isola a consulta; ele apenas faz eager loading
de relacionamentos após a consulta principal.

## Design

### 1. Consulta de candidatas

Extrair a construção dos filtros para um builder dedicado. Ele deve aplicar em
`stock_ios`:

- tipo e motivo de pré-fatura;
- exclusão dos estados de sincronização;
- ID;
- situação;
- data de faturamento;
- urgente;
- intervalo de criação;
- setor, equipe e demais regras de visibilidade do usuário.

Filtros dependentes de outras estruturas permanecem nesse builder:

- cliente, NUMANE e NUMPFA por `integrations`;
- número do pedido por `EXISTS` em produtos e suas integrações.

Esses joins ou `EXISTS` devem ser adicionados somente quando o filtro
correspondente estiver presente. A CTE deve selecionar as colunas necessárias
para ordenação e hidratação, além do ID.

```sql
WITH filtered_outputs AS MATERIALIZED (
    SELECT stock_ios.*
    FROM stock_ios
    -- joins condicionais e filtros
)
```

### 2. Vínculos ativos

`active_assignments` deve iniciar em `filtered_outputs`, atravessar somente os
produtos das saídas candidatas e considerar produtos e tarefas não cancelados.
O resultado permanece distinto por `stock_io_product_id`, preservando a regra
atual quando um produto possui mais de um vínculo.

### 3. Progresso de produtos

`product_progress` deve agregar por `stock_io_id` somente os produtos das
candidatas e calcular:

- total de produtos;
- total de produtos ativos;
- total de produtos ativos vinculados.

O status continua seguindo as regras atuais:

- 1: nenhum produto ativo vinculado;
- 2: parte dos produtos ativos vinculada;
- 3: todos vinculados;
- 3 também quando não existem produtos ativos.

### 4. Quantidade de tarefas

`task_counts` deve iniciar em `filtered_outputs` e usar
`picking_order_stock_ios`, agregando apenas as associações das candidatas.

### 5. Consulta final

A consulta final combina `filtered_outputs`, `product_progress` e
`task_counts`. O filtro `all_items_in_task` é aplicado somente depois do cálculo
do status agregado.

A ordenação permanece:

1. data de faturamento;
2. status de tarefa;
3. status da saída;
4. ID da saída.

A paginação continua por cursor e deve usar a mesma tupla de ordenação para
evitar duplicidade ou omissão entre páginas.

### 6. Integração com Laravel 10

Laravel 10 não oferece suporte nativo completo a CTE materializada. Criar um
serviço dedicado, por exemplo `ExpeditionStockOutputListingQuery`, responsável
por:

1. receber o builder das candidatas;
2. incorporar seu SQL e bindings em `filtered_outputs AS MATERIALIZED`;
3. acrescentar as CTEs estáticas de progresso e tarefas;
4. expor o resultado como fonte do Eloquent com `fromRaw` e bindings;
5. preservar eager loading de `integration` e `team` e a paginação por cursor.

O SQL bruto deve ficar encapsulado nesse serviço. Valores vindos do request
continuam como bindings; não devem ser interpolados na string SQL.

### 7. Índices

Manter os índices já adicionados para produtos e vínculos ativos. Avaliar um
índice parcial com ordem `(status, pre_invoice_billing_date, id)` para o acesso
inicial quando situação e data forem usadas juntas. Esse índice é complementar;
a estabilidade não deve depender dele nem de estatísticas estendidas.

### 8. Rollout

- Introduzir o novo serviço sem remover imediatamente o suporte SQL anterior.
- Comparar os IDs, atributos calculados e cursores retornados pelas duas
  consultas em base representativa.
- Publicar a troca de consulta sem mudança de contrato.
- Monitorar p50, p95, p99, linhas candidatas e tempo de banco por combinação de
  filtros.
- Remover o caminho antigo após a validação em produção.

## Error Handling

- Filtros inválidos continuam sendo rejeitados pelo Form Request existente.
- Ausência de integração deve preservar o comportamento atual do `INNER JOIN`
  quando filtros de integração forem usados.
- Saídas sem produtos devem continuar recebendo status de tarefa 3.
- Produtos e tarefas cancelados não entram nos totais ativos ou vinculados.
- Se a consulta exceder o limite operacional, registrar quantidade de
  candidatas e combinação de filtros, sem registrar dados sensíveis.
- A consulta deve permanecer em uma única instrução para garantir consistência
  entre filtros, progresso e paginação.

## Testing

### Equivalência funcional

- Todos os filtros isoladamente e em combinações representativas.
- Usuário master, administrador com equipes, administrador sem equipes e regras
  de setor.
- Saída sem tarefa, parcial, completa e sem produtos ativos.
- Produto cancelado, vínculo cancelado e tarefa cancelada.
- Tarefa legada e tarefa multi-pré-fatura.
- Número do pedido com uma e várias ocorrências por saída.
- Filtro `all_items_in_task` para os três estados.
- Ordenação e paginação por cursor em páginas consecutivas.

### Performance

- `EXPLAIN (ANALYZE, BUFFERS)` para todas as situações da tela.
- Período seletivo e período amplo.
- Cenário padrão sem data.
- Filtros de cliente, pré-fatura e pedido.
- Execuções com cache frio e quente.
- Confirmar que cada CTE de agregação possui um loop e que nenhuma agregação
  global é repetida por saída candidata.
- Validar p95 end-to-end de até 1,5 segundo em base representativa.

### Regressão estrutural

- Teste que inspeciona o SQL gerado e exige `filtered_outputs AS MATERIALIZED`.
- Teste que garante bindings para todos os valores do request.
- Teste que limita a agregação aos IDs da CTE de candidatas.

## Open Questions

- Definir a base e a quantidade de execuções usadas para certificar o p95.
- Definir por quanto tempo o caminho SQL anterior ficará disponível para
  comparação durante o rollout.