# Usa o arquivo local; se não existir, baixa da URL do recurso CSV no portal
arquivo_local <- "resources_80c5b3cd-d4f7-466e-9031-e636ef56dd99_156_cco_diario_2026__2_.csv"
url_dados <- "https://dados.recife.pe.gov.br/dataset/331e88a9-d878-464b-b0bb-cb803d7091b3/resource/80c5b3cd-d4f7-466e-9031-e636ef56dd99/download/156_cco_diario_2026.csv"   

if (!file.exists(arquivo_local)) {
  stopifnot("Informe url_dados ou coloque o CSV na pasta do projeto" = !is.na(url_dados))
  download.file(url_dados, arquivo_local, mode = "wb")
}

# Lê tudo como texto
bruto <- read_delim(arquivo_local, delim = ";",
                    col_types = cols(.default = col_character()),
                    locale = locale(encoding = "UTF-8"))

# Diagnóstico do arquivo bruto: valores distintos, ausentes e um exemplo por variável
tibble(variavel  = names(bruto),
       distintos = map_int(bruto, n_distinct),
       ausentes  = map_int(bruto, ~ sum(is.na(.x))),
       exemplo   = map_chr(bruto, ~ as.character(.x[!is.na(.x)][1]))) %>%
  kable(align = "lrrl", col.names = c("Variável original", "Valores distintos", "Ausentes", "Exemplo"))

dados <- bruto %>%
  rename_with(str_to_lower) %>%
  mutate(rpa = as.integer(rpa),
         across(c(data_demanda, data_ult_situacao), ymd),
         across(c(gruposervico_descricao, servico_descricao, logradouro, bairro), str_squish))

stopifnot(!anyNA(dados$data_demanda), !anyNA(dados$data_ult_situacao))

grupos_acentuados <- tribble(
  ~gruposervico_descricao,            ~grupo_servico,
  "ACAO EDUCATIVA",                   "Ação educativa",
  "ARBORIZACAO",                      "Arborização",
  "COLETA SELETIVA",                  "Coleta seletiva",
  "COLETA URBANA",                    "Coleta urbana",
  "CONSERVACAO",                      "Conservação",
  "DENUNCIA VEICULO",                 "Denúncia de veículo",
  "DENUNCIAS",                        "Denúncias",
  "DRENAGEM",                         "Drenagem",
  "ESCADARIA",                        "Escadaria",
  "FISCALIZACAO URBANA",              "Fiscalização urbana",
  "ILUMINACAO PUBLICA",               "Iluminação pública",
  "LIMPEZA URBANA",                   "Limpeza urbana",
  "MACRODRENAGEM",                    "Macrodrenagem",
  "MANUT. URBANA",                    "Manutenção urbana",
  "PAVIMENTACAO",                     "Pavimentação",
  "PLANEJAMENTO DE LIMPEZA URBANA",   "Planejamento de limpeza urbana",
  "POSTE",                            "Poste",
  "PRACAS",                           "Praças",
  "PROJETOS",                         "Projetos",
  "REPOSICAO DE PLACAS",              "Reposição de placas",
  "SEMAFOROS",                        "Semáforos",
  "SEMENTEIRA",                       "Sementeira"
)

# Situações na ordem lógica (presumida) do fluxo de atendimento
situacoes <- tribble(
  ~situacao_orig,   ~situacao,
  "CADASTRADA",     "Cadastrada",
  "PREPARACAO",     "Preparação",
  "EXECUCAO",       "Execução",
  "PENDENCIA",      "Pendência",
  "FISCALIZACAO",   "Fiscalização",
  "MONITORAMENTO",  "Monitoramento",
  "ATENDIDA",       "Atendida"
)

dados <- dados %>%
  mutate(servico = str_to_sentence(servico_descricao)) %>%
  rename(situacao_orig = situacao) %>%
  left_join(grupos_acentuados, by = "gruposervico_descricao") %>%
  left_join(situacoes, by = "situacao_orig") %>%
  mutate(situacao = factor(situacao, levels = situacoes$situacao))

# Conferência: nenhum grupo ou situação ficou sem correspondência
stopifnot(!anyNA(dados$grupo_servico), !anyNA(dados$situacao))

dados <- dados %>%
  separate(logradouro, into = c("tipo_via", "nome_via"), sep = " ", extra = "merge", fill = "right")

rpa_nomes <- tibble(rpa = 1:6,
                    rpa_nome = c("RPA 1 · Centro", "RPA 2 · Norte", "RPA 3 · Noroeste",
                                 "RPA 4 · Oeste", "RPA 5 · Sudoeste", "RPA 6 · Sul"))

dados <- dados %>%
  left_join(rpa_nomes, by = "rpa") %>%
  mutate(bairro = na_if(bairro, "INDEFINIDO"))

dados <- dados %>%
  mutate(id_demanda = row_number(),
         tempo_dias = as.integer(data_ult_situacao - data_demanda),
         coorte     = if_else(year(data_demanda) == 2026, "Aberta em 2026", "Legado (até 2025)"),
         atendida   = situacao == "Atendida") %>%
  select(id_demanda, grupo_servico, servico, tipo_via, nome_via, bairro, rpa, rpa_nome,
         data_demanda, data_ult_situacao, situacao, tempo_dias, coorte, atendida)

# Consistência lógica: a última movimentação nunca é anterior à abertura
stopifnot(all(dados$tempo_dias >= 0))

# Filtro 1: pedidos abertos em 2026. A base só traz demandas MOVIMENTADAS em 2026,
# então o "legado" é um grupo enviesado (seção 4.6); taxas e tempos usam só 2026.
d26 <- dados %>% filter(coorte == "Aberta em 2026")

# Filtro 2: dentro de 2026, só as atendidas (único caso em que tempo_dias = tempo de atendimento)
d26_atend <- d26 %>% filter(atendida)

tribble(
  ~Variável,                    ~Tipo,        ~Descrição,
  "id_demanda",                 "inteiro",    "Identificador criado na limpeza",
  "grupo_servico / servico",    "texto",      "Grupo (22 categorias, com acentos) e serviço (93 categorias)",
  "tipo_via / nome_via",        "texto",      "Resultado da separação do logradouro",
  "bairro",                     "texto",      "Bairro (NA quando 'Indefinido')",
  "rpa / rpa_nome",             "inteiro/texto", "Região Político-Administrativa (1 a 6) e seu nome",
  "data_demanda",               "data",       "Data de abertura do pedido",
  "data_ult_situacao",          "data",       "Data da última movimentação",
  "situacao",                   "fator",      "Etapa atual (7 níveis)",
  "tempo_dias",                 "inteiro",    "Dias entre abertura e última movimentação",
  "coorte",                     "texto",      "Aberta em 2026 ou Legado (até 2025)",
  "atendida",                   "lógico",     "TRUE se situação = Atendida"
) %>% kable()

set.seed(2026)
dados %>%
  slice_sample(n = 8) %>%
  select(grupo_servico, servico, bairro, rpa, data_demanda, situacao, tempo_dias) %>%
  mutate(servico = str_trunc(servico, 38)) %>%
  kable(caption = "Amostra aleatória de 8 linhas do conjunto limpo")

q <- quantile(dados$tempo_dias, c(.5, .9))
top_grupo  <- dados %>% count(grupo_servico, sort = TRUE) %>% slice(1)
top_bairro <- dados %>% filter(!is.na(bairro)) %>% count(bairro, sort = TRUE) %>% slice(1)

tribble(
  ~Variável, ~Resumo,
  "grupo_servico",
  str_glue("{n_distinct(dados$grupo_servico)} grupos; o maior é {top_grupo$grupo_servico} ({fmt_p(top_grupo$n/nrow(dados))} das demandas)"),
  "bairro / rpa",
  str_glue("{n_distinct(dados$bairro, na.rm = TRUE)} bairros em 6 RPAs; o mais frequente é {top_bairro$bairro} ({fmt_n(top_bairro$n)} demandas)"),
  "situacao",
  str_glue("{fmt_p(mean(dados$atendida))} Atendidas; {fmt_p(mean(dados$situacao == 'Cadastrada'))} ainda Cadastradas (primeira etapa)"),
  "data_demanda",
  str_glue("de {format(min(dados$data_demanda), '%d/%m/%Y')} a {format(max(dados$data_demanda), '%d/%m/%Y')}; {fmt_p(mean(dados$coorte == 'Aberta em 2026'))} abertas em 2026"),
  "tempo_dias",
  str_glue("mediana {q[1]} dias; percentil 90 = {q[2]} dias; máximo {fmt_n(max(dados$tempo_dias))} (muito assimétrica: a média é {fmt_d(mean(dados$tempo_dias))})")
) %>% kable(caption = "Resumo consolidado das variáveis de interesse")

# Distribuição dos grupos de serviço (curva de Pareto)
pareto <- dados %>%
  count(grupo_servico, name = "n", sort = TRUE) %>%
  mutate(pct = n / sum(n), pct_acum = cumsum(pct))

g1 <- pareto %>%
  slice_head(n = 10) %>%
  mutate(grupo_servico = fct_reorder(grupo_servico, n),
         dica = str_glue("{grupo_servico}<br>{fmt_n(n)} demandas ({fmt_p(pct)})")) %>%
  ggplot(aes(grupo_servico, n, text = dica)) +
  geom_col(fill = "#2C7FB8") +
  coord_flip() +
  scale_y_continuous(labels = fmt_n) +
  labs(title = "Iluminação pública domina a demanda por zeladoria",
       subtitle = "10 maiores grupos de serviço, jan–jun/2026", x = NULL, y = "Nº de demandas")

ggplotly(g1, tooltip = "text")

pareto %>%
  transmute(`Grupo de serviço` = grupo_servico, Demandas = n, `% do total` = pct, `% acumulado` = pct_acum) %>%
  dt_pt(pagina = 6) %>%
  formatPercentage(c("% do total", "% acumulado"), 1, dec.mark = ",", mark = ".") %>%
  formatRound("Demandas", 0, mark = ".")

cores_sit <- c("Cadastrada" = "#CBD5E1", "Preparação" = "#99D5CF", "Execução" = "#0284C7",
               "Pendência" = "#F59E0B", "Fiscalização" = "#7C3AED", "Monitoramento" = "#92400E",
               "Atendida" = "#16A34A")

top10 <- pareto$grupo_servico[1:10]

# Percentual de cada situação dentro de cada grupo
sit_grupo <- d26 %>%
  filter(grupo_servico %in% top10) %>%
  count(grupo_servico, situacao) %>%
  group_by(grupo_servico) %>%
  mutate(p = n / sum(n)) %>%
  ungroup()

# Ordena os grupos pela taxa de atendimento
ordem <- d26 %>% filter(grupo_servico %in% top10) %>%
  group_by(grupo_servico) %>% summarise(tx = mean(atendida)) %>% arrange(tx) %>% pull(grupo_servico)

g2 <- sit_grupo %>%
  mutate(grupo_servico = factor(grupo_servico, levels = ordem),
         dica = str_glue("{grupo_servico}<br>{situacao}: {fmt_p(p)} ({fmt_n(n)})")) %>%
  ggplot(aes(grupo_servico, p, fill = situacao, text = dica)) +
  geom_col(width = 0.75) +
  coord_flip() +
  scale_fill_manual(values = cores_sit, name = NULL) +
  scale_y_continuous(labels = label_percent()) +
  labs(title = "Em que etapa estão os pedidos de 2026?",
       subtitle = "Os 10 maiores grupos, ordenados pela taxa de atendimento", x = NULL, y = NULL)

ggplotly(g2, tooltip = "text") %>% layout(legend = list(orientation = "h", y = -0.15))

# Taxa de atendimento por grupo e classificação em "resposta" ou "fila"
regimes <- d26 %>%
  group_by(grupo_servico) %>%
  summarise(demandas = n(), pct_atendida = mean(atendida), .groups = "drop") %>%
  mutate(regime = case_when(demandas < 100       ~ "Amostra pequena (< 100)",
                            pct_atendida >= 0.70 ~ "Resposta (≥ 70% atendidas)",
                            pct_atendida >= 0.30 ~ "Intermediário (30–70%)",
                            TRUE                 ~ "Fila (< 30% atendidas)"))

regimes %>%
  group_by(regime) %>%
  summarise(exemplos = paste(head(grupo_servico[order(-demandas)], 3), collapse = "; "),
            grupos = n(), demandas = sum(demandas), .groups = "drop") %>%
  mutate(`% das demandas` = fmt_p(demandas / sum(demandas)), demandas = fmt_n(demandas)) %>%
  arrange(desc(grupos)) %>%
  select(regime, grupos, demandas, exemplos, `% das demandas`) %>%
  kable(col.names = c("Regime", "Nº de grupos", "Demandas", "Maiores grupos", "% das demandas"), align = "lrrlr")

fila <- regimes %>% filter(regime == "Fila (< 30% atendidas)")
fila_dados <- d26 %>% filter(grupo_servico %in% fila$grupo_servico)
ilum26 <- regimes %>% filter(grupo_servico == "Iluminação pública")

grupos_tempo <- d26_atend %>% count(grupo_servico) %>% filter(n >= 100) %>% pull(grupo_servico)

g3 <- d26_atend %>%
  filter(grupo_servico %in% grupos_tempo) %>%
  mutate(grupo_servico = fct_reorder(grupo_servico, tempo_dias, .fun = median)) %>%
  ggplot(aes(grupo_servico, tempo_dias)) +
  geom_boxplot(fill = "#A6CEE3", outlier.alpha = 0.15, outlier.size = 0.6) +
  coord_flip(ylim = c(0, 60)) +
  labs(title = "Tempo até o atendimento por grupo de serviço",
       subtitle = "Demandas atendidas abertas em 2026 (eixo cortado em 60 dias)",
       x = NULL, y = "Dias entre abertura e última movimentação")

ggplotly(g3)

tab_tempo <- d26_atend %>%
  filter(grupo_servico %in% grupos_tempo) %>%
  group_by(grupo_servico) %>%
  summarise(atendidas = n(), mediana = median(tempo_dias), p90 = quantile(tempo_dias, .9),
            ate_7d = mean(tempo_dias <= 7), mais_30d = mean(tempo_dias > 30), .groups = "drop") %>%
  arrange(mediana)

tab_tempo %>%
  rename(`Grupo de serviço` = grupo_servico, Atendidas = atendidas, `Mediana (dias)` = mediana,
         `P90 (dias)` = p90, `Até 7 dias` = ate_7d, `Mais de 30 dias` = mais_30d) %>%
  dt_pt(pagina = 8) %>%
  formatPercentage(c("Até 7 dias", "Mais de 30 dias"), 1, dec.mark = ",", mark = ".") %>%
  formatRound("Atendidas", 0, mark = ".")

t_ilum <- tab_tempo %>% filter(grupo_servico == "Iluminação pública")
t_limp <- tab_tempo %>% filter(grupo_servico == "Limpeza urbana")
t_arb  <- tab_tempo %>% filter(grupo_servico == "Arborização")
t_den  <- tab_tempo %>% filter(grupo_servico == "Denúncias")

rpa_tab <- d26 %>%
  group_by(rpa_nome) %>%
  summarise(demandas = n(),
            pct_iluminacao = mean(grupo_servico == "Iluminação pública"),
            pct_atendida = mean(atendida),
            mediana_dias = median(tempo_dias[atendida]), .groups = "drop")

rpa_tab %>%
  rename(RPA = rpa_nome, Demandas = demandas, `% iluminação` = pct_iluminacao,
         `% atendidas` = pct_atendida, `Mediana (dias, atendidas)` = mediana_dias) %>%
  dt_pt(pagina = 6) %>%
  formatPercentage(c("% iluminação", "% atendidas"), 1, dec.mark = ",", mark = ".") %>%
  formatRound("Demandas", 0, mark = ".")

rpa_melhor <- rpa_tab %>% slice_max(pct_atendida, n = 1, with_ties = FALSE)
rpa_pior   <- rpa_tab %>% slice_min(pct_atendida, n = 1, with_ties = FALSE)

grupos_heat <- c("Iluminação pública", "Limpeza urbana", "Arborização")

heat <- d26_atend %>%
  filter(grupo_servico %in% grupos_heat) %>%
  group_by(grupo_servico, rpa_nome) %>%
  summarise(mediana = median(tempo_dias), .groups = "drop")

ggplot(heat, aes(rpa_nome, factor(grupo_servico, levels = rev(grupos_heat)), fill = mediana)) +
  geom_tile(color = "white", linewidth = 1) +
  geom_text(aes(label = mediana), fontface = "bold", size = 5) +
  scale_fill_gradient(low = "#E5F5E0", high = "#E6550D", name = "Mediana (dias)") +
  labs(title = "Mesmo serviço, ritmos diferentes entre as RPAs",
       subtitle = "Mediana de dias até o atendimento, pedidos de 2026", x = NULL, y = NULL) +
  theme(axis.text.x = element_text(angle = 20, hjust = 1), panel.grid = element_blank())

# Mediana de um grupo em uma RPA (para citar no texto)
hv <- function(g, r) heat$mediana[heat$grupo_servico == g & heat$rpa_nome == r]
h_il <- heat %>% filter(grupo_servico == "Iluminação pública")

bairros <- d26 %>%
  filter(!is.na(bairro)) %>%
  group_by(bairro, rpa_nome) %>%
  summarise(demandas = n(), pct_iluminacao = mean(grupo_servico == "Iluminação pública"),
            pct_atendida = mean(atendida), .groups = "drop") %>%
  filter(demandas >= 300)

corr_mix <- cor(bairros$pct_iluminacao, bairros$pct_atendida)

g5 <- ggplot(bairros, aes(pct_iluminacao, pct_atendida, color = rpa_nome, size = demandas,
                          text = str_glue("{bairro}<br>{fmt_p(pct_iluminacao)} iluminação<br>{fmt_p(pct_atendida)} atendidos<br>{fmt_n(demandas)} demandas"))) +
  geom_point(alpha = 0.8) +
  scale_x_continuous(labels = label_percent()) +
  scale_y_continuous(labels = label_percent()) +
  scale_color_brewer(palette = "Dark2", name = NULL) +
  scale_size_continuous(guide = "none") +
  labs(title = "Mais iluminação no pedido, mais atendimento no bairro",
       subtitle = "Cada ponto é um bairro com 300+ pedidos em 2026",
       x = "% dos pedidos que são de iluminação", y = "% dos pedidos atendidos")

ggplotly(g5, tooltip = "text")

bairros %>%
  transmute(Bairro = bairro, RPA = rpa_nome, Demandas = demandas,
            `% iluminação` = pct_iluminacao, `% atendidos` = pct_atendida) %>%
  arrange(desc(`% atendidos`)) %>%
  dt_pt(pagina = 8) %>%
  formatPercentage(c("% iluminação", "% atendidos"), 1, dec.mark = ",", mark = ".") %>%
  formatRound("Demandas", 0, mark = ".")

# Semanas completas (segunda a domingo), para não subestimar a primeira e a última
top4 <- pareto$grupo_servico[1:4]
inicio <- as.Date("2026-01-05")
fim_ok <- max(d26$data_demanda) - days(6)

serie <- d26 %>%
  filter(grupo_servico %in% top4) %>%
  mutate(semana = floor_date(data_demanda, "week", week_start = 1)) %>%
  filter(semana >= inicio, semana <= fim_ok) %>%
  count(semana, grupo_servico)

g6 <- ggplot(serie, aes(semana, n, color = grupo_servico, group = grupo_servico,
                        text = str_glue("{grupo_servico}<br>Semana de {format(semana, '%d/%m')}: {fmt_n(n)}"))) +
  geom_line(linewidth = 0.9) +
  scale_color_brewer(palette = "Set1", name = NULL) +
  scale_x_date(date_breaks = "1 month", labels = rotulo_mes) +
  scale_y_continuous(labels = fmt_n) +
  labs(title = "Novos pedidos por semana", subtitle = "Quatro maiores grupos, semanas completas de 2026",
       x = NULL, y = "Pedidos abertos na semana")

ggplotly(g6, tooltip = "text")

# Meses completos (jan–mai); junho é parcial (dados até 19/06)
mensal <- d26 %>%
  filter(data_demanda < as.Date("2026-06-01")) %>%
  count(grupo_servico, mes = floor_date(data_demanda, "month")) %>%
  pivot_wider(names_from = mes, values_from = n)

var_mes <- function(g) {
  linha <- mensal %>% filter(grupo_servico == g)
  linha[["2026-05-01"]] / linha[["2026-01-01"]] - 1
}

legado <- dados %>% filter(coorte == "Legado (até 2025)")

atu_dia <- dados %>% count(data_ult_situacao, coorte)

pico <- atu_dia %>%
  group_by(data_ult_situacao) %>% summarise(total = sum(n), .groups = "drop") %>%
  slice_max(total, n = 1)

ggplot(atu_dia, aes(data_ult_situacao, n, fill = coorte)) +
  geom_col(width = 1) +
  annotate("text", x = pico$data_ult_situacao + days(8), y = pico$total,
           label = paste0(format(pico$data_ult_situacao, "%d/%m"), ": ", fmt_n(pico$total), " atualizações"),
           hjust = 0, size = 3.6) +
  scale_fill_manual(values = c("Aberta em 2026" = "#2C7FB8", "Legado (até 2025)" = "#E6550D"), name = NULL) +
  scale_x_date(date_breaks = "1 month", labels = rotulo_mes) +
  scale_y_continuous(labels = fmt_n) +
  labs(title = "Data da última movimentação de cada demanda",
       subtitle = "Um pico isolado em janeiro, dominado por pedidos antigos", x = NULL, y = "Nº de demandas")

leg_pico <- legado %>% filter(data_ult_situacao == pico$data_ult_situacao)
leg_pico_esc <- leg_pico %>% count(grupo_servico, sort = TRUE) %>% slice(1)