#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""合并飞书活动全量导出（2023-20261027，13 列）到 daily_activities_mcp.json。
规则：唯一键=活动链接末位数字；已有记录只补空字段，不覆盖正文/归属/类型；新记录带正文。
"""
import json, os, re, shutil, sys, time
import pandas as pd

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
DB = os.path.join(ROOT, '数据', 'daily_activities_mcp.json')
DEFAULT_XLSX = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/飞书活动导出/活动导出-销售管理（2023-20261027）.xlsx'
XLSX = os.environ.get('ACTIVITY_XLSX') or (sys.argv[1] if len(sys.argv) > 1 else DEFAULT_XLSX)

def s(v):
    if pd.isna(v): return ''
    return str(v).strip()

def numstr(v):
    t = s(v)
    if not t: return ''
    try: return str(int(float(t)))
    except Exception: return t

def key_of(link):
    m = re.search(r'/(\d{6,})', s(link))
    return int(m.group(1)) if m else None

df = pd.read_excel(XLSX, dtype=str)
print('导出行数', len(df), '列', list(df.columns))

db = json.load(open(DB, encoding='utf-8'))
items = db['items']
by_id = {int(x['work_item_id']): x for x in items if x.get('work_item_id')}
old_n = len(items)

stat = {'new':0,'upd':0,'same':0,'noid':0,'body_fill':0,'new_body':0,'new_by_year':{}}
for _, r in df.iterrows():
    wid = key_of(r.get('活动链接'))
    if not wid:
        stat['noid'] += 1
        continue
    title = s(r.get('名称')) or '活动跟进'
    cust = s(r.get('活动关联客户'))
    ckey = numstr(r.get('活动关联客户 (ID)'))
    body = s(r.get('活动描述'))
    plan = s(r.get('后续计划'))
    dept = s(r.get('跟进部门'))
    creator = s(r.get('创建者'))
    contact = s(r.get('活动关联联系人'))
    opp_name = s(r.get('活动对应商机'))
    opp_id = numstr(r.get('活动对应商机 (ID)'))
    day = s(r.get('创建时间'))[:10]

    cur = by_id.get(wid)
    if cur is None:
        rec = {
            'name': title,
            'start_time': day,
            'field_b99055': body,
            'field_5f20fc': {'key': ckey, 'label': cust} if cust else None,
            'field_76654e': None,
            'field_5ed7ab': None,
            'owner': {'name_cn': creator, 'name_en': creator} if creator else None,
            'work_item_id': wid,
            'opportunity_name': opp_name,
            'opportunity_id': opp_id,
            'department': dept,
            'follow_up_plan': plan,
            'contact': contact,
            'source': 'feishu_export_full',
        }
        items.append(rec); by_id[wid] = rec
        stat['new'] += 1
        if body: stat['new_body'] += 1
        y = day[:4]
        stat['new_by_year'][y] = stat['new_by_year'].get(y, 0) + 1
        continue

    changed = False
    if body and not (cur.get('field_b99055') or '').strip():
        cur['field_b99055'] = body; stat['body_fill'] += 1; changed = True
    if cust and not cur.get('field_5f20fc'):
        cur['field_5f20fc'] = {'key': ckey, 'label': cust}; changed = True
    if creator and not cur.get('owner'):
        cur['owner'] = {'name_cn': creator, 'name_en': creator}; changed = True
    if day and not cur.get('start_time'):
        cur['start_time'] = day; changed = True
    if opp_name and not cur.get('opportunity_name'):
        cur['opportunity_name'] = opp_name; changed = True
    if opp_id and not cur.get('opportunity_id'):
        cur['opportunity_id'] = opp_id; changed = True
    if dept and not cur.get('department'):
        cur['department'] = dept; changed = True
    if plan and not cur.get('follow_up_plan'):
        cur['follow_up_plan'] = plan; changed = True
    if contact and not cur.get('contact'):
        cur['contact'] = contact; changed = True
    stat['upd' if changed else 'same'] += 1

db['total'] = len(items)
db['updated'] = time.strftime('%Y-%m-%d %H:%M')
bak = DB + '.bak_' + time.strftime('%Y%m%d_%H%M%S')
shutil.copy2(DB, bak)
json.dump(db, open(DB, 'w', encoding='utf-8'), ensure_ascii=False)
print('备份', os.path.basename(bak))
print('旧库', old_n, '→ 新库', len(items))
print('新增', stat['new'], '更新', stat['upd'], '未变', stat['same'], '无ID', stat['noid'])
print('补正文', stat['body_fill'], '新增带正文', stat['new_body'], '新增按年', stat['new_by_year'])