trading-platform-database/schemas/03_trading_schema.sql

-- ============================================================================
-- OrbiQuant IA - Esquema TRADING
-- ============================================================================
-- Archivo: 03_trading_schema.sql
-- Descripción: Bots, señales, estrategias y operaciones de trading
-- Fecha: 2025-12-05
-- ============================================================================

SET search_path TO trading;

-- ============================================================================
-- TIPOS ENUMERADOS
-- ============================================================================

CREATE TYPE bot_status_enum AS ENUM ('active', 'paused', 'stopped', 'error', 'maintenance');
CREATE TYPE signal_status_enum AS ENUM ('pending', 'active', 'triggered', 'expired', 'cancelled');
CREATE TYPE position_status_enum AS ENUM ('open', 'closed', 'pending');
CREATE TYPE timeframe_enum AS ENUM ('1m', '5m', '15m', '30m', '1h', '4h', '1d', '1w');
CREATE TYPE amd_phase_enum AS ENUM ('accumulation', 'manipulation', 'distribution', 'unknown');
CREATE TYPE volatility_regime_enum AS ENUM ('low', 'medium', 'high', 'extreme');

-- ============================================================================
-- TABLA: symbols
-- Descripción: Instrumentos financieros disponibles
-- ============================================================================
CREATE TABLE IF NOT EXISTS symbols (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Identificación
    symbol VARCHAR(20) NOT NULL UNIQUE, -- 'XAUUSD', 'EURUSD', 'BTCUSD'
    name VARCHAR(100) NOT NULL,
    description TEXT,

    -- Clasificación
    asset_class VARCHAR(50), -- 'forex', 'crypto', 'commodities', 'indices', 'stocks'
    base_currency VARCHAR(10),
    quote_currency VARCHAR(10),

    -- Trading info
    pip_value DECIMAL(10,6),
    lot_size DECIMAL(10,2),
    min_lot DECIMAL(10,4),
    max_lot DECIMAL(10,2),
    tick_size DECIMAL(10,6),

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    trading_hours JSONB, -- [{day, open, close}]

    -- Data provider
    data_provider VARCHAR(50),
    provider_symbol VARCHAR(50),

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_symbols_symbol ON symbols(symbol);
CREATE INDEX idx_symbols_asset_class ON symbols(asset_class);

-- ============================================================================
-- TABLA: strategies
-- Descripción: Estrategias de trading disponibles
-- ============================================================================
CREATE TABLE IF NOT EXISTS strategies (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,

    -- Tipo
    strategy_type VARCHAR(50), -- 'intraday', 'swing', 'scalping', 'position'

    -- Configuración
    default_timeframe timeframe_enum DEFAULT '15m',
    supported_symbols TEXT[], -- NULL = todos
    risk_reward_ratio DECIMAL(4,2) DEFAULT 2.0,

    -- Parámetros configurables (JSON Schema)
    parameters_schema JSONB,
    default_parameters JSONB,

    -- ML Model asociado
    uses_ml_model BOOLEAN DEFAULT TRUE,
    ml_model_id UUID, -- Referencia a ml.models

    -- AMD
    uses_amd BOOLEAN DEFAULT TRUE,
    favorable_phases amd_phase_enum[],

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_strategies_slug ON strategies(slug);

-- ============================================================================
-- TABLA: bots (Agentes de Trading)
-- Descripción: Instancias de bots/agentes de trading
-- ============================================================================
CREATE TABLE IF NOT EXISTS bots (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Identificación
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    avatar_url TEXT,

    -- Perfil de riesgo
    risk_profile public.risk_profile_enum NOT NULL,

    -- Objetivos
    target_monthly_return DECIMAL(5,2), -- % objetivo
    max_drawdown DECIMAL(5,2),          -- % máximo
    max_position_size DECIMAL(5,2),     -- % del capital

    -- Estrategias
    strategy_id UUID REFERENCES strategies(id),
    strategy_parameters JSONB,

    -- Configuración
    supported_symbols TEXT[],
    default_timeframe timeframe_enum DEFAULT '15m',
    min_confidence DECIMAL(3,2) DEFAULT 0.55,

    -- Horarios de operación
    trading_schedule JSONB, -- Horarios permitidos

    -- Estado
    status bot_status_enum DEFAULT 'stopped',
    last_activity_at TIMESTAMPTZ,
    error_message TEXT,

    -- Métricas globales (actualizadas periódicamente)
    total_trades INT DEFAULT 0,
    winning_trades INT DEFAULT 0,
    total_profit DECIMAL(15,2) DEFAULT 0,
    sharpe_ratio DECIMAL(5,2),
    sortino_ratio DECIMAL(5,2),
    max_drawdown_actual DECIMAL(5,2),

    -- Público
    is_public BOOLEAN DEFAULT TRUE, -- Visible para asignar a cuentas

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_bots_slug ON bots(slug);
CREATE INDEX idx_bots_risk_profile ON bots(risk_profile);
CREATE INDEX idx_bots_status ON bots(status);

-- ============================================================================
-- TABLA: signals
-- Descripción: Señales de trading generadas
-- ============================================================================
CREATE TABLE IF NOT EXISTS signals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Origen
    bot_id UUID REFERENCES bots(id),
    strategy_id UUID REFERENCES strategies(id),
    symbol_id UUID REFERENCES symbols(id),
    symbol VARCHAR(20) NOT NULL,

    -- Señal
    direction public.trade_direction_enum NOT NULL,
    timeframe timeframe_enum NOT NULL,

    -- Precios
    entry_price DECIMAL(20,8) NOT NULL,
    stop_loss DECIMAL(20,8) NOT NULL,
    take_profit DECIMAL(20,8) NOT NULL,
    current_price DECIMAL(20,8),

    -- Predicción ML
    predicted_delta_high DECIMAL(20,8),
    predicted_delta_low DECIMAL(20,8),
    prob_tp_first DECIMAL(5,4), -- Probabilidad de tocar TP antes que SL
    confidence_score DECIMAL(5,4),

    -- Contexto de mercado
    amd_phase amd_phase_enum,
    volatility_regime volatility_regime_enum,
    market_context JSONB, -- Datos adicionales del contexto

    -- R:R
    risk_reward_ratio DECIMAL(4,2),
    risk_pips DECIMAL(10,4),
    reward_pips DECIMAL(10,4),

    -- Estado
    status signal_status_enum DEFAULT 'pending',
    triggered_at TIMESTAMPTZ,
    expired_at TIMESTAMPTZ,

    -- Resultado (si se operó)
    outcome VARCHAR(20), -- 'tp_hit', 'sl_hit', 'manual_close', 'expired'
    pnl DECIMAL(15,2),

    -- Timestamps
    valid_until TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_signals_bot ON signals(bot_id);
CREATE INDEX idx_signals_symbol ON signals(symbol);
CREATE INDEX idx_signals_status ON signals(status);
CREATE INDEX idx_signals_created ON signals(created_at DESC);
CREATE INDEX idx_signals_direction ON signals(direction);

-- ============================================================================
-- TABLA: positions
-- Descripción: Posiciones de trading (abiertas y cerradas)
-- ============================================================================
CREATE TABLE IF NOT EXISTS positions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Referencias
    bot_id UUID REFERENCES bots(id),
    signal_id UUID REFERENCES signals(id),
    investment_account_id UUID, -- Referencia a investment.accounts
    symbol_id UUID REFERENCES symbols(id),
    symbol VARCHAR(20) NOT NULL,

    -- Posición
    direction public.trade_direction_enum NOT NULL,
    lot_size DECIMAL(10,4) NOT NULL,

    -- Precios
    entry_price DECIMAL(20,8) NOT NULL,
    stop_loss DECIMAL(20,8),
    take_profit DECIMAL(20,8),
    exit_price DECIMAL(20,8),

    -- Broker
    broker_order_id VARCHAR(100),
    broker_position_id VARCHAR(100),

    -- Estado
    status position_status_enum DEFAULT 'open',
    opened_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMPTZ,
    close_reason VARCHAR(50), -- 'tp', 'sl', 'manual', 'forced', 'margin_call'

    -- P&L
    realized_pnl DECIMAL(15,2),
    unrealized_pnl DECIMAL(15,2),
    commission DECIMAL(10,2) DEFAULT 0,
    swap DECIMAL(10,2) DEFAULT 0,

    -- Metadata
    metadata JSONB,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_positions_bot ON positions(bot_id);
CREATE INDEX idx_positions_signal ON positions(signal_id);
CREATE INDEX idx_positions_account ON positions(investment_account_id);
CREATE INDEX idx_positions_status ON positions(status);
CREATE INDEX idx_positions_opened ON positions(opened_at DESC);

-- ============================================================================
-- TABLA: price_alerts
-- Descripción: Alertas de precio configuradas por usuarios
-- ============================================================================
CREATE TYPE alert_condition_enum AS ENUM ('above', 'below', 'crosses_above', 'crosses_below');

CREATE TABLE IF NOT EXISTS price_alerts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    symbol VARCHAR(20) NOT NULL,
    condition alert_condition_enum NOT NULL,
    price DECIMAL(20,8) NOT NULL,
    note TEXT,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    triggered_at TIMESTAMPTZ,
    triggered_price DECIMAL(20,8),

    -- Notificación
    notify_email BOOLEAN DEFAULT TRUE,
    notify_push BOOLEAN DEFAULT TRUE,

    -- Recurrencia
    is_recurring BOOLEAN DEFAULT FALSE, -- Si se reactiva después de triggear

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_price_alerts_user ON price_alerts(user_id);
CREATE INDEX idx_price_alerts_symbol ON price_alerts(symbol);
CREATE INDEX idx_price_alerts_active ON price_alerts(is_active) WHERE is_active = TRUE;

-- ============================================================================
-- TABLA: watchlists
-- Descripción: Listas de seguimiento de usuarios
-- ============================================================================
CREATE TABLE IF NOT EXISTS watchlists (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    name VARCHAR(100) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_watchlists_user ON watchlists(user_id);

-- ============================================================================
-- TABLA: watchlist_items
-- Descripción: Items en watchlists
-- ============================================================================
CREATE TABLE IF NOT EXISTS watchlist_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    watchlist_id UUID NOT NULL REFERENCES watchlists(id) ON DELETE CASCADE,

    symbol VARCHAR(20) NOT NULL,
    sort_order INT DEFAULT 0,
    notes TEXT,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(watchlist_id, symbol)
);

CREATE INDEX idx_watchlist_items_watchlist ON watchlist_items(watchlist_id);

-- ============================================================================
-- TABLA: paper_trading_accounts
-- Descripción: Cuentas de paper trading (simulación)
-- ============================================================================
CREATE TABLE IF NOT EXISTS paper_trading_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    name VARCHAR(100) DEFAULT 'Paper Account',
    initial_balance DECIMAL(15,2) NOT NULL DEFAULT 100000,
    current_balance DECIMAL(15,2) NOT NULL DEFAULT 100000,
    currency CHAR(3) DEFAULT 'USD',

    -- Métricas
    total_trades INT DEFAULT 0,
    winning_trades INT DEFAULT 0,
    total_pnl DECIMAL(15,2) DEFAULT 0,
    max_drawdown DECIMAL(5,2) DEFAULT 0,

    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_paper_accounts_user ON paper_trading_accounts(user_id);

-- ============================================================================
-- TABLA: paper_trading_positions
-- Descripción: Posiciones de paper trading
-- ============================================================================
CREATE TABLE IF NOT EXISTS paper_trading_positions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    account_id UUID NOT NULL REFERENCES paper_trading_accounts(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    symbol VARCHAR(20) NOT NULL,
    direction public.trade_direction_enum NOT NULL,
    lot_size DECIMAL(10,4) NOT NULL,

    entry_price DECIMAL(20,8) NOT NULL,
    stop_loss DECIMAL(20,8),
    take_profit DECIMAL(20,8),
    exit_price DECIMAL(20,8),

    status position_status_enum DEFAULT 'open',
    opened_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMPTZ,
    close_reason VARCHAR(50),

    realized_pnl DECIMAL(15,2),

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_paper_positions_account ON paper_trading_positions(account_id);
CREATE INDEX idx_paper_positions_user ON paper_trading_positions(user_id);
CREATE INDEX idx_paper_positions_status ON paper_trading_positions(status);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

CREATE TRIGGER update_symbols_updated_at
    BEFORE UPDATE ON symbols
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_strategies_updated_at
    BEFORE UPDATE ON strategies
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_bots_updated_at
    BEFORE UPDATE ON bots
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_signals_updated_at
    BEFORE UPDATE ON signals
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_positions_updated_at
    BEFORE UPDATE ON positions
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();