foxhunt/migrations/013_symbol_configuration_tables.sql

-- Symbol Configuration Tables Migration
-- Comprehensive symbol classification and configuration management
-- Created: 2025-09-29
-- Purpose: Support symbol-specific trading parameters, volatility profiles, and market hours

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Asset Classification enumeration
CREATE TYPE asset_classification AS ENUM (
    'EQUITY',
    'FUTURE', 
    'FOREX',
    'CRYPTO',
    'COMMODITY',
    'FIXED_INCOME',
    'OPTION',
    'ETF',
    'INDEX',
    'DERIVATIVE'
);

-- Volatility regime classification
CREATE TYPE volatility_regime AS ENUM (
    'LOW',
    'NORMAL',
    'ELEVATED', 
    'HIGH'
);

-- Main symbol configuration table
CREATE TABLE symbol_config (
    -- Primary identification
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol VARCHAR(50) NOT NULL UNIQUE,
    description TEXT NOT NULL,
    classification asset_classification NOT NULL,
    
    -- Market parameters
    primary_exchange VARCHAR(50) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    tick_size DECIMAL(18,8) NOT NULL DEFAULT 0.01,
    lot_size DECIMAL(18,8) NOT NULL DEFAULT 1.0,
    min_order_size DECIMAL(18,8) NOT NULL DEFAULT 1.0,
    max_order_size DECIMAL(18,8) NOT NULL DEFAULT 1000000.0,
    
    -- Financial metrics
    sector VARCHAR(100),
    industry VARCHAR(100),
    market_cap DECIMAL(20,2),
    avg_daily_volume DECIMAL(20,2) DEFAULT 0.0,
    margin_requirement DECIMAL(5,4) NOT NULL DEFAULT 0.25,
    
    -- Risk parameters
    position_limit DECIMAL(18,8),
    risk_multiplier DECIMAL(8,4) NOT NULL DEFAULT 1.0,
    
    -- Status and metadata
    is_active BOOLEAN NOT NULL DEFAULT true,
    data_source VARCHAR(50) NOT NULL DEFAULT 'manual',
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    last_validated TIMESTAMP WITH TIME ZONE,
    
    -- Constraints
    CONSTRAINT symbol_config_tick_size_positive CHECK (tick_size > 0),
    CONSTRAINT symbol_config_lot_size_positive CHECK (lot_size > 0),
    CONSTRAINT symbol_config_min_order_positive CHECK (min_order_size > 0),
    CONSTRAINT symbol_config_max_order_valid CHECK (max_order_size >= min_order_size),
    CONSTRAINT symbol_config_margin_valid CHECK (margin_requirement >= 0 AND margin_requirement <= 1),
    CONSTRAINT symbol_config_risk_multiplier_positive CHECK (risk_multiplier > 0)
);

-- Volatility profile table
CREATE TABLE volatility_profile (
    -- Primary identification
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol_config_id UUID NOT NULL REFERENCES symbol_config(id) ON DELETE CASCADE,
    
    -- Volatility metrics
    average_volatility DECIMAL(8,6) NOT NULL DEFAULT 0.20,
    max_volatility DECIMAL(8,6) NOT NULL DEFAULT 1.00,
    min_volatility DECIMAL(8,6) NOT NULL DEFAULT 0.05,
    beta DECIMAL(8,4) NOT NULL DEFAULT 1.0,
    atr DECIMAL(18,8) NOT NULL DEFAULT 0.0,
    market_correlation DECIMAL(6,4) NOT NULL DEFAULT 0.0,
    volatility_regime volatility_regime NOT NULL DEFAULT 'NORMAL',
    
    -- Metadata
    sample_size INTEGER NOT NULL DEFAULT 0,
    last_updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT volatility_profile_avg_positive CHECK (average_volatility >= 0),
    CONSTRAINT volatility_profile_max_positive CHECK (max_volatility >= 0),
    CONSTRAINT volatility_profile_min_positive CHECK (min_volatility >= 0),
    CONSTRAINT volatility_profile_range_valid CHECK (max_volatility >= average_volatility AND average_volatility >= min_volatility),
    CONSTRAINT volatility_profile_correlation_valid CHECK (market_correlation >= -1 AND market_correlation <= 1),
    CONSTRAINT volatility_profile_sample_size_positive CHECK (sample_size >= 0),
    
    -- Unique constraint
    UNIQUE(symbol_config_id)
);

-- Trading hours table
CREATE TABLE trading_hours (
    -- Primary identification
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol_config_id UUID NOT NULL REFERENCES symbol_config(id) ON DELETE CASCADE,
    
    -- Time zone and hours
    timezone VARCHAR(50) NOT NULL DEFAULT 'America/New_York',
    market_open TIME NOT NULL DEFAULT '09:30:00',
    market_close TIME NOT NULL DEFAULT '16:00:00',
    pre_market_open TIME,
    after_hours_close TIME,
    
    -- Trading days (stored as bit flags: Mon=1, Tue=2, Wed=4, Thu=8, Fri=16, Sat=32, Sun=64)
    trading_days_mask INTEGER NOT NULL DEFAULT 31, -- Mon-Fri = 1+2+4+8+16 = 31
    
    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT trading_hours_open_before_close CHECK (market_open < market_close),
    CONSTRAINT trading_hours_days_valid CHECK (trading_days_mask > 0 AND trading_days_mask < 128),
    
    -- Unique constraint
    UNIQUE(symbol_config_id)
);

-- Market holidays table
CREATE TABLE market_holidays (
    -- Primary identification
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    trading_hours_id UUID NOT NULL REFERENCES trading_hours(id) ON DELETE CASCADE,
    
    -- Holiday information
    holiday_date DATE NOT NULL,
    holiday_name VARCHAR(100) NOT NULL,
    is_half_day BOOLEAN NOT NULL DEFAULT false,
    early_close_time TIME, -- Only applicable if is_half_day = true
    
    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT market_holidays_early_close_valid CHECK (
        (is_half_day = false AND early_close_time IS NULL) OR
        (is_half_day = true AND early_close_time IS NOT NULL)
    ),
    
    -- Unique constraint
    UNIQUE(trading_hours_id, holiday_date)
);

-- Symbol configuration tags table (for flexible categorization)
CREATE TABLE symbol_config_tags (
    -- Primary identification
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol_config_id UUID NOT NULL REFERENCES symbol_config(id) ON DELETE CASCADE,
    
    -- Tag information
    tag_key VARCHAR(50) NOT NULL,
    tag_value VARCHAR(100) NOT NULL,
    
    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    -- Unique constraint
    UNIQUE(symbol_config_id, tag_key)
);

-- Indexes for performance
CREATE INDEX idx_symbol_config_symbol ON symbol_config(symbol);
CREATE INDEX idx_symbol_config_classification ON symbol_config(classification);
CREATE INDEX idx_symbol_config_exchange ON symbol_config(primary_exchange);
CREATE INDEX idx_symbol_config_active ON symbol_config(is_active);
CREATE INDEX idx_symbol_config_updated ON symbol_config(updated_at);

CREATE INDEX idx_volatility_profile_regime ON volatility_profile(volatility_regime);
CREATE INDEX idx_volatility_profile_updated ON volatility_profile(last_updated);

CREATE INDEX idx_trading_hours_timezone ON trading_hours(timezone);

CREATE INDEX idx_market_holidays_date ON market_holidays(holiday_date);

CREATE INDEX idx_symbol_tags_key ON symbol_config_tags(tag_key);
CREATE INDEX idx_symbol_tags_value ON symbol_config_tags(tag_value);

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_symbol_config_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update updated_at
CREATE TRIGGER symbol_config_update_trigger
    BEFORE UPDATE ON symbol_config
    FOR EACH ROW
    EXECUTE FUNCTION update_symbol_config_updated_at();

CREATE TRIGGER trading_hours_update_trigger
    BEFORE UPDATE ON trading_hours
    FOR EACH ROW
    EXECUTE FUNCTION update_symbol_config_updated_at();

-- Function to automatically create default volatility profile and trading hours
CREATE OR REPLACE FUNCTION create_default_symbol_components()
RETURNS TRIGGER AS $$
BEGIN
    -- Create default volatility profile
    INSERT INTO volatility_profile (symbol_config_id)
    VALUES (NEW.id);
    
    -- Create default trading hours based on asset classification
    INSERT INTO trading_hours (
        symbol_config_id,
        timezone,
        market_open,
        market_close,
        pre_market_open,
        after_hours_close,
        trading_days_mask
    )
    VALUES (
        NEW.id,
        CASE 
            WHEN NEW.classification = 'CRYPTO' THEN 'UTC'
            WHEN NEW.classification = 'FOREX' THEN 'America/New_York'
            ELSE 'America/New_York'
        END,
        CASE
            WHEN NEW.classification = 'CRYPTO' THEN '00:00:00'::TIME
            WHEN NEW.classification = 'FOREX' THEN '17:00:00'::TIME  -- Sunday 5 PM EST (Forex market opens)
            ELSE '09:30:00'::TIME
        END,
        CASE
            WHEN NEW.classification = 'CRYPTO' THEN '23:59:59'::TIME
            WHEN NEW.classification = 'FOREX' THEN '17:00:01'::TIME  -- Friday 5:00:01 PM EST (Forex market closes, 1 second after open for weekly cycle)
            ELSE '16:00:00'::TIME
        END,
        CASE 
            WHEN NEW.classification IN ('EQUITY', 'ETF') THEN '04:00:00'::TIME
            ELSE NULL
        END,
        CASE 
            WHEN NEW.classification IN ('EQUITY', 'ETF') THEN '20:00:00'::TIME
            ELSE NULL
        END,
        CASE 
            WHEN NEW.classification = 'CRYPTO' THEN 127 -- All days
            WHEN NEW.classification = 'FOREX' THEN 95  -- Sun-Fri (64+1+2+4+8+16)
            ELSE 31 -- Mon-Fri (1+2+4+8+16)
        END
    );
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to create default components
CREATE TRIGGER symbol_config_create_defaults_trigger
    AFTER INSERT ON symbol_config
    FOR EACH ROW
    EXECUTE FUNCTION create_default_symbol_components();

-- Views for easy access to complete symbol configuration

-- Complete symbol configuration view
CREATE VIEW v_symbol_config_complete AS
SELECT 
    sc.*,
    vp.average_volatility,
    vp.max_volatility,
    vp.min_volatility,
    vp.beta,
    vp.atr,
    vp.market_correlation,
    vp.volatility_regime,
    vp.sample_size,
    vp.last_updated as volatility_last_updated,
    th.timezone,
    th.market_open,
    th.market_close,
    th.pre_market_open,
    th.after_hours_close,
    th.trading_days_mask
FROM symbol_config sc
LEFT JOIN volatility_profile vp ON sc.id = vp.symbol_config_id
LEFT JOIN trading_hours th ON sc.id = th.symbol_config_id;

-- Active symbols view
CREATE VIEW v_active_symbols AS
SELECT * FROM v_symbol_config_complete
WHERE is_active = true;

-- Symbol configuration by classification view
CREATE VIEW v_symbols_by_classification AS
SELECT 
    classification,
    COUNT(*) as symbol_count,
    COUNT(CASE WHEN is_active THEN 1 END) as active_count,
    AVG(avg_daily_volume) as avg_volume,
    AVG(margin_requirement) as avg_margin_requirement
FROM symbol_config
GROUP BY classification;

-- Insert some example symbol configurations
INSERT INTO symbol_config (symbol, description, classification, primary_exchange, currency, sector, industry) VALUES
('AAPL', 'Apple Inc.', 'EQUITY', 'NASDAQ', 'USD', 'Technology', 'Consumer Electronics'),
('MSFT', 'Microsoft Corporation', 'EQUITY', 'NASDAQ', 'USD', 'Technology', 'Software'),
('SPY', 'SPDR S&P 500 ETF Trust', 'ETF', 'NYSE Arca', 'USD', NULL, NULL),
('EURUSD', 'Euro/US Dollar', 'FOREX', 'OTC', 'USD', NULL, NULL),
('BTCUSD', 'Bitcoin/US Dollar', 'CRYPTO', 'Coinbase', 'USD', NULL, NULL),
('ESH5', 'E-mini S&P 500 Future March 2025', 'FUTURE', 'CME', 'USD', NULL, NULL);

-- Update volatility profiles with example data
UPDATE volatility_profile SET
    average_volatility = 0.25,
    max_volatility = 0.80,
    min_volatility = 0.10,
    beta = 1.2,
    volatility_regime = 'NORMAL'
WHERE symbol_config_id = (SELECT id FROM symbol_config WHERE symbol = 'AAPL');

UPDATE volatility_profile SET
    average_volatility = 0.22,
    max_volatility = 0.65,
    min_volatility = 0.12,
    beta = 0.9,
    volatility_regime = 'NORMAL'
WHERE symbol_config_id = (SELECT id FROM symbol_config WHERE symbol = 'MSFT');

UPDATE volatility_profile SET
    average_volatility = 0.15,
    max_volatility = 0.45,
    min_volatility = 0.08,
    beta = 1.0,
    volatility_regime = 'NORMAL'
WHERE symbol_config_id = (SELECT id FROM symbol_config WHERE symbol = 'SPY');

UPDATE volatility_profile SET
    average_volatility = 0.45,
    max_volatility = 1.20,
    min_volatility = 0.20,
    beta = 0.1,
    volatility_regime = 'ELEVATED'
WHERE symbol_config_id = (SELECT id FROM symbol_config WHERE symbol = 'BTCUSD');

-- Add some configuration tags
INSERT INTO symbol_config_tags (symbol_config_id, tag_key, tag_value) VALUES
((SELECT id FROM symbol_config WHERE symbol = 'AAPL'), 'sector', 'technology'),
((SELECT id FROM symbol_config WHERE symbol = 'AAPL'), 'market_cap', 'large'),
((SELECT id FROM symbol_config WHERE symbol = 'MSFT'), 'sector', 'technology'),
((SELECT id FROM symbol_config WHERE symbol = 'MSFT'), 'market_cap', 'large'),
((SELECT id FROM symbol_config WHERE symbol = 'SPY'), 'type', 'index_etf'),
((SELECT id FROM symbol_config WHERE symbol = 'BTCUSD'), 'type', 'digital_asset'),
((SELECT id FROM symbol_config WHERE symbol = 'ESH5'), 'type', 'equity_index_future');

-- Add some market holidays for US equity markets
INSERT INTO market_holidays (trading_hours_id, holiday_date, holiday_name, is_half_day, early_close_time)
SELECT 
    th.id,
    '2025-01-01'::DATE,
    'New Year''s Day',
    false,
    NULL
FROM trading_hours th
JOIN symbol_config sc ON th.symbol_config_id = sc.id
WHERE sc.classification IN ('EQUITY', 'ETF');

INSERT INTO market_holidays (trading_hours_id, holiday_date, holiday_name, is_half_day, early_close_time)
SELECT 
    th.id,
    '2025-07-04'::DATE,
    'Independence Day',
    false,
    NULL
FROM trading_hours th
JOIN symbol_config sc ON th.symbol_config_id = sc.id
WHERE sc.classification IN ('EQUITY', 'ETF');

INSERT INTO market_holidays (trading_hours_id, holiday_date, holiday_name, is_half_day, early_close_time)
SELECT 
    th.id,
    '2025-11-27'::DATE,
    'Thanksgiving Day (Half Day)',
    true,
    '13:00:00'::TIME
FROM trading_hours th
JOIN symbol_config sc ON th.symbol_config_id = sc.id
WHERE sc.classification IN ('EQUITY', 'ETF');

-- Create configuration change notification function
CREATE OR REPLACE FUNCTION notify_symbol_config_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Send notification for configuration changes
    PERFORM pg_notify('symbol_config_changed', 
        json_build_object(
            'action', TG_OP,
            'symbol', COALESCE(NEW.symbol, OLD.symbol),
            'id', COALESCE(NEW.id, OLD.id),
            'timestamp', NOW()
        )::text
    );
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Triggers for configuration change notifications
CREATE TRIGGER symbol_config_notify_trigger
    AFTER INSERT OR UPDATE OR DELETE ON symbol_config
    FOR EACH ROW
    EXECUTE FUNCTION notify_symbol_config_change();

CREATE TRIGGER volatility_profile_notify_trigger
    AFTER INSERT OR UPDATE OR DELETE ON volatility_profile
    FOR EACH ROW
    EXECUTE FUNCTION notify_symbol_config_change();

-- Grant permissions (adjust as needed for your environment)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO trading_user;
-- GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO trading_user;
-- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO trading_user;

-- Performance optimization: Create partial indexes for common queries
CREATE INDEX idx_symbol_config_active_equity ON symbol_config(symbol) 
WHERE is_active = true AND classification = 'EQUITY';

CREATE INDEX idx_symbol_config_active_crypto ON symbol_config(symbol) 
WHERE is_active = true AND classification = 'CRYPTO';

CREATE INDEX idx_volatility_profile_high_vol ON volatility_profile(symbol_config_id, average_volatility) 
WHERE volatility_regime IN ('ELEVATED', 'HIGH');

-- Add comments for documentation
COMMENT ON TABLE symbol_config IS 'Core symbol configuration with trading parameters and metadata';
COMMENT ON TABLE volatility_profile IS 'Volatility metrics and risk characteristics for each symbol';
COMMENT ON TABLE trading_hours IS 'Market operating hours and trading session definitions';
COMMENT ON TABLE market_holidays IS 'Market holidays and half-day sessions';
COMMENT ON TABLE symbol_config_tags IS 'Flexible key-value tags for symbol categorization';

COMMENT ON COLUMN symbol_config.symbol IS 'Unique symbol identifier (e.g., AAPL, EURUSD, BTCUSD)';
COMMENT ON COLUMN symbol_config.classification IS 'Asset class for regulatory and risk management purposes';
COMMENT ON COLUMN symbol_config.tick_size IS 'Minimum price increment for the instrument';
COMMENT ON COLUMN symbol_config.margin_requirement IS 'Initial margin requirement as decimal (0.25 = 25%)';
COMMENT ON COLUMN volatility_profile.volatility_regime IS 'Current volatility classification for risk management';
COMMENT ON COLUMN trading_hours.trading_days_mask IS 'Bit mask for trading days (Mon=1, Tue=2, Wed=4, Thu=8, Fri=16, Sat=32, Sun=64)';