suppressPackageStartupMessages({
  library(data.table)
  library(ggplot2)
  library(ggtext)
  library(DBI)
  library(duckdb)
  library(grid)
})
.linkedin_dir <- tryCatch(
  dirname(normalizePath(sub("--file=", "", grep("--file=", commandArgs(FALSE), value = TRUE)[1]))),
  error = function(e) getwd()
)
source(file.path(.linkedin_dir, "chart_theme.R"))

db_path <- file.path(
  path.expand(Sys.getenv("LAHMANS_DBDIR", "~/Documents/Data/baseball")),
  "baseball.duckdb"
)
con <- dbConnect(duckdb(), db_path, read_only = TRUE)
on.exit(dbDisconnect(con, shutdown = TRUE))

# Dead money: salary > $1M with zero/negative WAR — aggregated to franchise level
dead <- dbGetQuery(con, "
  WITH fl AS (
    SELECT t.teamID, t.franchID
    FROM (
      SELECT teamID, franchID,
        ROW_NUMBER() OVER (PARTITION BY teamID ORDER BY yearID DESC) AS rn
      FROM Teams
    ) t WHERE t.rn = 1
  ),
  total_payroll AS (
    SELECT fl.franchID,
      SUM(s.salary) / 1e6       AS total_payroll_M,
      MIN(s.yearID)             AS first_year,
      MAX(s.yearID)             AS last_year,
      COUNT(DISTINCT s.yearID)  AS n_seasons
    FROM SalariesAll s
    JOIN fl ON s.teamID = fl.teamID
    WHERE s.is_actual = TRUE AND s.yearID != 2020 AND s.salary >= 1000000
    GROUP BY fl.franchID
  ),
  dead AS (
    SELECT fl.franchID,
      SUM(s.salary) / 1e6 AS dead_money_M,
      COUNT(*)             AS dead_contracts
    FROM SalariesAll s
    JOIN fl ON s.teamID = fl.teamID
    LEFT JOIN PlayerWAR w USING (playerID, yearID)
    WHERE s.is_actual = TRUE
      AND s.yearID != 2020
      AND s.salary >= 1000000
      AND (w.total_war IS NULL OR w.total_war <= 0)
    GROUP BY fl.franchID
  )
  SELECT
    tp.franchID,
    ROUND(tp.total_payroll_M, 1)  AS total_payroll_M,
    ROUND(d.dead_money_M, 1)      AS dead_money_M,
    d.dead_contracts,
    tp.n_seasons,
    ROUND(d.dead_money_M / tp.total_payroll_M * 100, 1) AS pct_dead,
    tp.first_year, tp.last_year
  FROM total_payroll tp
  JOIN dead d USING (franchID)
  ORDER BY pct_dead DESC
")
dt <- as.data.table(dead)

# Map franchID to common short names
dt[, short := TEAM_SHORT[franchID]]
dt[is.na(short), short := franchID]

# Exclude short-lived franchises with <15 seasons of data
dt <- dt[n_seasons >= 15]
setorder(dt, pct_dead)
dt[, short := factor(short, levels = short)]

dt[, short_md := factor(as.character(short), levels = as.character(short))]

# League median
median_pct <- median(dt$pct_dead)
dt[, above_median := pct_dead > median_pct]

# Total dead money in absolute terms
total_dead_B <- sum(dt$dead_money_M) / 1000
n_teams <- nrow(dt)
year_range <- paste0(min(dt$first_year), "--", max(dt$last_year))

p <- ggplot(dt, aes(x = pct_dead, y = short_md,
                    fill = above_median)) +
  geom_col(width = 0.7) +
  geom_vline(xintercept = median_pct, linetype = "dashed",
             color = "gray40", linewidth = 0.6) +
  annotate("text", x = median_pct + 0.4, y = 1.5,
           label = paste0("Median\n", median_pct, "%"),
           hjust = 0, size = 3, color = "gray40") +
  scale_fill_manual(values = c("FALSE" = "#2166AC", "TRUE" = "#D6604D"),
                    guide = "none") +
  scale_x_continuous(labels = function(x) paste0(x, "%"),
                     expand = expansion(mult = c(0, 0.05))) +
  geom_text(aes(label = paste0(pct_dead, "%")),
            hjust = -0.15, size = 2.8, color = "gray20") +
  labs(
    title = "Dead Money Burden by Franchise",
    subtitle = sprintf(
      "Salary paid to players with \u22640 WAR or no measurable output (salary \u2265$1M)\n%d franchises \u00b7 %s \u00b7 $%.1fB total dead money identified",
      n_teams, year_range, total_dead_B
    ),
    x = "Dead Money as % of Total Payroll",
    y = NULL,
    caption = paste0(
      "Dead money = salary \u2265$1M where total_war \u22640 or no Fangraphs WAR record that season\n",
      "Excludes 2020 (shortened season). Franchises with <15 salary seasons excluded.\n",
      AUTHOR_LINE, "  |  Data: Lahman DB, Spotrac, USA Today"
    )
  ) +
  theme_story() +
  theme(
    panel.grid.major.y = element_blank(),
    panel.grid.minor   = element_blank(),
    axis.text.y = element_text(size = 8.5)
  )

out_dir <- file.path(path.expand(Sys.getenv("LAHMANS_DBDIR", "~/Documents/Data/baseball")), "charts")
if (!dir.exists(out_dir)) dir.create(out_dir, recursive = TRUE)
ggsave(file.path(out_dir, "dead_money.png"), p,
       width = 9, height = 10, dpi = 300, device = ragg::agg_png)
saveRDS(p, file.path(out_dir, "dead_money.rds"))
cat("Saved: dead_money.png\n")