-- 创建跨店联合促销活动表
CREATE TABLE public.cross_store_promotions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  -- 活动类型：joint_discount(联合折扣), joint_coupon(联合优惠券), joint_gift(联合赠品)
  type TEXT NOT NULL DEFAULT 'joint_discount',
  -- 状态：draft(草稿), pending(待审核), active(进行中), paused(已暂停), ended(已结束)
  status TEXT NOT NULL DEFAULT 'draft',
  -- 参与店铺ID列表
  store_ids UUID[] NOT NULL DEFAULT ARRAY[]::UUID[],
  -- 活动配置（折扣规则、优惠券信息等）
  config JSONB NOT NULL DEFAULT '{}'::JSONB,
  -- 活动时间
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  -- 预算和限制
  total_budget NUMERIC DEFAULT 0,
  used_budget NUMERIC DEFAULT 0,
  max_participants INTEGER,
  current_participants INTEGER DEFAULT 0,
  -- AI策略建议
  ai_strategy TEXT,
  -- 创建者信息
  created_by TEXT NOT NULL DEFAULT 'manual',
  created_by_user_id UUID,
  -- 审核信息
  reviewed_by UUID,
  reviewed_at TIMESTAMP WITH TIME ZONE,
  review_notes TEXT,
  -- 时间戳
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 创建跨店促销参与店铺明细表
CREATE TABLE public.cross_store_promotion_stores (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  promotion_id UUID NOT NULL REFERENCES public.cross_store_promotions(id) ON DELETE CASCADE,
  store_name TEXT NOT NULL,
  store_type TEXT, -- 店铺类型
  contribution_ratio NUMERIC DEFAULT 0, -- 费用分摊比例
  sales_count INTEGER DEFAULT 0, -- 销售数量
  sales_amount NUMERIC DEFAULT 0, -- 销售金额
  status TEXT DEFAULT 'active', -- 参与状态
  joined_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 启用RLS
ALTER TABLE public.cross_store_promotions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.cross_store_promotion_stores ENABLE ROW LEVEL SECURITY;

-- 创建RLS策略：管理员可以管理所有跨店促销
CREATE POLICY "Admins can manage cross store promotions"
ON public.cross_store_promotions
FOR ALL
USING (EXISTS (
  SELECT 1 FROM profiles
  WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
));

-- 活跃的跨店促销对所有人可见
CREATE POLICY "Active cross store promotions are viewable by everyone"
ON public.cross_store_promotions
FOR SELECT
USING (status = 'active' AND start_date <= now() AND end_date >= now());

-- 管理员可以管理参与店铺
CREATE POLICY "Admins can manage cross store promotion stores"
ON public.cross_store_promotion_stores
FOR ALL
USING (EXISTS (
  SELECT 1 FROM profiles
  WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
));

-- 参与店铺信息对所有人可见
CREATE POLICY "Cross store promotion stores are viewable by everyone"
ON public.cross_store_promotion_stores
FOR SELECT
USING (EXISTS (
  SELECT 1 FROM cross_store_promotions
  WHERE cross_store_promotions.id = promotion_id
  AND cross_store_promotions.status = 'active'
));

-- 创建更新时间触发器
CREATE TRIGGER update_cross_store_promotions_updated_at
BEFORE UPDATE ON public.cross_store_promotions
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at_column();