foxhunt/migrations/012_create_event_and_config_tables.sql

-- Migration 012: Create Event Processing and Configuration Tables
-- This migration creates the missing tables for event processing and configuration
-- Tables: market_events, trading_events, event_processing_stats, configuration, secrets

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

-- Market events table - stores market-related events
CREATE TABLE IF NOT EXISTS market_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_type VARCHAR(64) NOT NULL,
    symbol VARCHAR(32),
    "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL,
    event_data JSONB NOT NULL,
    source VARCHAR(64), -- Event source (exchange, internal, etc.)
    severity VARCHAR(20) DEFAULT 'info' CHECK (severity IN ('debug', 'info', 'warning', 'error', 'critical')),
    processed BOOLEAN DEFAULT FALSE,
    processed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Trading events table - stores trading-related events
CREATE TABLE IF NOT EXISTS trading_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_type VARCHAR(64) NOT NULL,
    symbol VARCHAR(32),
    "order_id" UUID,
    account_id VARCHAR(64),
    "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL,
    event_data JSONB NOT NULL,
    "correlation_id" UUID, -- For tracing related events
    severity VARCHAR(20) DEFAULT 'info' CHECK (severity IN ('debug', 'info', 'warning', 'error', 'critical')),
    processed BOOLEAN DEFAULT FALSE,
    processed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Event processing stats table - stores processing statistics
CREATE TABLE IF NOT EXISTS event_processing_stats (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    processor_name VARCHAR(128) NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL,
    events_processed INTEGER NOT NULL DEFAULT 0,
    events_failed INTEGER NOT NULL DEFAULT 0,
    average_processing_time_ms DECIMAL(10, 3),
    max_processing_time_ms DECIMAL(10, 3),
    min_processing_time_ms DECIMAL(10, 3),
    total_processing_time_ms BIGINT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(processor_name, event_type, "timestamp")
);

-- Configuration table - stores application configuration (if not already exists from config migration)
CREATE TABLE IF NOT EXISTS configuration (
    id SERIAL PRIMARY KEY,
    key VARCHAR(256) NOT NULL UNIQUE,
    value TEXT NOT NULL,
    value_type VARCHAR(32) NOT NULL DEFAULT 'string' CHECK (value_type IN ('string', 'number', 'boolean', 'json')),
    description TEXT,
    category VARCHAR(128),
    environment VARCHAR(32) DEFAULT 'development',
    is_sensitive BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_by VARCHAR(128),
    updated_by VARCHAR(128)
);

-- Secrets table - stores encrypted sensitive configuration
CREATE TABLE IF NOT EXISTS secrets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key VARCHAR(256) NOT NULL,
    encrypted_value BYTEA NOT NULL,
    key_version INTEGER NOT NULL DEFAULT 1,
    environment VARCHAR(32) DEFAULT 'development',
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    created_by VARCHAR(128),
    updated_by VARCHAR(128),
    UNIQUE(key, environment)
);

-- Risk-related tables that were referenced
CREATE TABLE IF NOT EXISTS risk_alerts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    alert_type VARCHAR(64) NOT NULL,
    symbol VARCHAR(32),
    account_id VARCHAR(64),
    severity VARCHAR(20) NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    message TEXT NOT NULL,
    threshold_value DECIMAL(20, 8),
    current_value DECIMAL(20, 8),
    metadata JSONB,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by VARCHAR(128),
    acknowledged_at TIMESTAMP WITH TIME ZONE,
    resolved BOOLEAN DEFAULT FALSE,
    resolved_by VARCHAR(128),
    resolved_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS position_risks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    symbol VARCHAR(32) NOT NULL,
    account_id VARCHAR(64),
    position_size BIGINT NOT NULL,
    market_value DECIMAL(20, 8) NOT NULL,
    unrealized_pnl DECIMAL(20, 8),
    var_1d DECIMAL(20, 8), -- 1-day Value at Risk
    var_10d DECIMAL(20, 8), -- 10-day Value at Risk
    exposure_pct DECIMAL(10, 6), -- Exposure as percentage of total portfolio
    concentration_risk DECIMAL(10, 6),
    liquidity_risk DECIMAL(10, 6),
    calculated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(symbol, account_id, calculated_at)
);

CREATE TABLE IF NOT EXISTS var_calculations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    portfolio_id VARCHAR(128) NOT NULL,
    calculation_date DATE NOT NULL,
    confidence_level DECIMAL(5, 4) NOT NULL, -- e.g., 0.99 for 99%
    time_horizon_days INTEGER NOT NULL,
    var_amount DECIMAL(20, 8) NOT NULL,
    expected_shortfall DECIMAL(20, 8), -- Conditional VaR
    calculation_method VARCHAR(64) NOT NULL, -- historical, parametric, monte_carlo
    model_parameters JSONB,
    calculation_time_ms INTEGER,
    calculated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(portfolio_id, calculation_date, confidence_level, time_horizon_days)
);

-- Create indexes for optimal query performance

-- Market events indexes (only create if table was created by this migration, not migration 001)
DO $$
BEGIN
    -- Check if market_events has the "timestamp" column (our schema) vs event_timestamp (migration 001 schema)
    IF EXISTS (SELECT 1 FROM information_schema.columns
               WHERE table_name = 'market_events' AND column_name = 'timestamp') THEN
        CREATE INDEX IF NOT EXISTS idx_market_events_type_timestamp ON market_events(event_type, "timestamp" DESC);
        CREATE INDEX IF NOT EXISTS idx_market_events_symbol_timestamp ON market_events(symbol, "timestamp" DESC);
        CREATE INDEX IF NOT EXISTS idx_market_events_timestamp ON market_events("timestamp" DESC);
        CREATE INDEX IF NOT EXISTS idx_market_events_processed ON market_events(processed, "timestamp" DESC);
    END IF;
END $$;

-- Trading events indexes (only create if table was created by this migration, not migration 001)
DO $$
BEGIN
    -- Check if trading_events has the "timestamp" column (our schema) vs event_timestamp (migration 001 schema)
    IF EXISTS (SELECT 1 FROM information_schema.columns
               WHERE table_name = 'trading_events' AND column_name = 'timestamp') THEN
        CREATE INDEX IF NOT EXISTS idx_trading_events_type_timestamp ON trading_events(event_type, "timestamp" DESC);
        CREATE INDEX IF NOT EXISTS idx_trading_events_symbol_timestamp ON trading_events(symbol, "timestamp" DESC);
        CREATE INDEX IF NOT EXISTS idx_trading_events_order_id ON trading_events("order_id");
        CREATE INDEX IF NOT EXISTS idx_trading_events_correlation_id ON trading_events("correlation_id");
        CREATE INDEX IF NOT EXISTS idx_trading_events_timestamp ON trading_events("timestamp" DESC);
    END IF;
END $$;

-- Event processing stats indexes (always create as this table is new)
CREATE INDEX IF NOT EXISTS idx_event_processing_stats_processor_type ON event_processing_stats(processor_name, event_type, "timestamp" DESC);

-- Configuration indexes
CREATE INDEX IF NOT EXISTS idx_configuration_category ON configuration(category);
CREATE INDEX IF NOT EXISTS idx_configuration_environment ON configuration(environment);

-- Secrets indexes
CREATE INDEX IF NOT EXISTS idx_secrets_environment ON secrets(environment);
CREATE INDEX IF NOT EXISTS idx_secrets_expires_at ON secrets(expires_at);

-- Risk alerts indexes
CREATE INDEX IF NOT EXISTS idx_risk_alerts_type_severity ON risk_alerts(alert_type, severity, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_risk_alerts_symbol ON risk_alerts(symbol, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_risk_alerts_unresolved ON risk_alerts(resolved, created_at DESC) WHERE NOT resolved;

-- Position risks indexes
CREATE INDEX IF NOT EXISTS idx_position_risks_symbol_date ON position_risks(symbol, calculated_at DESC);
CREATE INDEX IF NOT EXISTS idx_position_risks_account_date ON position_risks(account_id, calculated_at DESC);

-- VaR calculations indexes
CREATE INDEX IF NOT EXISTS idx_var_calculations_portfolio_date ON var_calculations(portfolio_id, calculation_date DESC);

-- 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 triggers
CREATE TRIGGER update_configuration_updated_at BEFORE UPDATE ON configuration
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_secrets_updated_at BEFORE UPDATE ON secrets
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Add table comments
COMMENT ON TABLE market_events IS 'Market-related events from exchanges and internal systems';
COMMENT ON TABLE trading_events IS 'Trading-related events including orders, fills, and position changes';
COMMENT ON TABLE event_processing_stats IS 'Statistics and metrics for event processing performance';
COMMENT ON TABLE configuration IS 'Application configuration key-value pairs';
COMMENT ON TABLE secrets IS 'Encrypted sensitive configuration data';
COMMENT ON TABLE risk_alerts IS 'Risk management alerts and notifications';
COMMENT ON TABLE position_risks IS 'Position-level risk calculations and metrics';
COMMENT ON TABLE var_calculations IS 'Value at Risk calculations for portfolios';

-- 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
$$;