accounting/data/import-wechat.js

const XLSX = require('xlsx');

const API_BASE = 'http://localhost:3000/api';
const USER = '猪2';

// 分类ID映射
const CAT = {
  // 支出
  餐饮: 5, 交通: 6, 购物: 7, 房租: 8, 水电燃气: 9, 话费: 10,
  亚马逊物流费: 11, 亚马逊广告费: 12, 亚马逊货款: 13, 其他支出: 14,
  // 收入
  工资: 1, 亚马逊转账: 2, 利息: 3, 其他收入: 4,
};

// Excel序列号转日期
function excelDateToStr(serial) {
  const utcDays = Math.floor(serial - 25569);
  const d = new Date(utcDays * 86400 * 1000);
  const y = d.getUTCFullYear();
  const m = String(d.getUTCMonth() + 1).padStart(2, '0');
  const day = String(d.getUTCDate()).padStart(2, '0');
  return `${y}-${m}-${day}`;
}

// 根据交易对方和商品智能分类
function classify(row) {
  const counterparty = (row[2] || '').toString();
  const product = (row[3] || '').toString();
  const direction = (row[4] || '').toString(); // 收/支
  const status = (row[7] || '').toString();
  const txType = (row[1] || '').toString();

  const isIncome = direction === '收入';
  const combined = counterparty + product;

  if (isIncome) {
    // 收入分类
    if (txType.includes('退款')) {
      return { id: CAT.其他收入, note: `${counterparty}退款` };
    }
    if (txType.includes('红包') || combined.includes('红包')) {
      return { id: CAT.其他收入, note: `微信红包-${counterparty}` };
    }
    return { id: CAT.其他收入, note: counterparty };
  }

  // 支出分类
  // 交通
  if (combined.includes('12306') || combined.includes('中铁网络')) {
    return { id: CAT.交通, note: '12306火车票' };
  }
  if (combined.includes('货拉拉')) {
    return { id: CAT.交通, note: `货拉拉-${product}` };
  }

  // 餐饮
  if (combined.includes('肯德基') || combined.includes('KFC')) {
    return { id: CAT.餐饮, note: '肯德基' };
  }
  if (combined.includes('美团') || combined.includes('大众点评')) {
    return { id: CAT.餐饮, note: `美团-${counterparty}` };
  }
  if (combined.includes('菜馆') || combined.includes('米线') || combined.includes('羊肉')
    || combined.includes('餐') || combined.includes('饭') || combined.includes('食')
    || combined.includes('厨') || combined.includes('锅')) {
    return { id: CAT.餐饮, note: counterparty };
  }
  if (counterparty.includes('农夫山泉')) {
    return { id: CAT.餐饮, note: '农夫山泉饮用水' };
  }
  if (combined.includes('腐乳') || combined.includes('杨伯雪')) {
    return { id: CAT.餐饮, note: `${counterparty}-转账购买` };
  }

  // 购物
  if (combined.includes('拼多多')) {
    return { id: CAT.购物, note: `拼多多-${product}` };
  }
  if (combined.includes('山姆') || combined.includes('Sams')) {
    return { id: CAT.购物, note: '山姆会员商店' };
  }

  // 其他支出（带具体备注）
  if (combined.includes('丰巢') || combined.includes('寄件')) {
    return { id: CAT.其他支出, note: `快递寄件-${counterparty}` };
  }
  if (combined.includes('淘宝退货') || combined.includes('退货')) {
    return { id: CAT.其他支出, note: '淘宝退货运费' };
  }
  if (combined.includes('文化') || combined.includes('地图') || combined.includes('门票')) {
    return { id: CAT.其他支出, note: `旅游/门票-${counterparty}` };
  }
  if (counterparty.includes('倾城山')) {
    return { id: CAT.其他支出, note: '景点门票-倾城山' };
  }

  // 兜底
  return { id: CAT.其他支出, note: `${counterparty}-${product}` };
}

async function main() {
  const wb = XLSX.readFile('微信支付账单流水文件(20260101-20260321).xlsx');
  const ws = wb.Sheets[wb.SheetNames[0]];
  const data = XLSX.utils.sheet_to_json(ws, { header: 1 });

  const records = [];
  for (let i = 18; i < data.length; i++) {
    const row = data[i];
    if (!row || row.length < 6) continue;

    const dateStr = excelDateToStr(row[0]);
    const direction = (row[4] || '').toString();
    const amount = parseFloat(row[5]);
    const type = direction === '收入' ? 'income' : 'expense';
    const { id: category_id, note } = classify(row);

    records.push({ date: dateStr, type, category_id, amount, note, user: USER });
  }

  console.log(`共解析 ${records.length} 条记录，开始导入...\n`);

  let success = 0, fail = 0;
  for (let i = 0; i < records.length; i++) {
    const r = records[i];
    try {
      const res = await fetch(`${API_BASE}/records`, {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(r),
      });
      if (res.ok) {
        success++;
        console.log(`[${i + 1}/${records.length}] ✓ ${r.date} ${r.type === 'income' ? '收入' : '支出'} ¥${r.amount} ${r.note}`);
      } else {
        fail++;
        const err = await res.json();
        console.log(`[${i + 1}/${records.length}] ✗ ${r.date} ¥${r.amount} - ${err.error}`);
      }
    } catch (e) {
      fail++;
      console.log(`[${i + 1}/${records.length}] ✗ ${r.date} ¥${r.amount} - ${e.message}`);
    }
  }

  console.log(`\n导入完成！成功: ${success}, 失败: ${fail}`);
}

main().catch(console.error);