skim(orders)

normalize_city <- function(cities) {
  cities %>%
    str_to_lower() %>%
    str_remove(., paste0("\\s+", brazil_states_regex, "$")) %>%
    str_replace(., ".+@.+\\..+", NA_character_) %>%
    str_extract(., "^[^/\\\\,]+") %>%
    str_remove_all(., "\\(.*?\\)") %>%
    str_replace_all(., "d'|d´|d`", "d'") %>%
    str_remove_all(., "[\\.'ºª´\\^~]") %>%
    str_remove_all(., "^[\\.]+") %>%
    str_replace_all(., "-", " ") %>%
    str_squish() %>%
    str_replace(., paste0("^", brazil_states_regex, "$"), NA_character_)
}

customers <- customers %>% mutate(customer_city = normalize_city(customer_city))
sellers <- sellers %>% mutate(seller_city = normalize_city(seller_city))
geolocation <- geolocation %>% mutate(geolocation_city = normalize_city(geolocation_city))

geolocation_per_zip_code <- geolocation %>%
  group_by(geolocation_zip_code_prefix) %>%
  summarise(
    geolocation_lat = mean(geolocation_lat, na.rm = TRUE),
    geolocation_lng = mean(geolocation_lng, na.rm = TRUE)
  ) %>%
  ungroup()

products <- products %>%
  mutate(
    product_length_cm = ifelse(is.na(product_length_cm), median(product_length_cm, na.rm = TRUE), product_length_cm),
    product_height_cm = ifelse(is.na(product_height_cm), median(product_height_cm, na.rm = TRUE), product_height_cm),
    product_width_cm = ifelse(is.na(product_width_cm), median(product_width_cm, na.rm = TRUE), product_width_cm),
    product_weight_g = ifelse(is.na(product_weight_g), median(product_weight_g, na.rm = TRUE), product_weight_g)
  )

order_items <- order_items %>%
  filter(price < quantile(price, 0.99, na.rm = TRUE) & freight_value < quantile(freight_value, 0.99, na.rm = TRUE))

products <- products %>%
  left_join(category_translation, by = "product_category_name") %>%
  mutate(product_category_name = ifelse(is.na(product_category_name_english), product_category_name, product_category_name_english)) %>%
  select(-product_category_name_english)

payments_agg_valor <- order_payments %>%
  group_by(order_id) %>%
  summarise(valor_total_pedido = sum(payment_value, na.rm = TRUE))

payments_agg_tipo <- order_payments %>%
  filter(payment_sequential == 1) %>%
  select(order_id, tipo_pagamento_principal = payment_type)

payments_final <- payments_agg_valor %>%
  left_join(payments_agg_tipo, by = "order_id")

reviews_final <- order_reviews %>%
  mutate(
    review_comment_message = replace_na(review_comment_message, "sem_comentario"),
    review_comment_title = replace_na(review_comment_title, "sem_comentario"),
    review_creation_date = as_datetime(review_creation_date)
  ) %>%
  group_by(order_id) %>%
  arrange(desc(review_creation_date)) %>%
  slice(1) %>%
  ungroup() %>%
  select(order_id, review_score, review_comment_message)

tb_comercial <- order_items %>%
  select(order_id, order_item_id, product_id, seller_id, price, freight_value) %>%
  left_join(orders %>% select(order_id, order_status, order_purchase_timestamp), by = "order_id") %>%
  left_join(products, by = "product_id") %>%
  left_join(sellers %>% select(seller_id, seller_city, seller_state), by = "seller_id") %>%
  left_join(payments_final, by = "order_id")

geo_cust <- geolocation_per_zip_code %>% 
  rename(lat_cli = geolocation_lat, lng_cli = geolocation_lng)

geo_sell <- geolocation_per_zip_code %>% 
  rename(lat_venda = geolocation_lat, lng_venda = geolocation_lng)

tb_logistica <- order_items %>%
  select(order_id, order_item_id, seller_id, freight_value) %>%
  left_join(orders, by = "order_id") %>%
  left_join(sellers %>% select(seller_id, seller_zip_code_prefix, seller_state), by = "seller_id") %>%
  left_join(customers %>% select(customer_id, customer_zip_code_prefix, customer_state), by = "customer_id") %>%
  left_join(geo_sell, by = c("seller_zip_code_prefix" = "geolocation_zip_code_prefix")) %>%
  left_join(geo_cust, by = c("customer_zip_code_prefix" = "geolocation_zip_code_prefix")) %>%
  mutate(distancia_km = distHaversine(matrix(c(lng_venda, lat_venda), ncol = 2), 
                                      matrix(c(lng_cli, lat_cli), ncol = 2)) / 1000)

tb_clientes <- orders %>%
  left_join(customers, by = "customer_id") %>%
  left_join(payments_final, by = "order_id") %>%
  left_join(reviews_final, by = "order_id") %>%
  group_by(customer_unique_id) %>%
  summarise(
    cidade = first(customer_city),
    estado = first(customer_state),
    total_pedidos = n(),
    total_gasto = sum(valor_total_pedido, na.rm = TRUE),
    data_primeira_compra = min(order_purchase_timestamp),
    data_ultima_compra = max(order_purchase_timestamp),
    nota_media = mean(review_score, na.rm = TRUE)
  ) %>%
  ungroup()

tb_comercial <- tb_comercial %>%
  mutate(
    volume_produto_cm3 = product_length_cm * product_height_cm * product_width_cm,
    densidade_produto = product_weight_g / volume_produto_cm3
  )

tb_logistica <- tb_logistica %>%
  mutate(
    tempo_processamento_horas = as.numeric(difftime(order_approved_at, order_purchase_timestamp, units = "hours")),
    tempo_entrega_total_dias = as.numeric(difftime(order_delivered_customer_date, order_purchase_timestamp, units = "days")),
    atraso_entrega_dias = as.numeric(difftime(order_delivered_customer_date, order_estimated_delivery_date, units = "days")),
    status_sla = case_when(
      atraso_entrega_dias > 0 ~ "Atrasado",
      TRUE ~ "No Prazo/Adiantado"
    )
  )

data_ref <- max(orders$order_purchase_timestamp, na.rm = TRUE) + days(1)

tb_clientes <- tb_clientes %>%
  mutate(
    tipo_cliente = ifelse(total_pedidos > 1, "Recorrente", "Novo"),
    recencia_dias = as.numeric(difftime(data_ref, data_ultima_compra, units = "days")),
    ticket_medio = total_gasto / total_pedidos
  )

get_region <- function(state) {
  case_when(
    str_detect(str_to_lower(state), regex_norte) ~ "Norte",
    str_detect(str_to_lower(state), regex_nordeste) ~ "Nordeste",
    str_detect(str_to_lower(state), regex_centro_oeste) ~ "Centro-Oeste",
    str_detect(str_to_lower(state), regex_sudeste) ~ "Sudeste",
    str_detect(str_to_lower(state), regex_sul) ~ "Sul",
    TRUE ~ "Outros"
  )
}

tb_clientes <- tb_clientes %>% mutate(regiao = get_region(estado))
tb_logistica <- tb_logistica %>% mutate(regiao_cliente = get_region(customer_state), regiao_vendedor = get_region(seller_state))
tb_comercial <- tb_comercial %>% mutate(regiao_vendedor = get_region(seller_state))

tb_comercial <- tb_comercial %>%
  select(-product_name_lenght, -product_description_lenght, -product_photos_qty,
         -product_length_cm, -product_height_cm, -product_width_cm)

tb_logistica <- tb_logistica %>%
  select(-order_approved_at, -order_delivered_carrier_date)

vendas_temporal <- tb_comercial %>%
  mutate(mes_ano = floor_date(order_purchase_timestamp, "month")) %>%
  group_by(mes_ano) %>%
  summarise(
    total_pedidos = n_distinct(order_id),
    total_receita = sum(price, na.rm = TRUE)
  ) %>%
  filter(mes_ano >= "2017-01-01" & mes_ano <= "2018-08-01")

# Cálculo do percentual do pico
vendas_2017 <- vendas_temporal %>% filter(lubridate::year(mes_ano) == 2017)
pico_percent <- round(max(vendas_2017$total_pedidos) / sum(vendas_2017$total_pedidos) * 100, 1)

ggplot(vendas_temporal, aes(x = mes_ano, y = total_pedidos)) +
  geom_line(color = "#2c3e50", size = 1) +
  geom_point(color = "#c0392b", size = 2) +
  geom_smooth(method = "loess", se = FALSE, color = "gray", linetype = "dashed") +
  theme_minimal() +
  labs(title = "Evolução Mensal do Volume de Pedidos",
       x = "Mês", y = "Número de Pedidos") +
  scale_x_datetime(date_breaks = "2 months", date_labels = "%b/%y")

leaflet(tb_logistica %>% sample_n(5000)) %>%
  addTiles() %>%
  addCircleMarkers(lng = ~lng_cli, lat = ~lat_cli, radius = 1, color = "blue", opacity = 0.5) %>%
  addCircleMarkers(lng = ~lng_venda, lat = ~lat_venda, radius = 1, color = "red", opacity = 0.5)

resumo_regional_long <- resumo_regional %>%
  pivot_longer(cols = -regiao_cliente, names_to = "metrica", values_to = "valor")

ggplot(resumo_regional_long, aes(x = regiao_cliente, y = valor, fill = regiao_cliente)) +
  geom_col() +
  facet_wrap(~metrica, scales = "free_y") +
  theme_minimal() +
  labs(title = "Métricas Logísticas por Região", x = "Região", y = "Valor")

top_categorias <- tb_comercial %>%
  group_by(product_category_name) %>%
  summarise(receita = sum(price, na.rm = TRUE)) %>%
  top_n(10, receita)

ggplot(top_categorias, aes(x = reorder(product_category_name, receita), y = receita)) +
  geom_col(fill = "#2c3e50") +
  coord_flip() +
  theme_minimal() +
  labs(title = "Top Categorias por Receita", x = "Categoria", y = "Receita (R$)") +
  scale_y_continuous(labels = scales::dollar_format(prefix = "R$ ", big.mark = ".", decimal.mark = ","))

ggplot(fidelidade, aes(x = "", y = prop, fill = tipo_cliente)) +
  geom_bar(stat = "identity") +
  coord_polar("y") +
  theme_void() +
  labs(title = "Proporção de Clientes por Tipo")

ticket_reg <- tb_clientes %>%
  group_by(regiao) %>%
  summarise(ticket_medio = mean(ticket_medio, na.rm = TRUE)) %>%
  filter(regiao != "Outros")

ggplot(ticket_reg, aes(x = reorder(regiao, ticket_medio), y = ticket_medio, fill = regiao)) +
  geom_col() +
  theme_minimal() +
  labs(title = "Ticket Médio por Região", x = "Região", y = "Ticket (R$)")