#| label: setup
#| message: false
#| warning: false

library(tidyverse)
library(crosstalk)
library(DT)


#| label: read-from-local-file

# The path to the local data file
# The file is in a subfolder called "data"
file_path <- "data/sub-est2024.csv"

# Read the data from the CSV file
# The 'tryCatch' block will show a helpful message if the file is not found.
local_data <- tryCatch({
  # Explicitly set the SUMLEV column to be read as character to preserve leading zeros
  # Added locale to handle "invalid UTF-8" warnings by specifying the file encoding.
  read_csv(file_path, 
           col_types = cols(SUMLEV = col_character()),
           locale = locale(encoding = "latin1"))
}, error = function(e) {
  message("Error reading file: ", e$message)
  message("\nPlease make sure '", file_path, "' exists and is a valid CSV file.")
  # Return an empty tibble or NULL if the file can't be read
  tibble() 
})




 local_data <- local_data %>%
    mutate(
      sumlev_desc = case_when(
        SUMLEV == "040" ~ "State",
        SUMLEV == "050" ~ "County",
        SUMLEV == "061" ~ "Minor Civil Division",
        SUMLEV == "071" ~ "Minor Civil Division place part",
        SUMLEV == "157" ~ "County place part",
        SUMLEV == "162" ~ "Incorporated place",
        SUMLEV == "170" ~ "Consolidated city",
        SUMLEV == "172" ~ "Consolidated city -- place within consolidated city",
        TRUE ~ "Other" # A default for any values not in the key
      )
    )


  ranked_states <- local_data %>%
    filter(SUMLEV == "040") %>%
    arrange(desc(ESTIMATESBASE2020)) %>%
    mutate(Rank = row_number()) %>%
    select(Rank, STNAME, ESTIMATESBASE2020)

  # Assign the 16th ranked state name to a variable
  state_16 <- ranked_states$STNAME[16]

  # Display the table
  knitr::kable(
    ranked_states,
    caption = "State Population for 2020 Census, in Descending Order",
    col.names = c("Rank", "State Name", "2020 Population Base"),
    format.args = list(big.mark = ","))


  ranked_cities_by_state_all <- local_data %>%
    distinct(STNAME, NAME, ESTIMATESBASE2020, .keep_all = TRUE) %>%
    filter(SUMLEV %in% c( "162", "61", "50")) %>%
    group_by(STNAME) %>%
    arrange(desc(ESTIMATESBASE2020)) %>%
    mutate(Rank = row_number()) %>%
    filter(Rank == 16 ) %>%
    ungroup() %>%
    select(STNAME, NAME, ESTIMATESBASE2020) %>%
    arrange(STNAME)

    ranked_cities_by_state_all %>%
      mutate(Order = row_number()) %>%
  relocate(Order) %>%
      
    knitr::kable(
    caption = "16th Largest City or Place by 2020 Population Within Each State order alphabetically by State Name",
    col.names = c("Order", "State Name", "Place Name", "2020 Census Population"),
    format.args = list(big.mark = ",")
  )
    
    ranked_cities_by_state_by_pop <-ranked_cities_by_state_all %>% 
      arrange(desc(ESTIMATESBASE2020)) %>% mutate(Order = row_number()) %>%
  relocate(Order) 
    

    knitr::kable(ranked_cities_by_state_by_pop ,
    caption = "16th Largest City or Place by 2020 Census Population Within Each State ordered by Population Size",
    col.names = c("Order", "State Name", "Place Name", "2020 Census Population"),
    format.args = list(big.mark = ",")
  )
    
    



  ranked_cities_by_iowa <- local_data %>%
  filter(STNAME == "Iowa") %>%
filter(SUMLEV %in% c( "162", "61", "50")) %>%
    arrange(desc(POPESTIMATE2024)) %>%
    mutate(Rank = row_number()) %>%
    filter(Rank <= 25 ) %>%
   
    select(
      NAME,
      Rank,
      ESTIMATESBASE2020,
    
      POPESTIMATE2021,
      POPESTIMATE2022,
      POPESTIMATE2023,
      POPESTIMATE2024
    ) %>%
  arrange(Rank) %>%
  
    knitr::kable(
      caption = "Population Data for Iowa",
       col.names = c("Place Name", "Rank", "2020 Pop. Est.", "2021 Pop. Est.", "2022 Pop. Est.", "2023 Pop. Est.", "2024 Pop. Est."),
      format.args = list(big.mark = ",")
    )

 ranked_cities_by_iowa


local_data %>%
    filter(SUMLEV %in% c("162", "61", "50")) %>%
    group_by(STNAME) %>%
    summarise(Count = n(), .groups = 'drop') %>% 
    arrange(Count) %>%
    knitr::kable(
      caption = "Count of Selected Places per State", 
      col.names = c("State Name", "Selected Places")
    )


#| label: interactive-city-table
#| echo: false
#| warning: false
#| message: false
if (nrow(local_data) > 0) {
  # Prepare the data: Top 25 cities/places within each state
  top_25_cities <- local_data %>%
#    filter(SUMLEV %in% c("162", "170", "172")) %>%
    filter(SUMLEV %in% c( "162", "61", "50")) %>%
    group_by(STNAME) %>%
    arrange(desc(POPESTIMATE2020)) %>%
    mutate(Rank = row_number()) %>%
    filter(Rank <= 25) %>%
    ungroup() %>%
    select(
      `State` = STNAME,
      `Rank` = Rank,
      `Place Name` = NAME,
      `2020 Population Estimate` = POPESTIMATE2020
    )

  # Create a shared data object for crosstalk
  shared_cities <- crosstalk::SharedData$new(top_25_cities)

  # Create the interactive UI using bscols for a sidebar layout
  crosstalk::bscols(
    widths = c(3, 9),
    crosstalk::filter_select(
      id = "state_filter",
      label = "Select a State:",
      sharedData = shared_cities,
      group = ~State
    ),
    DT::datatable(
      shared_cities,
      caption = "Top 25 Cities and Places per State by Population for 2020 Census Data",
      options = list(pageLength = 25, lengthMenu = c(10, 25, 50)),
      class = "cell-border stripe",
      rownames = FALSE
    ) %>%
      DT::formatRound(columns = "2020 Population Estimate", digits = 0, mark = ",")
  )
} else {
  "Data could not be loaded."
}