-- TimescaleDB schema for mempool transactions
-- This extends the main database with time-series capabilities

-- Mempool transactions hypertable
CREATE TABLE IF NOT EXISTS mempool_transactions (
    time TIMESTAMPTZ NOT NULL,
    chain_id INTEGER NOT NULL,
    hash VARCHAR(66) NOT NULL,
    from_address VARCHAR(42) NOT NULL,
    to_address VARCHAR(42),
    value NUMERIC(78, 0),
    gas_price BIGINT,
    max_fee_per_gas BIGINT,
    max_priority_fee_per_gas BIGINT,
    gas_limit BIGINT,
    nonce BIGINT,
    input_data_length INTEGER,
    first_seen TIMESTAMPTZ NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    confirmed_block_number BIGINT,
    confirmed_at TIMESTAMPTZ,
    PRIMARY KEY (time, chain_id, hash)
);

-- Convert to hypertable
SELECT create_hypertable('mempool_transactions', 'time', if_not_exists => TRUE);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_mempool_chain_hash ON mempool_transactions(chain_id, hash);
CREATE INDEX IF NOT EXISTS idx_mempool_chain_from ON mempool_transactions(chain_id, from_address);
CREATE INDEX IF NOT EXISTS idx_mempool_chain_status ON mempool_transactions(chain_id, status, time);

-- Network metrics hypertable
CREATE TABLE IF NOT EXISTS network_metrics (
    time TIMESTAMPTZ NOT NULL,
    chain_id INTEGER NOT NULL,
    block_number BIGINT,
    tps DOUBLE PRECISION,
    gps DOUBLE PRECISION,
    avg_gas_price BIGINT,
    pending_transactions INTEGER,
    block_time_seconds DOUBLE PRECISION,
    PRIMARY KEY (time, chain_id)
);

SELECT create_hypertable('network_metrics', 'time', if_not_exists => TRUE);

CREATE INDEX IF NOT EXISTS idx_network_metrics_chain_time ON network_metrics(chain_id, time DESC);

-- Gas price history hypertable
CREATE TABLE IF NOT EXISTS gas_price_history (
    time TIMESTAMPTZ NOT NULL,
    chain_id INTEGER NOT NULL,
    block_number BIGINT,
    min_gas_price BIGINT,
    max_gas_price BIGINT,
    avg_gas_price BIGINT,
    p25_gas_price BIGINT,
    p50_gas_price BIGINT,
    p75_gas_price BIGINT,
    p95_gas_price BIGINT,
    p99_gas_price BIGINT,
    PRIMARY KEY (time, chain_id)
);

SELECT create_hypertable('gas_price_history', 'time', if_not_exists => TRUE);

-- Continuous aggregate for 1-minute network metrics
CREATE MATERIALIZED VIEW IF NOT EXISTS network_metrics_1m
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 minute', time) AS bucket,
    chain_id,
    AVG(tps) AS avg_tps,
    AVG(gps) AS avg_gps,
    AVG(avg_gas_price) AS avg_gas_price,
    AVG(pending_transactions) AS avg_pending_tx
FROM network_metrics
GROUP BY bucket, chain_id;

-- Add refresh policy for continuous aggregate
SELECT add_continuous_aggregate_policy('network_metrics_1m',
    start_offset => INTERVAL '1 hour',
    end_offset => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute',
    if_not_exists => TRUE);