#| eval: false
#| echo: true

# Install from CRAN
install.packages("cfbfastR")

# Or install development version from GitHub
# install.packages("devtools")
devtools::install_github("sportsdataverse/cfbfastR")

# Install companion packages
install.packages("tidyverse")
install.packages("gt")
install.packages("ggplot2")
install.packages("httr")
install.packages("jsonlite")

#| eval: false
#| echo: true

# Set API key as environment variable
# Option 1: In .Renviron file (recommended)
# Add this line to .Renviron:
# CFBD_API_KEY=your_key_here

# Option 2: Set in session
Sys.setenv(CFBD_API_KEY = "your_key_here")

# Option 3: Pass directly to functions
pbp <- cfbd_pbp_data(
  year = 2023,
  api_key = "your_key_here"
)

#| label: load-pbp-r
#| message: false
#| warning: false
#| cache: true

# Load required packages for data manipulation and presentation
library(tidyverse)   # Data manipulation and visualization
library(cfbfastR)    # College football data access
library(gt)          # Professional table formatting

# Load play-by-play data for the 2023 season
# This function queries the CFBD API and returns a data frame
# where each row represents a single play from the season
pbp_2023 <- cfbd_pbp_data(
  year = 2023,                # Season to load
  season_type = "regular",    # "regular", "postseason", or "both"
  week = NULL                 # NULL loads all weeks; specify 1-15 for single week
)

# Display basic information about the loaded dataset
# This helps verify the data loaded successfully
cat("Loaded", nrow(pbp_2023), "plays from 2023 season\n")
cat("Number of columns:", ncol(pbp_2023), "\n")

# Count unique teams (offensive and defensive sides)
# College football has 130+ FBS teams, so we expect ~130 unique teams
unique_teams <- n_distinct(c(pbp_2023$pos_team, pbp_2023$def_pos_team))
cat("Unique teams:", unique_teams, "\n")

# Show date range to verify we have the full season
cat("Date range:",
    min(pbp_2023$game_date, na.rm = TRUE), "to",
    max(pbp_2023$game_date, na.rm = TRUE), "\n")

# Display sample plays to understand the data structure
# We select key variables that analysts most commonly use
pbp_2023 %>%
  select(
    game_id,          # Unique identifier for the game
    pos_team,         # Team with possession (offense)
    def_pos_team,     # Defensive team
    down,             # Down (1-4)
    distance,         # Yards needed for first down
    yards_gained,     # Result of the play
    play_type,        # pass, rush, punt, etc.
    EPA               # Expected Points Added
  ) %>%
  head(10) %>%
  gt() %>%
  tab_header(
    title = "Sample College Football Plays",
    subtitle = "2023 Season"
  ) %>%
  fmt_number(
    columns = EPA,
    decimals = 3
  )

#| label: pbp-vars-r
#| message: false
#| warning: false
#| cache: true

# Examine key variables
key_variables <- tibble(
  Category = c(
    "Game Info", "Game Info", "Game Info",
    "Situation", "Situation", "Situation", "Situation",
    "Play Info", "Play Info", "Play Info",
    "Metrics", "Metrics", "Metrics"
  ),
  Variable = c(
    "game_id", "week", "home",
    "down", "distance", "yards_to_goal", "period",
    "play_type", "yards_gained", "play_text",
    "EPA", "success", "wpa"
  ),
  Description = c(
    "Unique game identifier",
    "Week of season (1-15+)",
    "Home team abbreviation",
    "Down (1-4)",
    "Yards to first down",
    "Yards from goal line",
    "Quarter (1-4+, includes OT)",
    "Type of play (pass, rush, etc)",
    "Net yards gained",
    "Play description text",
    "Expected Points Added",
    "Binary success indicator",
    "Win Probability Added"
  )
)

key_variables %>%
  gt() %>%
  tab_header(
    title = "Key College Football Play-by-Play Variables"
  ) %>%
  cols_label(
    Category = "Category",
    Variable = "Variable",
    Description = "Description"
  )

#| label: cfb-nfl-diff-r
#| message: false
#| warning: false
#| cache: true

# Create comparison table
differences <- tribble(
  ~Aspect, ~NFL, ~College,
  "Overtime", "Modified sudden death", "Alternating possessions from 25",
  "Clock rules", "2-minute warning", "No 2-minute warning",
  "Hash marks", "18.5 feet apart", "40 feet apart (wider)",
  "Pass interference", "Spot foul", "15-yard penalty maximum",
  "Targeting", "15 yards", "15 yards + ejection",
  "Down after penalty", "Can award untimed down", "Untimed down on def penalty",
  "Overtime start", "N/A (new rules)", "Starts at 25-yard line",
  "Player names", "Required on jerseys", "Optional in many conferences",
  "Clock stops", "Only on incompletions late", "Stops on 1st down (varies)",
  "Replay review", "Booth review limited", "Booth can review scoring plays"
)

differences %>%
  gt() %>%
  tab_header(
    title = "Key Rule Differences: NFL vs College Football"
  ) %>%
  cols_label(
    Aspect = "Rule Aspect",
    NFL = "NFL Rule",
    College = "College Rule"
  ) %>%
  tab_options(
    table.font.size = px(12)
  )

#| label: team-info-r
#| message: false
#| warning: false
#| cache: true

# Get FBS teams
fbs_teams <- cfbd_team_info(
  year = 2023,
  only_fbs = TRUE
)

# Display team information
fbs_teams %>%
  select(school, conference, division, color, mascot, abbreviation) %>%
  arrange(conference, school) %>%
  head(20) %>%
  gt() %>%
  tab_header(
    title = "FBS Team Information",
    subtitle = "2023 Season - Sample"
  ) %>%
  cols_label(
    school = "School",
    conference = "Conference",
    division = "Division",
    color = "Primary Color",
    mascot = "Mascot",
    abbreviation = "Abbr"
  )

# Count teams by conference
conference_counts <- fbs_teams %>%
  count(conference, sort = TRUE, name = "teams")

cat("\nTeams by Conference (2023):\n")
print(conference_counts)

#| label: realignment-r
#| message: false
#| warning: false
#| cache: true

# Track conference changes over time
# Example: Texas and Oklahoma to SEC
conference_history <- map_df(2020:2024, function(yr) {
  cfbd_team_info(year = yr) %>%
    filter(school %in% c("Texas", "Oklahoma", "UCLA", "USC")) %>%
    select(school, conference) %>%
    mutate(year = yr)
})

conference_history %>%
  pivot_wider(
    names_from = year,
    values_from = conference
  ) %>%
  gt() %>%
  tab_header(
    title = "Conference Realignment Examples",
    subtitle = "Selected schools, 2020-2024"
  )

#| label: divisions-r
#| message: false
#| warning: false
#| cache: true

# Get teams from all divisions
all_teams <- cfbd_team_info(year = 2023)

# Analyze division distribution
division_summary <- all_teams %>%
  mutate(
    division_level = case_when(
      classification == "fbs" ~ "FBS (Division I-A)",
      classification == "fcs" ~ "FCS (Division I-AA)",
      TRUE ~ "Other"
    )
  ) %>%
  count(division_level, name = "teams") %>%
  arrange(desc(teams))

division_summary %>%
  gt() %>%
  tab_header(
    title = "College Football Division Structure",
    subtitle = "2023 Season"
  ) %>%
  cols_label(
    division_level = "Division",
    teams = "Number of Teams"
  )

# Analyze FBS vs FCS matchups
games_2023 <- cfbd_game_info(year = 2023)

# Identify cross-division games
fbs_schools <- all_teams %>%
  filter(classification == "fbs") %>%
  pull(school)

cross_division_games <- games_2023 %>%
  filter(
    (home_team %in% fbs_schools & !away_team %in% fbs_schools) |
    (!home_team %in% fbs_schools & away_team %in% fbs_schools)
  )

cat("\nCross-division FBS vs FCS games in 2023:", nrow(cross_division_games), "\n")
cat("Average point differential:",
    mean(abs(cross_division_games$home_points - cross_division_games$away_points), na.rm = TRUE),
    "points\n")

#| label: competitive-balance-r
#| message: false
#| warning: false
#| cache: true

# Calculate team talent composites
talent <- cfbd_team_talent(year = 2023)

# Analyze talent distribution
talent_summary <- talent %>%
  summarise(
    teams = n(),
    mean_talent = mean(talent, na.rm = TRUE),
    median_talent = median(talent, na.rm = TRUE),
    sd_talent = sd(talent, na.rm = TRUE),
    min_talent = min(talent, na.rm = TRUE),
    max_talent = max(talent, na.rm = TRUE),
    range = max_talent - min_talent,
    top10_avg = mean(talent[order(-talent)][1:10], na.rm = TRUE),
    bottom10_avg = mean(talent[order(talent)][1:10], na.rm = TRUE),
    gap = top10_avg - bottom10_avg
  )

talent_summary %>%
  pivot_longer(everything(), names_to = "metric", values_to = "value") %>%
  gt() %>%
  tab_header(
    title = "FBS Talent Distribution",
    subtitle = "2023 Season - 247Sports Composite"
  ) %>%
  fmt_number(decimals = 2) %>%
  cols_label(
    metric = "Metric",
    value = "Value"
  )

# Identify talent tiers
talent_tiers <- talent %>%
  mutate(
    tier = case_when(
      talent >= quantile(talent, 0.90, na.rm = TRUE) ~ "Elite (Top 10%)",
      talent >= quantile(talent, 0.75, na.rm = TRUE) ~ "Very Good (75-90%)",
      talent >= quantile(talent, 0.50, na.rm = TRUE) ~ "Above Average (50-75%)",
      talent >= quantile(talent, 0.25, na.rm = TRUE) ~ "Below Average (25-50%)",
      TRUE ~ "Low (Bottom 25%)"
    )
  ) %>%
  count(tier, name = "teams")

talent_tiers %>%
  gt() %>%
  tab_header(
    title = "FBS Talent Tiers",
    subtitle = "Distribution of teams by talent level"
  )

#| label: recruiting-r
#| message: false
#| warning: false
#| cache: true

# Get recruiting rankings
recruiting_2024 <- cfbd_recruiting_player(
  year = 2024,
  classification = "HighSchool"
)

# Top recruits
top_recruits <- recruiting_2024 %>%
  arrange(desc(rating)) %>%
  select(name, position, school = committed_to,
         rating, ranking, stars, hometown = city, state = state_province) %>%
  head(25)

top_recruits %>%
  gt() %>%
  tab_header(
    title = "Top 2024 Recruits",
    subtitle = "247Sports Composite Rankings"
  ) %>%
  cols_label(
    name = "Player",
    position = "Pos",
    school = "Committed To",
    rating = "Rating",
    ranking = "National Rank",
    stars = "Stars",
    hometown = "City",
    state = "State"
  ) %>%
  fmt_number(
    columns = rating,
    decimals = 4
  ) %>%
  data_color(
    columns = stars,
    colors = scales::col_numeric(
      palette = c("lightgray", "gold"),
      domain = c(3, 5)
    )
  )

# Team recruiting class rankings
team_recruiting <- cfbd_recruiting_team(year = 2024)

team_recruiting %>%
  arrange(rank) %>%
  select(team, rank, points, avg_rating = average_rating) %>%
  head(20) %>%
  gt() %>%
  tab_header(
    title = "2024 Recruiting Class Rankings"
  ) %>%
  fmt_number(
    columns = c(points, avg_rating),
    decimals = 2
  ) %>%
  cols_label(
    team = "Team",
    rank = "Rank",
    points = "Points",
    avg_rating = "Avg Rating"
  )

#| label: recruiting-trends-r
#| message: false
#| warning: false
#| cache: true

# Get multiple years of recruiting data
recruiting_multi <- map_df(2020:2024, function(yr) {
  cfbd_recruiting_team(year = yr) %>%
    mutate(year = yr)
})

# Top programs by average recruiting rank
top_recruiters <- recruiting_multi %>%
  group_by(team) %>%
  summarise(
    classes = n(),
    avg_rank = mean(rank, na.rm = TRUE),
    avg_points = mean(points, na.rm = TRUE),
    top_10_classes = sum(rank <= 10),
    top_25_classes = sum(rank <= 25),
    .groups = "drop"
  ) %>%
  arrange(avg_rank) %>%
  head(20)

top_recruiters %>%
  gt() %>%
  tab_header(
    title = "Best Recruiting Programs (2020-2024)",
    subtitle = "Based on average class ranking over 5 years"
  ) %>%
  cols_label(
    team = "Team",
    classes = "Classes",
    avg_rank = "Avg Rank",
    avg_points = "Avg Points",
    top_10_classes = "Top 10",
    top_25_classes = "Top 25"
  ) %>%
  fmt_number(
    columns = c(avg_rank, avg_points),
    decimals = 1
  )

#| label: blue-chip-r
#| eval: false
#| echo: true

# Calculate Blue Chip Ratio
# Teams with >50% blue chips have won every national title since 2010

calculate_blue_chip_ratio <- function(team, years) {
  # Get recruiting data for past 4-5 years (typical roster composition)
  recruiting_data <- map_df(years, ~cfbd_recruiting_player(
    year = .x,
    team = team,
    classification = "HighSchool"
  ))

  blue_chips <- recruiting_data %>%
    filter(stars >= 4) %>%
    nrow()

  total_recruits <- nrow(recruiting_data)

  ratio <- blue_chips / total_recruits

  tibble(
    team = team,
    blue_chip_ratio = ratio,
    blue_chips = blue_chips,
    total_recruits = total_recruits
  )
}

# Example for top teams
top_teams <- c("Georgia", "Alabama", "Ohio State", "Michigan")
blue_chip_analysis <- map_df(
  top_teams,
  ~calculate_blue_chip_ratio(.x, 2020:2023)
)

blue_chip_analysis %>%
  arrange(desc(blue_chip_ratio)) %>%
  gt() %>%
  fmt_percent(columns = blue_chip_ratio, decimals = 1)

#| label: transfers-r
#| message: false
#| warning: false
#| cache: true

# Get transfer portal data
transfers_2024 <- cfbd_recruiting_transfer_portal(
  year = 2024
)

# Summary of transfer activity
transfer_summary <- transfers_2024 %>%
  summarise(
    total_transfers = n(),
    avg_rating = mean(rating, na.rm = TRUE),
    five_stars = sum(stars == 5, na.rm = TRUE),
    four_stars = sum(stars == 4, na.rm = TRUE),
    three_stars = sum(stars == 3, na.rm = TRUE),
    with_destination = sum(!is.na(destination)),
    still_uncommitted = sum(is.na(destination))
  )

transfer_summary %>%
  gt() %>%
  tab_header(
    title = "2024 Transfer Portal Summary"
  ) %>%
  fmt_number(
    columns = avg_rating,
    decimals = 4
  ) %>%
  cols_label(
    total_transfers = "Total",
    avg_rating = "Avg Rating",
    five_stars = "5 Stars",
    four_stars = "4 Stars",
    three_stars = "3 Stars",
    with_destination = "Committed",
    still_uncommitted = "Uncommitted"
  )

# Teams gaining most transfers
transfer_destinations <- transfers_2024 %>%
  filter(!is.na(destination)) %>%
  count(destination, sort = TRUE, name = "transfers_in") %>%
  head(20)

transfer_destinations %>%
  gt() %>%
  tab_header(
    title = "Top Transfer Destinations",
    subtitle = "2024 Transfer Portal"
  ) %>%
  cols_label(
    destination = "School",
    transfers_in = "Transfers In"
  )

# Teams losing most players
transfer_origins <- transfers_2024 %>%
  count(origin, sort = TRUE, name = "transfers_out") %>%
  head(20)

# Net transfer balance
transfer_balance <- transfers_2024 %>%
  filter(!is.na(destination)) %>%
  count(destination, name = "in") %>%
  full_join(
    transfers_2024 %>% count(origin, name = "out"),
    by = c("destination" = "origin")
  ) %>%
  mutate(
    in = replace_na(in, 0),
    out = replace_na(out, 0),
    net = in - out
  ) %>%
  arrange(desc(net)) %>%
  rename(team = destination)

transfer_balance %>%
  head(15) %>%
  gt() %>%
  tab_header(
    title = "Net Transfer Balance",
    subtitle = "Incoming minus outgoing transfers (2024)"
  ) %>%
  cols_label(
    team = "Team",
    in = "In",
    out = "Out",
    net = "Net"
  ) %>%
  data_color(
    columns = net,
    colors = scales::col_numeric(
      palette = c("red", "white", "green"),
      domain = c(-20, 20)
    )
  )

#| label: transfer-metrics-r
#| message: false
#| warning: false
#| cache: true

# Position breakdown
position_transfers <- transfers_2024 %>%
  count(position, sort = TRUE) %>%
  head(15)

position_transfers %>%
  ggplot(aes(x = reorder(position, n), y = n)) +
  geom_col(fill = "#D50032", alpha = 0.8) +
  coord_flip() +
  labs(
    title = "Transfer Portal by Position",
    subtitle = "2024 Season",
    x = "Position",
    y = "Number of Transfers",
    caption = "Data: 247Sports via CFBD API"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    panel.grid.major.y = element_blank()
  )

#| label: coaching-r
#| message: false
#| warning: false
#| cache: true

# Get coaching records
coaches_2023 <- cfbd_coaches(year = 2023)

# Current head coaches with longest tenure
tenured_coaches <- coaches_2023 %>%
  mutate(
    tenure = 2023 - first_year + 1,
    win_pct = wins / (wins + losses)
  ) %>%
  arrange(desc(tenure)) %>%
  select(first_name, last_name, school,
         first_year, tenure, wins, losses, win_pct) %>%
  head(20)

tenured_coaches %>%
  gt() %>%
  tab_header(
    title = "Longest-Tenured Active FBS Coaches",
    subtitle = "Through 2023 Season"
  ) %>%
  cols_label(
    first_name = "First",
    last_name = "Last",
    school = "School",
    first_year = "Start",
    tenure = "Years",
    wins = "Wins",
    losses = "Losses",
    win_pct = "Win %"
  ) %>%
  fmt_percent(
    columns = win_pct,
    decimals = 1
  )

# Coaching changes analysis
coaches_2022 <- cfbd_coaches(year = 2022)

# Identify new coaches in 2023
new_coaches_2023 <- coaches_2023 %>%
  filter(first_year == 2023)

cat("\n\nNew head coaches in 2023:", nrow(new_coaches_2023), "\n")

# Show new hires
new_coaches_2023 %>%
  select(first_name, last_name, school) %>%
  gt() %>%
  tab_header(
    title = "New Head Coaches in 2023"
  )

#| label: pipeline-r
#| eval: false
#| echo: true

# Complete College Football Data Pipeline
library(tidyverse)
library(cfbfastR)

build_college_football_database <- function(years, output_dir = "data/cfb") {

  # Create output directory
  dir.create(output_dir, showWarnings = FALSE, recursive = TRUE)

  # Initialize list to store data
  cfb_data <- list()

  for (year in years) {
    cat("Processing", year, "season...\n")

    # 1. Play-by-play data
    cat("  Loading play-by-play data...\n")
    pbp <- safely(cfbd_pbp_data)(year = year)
    if (!is.null(pbp$result)) {
      cfb_data[[paste0("pbp_", year)]] <- pbp$result
    }

    # 2. Team information
    cat("  Loading team information...\n")
    teams <- safely(cfbd_team_info)(year = year, only_fbs = TRUE)
    if (!is.null(teams$result)) {
      cfb_data[[paste0("teams_", year)]] <- teams$result
    }

    # 3. Game information
    cat("  Loading game data...\n")
    games <- safely(cfbd_game_info)(year = year)
    if (!is.null(games$result)) {
      cfb_data[[paste0("games_", year)]] <- games$result
    }

    # 4. Team statistics
    cat("  Loading team statistics...\n")
    team_stats <- safely(cfbd_stats_season_team)(year = year)
    if (!is.null(team_stats$result)) {
      cfb_data[[paste0("team_stats_", year)]] <- team_stats$result
    }

    # 5. Recruiting data
    cat("  Loading recruiting data...\n")
    recruiting <- safely(cfbd_recruiting_team)(year = year)
    if (!is.null(recruiting$result)) {
      cfb_data[[paste0("recruiting_", year)]] <- recruiting$result
    }

    # 6. Talent composites
    cat("  Loading talent data...\n")
    talent <- safely(cfbd_team_talent)(year = year)
    if (!is.null(talent$result)) {
      cfb_data[[paste0("talent_", year)]] <- talent$result
    }

    # 7. Transfer portal (2021 onwards)
    if (year >= 2021) {
      cat("  Loading transfer portal data...\n")
      transfers <- safely(cfbd_recruiting_transfer_portal)(year = year)
      if (!is.null(transfers$result)) {
        cfb_data[[paste0("transfers_", year)]] <- transfers$result
      }
    }

    # 8. Rankings
    cat("  Loading rankings...\n")
    rankings <- safely(cfbd_rankings)(year = year)
    if (!is.null(rankings$result)) {
      cfb_data[[paste0("rankings_", year)]] <- rankings$result
    }

    # 9. Betting lines
    cat("  Loading betting lines...\n")
    lines <- safely(cfbd_betting_lines)(year = year)
    if (!is.null(lines$result)) {
      cfb_data[[paste0("lines_", year)]] <- lines$result
    }

    # 10. Coaches
    cat("  Loading coaching data...\n")
    coaches <- safely(cfbd_coaches)(year = year)
    if (!is.null(coaches$result)) {
      cfb_data[[paste0("coaches_", year)]] <- coaches$result
    }

    # Save individual year
    saveRDS(
      cfb_data[grepl(year, names(cfb_data))],
      file.path(output_dir, paste0("cfb_data_", year, ".rds"))
    )

    cat("  Year", year, "complete.\n\n")

    # Rate limiting
    Sys.sleep(1)
  }

  # Save combined dataset
  saveRDS(cfb_data, file.path(output_dir, "cfb_data_complete.rds"))

  cat("Pipeline complete! Data saved to:", output_dir, "\n")
  return(cfb_data)
}

# Build database for recent years
cfb_db <- build_college_football_database(
  years = 2019:2023,
  output_dir = "data/college_football"
)

# Create integrated analysis dataset
create_integrated_dataset <- function(cfb_data, year) {

  # Extract year-specific data
  teams <- cfb_data[[paste0("teams_", year)]]
  games <- cfb_data[[paste0("games_", year)]]
  team_stats <- cfb_data[[paste0("team_stats_", year)]]
  recruiting <- cfb_data[[paste0("recruiting_", year)]]
  talent <- cfb_data[[paste0("talent_", year)]]

  # Combine game results
  team_records <- games %>%
    mutate(
      home_win = home_points > away_points,
      away_win = away_points > home_points
    ) %>%
    select(home_team, away_team, home_win, away_win,
           home_points, away_points) %>%
    pivot_longer(
      cols = c(home_team, away_team),
      names_to = "location",
      values_to = "team"
    ) %>%
    mutate(
      win = if_else(location == "home_team", home_win, away_win),
      points_for = if_else(location == "home_team", home_points, away_points),
      points_against = if_else(location == "home_team", away_points, home_points)
    ) %>%
    group_by(team) %>%
    summarise(
      games = n(),
      wins = sum(win, na.rm = TRUE),
      losses = games - wins,
      total_points = sum(points_for, na.rm = TRUE),
      total_points_allowed = sum(points_against, na.rm = TRUE),
      avg_points = total_points / games,
      avg_points_allowed = total_points_allowed / games,
      .groups = "drop"
    )

  # Combine into integrated dataset
  integrated <- teams %>%
    left_join(
      team_records,
      by = c("school" = "team")
    ) %>%
    left_join(
      recruiting %>%
        select(team, recruiting_rank = rank, recruiting_points = points),
      by = c("school" = "team")
    ) %>%
    left_join(
      talent %>% select(school, talent),
      by = "school"
    )

  return(integrated)
}

# Example: Create 2023 integrated dataset
integrated_2023 <- create_integrated_dataset(cfb_db, 2023)

# Save integrated dataset
write_csv(integrated_2023, "data/college_football/integrated_2023.csv")

#| label: conference-tempo-adjusted-r
#| eval: false
#| echo: true
#| message: false
#| warning: false

# Calculate conference-adjusted and tempo-free metrics
library(tidyverse)
library(cfbfastR)

# Load play-by-play and team data
pbp_2023 <- cfbd_pbp_data(year = 2023)
teams_2023 <- cfbd_team_info(year = 2023, only_fbs = TRUE)

# Calculate team-level tempo-free offensive metrics
team_offense <- pbp_2023 %>%
  filter(
    play_type %in% c("pass", "run"),  # Regular offensive plays only
    !is.na(EPA)                        # Valid EPA values only
  ) %>%
  group_by(pos_team) %>%
  summarise(
    # Counting stats (tempo-dependent)
    total_plays = n(),
    total_yards = sum(yards_gained, na.rm = TRUE),
    total_epa = sum(EPA, na.rm = TRUE),

    # Tempo-free stats (per-play efficiency)
    yards_per_play = mean(yards_gained, na.rm = TRUE),
    epa_per_play = mean(EPA, na.rm = TRUE),
    success_rate = mean(EPA > 0, na.rm = TRUE),

    # Tempo metric
    plays_per_game = n() / n_distinct(game_id),

    .groups = "drop"
  ) %>%
  rename(team = pos_team)

# Join with conference information
team_metrics <- team_offense %>%
  left_join(
    teams_2023 %>% select(school, conference),
    by = c("team" = "school")
  )

# Calculate conference strength (average EPA per play)
conference_strength <- team_metrics %>%
  group_by(conference) %>%
  summarise(
    teams = n(),
    avg_epa_per_play = mean(epa_per_play, na.rm = TRUE),
    median_epa_per_play = median(epa_per_play, na.rm = TRUE),
    sd_epa = sd(epa_per_play, na.rm = TRUE),
    avg_tempo = mean(plays_per_game, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  arrange(desc(avg_epa_per_play))

# Display conference strength rankings
conference_strength %>%
  gt() %>%
  tab_header(
    title = "Conference Offensive Strength Rankings",
    subtitle = "Tempo-free efficiency metrics, 2023"
  ) %>%
  fmt_number(
    columns = c(avg_epa_per_play, median_epa_per_play, sd_epa, avg_tempo),
    decimals = 3
  ) %>%
  cols_label(
    conference = "Conference",
    teams = "Teams",
    avg_epa_per_play = "Avg EPA/Play",
    median_epa_per_play = "Median EPA/Play",
    sd_epa = "SD EPA",
    avg_tempo = "Avg Plays/Game"
  )

# Calculate opponent-adjusted EPA for each team
team_adjusted <- team_metrics %>%
  left_join(
    conference_strength %>% select(conference, conf_avg_epa = avg_epa_per_play),
    by = "conference"
  ) %>%
  mutate(
    # Adjusted EPA = actual EPA minus conference average
    # Positive = performing above conference average
    epa_above_conference = epa_per_play - conf_avg_epa,

    # Classify tempo
    tempo_category = case_when(
      plays_per_game >= 75 ~ "Fast",
      plays_per_game >= 65 ~ "Average",
      TRUE ~ "Slow"
    )
  )

# Show top teams by adjusted EPA
team_adjusted %>%
  arrange(desc(epa_above_conference)) %>%
  select(team, conference, epa_per_play, epa_above_conference,
         plays_per_game, tempo_category) %>%
  head(20) %>%
  gt() %>%
  tab_header(
    title = "Top Teams by Conference-Adjusted Efficiency",
    subtitle = "EPA per play relative to conference average"
  ) %>%
  fmt_number(
    columns = c(epa_per_play, epa_above_conference, plays_per_game),
    decimals = 3
  )