-- ============================================================================
-- Schema: trading
-- Function: update_bot_stats
-- Description: Actualiza las estadísticas de un bot basándose en sus posiciones
-- Parameters:
--   p_bot_id: ID del bot
-- Returns: JSONB con las estadísticas actualizadas
-- ============================================================================

CREATE OR REPLACE FUNCTION trading.update_bot_stats(
  p_bot_id UUID
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
  v_total_trades INTEGER := 0;
  v_winning_trades INTEGER := 0;
  v_losing_trades INTEGER := 0;
  v_total_pnl DECIMAL(20,8) := 0;
  v_win_rate DECIMAL(5,2) := 0;
  v_current_capital DECIMAL(20,8);
  v_initial_capital DECIMAL(20,8);
BEGIN
  -- Obtener capital inicial
  SELECT initial_capital INTO v_initial_capital
  FROM trading.bots
  WHERE id = p_bot_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Bot % not found', p_bot_id;
  END IF;

  -- Contar trades cerrados
  SELECT
    COUNT(*)::INTEGER,
    COUNT(*) FILTER (WHERE realized_pnl > 0)::INTEGER,
    COUNT(*) FILTER (WHERE realized_pnl < 0)::INTEGER,
    COALESCE(SUM(realized_pnl), 0)
  INTO
    v_total_trades,
    v_winning_trades,
    v_losing_trades,
    v_total_pnl
  FROM trading.positions
  WHERE bot_id = p_bot_id
    AND status IN ('closed', 'liquidated');

  -- Calcular win rate
  IF v_total_trades > 0 THEN
    v_win_rate := (v_winning_trades::DECIMAL / v_total_trades::DECIMAL) * 100;
  END IF;

  -- Calcular capital actual
  v_current_capital := v_initial_capital + v_total_pnl;

  -- Actualizar bot
  UPDATE trading.bots
  SET
    total_trades = v_total_trades,
    winning_trades = v_winning_trades,
    total_profit_loss = v_total_pnl,
    win_rate = v_win_rate,
    current_capital = v_current_capital,
    updated_at = NOW()
  WHERE id = p_bot_id;

  -- Retornar estadísticas
  RETURN jsonb_build_object(
    'bot_id', p_bot_id,
    'total_trades', v_total_trades,
    'winning_trades', v_winning_trades,
    'losing_trades', v_losing_trades,
    'win_rate', v_win_rate,
    'total_profit_loss', v_total_pnl,
    'initial_capital', v_initial_capital,
    'current_capital', v_current_capital,
    'roi_percentage', CASE
      WHEN v_initial_capital > 0
      THEN ((v_current_capital - v_initial_capital) / v_initial_capital * 100)
      ELSE 0
    END
  );
END;
$$;

-- Comentarios
COMMENT ON FUNCTION trading.update_bot_stats IS 'Actualiza las estadísticas agregadas de un trading bot';