suppressPackageStartupMessages({
  library(DBI); library(duckdb); library(data.table); library(ggplot2); library(ggrepel)
})
.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), add = TRUE)

sql <- "
WITH franchise_map AS (SELECT DISTINCT teamID, franchID FROM Teams),
acq AS (
    SELECT DISTINCT pat.playerID, pat.teamID AS dest_team, pat.first_team_year AS arrival_year
    FROM PlayerAcquisitionType pat
    WHERE pat.acq_type = 'young_acq' AND pat.first_team_year >= 1990
),
mlb_service AS (SELECT playerID, yearID FROM Batting UNION SELECT playerID, yearID FROM Pitching),
prior_service AS (
    SELECT a.playerID, a.dest_team, a.arrival_year,
           COUNT(DISTINCT ms.yearID) AS prior_mlb_seasons
    FROM acq a
    JOIN mlb_service ms ON ms.playerID = a.playerID AND ms.yearID < a.arrival_year
    GROUP BY a.playerID, a.dest_team, a.arrival_year
),
last_team AS (
    SELECT DISTINCT a.playerID, a.dest_team, a.arrival_year, ms2.teamID AS source_team
    FROM acq a
    JOIN (SELECT playerID, teamID, yearID FROM Batting UNION SELECT playerID, teamID, yearID FROM Pitching) ms2
        ON ms2.playerID = a.playerID AND ms2.yearID = a.arrival_year - 1 AND ms2.teamID != a.dest_team
),
established_trades AS (
    SELECT ps.playerID, ps.dest_team, ps.arrival_year, lt.source_team
    FROM prior_service ps
    JOIN last_team lt USING (playerID, dest_team, arrival_year)
    JOIN franchise_map fm_d ON fm_d.teamID = ps.dest_team
    JOIN franchise_map fm_s ON fm_s.teamID = lt.source_team
    WHERE fm_d.franchID != fm_s.franchID AND ps.prior_mlb_seasons >= 2
),
incoming AS (
    SELECT fm_d.franchID AS teamID, et.playerID, AVG(COALESCE(w.total_war, 0)) AS avg_war
    FROM established_trades et
    JOIN (SELECT playerID, teamID, yearID FROM Batting UNION SELECT playerID, teamID, yearID FROM Pitching) stint
        ON stint.playerID = et.playerID AND stint.teamID = et.dest_team AND stint.yearID >= et.arrival_year
    LEFT JOIN PlayerWAR w ON et.playerID = w.playerID AND w.yearID = stint.yearID
    JOIN franchise_map fm_d ON fm_d.teamID = et.dest_team
    GROUP BY fm_d.franchID, et.playerID
),
outgoing AS (
    SELECT fm_s.franchID AS teamID, et.playerID, AVG(COALESCE(w.total_war, 0)) AS avg_war
    FROM established_trades et
    LEFT JOIN PlayerWAR w ON et.playerID = w.playerID AND w.yearID >= et.arrival_year
    JOIN franchise_map fm_s ON fm_s.teamID = et.source_team
    GROUP BY fm_s.franchID, et.playerID
),
team_scores AS (
    SELECT teamID, 'received' AS direction, COUNT(DISTINCT playerID) AS n, AVG(avg_war) AS avg_war
    FROM incoming GROUP BY teamID
    UNION ALL
    SELECT teamID, 'gave_away', COUNT(DISTINCT playerID), AVG(avg_war)
    FROM outgoing GROUP BY teamID
)
SELECT r.teamID,
       r.n AS n_received, ROUND(r.avg_war, 3) AS avg_war_received,
       g.n AS n_given,    ROUND(g.avg_war, 3) AS avg_war_given,
       ROUND(r.avg_war - g.avg_war, 3) AS trade_net
FROM team_scores r JOIN team_scores g ON r.teamID = g.teamID
WHERE r.direction = 'received' AND g.direction = 'gave_away'
  AND r.n >= 3
ORDER BY trade_net DESC
"
dt <- as.data.table(dbGetQuery(con, sql))
# Map to readable names
dt[, short := TEAM_SHORT[teamID]]
dt[is.na(short), short := teamID]
raw_fallbacks <- dt[short == teamID, unique(teamID)]
if (length(raw_fallbacks))
  warning("TEAM_SHORT missing teamIDs: ", paste(raw_fallbacks, collapse = ", "), call. = FALSE)

dt[, label := short]

# Colour: above diagonal = net winner
dt[, net_winner := avg_war_received > avg_war_given]

qs <- quad_setup(
  dt$avg_war_given, dt$avg_war_received,
  labels = c(
    "Best: received Future Stars,\ngave away lesser talent",
    "High-volume traders:\nhigh quality both ways",
    "Low-volume:\ngave and received little",
    "Worst: gave away Future Stars,\nreceived lesser talent"
  ),
  x_reversed = TRUE
)

n_teams <- nrow(dt)

p <- ggplot(dt, aes(x = avg_war_given, y = avg_war_received)) +
  # diagonal y=x reference
  geom_abline(slope = 1, intercept = 0, linetype = "dashed", colour = "grey55", linewidth = 0.5) +
  # ALL four quadrant shadings
  annotate("rect", xmin = -Inf, xmax = median(dt$avg_war_given),
           ymin  = median(dt$avg_war_received), ymax = Inf,
           fill  = "#d4edda", alpha = 0.45) +   # top-left  = net winners
  annotate("rect", xmin = median(dt$avg_war_given), xmax = Inf,
           ymin  = median(dt$avg_war_received), ymax = Inf,
           fill  = "#fff3cd", alpha = 0.55) +   # top-right = active two-way traders
  annotate("rect", xmin = -Inf, xmax = median(dt$avg_war_given),
           ymin  = -Inf, ymax = median(dt$avg_war_received),
           fill  = "#fff3cd", alpha = 0.55) +   # bot-left  = low-volume franchises
  annotate("rect", xmin = median(dt$avg_war_given), xmax = Inf,
           ymin  = -Inf, ymax = median(dt$avg_war_received),
           fill  = "#f8d7da", alpha = 0.45) +   # bot-right = net losers
  # points sized by trade volume, coloured by net WAR gain (blue = net winner, red = net loser)
  geom_point(aes(size = n_received + n_given,
                 colour = trade_net), alpha = PT_ALPHA) +
  scale_colour_gradient2(midpoint = 0, low = COL_HI, mid = "grey75", high = COL_LO,
                         name = "Trade net WAR\n(+ = net winner)",
                         guide = std_colourbar()) +
  scale_size_continuous(name = "Total moves\n(received + given)",
                        range = c(3, 10), breaks = c(10, 20, 30, 40)) +
  scale_x_reverse(limits = qs$xlim, expand = expansion(0)) +
  scale_y_continuous(limits = qs$ylim, expand = expansion(0)) +
  # labels (all teams)
  geom_label_repel(
    aes(label = label),
    size = LBL_SIZE, label.padding = 0.15, box.padding = 0.5,
    max.overlaps = Inf, segment.colour = "grey55", segment.size = 0.3,
    seed = 42, min.segment.length = 0, point.padding = 0.3, force = 2
  ) +
  geom_text(data = qs$quad_lbl,
    aes(x = x, y = y, label = label, hjust = hjust, vjust = vjust),
    colour = "grey35", size = QUAD_LBL_SIZE, fontface = "italic",
    lineheight = 0.85, inherit.aes = FALSE) +
  labs(
    title    = "Trade Quality: Future Stars Received vs Given Away",
    subtitle = paste0(
      "Future Stars = players (2+ MLB seasons) traded before FA eligibility (< 6 years service).\n",
      "Net trade losers rarely recover without elite draft luck. 1990\u20132025"
    ),
    x        = "Avg WAR/season of players given away post-trade  (right = gave away weaker players)",
    y        = "Avg WAR/season of Future Stars received post-trade",
    caption  = paste0(
      n_teams, " franchises | Diagonal = break-even | ",
      "Caveats: prospect-only trades NOT captured; includes trades, waivers, DFA pickups\n",
      "Large circles (more moves) are more reliable -- interpret small circles cautiously\n",
      AUTHOR_LINE, "  |  Data: Lahman DB, FanGraphs WAR"
    )
  ) +
  theme_story()

out_file <- file.path(
  path.expand(Sys.getenv("LAHMANS_DBDIR", "~/Documents/Data/baseball")),
  "charts", "trade_scores.png"
)
ggsave(out_file, p, width = 10, height = 7.5, dpi = 300, device = ragg::agg_png)
saveRDS(p, sub("\\.png$", ".rds", out_file))
message("Saved: ", out_file)