Ocupação de Salas

Objetivo

Mede, por sala ativa, quanto do tempo disponível configurado foi efetivamente ocupado por agendamentos e quanto foi bloqueado, dentro de um período. O resultado é uma linha por sala com: horas de ocupação, horas de bloqueio, total de horas configuradas e os percentuais de aproveitamento.

Conceitos e regras de negócio

1. Disponibilidade da sala (denominador)

Cada sala tem horários de funcionamento configurados por dia da semana (room_time_slots). A disponibilidade total no período é calculada assim:

1. Gera-se a lista de todos os dias do período (:startDate a :endDate).
2. Para cada dia, identifica-se o dia da semana (MONDAY, TUESDAY, ... — mesmo formato da coluna day_of_week).
3. Cruza-se com os horários configurados da sala naquele dia da semana.
4. Soma-se (end_time - start_time) de cada slot. As colunas start_time e end_time são inteiros em horas (ex.: 8 e 18 = das 8h às 18h).

Exemplo: sala com slot de segunda a sexta das 8h às 18h, período de 7 dias (5 dias úteis) → total = 5 × 10 = 50 horas.

2. Ocupação e bloqueios (numeradores)

Vêm da tabela appointments, filtrando pelo período (start_date between :startDate and :endDate) e separando por situação:

Nota: pela regra original, agendamentos Desmarcados (8) contam como ocupação, pois a exclusão é apenas de Falta e Bloqueio. Manter assim para ficar fiel ao relatório; se o time de dados quiser excluir desmarcados, basta adicionar o 8 na lista de exclusão — mas isso divergirá do sistema.

Tabela de referência dos status de agendamento:

3. Merge de sobreposições (regra mais importante)

Uma sala pode ter agendamentos simultâneos ou sobrepostos (ex.: dois clientes na mesma sala das 14h às 15h). Se simplesmente somarmos as durações, a ocupação fica inflada e pode passar de 100%.

Por isso, antes de somar, os intervalos de cada sala são fundidos: períodos que se sobrepõem contam apenas uma vez. A técnica usa window functions (funciona igual no Redshift):

1. Ordena os agendamentos da sala por start_date.
2. Calcula o "fim acumulado" até a linha anterior: max(end_date) over (partition by room ordenado por start_date, rows between unbounded preceding and 1 preceding).
3. Compara cada agendamento com esse fim acumulado (lag):
   • Se começa depois do fim acumulado → conta a duração inteira.
   • Se começa dentro de um período já contado mas termina depois → conta só a parte que excede.
   • Se está totalmente contido em um período já contado → conta zero.

Esse merge é feito separadamente para ocupação e para bloqueios.

4. Salas consideradas

Somente salas com status = 'ACTIVE' na tabela rooms.

Métricas finais

Usar nullif(total, 0) no denominador para evitar divisão por zero (sala sem horário configurado no período).

Totalizador: contagem de salas retornadas.

Adaptações necessárias para o Redshift

O sistema de origem usa recursos do PostgreSQL que não existem no Redshift. As substituições:

As window functions (lag, max ... rows between unbounded preceding and 1 preceding) e greatest funcionam normalmente no Redshift.

Tabelas envolvidas

⚠️ Todo join deve incluir a coluna "schema" além dos ids, para não cruzar salas e agendamentos de franquias diferentes. Registros deletados não existem no datalake (o ETL não sincroniza soft deletes).

Escopo multi-franquia

O relatório faz sentido por franquia (as salas pertencem a uma unidade), então o resultado deve sempre incluir "schema" no agrupamento — seja exibindo a coluna, seja filtrando por uma franquia específica.

Colunas do relatório → Datalake

Pontos de atenção

• Sobreposições: nunca somar durações direto — sempre aplicar o merge, senão salas com agendamentos simultâneos passam de 100%.
• Agendamentos que atravessam a meia-noite ou saem do período: o filtro é apenas por start_date dentro do período; se o end_date cair fora, a duração inteira conta (fiel ao original).
• Formato de exibição: o relatório original exibe as durações como texto de intervalo (ex.: 12:30:00); no datalake sugerimos horas decimais (ex.: 12.5), mais prático para BI.
• date_part(dow) no Redshift retorna 0 para domingo — o mapeamento para os nomes dos dias é diferente do isodow do PostgreSQL (que retorna 7 para domingo).