-- 创建 API 注册表
CREATE TABLE public.api_registry (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL, -- admin, product, marketing, finance, service, genui, insights
  endpoint TEXT NOT NULL,
  method TEXT NOT NULL DEFAULT 'POST',
  description TEXT NOT NULL,
  parameters JSONB DEFAULT '[]'::jsonb,
  response_schema JSONB DEFAULT '{}'::jsonb,
  auth_required BOOLEAN DEFAULT true,
  examples JSONB DEFAULT '[]'::jsonb,
  tags TEXT[] DEFAULT '{}',
  is_active BOOLEAN DEFAULT true,
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 创建 Agent 角色注册表
CREATE TABLE public.agent_roles (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  role_id TEXT NOT NULL UNIQUE, -- marketing, product, finance, service, manager
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  capabilities TEXT[] DEFAULT '{}',
  allowed_actions TEXT[] DEFAULT '{}',
  icon TEXT,
  color TEXT,
  is_active BOOLEAN DEFAULT true,
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 创建 GenUI 组件注册表
CREATE TABLE public.genui_components (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  component_type TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  props_schema JSONB NOT NULL DEFAULT '{}'::jsonb,
  example JSONB,
  category TEXT DEFAULT 'display', -- display, action, data, chart
  is_active BOOLEAN DEFAULT true,
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- 启用 RLS
ALTER TABLE public.api_registry ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.genui_components ENABLE ROW LEVEL SECURITY;

-- 公开读取策略（供 Agent 查询）
CREATE POLICY "Anyone can read api_registry" ON public.api_registry FOR SELECT USING (true);
CREATE POLICY "Anyone can read agent_roles" ON public.agent_roles FOR SELECT USING (true);
CREATE POLICY "Anyone can read genui_components" ON public.genui_components FOR SELECT USING (true);

-- 管理员写入策略
CREATE POLICY "Admins can manage api_registry" ON public.api_registry FOR ALL USING (
  EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = auth.uid() AND role IN ('admin', 'super_admin'))
);
CREATE POLICY "Admins can manage agent_roles" ON public.agent_roles FOR ALL USING (
  EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = auth.uid() AND role IN ('admin', 'super_admin'))
);
CREATE POLICY "Admins can manage genui_components" ON public.genui_components FOR ALL USING (
  EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = auth.uid() AND role IN ('admin', 'super_admin'))
);

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

CREATE TRIGGER update_agent_roles_updated_at
  BEFORE UPDATE ON public.agent_roles
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_genui_components_updated_at
  BEFORE UPDATE ON public.genui_components
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

-- 插入初始数据：Agent 角色
INSERT INTO public.agent_roles (role_id, name, description, capabilities, allowed_actions, icon, color, display_order) VALUES
('marketing', '营销专员', '负责营销活动策划、优惠券管理、促销活动', 
  ARRAY['创建优惠券', '管理限时抢购', '设置拼团活动', '跨店联盟'], 
  ARRAY['marketing-operations', 'coupon-management', 'marketing-genui'],
  'Megaphone', 'orange', 1),
('product', '商品专员', '负责商品管理、库存监控、定价策略',
  ARRAY['商品上下架', '库存管理', '价格调整', '商品推荐'],
  ARRAY['product-operations', 'product-genui'],
  'Package', 'blue', 2),
('finance', '财务专员', '负责财务分析、交易审核、会员管理',
  ARRAY['交易查询', '退款审批', '财务报表', '会员充值'],
  ARRAY['admin-operations', 'finance-genui'],
  'DollarSign', 'green', 3),
('service', '售后专员', '负责售后处理、客诉跟进、退换货管理',
  ARRAY['售后审批', '退款处理', '换货安排', '客诉跟进'],
  ARRAY['service-operations', 'service-query', 'service-genui'],
  'HeadphonesIcon', 'purple', 4),
('manager', '运营总监', '统筹管理所有业务，制定目标，跨部门协调',
  ARRAY['目标设定', '团队管理', '数据分析', '跨部门协调'],
  ARRAY['admin-operations', 'manager-genui', 'system-info'],
  'Crown', 'gold', 0);

-- 插入初始数据：核心 API
INSERT INTO public.api_registry (name, category, endpoint, method, description, parameters, auth_required, display_order) VALUES
('admin-operations', 'admin', '/functions/v1/admin-operations', 'POST', '管理后台通用操作接口', 
  '[{"name": "action", "type": "string", "required": true}, {"name": "params", "type": "object"}]'::jsonb, true, 1),
('product-operations', 'product', '/functions/v1/product-operations', 'POST', '商品管理操作',
  '[{"name": "action", "type": "string", "required": true}, {"name": "productId", "type": "string"}, {"name": "data", "type": "object"}]'::jsonb, true, 2),
('marketing-operations', 'marketing', '/functions/v1/marketing-operations', 'POST', '营销活动操作',
  '[{"name": "action", "type": "string", "required": true}, {"name": "activityType", "type": "string"}, {"name": "data", "type": "object"}]'::jsonb, true, 3),
('service-operations', 'service', '/functions/v1/service-operations', 'POST', '售后服务操作',
  '[{"name": "action", "type": "string", "required": true}, {"name": "requestId", "type": "string"}, {"name": "data", "type": "object"}]'::jsonb, true, 4),
('coupon-management', 'marketing', '/functions/v1/coupon-management', 'POST', '优惠券管理',
  '[{"name": "action", "type": "string", "required": true}, {"name": "couponId", "type": "string"}, {"name": "data", "type": "object"}]'::jsonb, true, 5),
('system-info', 'admin', '/functions/v1/system-info', 'POST', '系统信息查询',
  '[{"name": "query", "type": "string", "required": true}]'::jsonb, true, 6),
('api-registry', 'admin', '/functions/v1/api-registry', 'GET', 'API注册表查询（本接口）',
  '[{"name": "format", "type": "string"}, {"name": "category", "type": "string"}]'::jsonb, false, 0);

-- 插入初始数据：GenUI 组件
INSERT INTO public.genui_components (component_type, name, description, props_schema, category, display_order) VALUES
('stats_row', '统计行', '展示关键指标统计', '{"stats": {"type": "array", "items": {"label": "string", "value": "string|number", "trend": "string"}}}'::jsonb, 'data', 1),
('data_table', '数据表格', '展示表格数据', '{"columns": "array", "data": "array", "pagination": "object"}'::jsonb, 'data', 2),
('chart', '图表', '数据可视化图表', '{"chartType": "line|bar|pie", "data": "array", "config": "object"}'::jsonb, 'chart', 3),
('insight_card', '洞察卡片', '展示AI分析洞察', '{"title": "string", "content": "string", "type": "info|warning|success", "actions": "array"}'::jsonb, 'display', 4),
('approval_card', '审批卡片', '待审批事项展示', '{"title": "string", "description": "string", "actions": "array", "metadata": "object"}'::jsonb, 'action', 5),
('action_buttons', '操作按钮组', '可执行的操作按钮', '{"buttons": {"type": "array", "items": {"label": "string", "action": "string", "variant": "string"}}}'::jsonb, 'action', 6),
('progress_bar', '进度条', '目标进度展示', '{"label": "string", "current": "number", "target": "number", "unit": "string"}'::jsonb, 'display', 7),
('text_block', '文本块', '富文本内容展示', '{"content": "string", "format": "markdown|plain"}'::jsonb, 'display', 8),
('alert_box', '提示框', '警告或提示信息', '{"type": "info|warning|error|success", "title": "string", "message": "string"}'::jsonb, 'display', 9);