-- =====================================================
-- ML SCHEMA - PREDICTION OUTCOMES TABLE
-- =====================================================
-- Description: Actual outcomes vs predictions for model evaluation
-- Schema: ml
-- Author: Database Agent
-- Date: 2025-12-06
-- =====================================================

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

  -- Predicción asociada
  prediction_id UUID NOT NULL REFERENCES ml.predictions(id) ON DELETE CASCADE,

  -- Resultado real
  actual_result ml.prediction_result,
  actual_value DECIMAL(20,8),

  -- Evaluación
  outcome_status ml.outcome_status NOT NULL DEFAULT 'pending',
  is_correct BOOLEAN,

  -- Métricas de error
  absolute_error DECIMAL(20,8), -- |predicted - actual|
  relative_error DECIMAL(10,6), -- (predicted - actual) / actual

  -- Contexto de mercado en verificación
  actual_price DECIMAL(20,8),
  price_change_percent DECIMAL(10,6),

  -- Timing
  outcome_timestamp TIMESTAMPTZ NOT NULL, -- Cuando se verificó el outcome
  time_to_outcome_hours DECIMAL(10,2), -- Horas desde predicción hasta outcome

  -- Metadata
  outcome_metadata JSONB,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Constraints
  CONSTRAINT unique_prediction_outcome UNIQUE(prediction_id)
);

-- Índices
CREATE INDEX idx_outcomes_prediction ON ml.prediction_outcomes(prediction_id);
CREATE INDEX idx_outcomes_status ON ml.prediction_outcomes(outcome_status);
CREATE INDEX idx_outcomes_correct ON ml.prediction_outcomes(is_correct)
  WHERE is_correct IS NOT NULL;
CREATE INDEX idx_outcomes_timestamp ON ml.prediction_outcomes(outcome_timestamp DESC);

-- Comentarios
COMMENT ON TABLE ml.prediction_outcomes IS 'Actual results for model predictions, used for performance tracking';
COMMENT ON COLUMN ml.prediction_outcomes.actual_result IS 'What actually happened (buy/sell/hold/up/down/neutral)';
COMMENT ON COLUMN ml.prediction_outcomes.actual_value IS 'Actual numeric value (e.g., actual price reached)';
COMMENT ON COLUMN ml.prediction_outcomes.is_correct IS 'Whether prediction matched actual outcome';
COMMENT ON COLUMN ml.prediction_outcomes.absolute_error IS 'Absolute difference between predicted and actual';
COMMENT ON COLUMN ml.prediction_outcomes.relative_error IS 'Percentage error relative to actual value';
COMMENT ON COLUMN ml.prediction_outcomes.time_to_outcome_hours IS 'Time elapsed from prediction to outcome verification';

-- Ejemplo de outcome_metadata JSONB:
COMMENT ON COLUMN ml.prediction_outcomes.outcome_metadata IS
'Example: {
  "verification_method": "automated",
  "market_volatility": "high",
  "external_events": ["fed_announcement"],
  "notes": "Prediction incorrect due to unexpected news"
}';