-- 创建折扣券表
CREATE TABLE IF NOT EXISTS public.coupons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  code TEXT UNIQUE NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('amount', 'percentage')),
  discount_value NUMERIC NOT NULL CHECK (discount_value > 0),
  min_purchase_amount NUMERIC DEFAULT 0,
  max_discount_amount NUMERIC,
  total_quantity INTEGER NOT NULL CHECK (total_quantity > 0),
  used_quantity INTEGER DEFAULT 0 CHECK (used_quantity >= 0),
  per_user_limit INTEGER DEFAULT 1,
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'expired')),
  scope TEXT NOT NULL DEFAULT 'all' CHECK (scope IN ('all', 'products', 'categories')),
  scope_ids TEXT[],
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- 创建用户领取券记录表
CREATE TABLE IF NOT EXISTS public.user_coupons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  coupon_id UUID NOT NULL REFERENCES public.coupons(id) ON DELETE CASCADE,
  order_id UUID REFERENCES public.orders(id) ON DELETE SET NULL,
  status TEXT NOT NULL DEFAULT 'unused' CHECK (status IN ('unused', 'used', 'expired')),
  received_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  used_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  UNIQUE(user_id, coupon_id, order_id)
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_coupons_code ON public.coupons(code);
CREATE INDEX IF NOT EXISTS idx_coupons_status ON public.coupons(status);
CREATE INDEX IF NOT EXISTS idx_coupons_dates ON public.coupons(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_user_coupons_user_id ON public.user_coupons(user_id);
CREATE INDEX IF NOT EXISTS idx_user_coupons_coupon_id ON public.user_coupons(coupon_id);
CREATE INDEX IF NOT EXISTS idx_user_coupons_status ON public.user_coupons(status);

-- 启用RLS
ALTER TABLE public.coupons ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_coupons ENABLE ROW LEVEL SECURITY;

-- 创建RLS策略 - 优惠券
CREATE POLICY "Coupons are viewable by everyone"
  ON public.coupons FOR SELECT
  USING (status = 'active' AND start_date <= now() AND end_date >= now());

CREATE POLICY "Admins can manage all coupons"
  ON public.coupons FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
    )
  );

-- 创建RLS策略 - 用户优惠券
CREATE POLICY "Users can view their own coupons"
  ON public.user_coupons FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can receive coupons"
  ON public.user_coupons FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can use their own coupons"
  ON public.user_coupons FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Admins can view all user coupons"
  ON public.user_coupons FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
    )
  );

-- 创建触发器更新 updated_at
CREATE TRIGGER update_coupons_updated_at
  BEFORE UPDATE ON public.coupons
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

-- 创建函数检查用户领券限制
CREATE OR REPLACE FUNCTION public.check_user_coupon_limit()
RETURNS TRIGGER 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;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 创建领券触发器
CREATE TRIGGER check_coupon_limit_before_insert
  BEFORE INSERT ON public.user_coupons
  FOR EACH ROW
  EXECUTE FUNCTION public.check_user_coupon_limit();