-- 创建系统能力配置表，用于存储可由前端动态更新的能力定义
CREATE TABLE public.system_capabilities (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  category text NOT NULL,
  type text NOT NULL,
  name text NOT NULL,
  description text NOT NULL,
  params jsonb DEFAULT '[]'::jsonb,
  examples jsonb DEFAULT '[]'::jsonb,
  metadata jsonb DEFAULT '{}'::jsonb,
  is_active boolean DEFAULT true,
  display_order integer DEFAULT 0,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  UNIQUE(category, type)
);

-- 添加注释
COMMENT ON TABLE public.system_capabilities IS '系统能力配置表 - 存储AI助手可执行的操作定义，支持动态更新';
COMMENT ON COLUMN public.system_capabilities.category IS '能力分类: page_action, data_action, query等';
COMMENT ON COLUMN public.system_capabilities.type IS '操作类型标识，如navigate, filter, create_product';
COMMENT ON COLUMN public.system_capabilities.params IS '参数定义JSON数组';
COMMENT ON COLUMN public.system_capabilities.examples IS '使用示例JSON数组';
COMMENT ON COLUMN public.system_capabilities.metadata IS '额外元数据，如页面映射、枚举值等';

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

-- 管理员可以管理，所有人可以读取
CREATE POLICY "Anyone can read system capabilities" 
ON public.system_capabilities 
FOR SELECT 
USING (true);

CREATE POLICY "Admins can manage system capabilities" 
ON public.system_capabilities 
FOR ALL 
USING (public.is_admin(auth.uid()));

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