-- KenPom Integration Schema for Rainmaker
-- Created: 2026-03-09
-- Purpose: Store KenPom NCAA basketball analytics for Rainmaker forecasting

-- Core ratings table (primary endpoint)
CREATE TABLE IF NOT EXISTS kp_ratings (
    id SERIAL PRIMARY KEY,
    season INTEGER NOT NULL,
    team_name TEXT NOT NULL,
    team_name_canonical TEXT,           -- normalized for joining
    conf_short TEXT,
    coach TEXT,
    wins INTEGER,
    losses INTEGER,
    seed INTEGER,
    adj_em FLOAT,
    rank_adj_em INTEGER,
    pythag FLOAT,
    rank_pythag INTEGER,
    adj_oe FLOAT,
    rank_adj_oe INTEGER,
    oe FLOAT,
    adj_de FLOAT,
    rank_adj_de INTEGER,
    de FLOAT,
    tempo FLOAT,
    adj_tempo FLOAT,
    rank_adj_tempo INTEGER,
    luck FLOAT,
    rank_luck INTEGER,
    sos FLOAT,
    rank_sos INTEGER,
    soso FLOAT,                         -- SOS offense
    rank_soso INTEGER,
    sosd FLOAT,                         -- SOS defense
    rank_sosd INTEGER,
    ncsos FLOAT,                        -- non-conference SOS
    rank_ncsos INTEGER,
    apl_off FLOAT,                      -- avg possession length offense
    apl_def FLOAT,                      -- avg possession length defense
    event TEXT,                         -- tournament event/round
    data_through TEXT,
    pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, team_name, pull_date)
);

-- Four factors table
CREATE TABLE IF NOT EXISTS kp_four_factors (
    id SERIAL PRIMARY KEY,
    season INTEGER NOT NULL,
    team_name TEXT NOT NULL,
    team_name_canonical TEXT,
    conf_only BOOLEAN DEFAULT FALSE,
    efg_pct FLOAT,                      -- effective FG% (offense)
    rank_efg_pct INTEGER,
    to_pct FLOAT,                       -- turnover % (offense)
    rank_to_pct INTEGER,
    or_pct FLOAT,                       -- offensive rebound %
    rank_or_pct INTEGER,
    ft_rate FLOAT,                      -- free throw rate (offense)
    rank_ft_rate INTEGER,
    defg_pct FLOAT,                     -- effective FG% allowed (defense)
    rank_defg_pct INTEGER,
    dto_pct FLOAT,                      -- TO% forced (defense)
    rank_dto_pct INTEGER,
    dor_pct FLOAT,                      -- defensive rebound %
    rank_dor_pct INTEGER,
    dft_rate FLOAT,                     -- FT rate allowed (defense)
    rank_dft_rate INTEGER,
    adj_oe FLOAT,
    adj_de FLOAT,
    adj_tempo FLOAT,
    data_through TEXT,
    pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, team_name, pull_date, conf_only)
);

-- Height, experience, continuity
CREATE TABLE IF NOT EXISTS kp_height (
    id SERIAL PRIMARY KEY,
    season INTEGER NOT NULL,
    team_name TEXT NOT NULL,
    team_name_canonical TEXT,
    conf_short TEXT,
    avg_hgt FLOAT,
    hgt_eff FLOAT,                      -- effective height
    hgt_5 FLOAT, hgt_4 FLOAT, hgt_3 FLOAT, hgt_2 FLOAT, hgt_1 FLOAT,  -- position heights
    experience FLOAT,
    rank_exp INTEGER,
    bench FLOAT,
    rank_bench INTEGER,
    continuity FLOAT,
    rank_continuity INTEGER,
    data_through TEXT,
    pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, team_name, pull_date)
);

-- Miscellaneous stats (shooting, blocks, steals, assists, etc.)
CREATE TABLE IF NOT EXISTS kp_misc_stats (
    id SERIAL PRIMARY KEY,
    season INTEGER NOT NULL,
    team_name TEXT NOT NULL,
    team_name_canonical TEXT,
    conf_short TEXT,
    conf_only BOOLEAN DEFAULT FALSE,
    -- Offense
    fg3_pct FLOAT, rank_fg3_pct INTEGER,
    fg2_pct FLOAT, rank_fg2_pct INTEGER,
    ft_pct FLOAT, rank_ft_pct INTEGER,
    block_pct FLOAT, rank_block_pct INTEGER,
    stl_rate FLOAT, rank_stl_rate INTEGER,
    nst_rate FLOAT, rank_nst_rate INTEGER,     -- non-steal turnover rate
    a_rate FLOAT, rank_a_rate INTEGER,          -- assist rate
    f3g_rate FLOAT, rank_f3g_rate INTEGER,      -- 3PA rate
    avg_2pa_dist FLOAT, rank_avg_2pa_dist INTEGER,
    -- Defense
    opp_fg3_pct FLOAT, rank_opp_fg3_pct INTEGER,
    opp_fg2_pct FLOAT, rank_opp_fg2_pct INTEGER,
    opp_ft_pct FLOAT,
    opp_block_pct FLOAT, rank_opp_block_pct INTEGER,
    opp_stl_rate FLOAT, rank_opp_stl_rate INTEGER,
    opp_nst_rate FLOAT,
    opp_a_rate FLOAT, rank_opp_a_rate INTEGER,
    opp_f3g_rate FLOAT, rank_opp_f3g_rate INTEGER,
    opp_avg_2pa_dist FLOAT,
    adj_oe FLOAT, adj_de FLOAT,
    data_through TEXT,
    pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, team_name, pull_date, conf_only)
);

-- Point distribution
CREATE TABLE IF NOT EXISTS kp_point_dist (
    id SERIAL PRIMARY KEY,
    season INTEGER NOT NULL,
    team_name TEXT NOT NULL,
    team_name_canonical TEXT,
    conf_short TEXT,
    conf_only BOOLEAN DEFAULT FALSE,
    off_ft FLOAT, rank_off_ft INTEGER,
    off_fg2 FLOAT, rank_off_fg2 INTEGER,
    off_fg3 FLOAT, rank_off_fg3 INTEGER,
    def_ft FLOAT, rank_def_ft INTEGER,
    def_fg2 FLOAT, rank_def_fg2 INTEGER,
    def_fg3 FLOAT, rank_def_fg3 INTEGER,
    data_through TEXT,
    pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, team_name, pull_date, conf_only)
);

-- Conference ratings
CREATE TABLE IF NOT EXISTS kp_conf_ratings (
    id SERIAL PRIMARY KEY,
    season INTEGER NOT NULL,
    conf_short TEXT NOT NULL,
    conf_long TEXT,
    conf_id INTEGER,
    rank INTEGER,
    rating FLOAT,                       -- NetRtg of .500 team in conference
    pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, conf_short, pull_date)
);

-- Fanmatch game predictions
CREATE TABLE IF NOT EXISTS kp_fanmatch (
    id SERIAL PRIMARY KEY,
    season INTEGER NOT NULL,
    game_id INTEGER,
    game_date DATE NOT NULL,
    visitor TEXT NOT NULL,
    visitor_canonical TEXT,
    home TEXT NOT NULL,
    home_canonical TEXT,
    home_rank INTEGER,
    visitor_rank INTEGER,
    home_pred FLOAT,
    visitor_pred FLOAT,
    home_wp FLOAT,                      -- win probability
    pred_tempo FLOAT,
    thrill_score FLOAT,
    pull_date DATE NOT NULL DEFAULT CURRENT_DATE,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, game_id, pull_date)
);

-- Team ID lookup (from teams endpoint)
CREATE TABLE IF NOT EXISTS kp_teams (
    id SERIAL PRIMARY KEY,
    season INTEGER NOT NULL,
    team_name TEXT NOT NULL,
    team_name_canonical TEXT,
    team_id INTEGER NOT NULL,
    conf_short TEXT,
    coach TEXT,
    arena TEXT,
    arena_city TEXT,
    arena_state TEXT,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, team_id)
);

-- Ratings archive (historical snapshots)
CREATE TABLE IF NOT EXISTS kp_archive (
    id SERIAL PRIMARY KEY,
    archive_date DATE,
    season INTEGER NOT NULL,
    is_preseason BOOLEAN DEFAULT FALSE,
    team_name TEXT NOT NULL,
    team_name_canonical TEXT,
    seed INTEGER,
    event TEXT,
    conf_short TEXT,
    adj_em FLOAT, rank_adj_em INTEGER,
    adj_oe FLOAT, rank_adj_oe INTEGER,
    adj_de FLOAT, rank_adj_de INTEGER,
    adj_tempo FLOAT, rank_adj_tempo INTEGER,
    adj_em_final FLOAT, rank_adj_em_final INTEGER,
    adj_oe_final FLOAT, adj_de_final FLOAT,
    adj_tempo_final FLOAT,
    rank_chg INTEGER,
    adj_em_chg FLOAT,
    adj_t_chg FLOAT,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(season, team_name, archive_date, is_preseason)
);

-- Ingestion log for auditing
CREATE TABLE IF NOT EXISTS kp_ingestion_log (
    id SERIAL PRIMARY KEY,
    endpoint TEXT NOT NULL,
    season INTEGER,
    pull_date DATE,
    rows_fetched INTEGER,
    rows_inserted INTEGER,
    rows_updated INTEGER,
    status TEXT DEFAULT 'success',       -- success, error, partial
    error_message TEXT,
    duration_ms INTEGER,
    pulled_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Team name mapping: KenPom name ↔ Rainmaker/SGO canonical name
CREATE TABLE IF NOT EXISTS kp_team_map (
    id SERIAL PRIMARY KEY,
    kp_name TEXT NOT NULL UNIQUE,        -- KenPom team name (exactly as returned)
    canonical_name TEXT,                 -- Rainmaker/SGO canonical form
    sgo_team_id TEXT,                    -- SGO API team identifier
    odds_api_name TEXT,                  -- The Odds API name
    conf_short TEXT,
    match_method TEXT,                   -- exact, fuzzy, manual, alias
    match_confidence FLOAT DEFAULT 1.0,  -- 0-1 confidence
    verified BOOLEAN DEFAULT FALSE,      -- manually verified
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for query performance
CREATE INDEX IF NOT EXISTS idx_kp_ratings_season_team ON kp_ratings(season, team_name_canonical);
CREATE INDEX IF NOT EXISTS idx_kp_ratings_pulldate ON kp_ratings(pull_date);
CREATE INDEX IF NOT EXISTS idx_kp_ratings_rank ON kp_ratings(season, rank_adj_em);
CREATE INDEX IF NOT EXISTS idx_kp_four_factors_team ON kp_four_factors(season, team_name_canonical);
CREATE INDEX IF NOT EXISTS idx_kp_height_team ON kp_height(season, team_name_canonical);
CREATE INDEX IF NOT EXISTS idx_kp_misc_team ON kp_misc_stats(season, team_name_canonical);
CREATE INDEX IF NOT EXISTS idx_kp_fanmatch_date ON kp_fanmatch(game_date);
CREATE INDEX IF NOT EXISTS idx_kp_fanmatch_teams ON kp_fanmatch(home_canonical, visitor_canonical);
CREATE INDEX IF NOT EXISTS idx_kp_archive_team ON kp_archive(season, team_name_canonical);
CREATE INDEX IF NOT EXISTS idx_kp_team_map_kp ON kp_team_map(kp_name);
CREATE INDEX IF NOT EXISTS idx_kp_team_map_canonical ON kp_team_map(canonical_name);
