-- ============================================================================
-- Schema: trading
-- Table: trades
-- Description: Historial detallado de trades ejecutados
-- Dependencies: trading.orders, trading.positions
-- ============================================================================

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

  -- Referencias
  order_id UUID NOT NULL REFERENCES trading.orders(id) ON DELETE CASCADE,
  position_id UUID REFERENCES trading.positions(id) ON DELETE SET NULL,

  -- Identificador externo (de exchange)
  external_trade_id VARCHAR(100),

  -- Detalles del trade
  symbol VARCHAR(20) NOT NULL,
  side trading.order_side NOT NULL,
  price DECIMAL(20,8) NOT NULL,
  quantity DECIMAL(20,8) NOT NULL,
  quote_quantity DECIMAL(20,8) NOT NULL, -- price * quantity

  -- Comisión
  commission DECIMAL(20,8) DEFAULT 0,
  commission_asset VARCHAR(10),

  -- Metadata
  is_maker BOOLEAN DEFAULT false,
  metadata JSONB DEFAULT '{}',

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

-- Índices
CREATE INDEX idx_trades_order ON trading.trades(order_id);
CREATE INDEX idx_trades_position ON trading.trades(position_id);
CREATE INDEX idx_trades_symbol ON trading.trades(symbol);
CREATE INDEX idx_trades_executed ON trading.trades(executed_at DESC);
CREATE INDEX idx_trades_external ON trading.trades(external_trade_id);

-- Comentarios
COMMENT ON TABLE trading.trades IS 'Historial detallado de trades ejecutados (fills individuales)';
COMMENT ON COLUMN trading.trades.external_trade_id IS 'ID del trade en el exchange externo';
COMMENT ON COLUMN trading.trades.quote_quantity IS 'Valor total del trade (price * quantity)';
COMMENT ON COLUMN trading.trades.is_maker IS 'true si el trade fue maker, false si fue taker';