-- Populate DefenseVsPosition for ALL sports
-- Run daily to refresh

-- ============================================================
-- NBA
-- ============================================================
INSERT INTO "DefenseVsPosition" (league, season, team, "positionGroup", "statKey", "avgAllowed", "medianAllowed", "gamesCount", "totalPlayers", "updatedAt", "createdAt")
SELECT 'nba', 2025, pgm.opponent,
  CASE
    WHEN position IN ('G', 'PG', 'SG', 'G-F') THEN 'G'
    WHEN position IN ('F', 'SF', 'PF', 'F-G', 'F-C') THEN 'F'
    WHEN position IN ('C', 'C-F') THEN 'C'
    ELSE position END,
  pgm."statKey",
  ROUND(AVG(pgm.value)::numeric, 2),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pgm.value)::numeric, 2),
  COUNT(DISTINCT pgm."gameDate"::date), COUNT(*), NOW(), NOW()
FROM "PlayerGameMetric" pgm
WHERE pgm.league = 'nba' AND pgm."gameDate" >= '2025-10-01'
  AND pgm.opponent IS NOT NULL AND pgm.opponent != ''
  AND pgm.position IS NOT NULL AND pgm.position != ''
  AND pgm."statKey" IN ('points', 'rebounds', 'assists', 'steals', 'blocks', 'threePointersMade')
GROUP BY pgm.opponent,
  CASE WHEN position IN ('G', 'PG', 'SG', 'G-F') THEN 'G'
       WHEN position IN ('F', 'SF', 'PF', 'F-G', 'F-C') THEN 'F'
       WHEN position IN ('C', 'C-F') THEN 'C' ELSE position END,
  pgm."statKey"
HAVING COUNT(*) >= 5
ON CONFLICT (league, season, team, "positionGroup", "statKey")
DO UPDATE SET "avgAllowed" = EXCLUDED."avgAllowed", "medianAllowed" = EXCLUDED."medianAllowed",
  "gamesCount" = EXCLUDED."gamesCount", "totalPlayers" = EXCLUDED."totalPlayers", "updatedAt" = NOW();

-- ============================================================
-- NHL
-- ============================================================
INSERT INTO "DefenseVsPosition" (league, season, team, "positionGroup", "statKey", "avgAllowed", "medianAllowed", "gamesCount", "totalPlayers", "updatedAt", "createdAt")
SELECT 'nhl', 2025, pgm.opponent,
  CASE WHEN position IN ('C', 'L', 'R', 'LW', 'RW') THEN 'F'
       WHEN position = 'D' THEN 'D' WHEN position = 'G' THEN 'G' ELSE position END,
  pgm."statKey",
  ROUND(AVG(pgm.value)::numeric, 2),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pgm.value)::numeric, 2),
  COUNT(DISTINCT pgm."gameDate"::date), COUNT(*), NOW(), NOW()
FROM "PlayerGameMetric" pgm
WHERE pgm.league = 'nhl' AND pgm."gameDate" >= '2025-10-01'
  AND pgm.opponent IS NOT NULL AND pgm.opponent != ''
  AND pgm.position IS NOT NULL AND pgm.position != ''
  AND pgm."statKey" IN ('goals', 'points', 'assists', 'shots')
GROUP BY pgm.opponent,
  CASE WHEN position IN ('C', 'L', 'R', 'LW', 'RW') THEN 'F'
       WHEN position = 'D' THEN 'D' WHEN position = 'G' THEN 'G' ELSE position END,
  pgm."statKey"
HAVING COUNT(*) >= 5
ON CONFLICT (league, season, team, "positionGroup", "statKey")
DO UPDATE SET "avgAllowed" = EXCLUDED."avgAllowed", "medianAllowed" = EXCLUDED."medianAllowed",
  "gamesCount" = EXCLUDED."gamesCount", "totalPlayers" = EXCLUDED."totalPlayers", "updatedAt" = NOW();

-- ============================================================
-- NCAAB
-- ============================================================
INSERT INTO "DefenseVsPosition" (league, season, team, "positionGroup", "statKey", "avgAllowed", "medianAllowed", "gamesCount", "totalPlayers", "updatedAt", "createdAt")
SELECT 'ncaab', 2025, pgm.opponent,
  CASE WHEN position IN ('G', 'PG', 'SG', 'G-F') THEN 'G'
       WHEN position IN ('F', 'SF', 'PF', 'F-G', 'F-C') THEN 'F'
       WHEN position IN ('C', 'C-F') THEN 'C' ELSE position END,
  pgm."statKey",
  ROUND(AVG(pgm.value)::numeric, 2),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pgm.value)::numeric, 2),
  COUNT(DISTINCT pgm."gameDate"::date), COUNT(*), NOW(), NOW()
FROM "PlayerGameMetric" pgm
WHERE pgm.league = 'ncaab' AND pgm."gameDate" >= '2025-11-01'
  AND pgm.opponent IS NOT NULL AND pgm.opponent != ''
  AND pgm.position IS NOT NULL AND pgm.position != ''
  AND pgm."statKey" IN ('points', 'rebounds', 'assists', 'steals', 'blocks', 'threePointersMade')
GROUP BY pgm.opponent,
  CASE WHEN position IN ('G', 'PG', 'SG', 'G-F') THEN 'G'
       WHEN position IN ('F', 'SF', 'PF', 'F-G', 'F-C') THEN 'F'
       WHEN position IN ('C', 'C-F') THEN 'C' ELSE position END,
  pgm."statKey"
HAVING COUNT(*) >= 5
ON CONFLICT (league, season, team, "positionGroup", "statKey")
DO UPDATE SET "avgAllowed" = EXCLUDED."avgAllowed", "medianAllowed" = EXCLUDED."medianAllowed",
  "gamesCount" = EXCLUDED."gamesCount", "totalPlayers" = EXCLUDED."totalPlayers", "updatedAt" = NOW();

-- ============================================================
-- MLB
-- ============================================================
INSERT INTO "DefenseVsPosition" (league, season, team, "positionGroup", "statKey", "avgAllowed", "medianAllowed", "gamesCount", "totalPlayers", "updatedAt", "createdAt")
SELECT 'mlb', 2025, pgm.opponent,
  CASE WHEN position = 'P' THEN 'P'
       WHEN position IN ('C', 'DH', 'PH') THEN 'C_DH'
       WHEN position IN ('1B', '2B', '3B', 'SS') THEN 'IF'
       WHEN position IN ('LF', 'CF', 'RF') THEN 'OF' ELSE position END,
  pgm."statKey",
  ROUND(AVG(pgm.value)::numeric, 2),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pgm.value)::numeric, 2),
  COUNT(DISTINCT pgm."gameDate"::date), COUNT(*), NOW(), NOW()
FROM "PlayerGameMetric" pgm
WHERE pgm.league = 'mlb' AND pgm."gameDate" >= '2025-03-01'
  AND pgm.opponent IS NOT NULL AND pgm.opponent != ''
  AND pgm.position IS NOT NULL AND pgm.position != ''
  AND pgm."statKey" IN ('mlb_hits', 'mlb_runs', 'mlb_rbi', 'mlb_home_runs', 'mlb_strikeouts', 'mlb_stolen_bases', 'mlb_total_bases', 'mlb_walks', 'mlb_earned_runs', 'mlb_pitcher_strikeouts')
GROUP BY pgm.opponent,
  CASE WHEN position = 'P' THEN 'P'
       WHEN position IN ('C', 'DH', 'PH') THEN 'C_DH'
       WHEN position IN ('1B', '2B', '3B', 'SS') THEN 'IF'
       WHEN position IN ('LF', 'CF', 'RF') THEN 'OF' ELSE position END,
  pgm."statKey"
HAVING COUNT(*) >= 5
ON CONFLICT (league, season, team, "positionGroup", "statKey")
DO UPDATE SET "avgAllowed" = EXCLUDED."avgAllowed", "medianAllowed" = EXCLUDED."medianAllowed",
  "gamesCount" = EXCLUDED."gamesCount", "totalPlayers" = EXCLUDED."totalPlayers", "updatedAt" = NOW();

-- ============================================================
-- NFL
-- ============================================================
INSERT INTO "DefenseVsPosition" (league, season, team, "positionGroup", "statKey", "avgAllowed", "medianAllowed", "gamesCount", "totalPlayers", "updatedAt", "createdAt")
SELECT 'nfl', 2025, pgm.opponent,
  CASE WHEN position = 'QB' THEN 'QB'
       WHEN position IN ('RB', 'FB') THEN 'RB'
       WHEN position = 'WR' THEN 'WR'
       WHEN position = 'TE' THEN 'TE'
       WHEN position IN ('K', 'PK') THEN 'K' ELSE position END,
  pgm."statKey",
  ROUND(AVG(pgm.value)::numeric, 2),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pgm.value)::numeric, 2),
  COUNT(DISTINCT pgm."gameDate"::date), COUNT(*), NOW(), NOW()
FROM "PlayerGameMetric" pgm
WHERE pgm.league = 'nfl' AND pgm."gameDate" >= '2025-09-01'
  AND pgm.opponent IS NOT NULL AND pgm.opponent != ''
  AND pgm.position IS NOT NULL AND pgm.position != ''
  AND pgm."statKey" IN ('passing_yards', 'rushing_yards', 'receiving_yards', 'passing_tds', 'receptions', 'interceptions', 'espn_td')
GROUP BY pgm.opponent,
  CASE WHEN position = 'QB' THEN 'QB'
       WHEN position IN ('RB', 'FB') THEN 'RB'
       WHEN position = 'WR' THEN 'WR'
       WHEN position = 'TE' THEN 'TE'
       WHEN position IN ('K', 'PK') THEN 'K' ELSE position END,
  pgm."statKey"
HAVING COUNT(*) >= 5
ON CONFLICT (league, season, team, "positionGroup", "statKey")
DO UPDATE SET "avgAllowed" = EXCLUDED."avgAllowed", "medianAllowed" = EXCLUDED."medianAllowed",
  "gamesCount" = EXCLUDED."gamesCount", "totalPlayers" = EXCLUDED."totalPlayers", "updatedAt" = NOW();

-- ============================================================
-- WNBA (positions from Player table via name join — PGM.position is empty)
-- ============================================================
INSERT INTO "DefenseVsPosition" (league, season, team, "positionGroup", "statKey", "avgAllowed", "medianAllowed", "gamesCount", "totalPlayers", "updatedAt", "createdAt")
SELECT 'wnba', 2025, pgm.opponent,
  pos_group,
  pgm."statKey",
  ROUND(AVG(pgm.value)::numeric, 2),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pgm.value)::numeric, 2),
  COUNT(DISTINCT pgm."gameDate"::date), COUNT(*), NOW(), NOW()
FROM "PlayerGameMetric" pgm
JOIN "Player" p ON LOWER(p.name) = LOWER(pgm."playerName") AND p.league = 'wnba'
CROSS JOIN LATERAL (
  SELECT CASE
    WHEN p.position IN ('G', 'PG', 'SG', 'Guard') THEN 'G'
    WHEN p.position IN ('F', 'SF', 'PF', 'Forward') THEN 'F'
    WHEN p.position IN ('C', 'Center') THEN 'C'
    ELSE NULL
  END AS pos_group
) pg
WHERE pgm.league = 'wnba' AND pgm."gameDate" >= '2025-05-01'
  AND pgm.opponent IS NOT NULL AND pgm.opponent != ''
  AND pg.pos_group IS NOT NULL
  AND pgm."statKey" IN ('points', 'rebounds', 'assists', 'steals', 'blocks', 'threePointersMade')
GROUP BY pgm.opponent, pos_group, pgm."statKey"
HAVING COUNT(*) >= 5
ON CONFLICT (league, season, team, "positionGroup", "statKey")
DO UPDATE SET "avgAllowed" = EXCLUDED."avgAllowed", "medianAllowed" = EXCLUDED."medianAllowed",
  "gamesCount" = EXCLUDED."gamesCount", "totalPlayers" = EXCLUDED."totalPlayers", "updatedAt" = NOW();

-- ============================================================
-- UFC (position = weight class, team = opponent fighter name)
-- ============================================================
INSERT INTO "DefenseVsPosition" (league, season, team, "positionGroup", "statKey", "avgAllowed", "medianAllowed", "gamesCount", "totalPlayers", "updatedAt", "createdAt")
SELECT 'ufc', 2025, pgm.opponent,
  pgm.position,
  pgm."statKey",
  ROUND(AVG(pgm.value)::numeric, 2),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pgm.value)::numeric, 2),
  COUNT(DISTINCT pgm."gameDate"::date), COUNT(*), NOW(), NOW()
FROM "PlayerGameMetric" pgm
WHERE pgm.league = 'ufc' AND pgm."gameDate" >= '2020-01-01'
  AND pgm.opponent IS NOT NULL AND pgm.opponent != ''
  AND pgm.position IS NOT NULL AND pgm.position != ''
  AND pgm."statKey" IN ('knockdowns', 'takedowns', 'total_strikes', 'submission_attempts', 'sig_strike_pct')
GROUP BY pgm.opponent, pgm.position, pgm."statKey"
HAVING COUNT(*) >= 3
ON CONFLICT (league, season, team, "positionGroup", "statKey")
DO UPDATE SET "avgAllowed" = EXCLUDED."avgAllowed", "medianAllowed" = EXCLUDED."medianAllowed",
  "gamesCount" = EXCLUDED."gamesCount", "totalPlayers" = EXCLUDED."totalPlayers", "updatedAt" = NOW();

-- ============================================================
-- League averages (all sports)
-- ============================================================
WITH league_avgs AS (
  SELECT league, "positionGroup", "statKey", AVG("avgAllowed") as league_avg
  FROM "DefenseVsPosition" WHERE season = 2025
  GROUP BY league, "positionGroup", "statKey"
)
UPDATE "DefenseVsPosition" dvp
SET "leagueAvg" = la.league_avg
FROM league_avgs la
WHERE dvp.league = la.league AND dvp."positionGroup" = la."positionGroup"
  AND dvp."statKey" = la."statKey" AND dvp.season = 2025;

-- ============================================================
-- Ranks (all sports)
-- ============================================================
WITH ranked AS (
  SELECT id, RANK() OVER (PARTITION BY league, "positionGroup", "statKey" ORDER BY "avgAllowed" DESC) as rnk
  FROM "DefenseVsPosition" WHERE season = 2025
)
UPDATE "DefenseVsPosition" dvp
SET "rank" = r.rnk
FROM ranked r
WHERE dvp.id = r.id;
