-- ============================================================
-- Rainmaker 1.0 — NCAA Tournament Intelligence Stack
-- 11-Layer Contextual Intelligence Schema
-- ============================================================

-- Tournament venues with geolocation + environment data
CREATE TABLE IF NOT EXISTS rm_tournament_venues (
  id SERIAL PRIMARY KEY,
  season INTEGER NOT NULL,
  round TEXT NOT NULL,           -- first_four, first_second, sweet16_elite8, final_four
  region TEXT,                   -- south, west, midwest, east (NULL for first_four/final_four)
  venue_name TEXT NOT NULL,
  city TEXT NOT NULL,
  state TEXT NOT NULL,
  latitude DOUBLE PRECISION,
  longitude DOUBLE PRECISION,
  capacity INTEGER,
  altitude_ft INTEGER DEFAULT 0,
  venue_type TEXT DEFAULT 'arena',  -- arena | stadium
  host_school TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(season, venue_name, round)
);

-- Team campus/arena geolocation for distance calculations
CREATE TABLE IF NOT EXISTS rm_team_geo (
  id SERIAL PRIMARY KEY,
  team_name TEXT NOT NULL,
  team_name_canonical TEXT,
  city TEXT,
  state TEXT,
  latitude DOUBLE PRECISION,
  longitude DOUBLE PRECISION,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(team_name)
);

-- Bracket assignments (populated when Selection Sunday announces bracket)
CREATE TABLE IF NOT EXISTS rm_tournament_bracket (
  id SERIAL PRIMARY KEY,
  season INTEGER NOT NULL,
  team_name TEXT NOT NULL,
  team_name_canonical TEXT,
  seed INTEGER NOT NULL,
  region TEXT NOT NULL,          -- south, west, midwest, east
  first_round_venue TEXT,
  play_in BOOLEAN DEFAULT FALSE,
  eliminated BOOLEAN DEFAULT FALSE,
  current_round TEXT DEFAULT 'first_round',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(season, team_name)
);

-- Historical seed matchup upset rates (1985-2025, 40 years)
CREATE TABLE IF NOT EXISTS rm_seed_upset_history (
  id SERIAL PRIMARY KEY,
  high_seed INTEGER NOT NULL,   -- favored (lower number)
  low_seed INTEGER NOT NULL,    -- underdog (higher number)
  round TEXT NOT NULL DEFAULT 'first_round',
  total_games INTEGER NOT NULL,
  high_seed_wins INTEGER NOT NULL,
  low_seed_wins INTEGER NOT NULL,
  upset_rate DOUBLE PRECISION NOT NULL,
  notable_upsets TEXT,           -- JSON array of notable upsets
  last_updated TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(high_seed, low_seed, round)
);

-- Coach tournament history
CREATE TABLE IF NOT EXISTS rm_coach_tourney_history (
  id SERIAL PRIMARY KEY,
  coach_name TEXT NOT NULL,
  current_team TEXT,
  tourney_appearances INTEGER DEFAULT 0,
  tourney_wins INTEGER DEFAULT 0,
  sweet16 INTEGER DEFAULT 0,
  elite8 INTEGER DEFAULT 0,
  final4 INTEGER DEFAULT 0,
  championship_games INTEGER DEFAULT 0,
  titles INTEGER DEFAULT 0,
  last_tourney_year INTEGER,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(coach_name)
);

-- Computed contextual scores per matchup (the 11-layer output)
CREATE TABLE IF NOT EXISTS rm_tournament_context (
  id SERIAL PRIMARY KEY,
  season INTEGER NOT NULL,
  event_id TEXT,
  forecast_id TEXT,
  team_a TEXT NOT NULL,
  team_b TEXT NOT NULL,
  seed_a INTEGER,
  seed_b INTEGER,
  venue TEXT,
  round TEXT,

  -- Layer 1: Travel
  travel_distance_a DOUBLE PRECISION,
  travel_distance_b DOUBLE PRECISION,
  travel_time_a DOUBLE PRECISION,
  travel_time_b DOUBLE PRECISION,
  travel_class_a TEXT,
  travel_class_b TEXT,
  travel_disparity DOUBLE PRECISION,

  -- Layer 2: Crowd
  crowd_score_a DOUBLE PRECISION,
  crowd_score_b DOUBLE PRECISION,
  crowd_modifier DOUBLE PRECISION,

  -- Layer 3: Arena Environment
  arena_env_score DOUBLE PRECISION,

  -- Layer 4: Referee
  referee_score DOUBLE PRECISION,

  -- Layer 5: Fatigue
  fatigue_score_a DOUBLE PRECISION,
  fatigue_score_b DOUBLE PRECISION,

  -- Layer 6: Tournament Experience
  experience_score_a DOUBLE PRECISION,
  experience_score_b DOUBLE PRECISION,

  -- Layer 7: Seed Volatility
  seed_volatility DOUBLE PRECISION,

  -- Layer 8: Coaching Matchup
  coaching_matchup DOUBLE PRECISION,

  -- Layer 9: Market Sentiment
  market_sentiment DOUBLE PRECISION,

  -- Layer 10: Player Exploitation
  player_exploit_a DOUBLE PRECISION,
  player_exploit_b DOUBLE PRECISION,

  -- Layer 11: Depth Volatility
  depth_vol_a DOUBLE PRECISION,
  depth_vol_b DOUBLE PRECISION,

  -- Final
  contextual_adjustment DOUBLE PRECISION,
  adjustment_direction TEXT,     -- favors_a | favors_b | neutral
  contextual_detail JSONB,

  computed_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(season, team_a, team_b, round)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_tourney_venues_season ON rm_tournament_venues(season);
CREATE INDEX IF NOT EXISTS idx_team_geo_name ON rm_team_geo(team_name);
CREATE INDEX IF NOT EXISTS idx_tourney_bracket_season ON rm_tournament_bracket(season);
CREATE INDEX IF NOT EXISTS idx_tourney_context_season ON rm_tournament_context(season);
CREATE INDEX IF NOT EXISTS idx_tourney_context_event ON rm_tournament_context(event_id);
CREATE INDEX IF NOT EXISTS idx_coach_history_team ON rm_coach_tourney_history(current_team);
