-- Traces table for storing transaction traces

CREATE TABLE IF NOT EXISTS traces (
    chain_id INTEGER NOT NULL,
    transaction_hash VARCHAR(66) NOT NULL,
    block_number BIGINT NOT NULL,
    trace_data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (chain_id, transaction_hash)
) PARTITION BY LIST (chain_id);

-- Create partition for ChainID 138
CREATE TABLE IF NOT EXISTS traces_chain_138 PARTITION OF traces FOR VALUES IN (138);

-- Index
CREATE INDEX IF NOT EXISTS idx_traces_chain_tx ON traces(chain_id, transaction_hash);
CREATE INDEX IF NOT EXISTS idx_traces_chain_block ON traces(chain_id, block_number);
CREATE INDEX IF NOT EXISTS idx_traces_data_gin ON traces USING GIN (trace_data);