-- ============================================================================
-- Schema: trading
-- Table: orders
-- Description: Órdenes de trading (pendientes, ejecutadas, canceladas)
-- Dependencies: auth.users, trading.bots, trading.symbols
-- ============================================================================

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

  -- Referencias
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  bot_id UUID REFERENCES trading.bots(id) ON DELETE SET NULL,
  symbol_id UUID NOT NULL REFERENCES trading.symbols(id),

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

  -- Tipo y lado
  order_type trading.order_type NOT NULL,
  order_side trading.order_side NOT NULL,
  status trading.order_status NOT NULL DEFAULT 'pending',

  -- Precios
  price DECIMAL(20,8),              -- Precio límite (NULL para market orders)
  stop_price DECIMAL(20,8),         -- Precio stop
  average_fill_price DECIMAL(20,8), -- Precio promedio de ejecución

  -- Cantidades
  quantity DECIMAL(20,8) NOT NULL,
  filled_quantity DECIMAL(20,8) DEFAULT 0,
  remaining_quantity DECIMAL(20,8) NOT NULL,

  -- Costos
  commission DECIMAL(20,8) DEFAULT 0,
  commission_asset VARCHAR(10),

  -- Time in force
  time_in_force VARCHAR(20) DEFAULT 'GTC', -- GTC, IOC, FOK

  -- Validez
  expires_at TIMESTAMPTZ,

  -- Metadata
  metadata JSONB DEFAULT '{}',
  error_message TEXT,

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  filled_at TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ
);

-- Índices
CREATE INDEX idx_orders_user ON trading.orders(user_id);
CREATE INDEX idx_orders_bot ON trading.orders(bot_id);
CREATE INDEX idx_orders_symbol ON trading.orders(symbol_id);
CREATE INDEX idx_orders_status ON trading.orders(status);
CREATE INDEX idx_orders_created ON trading.orders(created_at DESC);
CREATE INDEX idx_orders_external ON trading.orders(external_order_id);

-- Comentarios
COMMENT ON TABLE trading.orders IS 'Órdenes de trading (todas las órdenes del sistema)';
COMMENT ON COLUMN trading.orders.external_order_id IS 'ID de la orden en el exchange externo';
COMMENT ON COLUMN trading.orders.time_in_force IS 'GTC (Good Till Cancel), IOC (Immediate or Cancel), FOK (Fill or Kill)';
COMMENT ON COLUMN trading.orders.average_fill_price IS 'Precio promedio de ejecución para órdenes parcialmente completadas';