tihao-ai/claude-code-tihao-sourcing/mcp/src/features/tihao-sourcing/pgy-schema.js

// 蒲公英博主字段 schema（结构化自《field_dictionary.md》）
// 边界：仅沉淀字段定义 + 建表参考 + 选号查询映射，供未来建库/开发查询接口时直接消费，不在此建库。
// 源码映射：hc_pgy_spider/pgy_field_map.py；采集接口共 9 个详情 + 1 个列表。

// 采集接口（endpointId 供未来查询接口注册使用）
const SOURCES = {
  list: { name: '列表 blogger/v2', method: 'POST', path: 'xiaohongshu-pgy/api/solar/cooperator/blogger/v2', priority: 3 },
  profile: { name: '个人简介', method: 'GET', path: 'cooperator/user/blogger/{id}', priority: 2 },
  notesRate: { name: '数据概括 notesRate?noteType=3', method: 'GET', path: 'kol/dataV3/notesRate', priority: 2 },
  noteData: { name: '笔记数据 notesRate×3', method: 'GET', path: 'kol/dataV3/notesRate', priority: 1 },
  notesDetail: { name: '笔记案例 notesDetail×3', method: 'GET', path: 'kol/dataV2/notesDetail', priority: 1 },
  fansSummary: { name: '粉丝概览 fansSummary', method: 'GET', path: 'kol/dataV3/fansSummary', priority: 2 },
  fansProfile: { name: '粉丝画像 fans_profile', method: 'GET', path: 'kol/data/{id}/fans_profile', priority: 1 },
  dataSummary: { name: '数据表现总览 data_summary', method: 'GET', path: 'kol/data_v3/data_summary', priority: 1 },
  costEffective: { name: '流量性价比 costEffective', method: 'GET', path: 'kol/dataV2/costEffective', priority: 0 }
};

// 选号查询维度 → 蒲公英字段 + MongoDB 查询示例
// 这是未来「选号时开发接口查询」的核心映射：rank/platform-quality-rules 的每个维度对应哪些字段、怎么查。
const DIMENSIONS = {
  fanRange: {
    zh: '粉丝范围',
    fields: ['follower_count'],
    type: 'number',
    unit: '人',
    query: { follower_count: { $gte: 50000, $lte: 500000 } },
    note: '腰部 KOL 5万-50万；对应 ranker.fanRange 与 platform-quality-rules 分级'
  },
  interaction: {
    zh: '互动率/互动中位数',
    fields: ['interaction_rate', 'interact_midnum', 'inter_midnum_30d_total'],
    type: 'number',
    unit: '% / 次',
    query: { interaction_rate: { $gte: 3 } },
    note: '小红书互动率 ≥3% 优秀、≥1% 合格；抖音 ≥3% 优秀、≥1.5% 合格'
  },
  price: {
    zh: '报价（必采）',
    fields: ['picture_price', 'video_price', 'lower_price'],
    type: 'number',
    unit: '元',
    query: { lower_price: { $lte: 3000 } },
    note: '⭐ 列表接口只采这 3 个报价字段'
  },
  verticality: {
    zh: '垂直度/类目',
    fields: ['trade_type', 'content_tags', 'feature_tags', 'note_type_share'],
    type: 'string/array',
    query: { trade_type: '美妆护肤', content_tags: { $all: ['成分党'] } },
    note: '小红书垂直度 ≥70% 合格、≥90% 优秀'
  },
  score: {
    zh: '评分/信用/等级',
    fields: ['credit_level', 'goodness_level', 'level_name'],
    type: 'string',
    query: { credit_level: { $in: ['Lv5', 'Lv6', 'Lv7'] } },
    note: '蒲公英评分 ≥4.0 合格、≥4.5 优秀、<3.5 暂不考虑'
  },
  location: {
    zh: '所在地/城市',
    fields: ['location'],
    type: 'string',
    query: { location: { $regex: '上海' } },
    note: '地域定向'
  },
  gender: {
    zh: '性别',
    fields: ['gender'],
    type: 'int',
    query: { gender: 1 },
    note: '1=女 2=男'
  },
  fansProfile: {
    zh: '粉丝画像（年龄/性别/城市）',
    fields: ['fans_age_dist', 'fans_gender_dist', 'fans_city_top'],
    type: 'array',
    query: { 'fans_age_dist': { $elemMatch: { age: '18-24', rate: { $gte: 40 } } } },
    note: '粉丝画像匹配度 ≥60% 合格、≥80% 优秀'
  },
  completionRate: {
    zh: '视频完播率',
    fields: ['video_full_view_rate', 'video_finish_rate'],
    type: 'number',
    unit: '%',
    query: { video_full_view_rate: { $gte: 20 } },
    note: '抖音完播率 ≥20% 合格、≥40% 优秀、<10% 危险'
  },
  likeCollectRatio: {
    zh: '赞藏比（收藏/点赞）',
    fields: ['like_median', 'collect_median'],
    type: 'number',
    query: { $expr: { $gte: [{ $divide: ['$collect_median', '$like_median'] }, 0.5] } },
    note: '小红书赞藏比 ≥0.5 合格、≥1.0 优秀'
  },
  cpm: {
    zh: 'CPM/CPE/阅读成本',
    fields: ['estimate_picture_cpm', 'estimate_video_cpm', 'estimate_picture_engage_cost', 'estimate_video_engage_cost'],
    type: 'number',
    unit: '元',
    query: { estimate_video_cpm: { $lte: 80 } },
    note: '抖音腰部 CPM 40-80、CPE 5-10'
  },
  growth: {
    zh: '粉丝增长',
    fields: ['fans_growth_rate', 'fans_30d_growth_rate', 'fans_30d_growth_num'],
    type: 'number',
    unit: '%',
    query: { fans_30d_growth_rate: { $gte: 0 } },
    note: '负增长 = 账号健康度风险（platform-quality-rules 已接入）'
  },
  activity: {
    zh: '活跃度',
    fields: ['active_days_in_last7', 'is_active', 'last_active_time'],
    type: 'mixed',
    query: { is_active: true, active_days_in_last7: { $gte: 3 } },
    note: '近30天无更新 = 数据真实性风险'
  },
  verified: {
    zh: '认证/合作权限',
    fields: ['verified', 'is_pgy_creator', 'has_buyer_auth', 'cooperate_state'],
    type: 'bool',
    query: { is_pgy_creator: true, has_buyer_auth: true },
    note: '蒲公英创作者 + 品牌合作权限是下单前提'
  }
};

// 核心字段表（字段名 → 中文/类型/来源接口/选号维度）
const CORE_FIELDS = {
  // 顶级必采
  blogger_id: { zh: '博主ID', type: 'string', sources: ['list', 'profile'], dimension: 'id', primary: true },
  nickname: { zh: '昵称', type: 'string', sources: ['list', 'profile'], dimension: 'id' },
  follower_count: { zh: '粉丝数', type: 'int', sources: ['list', 'profile'], dimension: 'fanRange' },
  like_count: { zh: '获赞数', type: 'int', sources: ['list', 'profile'], dimension: 'interaction' },
  post_count: { zh: '笔记数', type: 'int', sources: ['list'], dimension: 'activity' },
  co_note_count: { zh: '合作笔记数', type: 'int', sources: ['list'], dimension: 'activity' },
  verified: { zh: '是否认证', type: 'bool', sources: ['list', 'profile'], dimension: 'verified' },
  tags: { zh: '个人标签', type: 'array', sources: ['list', 'profile'], dimension: 'verticality' },
  content_tags: { zh: '内容标签', type: 'array', sources: ['list'], dimension: 'verticality' },
  feature_tags: { zh: '特色标签', type: 'array', sources: ['list'], dimension: 'verticality' },
  trade_type: { zh: '行业', type: 'string', sources: ['list'], dimension: 'verticality' },
  gender: { zh: '性别', type: 'int', sources: ['list', 'profile'], dimension: 'gender' },
  location: { zh: '所在地', type: 'string', sources: ['list', 'profile'], dimension: 'location' },
  // 报价（必采）
  picture_price: { zh: '图文报价', type: 'number', sources: ['list'], dimension: 'price', priority: 3 },
  video_price: { zh: '视频报价', type: 'number', sources: ['list'], dimension: 'price', priority: 3 },
  lower_price: { zh: '报价下限', type: 'number', sources: ['list'], dimension: 'price', priority: 3 },
  // 互动/数据
  interaction_rate: { zh: '互动率', type: 'number', sources: ['notesRate'], dimension: 'interaction', priority: 2 },
  interact_midnum: { zh: '互动中位数', type: 'number', sources: ['list'], dimension: 'interaction' },
  read_midnum: { zh: '阅读中位数', type: 'number', sources: ['list'], dimension: 'cpm' },
  like_median: { zh: '中位点赞量', type: 'number', sources: ['notesRate'], dimension: 'likeCollectRatio' },
  collect_median: { zh: '中位收藏量', type: 'number', sources: ['notesRate'], dimension: 'likeCollectRatio' },
  video_full_view_rate: { zh: '视频完播率', type: 'number', sources: ['notesRate'], dimension: 'completionRate' },
  hundred_like_rate: { zh: '百赞笔记比例', type: 'number', sources: ['notesRate'], dimension: 'interaction' },
  kilo_like_rate: { zh: '千赞笔记比例', type: 'number', sources: ['notesRate'], dimension: 'interaction' },
  // 粉丝增长/画像
  fans_growth_rate: { zh: '30天增粉率', type: 'number', sources: ['fansSummary'], dimension: 'growth' },
  fans_30d_growth_rate: { zh: '30天粉丝增长率', type: 'number', sources: ['list', 'dataSummary'], dimension: 'growth' },
  fans_active_l28_rate: { zh: '28天活跃粉丝占比', type: 'number', sources: ['list'], dimension: 'activity' },
  fans_engage_30d_rate: { zh: '30天互动率', type: 'number', sources: ['list'], dimension: 'interaction' },
  fans_age_dist: { zh: '粉丝年龄分布', type: 'array', sources: ['fansProfile'], dimension: 'fansProfile' },
  fans_gender_dist: { zh: '粉丝性别分布', type: 'array', sources: ['fansProfile'], dimension: 'fansProfile' },
  fans_city_top: { zh: '粉丝城市TOP', type: 'array', sources: ['fansProfile'], dimension: 'fansProfile' },
  // 成本/CPM
  estimate_picture_cpm: { zh: '估算图文CPM', type: 'number', sources: ['list', 'costEffective'], dimension: 'cpm' },
  estimate_video_cpm: { zh: '估算视频CPM', type: 'number', sources: ['list', 'costEffective'], dimension: 'cpm' },
  estimate_picture_engage_cost: { zh: '估算图文互动成本', type: 'number', sources: ['list', 'costEffective'], dimension: 'cpm' },
  estimate_video_engage_cost: { zh: '估算视频互动成本', type: 'number', sources: ['list', 'costEffective'], dimension: 'cpm' },
  // 信用/合作
  credit_level: { zh: '信用等级', type: 'string', sources: ['profile'], dimension: 'score' },
  goodness_level: { zh: '诚信等级', type: 'string', sources: ['profile'], dimension: 'score' },
  level_name: { zh: '等级名称', type: 'string', sources: ['profile'], dimension: 'score' },
  is_pgy_creator: { zh: '蒲公英创作者', type: 'bool', sources: ['profile'], dimension: 'verified' },
  has_buyer_auth: { zh: '品牌合作权限', type: 'bool', sources: ['list'], dimension: 'verified' },
  cooperate_state: { zh: '合作状态', type: 'string', sources: ['list'], dimension: 'verified' },
  active_days_in_last7: { zh: '近7天活跃天数', type: 'int', sources: ['dataSummary'], dimension: 'activity' },
  is_active: { zh: '是否活跃', type: 'bool', sources: ['dataSummary'], dimension: 'activity' },
  last_active_time: { zh: '最后活跃时间', type: 'string', sources: ['profile'], dimension: 'activity' },
  response_rate: { zh: '回复率', type: 'number', sources: ['dataSummary'], dimension: 'activity' }
};

// 建议索引（选号查询热路径）
const INDEXES = [
  { name: 'uk_blogger_id', spec: { blogger_id: 1 }, options: { unique: true } },
  { name: 'idx_fans_desc', spec: { follower_count: -1 }, options: {} },
  { name: 'idx_trade_fans', spec: { trade_type: 1, follower_count: -1 }, options: {} },
  { name: 'idx_interaction_desc', spec: { interaction_rate: -1 }, options: {} },
  { name: 'idx_price_asc', spec: { lower_price: 1 }, options: {} },
  { name: 'idx_tags', spec: { content_tags: 1, feature_tags: 1 }, options: {} },
  { name: 'idx_location', spec: { location: 1 }, options: {} },
  { name: 'idx_active', spec: { is_active: 1, active_days_in_last7: -1 }, options: {} },
  { name: 'idx_cpm_asc', spec: { estimate_video_cpm: 1, estimate_picture_cpm: 1 }, options: {} }
];

// 生成 MongoDB 建表 + 索引 DDL 文本（未来建库直接抄）
function buildDdl(collectionName = 'pgy_blogger') {
  const lines = [];
  lines.push('// 蒲公英博主 collection（MongoDB）—— 由 pgy-schema 生成，未来建库直接执行');
  lines.push(`db.createCollection('${collectionName}');`);
  lines.push('');
  for (const index of INDEXES) {
    lines.push(`db.${collectionName}.createIndex(${JSON.stringify(index.spec)}, ${JSON.stringify({ name: index.name, ...index.options })});`);
  }
  lines.push('');
  lines.push(`// 必采报价字段（列表接口）示例文档：`);
  lines.push('// { blogger_id, nickname, follower_count, picture_price, video_price, lower_price, trade_type, content_tags }');
  return lines.join('\n');
}

// 生成选号查询映射 markdown（未来接口开发参考）
function buildQueryMap() {
  const lines = [];
  lines.push('# 蒲公英选号查询映射');
  lines.push('');
  lines.push('> 由 pgy-schema 生成。选号 SOP 的每个筛选维度 → 蒲公英字段 → MongoDB 查询。');
  lines.push('');
  lines.push('| 选号维度 | 蒲公英字段 | 查询示例 |');
  lines.push('| --- | --- | --- |');
  for (const [key, dim] of Object.entries(DIMENSIONS)) {
    lines.push(`| ${dim.zh} | ${dim.fields.join('、')} | \`${JSON.stringify(dim.query)}\` |`);
  }
  lines.push('');
  lines.push('## 采集接口优先级');
  lines.push('');
  lines.push('| 优先级 | 接口 | 核心字段 |');
  lines.push('| --- | --- | --- |');
  const priorityMap = { 3: '⭐⭐⭐ 必采', 2: '⭐⭐ 高优先', 1: '⭐ 中优先', 0: '低优先' };
  for (const [key, source] of Object.entries(SOURCES)) {
    lines.push(`| ${priorityMap[source.priority] || '-'} | ${source.name}（\`${source.method} ${source.path}\`） | ${fieldsBySource(key).join('、') || '-'} |`);
  }
  lines.push('');
  lines.push('> 接口总数：9 个详情 + 1 个列表 = 10 个接口。');
  return lines.join('\n');
}

function fieldsBySource(sourceKey) {
  return Object.entries(CORE_FIELDS)
    .filter(([, field]) => field.sources.includes(sourceKey))
    .map(([name]) => name)
    .slice(0, 12);
}

module.exports = {
  SOURCES,
  DIMENSIONS,
  CORE_FIELDS,
  INDEXES,
  buildDdl,
  buildQueryMap
};