foxhunt/migrations/032_create_trading_universes_table.sql

-- Create trading_universes table for Trading Agent Service
-- This table stores universe selection results with criteria, instruments, and metrics

CREATE TABLE IF NOT EXISTS trading_universes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    universe_id TEXT NOT NULL UNIQUE,
    criteria JSONB NOT NULL,
    instruments JSONB NOT NULL,  -- Array of Instrument objects
    metrics JSONB NOT NULL,      -- UniverseMetrics
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_trading_universes_created_at ON trading_universes(created_at DESC);
CREATE INDEX idx_trading_universes_universe_id ON trading_universes(universe_id);

-- Asset selections table
CREATE TABLE IF NOT EXISTS asset_selections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    universe_id TEXT NOT NULL,
    criteria JSONB NOT NULL,
    asset_scores JSONB NOT NULL,  -- Array of AssetScore objects
    metrics JSONB NOT NULL,
    selected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY (universe_id) REFERENCES trading_universes(universe_id) ON DELETE CASCADE
);

CREATE INDEX idx_asset_selections_universe ON asset_selections(universe_id);
CREATE INDEX idx_asset_selections_selected_at ON asset_selections(selected_at DESC);

COMMENT ON TABLE trading_universes IS 'Stores trading universe selections with instruments and metrics';
COMMENT ON TABLE asset_selections IS 'Stores asset selection results within universes';