Avocado Atlas Sweep

suppressPackageStartupMessages(library(htmltools))
suppressPackageStartupMessages(library(jsonlite))
suppressPackageStartupMessages(library(plotly))
suppressPackageStartupMessages(library(DT))
suppressPackageStartupMessages(library(htmlwidgets))
suppressPackageStartupMessages(library(arrow))
suppressPackageStartupMessages(library(knitr))
suppressPackageStartupMessages(library(rmarkdown))
suppressPackageStartupMessages(library(dplyr))
suppressPackageStartupMessages(library(tidyr))
suppressPackageStartupMessages(library(ggplot2))
suppressPackageStartupMessages(library(stringr))
suppressPackageStartupMessages(library(lubridate))
suppressPackageStartupMessages(library(broom))
suppressPackageStartupMessages(library(survival))
suppressPackageStartupMessages(library(Matrix))
suppressPackageStartupMessages(library(cluster))
suppressPackageStartupMessages(library(data.table))

initial_rows <- nrow(df)
  final_rows   <- nrow(df)
  rows_removed <- 0L

df$date_parsed <- lubridate::ymd(df$date)
  df$year_month  <- lubridate::floor_date(df$date_parsed, "month")
  df$yyyymm      <- format(df$year_month, "%Y%m")
  df$month_label <- format(df$year_month, "%b %Y")

  price_over_time <- df %>%
    dplyr::group_by(yyyymm, month_label) %>%
    dplyr::summarise(
      average_price = mean(average_price, na.rm = TRUE),
      n = dplyr::n(),
      .groups = "drop"
    ) %>%
    dplyr::filter(n >= 5) %>%
    dplyr::arrange(yyyymm) %>%
    dplyr::select(date_period = month_label, average_price)

price_by_type <- df %>%
    dplyr::mutate(type = tools::toTitleCase(type)) %>%
    dplyr::select(type, average_price)

df$region_display <- gsub("([a-z])([A-Z])", "\\1 \\2", df$region)

  price_by_region_all <- df %>%
    dplyr::group_by(region_display) %>%
    dplyr::summarise(average_price = mean(average_price, na.rm = TRUE), .groups = "drop") %>%
    dplyr::arrange(dplyr::desc(average_price))

  n_regions <- nrow(price_by_region_all)
  if (n_regions > 12) {
    price_by_region <- dplyr::bind_rows(
      dplyr::slice(price_by_region_all, 1:12),
      data.frame(
        region_display = paste0("Other(", n_regions - 12, ")"),
        average_price = mean(price_by_region_all$average_price[13:n_regions])
      )
    )
  } else {
    price_by_region <- price_by_region_all
  }

region_summary <- df %>%
    dplyr::group_by(region_display) %>%
    dplyr::summarise(
      total_volume = sum(total_volume, na.rm = TRUE),
      average_price = mean(average_price, na.rm = TRUE),
      observation_count = dplyr::n(),
      .groups = "drop"
    ) %>%
    dplyr::arrange(dplyr::desc(total_volume))

tpl_vars <- list(
    n_observations_fmt = format(final_rows, big.mark = ","),
    n_regions = n_regions,
    n_types = length(unique(df$type)),
    date_range = paste0(
      format(min(df$date_parsed, na.rm = TRUE), "%B %d, %Y"),
      " to ",
      format(max(df$date_parsed, na.rm = TRUE), "%B %d, %Y")
    ),
    price_min_str = sprintf("$%.2f", price_range_min),
    price_max_str = sprintf("$%.2f", price_range_max),
    price_avg_str = sprintf("$%.2f", mean(df$average_price, na.rm = TRUE)),
    organic_premium_str = sprintf("%.1f%%", if (!is.na(organic_premium_pct)) organic_premium_pct else 0),
    organic_avg_str = sprintf("$%.2f", if (length(organic_price) > 0) organic_price else 0),
    conventional_avg_str = sprintf("$%.2f", if (length(conventional_price) > 0) conventional_price else 0)
  )

shared <- compute_shared(df, params)

n_months <- nrow(pot)

  # Format currency helper (base-R, no scales package)
  fmt_currency <- function(x) {
    paste0("$", formatC(x, format = "f", digits = 2, big.mark = ","))
  }

  if (n_months > 0) {
    first_price <- pot$average_price[1]
    last_price <- pot$average_price[n_months]
    peak_idx <- which.max(pot$average_price)
    peak_price <- pot$average_price[peak_idx]
    peak_month <- pot$date_period_label[peak_idx]
    trough_idx <- which.min(pot$average_price)
    trough_price <- pot$average_price[trough_idx]
    trough_month <- pot$date_period_label[trough_idx]

    displayed_range <- paste0(
      pot$date_period_label[1], " to ",
      pot$date_period_label[n_months]
    )

    # Trend direction: compare first 3 vs last 3 months
    if (n_months >= 3) {
      first3_mean <- mean(pot$average_price[1:min(3, n_months)], na.rm = TRUE)
      last3_mean <- mean(pot$average_price[max(1, n_months - 2):n_months], na.rm = TRUE)
      if (last3_mean > first3_mean * 1.1) {
        trend <- "upward trend, reflecting rising avocado prices."
      } else if (last3_mean < first3_mean * 0.9) {
        trend <- "downward trend, indicating price decreases."
      } else {
        trend <- "relatively stable pattern with moderate fluctuations."
      }
    } else {
      trend <- "limited data for trend assessment."
    }
  } else {
    first_price <- last_price <- peak_price <- trough_price <- 0
    peak_month <- trough_month <- displayed_range <- "N/A"
    trend <- "no data available."
  }

  # Build narrative text
  text <- paste0(
    "Monthly average avocado prices span ", n_months, " periods(",
    displayed_range, "). Peak price of ", fmt_currency(peak_price),
    " occurred in ", peak_month, ". The lowest price was ",
    fmt_currency(trough_price), " in ", trough_month, ". ",
    "The first period recorded ", fmt_currency(first_price),
    " and the final period ", fmt_currency(last_price), ". ",
    "The market exhibits an overall ", trend
  )

  # Append capping note if applied
  if (capped) {
    text <- paste0(
      text, " (Displayed prices capped at q99 = ",
      fmt_currency(cap_value), " for clarity.)"
    )
  }

  # Prepare output data frame with ISO-8601 date_period and capped average_price
  data_out <- data.frame(
    date_period = pot$date_period,
    average_price = pot$average_price,
    stringsAsFactors = FALSE
  )

  list(
    title = "Price Trend Over Time",
    description = "Monthly average avocado prices over time",
    text = text,
    columns = list(
      date_period = list(role = "temporal"),
      average_price = list(role = "value", format = "currency", symbol = "$")
    ),
    data = list(price_over_time = data_out)
  )
}

# Card: regional_price_mix (horizontal_bar)
# Returns: list(title, description, text, data)

card_regional_price_mix <- function(shared, df, params) {
  # Extract and rename column to match spec
  card_df <- shared$price_by_region %>%
    dplyr::rename(region = region_display)

  # Compute narrative stats from ALL regions (not just displayed top-12)
  # to avoid claiming "Other" aggregation is the lowest individual region
  all_region_stats <- df %>%
    dplyr::mutate(region = gsub("([a-z])([A-Z])", "\\1 \\2", region)) %>%
    dplyr::group_by(region) %>%
    dplyr::summarise(average_price = mean(average_price, na.rm = TRUE), .groups = "drop") %>%
    dplyr::arrange(dplyr::desc(average_price))

  n_regions <- nrow(all_region_stats)

  if (n_regions > 0) {
    # Highest from all regions
    highest_region <- all_region_stats$region[1]
    highest_price <- all_region_stats$average_price[1]
    # Lowest from all regions (true minimum, not "Other" aggregation)
    lowest_region <- all_region_stats$region[n_regions]
    lowest_price <- all_region_stats$average_price[n_regions]
    price_spread <- highest_price - lowest_price
    avg_price <- mean(all_region_stats$average_price, na.rm = TRUE)
  } else {
    highest_region <- "N/A"
    highest_price <- 0
    lowest_region <- "N/A"
    lowest_price <- 0
    price_spread <- 0
    avg_price <- 0
  }

  # Check for heavy-tailed outliers (monetary_card rule 1)
  display_note <- ""
  if (n_regions > 0) {
    p99_price <- quantile(card_df$average_price, 0.99, na.rm = TRUE)
    max_price <- max(card_df$average_price, na.rm = TRUE)

    if (max_price > p99_price && (max_price / p99_price) >= 3) {
      # Cap for display at p99
      card_df$average_price <- pmin(card_df$average_price, p99_price)
      display_note <- paste0(" (capped at 99th percentile: $", round(p99_price, 2), " for display)")
    }
  }

  # LAT-59 #1 (categorical_axis #6): For horizontal_bar ranking card,
  # sort ASCENDING so highest bars render at TOP
  card_df <- card_df %>%
    dplyr::arrange(average_price)

  # Format currency (monetary_card rule 3)
  fmt_dollar <- function(x) {
    paste0("$", formatC(x, format = "f", big.mark = ",", digits = 2))
  }

  # Build narrative from actual computed values
  narrative_text <- paste0(
    "Regional avocado prices vary substantially across the ",
    n_regions,
    " regions analyzed. ",
    highest_region,
    " commands the highest average price at ",
    fmt_dollar(highest_price),
    ", while ",
    lowest_region,
    " has the lowest at ",
    fmt_dollar(lowest_price),
    ". The spread between highest and lowest is ",
    fmt_dollar(price_spread),
    ", reflecting significant regional pricing variation. ",
    "The average price across all regions is ",
    fmt_dollar(avg_price),
    ".",
    display_note
  )

  list(
    title = "Average Price by Region",
    description = paste0(
      "Regional variation in average avocado prices. ",
      "Shows all regions ranked by average price, with top 12 distinct regions plus &#x27;Other' category."
    ),
    text = narrative_text,
    data = list(
      price_by_region = card_df
    )
  )
}

# Card: regional_summary (table)
# Returns: list(title, description, text, data)
# Displays regional aggregation: volume, average price, observation count per region

card_regional_summary <- function(shared, df, params) {
  # Extract pre-computed regional summary table (sorted by volume descending)
  region_data <- shared$region_summary

  # Validate data availability
  if (is.null(region_data) || nrow(region_data) == 0) {
    return(list(
      title = "Regional Price and Volume Summary",
      description = "Summary of regional market size, pricing, and sampling depth",
      text = "Insufficient data to compute regional summary.",
      data = list()
    ))
  }

  # Compute statistics for narrative
  n_regions <- nrow(region_data)
  top_region <- region_data$region_display[1]
  top_volume <- region_data$total_volume[1]

  # Price and observation statistics across all regions
  price_max_region <- region_data$region_display[which.max(region_data$average_price)]
  price_max <- max(region_data$average_price, na.rm = TRUE)
  price_min_region <- region_data$region_display[which.min(region_data$average_price)]
  price_min <- min(region_data$average_price, na.rm = TRUE)
  price_spread <- price_max - price_min

  total_obs <- sum(region_data$observation_count, na.rm = TRUE)
  avg_obs_per_region <- round(mean(region_data$observation_count, na.rm = TRUE), 0)

  # Volume concentration: what % of total is top region?
  total_volume <- sum(region_data$total_volume, na.rm = TRUE)
  top_volume_pct <- round(100 * top_volume / total_volume, 1)

  # Build narrative addressing insight focus: market size, pricing tiers, observation depth
  regional_summary_text <- paste0(
    "The ",
    n_regions,
    " regions in this analysis show substantial variation in market size and pricing. ",
    top_region,
    " dominates by volume with ",
    format(round(top_volume, 0), big.mark = ","),
    " units—approximately ",
    top_volume_pct,
    "% of the total market across all regions. ",
    "Regional average prices span from ",
    sprintf("$%.2f", price_min),
    " in ",
    price_min_region,
    " to ",
    sprintf("$%.2f", price_max),
    " in ",
    price_max_region,
    ", a spread of ",
    sprintf("$%.2f", price_spread),
    " that reflects meaningful pricing tier variation across geographies. ",
    "Observation depth per region averages ",
    format(avg_obs_per_region, big.mark = ","),
    " weekly samples, with ",
    format(total_obs, big.mark = ","),
    " total observations distributed across all regions to support consistent regional estimates."
  )

  # Prepare display table: rename columns for readability
  display_table <- region_data %>%
    dplyr::rename(
      "Region" = region_display,
      "Total Volume" = total_volume,
      "Average Price" = average_price,
      "Observation Count" = observation_count
    )

  list(
    title = "Regional Price and Volume Summary",
    description = "Summary of regional market size, pricing, and sampling depth",
    text = regional_summary_text,
    data = list(region_summary = display_table)
  )
}

# Card: tldr (tldr)
# Returns: tldr -> list(title, description, metrics, text);
#          other -> list(title, description, text, data)

card_tldr <- function(shared, df, params) {
  tv <- shared$tpl_vars
  metrics <- shared$metrics
  price_over_time <- shared$price_over_time
  price_by_type <- shared$price_by_type

  # Compute price trend direction
  if (nrow(price_over_time) >= 2) {
    first_price <- price_over_time$average_price[1]
    last_price <- price_over_time$average_price[nrow(price_over_time)]
    price_change <- last_price - first_price
    trend_direction <- if (price_change > 0.1) "increased" else if (price_change < -0.1) "decreased" else "remained relatively stable"
    price_change_pct <- round(100 * price_change / first_price, 1)
  } else {
    trend_direction <- "stable"
    price_change_pct <- 0
  }

  # Compute regional price variance (max - min) from all individual regions (not rolled-up "Other")
  # Match the regional_summary card's use of all actual regions, not the top-12+Other aggregation
  all_region_stats <- df %>%
    dplyr::mutate(region = gsub("([a-z])([A-Z])", "\\1 \\2", region)) %>%
    dplyr::group_by(region) %>%
    dplyr::summarise(average_price = mean(average_price, na.rm = TRUE), .groups = "drop") %>%
    dplyr::arrange(dplyr::desc(average_price))

  if (nrow(all_region_stats) >= 2) {
    regional_max <- max(all_region_stats$average_price, na.rm = TRUE)
    regional_min <- min(all_region_stats$average_price, na.rm = TRUE)
    regional_spread <- regional_max - regional_min
    highest_region <- all_region_stats$region[1]
    lowest_region <- all_region_stats$region[nrow(all_region_stats)]
  } else {
    regional_spread <- 0
    highest_region <- "N/A"
    lowest_region <- "N/A"
  }

  # Compute type comparison
  type_stats <- price_by_type %>%
    dplyr::group_by(type) %>%
    dplyr::summarise(
      mean_price = mean(average_price, na.rm = TRUE),
      count = dplyr::n(),
      .groups = "drop"
    ) %>%
    dplyr::arrange(dplyr::desc(mean_price))

  organic_premium_pct <- metrics$Organic_Premium_Pct %||% 0

  # Assemble narrative using computed values only
  tldr_text <- paste0(
    "Analysis of ",
    tv$n_observations_fmt,
    " weekly avocado price observations across ",
    tv$n_regions,
    " regions and ",
    tv$n_types,
    " product types spanning ",
    tv$date_range,
    " reveals that average prices ",
    trend_direction,
    " by approximately ",
    sprintf("%.1f%%", price_change_pct),
    " over the period. Prices ranged from ",
    tv$price_min_str,
    " to ",
    tv$price_max_str,
    ", with regional variation of ",
    sprintf("$%.2f", regional_spread),
    " between ",
    lowest_region,
    " (lowest) and ",
    highest_region,
    " (highest). Organic avocados commanded a premium of ",
    tv$organic_premium_str,
    " compared to conventional varieties, averaging ",
    tv$organic_avg_str,
    " versus ",
    tv$conventional_avg_str,
    " respectively."
  )

  list(
    title = "Executive Summary",
    description = "Headline insights on price trends, regional variation, and type comparison",
    metrics = metrics,
    text = tldr_text
  )
}