-- Migration: 006_data_warehouse_calibration.sql
-- Data cache tables, grade_outcomes (all ship-version columns),
-- player_calibrated_weights.
-- Created: 2026-04-13

-- ============================================================
-- NBA Data Cache
-- ============================================================
CREATE TABLE IF NOT EXISTS nba_data_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  cache_key TEXT NOT NULL UNIQUE,
  data JSONB NOT NULL,
  fetched_at TIMESTAMPTZ DEFAULT NOW(),
  ttl_hours INTEGER DEFAULT 6
);
CREATE INDEX IF NOT EXISTS idx_nba_ck ON nba_data_cache(cache_key);

-- ============================================================
-- MLB Data Cache
-- ============================================================
CREATE TABLE IF NOT EXISTS mlb_data_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  cache_key TEXT NOT NULL UNIQUE,
  data JSONB NOT NULL,
  fetched_at TIMESTAMPTZ DEFAULT NOW(),
  ttl_hours INTEGER DEFAULT 24
);
CREATE INDEX IF NOT EXISTS idx_mlb_ck ON mlb_data_cache(cache_key);

-- ============================================================
-- Grade Outcomes — ALL ship-version columns
-- ============================================================
CREATE TABLE IF NOT EXISTS grade_outcomes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sport TEXT NOT NULL,
  player_id TEXT NOT NULL,
  player_name TEXT,
  stat_type TEXT NOT NULL,
  prop_line DECIMAL NOT NULL,
  over_under TEXT NOT NULL,
  grade TEXT,
  abstained BOOLEAN DEFAULT FALSE,
  abstention_reason TEXT,
  confidence DECIMAL,
  global_offset_applied DECIMAL DEFAULT 0.0,
  projected_value DECIMAL,
  projected_std DECIMAL,
  actual_value DECIMAL,
  hit BOOLEAN,
  real_edge DECIMAL,
  implied_probability DECIMAL,
  ev_per_dollar DECIMAL,
  kelly_recommended_pct DECIMAL,
  sub_scores JSONB NOT NULL,
  player_weights JSONB NOT NULL,
  bayesian_weights_used JSONB,
  similar_game_count INTEGER,
  similar_game_modifier DECIMAL,
  archetype_scores JSONB,
  player_profile_snapshot JSONB,
  pitcher_profile_snapshot JSONB,
  matchup_context JSONB,
  game_context JSONB,
  weather_context JSONB,
  teammate_context JSONB,
  bullpen_context JSONB,
  lineup_protection_context JSONB,
  home_road_context JSONB,
  day_night_context JSONB,
  opponent_quality_context JSONB,
  travel_fatigue_context JSONB,
  regime_check JSONB,
  lineup_status TEXT,
  lineup_source TEXT,
  line_source TEXT,
  american_odds INTEGER,
  bookmaker TEXT,
  clv_opening_line DECIMAL,
  clv_closing_line DECIMAL,
  clv_movement DECIMAL,
  clv_win BOOLEAN,
  market_alignment TEXT,
  discipline_score DECIMAL,
  umpire_id TEXT,
  umpire_adjustment DECIMAL,
  referee_crew_id TEXT,
  referee_adjustment DECIMAL,
  pick_number INTEGER,
  capper_post_text TEXT,
  game_id TEXT,
  game_date DATE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  resolved_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_go_player ON grade_outcomes(player_id);
CREATE INDEX IF NOT EXISTS idx_go_sport ON grade_outcomes(sport);
CREATE INDEX IF NOT EXISTS idx_go_date ON grade_outcomes(game_date);
CREATE INDEX IF NOT EXISTS idx_go_resolved ON grade_outcomes(resolved_at);
CREATE INDEX IF NOT EXISTS idx_go_grade ON grade_outcomes(grade);
CREATE INDEX IF NOT EXISTS idx_go_abstained ON grade_outcomes(abstained);

-- ============================================================
-- Player Calibrated Weights
-- ============================================================
CREATE TABLE IF NOT EXISTS player_calibrated_weights (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id TEXT NOT NULL,
  sport TEXT NOT NULL,
  stat_type TEXT NOT NULL,
  weights JSONB NOT NULL,
  sample_size INTEGER NOT NULL,
  calibrated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(player_id, sport, stat_type)
);
CREATE INDEX IF NOT EXISTS idx_pcw_player ON player_calibrated_weights(player_id);

-- ============================================================
-- Row Level Security
-- ============================================================
ALTER TABLE nba_data_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE mlb_data_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE grade_outcomes ENABLE ROW LEVEL SECURITY;
ALTER TABLE player_calibrated_weights ENABLE ROW LEVEL SECURITY;

CREATE POLICY nba_c_svc ON nba_data_cache FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY mlb_c_svc ON mlb_data_cache FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY go_svc ON grade_outcomes FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY pcw_svc ON player_calibrated_weights FOR ALL USING (auth.role() = 'service_role');