-- =====================================================
-- ML SCHEMA - RISK EVENTS TABLE
-- =====================================================
-- Description: Risk management events and circuit breaker triggers
-- Schema: ml
-- Author: Database Agent
-- Date: 2026-01-04
-- Module: OQI-010-llm-trading-integration
-- =====================================================

CREATE TABLE ml.risk_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- User context (optional, for multi-user systems)
  user_id UUID,

  -- Event classification
  event_type VARCHAR(50) NOT NULL,
  severity VARCHAR(20) NOT NULL,

  -- Event details
  details JSONB NOT NULL DEFAULT '{}',

  -- Response
  action_taken VARCHAR(100),

  -- Resolution status
  resolved BOOLEAN NOT NULL DEFAULT FALSE,
  resolved_at TIMESTAMPTZ,

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Constraints
  CONSTRAINT chk_risk_events_type CHECK (
    event_type IN (
      'CIRCUIT_BREAKER',      -- Trading halted due to losses
      'DAILY_LIMIT',          -- Daily loss limit reached
      'WEEKLY_LIMIT',         -- Weekly loss limit reached
      'EXPOSURE_LIMIT',       -- Maximum exposure exceeded
      'POSITION_LIMIT',       -- Maximum position size exceeded
      'CORRELATION_LIMIT',    -- Too many correlated positions
      'DRAWDOWN_WARNING',     -- Approaching drawdown limit
      'CONSECUTIVE_LOSSES',   -- Multiple consecutive losing trades
      'VOLATILITY_SPIKE',     -- Abnormal market volatility detected
      'LIQUIDITY_WARNING',    -- Low liquidity conditions
      'NEWS_EVENT',           -- High-impact news event
      'SYSTEM_ERROR',         -- System malfunction
      'MANUAL_OVERRIDE',      -- User manually intervened
      'POSITION_TIMEOUT',     -- Position held too long
      'SLIPPAGE_ALERT'        -- Excessive slippage detected
    )
  ),
  CONSTRAINT chk_risk_events_severity CHECK (
    severity IN ('info', 'warning', 'critical', 'emergency')
  ),
  CONSTRAINT chk_risk_events_resolution CHECK (
    (resolved = TRUE AND resolved_at IS NOT NULL) OR resolved = FALSE
  )
);

-- Indices
CREATE INDEX idx_risk_events_user ON ml.risk_events(user_id)
  WHERE user_id IS NOT NULL;
CREATE INDEX idx_risk_events_type ON ml.risk_events(event_type);
CREATE INDEX idx_risk_events_severity ON ml.risk_events(severity);
CREATE INDEX idx_risk_events_resolved ON ml.risk_events(resolved);
CREATE INDEX idx_risk_events_created ON ml.risk_events(created_at DESC);
CREATE INDEX idx_risk_events_resolved_at ON ml.risk_events(resolved_at DESC)
  WHERE resolved_at IS NOT NULL;

-- Active critical events (unresolved)
CREATE INDEX idx_risk_events_active_critical ON ml.risk_events(created_at DESC)
  WHERE resolved = FALSE AND severity IN ('critical', 'emergency');

-- User active events
CREATE INDEX idx_risk_events_user_active ON ml.risk_events(user_id, created_at DESC)
  WHERE resolved = FALSE;

-- Index for circuit breaker status check
CREATE INDEX idx_risk_events_circuit_breaker ON ml.risk_events(user_id, created_at DESC)
  WHERE event_type = 'CIRCUIT_BREAKER' AND resolved = FALSE;

-- Comments
COMMENT ON TABLE ml.risk_events IS 'Risk management events including circuit breakers, limit violations, and system alerts';

COMMENT ON COLUMN ml.risk_events.id IS 'Unique identifier for the risk event';
COMMENT ON COLUMN ml.risk_events.user_id IS 'User ID for multi-user systems (NULL for system-wide events)';
COMMENT ON COLUMN ml.risk_events.event_type IS 'Type of risk event: CIRCUIT_BREAKER, DAILY_LIMIT, EXPOSURE_LIMIT, etc.';
COMMENT ON COLUMN ml.risk_events.severity IS 'Event severity: info, warning, critical, emergency';
COMMENT ON COLUMN ml.risk_events.details IS 'JSON object with event-specific details';
COMMENT ON COLUMN ml.risk_events.action_taken IS 'Action taken in response to the event';
COMMENT ON COLUMN ml.risk_events.resolved IS 'Whether the event has been resolved';
COMMENT ON COLUMN ml.risk_events.resolved_at IS 'Timestamp when event was resolved';
COMMENT ON COLUMN ml.risk_events.created_at IS 'Timestamp when event was detected';

-- Example of details JSONB structure
COMMENT ON COLUMN ml.risk_events.details IS
'Example structures by event type:

CIRCUIT_BREAKER:
{
  "trigger_reason": "daily_drawdown_limit",
  "current_drawdown_pct": 5.2,
  "limit_pct": 5.0,
  "trading_paused_until": "2026-01-05T00:00:00Z",
  "affected_symbols": ["XAUUSD", "EURUSD"]
}

DAILY_LIMIT:
{
  "current_loss_pct": 2.1,
  "limit_pct": 2.0,
  "trades_today": 8,
  "last_trade_pnl": -45.50
}

EXPOSURE_LIMIT:
{
  "current_exposure_pct": 22.5,
  "limit_pct": 20.0,
  "open_positions": [
    {"symbol": "XAUUSD", "exposure_pct": 8.5},
    {"symbol": "EURUSD", "exposure_pct": 7.0},
    {"symbol": "BTCUSDT", "exposure_pct": 7.0}
  ]
}

VOLATILITY_SPIKE:
{
  "symbol": "XAUUSD",
  "current_volatility": 2.5,
  "normal_volatility": 0.8,
  "volatility_ratio": 3.125,
  "recommendation": "reduce_position_size"
}

NEWS_EVENT:
{
  "event_name": "FOMC Decision",
  "impact": "high",
  "scheduled_time": "2026-01-04T19:00:00Z",
  "affected_pairs": ["EURUSD", "GBPUSD", "USDJPY", "XAUUSD"],
  "recommendation": "close_positions_before_event"
}';