-- ============================================================================
-- Schema: trading
-- Table: signals
-- Description: INTERFAZ con ML - Señales de trading generadas por modelos ML
-- Dependencies: trading.signal_type, trading.confidence_level, trading.timeframe
--
-- IMPORTANTE: Esta tabla es la INTERFAZ entre Trading y ML Signals
-- Resuelve la dependencia circular permitiendo que ambos módulos
-- trabajen independientemente. ML escribe señales aquí, Trading las consume.
-- ============================================================================

CREATE TABLE trading.signals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Origen de la señal
  source VARCHAR(50) NOT NULL, -- 'ml_atlas', 'ml_orion', 'ml_nova', 'manual'
  model_version VARCHAR(50),

  -- Target
  symbol VARCHAR(20) NOT NULL,
  timeframe trading.timeframe NOT NULL,

  -- Señal
  signal_type trading.signal_type NOT NULL,
  confidence trading.confidence_level NOT NULL,
  confidence_score DECIMAL(5,4), -- 0.0000 to 1.0000

  -- Precios objetivo
  entry_price DECIMAL(20,8),
  target_price DECIMAL(20,8),
  stop_loss DECIMAL(20,8),

  -- Predicciones
  predicted_delta_high DECIMAL(10,4), -- % esperado de subida
  predicted_delta_low DECIMAL(10,4),  -- % esperado de bajada

  -- Resultado (se actualiza después)
  actual_outcome VARCHAR(20), -- 'hit_target', 'hit_stop', 'expired', 'cancelled'
  actual_delta DECIMAL(10,4),
  outcome_at TIMESTAMPTZ,

  -- Validez
  valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  valid_until TIMESTAMPTZ NOT NULL,
  is_active BOOLEAN DEFAULT true,

  -- Metadata
  metadata JSONB DEFAULT '{}',

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

-- Índices
CREATE INDEX idx_signals_symbol ON trading.signals(symbol);
CREATE INDEX idx_signals_active ON trading.signals(is_active, valid_until) WHERE is_active = true;
CREATE INDEX idx_signals_source ON trading.signals(source);
CREATE INDEX idx_signals_created ON trading.signals(created_at DESC);
CREATE INDEX idx_signals_symbol_timeframe ON trading.signals(symbol, timeframe);
CREATE INDEX idx_signals_outcome ON trading.signals(actual_outcome);

-- Comentarios
COMMENT ON TABLE trading.signals IS 'INTERFAZ: Señales de trading generadas por modelos ML o manualmente';
COMMENT ON COLUMN trading.signals.source IS 'Origen: ml_atlas, ml_orion, ml_nova, manual';
COMMENT ON COLUMN trading.signals.confidence_score IS 'Score numérico de confianza entre 0 y 1';
COMMENT ON COLUMN trading.signals.predicted_delta_high IS 'Porcentaje esperado de subida desde entry_price';
COMMENT ON COLUMN trading.signals.predicted_delta_low IS 'Porcentaje esperado de bajada desde entry_price';
COMMENT ON COLUMN trading.signals.actual_outcome IS 'Resultado real: hit_target, hit_stop, expired, cancelled';