-- Migration: 008_supplement_tables.sql
-- Supplement intelligence systems: coaching tendencies, player-out history,
-- evolution detections, unconventional validations.
-- Created: 2026-04-13

-- ============================================================
-- Coaching Tendencies
-- ============================================================
CREATE TABLE IF NOT EXISTS coaching_tendencies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  coach_id TEXT NOT NULL,
  team_id TEXT NOT NULL,
  sport TEXT NOT NULL,
  season TEXT NOT NULL,
  tendencies JSONB NOT NULL,
  games_sampled INTEGER DEFAULT 0,
  last_updated DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(coach_id, team_id, sport, season)
);
CREATE INDEX IF NOT EXISTS idx_ct_coach ON coaching_tendencies(coach_id);
CREATE INDEX IF NOT EXISTS idx_ct_team ON coaching_tendencies(team_id);

-- ============================================================
-- Player-Out History (redistribution training data)
-- ============================================================
CREATE TABLE IF NOT EXISTS player_out_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  player_out_id TEXT NOT NULL,
  team_id TEXT NOT NULL,
  game_id TEXT NOT NULL,
  game_date DATE NOT NULL,
  beneficiary_stats JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_poh_player ON player_out_history(player_out_id);
CREATE INDEX IF NOT EXISTS idx_poh_team ON player_out_history(team_id);

-- ============================================================
-- Evolution Detections (timestamped accuracy ledger)
-- ============================================================
CREATE TABLE IF NOT EXISTS evolution_detections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id TEXT NOT NULL,
  player_name TEXT,
  sport TEXT NOT NULL,
  detection_date DATE NOT NULL,
  metrics JSONB NOT NULL,
  market_adjusted_at DATE,
  confirmed BOOLEAN,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ed_player ON evolution_detections(player_id);
CREATE INDEX IF NOT EXISTS idx_ed_date ON evolution_detections(detection_date);

-- ============================================================
-- Unconventional Factor Validation Log
-- ============================================================
CREATE TABLE IF NOT EXISTS unconventional_validations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  factor_name TEXT NOT NULL,
  validated BOOLEAN NOT NULL,
  pearson_r DECIMAL,
  p_value DECIMAL,
  corrected_alpha DECIMAL,
  sample_size INTEGER,
  validated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_uv_factor ON unconventional_validations(factor_name);

-- ============================================================
-- Row Level Security
-- ============================================================
ALTER TABLE coaching_tendencies ENABLE ROW LEVEL SECURITY;
ALTER TABLE player_out_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE evolution_detections ENABLE ROW LEVEL SECURITY;
ALTER TABLE unconventional_validations ENABLE ROW LEVEL SECURITY;

CREATE POLICY ct_svc ON coaching_tendencies FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY poh_svc ON player_out_history FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY ed_svc ON evolution_detections FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY uv_svc ON unconventional_validations FOR ALL USING (auth.role() = 'service_role');