foxhunt/migrations/011_create_market_data_tables.sql

-- Migration 011: Create Market Data Tables
-- This migration creates all the missing market data tables required for SQLx compilation
-- Tables: prices, order_book_levels, technical_indicators, market_ticks, candles

-- Enable required extensions if not already enabled
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "btree_gin";

-- Prices table - stores bid/ask/last prices and OHLCV data
CREATE TABLE IF NOT EXISTS prices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol VARCHAR(32) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    bid BIGINT, -- Bid price in fixed-point cents
    ask BIGINT, -- Ask price in fixed-point cents
    last BIGINT, -- Last trade price in fixed-point cents
    volume BIGINT, -- Volume traded
    open BIGINT, -- Opening price in fixed-point cents
    high BIGINT, -- High price in fixed-point cents
    low BIGINT, -- Low price in fixed-point cents
    close BIGINT, -- Closing price in fixed-point cents
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(symbol, timestamp)
);

-- Order book levels table - stores order book depth data
CREATE TABLE IF NOT EXISTS order_book_levels (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol VARCHAR(32) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    side VARCHAR(10) NOT NULL CHECK (side IN ('bid', 'ask')),
    price BIGINT NOT NULL, -- Price level in fixed-point cents
    quantity BIGINT NOT NULL, -- Quantity at this level
    level INTEGER NOT NULL, -- Level in the book (0 = best bid/ask)
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(symbol, timestamp, side, level)
);

-- Technical indicators table - stores computed technical indicators
CREATE TABLE IF NOT EXISTS technical_indicators (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol VARCHAR(32) NOT NULL,
    indicator_name VARCHAR(64) NOT NULL,
    indicator_type VARCHAR(32) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    value DECIMAL(20, 8) NOT NULL,
    parameters JSONB, -- Indicator-specific parameters
    metadata JSONB, -- Additional metadata
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(symbol, indicator_name, timestamp)
);

-- Market ticks table - stores raw market tick data
CREATE TABLE IF NOT EXISTS market_ticks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol VARCHAR(32) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    price BIGINT NOT NULL, -- Tick price in fixed-point cents
    quantity BIGINT NOT NULL, -- Tick quantity
    side VARCHAR(10) CHECK (side IN ('buy', 'sell')),
    tick_type VARCHAR(20) NOT NULL, -- trade, bid, ask, etc.
    exchange VARCHAR(32), -- Exchange identifier
    sequence_number BIGINT, -- Exchange sequence number
    conditions JSONB, -- Trade conditions/flags
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Candles table - stores OHLCV candlestick data
CREATE TABLE IF NOT EXISTS candles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol VARCHAR(32) NOT NULL,
    period VARCHAR(10) NOT NULL, -- 1m, 5m, 15m, 1h, 1d, etc.
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL, -- Start of the period
    open BIGINT NOT NULL, -- Opening price in fixed-point cents
    high BIGINT NOT NULL, -- High price in fixed-point cents
    low BIGINT NOT NULL, -- Low price in fixed-point cents
    close BIGINT NOT NULL, -- Closing price in fixed-point cents
    volume BIGINT NOT NULL DEFAULT 0, -- Total volume
    trade_count INTEGER DEFAULT 0, -- Number of trades in period
    vwap BIGINT, -- Volume-weighted average price
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(symbol, period, timestamp)
);

-- Create indexes for optimal query performance

-- Prices table indexes
CREATE INDEX IF NOT EXISTS idx_prices_symbol_timestamp ON prices(symbol, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_prices_timestamp ON prices(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_prices_symbol ON prices(symbol);

-- Order book levels indexes
CREATE INDEX IF NOT EXISTS idx_order_book_levels_symbol_timestamp ON order_book_levels(symbol, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_order_book_levels_symbol_side_timestamp ON order_book_levels(symbol, side, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_order_book_levels_timestamp ON order_book_levels(timestamp DESC);

-- Technical indicators indexes
CREATE INDEX IF NOT EXISTS idx_technical_indicators_symbol_name_timestamp ON technical_indicators(symbol, indicator_name, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_technical_indicators_timestamp ON technical_indicators(timestamp DESC);

-- Market ticks indexes
CREATE INDEX IF NOT EXISTS idx_market_ticks_symbol_timestamp ON market_ticks(symbol, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_market_ticks_timestamp ON market_ticks(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_market_ticks_sequence ON market_ticks(exchange, sequence_number);

-- Candles indexes
CREATE INDEX IF NOT EXISTS idx_candles_symbol_period_timestamp ON candles(symbol, period, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_candles_timestamp ON candles(timestamp DESC);

-- Partitioning for large tables (commented out for initial setup, can be enabled later)
-- This would be useful for production deployments with high data volumes

/*
-- Example partitioning setup for prices table (by month)
-- CREATE TABLE prices_partitioned (LIKE prices INCLUDING ALL) PARTITION BY RANGE (timestamp);
-- CREATE TABLE prices_y2025m01 PARTITION OF prices_partitioned FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- CREATE TABLE prices_y2025m02 PARTITION OF prices_partitioned FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- ... continue for each month
*/

-- Add triggers for updating timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply update trigger to candles table
CREATE TRIGGER update_candles_updated_at BEFORE UPDATE ON candles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Add comments for documentation
COMMENT ON TABLE prices IS 'Market price data with bid/ask/last prices and OHLCV data';
COMMENT ON TABLE order_book_levels IS 'Order book depth data with price levels and quantities';
COMMENT ON TABLE technical_indicators IS 'Computed technical indicators (RSI, MACD, etc.)';
COMMENT ON TABLE market_ticks IS 'Raw market tick data from exchanges';
COMMENT ON TABLE candles IS 'OHLCV candlestick data for various time periods';

-- Grant permissions (assuming the trading service user exists from previous migrations)
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'trading_service') THEN
        GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO trading_service;
        GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO trading_service;
    END IF;
END
$$;