#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""90 天活动全景视图导出 → daily_activities_mcp.json
唯一键=工作项链接末位数字；已有记录只补空正文/归属，不覆盖已有正文；
新记录写正文，source=feishu_export_90d。
"""
import json
import os
import re
import shutil
import time

import pandas as pd

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
DB = os.path.join(ROOT, '数据', 'daily_activities_mcp.json')
XLSX = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/飞书活动导出/20261029及近90日活动全景视图导出-销售管理.xlsx')


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


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


def main():
    df = pd.read_excel(XLSX, dtype=str)
    print('导出行数', len(df), '唯一链接', df['工作项链接'].nunique())
    db = json.load(open(DB, encoding='utf-8'))
    items = db.get('items') or []
    by_id = {int(x['work_item_id']): x for x in items if x.get('work_item_id')}
    old_n = len(items)
    st = {'new': 0, 'upd': 0, 'same': 0, 'noid': 0, 'body_fill': 0, 'new_body': 0, 'owner_fill': 0}
    for _, r in df.iterrows():
        wid = key_of(r.get('工作项链接'))
        if not wid:
            st['noid'] += 1
            continue
        title = s(r.get('标题')) or '活动跟进'
        cust = s(r.get('活动关联客户'))
        ckey = s(r.get('活动关联客户 (ID)'))
        body = s(r.get('活动描述'))
        creator = s(r.get('创建人'))
        owner = s(r.get('客户-所属人')) or creator
        day = s(r.get('创建时间'))[:10]
        opp_name = s(r.get('活动对应商机'))
        opp_id = s(r.get('活动对应商机 (ID)'))
        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': owner, 'name_en': owner} if owner else None,
                'work_item_id': wid, 'opportunity_name': opp_name, 'opportunity_id': opp_id,
                'business_line': s(r.get('业务线')), 'status': s(r.get('当前状态')),
                'source': 'feishu_export_90d',
            }
            items.append(rec); by_id[wid] = rec
            st['new'] += 1
            if body: st['new_body'] += 1
            continue
        changed = False
        for k, v in [('name', title), ('start_time', day)]:
            if v and cur.get(k) != v:
                cur[k] = v; changed = True
        if cust:
            old = cur.get('field_5f20fc') or {}
            old_label = old.get('label') if isinstance(old, dict) else str(old or '')
            old_key = old.get('key') if isinstance(old, dict) else ''
            if old_label != cust or old_key != ckey:
                cur['field_5f20fc'] = {'key': ckey, 'label': cust}; changed = True
        if owner and not (cur.get('owner') or {}).get('name_cn'):
            cur['owner'] = {'name_cn': owner, 'name_en': owner}; st['owner_fill'] += 1; changed = True
        if body and not (cur.get('field_b99055') or '').strip():
            cur['field_b99055'] = body; st['body_fill'] += 1; changed = True
        for k, v in [('opportunity_name', opp_name), ('opportunity_id', opp_id)]:
            if v and not cur.get(k):
                cur[k] = v; changed = True
        st['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)
    with open(DB, 'w', encoding='utf-8') as f:
        json.dump(db, f, ensure_ascii=False)
    print('备份', os.path.basename(bak))
    print('旧库', old_n, '→ 新库', len(items))
    print('新增', st['new'], '更新', st['upd'], '未变', st['same'], '无ID', st['noid'])
    print('补正文', st['body_fill'], '新增带正文', st['new_body'], '补负责人', st['owner_fill'])


if __name__ == '__main__':
    main()