-- Migration: Add per-token USD price columns to swap_events
-- Description: Aligns lightweight swap_events schema with token-aggregation writer and
-- enables historical OHLCV generation to derive token-specific candles

ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS amount0_in NUMERIC(78, 0) DEFAULT 0;
ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS amount1_in NUMERIC(78, 0) DEFAULT 0;
ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS amount0_out NUMERIC(78, 0) DEFAULT 0;
ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS amount1_out NUMERIC(78, 0) DEFAULT 0;
ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS price_usd NUMERIC(30, 8);
ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS token0_price_usd NUMERIC(30, 8);
ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS token1_price_usd NUMERIC(30, 8);
ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS sender VARCHAR(42);
ALTER TABLE IF EXISTS swap_events ADD COLUMN IF NOT EXISTS to_address VARCHAR(42);

CREATE INDEX IF NOT EXISTS idx_swap_events_token0_price
  ON swap_events (chain_id, token0_address, timestamp DESC)
  WHERE token0_price_usd IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_swap_events_token1_price
  ON swap_events (chain_id, token1_address, timestamp DESC)
  WHERE token1_price_usd IS NOT NULL;

DROP INDEX IF EXISTS idx_swap_events_unique_log;
DROP INDEX IF EXISTS idx_swap_events_chain_tx_log;

CREATE UNIQUE INDEX IF NOT EXISTS idx_swap_events_chain_tx_log
  ON swap_events (chain_id, transaction_hash, log_index);
