-- ============================================================================
-- Publishing Integrity Schema
-- Rainmaker: Roster, Availability, Lineup, and Prop Eligibility Validation
-- Deployed: 2026-03-10
-- ============================================================================

-- ── Prop Eligibility Status ────────────────────────────────────────────────
-- Core table: tracks validation state per player-prop per game
CREATE TABLE IF NOT EXISTS pi_prop_eligibility (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    run_id          VARCHAR(20) NOT NULL,
    game_id         VARCHAR(255) NOT NULL,
    player_id       VARCHAR(255) NOT NULL,
    player_name     VARCHAR(255) NOT NULL,
    team_id         VARCHAR(50) DEFAULT '',
    league          VARCHAR(50) NOT NULL,
    validation_state VARCHAR(60) NOT NULL DEFAULT 'MANUAL_REVIEW_REQUIRED',
    market_presence_flag BOOLEAN DEFAULT FALSE,
    roster_verified_at   TIMESTAMPTZ,
    availability_verified_at TIMESTAMPTZ,
    lineup_verified_at   TIMESTAMPTZ,
    suppress_reason      VARCHAR(60),
    suppress_reason_human TEXT,
    winning_source       VARCHAR(100),
    conflict_payload     JSONB,
    publish_allowed      BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (game_id, player_id)
);

CREATE INDEX IF NOT EXISTS idx_pi_elig_game ON pi_prop_eligibility(game_id);
CREATE INDEX IF NOT EXISTS idx_pi_elig_player ON pi_prop_eligibility(player_id);
CREATE INDEX IF NOT EXISTS idx_pi_elig_league ON pi_prop_eligibility(league);
CREATE INDEX IF NOT EXISTS idx_pi_elig_state ON pi_prop_eligibility(validation_state);
CREATE INDEX IF NOT EXISTS idx_pi_elig_publish ON pi_prop_eligibility(publish_allowed);

-- ── Publish Audit Log ──────────────────────────────────────────────────────
-- Immutable log of all publish/suppress/unpublish decisions
CREATE TABLE IF NOT EXISTS pi_publish_audit_log (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    run_id          VARCHAR(20) NOT NULL,
    game_id         VARCHAR(255) NOT NULL,
    player_id       VARCHAR(255) NOT NULL,
    player_name     VARCHAR(255) NOT NULL DEFAULT '',
    team_id         VARCHAR(50) DEFAULT '',
    league          VARCHAR(50) NOT NULL DEFAULT '',
    action          VARCHAR(30) NOT NULL,
    validation_state VARCHAR(60) NOT NULL,
    suppress_reason  VARCHAR(60),
    suppress_reason_human TEXT,
    winning_source   VARCHAR(100),
    conflict_payload JSONB,
    prior_state      VARCHAR(60),
    acted_at        TIMESTAMPTZ DEFAULT NOW(),
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pi_audit_game ON pi_publish_audit_log(game_id);
CREATE INDEX IF NOT EXISTS idx_pi_audit_player ON pi_publish_audit_log(player_id);
CREATE INDEX IF NOT EXISTS idx_pi_audit_action ON pi_publish_audit_log(action);
CREATE INDEX IF NOT EXISTS idx_pi_audit_acted ON pi_publish_audit_log(acted_at DESC);

-- ── Regression Failures ────────────────────────────────────────────────────
-- Track test failures from automated regression runs
CREATE TABLE IF NOT EXISTS pi_regression_failures (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    run_id          VARCHAR(20) NOT NULL,
    test_name       VARCHAR(255) NOT NULL,
    league          VARCHAR(50) NOT NULL DEFAULT '',
    entity_type     VARCHAR(50) NOT NULL DEFAULT '',
    entity_id       VARCHAR(255) NOT NULL DEFAULT '',
    severity        VARCHAR(20) NOT NULL DEFAULT 'high',
    failure_reason  TEXT NOT NULL,
    failure_payload JSONB,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pi_regfail_run ON pi_regression_failures(run_id);
CREATE INDEX IF NOT EXISTS idx_pi_regfail_test ON pi_regression_failures(test_name);

-- ── Roster Daily Snapshot ──────────────────────────────────────────────────
-- Point-in-time snapshot of roster state (for drift detection)
CREATE TABLE IF NOT EXISTS pi_rosters_daily (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    run_id          VARCHAR(20) NOT NULL,
    league          VARCHAR(50) NOT NULL,
    team_id         VARCHAR(50) NOT NULL,
    player_id       VARCHAR(255) NOT NULL,
    player_name     VARCHAR(255) NOT NULL DEFAULT '',
    roster_status   VARCHAR(50) DEFAULT 'active',
    source          VARCHAR(100) NOT NULL DEFAULT '',
    source_timestamp TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pi_roster_league ON pi_rosters_daily(league);
CREATE INDEX IF NOT EXISTS idx_pi_roster_team ON pi_rosters_daily(team_id);
CREATE INDEX IF NOT EXISTS idx_pi_roster_date ON pi_rosters_daily(created_at);

-- ── Availability Reports ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pi_availability_reports (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    run_id          VARCHAR(20) NOT NULL,
    game_id         VARCHAR(255) NOT NULL DEFAULT '',
    player_id       VARCHAR(255) NOT NULL,
    player_name     VARCHAR(255) NOT NULL DEFAULT '',
    team_id         VARCHAR(50) DEFAULT '',
    availability_status VARCHAR(30) NOT NULL DEFAULT 'UNKNOWN',
    detailed_status TEXT DEFAULT '',
    source          VARCHAR(100) NOT NULL DEFAULT '',
    source_timestamp TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pi_avail_game ON pi_availability_reports(game_id);
CREATE INDEX IF NOT EXISTS idx_pi_avail_player ON pi_availability_reports(player_id);

-- ── Lineup Confirmations ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pi_lineup_confirmations (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    run_id          VARCHAR(20) NOT NULL,
    game_id         VARCHAR(255) NOT NULL DEFAULT '',
    player_id       VARCHAR(255) NOT NULL,
    player_name     VARCHAR(255) NOT NULL DEFAULT '',
    team_id         VARCHAR(50) DEFAULT '',
    lineup_status   VARCHAR(30) NOT NULL DEFAULT 'UNKNOWN',
    lineup_role     VARCHAR(50) DEFAULT '',
    source          VARCHAR(100) NOT NULL DEFAULT '',
    source_timestamp TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pi_lineup_game ON pi_lineup_confirmations(game_id);
CREATE INDEX IF NOT EXISTS idx_pi_lineup_player ON pi_lineup_confirmations(player_id);

-- ── Market Player Props ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pi_market_player_props (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    run_id          VARCHAR(20) NOT NULL,
    sportsbook      VARCHAR(100) DEFAULT '',
    game_id         VARCHAR(255) NOT NULL DEFAULT '',
    player_id       VARCHAR(255) NOT NULL,
    player_name     VARCHAR(255) NOT NULL DEFAULT '',
    team_id         VARCHAR(50) DEFAULT '',
    market_type     VARCHAR(100) DEFAULT '',
    market_status   VARCHAR(30) DEFAULT 'active',
    source_timestamp TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pi_market_game ON pi_market_player_props(game_id);
CREATE INDEX IF NOT EXISTS idx_pi_market_player ON pi_market_player_props(player_id);

-- ── Feature Flags (persistent, editable via admin) ─────────────────────────
CREATE TABLE IF NOT EXISTS pi_feature_flags (
    flag_name       VARCHAR(100) PRIMARY KEY,
    flag_value      BOOLEAN NOT NULL DEFAULT TRUE,
    description     TEXT DEFAULT '',
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- Insert default flags
INSERT INTO pi_feature_flags (flag_name, flag_value, description) VALUES
    ('publishing_integrity_enabled', true, 'Master switch for the entire system'),
    ('pi_league_nba', true, 'Enable for NBA'),
    ('pi_league_mlb', true, 'Enable for MLB'),
    ('pi_league_nhl', true, 'Enable for NHL'),
    ('pi_league_mls', true, 'Enable for MLS'),
    ('pi_league_epl', true, 'Enable for EPL'),
    ('pi_league_ncaab', true, 'Enable for NCAAB'),
    ('pi_league_soccer', true, 'Enable for all soccer leagues'),
    ('pi_require_market_confirmation', false, 'Require sportsbook market to exist'),
    ('pi_require_confirmed_lineups', false, 'Require confirmed lineups for all props')
ON CONFLICT (flag_name) DO NOTHING;
