-- 修复check_user_coupon_limit函数的search_path
CREATE OR REPLACE FUNCTION public.check_user_coupon_limit()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_coupon RECORD;
  v_user_coupon_count INTEGER;
BEGIN
  -- 获取优惠券信息
  SELECT * INTO v_coupon FROM public.coupons WHERE id = NEW.coupon_id;
  
  -- 检查优惠券是否存在
  IF NOT FOUND THEN
    RAISE EXCEPTION '优惠券不存在';
  END IF;
  
  -- 检查优惠券是否已满
  IF v_coupon.used_quantity >= v_coupon.total_quantity THEN
    RAISE EXCEPTION '优惠券已领完';
  END IF;
  
  -- 检查用户已领取数量
  SELECT COUNT(*) INTO v_user_coupon_count
  FROM public.user_coupons
  WHERE user_id = NEW.user_id AND coupon_id = NEW.coupon_id;
  
  IF v_user_coupon_count >= v_coupon.per_user_limit THEN
    RAISE EXCEPTION '已达到领取上限';
  END IF;
  
  -- 更新已使用数量
  UPDATE public.coupons
  SET used_quantity = used_quantity + 1
  WHERE id = NEW.coupon_id;
  
  RETURN NEW;
END;
$$;