-- Migration: Token Aggregation Schema
-- Description: Creates tables for token market data, liquidity pools, OHLCV, and external API cache
-- Supports ChainID 138 and 651940

-- Token Market Data - Aggregated market metrics per token
CREATE TABLE IF NOT EXISTS token_market_data (
    id BIGSERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    token_address VARCHAR(42) NOT NULL,
    price_usd NUMERIC(30, 8),
    price_change_24h NUMERIC(10, 4),
    volume_24h NUMERIC(30, 8) DEFAULT 0,
    volume_7d NUMERIC(30, 8) DEFAULT 0,
    volume_30d NUMERIC(30, 8) DEFAULT 0,
    market_cap_usd NUMERIC(30, 8),
    liquidity_usd NUMERIC(30, 8) DEFAULT 0,
    holders_count INTEGER DEFAULT 0,
    transfers_24h INTEGER DEFAULT 0,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, token_address)
) PARTITION BY LIST (chain_id);

-- Create partitions for supported chains
CREATE TABLE IF NOT EXISTS token_market_data_chain_138 PARTITION OF token_market_data FOR VALUES IN (138);
CREATE TABLE IF NOT EXISTS token_market_data_chain_651940 PARTITION OF token_market_data FOR VALUES IN (651940);

CREATE INDEX idx_token_market_data_chain_token ON token_market_data(chain_id, token_address);
CREATE INDEX idx_token_market_data_price ON token_market_data(price_usd) WHERE price_usd IS NOT NULL;
CREATE INDEX idx_token_market_data_volume ON token_market_data(volume_24h) WHERE volume_24h > 0;
CREATE INDEX idx_token_market_data_last_updated ON token_market_data(last_updated);

-- Liquidity Pools - DEX pool information
CREATE TABLE IF NOT EXISTS liquidity_pools (
    id BIGSERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    pool_address VARCHAR(42) NOT NULL,
    token0_address VARCHAR(42) NOT NULL,
    token1_address VARCHAR(42) NOT NULL,
    dex_type VARCHAR(20) NOT NULL CHECK (dex_type IN ('uniswap_v2', 'uniswap_v3', 'dodo', 'custom')),
    factory_address VARCHAR(42),
    router_address VARCHAR(42),
    reserve0 NUMERIC(78, 0) DEFAULT 0,
    reserve1 NUMERIC(78, 0) DEFAULT 0,
    reserve0_usd NUMERIC(30, 8) DEFAULT 0,
    reserve1_usd NUMERIC(30, 8) DEFAULT 0,
    total_liquidity_usd NUMERIC(30, 8) DEFAULT 0,
    volume_24h NUMERIC(30, 8) DEFAULT 0,
    fee_tier INTEGER, -- For UniswapV3 (500, 3000, 10000)
    created_at_block BIGINT,
    created_at_timestamp TIMESTAMP WITH TIME ZONE,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, pool_address)
) PARTITION BY LIST (chain_id);

-- Create partitions for supported chains
CREATE TABLE IF NOT EXISTS liquidity_pools_chain_138 PARTITION OF liquidity_pools FOR VALUES IN (138);
CREATE TABLE IF NOT EXISTS liquidity_pools_chain_651940 PARTITION OF liquidity_pools FOR VALUES IN (651940);

CREATE INDEX idx_liquidity_pools_chain_pool ON liquidity_pools(chain_id, pool_address);
CREATE INDEX idx_liquidity_pools_token0 ON liquidity_pools(chain_id, token0_address);
CREATE INDEX idx_liquidity_pools_token1 ON liquidity_pools(chain_id, token1_address);
CREATE INDEX idx_liquidity_pools_dex_type ON liquidity_pools(chain_id, dex_type);
CREATE INDEX idx_liquidity_pools_tvl ON liquidity_pools(total_liquidity_usd) WHERE total_liquidity_usd > 0;

-- Pool Reserves History - Time-series snapshots of pool reserves
CREATE TABLE IF NOT EXISTS pool_reserves_history (
    id BIGSERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    pool_address VARCHAR(42) NOT NULL,
    reserve0 NUMERIC(78, 0) NOT NULL,
    reserve1 NUMERIC(78, 0) NOT NULL,
    reserve0_usd NUMERIC(30, 8),
    reserve1_usd NUMERIC(30, 8),
    total_liquidity_usd NUMERIC(30, 8),
    block_number BIGINT NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
) PARTITION BY LIST (chain_id);

-- Create partitions for supported chains
CREATE TABLE IF NOT EXISTS pool_reserves_history_chain_138 PARTITION OF pool_reserves_history FOR VALUES IN (138);
CREATE TABLE IF NOT EXISTS pool_reserves_history_chain_651940 PARTITION OF pool_reserves_history FOR VALUES IN (651940);

-- Convert to hypertable for TimescaleDB time-series optimization
SELECT create_hypertable('pool_reserves_history', 'timestamp', 
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);

CREATE INDEX idx_pool_reserves_history_pool_time ON pool_reserves_history(chain_id, pool_address, timestamp DESC);
CREATE INDEX idx_pool_reserves_history_timestamp ON pool_reserves_history(timestamp DESC);

-- Token OHLCV - Open, High, Low, Close, Volume data by interval
CREATE TABLE IF NOT EXISTS token_ohlcv (
    id BIGSERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    token_address VARCHAR(42) NOT NULL,
    pool_address VARCHAR(42), -- Optional: specific pool, NULL = aggregated across all pools
    interval_type VARCHAR(10) NOT NULL CHECK (interval_type IN ('5m', '15m', '1h', '4h', '24h')),
    open_price NUMERIC(30, 8) NOT NULL,
    high_price NUMERIC(30, 8) NOT NULL,
    low_price NUMERIC(30, 8) NOT NULL,
    close_price NUMERIC(30, 8) NOT NULL,
    volume NUMERIC(30, 8) DEFAULT 0,
    volume_usd NUMERIC(30, 8) DEFAULT 0,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, token_address, pool_address, interval_type, timestamp)
) PARTITION BY LIST (chain_id);

-- Create partitions for supported chains
CREATE TABLE IF NOT EXISTS token_ohlcv_chain_138 PARTITION OF token_ohlcv FOR VALUES IN (138);
CREATE TABLE IF NOT EXISTS token_ohlcv_chain_651940 PARTITION OF token_ohlcv FOR VALUES IN (651940);

-- Convert to hypertable for TimescaleDB time-series optimization
SELECT create_hypertable('token_ohlcv', 'timestamp',
    chunk_time_interval => INTERVAL '7 days',
    if_not_exists => TRUE
);

CREATE INDEX idx_token_ohlcv_token_time ON token_ohlcv(chain_id, token_address, interval_type, timestamp DESC);
CREATE INDEX idx_token_ohlcv_pool_time ON token_ohlcv(chain_id, pool_address, interval_type, timestamp DESC) WHERE pool_address IS NOT NULL;
CREATE INDEX idx_token_ohlcv_timestamp ON token_ohlcv(timestamp DESC);

-- External API Cache - Cached responses from external APIs
CREATE TABLE IF NOT EXISTS external_api_cache (
    id BIGSERIAL PRIMARY KEY,
    api_provider VARCHAR(50) NOT NULL CHECK (api_provider IN ('coingecko', 'coinmarketcap', 'dexscreener')),
    cache_key VARCHAR(255) NOT NULL,
    chain_id INTEGER,
    token_address VARCHAR(42),
    response_data JSONB NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(api_provider, cache_key)
);

CREATE INDEX idx_external_api_cache_provider_key ON external_api_cache(api_provider, cache_key);
CREATE INDEX idx_external_api_cache_chain_token ON external_api_cache(chain_id, token_address) WHERE chain_id IS NOT NULL AND token_address IS NOT NULL;
CREATE INDEX idx_external_api_cache_expires ON external_api_cache(expires_at);

-- Token Signals - Trending and growth metrics
CREATE TABLE IF NOT EXISTS token_signals (
    id BIGSERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    token_address VARCHAR(42) NOT NULL,
    tx_count_growth_24h NUMERIC(10, 4) DEFAULT 0, -- Percentage change
    unique_wallets_24h INTEGER DEFAULT 0,
    unique_wallets_growth_24h NUMERIC(10, 4) DEFAULT 0,
    swap_count_24h INTEGER DEFAULT 0,
    swap_count_growth_24h NUMERIC(10, 4) DEFAULT 0,
    new_lp_creations_24h INTEGER DEFAULT 0,
    attention_score NUMERIC(10, 4) DEFAULT 0, -- Composite score 0-100
    trending_rank INTEGER, -- Rank among trending tokens
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, token_address, timestamp)
) PARTITION BY LIST (chain_id);

-- Create partitions for supported chains
CREATE TABLE IF NOT EXISTS token_signals_chain_138 PARTITION OF token_signals FOR VALUES IN (138);
CREATE TABLE IF NOT EXISTS token_signals_chain_651940 PARTITION OF token_signals FOR VALUES IN (651940);

-- Convert to hypertable for TimescaleDB time-series optimization
SELECT create_hypertable('token_signals', 'timestamp',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);

CREATE INDEX idx_token_signals_token_time ON token_signals(chain_id, token_address, timestamp DESC);
CREATE INDEX idx_token_signals_attention ON token_signals(chain_id, attention_score DESC, timestamp DESC);
CREATE INDEX idx_token_signals_trending ON token_signals(chain_id, trending_rank, timestamp DESC) WHERE trending_rank IS NOT NULL;

-- Swap Events - Track individual swap events for volume calculation
CREATE TABLE IF NOT EXISTS swap_events (
    id BIGSERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    pool_address VARCHAR(42) NOT NULL,
    transaction_hash VARCHAR(66) NOT NULL,
    block_number BIGINT NOT NULL,
    log_index INTEGER NOT NULL,
    token0_address VARCHAR(42) NOT NULL,
    token1_address VARCHAR(42) NOT NULL,
    amount0_in NUMERIC(78, 0) DEFAULT 0,
    amount1_in NUMERIC(78, 0) DEFAULT 0,
    amount0_out NUMERIC(78, 0) DEFAULT 0,
    amount1_out NUMERIC(78, 0) DEFAULT 0,
    amount_usd NUMERIC(30, 8), -- Calculated USD value
    sender VARCHAR(42),
    to_address VARCHAR(42),
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, transaction_hash, log_index)
) PARTITION BY LIST (chain_id);

-- Create partitions for supported chains
CREATE TABLE IF NOT EXISTS swap_events_chain_138 PARTITION OF swap_events FOR VALUES IN (138);
CREATE TABLE IF NOT EXISTS swap_events_chain_651940 PARTITION OF swap_events FOR VALUES IN (651940);

-- Convert to hypertable for TimescaleDB time-series optimization
SELECT create_hypertable('swap_events', 'timestamp',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);

CREATE INDEX idx_swap_events_pool_time ON swap_events(chain_id, pool_address, timestamp DESC);
CREATE INDEX idx_swap_events_token0 ON swap_events(chain_id, token0_address, timestamp DESC);
CREATE INDEX idx_swap_events_token1 ON swap_events(chain_id, token1_address, timestamp DESC);
CREATE INDEX idx_swap_events_tx_hash ON swap_events(chain_id, transaction_hash);
CREATE INDEX idx_swap_events_block ON swap_events(chain_id, block_number);

-- Update triggers for last_updated
CREATE TRIGGER update_token_market_data_updated_at BEFORE UPDATE ON token_market_data
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_liquidity_pools_updated_at BEFORE UPDATE ON liquidity_pools
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Comments for documentation
COMMENT ON TABLE token_market_data IS 'Aggregated market data per token including price, volume, market cap, and liquidity';
COMMENT ON TABLE liquidity_pools IS 'DEX liquidity pool information with reserves and TVL';
COMMENT ON TABLE pool_reserves_history IS 'Time-series history of pool reserve snapshots';
COMMENT ON TABLE token_ohlcv IS 'OHLCV (Open, High, Low, Close, Volume) data for token price charts';
COMMENT ON TABLE external_api_cache IS 'Cached responses from external APIs (CoinGecko, CMC, DexScreener)';
COMMENT ON TABLE token_signals IS 'Trending signals and growth metrics for tokens';
COMMENT ON TABLE swap_events IS 'Individual swap events from DEX pools for volume calculation';