foxhunt/migrations/002_risk_events.sql

-- ================================================================================================
-- Migration 002: Risk Events Schema
-- Comprehensive risk management event storage with real-time monitoring
-- Production-ready with compliance, stress testing, and alert capabilities
-- ================================================================================================

-- ================================================================================================
-- RISK EVENT TYPES AND ENUMS
-- Comprehensive classification for all risk-related events
-- ================================================================================================

CREATE TYPE risk_event_type AS ENUM (
    'var_breach',
    'exposure_limit_breach',
    'position_limit_breach',
    'concentration_risk',
    'leverage_excess',
    'margin_call',
    'drawdown_limit',
    'volatility_spike',
    'correlation_breakdown',
    'liquidity_shortage',
    'stress_test_failure',
    'compliance_violation',
    'model_validation_error',
    'circuit_breaker_triggered',
    'emergency_shutdown',
    'risk_limit_update',
    'model_recalibration',
    'backtest_failure'
);

CREATE TYPE risk_severity AS ENUM (
    'info',      -- Information only
    'low',       -- Minor risk, monitoring required
    'medium',    -- Elevated risk, caution advised
    'high',      -- Significant risk, action may be required
    'critical',  -- Immediate action required
    'emergency'  -- System shutdown level risk
);

CREATE TYPE risk_action_type AS ENUM (
    'alert_only',
    'reduce_position',
    'close_position',
    'halt_trading',
    'reduce_leverage',
    'increase_margin',
    'manual_intervention',
    'system_shutdown',
    'compliance_review'
);

CREATE TYPE risk_metric_type AS ENUM (
    'var_1d',
    'var_10d',
    'cvar_1d',
    'cvar_10d',
    'exposure_gross',
    'exposure_net',
    'leverage_ratio',
    'concentration_single',
    'concentration_sector',
    'beta_portfolio',
    'sharpe_ratio',
    'max_drawdown',
    'volatility_realized',
    'volatility_implied',
    'correlation_matrix',
    'margin_excess',
    'margin_requirement',
    'liquidity_score'
);

-- ================================================================================================
-- RISK EVENTS TABLE
-- Immutable event store for all risk-related activities
-- ================================================================================================
CREATE TABLE risk_events (
    -- Primary identifiers
    id UUID DEFAULT uuid_generate_v4(),
    event_id BIGSERIAL NOT NULL,
    correlation_id UUID NOT NULL,

    -- Timing with nanosecond precision
    event_timestamp ns_timestamp NOT NULL,
    detected_timestamp ns_timestamp NOT NULL,
    acknowledged_timestamp ns_timestamp,
    resolved_timestamp ns_timestamp,

    -- Risk event classification
    event_type risk_event_type NOT NULL,
    severity risk_severity NOT NULL,
    risk_metric risk_metric_type,

    -- Risk context
    symbol VARCHAR(32),
    account_id VARCHAR(64),
    strategy_id VARCHAR(100),
    portfolio_id VARCHAR(100),

    -- Risk values and thresholds
    threshold_value DECIMAL(20, 8),
    actual_value DECIMAL(20, 8),
    breach_percentage DECIMAL(8, 4), -- How much threshold was exceeded by
    risk_score DECIMAL(10, 6), -- Normalized risk score 0-1

    -- Event details
    description TEXT NOT NULL,
    risk_model VARCHAR(100), -- Which risk model detected this
    model_version VARCHAR(50),

    -- Actions and responses
    recommended_action risk_action_type,
    action_taken risk_action_type,
    action_details JSONB,
    automated_response BOOLEAN DEFAULT FALSE,

    -- System context
    source_system VARCHAR(100) NOT NULL,
    node_id VARCHAR(50) NOT NULL,
    process_id INTEGER NOT NULL,

    -- Audit and compliance
    acknowledged_by VARCHAR(64),
    resolved_by VARCHAR(64),
    escalated_to VARCHAR(64),
    compliance_notification_sent BOOLEAN DEFAULT FALSE,

    -- Additional data
    event_data JSONB NOT NULL, -- Complete risk event payload
    metadata JSONB,

    -- Partition key
    event_date DATE NOT NULL,

    -- Constraints
    PRIMARY KEY (id, event_date),
    CONSTRAINT chk_risk_timestamps CHECK (
        detected_timestamp >= event_timestamp AND
        (acknowledged_timestamp IS NULL OR acknowledged_timestamp >= detected_timestamp) AND
        (resolved_timestamp IS NULL OR resolved_timestamp >= COALESCE(acknowledged_timestamp, detected_timestamp))
    ),
    CONSTRAINT chk_breach_percentage CHECK (
        breach_percentage IS NULL OR breach_percentage >= 0
    )
) PARTITION BY RANGE (event_date);

-- ================================================================================================
-- RISK METRICS TABLE
-- Current and historical risk metric values
-- ================================================================================================
CREATE TABLE risk_metrics (
    -- Primary identifiers
    id UUID DEFAULT uuid_generate_v4(),
    metric_name risk_metric_type NOT NULL,

    -- Scope identifiers
    symbol VARCHAR(32), -- NULL for portfolio-level metrics
    account_id VARCHAR(64),
    strategy_id VARCHAR(100),
    portfolio_id VARCHAR(100),

    -- Metric values
    value DECIMAL(20, 8) NOT NULL,
    confidence_interval_lower DECIMAL(20, 8),
    confidence_interval_upper DECIMAL(20, 8),
    confidence_level DECIMAL(5, 4) DEFAULT 0.95, -- 95% confidence by default

    -- Thresholds and limits
    warning_threshold DECIMAL(20, 8),
    breach_threshold DECIMAL(20, 8),
    emergency_threshold DECIMAL(20, 8),

    -- Calculation context
    calculation_timestamp ns_timestamp NOT NULL,
    data_timestamp ns_timestamp NOT NULL, -- Timestamp of underlying data
    model_name VARCHAR(100) NOT NULL,
    model_version VARCHAR(50) NOT NULL,
    calculation_method VARCHAR(200),

    -- Time horizon and parameters
    time_horizon_days INTEGER,
    lookback_days INTEGER,
    confidence_level_pct DECIMAL(5, 2),

    -- Status and validation
    is_valid BOOLEAN DEFAULT TRUE,
    validation_errors TEXT[],
    last_updated ns_timestamp NOT NULL,

    -- Additional context
    market_conditions JSONB, -- Market state when calculated
    calculation_details JSONB, -- Model parameters and inputs

    -- Partition key
    metric_date DATE NOT NULL,

    -- Constraints
    PRIMARY KEY (id, metric_date),
    CONSTRAINT chk_confidence_level CHECK (confidence_level > 0 AND confidence_level <= 1),
    CONSTRAINT chk_time_horizons CHECK (
        time_horizon_days IS NULL OR time_horizon_days > 0
    ),
    CONSTRAINT chk_calculation_timestamps CHECK (
        calculation_timestamp >= data_timestamp
    )
) PARTITION BY RANGE (metric_date);

-- ================================================================================================
-- RISK LIMITS TABLE
-- Configurable risk limits and thresholds
-- ================================================================================================
CREATE TABLE risk_limits (
    -- Primary identifiers
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    limit_name VARCHAR(200) NOT NULL,
    limit_type risk_metric_type NOT NULL,

    -- Scope (hierarchy: global -> account -> strategy -> symbol)
    scope_level VARCHAR(20) NOT NULL CHECK (scope_level IN ('global', 'account', 'strategy', 'symbol')),
    account_id VARCHAR(64),
    strategy_id VARCHAR(100),
    symbol VARCHAR(32),

    -- Limit values
    warning_threshold DECIMAL(20, 8),
    breach_threshold DECIMAL(20, 8) NOT NULL,
    emergency_threshold DECIMAL(20, 8),

    -- Time-based limits
    intraday_limit DECIMAL(20, 8),
    daily_limit DECIMAL(20, 8),
    weekly_limit DECIMAL(20, 8),
    monthly_limit DECIMAL(20, 8),

    -- Limit behavior
    is_active BOOLEAN DEFAULT TRUE,
    is_hard_limit BOOLEAN DEFAULT FALSE, -- If true, system enforces automatically
    breach_action risk_action_type DEFAULT 'alert_only',

    -- Timing and validity
    effective_from TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    effective_to TIMESTAMP WITH TIME ZONE,
    time_zone VARCHAR(50) DEFAULT 'UTC',

    -- Approval and audit
    approved_by VARCHAR(64) NOT NULL,
    approval_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_by VARCHAR(64) NOT NULL,
    last_modified_by VARCHAR(64),

    -- Change tracking
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    version INTEGER NOT NULL DEFAULT 1,

    -- Additional configuration
    limit_details JSONB, -- Additional limit parameters
    override_permissions TEXT[], -- Who can override this limit

    -- Constraints (unique constraint moved to expression index below)
    CONSTRAINT chk_threshold_order CHECK (
        warning_threshold IS NULL OR breach_threshold IS NULL OR warning_threshold <= breach_threshold
    ),
    CONSTRAINT chk_scope_consistency CHECK (
        (scope_level = 'global' AND account_id IS NULL AND strategy_id IS NULL AND symbol IS NULL) OR
        (scope_level = 'account' AND account_id IS NOT NULL AND strategy_id IS NULL AND symbol IS NULL) OR
        (scope_level = 'strategy' AND account_id IS NOT NULL AND strategy_id IS NOT NULL AND symbol IS NULL) OR
        (scope_level = 'symbol' AND symbol IS NOT NULL)
    )
);

-- Expression index for risk_limits uniqueness
CREATE UNIQUE INDEX uk_risk_limits_unique ON risk_limits (
    limit_type,
    scope_level,
    COALESCE(account_id, ''),
    COALESCE(strategy_id, ''),
    COALESCE(symbol, '')
);

-- ================================================================================================
-- STRESS TEST SCENARIOS TABLE
-- Predefined stress test scenarios and results
-- ================================================================================================
CREATE TABLE stress_test_scenarios (
    -- Primary identifiers
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    scenario_name VARCHAR(200) NOT NULL UNIQUE,
    scenario_type VARCHAR(100) NOT NULL, -- 'historical', 'hypothetical', 'monte_carlo'

    -- Scenario definition
    description TEXT NOT NULL,
    stress_parameters JSONB NOT NULL, -- Market movements, shocks, etc.
    test_duration_days INTEGER DEFAULT 1,

    -- Execution details
    is_active BOOLEAN DEFAULT TRUE,
    frequency_hours INTEGER DEFAULT 24, -- How often to run this scenario
    last_executed TIMESTAMP WITH TIME ZONE,
    next_scheduled TIMESTAMP WITH TIME ZONE,

    -- Validation and approval
    created_by VARCHAR(64) NOT NULL,
    approved_by VARCHAR(64),
    approval_date TIMESTAMP WITH TIME ZONE,

    -- Change tracking
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    version INTEGER NOT NULL DEFAULT 1
);

-- ================================================================================================
-- STRESS TEST RESULTS TABLE
-- Results from stress test executions
-- ================================================================================================
CREATE TABLE stress_test_results (
    -- Primary identifiers
    id UUID DEFAULT uuid_generate_v4(),
    scenario_id UUID NOT NULL REFERENCES stress_test_scenarios(id),
    execution_id UUID NOT NULL, -- Groups results from same execution

    -- Execution context
    execution_timestamp ns_timestamp NOT NULL,
    portfolio_snapshot_id UUID, -- Reference to portfolio state at test time
    market_data_timestamp ns_timestamp,

    -- Scope of test
    account_id VARCHAR(64),
    strategy_id VARCHAR(100),
    symbol VARCHAR(32),

    -- Results
    base_value DECIMAL(20, 8) NOT NULL, -- Portfolio value before stress
    stressed_value DECIMAL(20, 8) NOT NULL, -- Portfolio value after stress
    pnl_impact DECIMAL(20, 8) NOT NULL, -- Profit/Loss impact
    percentage_impact DECIMAL(8, 4) NOT NULL, -- Percentage change

    -- Risk metrics under stress
    stressed_var DECIMAL(20, 8),
    stressed_volatility DECIMAL(10, 6),
    stressed_correlation DECIMAL(6, 4),
    max_drawdown DECIMAL(8, 4),

    -- Test verdict
    test_passed BOOLEAN NOT NULL,
    failure_reason TEXT,
    risk_score DECIMAL(10, 6), -- Overall risk score after stress

    -- Additional details
    detailed_results JSONB, -- Breakdown by position, factor, etc.
    calculation_time_ms INTEGER, -- How long the calculation took

    -- Partition key
    execution_date DATE NOT NULL,

    -- Constraints
    PRIMARY KEY (id, execution_date)
) PARTITION BY RANGE (execution_date);

-- ================================================================================================
-- RISK DASHBOARD MATERIALIZED VIEW
-- Real-time risk monitoring dashboard
-- ================================================================================================
CREATE MATERIALIZED VIEW mv_risk_dashboard AS
SELECT
    -- Scope identifiers
    COALESCE(rm.account_id, 'ALL') as account_id,
    COALESCE(rm.strategy_id, 'ALL') as strategy_id,
    COALESCE(rm.symbol, 'ALL') as symbol,

    -- Current risk metrics
    rm.metric_name,
    rm.value as current_value,
    rm.warning_threshold,
    rm.breach_threshold,
    rm.emergency_threshold,

    -- Risk status
    CASE
        WHEN rm.value > COALESCE(rm.emergency_threshold, rm.breach_threshold) THEN 'emergency'
        WHEN rm.value > rm.breach_threshold THEN 'critical'
        WHEN rm.value > COALESCE(rm.warning_threshold, rm.breach_threshold * 0.8) THEN 'warning'
        ELSE 'normal'
    END as risk_status,

    -- Utilization percentages
    CASE
        WHEN rm.breach_threshold > 0 THEN (rm.value / rm.breach_threshold * 100)
        ELSE 0
    END as threshold_utilization_pct,

    -- Timing
    rm.calculation_timestamp,
    rm.last_updated,

    -- Recent events
    (SELECT COUNT(*)
     FROM risk_events re
     WHERE re.event_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '1 hour')) * 1000000000
       AND re.severity IN ('high', 'critical', 'emergency')
       AND (re.account_id = rm.account_id OR rm.account_id IS NULL)
       AND (re.strategy_id = rm.strategy_id OR rm.strategy_id IS NULL)
       AND (re.symbol = rm.symbol OR rm.symbol IS NULL)
    ) as recent_high_severity_events,

    -- Model information
    rm.model_name,
    rm.model_version,
    rm.is_valid

FROM risk_metrics rm
WHERE rm.calculation_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '4 hours')) * 1000000000
  AND rm.is_valid = TRUE

-- Get the most recent metric for each combination
AND rm.calculation_timestamp = (
    SELECT MAX(rm2.calculation_timestamp)
    FROM risk_metrics rm2
    WHERE rm2.metric_name = rm.metric_name
      AND COALESCE(rm2.account_id, '') = COALESCE(rm.account_id, '')
      AND COALESCE(rm2.strategy_id, '') = COALESCE(rm.strategy_id, '')
      AND COALESCE(rm2.symbol, '') = COALESCE(rm.symbol, '')
      AND rm2.calculation_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '4 hours')) * 1000000000
      AND rm2.is_valid = TRUE
);

-- ================================================================================================
-- HIGH-PERFORMANCE INDEXES
-- ================================================================================================

-- Risk events indexes
CREATE INDEX idx_risk_events_timestamp ON risk_events USING BTREE (event_timestamp);
CREATE INDEX idx_risk_events_severity_timestamp ON risk_events USING BTREE (severity, event_timestamp);
CREATE INDEX idx_risk_events_type_timestamp ON risk_events USING BTREE (event_type, event_timestamp);
CREATE INDEX idx_risk_events_account ON risk_events USING BTREE (account_id, event_timestamp) WHERE account_id IS NOT NULL;
CREATE INDEX idx_risk_events_symbol ON risk_events USING BTREE (symbol, event_timestamp) WHERE symbol IS NOT NULL;
CREATE INDEX idx_risk_events_unresolved ON risk_events USING BTREE (severity, event_timestamp) WHERE resolved_timestamp IS NULL;
CREATE INDEX idx_risk_events_correlation ON risk_events USING HASH (correlation_id);

-- GIN indexes for JSONB fields
CREATE INDEX idx_risk_events_data_gin ON risk_events USING GIN (event_data);
CREATE INDEX idx_risk_events_action_details_gin ON risk_events USING GIN (action_details);

-- Risk metrics indexes
CREATE INDEX idx_risk_metrics_timestamp ON risk_metrics USING BTREE (calculation_timestamp);
CREATE INDEX idx_risk_metrics_name_scope ON risk_metrics USING BTREE (metric_name, account_id, strategy_id, symbol);
CREATE INDEX idx_risk_metrics_account_timestamp ON risk_metrics USING BTREE (account_id, calculation_timestamp) WHERE account_id IS NOT NULL;
CREATE INDEX idx_risk_metrics_symbol_timestamp ON risk_metrics USING BTREE (symbol, calculation_timestamp) WHERE symbol IS NOT NULL;
CREATE INDEX idx_risk_metrics_valid ON risk_metrics USING BTREE (metric_name, calculation_timestamp) WHERE is_valid = TRUE;

-- Risk limits indexes
CREATE INDEX idx_risk_limits_scope ON risk_limits USING BTREE (limit_type, scope_level);
CREATE INDEX idx_risk_limits_account ON risk_limits USING BTREE (account_id) WHERE account_id IS NOT NULL;
CREATE INDEX idx_risk_limits_active ON risk_limits USING BTREE (limit_type, is_active) WHERE is_active = TRUE;
CREATE INDEX idx_risk_limits_effective ON risk_limits USING BTREE (effective_from, effective_to);

-- Stress test indexes
CREATE INDEX idx_stress_test_results_execution ON stress_test_results USING BTREE (execution_id, execution_timestamp);
CREATE INDEX idx_stress_test_results_scenario ON stress_test_results USING BTREE (scenario_id, execution_timestamp);
CREATE INDEX idx_stress_test_results_account ON stress_test_results USING BTREE (account_id, execution_timestamp) WHERE account_id IS NOT NULL;

-- ================================================================================================
-- TRIGGER FUNCTIONS FOR GENERATED COLUMNS (converted from GENERATED ALWAYS)
-- ================================================================================================

-- Set event_date for risk_events
CREATE OR REPLACE FUNCTION set_risk_event_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.event_date := DATE(TO_TIMESTAMP(NEW.event_timestamp / 1000000000.0));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TRIGGER tg_set_risk_event_date
    BEFORE INSERT OR UPDATE ON risk_events
    FOR EACH ROW
    EXECUTE FUNCTION set_risk_event_date();

-- Set metric_date for risk_metrics
CREATE OR REPLACE FUNCTION set_risk_metric_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.metric_date := DATE(TO_TIMESTAMP(NEW.calculation_timestamp / 1000000000.0));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TRIGGER tg_set_risk_metric_date
    BEFORE INSERT OR UPDATE ON risk_metrics
    FOR EACH ROW
    EXECUTE FUNCTION set_risk_metric_date();

-- Set execution_date for stress_test_results
CREATE OR REPLACE FUNCTION set_stress_test_execution_date()
RETURNS TRIGGER AS $$
BEGIN
    NEW.execution_date := DATE(TO_TIMESTAMP(NEW.execution_timestamp / 1000000000.0));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TRIGGER tg_set_stress_test_execution_date
    BEFORE INSERT OR UPDATE ON stress_test_results
    FOR EACH ROW
    EXECUTE FUNCTION set_stress_test_execution_date();

-- ================================================================================================
-- AUTOMATIC PARTITIONING
-- ================================================================================================

-- Function to create daily partitions for risk events
CREATE OR REPLACE FUNCTION create_risk_events_partition(target_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := target_date;
    end_date := target_date + INTERVAL '1 day';
    partition_name := 'risk_events_' || to_char(start_date, 'YYYY_MM_DD');

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_name = partition_name
    ) THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF risk_events
                       FOR VALUES FROM (%L) TO (%L)',
                       partition_name, start_date, end_date);

        -- Add partition-specific indexes
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (event_timestamp)',
                       'idx_' || partition_name || '_timestamp', partition_name);
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (severity, event_timestamp)',
                       'idx_' || partition_name || '_severity_ts', partition_name);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Function to create monthly partitions for risk metrics
CREATE OR REPLACE FUNCTION create_risk_metrics_partition(target_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := date_trunc('month', target_date);
    end_date := start_date + INTERVAL '1 month';
    partition_name := 'risk_metrics_' || to_char(start_date, 'YYYY_MM');

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_name = partition_name
    ) THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF risk_metrics
                       FOR VALUES FROM (%L) TO (%L)',
                       partition_name, start_date, end_date);

        -- Add partition-specific indexes
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (calculation_timestamp)',
                       'idx_' || partition_name || '_timestamp', partition_name);
        EXECUTE format('CREATE INDEX %I ON %I USING BTREE (metric_name, calculation_timestamp)',
                       'idx_' || partition_name || '_name_ts', partition_name);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Create initial partitions
DO $$
DECLARE
    i INTEGER;
BEGIN
    -- Create risk_events partitions for current and next 7 days
    FOR i IN 0..7 LOOP
        PERFORM create_risk_events_partition(CURRENT_DATE + i);
    END LOOP;

    -- Create risk_metrics partitions for current and next 2 months
    FOR i IN 0..2 LOOP
        PERFORM create_risk_metrics_partition((CURRENT_DATE + (i || ' months')::INTERVAL)::DATE);
    END LOOP;

    -- Create stress_test_results partitions (daily, same as risk_events)
    FOR i IN 0..7 LOOP
        -- Create stress_test_results partitions using similar logic
        DECLARE
            partition_name TEXT;
            start_date DATE;
            end_date DATE;
        BEGIN
            start_date := CURRENT_DATE + i;
            end_date := start_date + INTERVAL '1 day';
            partition_name := 'stress_test_results_' || to_char(start_date, 'YYYY_MM_DD');

            IF NOT EXISTS (
                SELECT 1 FROM information_schema.tables
                WHERE table_name = partition_name
            ) THEN
                EXECUTE format('CREATE TABLE %I PARTITION OF stress_test_results
                               FOR VALUES FROM (%L) TO (%L)',
                               partition_name, start_date, end_date);
            END IF;
        END;
    END LOOP;
END $$;

-- ================================================================================================
-- TRIGGER FUNCTIONS FOR AUTOMATION
-- ================================================================================================

-- Function to automatically update risk limits timestamp
CREATE OR REPLACE FUNCTION update_risk_limits_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    NEW.version := OLD.version + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function to validate risk limit hierarchy
CREATE OR REPLACE FUNCTION validate_risk_limit_hierarchy()
RETURNS TRIGGER AS $$
DECLARE
    parent_limit DECIMAL(20, 8);
BEGIN
    -- Check that child limits don't exceed parent limits
    IF NEW.scope_level = 'account' THEN
        SELECT breach_threshold INTO parent_limit
        FROM risk_limits
        WHERE limit_type = NEW.limit_type
          AND scope_level = 'global'
          AND is_active = TRUE;

        IF parent_limit IS NOT NULL AND NEW.breach_threshold > parent_limit THEN
            RAISE EXCEPTION 'Account limit cannot exceed global limit for %', NEW.limit_type;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function to generate risk events from metric breaches
CREATE OR REPLACE FUNCTION check_risk_metric_breach()
RETURNS TRIGGER AS $$
DECLARE
    applicable_limit RECORD;
    breach_detected BOOLEAN := FALSE;
    severity_level risk_severity;
    event_type_val risk_event_type;
BEGIN
    -- Find applicable risk limit (most specific first)
    SELECT * INTO applicable_limit
    FROM risk_limits rl
    WHERE rl.limit_type = NEW.metric_name
      AND rl.is_active = TRUE
      AND NOW() BETWEEN rl.effective_from AND COALESCE(rl.effective_to, 'infinity'::TIMESTAMP WITH TIME ZONE)
      AND (
        (rl.scope_level = 'symbol' AND rl.symbol = NEW.symbol) OR
        (rl.scope_level = 'strategy' AND rl.strategy_id = NEW.strategy_id) OR
        (rl.scope_level = 'account' AND rl.account_id = NEW.account_id) OR
        (rl.scope_level = 'global')
      )
    ORDER BY
      CASE rl.scope_level
        WHEN 'symbol' THEN 1
        WHEN 'strategy' THEN 2
        WHEN 'account' THEN 3
        WHEN 'global' THEN 4
      END
    LIMIT 1;

    -- Check for breaches
    IF applicable_limit.id IS NOT NULL THEN
        IF NEW.value > COALESCE(applicable_limit.emergency_threshold, applicable_limit.breach_threshold) THEN
            breach_detected := TRUE;
            severity_level := 'emergency';
            event_type_val := CASE
                WHEN NEW.metric_name IN ('var_1d', 'var_10d') THEN 'var_breach'
                WHEN NEW.metric_name IN ('exposure_gross', 'exposure_net') THEN 'exposure_limit_breach'
                WHEN NEW.metric_name = 'leverage_ratio' THEN 'leverage_excess'
                WHEN NEW.metric_name IN ('concentration_single', 'concentration_sector') THEN 'concentration_risk'
                ELSE 'stress_test_failure'
            END;
        ELSIF NEW.value > applicable_limit.breach_threshold THEN
            breach_detected := TRUE;
            severity_level := 'critical';
            event_type_val := CASE
                WHEN NEW.metric_name IN ('var_1d', 'var_10d') THEN 'var_breach'
                WHEN NEW.metric_name IN ('exposure_gross', 'exposure_net') THEN 'exposure_limit_breach'
                WHEN NEW.metric_name = 'leverage_ratio' THEN 'leverage_excess'
                WHEN NEW.metric_name IN ('concentration_single', 'concentration_sector') THEN 'concentration_risk'
                ELSE 'stress_test_failure'
            END;
        ELSIF NEW.value > COALESCE(applicable_limit.warning_threshold, applicable_limit.breach_threshold * 0.8) THEN
            breach_detected := TRUE;
            severity_level := 'medium';
            event_type_val := CASE
                WHEN NEW.metric_name IN ('var_1d', 'var_10d') THEN 'var_breach'
                WHEN NEW.metric_name IN ('exposure_gross', 'exposure_net') THEN 'exposure_limit_breach'
                WHEN NEW.metric_name = 'leverage_ratio' THEN 'leverage_excess'
                WHEN NEW.metric_name IN ('concentration_single', 'concentration_sector') THEN 'concentration_risk'
                ELSE 'stress_test_failure'
            END;
        END IF;

        -- Generate risk event if breach detected
        IF breach_detected THEN
            INSERT INTO risk_events (
                correlation_id, event_timestamp, detected_timestamp,
                event_type, severity, risk_metric,
                symbol, account_id, strategy_id,
                threshold_value, actual_value, breach_percentage,
                description, risk_model, model_version,
                recommended_action, source_system, node_id, process_id,
                event_data
            ) VALUES (
                NEW.id,
                NEW.calculation_timestamp,
                EXTRACT(EPOCH FROM NOW()) * 1000000000,
                event_type_val,
                severity_level,
                NEW.metric_name,
                NEW.symbol,
                NEW.account_id,
                NEW.strategy_id,
                applicable_limit.breach_threshold,
                NEW.value,
                ((NEW.value - applicable_limit.breach_threshold) / applicable_limit.breach_threshold * 100),
                format('Risk metric %s breached: %s > %s', NEW.metric_name, NEW.value, applicable_limit.breach_threshold),
                NEW.model_name,
                NEW.model_version,
                applicable_limit.breach_action,
                'risk_engine',
                'risk-node-01',
                pg_backend_pid(),
                jsonb_build_object(
                    'metric_id', NEW.id,
                    'limit_id', applicable_limit.id,
                    'calculation_details', NEW.calculation_details,
                    'confidence_level', NEW.confidence_level
                )
            );
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ================================================================================================
-- CREATE TRIGGERS
-- ================================================================================================

-- Risk limits triggers
CREATE TRIGGER tg_update_risk_limits_timestamp
    BEFORE UPDATE ON risk_limits
    FOR EACH ROW
    EXECUTE FUNCTION update_risk_limits_timestamp();

CREATE TRIGGER tg_validate_risk_limit_hierarchy
    BEFORE INSERT OR UPDATE ON risk_limits
    FOR EACH ROW
    EXECUTE FUNCTION validate_risk_limit_hierarchy();

-- Risk metrics breach detection
CREATE TRIGGER tg_check_risk_metric_breach
    AFTER INSERT OR UPDATE ON risk_metrics
    FOR EACH ROW
    WHEN (NEW.is_valid = TRUE)
    EXECUTE FUNCTION check_risk_metric_breach();

-- Update stress test scenario timestamp
CREATE TRIGGER tg_update_stress_scenarios_timestamp
    BEFORE UPDATE ON stress_test_scenarios
    FOR EACH ROW
    EXECUTE FUNCTION update_risk_limits_timestamp(); -- Reuse same function

-- ================================================================================================
-- RISK MANAGEMENT FUNCTIONS
-- ================================================================================================

-- Function to calculate portfolio VaR
CREATE OR REPLACE FUNCTION calculate_portfolio_var(
    p_account_id VARCHAR(64) DEFAULT NULL,
    p_strategy_id VARCHAR(100) DEFAULT NULL,
    p_confidence_level DECIMAL(5,4) DEFAULT 0.95,
    p_time_horizon_days INTEGER DEFAULT 1
) RETURNS DECIMAL(20,8) AS $$
DECLARE
    portfolio_var DECIMAL(20,8) := 0;
    position_count INTEGER;
BEGIN
    -- Simple VaR calculation based on current positions
    -- In production, this would use more sophisticated models

    SELECT COUNT(*) INTO position_count
    FROM positions p
    WHERE (p_account_id IS NULL OR p.account_id = p_account_id)
      AND (p_strategy_id IS NULL OR p.strategy_id = p_strategy_id)
      AND p.quantity != 0;

    IF position_count = 0 THEN
        RETURN 0;
    END IF;

    -- Placeholder calculation - implement actual VaR model
    SELECT COALESCE(SUM(ABS(p.market_value) * 0.02), 0) -- 2% daily volatility assumption
    INTO portfolio_var
    FROM positions p
    WHERE (p_account_id IS NULL OR p.account_id = p_account_id)
      AND (p_strategy_id IS NULL OR p.strategy_id = p_strategy_id)
      AND p.quantity != 0;

    -- Adjust for confidence level and time horizon
    portfolio_var := portfolio_var * SQRT(p_time_horizon_days) *
                    (CASE
                        WHEN p_confidence_level >= 0.99 THEN 2.33
                        WHEN p_confidence_level >= 0.95 THEN 1.65
                        ELSE 1.28
                    END);

    RETURN portfolio_var;
END;
$$ LANGUAGE plpgsql;

-- Function to refresh risk dashboard
CREATE OR REPLACE FUNCTION refresh_risk_dashboard()
RETURNS VOID AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_risk_dashboard;
END;
$$ LANGUAGE plpgsql;

-- Function to get active risk alerts
CREATE OR REPLACE FUNCTION get_active_risk_alerts(
    p_severity risk_severity[] DEFAULT ARRAY['high'::risk_severity, 'critical'::risk_severity, 'emergency'::risk_severity]
) RETURNS TABLE (
    event_id UUID,
    event_type risk_event_type,
    severity risk_severity,
    symbol VARCHAR(32),
    account_id VARCHAR(64),
    description TEXT,
    event_timestamp ns_timestamp,
    age_minutes INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        re.id,
        re.event_type,
        re.severity,
        re.symbol,
        re.account_id,
        re.description,
        re.event_timestamp,
        EXTRACT(EPOCH FROM (NOW() - TO_TIMESTAMP(re.event_timestamp / 1000000000.0))) / 60 AS age_minutes
    FROM risk_events re
    WHERE re.resolved_timestamp IS NULL
      AND re.severity = ANY(p_severity)
      AND re.event_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '24 hours')) * 1000000000
    ORDER BY re.severity DESC, re.event_timestamp DESC;
END;
$$ LANGUAGE plpgsql;

-- ================================================================================================
-- REPORTING VIEWS
-- ================================================================================================

-- Active risk alerts view
CREATE VIEW v_active_risk_alerts AS
SELECT
    re.id,
    re.event_type,
    re.severity,
    re.symbol,
    re.account_id,
    re.strategy_id,
    re.description,
    re.actual_value,
    re.threshold_value,
    re.breach_percentage,
    TO_TIMESTAMP(re.event_timestamp / 1000000000.0) as event_time,
    TO_TIMESTAMP(re.detected_timestamp / 1000000000.0) as detected_time,
    EXTRACT(EPOCH FROM (NOW() - TO_TIMESTAMP(re.event_timestamp / 1000000000.0))) / 60 as age_minutes,
    re.recommended_action,
    re.acknowledged_by IS NOT NULL as is_acknowledged
FROM risk_events re
WHERE re.resolved_timestamp IS NULL
  AND re.severity IN ('medium', 'high', 'critical', 'emergency')
  AND re.event_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '7 days')) * 1000000000
ORDER BY
    CASE re.severity
        WHEN 'emergency' THEN 1
        WHEN 'critical' THEN 2
        WHEN 'high' THEN 3
        WHEN 'medium' THEN 4
        ELSE 5
    END,
    re.event_timestamp DESC;

-- Risk metrics summary view
CREATE VIEW v_risk_metrics_summary AS
SELECT
    rm.metric_name,
    rm.account_id,
    rm.strategy_id,
    rm.symbol,
    rm.value as current_value,
    rl.warning_threshold,
    rl.breach_threshold,
    rl.emergency_threshold,
    CASE
        WHEN rm.value > COALESCE(rl.emergency_threshold, rl.breach_threshold) THEN 'EMERGENCY'
        WHEN rm.value > rl.breach_threshold THEN 'CRITICAL'
        WHEN rm.value > COALESCE(rl.warning_threshold, rl.breach_threshold * 0.8) THEN 'WARNING'
        ELSE 'NORMAL'
    END as status,
    TO_TIMESTAMP(rm.calculation_timestamp / 1000000000.0) as calculated_at,
    rm.model_name,
    rm.is_valid
FROM risk_metrics rm
LEFT JOIN risk_limits rl ON (
    rl.limit_type = rm.metric_name
    AND rl.is_active = TRUE
    AND NOW() BETWEEN rl.effective_from AND COALESCE(rl.effective_to, 'infinity'::TIMESTAMP WITH TIME ZONE)
    AND (
        (rl.scope_level = 'symbol' AND rl.symbol = rm.symbol) OR
        (rl.scope_level = 'strategy' AND rl.strategy_id = rm.strategy_id) OR
        (rl.scope_level = 'account' AND rl.account_id = rm.account_id) OR
        (rl.scope_level = 'global' AND rl.account_id IS NULL AND rl.strategy_id IS NULL AND rl.symbol IS NULL)
    )
)
WHERE rm.calculation_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '4 hours')) * 1000000000
  AND rm.is_valid = TRUE
  -- Get most recent calculation for each metric/scope combination
  AND rm.calculation_timestamp = (
    SELECT MAX(rm2.calculation_timestamp)
    FROM risk_metrics rm2
    WHERE rm2.metric_name = rm.metric_name
      AND COALESCE(rm2.account_id, '') = COALESCE(rm.account_id, '')
      AND COALESCE(rm2.strategy_id, '') = COALESCE(rm.strategy_id, '')
      AND COALESCE(rm2.symbol, '') = COALESCE(rm.symbol, '')
      AND rm2.is_valid = TRUE
  );

-- ================================================================================================
-- COMMENTS AND DOCUMENTATION
-- ================================================================================================

COMMENT ON TABLE risk_events IS 'Immutable event store for all risk management events including breaches, alerts, and stress test results. Critical for compliance and risk monitoring.';
COMMENT ON TABLE risk_metrics IS 'Historical and current risk metric calculations with confidence intervals. Partitioned by date for performance.';
COMMENT ON TABLE risk_limits IS 'Configurable risk limits with hierarchical scope (global > account > strategy > symbol). Supports time-based limits and automatic enforcement.';
COMMENT ON TABLE stress_test_scenarios IS 'Predefined stress test scenarios including historical events, hypothetical shocks, and Monte Carlo simulations.';
COMMENT ON TABLE stress_test_results IS 'Results from stress test executions showing portfolio impact under various scenarios. Critical for regulatory reporting.';

COMMENT ON MATERIALIZED VIEW mv_risk_dashboard IS 'Real-time risk monitoring dashboard with current metrics, thresholds, and alert counts. Refresh every 5 minutes in production.';

COMMENT ON FUNCTION calculate_portfolio_var IS 'Calculate portfolio Value at Risk using specified confidence level and time horizon. Implement with actual risk models in production.';
COMMENT ON FUNCTION get_active_risk_alerts IS 'Get currently active risk alerts filtered by severity. Used by monitoring systems and dashboards.';