-- 1. Flash Sales table (秒杀/限时折扣)
CREATE TABLE public.flash_sales (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  product_id UUID NOT NULL REFERENCES public.products(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  original_price NUMERIC NOT NULL,
  flash_price NUMERIC NOT NULL,
  stock_limit INTEGER NOT NULL DEFAULT 100,
  sold_count INTEGER NOT NULL DEFAULT 0,
  per_user_limit INTEGER DEFAULT 1,
  start_time TIMESTAMP WITH TIME ZONE NOT NULL,
  end_time TIMESTAMP WITH TIME ZONE NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft',
  type TEXT NOT NULL DEFAULT 'flash_sale',
  created_by TEXT NOT NULL DEFAULT 'manual',
  ai_strategy TEXT,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 2. Payment Gifts table (支付有礼)
CREATE TABLE public.payment_gifts (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  min_payment_amount NUMERIC NOT NULL DEFAULT 0,
  gift_type TEXT NOT NULL DEFAULT 'coupon',
  gift_value JSONB NOT NULL DEFAULT '{}',
  probability INTEGER NOT NULL DEFAULT 100,
  total_quantity INTEGER,
  used_quantity INTEGER NOT NULL DEFAULT 0,
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft',
  created_by TEXT NOT NULL DEFAULT 'manual',
  ai_strategy TEXT,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 3. Group Buy Activities table (多人拼团)
CREATE TABLE public.group_buy_activities (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  product_id UUID NOT NULL REFERENCES public.products(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  original_price NUMERIC NOT NULL,
  group_price NUMERIC NOT NULL,
  min_group_size INTEGER NOT NULL DEFAULT 2,
  max_group_size INTEGER NOT NULL DEFAULT 10,
  duration_hours INTEGER NOT NULL DEFAULT 24,
  stock_limit INTEGER,
  sold_count INTEGER NOT NULL DEFAULT 0,
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft',
  created_by TEXT NOT NULL DEFAULT 'manual',
  ai_strategy TEXT,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 4. Group Buy Orders table (拼团订单)
CREATE TABLE public.group_buy_orders (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  activity_id UUID NOT NULL REFERENCES public.group_buy_activities(id) ON DELETE CASCADE,
  leader_user_id UUID NOT NULL,
  current_size INTEGER NOT NULL DEFAULT 1,
  status TEXT NOT NULL DEFAULT 'pending',
  expire_at TIMESTAMP WITH TIME ZONE NOT NULL,
  completed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 5. Group Buy Members table (拼团成员)
CREATE TABLE public.group_buy_members (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  group_order_id UUID NOT NULL REFERENCES public.group_buy_orders(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,
  joined_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  order_id UUID REFERENCES public.orders(id)
);

-- Enable RLS
ALTER TABLE public.flash_sales ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.payment_gifts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.group_buy_activities ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.group_buy_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.group_buy_members ENABLE ROW LEVEL SECURITY;

-- RLS Policies for flash_sales
CREATE POLICY "Admins can manage flash sales" ON public.flash_sales
  FOR ALL USING (EXISTS (SELECT 1 FROM profiles WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'));

CREATE POLICY "Active flash sales are viewable by everyone" ON public.flash_sales
  FOR SELECT USING (status = 'active' AND start_time <= now() AND end_time >= now());

-- RLS Policies for payment_gifts
CREATE POLICY "Admins can manage payment gifts" ON public.payment_gifts
  FOR ALL USING (EXISTS (SELECT 1 FROM profiles WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'));

CREATE POLICY "Active payment gifts are viewable by everyone" ON public.payment_gifts
  FOR SELECT USING (status = 'active' AND start_date <= now() AND end_date >= now());

-- RLS Policies for group_buy_activities
CREATE POLICY "Admins can manage group buy activities" ON public.group_buy_activities
  FOR ALL USING (EXISTS (SELECT 1 FROM profiles WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'));

CREATE POLICY "Active group buy activities are viewable by everyone" ON public.group_buy_activities
  FOR SELECT USING (status = 'active' AND start_date <= now() AND end_date >= now());

-- RLS Policies for group_buy_orders
CREATE POLICY "Users can create group orders" ON public.group_buy_orders
  FOR INSERT WITH CHECK (auth.uid() = leader_user_id);

CREATE POLICY "Users can view group orders they participate in" ON public.group_buy_orders
  FOR SELECT USING (
    leader_user_id = auth.uid() OR 
    EXISTS (SELECT 1 FROM group_buy_members WHERE group_buy_members.group_order_id = id AND group_buy_members.user_id = auth.uid())
  );

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

-- RLS Policies for group_buy_members
CREATE POLICY "Users can join groups" ON public.group_buy_members
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can view group members" ON public.group_buy_members
  FOR SELECT USING (
    user_id = auth.uid() OR 
    EXISTS (SELECT 1 FROM group_buy_orders WHERE group_buy_orders.id = group_order_id AND group_buy_orders.leader_user_id = auth.uid())
  );

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

-- Indexes
CREATE INDEX idx_flash_sales_status ON public.flash_sales(status);
CREATE INDEX idx_flash_sales_dates ON public.flash_sales(start_time, end_time);
CREATE INDEX idx_flash_sales_product ON public.flash_sales(product_id);

CREATE INDEX idx_payment_gifts_status ON public.payment_gifts(status);
CREATE INDEX idx_payment_gifts_dates ON public.payment_gifts(start_date, end_date);

CREATE INDEX idx_group_buy_activities_status ON public.group_buy_activities(status);
CREATE INDEX idx_group_buy_activities_dates ON public.group_buy_activities(start_date, end_date);
CREATE INDEX idx_group_buy_activities_product ON public.group_buy_activities(product_id);

CREATE INDEX idx_group_buy_orders_activity ON public.group_buy_orders(activity_id);
CREATE INDEX idx_group_buy_orders_leader ON public.group_buy_orders(leader_user_id);
CREATE INDEX idx_group_buy_orders_status ON public.group_buy_orders(status);

CREATE INDEX idx_group_buy_members_order ON public.group_buy_members(group_order_id);
CREATE INDEX idx_group_buy_members_user ON public.group_buy_members(user_id);