-- =====================================================
-- ML SCHEMA - LLM DECISIONS TABLE
-- =====================================================
-- Description: Decisions made by the LLM Trading Agent
-- Schema: ml
-- Author: Database Agent
-- Date: 2026-01-04
-- Module: OQI-010-llm-trading-integration
-- =====================================================

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

  -- Reference to prediction
  prediction_id UUID REFERENCES ml.llm_predictions(id) ON DELETE SET NULL,

  -- Decision details
  decision_type VARCHAR(50) NOT NULL,
  action_taken VARCHAR(50) NOT NULL,
  reasoning TEXT,

  -- Risk assessment
  risk_level VARCHAR(20),
  position_size DECIMAL(10,4),
  risk_pct DECIMAL(5,4) CHECK (risk_pct >= 0 AND risk_pct <= 1),

  -- Execution details
  executed BOOLEAN NOT NULL DEFAULT FALSE,
  execution_venue VARCHAR(20),
  order_id VARCHAR(100),

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

  -- Constraints
  CONSTRAINT chk_llm_decisions_type CHECK (
    decision_type IN (
      'TRADE',           -- Execute a trade
      'ALERT',           -- Send alert to user
      'WAIT',            -- Wait for better conditions
      'CLOSE',           -- Close existing position
      'PARTIAL_CLOSE',   -- Partially close position
      'MODIFY_SL',       -- Modify stop loss
      'MODIFY_TP',       -- Modify take profit
      'SCALE_IN',        -- Add to existing position
      'HEDGE',           -- Open hedging position
      'STANDBY'          -- No action, monitoring
    )
  ),
  CONSTRAINT chk_llm_decisions_action CHECK (
    action_taken IN (
      'BUY', 'SELL', 'HOLD', 'CLOSE_LONG', 'CLOSE_SHORT',
      'PARTIAL_CLOSE_LONG', 'PARTIAL_CLOSE_SHORT',
      'MOVE_SL_BE', 'TRAILING_STOP', 'ALERT_SENT',
      'WAITING', 'MONITORING', 'REJECTED', 'SKIPPED'
    )
  ),
  CONSTRAINT chk_llm_decisions_risk_level CHECK (
    risk_level IS NULL OR risk_level IN (
      'minimal', 'conservative', 'moderate', 'aggressive', 'high_risk'
    )
  ),
  CONSTRAINT chk_llm_decisions_venue CHECK (
    execution_venue IS NULL OR execution_venue IN ('MT4', 'MT5', 'BINANCE', 'PAPER')
  ),
  CONSTRAINT chk_llm_decisions_position_size CHECK (
    position_size IS NULL OR position_size > 0
  ),
  CONSTRAINT chk_llm_decisions_execution CHECK (
    (executed = TRUE AND execution_venue IS NOT NULL) OR executed = FALSE
  )
);

-- Indices
CREATE INDEX idx_llm_decisions_prediction ON ml.llm_decisions(prediction_id)
  WHERE prediction_id IS NOT NULL;
CREATE INDEX idx_llm_decisions_type ON ml.llm_decisions(decision_type);
CREATE INDEX idx_llm_decisions_action ON ml.llm_decisions(action_taken);
CREATE INDEX idx_llm_decisions_executed ON ml.llm_decisions(executed);
CREATE INDEX idx_llm_decisions_venue ON ml.llm_decisions(execution_venue)
  WHERE execution_venue IS NOT NULL;
CREATE INDEX idx_llm_decisions_order ON ml.llm_decisions(order_id)
  WHERE order_id IS NOT NULL;
CREATE INDEX idx_llm_decisions_created ON ml.llm_decisions(created_at DESC);
CREATE INDEX idx_llm_decisions_risk_level ON ml.llm_decisions(risk_level)
  WHERE risk_level IS NOT NULL;

-- Index for trade decisions that were executed
CREATE INDEX idx_llm_decisions_trades ON ml.llm_decisions(created_at DESC)
  WHERE decision_type = 'TRADE' AND executed = TRUE;

-- Comments
COMMENT ON TABLE ml.llm_decisions IS 'Decisions made by the LLM Trading Agent based on predictions and risk assessment';

COMMENT ON COLUMN ml.llm_decisions.id IS 'Unique identifier for the decision';
COMMENT ON COLUMN ml.llm_decisions.prediction_id IS 'Reference to the prediction that triggered this decision';
COMMENT ON COLUMN ml.llm_decisions.decision_type IS 'Type of decision: TRADE, ALERT, WAIT, CLOSE, etc.';
COMMENT ON COLUMN ml.llm_decisions.action_taken IS 'Specific action: BUY, SELL, HOLD, CLOSE_LONG, etc.';
COMMENT ON COLUMN ml.llm_decisions.reasoning IS 'LLM generated reasoning for the decision';
COMMENT ON COLUMN ml.llm_decisions.risk_level IS 'Risk profile applied: minimal, conservative, moderate, aggressive';
COMMENT ON COLUMN ml.llm_decisions.position_size IS 'Position size in lots or units';
COMMENT ON COLUMN ml.llm_decisions.risk_pct IS 'Risk percentage of capital (0.0 to 1.0)';
COMMENT ON COLUMN ml.llm_decisions.executed IS 'Whether the decision was executed in market';
COMMENT ON COLUMN ml.llm_decisions.execution_venue IS 'Execution venue: MT4, MT5, BINANCE, or PAPER';
COMMENT ON COLUMN ml.llm_decisions.order_id IS 'Order ID from execution venue if executed';
COMMENT ON COLUMN ml.llm_decisions.created_at IS 'Timestamp when decision was made';