openclaw-voc-skill/claude-code/claude-code-qiwe-assistant/knowledge-base/property-data/match-engine-pg.js

#!/usr/bin/env node
/**
 * 小牛看房 — 房源智能匹配引擎 v2 (PostgreSQL)
 * 用法: node src/assets/data/match-engine-pg.js [--buyer buyer_001] [--top 5]
 */

const { Client } = require('pg');

const PG_CONFIG = {
  host: 'localhost', port: 5432,
  user: 'postgres', password: '20061003',
  database: 'huaxiangpipei'
};

// ============================================================
// 数据加载（从 PostgreSQL）
// ============================================================
async function loadBuyers(pg) {
  const res = await pg.query('SELECT * FROM buyers ORDER BY id');
  if (res.rows.length === 0) {
    // 没有buyers数据，从JSON加载默认5个客户
    const json = require('./buyers.json');
    return json.clients;
  }
  return res.rows.map(r => ({
    id: r.buyer_code || ('buyer_' + r.id),
    type: r.buyer_type,
    name: r.name,
    targetDistricts: r.target_districts || [],
    budgetMin: parseFloat(r.budget_min) || 0,
    budgetMax: parseFloat(r.budget_max) || 0,
    preferredLayouts: r.preferred_layouts || [],
    areaMin: parseFloat(r.area_min) || 0,
    areaMax: parseFloat(r.area_max) || 0,
    floorPreference: r.floor_preference || '中',
    preferredOrientations: r.preferred_orientations || [],
    purpose: r.purpose || '',
    decorationRequirement: r.decoration_requirement || '不限',
    buildingAgeMax: r.building_age_max || 30,
    schoolDistrictRequired: r.school_district_required || false,
    targetSchools: r.target_schools || [],
    decisionStyle: r.decision_style || '对比型',
    familyStructure: { members: r.family_members || 2, hasElderly: r.has_elderly || false, hasKids: r.has_kids || false },
    coreConcerns: r.core_concerns || [],
    resistFactors: r.resist_factors || [],
    specialRequirements: r.special_requirements || [],
    surfaceBudget: parseFloat(r.surface_budget) || 0,
    downPayment: parseFloat(r.down_payment) || 0,
    monthlyPaymentCapacity: parseFloat(r.monthly_payment_capacity) || 0,
    oldHouseEstimatedValue: 0,
    tags: r.tags || []
  }));
}

async function loadProperties(pg) {
  const res = await pg.query('SELECT * FROM properties ORDER BY total_price');
  return res.rows;
}

// ============================================================
// 权重模板（不变）
// ============================================================
// POI别名→名称映射
const POI_ALIASES = {
  '三中': '常州市第三中学',
  '常州三中': '常州市第三中学',
  '第三中学': '常州市第三中学',
  '局小': '局前街小学',
  '局前街': '局前街小学',
  '博小': '博爱路小学',
  '博爱路': '博爱路小学',
  '解小': '解放路小学',
  '文化宫': '文化宫商圈',
  '万达': '万达商圈(新北)',
  '吾悦': '吾悦广场(武进)',
  '常州站': '地铁1号线常州火车站',
  '火车站': '地铁1号线常州火车站',
  '红梅公园': '红梅公园',
};

const WEIGHT_TEMPLATES = {
  '婚房刚需型': { priceAdvantage:0.30, layoutMatch:0.15, areaMatch:0.10, decoration:0.10, schoolMatch:0.05, transport:0.10, community:0.05, floorMatch:0.05, orientation:0.05, surrounding:0.05, poiProximity:0.00 },
  '学区焦虑型': { priceAdvantage:0.15, layoutMatch:0.10, areaMatch:0.05, decoration:0.05, schoolMatch:0.45, transport:0.10, community:0.05, floorMatch:0.02, orientation:0.02, surrounding:0.01, poiProximity:0.00 },
  '置换改善型': { priceAdvantage:0.20, layoutMatch:0.20, areaMatch:0.20, decoration:0.15, schoolMatch:0.05, transport:0.05, community:0.10, floorMatch:0.03, orientation:0.02, surrounding:0.00, poiProximity:0.00 },
  '隐性需求型': { priceAdvantage:0.20, layoutMatch:0.10, areaMatch:0.10, decoration:0.25, schoolMatch:0.05, transport:0.10, community:0.10, floorMatch:0.05, orientation:0.03, surrounding:0.02, poiProximity:0.00 },
  '刚需升级型': { priceAdvantage:0.25, layoutMatch:0.15, areaMatch:0.15, decoration:0.20, schoolMatch:0.05, transport:0.05, community:0.05, floorMatch:0.05, orientation:0.03, surrounding:0.02, poiProximity:0.00 },
};

const HIGHLIGHT_BONUS = {
  '豪装': { '隐性需求型':3, '刚需升级型':3 },
  '精装': { '隐性需求型':2, '刚需升级型':2, '置换改善型':1 },
  '满五唯一': { '婚房刚需型':2 },
  '急售': { '婚房刚需型':2, '置换改善型':-1 },
  '南北通透': { '婚房刚需型':1, '置换改善型':1, '刚需升级型':1, '隐性需求型':1, '学区焦虑型':1 },
  '近地铁': { '婚房刚需型':2 },
  '地铁': { '婚房刚需型':2 },
  '人车分流': { '置换改善型':2 },
  '总价低': { '婚房刚需型':2, '刚需升级型':1 },
  '品牌开发商': { '置换改善型':1, '刚需升级型':1 },
  '次新房': { '婚房刚需型':1, '置换改善型':1, '刚需升级型':1, '隐性需求型':1 },
};

// ============================================================
// 匹配核心（与原版完全一致）
// ============================================================
function hardFilter(buyer, properties) {
  const loanCoefficient = 180;
  const maxLoan = buyer.monthlyPaymentCapacity * loanCoefficient / 10000;
  const realBudgetMax = Math.max(buyer.budgetMax, (buyer.downPayment + maxLoan) * 0.9);

  return properties.filter(p => {
    const totalPrice = parseFloat(p.total_price);
    if (totalPrice > realBudgetMax * 1.1) return false;
    const districtMatch = buyer.targetDistricts.length === 0 || buyer.targetDistricts.some(d => {
      const short = d.split('-')[0]; // "新北区-薛家" → "新北区"
      return (p.district || '').includes(short) || short.includes(p.district || '');
    });
    if (!districtMatch) return false;
    if (p.building_age && p.building_age > buyer.buildingAgeMax) return false;
    // 学区：同区有学区房则严格过滤，无则放行（真实房源学区数据不完整）
    if (buyer.schoolDistrictRequired && !p.is_school_district) {
      const hasSchoolInDistrict = properties.some(x => x.is_school_district && x.district === p.district);
      if (hasSchoolInDistrict) return false;
    }
    if (buyer.resistFactors.includes('底层') && p.is_ground_floor) return false;
    if (buyer.resistFactors.includes('顶楼') && p.is_top_floor) return false;
    // 人车分流降为软偏好（贝壳数据无此标注）
    if (buyer.areaMin > 0 && parseFloat(p.area) < buyer.areaMin * 0.85) return false;
    // POI距离硬过滤：客户说"三中附近1公里内"
    if (buyer.nearbyPoi && buyer.nearbyPoi.name) {
      const poiName = POI_ALIASES[buyer.nearbyPoi.name] || buyer.nearbyPoi.name;
      const dists = p.poi_distances ? (typeof p.poi_distances === 'string' ? JSON.parse(p.poi_distances) : p.poi_distances) : {};
      const dist = parseFloat(dists[poiName]);
      if (dists[poiName] !== undefined && buyer.nearbyPoi.maxDistance && dist > buyer.nearbyPoi.maxDistance) return false;
    }
    return true;
  });
}

function scoreProperty(buyer, p, weights) {
  const s = {};
  const totalPrice = parseFloat(p.total_price);
  const area = parseFloat(p.area);
  const tags = p.highlight_tags || [];

  s.priceAdvantage = (p.price_advantage || 5) / 10;
  s.layoutMatch = buyer.preferredLayouts.includes(p.layout) ? 1.0 :
    buyer.preferredLayouts.some(pl => pl.split('室')[0] === (p.layout||'').split('室')[0]) ? 0.6 : 0.3;
  s.areaMatch = area >= buyer.areaMin && area <= buyer.areaMax ? 1.0 :
    area >= buyer.areaMin * 0.85 && area <= buyer.areaMax * 1.15 ? 0.6 : 0.2;
  const decoMap = { '豪装':1.0, '精装':0.75, '简装':0.4, '毛坯':0.2 };
  const reqLevel = buyer.decorationRequirement === '不限' ? 0 : (decoMap[buyer.decorationRequirement] || 0.5);
  const actLevel = decoMap[p.decoration] || 0.5;
  s.decoration = actLevel >= reqLevel ? 1.0 : actLevel / Math.max(reqLevel, 0.1);
  s.schoolMatch = !buyer.schoolDistrictRequired ? 1.0 : (p.is_school_district ? 1.0 : 0.0);
  s.transport = (p.transport_score || 5) / 10;
  s.community = (p.community_quality || 5) / 10;
  const floorMap = { '低':0, '中':1, '高':2 };
  const bf = floorMap[buyer.floorPreference] ?? 1;
  const pf = floorMap[p.floor_level] ?? 1;
  s.floorMatch = Math.abs(bf - pf) === 0 ? 1.0 : Math.abs(bf - pf) === 1 ? 0.6 : 0.3;
  if (buyer.preferredOrientations.includes('不限')) s.orientation = 0.8;
  else s.orientation = buyer.preferredOrientations.some(o => (p.orientation||'').includes(o)) ? 1.0 : 0.3;
  s.surrounding = (p.surrounding_score || 5) / 10;

  // POI距离评分：客户说"三中附近"→ 越近分越高
  if (buyer.nearbyPoi && buyer.nearbyPoi.name && buyer.nearbyPoi.maxDistance) {
    const poiName = POI_ALIASES[buyer.nearbyPoi.name] || buyer.nearbyPoi.name;
    const dists = p.poi_distances ? (typeof p.poi_distances === 'string' ? JSON.parse(p.poi_distances) : p.poi_distances) : {};
    const dist = parseFloat(dists[poiName]);
    if (!isNaN(dist) && buyer.nearbyPoi.maxDistance > 0) {
      s.poiProximity = Math.max(0, 1 - dist / buyer.nearbyPoi.maxDistance);
    } else {
      s.poiProximity = 0.5;
    }
  } else {
    s.poiProximity = 0.5;
  }

  let total = 0, totalW = 0;
  for (const [k, w] of Object.entries(weights)) {
    if (s[k] !== undefined) { total += s[k] * w; totalW += w; }
  }
  return totalW > 0 ? (total / totalW) * 100 : 0;
}

function calcHighlightBonus(buyerType, tags) {
  let bonus = 0;
  for (const tag of (tags || [])) {
    const b = HIGHLIGHT_BONUS[tag];
    if (b && b[buyerType]) bonus += b[buyerType];
  }
  return Math.min(bonus, 5);
}

function calcPsychBonus(buyer, p) {
  let bonus = 0;
  const tags = p.highlight_tags || [];
  if (buyer.decisionStyle === '对比型' && (p.price_advantage || 5) >= 7) bonus += 2;
  if (buyer.decisionStyle === '谨慎型' && !p.is_ground_floor && !p.is_top_floor) bonus += 2;
  if ((buyer.decisionStyle === '冲动型' || buyer.type === '隐性需求型') && tags.some(t => t.includes('豪装'))) bonus += 3;
  if (buyer.type === '置换改善型' && tags.includes('人车分流') && (p.community_quality || 5) >= 8) bonus += 1;
  return Math.min(bonus, 3);
}

function generateCons(p) {
  const cons = [];
  if (p.building_age >= 20) cons.push('房龄较老，需关注管道老化和渗水情况');
  if (p.building_age >= 25) cons.push('房龄超过25年，贷款年限可能受限');
  if (p.decoration === '简装') cons.push('装修简单，入住前可能需翻新');
  if (p.decoration === '毛坯') cons.push('毛坯房，需额外准备装修预算约10-15万');
  if (p.is_ground_floor) cons.push('底层，需关注防潮和隐私问题');
  if (p.is_top_floor) cons.push('顶楼，夏季较热，需关注防水');
  if ((p.parking || '无') === '无') cons.push('无车位，周边停车可能不便');
  if ((p.community_quality || 5) <= 4) cons.push('小区品质一般');
  if ((p.transport_score || 5) <= 5) cons.push('交通便利度一般');
  if ((p.floor_level === '低') && !p.is_ground_floor) cons.push('低楼层，采光可能受遮挡影响');
  return cons.length > 0 ? cons : ['无明显硬伤，整体较为均衡'];
}

function match(buyer, properties, topN = 5) {
  const weights = WEIGHT_TEMPLATES[buyer.type] || WEIGHT_TEMPLATES['婚房刚需型'];
  const candidates = hardFilter(buyer, properties);

  const scored = candidates.map(p => {
    const s = scoreProperty(buyer, p, weights);
    const hb = calcHighlightBonus(buyer.type, p.highlight_tags || []);
    const pb = calcPsychBonus(buyer, p);
    const final = s + hb + pb;
    return { property: p, finalScore: final, stage2: s, highlightBonus: hb, psychBonus: pb,
      level: final >= 82 ? '强烈推荐' : final >= 72 ? '推荐' : final >= 62 ? '备选' : '不推荐' };
  });

  scored.sort((a, b) => b.finalScore - a.finalScore);
  return scored.slice(0, topN);
}

// ============================================================
// 输出
// ============================================================
function printResults(buyer, results) {
  console.log(`\n${'='.repeat(70)}`);
  console.log(`🎯 ${buyer.name}（${buyer.type}）| 预算${buyer.surfaceBudget}万 | 首付${buyer.downPayment}万`);
  console.log(`   区域: ${buyer.targetDistricts.join('、')} | 关注: ${buyer.coreConcerns.join(' > ')}`);
  console.log(`${'='.repeat(70)}`);

  if (results.length === 0) {
    console.log('\n⚠️ 无匹配房源');
    return;
  }

  results.forEach((r, i) => {
    const p = r.property;
    const cons = generateCons(p);
    console.log(`\n${'─'.repeat(70)}`);
    console.log(`🏠 #${i+1} [${r.level}] ${p.community} · ${p.layout} · ${p.total_price}万`);
    console.log(`   综合 ${r.finalScore.toFixed(1)} (基础${r.stage2.toFixed(1)}+卖点${r.highlightBonus}+心理${r.psychBonus})`);
    console.log(`   ${p.district} | ${p.area}㎡ | ${p.floor_info||p.floor_level} | ${p.orientation} | ${p.decoration} | ${p.building_age}年`);
    console.log(`   亮点: ${(p.highlight_tags||[]).join(' · ')}`);
    console.log(`   ⚠️ ${cons.join('；')}`);
    console.log(`   来源: ${p.source}`);
  });

  console.log(`\n${'─'.repeat(70)}`);
  console.log(`📊 ${results.length}条结果\n`);
}

// ============================================================
// 入口
// ============================================================
async function main() {
  const args = process.argv.slice(2);
  const buyerId = args.includes('--buyer') ? args[args.indexOf('--buyer')+1] : null;
  const topN = args.includes('--top') ? parseInt(args[args.indexOf('--top')+1]) || 5 : 5;

  const pg = new Client(PG_CONFIG);
  await pg.connect();

  const buyers = await loadBuyers(pg);
  const properties = await loadProperties(pg);

  console.log(`📦 数据库: ${buyers.length} 客户, ${properties.length} 房源 (${properties.filter(p=>p.source==='scraped').length}真实 + ${properties.filter(p=>p.source==='mock').length}模拟)\n`);

  const targets = buyerId ? buyers.filter(b => b.id === buyerId || b.name?.includes(buyerId)) : buyers;
  if (targets.length === 0) {
    console.error(`❌ 未找到: ${buyerId}`);
    console.error(`   可用: ${buyers.map(b => b.id + ':' + b.name).join(', ')}`);
    process.exit(1);
  }

  for (const buyer of targets) {
    const results = match(buyer, properties, topN);
    printResults(buyer, results);
  }

  await pg.end();
}

if (require.main === module) {
  main().catch(e => { console.error(e.message); process.exit(1); });
}