-- MEV builder blocks table (matches data_pipeline.py output)
-- Data sourced from public MEV-Boost relay APIs
CREATE TABLE IF NOT EXISTS blocks (
    id BIGSERIAL PRIMARY KEY,
    slot BIGINT,
    block_number BIGINT,
    block_hash TEXT,
    builder_pubkey TEXT,
    builder_name TEXT,
    proposer_pubkey TEXT,
    proposer_fee_recipient TEXT,
    value_wei NUMERIC,
    value_eth DOUBLE PRECISION,
    gas_used BIGINT,
    gas_limit BIGINT,
    num_tx INTEGER,
    relays_seen TEXT,
    relay_count INTEGER,
    -- Engineered features
    log_value_eth DOUBLE PRECISION,
    gas_utilization DOUBLE PRECISION,
    log_gas_used DOUBLE PRECISION,
    tx_per_gas DOUBLE PRECISION,
    value_per_gas DOUBLE PRECISION,
    value_per_tx DOUBLE PRECISION,
    relay_coverage DOUBLE PRECISION,
    builder_block_share DOUBLE PRECISION,
    log_num_tx DOUBLE PRECISION,
    payment_zscore DOUBLE PRECISION,
    tx_deficit_ratio DOUBLE PRECISION,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_blocks_slot ON blocks(slot DESC);
CREATE INDEX IF NOT EXISTS idx_blocks_builder ON blocks(builder_name);
CREATE INDEX IF NOT EXISTS idx_blocks_block_number ON blocks(block_number DESC);