library(reactable); library(reactablefmtr)
library(tidytable); library(data.table)

area_table <- fread('orders.csv') %>%
  mutate(across(contains('moment'),lubridate::mdy_hms),
         date = lubridate::date(order_moment_created),
         week = lubridate::floor_date(date, 'week')) %>% 
  left_join(fread('payments.csv') %>% 
              summarise(payment = sum(payment_amount),.by = payment_order_id),
            by = 'payment_order_id') %>% 
  left_join(
    fread('stores.csv') %>% select(store_id,hub_id) %>% 
      left_join( fread('hubs.csv') %>% 
                 mutate(hub_city = case_when(hub_state == 'SP' ~ 'SAO PAULO', 
                                             TRUE ~ hub_city)) %>% 
                   select(hub_id,hub_city), by = 'hub_id') %>% 
      select(store_id,hub_city),
  by = 'store_id') %>% filter(order_status != 'CANCELED') %>% 
  summarise(Sales = sum(order_amount), Payment = sum(payment, na.rm=T),
            Orders = uniqueN(order_id),
            .by = c(hub_city,week)) %>% 
  summarise(Revenue = Sales[week == max(week)],
            Payment = Payment[week == max(week)],
            Orders = Orders[week == max(week)],
            Trend = list(Sales/1e3),
            .by = hub_city) %>% mutate(Cont = Revenue/sum(Revenue)) %>% 
  reactable(pagination = F,searchable = F,highlight = T,outlined = T,compact = T,
            fullWidth = F, theme = fivethirtyeight(), borderless = TRUE, 
            columns = list(
              hub_city = colDef(name = "Location", width = 80, align = 'left'),
              Trend = colDef( name = "Sales('000)", align = "center", width = 170,
                  cell = react_sparkline(select(.,Trend),decimals=0, height = 50,
                             show_area = TRUE, labels = c("min","max"), show_line = TRUE,
                             highlight_points = highlight_points(min="red",max="blue"),
                             line_color = "darkgreen", statline = "mean",
                             statline_color = "chocolate", tooltip_type = 2)  ),
              Revenue = colDef( name = "Total Sales", width=90, align = "center",
                  cell = color_tiles(select(.,Revenue), text_color = "#373737",
                                     colors = "blue4", opacity = 0.75, box_shadow = TRUE,
                                      number_fmt = scales::label_dollar(accuracy=0.01,
                                              scale_cut = scales::cut_short_scale()) )),
              Cont = colDef( name = "Sales Cont(%)", width = 100, align = "center",
                  cell = gauge_chart(select(.,Cont), size =1, text_size = 10, 
                                     max_value = 0.8,
                                     number_fmt = scales::label_percent(accuracy=0.1),
                                     fill_color = 'darkblue', text_color = '#373737' )),
              Payment = colDef(name="Payments", width=90, align = "center",
                  cell = color_tiles(select(.,Payment), text_color = "#373737",
                                     colors = "#ffffdf", opacity = 0.75, box_shadow = TRUE,
                                      number_fmt = scales::label_dollar(accuracy=0.01,
                                              scale_cut = scales::cut_short_scale()) ))
              )) %>% 
  add_title(title="Location Summary",align="left", font_color="darkblue", font_size = 20, 
            font_weight = "bold")