#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把「全景视图导出-销售管理」活动导出合并进工作台活动库。

来源：~/Desktop/hermas输入-工作台数据库/飞书活动导出/全景视图导出-销售管理（...）.xlsx
目标：数据/daily_activities_mcp.json（工作台活动基础库）
规则：
  1) 用「工作项链接」末尾数字作为活动唯一键 work_item_id，与库内对齐；
  2) 已存在 → 只补/更新 标题(name)、客户(field_5f20fc)、创建时间(start_time)、商机字段；
     不覆盖正文 field_b99055（导出没有正文）；
  3) 不存在 → 新增记录，正文留空并在 source 上标注 'export_no_body'；
  4) 先备份，再写回；打印新增/更新/未变计数。
用法：python3 脚本/merge_activity_export.py [xlsx路径]
"""
import json
import os
import re
import shutil
import sys
import time

import openpyxl

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
DB = os.path.join(ROOT, '数据', 'daily_activities_mcp.json')
DEFAULT_XLSX = os.path.join(
    os.path.expanduser('~'), 'Desktop', 'hermas输入-工作台数据库', '飞书活动导出',
    '全景视图导出-销售管理（20261022往前倒推60天内的活动导出）.xlsx')

xlsx = sys.argv[1] if len(sys.argv) > 1 else DEFAULT_XLSX

wb = openpyxl.load_workbook(xlsx, data_only=True)
ws = wb.active
rows = list(ws.iter_rows(values_only=True))
H = [str(x or '').strip() for x in rows[0]]
ix = {h: j for j, h in enumerate(H)}


def cell(r, name):
    j = ix.get(name)
    return r[j] if j is not None else None


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


def d10(v):
    s = str(v or '').strip()
    return s[:10] if s else ''


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

stat = {'new': 0, 'upd': 0, 'same': 0, 'noid': 0, 'nobody': 0}
for r in rows[1:]:
    wid = key_of(cell(r, '工作项链接'))
    if not wid:
        stat['noid'] += 1
        continue
    title = str(cell(r, '标题') or '').strip()
    cust_name = str(cell(r, '活动关联客户') or '').strip()
    cust_key = str(cell(r, '活动关联客户 (ID)') or '').strip()
    create_d = d10(cell(r, '创建时间'))
    opp_name = str(cell(r, '活动对应商机') or '').strip()
    opp_id = str(cell(r, '活动对应商机 (ID)') or '').strip()
    owner_name = str(cell(r, '客户-所属人') or '').strip()

    cur = by_id.get(wid)
    if cur is None:
        rec = {
            'name': title,
            'start_time': create_d,
            'field_b99055': '',
            'field_5f20fc': {'key': cust_key, 'label': cust_name} if cust_name else None,
            'field_76654e': None,
            'field_5ed7ab': None,
            'owner': {'name_cn': owner_name} if owner_name else None,
            'work_item_id': wid,
            'opportunity_name': opp_name,
            'opportunity_id': opp_id,
            'source': 'export_no_body',
        }
        items.append(rec)
        by_id[wid] = rec
        stat['new'] += 1
        stat['nobody'] += 1
        continue

    changed = False
    if title and cur.get('name') != title:
        cur['name'] = title
        changed = True
    if cust_name:
        old = cur.get('field_5f20fc') or {}
        old_label = old.get('label') if isinstance(old, dict) else str(old or '')
        if old_label != cust_name or (old.get('key') if isinstance(old, dict) else '') != cust_key:
            cur['field_5f20fc'] = {'key': cust_key, 'label': cust_name}
            changed = True
    if create_d and str(cur.get('start_time') or '')[:10] != create_d:
        cur['start_time'] = create_d
        changed = True
    if opp_name or opp_id:
        if cur.get('opportunity_id') != opp_id or cur.get('opportunity_name') != opp_name:
            cur['opportunity_id'] = opp_id
            cur['opportunity_name'] = opp_name
            changed = True
    if not cur.get('field_b99055'):
        stat['nobody'] += 1
    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('导出 %d 行 ｜ 新增 %d ｜ 更新 %d ｜ 未变 %d ｜ 无ID %d ｜ 无正文 %d' % (
    len(rows) - 1, stat['new'], stat['upd'], stat['same'], stat['noid'], stat['nobody']))
print('库内总数', len(items))