-- Rainmaker Database Schema
-- Run: psql -h 127.0.0.1 -p 5433 -U eventheodds -d eventheodds_sports -f schema.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Users table
CREATE TABLE IF NOT EXISTS rm_users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_weatherman BOOLEAN DEFAULT FALSE,
  single_picks INTEGER DEFAULT 0,
  signup_bonus_forecasts INTEGER DEFAULT 0,
  survey_bonus_forecasts INTEGER DEFAULT 0,
  daily_pass_picks INTEGER DEFAULT 0,
  daily_pass_date DATE,
  stripe_customer_id VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_rm_users_email ON rm_users(email);
CREATE INDEX idx_rm_users_stripe ON rm_users(stripe_customer_id);

-- Purchases table
CREATE TABLE IF NOT EXISTS rm_purchases (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES rm_users(id),
  product_type VARCHAR(50) NOT NULL, -- 'single_pick', 'daily_pass', or 'monthly_pass'
  amount_cents INTEGER NOT NULL,
  picks_granted INTEGER NOT NULL,
  stripe_payment_intent_id VARCHAR(255),
  stripe_session_id VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_rm_purchases_user ON rm_purchases(user_id);
CREATE INDEX idx_rm_purchases_created ON rm_purchases(created_at);

-- Forecast cache (shared across users)
CREATE TABLE IF NOT EXISTS rm_forecast_cache (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  event_id VARCHAR(255) UNIQUE NOT NULL,
  league VARCHAR(50) NOT NULL,
  home_team VARCHAR(255),
  away_team VARCHAR(255),
  forecast_data JSONB NOT NULL,
  confidence_score FLOAT,
  starts_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_rm_forecast_event ON rm_forecast_cache(event_id);
CREATE INDEX idx_rm_forecast_expires ON rm_forecast_cache(expires_at);
CREATE INDEX idx_rm_forecast_league ON rm_forecast_cache(league);

-- User picks (tracks which forecasts each user purchased)
CREATE TABLE IF NOT EXISTS rm_user_picks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES rm_users(id),
  forecast_id UUID REFERENCES rm_forecast_cache(id),
  event_id VARCHAR(255) NOT NULL,
  was_weatherman BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_rm_picks_user ON rm_user_picks(user_id);
CREATE INDEX idx_rm_picks_event ON rm_user_picks(event_id);
CREATE UNIQUE INDEX idx_rm_picks_user_event_unique ON rm_user_picks(user_id, event_id);

-- Forecast accuracy (post-game resolution)
CREATE TABLE IF NOT EXISTS rm_forecast_accuracy (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  forecast_id UUID REFERENCES rm_forecast_cache(id),
  event_id VARCHAR(255) NOT NULL,
  league VARCHAR(50) NOT NULL,
  predicted_winner VARCHAR(255),
  actual_winner VARCHAR(255),
  predicted_spread FLOAT,
  actual_spread FLOAT,
  predicted_total FLOAT,
  actual_total FLOAT,
  accuracy_bucket VARCHAR(20), -- '90+', '80-89', '70-79', '60-69', '50-59', '<50'
  accuracy_pct FLOAT,
  resolved_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_rm_accuracy_forecast ON rm_forecast_accuracy(forecast_id);
CREATE INDEX idx_rm_accuracy_bucket ON rm_forecast_accuracy(accuracy_bucket);
CREATE INDEX idx_rm_accuracy_league ON rm_forecast_accuracy(league);

-- Team props cache (shared across users)
CREATE TABLE IF NOT EXISTS rm_team_props_cache (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  event_id VARCHAR(255) NOT NULL,
  team VARCHAR(10) NOT NULL, -- 'home' or 'away'
  team_name VARCHAR(255) NOT NULL,
  team_short VARCHAR(10),
  league VARCHAR(50) NOT NULL,
  props_data JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(event_id, team)
);

CREATE INDEX IF NOT EXISTS idx_rm_team_props_event ON rm_team_props_cache(event_id);

-- Team props unlock records (per-user, per-event, per-team)
CREATE TABLE IF NOT EXISTS rm_team_props_unlocks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES rm_users(id),
  event_id VARCHAR(255) NOT NULL,
  team VARCHAR(10) NOT NULL, -- 'home' or 'away'
  was_weatherman BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, event_id, team)
);

CREATE INDEX IF NOT EXISTS idx_rm_props_unlocks_user ON rm_team_props_unlocks(user_id);
CREATE INDEX IF NOT EXISTS idx_rm_props_unlocks_user_event ON rm_team_props_unlocks(user_id, event_id);

-- Team logo provenance tracking
CREATE TABLE IF NOT EXISTS rm_team_logos (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  league VARCHAR(50) NOT NULL,
  team_abbr VARCHAR(20) NOT NULL,
  team_name VARCHAR(255),
  file_path VARCHAR(500),
  file_exists BOOLEAN DEFAULT FALSE,
  checksum_sha256 VARCHAR(64),
  source VARCHAR(50) DEFAULT 'local_cache', -- local_cache, thesportsdb, manual
  license VARCHAR(100),
  source_url VARCHAR(500),
  resolution_status VARCHAR(20) DEFAULT 'pending', -- resolved, pending, failed
  admin_override BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(league, team_abbr)
);

CREATE INDEX IF NOT EXISTS idx_rm_team_logos_league ON rm_team_logos(league);
CREATE INDEX IF NOT EXISTS idx_rm_team_logos_status ON rm_team_logos(resolution_status);

-- Email verification + daily free forecasts columns on rm_users
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN DEFAULT FALSE;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMPTZ;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS verification_token VARCHAR(255);
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS verification_expires TIMESTAMPTZ;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS verification_email_last_sent_at TIMESTAMPTZ;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS password_reset_token VARCHAR(255);
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS password_reset_expires TIMESTAMPTZ;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS auth_token_version INTEGER NOT NULL DEFAULT 0;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS daily_free_forecasts INTEGER DEFAULT 0;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS signup_bonus_forecasts INTEGER DEFAULT 0;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS survey_bonus_forecasts INTEGER DEFAULT 0;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS last_reset_date_pacific DATE;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS marketing_consent BOOLEAN DEFAULT FALSE;
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS google_sub VARCHAR(255);
CREATE UNIQUE INDEX IF NOT EXISTS idx_rm_users_google_sub ON rm_users(google_sub) WHERE google_sub IS NOT NULL;

-- Country / region for segmentation (IP auto-detect + user override)
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS country_code VARCHAR(2);
ALTER TABLE rm_users ADD COLUMN IF NOT EXISTS country_source VARCHAR(10) DEFAULT 'unknown'; -- 'ip', 'user', 'unknown'
CREATE INDEX IF NOT EXISTS idx_rm_users_country ON rm_users(country_code);

-- Forecast ledger (audit trail for all forecast balance changes)
CREATE TABLE IF NOT EXISTS rm_forecast_ledger (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES rm_users(id),
  change_amount INTEGER NOT NULL,
  reason VARCHAR(50) NOT NULL,  -- DAILY_GRANT, FORECAST/PROP unlocks, PURCHASE_SINGLE, PURCHASE_DAILY_PASS, PURCHASE_MONTHLY, SURVEY_REWARD, VERIFICATION_GRANT, ADMIN_ADJUST
  balance_after INTEGER,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rm_ledger_user ON rm_forecast_ledger(user_id);
CREATE INDEX IF NOT EXISTS idx_rm_ledger_created ON rm_forecast_ledger(created_at);

-- Insight content cache (steam/sharp analysis, shared across users)
CREATE TABLE IF NOT EXISTS rm_insight_cache (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  event_id VARCHAR(255) NOT NULL,
  insight_type VARCHAR(20) NOT NULL,  -- 'STEAM' or 'SHARP'
  league VARCHAR(50) NOT NULL,
  home_team VARCHAR(255),
  away_team VARCHAR(255),
  insight_data JSONB NOT NULL,
  source_data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(event_id, insight_type)
);
CREATE INDEX IF NOT EXISTS idx_rm_insight_cache_event ON rm_insight_cache(event_id);

-- Per-user insight unlock tracking
CREATE TABLE IF NOT EXISTS rm_insight_unlocks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES rm_users(id),
  event_id VARCHAR(255) NOT NULL,
  insight_type VARCHAR(20) NOT NULL,
  was_weatherman BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, event_id, insight_type)
);
CREATE INDEX IF NOT EXISTS idx_rm_insight_unlocks_user ON rm_insight_unlocks(user_id);
CREATE INDEX IF NOT EXISTS idx_rm_insight_unlocks_user_event ON rm_insight_unlocks(user_id, event_id);

-- Operational alert notification log
CREATE TABLE IF NOT EXISTS rm_operational_alert_notifications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  date_et DATE NOT NULL,
  alert_type VARCHAR(50) NOT NULL,
  recipient_email VARCHAR(255) NOT NULL,
  payload_hash VARCHAR(64) NOT NULL,
  delivery_status VARCHAR(20) NOT NULL DEFAULT 'SENT',
  metadata JSONB,
  sent_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (date_et, alert_type, recipient_email, payload_hash)
);

CREATE INDEX IF NOT EXISTS idx_rm_operational_alert_date_type
  ON rm_operational_alert_notifications(date_et, alert_type);

-- Paid traffic router: stable ad slugs, experiment configs, and click attribution
CREATE TABLE IF NOT EXISTS rm_go_routes (
  slug VARCHAR(150) PRIMARY KEY,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  source VARCHAR(100),
  campaign VARCHAR(150),
  creative VARCHAR(150),
  ad_type VARCHAR(50),
  default_destination_path VARCHAR(500),
  notes TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rm_go_routes_active ON rm_go_routes(active);

CREATE TABLE IF NOT EXISTS rm_go_experiments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  slug VARCHAR(150) NOT NULL REFERENCES rm_go_routes(slug) ON DELETE CASCADE,
  name VARCHAR(200) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  traffic_source VARCHAR(100),
  creative VARCHAR(150),
  ad_type VARCHAR(50),
  start_at TIMESTAMPTZ,
  end_at TIMESTAMPTZ,
  notes TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rm_go_experiments_slug_active
  ON rm_go_experiments(slug, active);

CREATE TABLE IF NOT EXISTS rm_go_variants (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  experiment_id UUID NOT NULL REFERENCES rm_go_experiments(id) ON DELETE CASCADE,
  variant_key VARCHAR(100) NOT NULL,
  destination_path VARCHAR(500) NOT NULL,
  weight INTEGER NOT NULL DEFAULT 100,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  notes TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (experiment_id, variant_key)
);

CREATE INDEX IF NOT EXISTS idx_rm_go_variants_experiment_active
  ON rm_go_variants(experiment_id, active);

CREATE TABLE IF NOT EXISTS rm_go_clicks (
  click_id UUID PRIMARY KEY,
  slug VARCHAR(150) NOT NULL,
  experiment_id UUID REFERENCES rm_go_experiments(id) ON DELETE SET NULL,
  variant_id UUID REFERENCES rm_go_variants(id) ON DELETE SET NULL,
  source VARCHAR(100),
  campaign VARCHAR(150),
  creative VARCHAR(150),
  ad_type VARCHAR(50),
  assigned_variant VARCHAR(100),
  destination_path VARCHAR(500),
  destination_url TEXT,
  referrer TEXT,
  ip_address VARCHAR(100),
  user_agent VARCHAR(512),
  query_string TEXT,
  query_params JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rm_go_clicks_slug_created
  ON rm_go_clicks(slug, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_rm_go_clicks_variant_created
  ON rm_go_clicks(assigned_variant, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_rm_go_clicks_source_created
  ON rm_go_clicks(source, created_at DESC);

CREATE TABLE IF NOT EXISTS rm_admin_action_audit (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES rm_users(id) ON DELETE SET NULL,
  user_email VARCHAR(255),
  area VARCHAR(100) NOT NULL,
  action VARCHAR(255) NOT NULL,
  allowed BOOLEAN NOT NULL DEFAULT TRUE,
  status_code INTEGER NOT NULL,
  ip_address VARCHAR(100),
  user_agent VARCHAR(512),
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rm_admin_action_audit_created_at
  ON rm_admin_action_audit(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_rm_admin_action_audit_user_id
  ON rm_admin_action_audit(user_id, created_at DESC);

ALTER TABLE rm_pending_orders ADD COLUMN IF NOT EXISTS attribution JSONB;
CREATE INDEX IF NOT EXISTS idx_rm_pending_orders_click_id
  ON rm_pending_orders ((attribution->>'click_id'));

ALTER TABLE rm_purchases ADD COLUMN IF NOT EXISTS attribution JSONB;
CREATE INDEX IF NOT EXISTS idx_rm_purchases_click_id
  ON rm_purchases ((attribution->>'click_id'));

CREATE OR REPLACE VIEW rm_go_reporting AS
SELECT
  c.slug,
  c.source,
  c.campaign,
  c.creative,
  c.ad_type,
  c.assigned_variant AS landing_variant,
  COUNT(*) AS clicks,
  COUNT(*) FILTER (
    WHERE EXISTS (
      SELECT 1
      FROM site_pageviews pv
      WHERE pv.site = 'rainmaker'
        AND pv.attribution ->> 'click_id' = c.click_id::text
    )
  ) AS landing_views,
  COUNT(*) FILTER (
    WHERE EXISTS (
      SELECT 1
      FROM site_conversions sc
      WHERE sc.site = 'rainmaker'
        AND sc.event = 'signup'
        AND sc.event_data -> 'attribution' ->> 'click_id' = c.click_id::text
    )
  ) AS signups,
  COUNT(*) FILTER (
    WHERE EXISTS (
      SELECT 1
      FROM rm_user_behavior ub
      WHERE ub.event_type = 'checkout_start'
        AND ub.event_data -> 'attribution' ->> 'click_id' = c.click_id::text
    )
  ) AS checkout_starts,
  COUNT(*) FILTER (
    WHERE EXISTS (
      SELECT 1
      FROM site_conversions sc
      WHERE sc.site = 'rainmaker'
        AND sc.event = 'purchase_complete'
        AND sc.event_data -> 'attribution' ->> 'click_id' = c.click_id::text
    )
  ) AS purchases,
  MIN(c.created_at) AS first_click_at,
  MAX(c.created_at) AS last_click_at
FROM rm_go_clicks c
GROUP BY c.slug, c.source, c.campaign, c.creative, c.ad_type, c.assigned_variant;

-- Persisted MLB prop-feed coverage and zero-candidate issue history
CREATE TABLE IF NOT EXISTS rm_mlb_operational_issues (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  date_et DATE NOT NULL,
  event_id VARCHAR(255) NOT NULL,
  starts_at TIMESTAMPTZ,
  matchup VARCHAR(255) NOT NULL,
  home_team VARCHAR(20) NOT NULL,
  away_team VARCHAR(20) NOT NULL,
  home_feed_rows INTEGER NOT NULL DEFAULT 0,
  away_feed_rows INTEGER NOT NULL DEFAULT 0,
  home_candidate_count INTEGER NOT NULL DEFAULT 0,
  away_candidate_count INTEGER NOT NULL DEFAULT 0,
  zero_candidate_teams JSONB NOT NULL DEFAULT '[]'::jsonb,
  issue_type VARCHAR(50) NOT NULL,
  captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  metadata JSONB,
  UNIQUE (date_et, event_id, issue_type)
);

CREATE INDEX IF NOT EXISTS idx_rm_mlb_operational_issues_date
  ON rm_mlb_operational_issues(date_et);

CREATE INDEX IF NOT EXISTS idx_rm_mlb_operational_issues_type
  ON rm_mlb_operational_issues(issue_type);

-- Operational cron registry + run history
CREATE TABLE IF NOT EXISTS rm_operational_crons (
  slug VARCHAR(100) PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  schedule VARCHAR(100) NOT NULL,
  description TEXT,
  category VARCHAR(100),
  priority VARCHAR(20) NOT NULL DEFAULT 'NORMAL',
  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  safe_mode BOOLEAN NOT NULL DEFAULT TRUE,
  non_blocking BOOLEAN NOT NULL DEFAULT TRUE,
  last_run TIMESTAMPTZ,
  avg_runtime_ms INTEGER,
  error_rate NUMERIC(6,2),
  notes JSONB NOT NULL DEFAULT '[]'::jsonb,
  metrics JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT rm_operational_crons_priority_check
    CHECK (priority IN ('LOW', 'NORMAL', 'HIGH', 'CRITICAL')),
  CONSTRAINT rm_operational_crons_status_check
    CHECK (status IN ('ACTIVE', 'PAUSED', 'ERROR', 'DISABLED'))
);

CREATE TABLE IF NOT EXISTS rm_operational_cron_runs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  cron_slug VARCHAR(100) NOT NULL REFERENCES rm_operational_crons(slug) ON DELETE CASCADE,
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  status VARCHAR(20) NOT NULL DEFAULT 'RUNNING',
  duration_ms INTEGER,
  metrics JSONB NOT NULL DEFAULT '{}'::jsonb,
  error_message TEXT,
  log_blob TEXT,
  CONSTRAINT rm_operational_cron_runs_status_check
    CHECK (status IN ('RUNNING', 'SUCCESS', 'FAILED', 'SKIPPED'))
);

CREATE INDEX IF NOT EXISTS idx_rm_operational_cron_runs_slug_started
  ON rm_operational_cron_runs(cron_slug, started_at DESC);

CREATE INDEX IF NOT EXISTS idx_rm_operational_cron_runs_status
  ON rm_operational_cron_runs(status, started_at DESC);

-- Normalized MLB player prop markets built from raw PlayerPropLine rows.
CREATE TABLE IF NOT EXISTS rm_mlb_normalized_player_prop_markets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  event_id TEXT NOT NULL,
  league VARCHAR(20) NOT NULL DEFAULT 'mlb',
  starts_at TIMESTAMPTZ,
  home_team VARCHAR(20),
  away_team VARCHAR(20),
  player_id TEXT NOT NULL,
  player_name VARCHAR(255) NOT NULL,
  team_short VARCHAR(20),
  stat_type TEXT NOT NULL,
  normalized_stat_type TEXT NOT NULL,
  line NUMERIC(10,3) NOT NULL,
  market_name TEXT,
  primary_source VARCHAR(100),
  completion_method VARCHAR(32) NOT NULL DEFAULT 'none',
  completeness_status VARCHAR(32) NOT NULL DEFAULT 'incomplete',
  is_gap_filled BOOLEAN NOT NULL DEFAULT FALSE,
  available_sides TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  over_payload JSONB,
  under_payload JSONB,
  source_map JSONB NOT NULL DEFAULT '[]'::jsonb,
  raw_market_count INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT rm_mlb_normalized_player_prop_markets_completion_method_check
    CHECK (completion_method IN ('none', 'source', 'multi_source')),
  CONSTRAINT rm_mlb_normalized_player_prop_markets_completeness_check
    CHECK (completeness_status IN ('source_complete', 'multi_source_complete', 'incomplete')),
  CONSTRAINT rm_mlb_normalized_player_prop_markets_unique_market
    UNIQUE (event_id, player_id, stat_type, line)
);

CREATE INDEX IF NOT EXISTS idx_rm_mlb_normalized_markets_event
  ON rm_mlb_normalized_player_prop_markets(event_id, completeness_status);

CREATE INDEX IF NOT EXISTS idx_rm_mlb_normalized_markets_updated
  ON rm_mlb_normalized_player_prop_markets(updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_rm_mlb_normalized_markets_player
  ON rm_mlb_normalized_player_prop_markets(player_id, stat_type, line);

-- CRM linkage
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'crm_contacts' AND column_name = 'rm_user_id'
  ) THEN
    ALTER TABLE crm_contacts ADD COLUMN rm_user_id UUID;
  END IF;
END $$;
