-- Migration: 003_phase1_additions.sql
-- Phase 1 additions: Role profiles, activations, predictions, joint outcomes, discrepancy scores
-- Created: 2026-03-28

-- ============================================================
-- Table 1: player_role_profiles
-- ============================================================
CREATE TABLE IF NOT EXISTS player_role_profiles (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  player_id TEXT NOT NULL,
  player_name TEXT NOT NULL,
  sport TEXT NOT NULL DEFAULT 'NBA',
  team TEXT,
  role_profile JSONB NOT NULL DEFAULT '{}',
  conditional_roles JSONB DEFAULT '{}',
  dominant_role TEXT,
  role_variance_score NUMERIC(4,3) DEFAULT 0,
  effective_date DATE DEFAULT CURRENT_DATE,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Table 2: lineup_role_profiles
-- ============================================================
CREATE TABLE IF NOT EXISTS lineup_role_profiles (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  lineup_id TEXT NOT NULL,
  player_id TEXT NOT NULL,
  sport TEXT NOT NULL DEFAULT 'NBA',
  team TEXT,
  role_in_this_lineup TEXT,
  historical_production JSONB DEFAULT '{}',
  instances_tracked INTEGER DEFAULT 0,
  last_game_date DATE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Table 3: player_role_activations
-- ============================================================
CREATE TABLE IF NOT EXISTS player_role_activations (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  game_id TEXT,
  player_id TEXT NOT NULL,
  activated_role TEXT NOT NULL,
  activation_trigger TEXT,
  activation_timestamp TIMESTAMPTZ DEFAULT NOW(),
  pre_activation_line NUMERIC(6,2),
  post_activation_line NUMERIC(6,2),
  line_movement_after NUMERIC(6,2),
  actual_result NUMERIC(6,2),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Table 4: model_predictions_extended
-- ============================================================
CREATE TABLE IF NOT EXISTS model_predictions_extended (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  prediction_id UUID REFERENCES picks(id),
  role_stability_score NUMERIC(4,3),
  role_variance_score NUMERIC(4,3),
  active_role_tonight TEXT,
  elevation_detected BOOLEAN DEFAULT FALSE,
  evolution_detected BOOLEAN DEFAULT FALSE,
  evolution_confidence NUMERIC(4,3),
  similarity_instances_found INTEGER DEFAULT 0,
  model_probability NUMERIC(5,4),
  confidence_interval_low NUMERIC(6,2),
  confidence_interval_high NUMERIC(6,2),
  confidence_interval_asymmetric BOOLEAN DEFAULT FALSE,
  optimal_alt_line NUMERIC(6,2),
  optimal_alt_line_edge NUMERIC(5,4),
  discrepancy_window_flag BOOLEAN DEFAULT FALSE,
  clv_at_prediction NUMERIC(6,2),
  clv_at_24hr NUMERIC(6,2),
  clv_at_tip NUMERIC(6,2),
  model_version TEXT DEFAULT '1.0',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Table 5: prediction_registry
-- ============================================================
CREATE TABLE IF NOT EXISTS prediction_registry (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  prediction_type TEXT NOT NULL,
  player_id TEXT NOT NULL,
  sport TEXT NOT NULL,
  stat_type TEXT NOT NULL,
  prediction_detail JSONB NOT NULL,
  registered_at TIMESTAMPTZ DEFAULT NOW(),
  outcome TEXT DEFAULT 'pending' CHECK (outcome IN ('pending', 'verified', 'false')),
  verified_at TIMESTAMPTZ,
  public_url TEXT
);

-- ============================================================
-- Table 6: joint_outcomes
-- ============================================================
CREATE TABLE IF NOT EXISTS joint_outcomes (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  game_id TEXT,
  player_a_id TEXT NOT NULL,
  player_a_stat TEXT NOT NULL,
  player_a_result NUMERIC(6,2),
  player_b_id TEXT NOT NULL,
  player_b_stat TEXT NOT NULL,
  player_b_result NUMERIC(6,2),
  phi_coefficient NUMERIC(5,4),
  sample_size INTEGER DEFAULT 0,
  game_state_at_outcome TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Table 7: discrepancy_reliability_scores
-- ============================================================
CREATE TABLE IF NOT EXISTS discrepancy_reliability_scores (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  prop_type TEXT NOT NULL,
  sport TEXT NOT NULL,
  reliability_score NUMERIC(4,3),
  avg_resolution_minutes NUMERIC(6,1),
  sample_size INTEGER DEFAULT 0,
  last_updated TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Indexes
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_role_profiles_player ON player_role_profiles(player_id);
CREATE INDEX IF NOT EXISTS idx_role_profiles_sport_team ON player_role_profiles(sport, team);
CREATE INDEX IF NOT EXISTS idx_lineup_roles_player ON lineup_role_profiles(player_id);
CREATE INDEX IF NOT EXISTS idx_lineup_roles_lineup ON lineup_role_profiles(lineup_id);
CREATE INDEX IF NOT EXISTS idx_role_activations_player ON player_role_activations(player_id);
CREATE INDEX IF NOT EXISTS idx_role_activations_game ON player_role_activations(game_id);
CREATE INDEX IF NOT EXISTS idx_predictions_ext_prediction ON model_predictions_extended(prediction_id);
CREATE INDEX IF NOT EXISTS idx_prediction_registry_player ON prediction_registry(player_id, sport);
CREATE INDEX IF NOT EXISTS idx_prediction_registry_outcome ON prediction_registry(outcome);
CREATE INDEX IF NOT EXISTS idx_joint_outcomes_players ON joint_outcomes(player_a_id, player_b_id);
CREATE INDEX IF NOT EXISTS idx_joint_outcomes_game ON joint_outcomes(game_id);
CREATE INDEX IF NOT EXISTS idx_discrepancy_scores_prop ON discrepancy_reliability_scores(prop_type, sport);

-- ============================================================
-- Row Level Security
-- ============================================================
ALTER TABLE player_role_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE lineup_role_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE player_role_activations ENABLE ROW LEVEL SECURITY;
ALTER TABLE model_predictions_extended ENABLE ROW LEVEL SECURITY;
ALTER TABLE prediction_registry ENABLE ROW LEVEL SECURITY;
ALTER TABLE joint_outcomes ENABLE ROW LEVEL SECURITY;
ALTER TABLE discrepancy_reliability_scores ENABLE ROW LEVEL SECURITY;

-- Public read on prediction_registry
CREATE POLICY "prediction_registry_public_read" ON prediction_registry
  FOR SELECT USING (true);

-- Service role write on prediction_registry
CREATE POLICY "prediction_registry_service_write" ON prediction_registry
  FOR INSERT WITH CHECK (auth.role() = 'service_role');

-- Service role full access on all other tables
CREATE POLICY "role_profiles_service_access" ON player_role_profiles
  FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY "lineup_roles_service_access" ON lineup_role_profiles
  FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY "role_activations_service_access" ON player_role_activations
  FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY "predictions_ext_service_access" ON model_predictions_extended
  FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY "joint_outcomes_service_access" ON joint_outcomes
  FOR ALL USING (auth.role() = 'service_role');
CREATE POLICY "discrepancy_scores_service_access" ON discrepancy_reliability_scores
  FOR ALL USING (auth.role() = 'service_role');