shuhua-majiyong_VOC/majiyong/scripts/collect-majiyong.js

#!/usr/bin/env node
/**
 * 马记永 · 四月小红书 KOL/KOC 宣发 VOC 数据采集
 *
 * 输入: ../【新鲜菜项目】马记永4月宣发数据汇总(1).xlsx
 *       Sheets: KOL合作表(13) + KOC合作表(282)
 * 流程:
 *   1. 解析两张 sheet 合并成统一行
 *   2. 直连 URL → 直接提取 noteId / 短链 → 跳转解析
 *   3. TikHub /thapi/v1/xiaohongshu/app/get_note_comments 拉评论 (3 页/笔记)
 *   4. 落盘 data/raw-merged.json
 */
const fs = require('fs');
const path = require('path');
const https = require('https');
const http = require('http');
const xlsx = require('xlsx');

const ROOT = path.resolve(__dirname, '..');
const XLSX_PATH = path.resolve(ROOT, '..', '【新鲜菜项目】马记永4月宣发数据汇总(1).xlsx');
const DATA_DIR = path.join(ROOT, 'data');
const NOTES_DIR = path.join(DATA_DIR, 'notes');
const COMMENTS_DIR = path.join(DATA_DIR, 'comments');
const AUDIT_LOG = path.join(DATA_DIR, 'audit.log');
[DATA_DIR, NOTES_DIR, COMMENTS_DIR].forEach((d) => { if (!fs.existsSync(d)) fs.mkdirSync(d, { recursive: true }); });

const TIKHUB_TOKEN = process.env.TIKHUB_TOKEN
    || 'gqsZHfMWgAiMwV+ITbmZy0qALADWBZVS7QnV7kKJe9CwzgWgJG+7bwK+GQ==';
const API_HOST = 'server.fmode.cn';
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

function log(msg) {
    const line = `[${new Date().toISOString()}] ${msg}`;
    console.log(line);
    try { fs.appendFileSync(AUDIT_LOG, line + '\n'); } catch {}
}

function excelDateToISO(serial) {
    if (typeof serial !== 'number') return String(serial || '');
    return new Date(Math.round((serial - 25569) * 86400 * 1000)).toISOString().slice(0, 10);
}

function tikhubGet(apiPath, params = {}) {
    const qs = Object.entries(params).filter(([, v]) => v !== '' && v !== undefined && v !== null)
        .map(([k, v]) => `${encodeURIComponent(k)}=${encodeURIComponent(v)}`).join('&');
    const url = qs ? `${apiPath}?${qs}` : apiPath;
    return new Promise((resolve) => {
        const req = https.request({
            hostname: API_HOST, path: url, method: 'GET',
            headers: { Authorization: `Bearer ${TIKHUB_TOKEN}`, Accept: 'application/json' },
        }, (res) => {
            const chunks = [];
            res.on('data', (c) => chunks.push(c));
            res.on('end', () => {
                const body = Buffer.concat(chunks).toString('utf-8');
                try { resolve({ data: JSON.parse(body), status: res.statusCode }); }
                catch (e) { resolve({ data: { _parse_error: e.message, _raw: body.slice(0, 200) }, status: res.statusCode }); }
            });
        });
        req.on('error', (e) => resolve({ data: { _error: e.message }, status: 0 }));
        req.setTimeout(45000, () => { req.destroy(); resolve({ data: { _error: 'timeout' }, status: 0 }); });
        req.end();
    });
}

function extractNoteIdFromUrl(u) {
    if (!u) return null;
    const m = u.match(/(?:discovery\/item|explore|item)\/([0-9a-f]{16,32})/);
    return m ? m[1] : null;
}

function resolveShortLink(shortUrl, depth = 0) {
    // 直连优先,免抓取
    const direct = extractNoteIdFromUrl(shortUrl);
    if (direct) return Promise.resolve({ finalUrl: shortUrl, noteId: direct, status: 0 });
    if (depth > 6) return Promise.resolve({ finalUrl: shortUrl, noteId: null });
    return new Promise((resolve) => {
        let u; try { u = new URL(shortUrl); } catch { return resolve({ finalUrl: shortUrl, noteId: null, _error: 'bad-url' }); }
        const mod = u.protocol === 'https:' ? https : http;
        const req = mod.request({
            method: 'GET', hostname: u.hostname, path: u.pathname + (u.search || ''),
            headers: {
                'User-Agent': 'Mozilla/5.0 (iPhone; CPU iPhone OS 16_0 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.0 Mobile/15E148 Safari/604.1',
                Accept: 'text/html,*/*',
            },
        }, (res) => {
            const loc = res.headers.location;
            if ([301, 302, 303, 307, 308].includes(res.statusCode) && loc) {
                res.resume();
                const next = loc.startsWith('http') ? loc : new URL(loc, shortUrl).toString();
                return resolve(resolveShortLink(next, depth + 1));
            }
            const chunks = [];
            res.on('data', (c) => chunks.push(c));
            res.on('end', () => {
                const html = Buffer.concat(chunks).toString('utf-8');
                let noteId = extractNoteIdFromUrl(shortUrl);
                if (!noteId) {
                    const m = html.match(/"note_?id"\s*:\s*"([0-9a-f]{16,32})"/i) || html.match(/\/(?:discovery\/item|explore)\/([0-9a-f]{16,32})/);
                    if (m) noteId = m[1];
                }
                resolve({ finalUrl: shortUrl, noteId, status: res.statusCode });
            });
        });
        req.on('error', (e) => resolve({ finalUrl: shortUrl, noteId: null, _error: e.message }));
        req.setTimeout(15000, () => { req.destroy(); resolve({ finalUrl: shortUrl, noteId: null, _error: 'timeout' }); });
        req.end();
    });
}

function readRows() {
    const wb = xlsx.readFile(XLSX_PATH);
    const all = [];
    for (const sheet of ['KOL合作表', 'KOC合作表']) {
        const rows = xlsx.utils.sheet_to_json(wb.Sheets[sheet], { defval: '' });
        for (const r of rows) {
            if (!r['发布链接']) continue;
            all.push({
                sheet,
                seq: r['序号'],
                tier: r['量级'] || '-',
                contentType: r['类型'] || '-',
                name: r['小红书昵称'],
                profileUrl: r['主页链接'],
                xhsId: r['小红书id'],
                followersWan: r['粉丝数/w'],
                region: r['地区'] || '-',
                cooperation: r['合作形式'] || '-',
                priceOrder: r['下单价格'] || '',
                authorized: r['是否授权'] || '',
                publishDate: typeof r['发布时间'] === 'number' ? excelDateToISO(r['发布时间']) : (r['发布时间'] || ''),
                postUrl: r['发布链接'],
                likes: r['点赞'],
                collects: r['收藏'],
                comments: r['评论'],
                engagement: r['总互动'],
                impressions: r['曝光量'],
                syncEngagement: r['同步互动量'] || r['同步互动量总计'] || 0,
                syncImpressions: r['同步曝光量'] || r['同步曝光量总计'] || 0,
                engagementSubtotal: r['互动量小计'],
                impressionsSubtotal: r['曝光量小计'],
            });
        }
    }
    return all;
}

async function main() {
    const argv = process.argv.slice(2);
    const force = argv.includes('--force');
    const limit = (() => { const a = argv.find((x) => x.startsWith('--limit=')); return a ? parseInt(a.split('=')[1], 10) : 0; })();
    const skipResolve = argv.includes('--skip-resolve');
    const onlyResolve = argv.includes('--only-resolve');

    log(`▶ 启动马记永采集 · TIKHUB_TOKEN=${TIKHUB_TOKEN ? '✓' : '✗'} · force=${force} · limit=${limit || 'all'}`);

    const rows = readRows();
    log(`  · 解析 xlsx(2 sheets) 合并 ${rows.length} 条 KOL/KOC 记录`);

    // 1. resolve
    const resolved = [];
    let directHit = 0, redirectCount = 0, failCount = 0;
    for (let i = 0; i < rows.length; i++) {
        if (limit && i >= limit) break;
        const row = rows[i];
        const cacheKey = `${row.sheet === 'KOL合作表' ? 'L' : 'C'}-${row.seq}`;
        const cachePath = path.join(NOTES_DIR, `${cacheKey}-resolve.json`);
        let r;
        if (!force && fs.existsSync(cachePath)) {
            r = JSON.parse(fs.readFileSync(cachePath, 'utf8'));
        } else {
            r = await resolveShortLink(row.postUrl);
            fs.writeFileSync(cachePath, JSON.stringify(r, null, 2));
            if (!extractNoteIdFromUrl(row.postUrl)) await sleep(250);
        }
        if (r.noteId) {
            if (extractNoteIdFromUrl(row.postUrl)) directHit++; else redirectCount++;
        } else { failCount++; }
        if ((i + 1) % 25 === 0 || i === 0) log(`  [${cacheKey}] ${row.name?.slice(0, 12)} → ${r.noteId ? r.noteId : 'NULL'} · 进度 ${i + 1}/${rows.length}`);
        resolved.push({ ...row, noteId: r.noteId, finalUrl: r.finalUrl, resolveError: r._error || null });
    }
    log(`▶ 解析完成: 直连 ${directHit} · 跳转 ${redirectCount} · 失败 ${failCount}`);

    if (onlyResolve) {
        fs.writeFileSync(path.join(DATA_DIR, 'resolved.json'), JSON.stringify(resolved, null, 2));
        log('  · only-resolve mode 退出');
        return;
    }

    // 2. fetch comments
    let totalCmt = 0, withCmt = 0;
    for (let i = 0; i < resolved.length; i++) {
        const row = resolved[i];
        if (!row.noteId) { row._comments = []; continue; }
        if (skipResolve && Number(row.comments || 0) === 0) { row._comments = []; continue; }
        const cacheKey = `${row.sheet === 'KOL合作表' ? 'L' : 'C'}-${row.seq}`;
        const cachePath = path.join(COMMENTS_DIR, `${cacheKey}-${row.noteId}.json`);
        if (!force && fs.existsSync(cachePath)) {
            row._comments = JSON.parse(fs.readFileSync(cachePath, 'utf8')).comments || [];
            if (row._comments.length) { totalCmt += row._comments.length; withCmt++; }
            continue;
        }
        const all = [];
        let cursor = '';
        const maxPages = Number(row.comments || 0) > 30 ? 5 : 3;
        for (let p = 0; p < maxPages; p++) {
            await sleep(700);
            const res = await tikhubGet('/thapi/v1/xiaohongshu/app/get_note_comments', { note_id: row.noteId, cursor });
            const inner = res?.data?.data?.data || res?.data?.data || {};
            const cmts = inner?.comments || [];
            for (const c of cmts) {
                all.push({
                    id: c.id, content: c.content, time: c.time || c.create_time,
                    like_count: c.like_count, sub_comment_count: c.sub_comment_count,
                    ip_location: c.ip_location,
                    nickname: c.user?.nickname || c.user_info?.nickname || '匿名',
                    user_id: c.user?.userid || c.user_info?.user_id,
                    sub_comments: (c.sub_comments || []).slice(0, 5).map((s) => ({
                        content: s.content, like_count: s.like_count,
                        nickname: s.user?.nickname || s.user_info?.nickname,
                    })),
                });
            }
            const hasMore = inner?.has_more;
            cursor = inner?.cursor || '';
            if (!hasMore || !cursor) break;
        }
        row._comments = all;
        fs.writeFileSync(cachePath, JSON.stringify({ noteId: row.noteId, fetched_at: new Date().toISOString(), comments: all }, null, 2));
        if (all.length) { totalCmt += all.length; withCmt++; }
        if ((i + 1) % 20 === 0 || (all.length > 0)) {
            log(`  [${cacheKey}] 💬 ${row.name?.slice(0, 14)} → ${all.length} 条 (xlsx ${row.comments}) · ${i + 1}/${resolved.length}`);
        }
    }

    const out = {
        meta: {
            source: '【新鲜菜项目】马记永4月宣发数据汇总(1).xlsx',
            generated_at: new Date().toISOString(),
            total_posts: resolved.length,
            resolved_count: resolved.filter((x) => x.noteId).length,
            with_api_comments: withCmt,
            total_api_comments: totalCmt,
            total_xlsx_comments: resolved.reduce((a, b) => a + Number(b.comments || 0), 0),
        },
        items: resolved,
    };
    const outPath = path.join(DATA_DIR, 'raw-merged.json');
    fs.writeFileSync(outPath, JSON.stringify(out, null, 2), 'utf8');
    log(`\n✓ 完成: ${out.meta.resolved_count}/${out.meta.total_posts} 解析 · ${withCmt} 笔记有评论 · 抓取 ${totalCmt} 条 (xlsx 报备 ${out.meta.total_xlsx_comments}) → ${outPath}`);
}

if (require.main === module) {
    main().catch((e) => { console.error('fatal:', e); process.exit(1); });
}