In jacciz/shiny_wisdot_crash_dashboard: WisDOTcrashdashboard

library(dplyr)
pool <- pool::dbPool(RSQLite::SQLite(), dbname = "inst/app/www/crash_db.db")

crash_df <-DBI::dbReadTable(pool, "2017crash")
flag_df <-DBI::dbReadTable(pool, "2017crsh_flags") |> select(-c(CRSHDATE, CNTYCODE, CRSHSVR))

color_map_svr <- c("Fatal"="#DB7E65", "Injury"="#4AAECF", "Property Damage"="#44DBAE")

crshflag_selected_inputs <-
  c("speed_flag", "distracted_flag", "teendriver_flag", "olderdriver_flag", "CYCLFLAG",
    "PEDFLAG", "BIKEFLAG", "singlevehflag", "lanedepflag","deer_flag","intersection_flag", "impaired_flag")

count_crash_flag <-
  function(crash_df, flag, by_variables = c("CRSHSVR")) {
    # crash_df[get(flag) %in% c("Yes", "Y"), .(crash_count = .N, flag_type = flag), by = by_variables]

    crash_df |>
      dplyr::filter(!!rlang::sym(flag) %in% c("Yes", "Y")) |> group_by(!!rlang::sym(by_variables)) |>
      dplyr::count() |>
      mutate(flag_type = flag)
  }


    crash_w_flags = dplyr::left_join(crash_df, flag_df, by = "CRSHNMBR") #CRSHDATE   CNTYCODE CRSHSVR 
    crsh_svr_count =crash_df |> dplyr::count(CRSHSVR, name = "total")
    # print(count_crash_flag(crash_w_flags, flag = "speed_flag"))
    # print(crash_w_flags)
    flag_count = 
    purrr:::map_df(
      crshflag_selected_inputs,
      count_crash_flag,
      crash_df = crash_w_flags
      # by_variables = c()
    ) |> dplyr::left_join(crsh_svr_count, by = "CRSHSVR")

flag_count = flag_count |> dplyr::mutate(perc = n / total, flag_type = dplyr::case_when(
  # flag_type == "ALCFLAG" ~ "Alcohol",
  # flag_type == "DRUGFLAG" ~ "Drug",
  flag_type == "speed_flag" ~ "Speed",
  flag_type == "distracted_flag" ~ "Distracted driver",
  flag_type == "teendriver_flag" ~ "Teen driver",
  flag_type == "olderdriver_flag" ~ "Older driver",
  flag_type == "CYCLFLAG" ~ "Motorcycle",
  flag_type == "PEDFLAG" ~ "Pedestrian",
  flag_type == "BIKEFLAG" ~ "Bicycle",
  flag_type == "singlevehflag" ~ "Single vehicle",
  flag_type == "lanedepflag" ~ "Lane Departure",      
  flag_type == "deer_flag" ~ "Deer",
  flag_type == "intersection_flag" ~ "Intersection",
  flag_type == "impaired_flag" ~ "Drug/alcohol impaired driver",
  # TRUE ~ flag_type
  TRUE ~ "NA"
))

plotly::plot_ly(
  flag_count,
  type = 'bar',
  # x = ~ scales::percent(perc, 1),
  x = ~ perc*100,
  y = ~ reorder(flag_type, perc, sum),
  color = ~ CRSHSVR,
  colors = ~ rev(color_map_svr),
  orientation = 'h',
  hovertemplate = paste('<br>%{x:.0f}% of total crashes<br>',
                        '<b>%{y: .0f}<b>')
) |> plotly::layout(xaxis = list(
                     zerolinecolor = '#ffff',
                     zerolinewidth = 2,
                     gridcolor = 'ffff',
                     range=list(0, 100)))