-- FanGraphs MLB Advanced Stats Tables
-- Created: 2026-03-11

-- Batting stats: full season + recent splits (last 7/14/30 days)
CREATE TABLE IF NOT EXISTS fg_batting_stats (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  fg_player_id INTEGER NOT NULL,
  mlbam_id INTEGER,
  player_name VARCHAR(255) NOT NULL,
  team VARCHAR(10) NOT NULL,
  season INTEGER NOT NULL,
  split VARCHAR(20) NOT NULL DEFAULT 'full',
  -- Counting
  g INTEGER, ab INTEGER, pa INTEGER, h INTEGER,
  singles INTEGER, doubles INTEGER, triples INTEGER, hr INTEGER,
  r INTEGER, rbi INTEGER, bb INTEGER, so INTEGER, sb INTEGER, cs INTEGER,
  hbp INTEGER, sf INTEGER,
  -- Rates
  avg NUMERIC(6,4), obp NUMERIC(6,4), slg NUMERIC(6,4), ops NUMERIC(6,4),
  iso NUMERIC(6,4), babip NUMERIC(6,4), woba NUMERIC(6,4), wrc_plus NUMERIC(6,2),
  bb_pct NUMERIC(6,4), k_pct NUMERIC(6,4),
  -- Batted ball
  ld_pct NUMERIC(6,4), gb_pct NUMERIC(6,4), fb_pct NUMERIC(6,4), hr_fb NUMERIC(6,4),
  -- Statcast
  ev NUMERIC(6,2), hard_hit_pct NUMERIC(6,4), barrel_pct NUMERIC(6,4),
  -- Value
  war NUMERIC(6,2), off NUMERIC(6,2), def NUMERIC(6,2), spd NUMERIC(6,2),
  -- Meta
  pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(fg_player_id, season, split, pull_date)
);

CREATE INDEX IF NOT EXISTS idx_fg_bat_team ON fg_batting_stats(team, season, split);
CREATE INDEX IF NOT EXISTS idx_fg_bat_pull ON fg_batting_stats(pull_date);
CREATE INDEX IF NOT EXISTS idx_fg_bat_name ON fg_batting_stats(player_name);

-- Pitching stats
CREATE TABLE IF NOT EXISTS fg_pitching_stats (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  fg_player_id INTEGER NOT NULL,
  mlbam_id INTEGER,
  player_name VARCHAR(255) NOT NULL,
  team VARCHAR(10) NOT NULL,
  season INTEGER NOT NULL,
  split VARCHAR(20) NOT NULL DEFAULT 'full',
  -- Core
  w INTEGER, l INTEGER, era NUMERIC(6,4), g INTEGER, gs INTEGER,
  sv INTEGER, hld INTEGER, ip NUMERIC(6,1),
  h INTEGER, r INTEGER, er INTEGER, hr INTEGER, bb INTEGER, so INTEGER, hbp INTEGER,
  -- Rates
  whip NUMERIC(6,4), k_per_9 NUMERIC(6,2), bb_per_9 NUMERIC(6,2),
  k_pct NUMERIC(6,4), bb_pct NUMERIC(6,4), k_bb_pct NUMERIC(6,4),
  hr_per_9 NUMERIC(6,2), avg NUMERIC(6,4), babip NUMERIC(6,4), lob_pct NUMERIC(6,4),
  -- Advanced
  fip NUMERIC(6,4), xfip NUMERIC(6,4), siera NUMERIC(6,4), xera NUMERIC(6,4),
  -- Batted ball
  gb_pct NUMERIC(6,4), fb_pct NUMERIC(6,4), ld_pct NUMERIC(6,4),
  hr_fb NUMERIC(6,4), hard_hit_pct NUMERIC(6,4),
  -- Statcast
  ev NUMERIC(6,2), barrel_pct NUMERIC(6,4),
  -- Pitch mix
  fb_pct_pitch NUMERIC(6,4), fb_velo NUMERIC(6,1),
  sl_pct NUMERIC(6,4), sl_velo NUMERIC(6,1),
  cb_pct NUMERIC(6,4), cb_velo NUMERIC(6,1),
  ch_pct NUMERIC(6,4), ch_velo NUMERIC(6,1),
  -- Value
  war NUMERIC(6,2),
  -- Meta
  pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(fg_player_id, season, split, pull_date)
);

CREATE INDEX IF NOT EXISTS idx_fg_pit_team ON fg_pitching_stats(team, season, split);
CREATE INDEX IF NOT EXISTS idx_fg_pit_pull ON fg_pitching_stats(pull_date);
CREATE INDEX IF NOT EXISTS idx_fg_pit_name ON fg_pitching_stats(player_name);

-- Projections (Steamer, ZiPS, etc.)
CREATE TABLE IF NOT EXISTS fg_projections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  fg_player_id INTEGER NOT NULL,
  mlbam_id INTEGER,
  player_name VARCHAR(255) NOT NULL,
  team VARCHAR(10) NOT NULL,
  season INTEGER NOT NULL,
  stat_type VARCHAR(10) NOT NULL,
  projection_system VARCHAR(20) NOT NULL,
  -- Batting
  proj_pa INTEGER, proj_hr INTEGER, proj_rbi INTEGER, proj_r INTEGER,
  proj_sb INTEGER, proj_avg NUMERIC(6,4), proj_obp NUMERIC(6,4),
  proj_slg NUMERIC(6,4), proj_ops NUMERIC(6,4), proj_woba NUMERIC(6,4),
  proj_wrc_plus NUMERIC(6,2), proj_war NUMERIC(6,2),
  proj_k_pct NUMERIC(6,4), proj_bb_pct NUMERIC(6,4),
  -- Pitching
  proj_w INTEGER, proj_l INTEGER, proj_era NUMERIC(6,4),
  proj_ip NUMERIC(6,1), proj_so INTEGER, proj_bb INTEGER,
  proj_whip NUMERIC(6,4), proj_fip NUMERIC(6,4), proj_war_pit NUMERIC(6,2),
  proj_k_per_9 NUMERIC(6,2), proj_bb_per_9 NUMERIC(6,2),
  -- Meta
  pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(fg_player_id, season, stat_type, projection_system, pull_date)
);

CREATE INDEX IF NOT EXISTS idx_fg_proj_team ON fg_projections(team, season, projection_system);
CREATE INDEX IF NOT EXISTS idx_fg_proj_pull ON fg_projections(pull_date);
