CREATE TABLE IF NOT EXISTS symbol_universe_cache (
    symbol TEXT PRIMARY KEY,
    base TEXT NOT NULL,
    quote TEXT NOT NULL DEFAULT 'USDT',
    decision TEXT NOT NULL DEFAULT 'excluded',
    reason_code TEXT NOT NULL DEFAULT '',
    reason_label TEXT NOT NULL DEFAULT '',
    reason_type TEXT NOT NULL DEFAULT 'dynamic',
    source TEXT NOT NULL DEFAULT 'screener',
    evidence_json TEXT DEFAULT '{}',
    first_seen_at TEXT NOT NULL,
    last_seen_at TEXT NOT NULL,
    expires_at TEXT DEFAULT '',
    hit_count INTEGER NOT NULL DEFAULT 0,
    manual_override INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_symbol_universe_cache_decision_expires
    ON symbol_universe_cache(decision, expires_at);
CREATE INDEX IF NOT EXISTS idx_symbol_universe_cache_reason
    ON symbol_universe_cache(reason_code, last_seen_at DESC);

CREATE TABLE IF NOT EXISTS screening_coverage_audit (
    id BIGSERIAL PRIMARY KEY,
    scan_started_at TEXT NOT NULL,
    scan_finished_at TEXT NOT NULL,
    source TEXT NOT NULL DEFAULT 'binance_spot_usdt_market',
    status TEXT NOT NULL DEFAULT 'completed',
    raw_ticker_count INTEGER NOT NULL DEFAULT 0,
    usdt_pair_count INTEGER NOT NULL DEFAULT 0,
    tradable_universe_count INTEGER NOT NULL DEFAULT 0,
    cached_exclusion_count INTEGER NOT NULL DEFAULT 0,
    universe_gate_count INTEGER NOT NULL DEFAULT 0,
    static_exclusion_count INTEGER NOT NULL DEFAULT 0,
    dynamic_exclusion_count INTEGER NOT NULL DEFAULT 0,
    low_turnover_count INTEGER NOT NULL DEFAULT 0,
    stale_ticker_count INTEGER NOT NULL DEFAULT 0,
    kline_attempt_count INTEGER NOT NULL DEFAULT 0,
    kline_h1_success_count INTEGER NOT NULL DEFAULT 0,
    kline_h4_success_count INTEGER NOT NULL DEFAULT 0,
    coarse_candidate_count INTEGER NOT NULL DEFAULT 0,
    fine_qualified_count INTEGER NOT NULL DEFAULT 0,
    quality_rejected_count INTEGER NOT NULL DEFAULT 0,
    top_gainer_discovery_count INTEGER NOT NULL DEFAULT 0,
    detail_json TEXT DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_screening_coverage_audit_started
    ON screening_coverage_audit(scan_started_at DESC);
CREATE INDEX IF NOT EXISTS idx_screening_coverage_audit_status
    ON screening_coverage_audit(status, scan_started_at DESC);