-- ============================================================================
-- Schema: trading
-- Function: calculate_position_pnl
-- Description: Calcula el PnL (realized y unrealized) de una posición
-- Parameters:
--   p_position_id: ID de la posición
--   p_current_price: Precio actual del mercado (para unrealized PnL)
-- Returns: JSONB con realized_pnl, unrealized_pnl, pnl_percentage
-- ============================================================================

CREATE OR REPLACE FUNCTION trading.calculate_position_pnl(
  p_position_id UUID,
  p_current_price DECIMAL(20,8) DEFAULT NULL
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
  v_position RECORD;
  v_realized_pnl DECIMAL(20,8) := 0;
  v_unrealized_pnl DECIMAL(20,8) := 0;
  v_pnl_percentage DECIMAL(10,4) := 0;
  v_total_commission DECIMAL(20,8) := 0;
  v_current_value DECIMAL(20,8);
BEGIN
  -- Obtener datos de la posición
  SELECT
    p.position_side,
    p.status,
    p.entry_price,
    p.entry_quantity,
    p.entry_value,
    p.entry_commission,
    p.exit_price,
    p.exit_quantity,
    p.exit_value,
    p.exit_commission
  INTO v_position
  FROM trading.positions p
  WHERE p.id = p_position_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Position % not found', p_position_id;
  END IF;

  -- Calcular comisiones totales
  v_total_commission := COALESCE(v_position.entry_commission, 0) + COALESCE(v_position.exit_commission, 0);

  -- Calcular PnL según el estado
  IF v_position.status = 'closed' OR v_position.status = 'liquidated' THEN
    -- Posición cerrada: calcular realized PnL
    IF v_position.position_side = 'buy' THEN
      -- Long position: profit = (exit_price - entry_price) * quantity - commissions
      v_realized_pnl := (v_position.exit_value - v_position.entry_value) - v_total_commission;
    ELSE
      -- Short position: profit = (entry_price - exit_price) * quantity - commissions
      v_realized_pnl := (v_position.entry_value - v_position.exit_value) - v_total_commission;
    END IF;

    -- Calcular porcentaje
    IF v_position.entry_value > 0 THEN
      v_pnl_percentage := (v_realized_pnl / v_position.entry_value) * 100;
    END IF;

  ELSIF v_position.status = 'open' AND p_current_price IS NOT NULL THEN
    -- Posición abierta: calcular unrealized PnL
    v_current_value := p_current_price * v_position.entry_quantity;

    IF v_position.position_side = 'buy' THEN
      -- Long position
      v_unrealized_pnl := (v_current_value - v_position.entry_value) - v_position.entry_commission;
    ELSE
      -- Short position
      v_unrealized_pnl := (v_position.entry_value - v_current_value) - v_position.entry_commission;
    END IF;

    -- Calcular porcentaje
    IF v_position.entry_value > 0 THEN
      v_pnl_percentage := (v_unrealized_pnl / v_position.entry_value) * 100;
    END IF;
  END IF;

  -- Retornar resultado
  RETURN jsonb_build_object(
    'position_id', p_position_id,
    'status', v_position.status,
    'realized_pnl', v_realized_pnl,
    'unrealized_pnl', v_unrealized_pnl,
    'pnl_percentage', v_pnl_percentage,
    'total_commission', v_total_commission
  );
END;
$$;

-- Comentarios
COMMENT ON FUNCTION trading.calculate_position_pnl IS 'Calcula el PnL realizado y no realizado de una posición';