-- Roster reconciliation tables for ESPN → SGO corrections

CREATE TABLE IF NOT EXISTS rm_roster_reconciliation (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  run_id UUID NOT NULL,
  league VARCHAR(20) NOT NULL,
  correction_type VARCHAR(30) NOT NULL,  -- name_mismatch, team_mismatch, missing_from_sgo, cut_from_roster
  player_name_sgo VARCHAR(200),
  player_name_espn VARCHAR(200),
  player_id_sgo VARCHAR(200),
  player_id_espn VARCHAR(50),
  team_sgo VARCHAR(100),
  team_espn VARCHAR(100),
  match_tier INTEGER,                    -- 1=exact, 2=suffix-stripped, 3=initial-match
  match_confidence DOUBLE PRECISION,
  applied BOOLEAN DEFAULT FALSE,
  details JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rm_roster_recon_run ON rm_roster_reconciliation(run_id);
CREATE INDEX IF NOT EXISTS idx_rm_roster_recon_league ON rm_roster_reconciliation(league);
CREATE INDEX IF NOT EXISTS idx_rm_roster_recon_type ON rm_roster_reconciliation(correction_type);

CREATE TABLE IF NOT EXISTS rm_roster_reconciliation_runs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  started_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  status VARCHAR(20) NOT NULL DEFAULT 'RUNNING',  -- RUNNING, COMPLETED, FAILED
  leagues_processed TEXT[],
  total_espn_players INTEGER DEFAULT 0,
  total_sgo_players INTEGER DEFAULT 0,
  total_matched INTEGER DEFAULT 0,
  total_name_corrections INTEGER DEFAULT 0,
  total_team_corrections INTEGER DEFAULT 0,
  total_missing INTEGER DEFAULT 0,
  total_cut INTEGER DEFAULT 0,
  total_applied INTEGER DEFAULT 0,
  duration_ms INTEGER,
  error_message TEXT
);
