-- 创建营销活动主表
CREATE TABLE public.marketing_campaigns (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  
  -- 活动类型：coupon(优惠券), flash_sale(限时折扣), member_day(会员日), promotion(促销活动)
  type TEXT NOT NULL DEFAULT 'promotion',
  
  -- 状态：draft(草稿), pending_review(待审核), active(进行中), paused(已暂停), completed(已完成)
  status TEXT NOT NULL DEFAULT 'draft',
  
  -- 时间范围
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  
  -- 创建者信息
  created_by TEXT NOT NULL DEFAULT 'manual', -- 'ai' 或 'manual'
  created_by_user_id UUID,
  ai_strategy TEXT, -- AI创建时的策略说明
  
  -- 关联优惠券（如果是优惠券活动）
  related_coupon_id UUID REFERENCES public.coupons(id) ON DELETE SET NULL,
  
  -- 审批信息
  reviewed_by UUID,
  reviewed_at TIMESTAMP WITH TIME ZONE,
  review_notes TEXT,
  
  -- 活动配置（JSON格式，存储不同类型活动的具体参数）
  config JSONB DEFAULT '{}'::jsonb,
  
  -- 时间戳
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

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

-- 管理员可以管理所有活动
CREATE POLICY "Admins can manage marketing campaigns"
ON public.marketing_campaigns
FOR ALL
USING (EXISTS (
  SELECT 1 FROM profiles
  WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
));

-- 所有人可以查看进行中的活动
CREATE POLICY "Active campaigns are viewable by everyone"
ON public.marketing_campaigns
FOR SELECT
USING (status = 'active' AND start_date <= now() AND end_date >= now());

-- 更新时间戳触发器
CREATE TRIGGER update_marketing_campaigns_updated_at
BEFORE UPDATE ON public.marketing_campaigns
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at_column();

-- 添加索引优化查询
CREATE INDEX idx_marketing_campaigns_status ON public.marketing_campaigns(status);
CREATE INDEX idx_marketing_campaigns_dates ON public.marketing_campaigns(start_date, end_date);
CREATE INDEX idx_marketing_campaigns_type ON public.marketing_campaigns(type);
CREATE INDEX idx_marketing_campaigns_created_by ON public.marketing_campaigns(created_by);