-- Create categories table
CREATE TABLE IF NOT EXISTS public.categories (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  display_order INTEGER DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Enable RLS
ALTER TABLE public.categories ENABLE ROW LEVEL SECURITY;

-- Categories are viewable by everyone
CREATE POLICY "Categories are viewable by everyone"
  ON public.categories
  FOR SELECT
  USING (true);

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

-- Add trigger for updated_at
CREATE TRIGGER update_categories_updated_at
  BEFORE UPDATE ON public.categories
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

-- Insert default categories
INSERT INTO public.categories (name, description, display_order) VALUES
  ('电子产品', '电脑、手机、数码配件等', 1),
  ('家居生活', '家具、家居用品、装饰品等', 2),
  ('服装鞋包', '服装、鞋类、箱包配饰等', 3),
  ('食品饮料', '食品、饮料、零食等', 4),
  ('图书文娱', '图书、文具、乐器等', 5),
  ('运动户外', '运动器材、户外用品等', 6),
  ('美妆护肤', '化妆品、护肤品、个护用品等', 7),
  ('母婴玩具', '母婴用品、玩具、童装等', 8)
ON CONFLICT (name) DO NOTHING;