from pathlib import Path
import json
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
BASE=Path('/Users/bot1/Volumes/root_for_ai/AI工作区/良渚文化_月报_2026年5月_20260601_1123')
W=BASE/'work'; D=BASE/'deliverables'
store=json.loads((W/'store_monthly.json').read_text(encoding='utf-8'))
products=json.loads((W/'product_monthly.json').read_text(encoding='utf-8'))
by={(r['平台'],r['月份']):dict(r) for r in store}
# Supplement JD Apr/May refund/units from product rows, same as report generation
for month in ['2026-04','2026-05']:
    r=by.get(('京东',month))
    rows=[x for x in products if x['平台']=='京东' and x['月份']==month]
    if r and rows:
        r['支付件数']=sum((x.get('支付件数') or 0) for x in rows)
        r['退款金额']=sum((x.get('退款金额') or 0) for x in rows)
        r['实际成交金额']=(r.get('支付金额') or 0)-r['退款金额']

def rate(cur, base):
    return None if cur is None or base in [None,0] else (cur-base)/base

def total(rows, month, platform='合计'):
    out={'平台':platform,'月份':month}
    for col in ['访客数','支付金额','退款金额','实际成交金额','支付买家数','支付件数']:
        vals=[r.get(col) for r in rows if r.get(col) is not None]
        out[col]=sum(vals) if vals else None
    out['转化率']=out['支付买家数']/out['访客数'] if out.get('支付买家数') and out.get('访客数') else None
    out['客单价']=out['支付金额']/out['支付买家数'] if out.get('支付金额') and out.get('支付买家数') else None
    return out
platforms=['天猫','淘宝','京东']
may=[by[(p,'2026-05')] for p in platforms]
apr=[by[(p,'2026-04')] for p in platforms]
may_total=total(may,'2026-05','三渠道合计')
apr_total=total(apr,'2026-04','三渠道合计')
# Comparable YoY only for platforms with 2025-05
comp_plats=[p for p in platforms if (p,'2025-05') in by and (p,'2026-05') in by]
may_comp=total([by[(p,'2026-05')] for p in comp_plats],'2026-05','可比渠道合计（天猫+淘宝）')
yoy_comp=total([by[(p,'2025-05')] for p in comp_plats],'2025-05','去年同期可比渠道合计（天猫+淘宝）')

def overview_rows():
    rows=[]
    for p in platforms:
        cur=by.get((p,'2026-05')); prev=by.get((p,'2026-04')); yr=by.get((p,'2025-05'))
        rows.append({
            '渠道':p,'实际成交金额':cur.get('实际成交金额'),'支付/成交金额':cur.get('支付金额'),'退款金额':cur.get('退款金额'),
            '退款率':cur.get('退款金额')/cur.get('支付金额') if cur.get('退款金额') is not None and cur.get('支付金额') else None,
            '访客数':cur.get('访客数'),'支付/成交买家数':cur.get('支付买家数'),'支付/成交件数':cur.get('支付件数'),
            '转化率':cur.get('转化率'),'客单价':cur.get('客单价'),
            '实际成交占比':cur.get('实际成交金额')/may_total['实际成交金额'] if may_total.get('实际成交金额') else None,
            '实际成交环比':rate(cur.get('实际成交金额'), prev.get('实际成交金额') if prev else None),
            '实际成交同比':rate(cur.get('实际成交金额'), yr.get('实际成交金额') if yr else None),
            '支付/成交金额同比':rate(cur.get('支付金额'), yr.get('支付金额') if yr else None),
            '访客数同比':rate(cur.get('访客数'), yr.get('访客数') if yr else None),
            '转化率同比':rate(cur.get('转化率'), yr.get('转化率') if yr else None),
            '备注':'未提供2025年5月同口径店铺数据，暂不做同比' if p=='京东' else ''
        })
    rows.append({'渠道':'三渠道合计','实际成交金额':may_total.get('实际成交金额'),'支付/成交金额':may_total.get('支付金额'),'退款金额':may_total.get('退款金额'),'退款率':may_total.get('退款金额')/may_total.get('支付金额'),
        '访客数':may_total.get('访客数'),'支付/成交买家数':may_total.get('支付买家数'),'支付/成交件数':may_total.get('支付件数'),'转化率':may_total.get('转化率'),'客单价':may_total.get('客单价'),'实际成交占比':1,
        '实际成交环比':rate(may_total.get('实际成交金额'),apr_total.get('实际成交金额')),
        '实际成交同比':None,'支付/成交金额同比':None,'访客数同比':None,'转化率同比':None,
        '备注':'同比因缺少京东2025年5月数据，不做三渠道合计同比；见可比渠道同比行'})
    rows.append({'渠道':'可比渠道合计（天猫+淘宝）','实际成交金额':may_comp.get('实际成交金额'),'支付/成交金额':may_comp.get('支付金额'),'退款金额':may_comp.get('退款金额'),'退款率':may_comp.get('退款金额')/may_comp.get('支付金额'),
        '访客数':may_comp.get('访客数'),'支付/成交买家数':may_comp.get('支付买家数'),'支付/成交件数':may_comp.get('支付件数'),'转化率':may_comp.get('转化率'),'客单价':may_comp.get('客单价'),'实际成交占比':may_comp.get('实际成交金额')/may_total.get('实际成交金额'),
        '实际成交环比':rate(may_comp.get('实际成交金额'), total([by[(p,'2026-04')] for p in comp_plats],'2026-04').get('实际成交金额')),
        '实际成交同比':rate(may_comp.get('实际成交金额'),yoy_comp.get('实际成交金额')),
        '支付/成交金额同比':rate(may_comp.get('支付金额'),yoy_comp.get('支付金额')),
        '访客数同比':rate(may_comp.get('访客数'),yoy_comp.get('访客数')),
        '转化率同比':rate(may_comp.get('转化率'),yoy_comp.get('转化率')),
        '备注':'同比仅包含天猫、淘宝两个有去年同期店铺数据的渠道'})
    return rows

def style(ws):
    header_fill=PatternFill('solid', fgColor='1F4E78'); white=Font(color='FFFFFF', bold=True)
    thin=Side(style='thin', color='CCCCCC'); border=Border(left=thin,right=thin,top=thin,bottom=thin)
    for row in ws.iter_rows():
        for c in row:
            c.border=border; c.alignment=Alignment(vertical='center', wrap_text=True)
    for c in ws[1]:
        c.fill=header_fill; c.font=white; c.alignment=Alignment(horizontal='center', vertical='center')
    for col in ws.columns:
        letter=get_column_letter(col[0].column); max_len=0
        for c in col:
            max_len=max(max_len, min(len(str(c.value)) if c.value is not None else 0,60))
        ws.column_dimensions[letter].width=max(12,min(max_len+2,40))

def write_table(ws, rows):
    headers=list(rows[0].keys())
    ws.append(headers)
    for r in rows: ws.append([r.get(h) for h in headers])
    style(ws)
    for row in ws.iter_rows(min_row=2):
        for i,c in enumerate(row, start=1):
            h=ws.cell(1,i).value
            if h and ('率' in h or '占比' in h or '同比' in h or '环比' in h) and isinstance(c.value,(int,float)):
                c.number_format='0.00%'
            elif h and any(x in h for x in ['金额','客单价']) and isinstance(c.value,(int,float)):
                c.number_format='¥#,##0.00'
            elif h and any(x in h for x in ['访客数','买家数','件数']) and isinstance(c.value,(int,float)):
                c.number_format='#,##0'

xlsx=D/'良渚文化_2026年5月月报数据分析附录.xlsx'
wb=openpyxl.load_workbook(xlsx)
# replace overview sheet
if '01_月度总览' in wb.sheetnames:
    idx=wb.sheetnames.index('01_月度总览')
    del wb['01_月度总览']
    ws=wb.create_sheet('01_月度总览', idx)
else:
    ws=wb.create_sheet('01_月度总览',0)
write_table(ws, overview_rows())
# add/rewrite YoY sheet
if '01b_店铺同比' in wb.sheetnames: del wb['01b_店铺同比']
ws=wb.create_sheet('01b_店铺同比',1)
yoy_rows=[]
for p in comp_plats:
    cur=by[(p,'2026-05')]; yr=by[(p,'2025-05')]
    yoy_rows.append({'渠道':p,'2026年5月实际成交金额':cur.get('实际成交金额'),'2025年5月实际成交金额':yr.get('实际成交金额'),'实际成交同比':rate(cur.get('实际成交金额'),yr.get('实际成交金额')),
                     '2026年5月支付/成交金额':cur.get('支付金额'),'2025年5月支付/成交金额':yr.get('支付金额'),'支付/成交金额同比':rate(cur.get('支付金额'),yr.get('支付金额')),
                     '2026年5月访客数':cur.get('访客数'),'2025年5月访客数':yr.get('访客数'),'访客数同比':rate(cur.get('访客数'),yr.get('访客数')),
                     '2026年5月转化率':cur.get('转化率'),'2025年5月转化率':yr.get('转化率'),'转化率同比':rate(cur.get('转化率'),yr.get('转化率'))})
yoy_rows.append({'渠道':'可比渠道合计（天猫+淘宝）','2026年5月实际成交金额':may_comp.get('实际成交金额'),'2025年5月实际成交金额':yoy_comp.get('实际成交金额'),'实际成交同比':rate(may_comp.get('实际成交金额'),yoy_comp.get('实际成交金额')),
                 '2026年5月支付/成交金额':may_comp.get('支付金额'),'2025年5月支付/成交金额':yoy_comp.get('支付金额'),'支付/成交金额同比':rate(may_comp.get('支付金额'),yoy_comp.get('支付金额')),
                 '2026年5月访客数':may_comp.get('访客数'),'2025年5月访客数':yoy_comp.get('访客数'),'访客数同比':rate(may_comp.get('访客数'),yoy_comp.get('访客数')),
                 '2026年5月转化率':may_comp.get('转化率'),'2025年5月转化率':yoy_comp.get('转化率'),'转化率同比':rate(may_comp.get('转化率'),yoy_comp.get('转化率'))})
yoy_rows.append({'渠道':'京东','2026年5月实际成交金额':by[('京东','2026-05')].get('实际成交金额'),'2025年5月实际成交金额':None,'实际成交同比':None,
                 '2026年5月支付/成交金额':by[('京东','2026-05')].get('支付金额'),'2025年5月支付/成交金额':None,'支付/成交金额同比':None,
                 '2026年5月访客数':by[('京东','2026-05')].get('访客数'),'2025年5月访客数':None,'访客数同比':None,
                 '2026年5月转化率':by[('京东','2026-05')].get('转化率'),'2025年5月转化率':None,'转化率同比':None})
write_table(ws, yoy_rows)
# append note to data口径
if '08_数据口径' in wb.sheetnames:
    ws=wb['08_数据口径']
    ws.append(['去年同期同比','店铺整体同比只对有2025年5月同口径数据的天猫、淘宝计算；京东当前上传店铺月报只含2026年1-5月，缺少2025年5月同口径数据，暂不纳入三渠道同比。'])
    style(ws)
wb.save(xlsx)
# update md with YoY wording
md=D/'良渚文化_2026年5月月报分析稿.md'
text=md.read_text(encoding='utf-8')
old='2026年5月，良渚文化天猫、淘宝、京东三渠道合计支付/成交金额 ¥74,929.61，扣除退款后的实际成交金额 ¥63,688.71，环比4月实际成交增长 60.5%；合计访客数 45,739，环比增长 73.5%。'
new=('2026年5月，良渚文化天猫、淘宝、京东三渠道合计支付/成交金额 ¥74,929.61，扣除退款后的实际成交金额 ¥63,688.71，环比4月实际成交增长 60.5%；合计访客数 45,739，环比增长 73.5%。\n\n'
     '去年同期同比方面：当前表内天猫、淘宝有2025年5月同口径店铺整体数据，京东当前上传文件缺少2025年5月店铺同口径数据，因此同比先按**天猫+淘宝可比渠道口径**计算。天猫+淘宝2026年5月实际成交金额 ¥57,119.61，较2025年5月 ¥110,231.33 同比下降 48.2%；支付/成交金额同比下降 52.4%，访客数同比下降 35.4%，整体转化率同比下降 12.0%。')
text=text.replace(old,new)
text=text.replace('- **天猫**：实际成交 ¥36,113.89，占三渠道 56.7%；支付/成交金额 ¥44,726.39，退款 ¥8,612.50，访客 21,827，转化率 1.75%，实际成交环比 56.6%。',
                  '- **天猫**：实际成交 ¥36,113.89，占三渠道 56.7%；支付/成交金额 ¥44,726.39，退款 ¥8,612.50，访客 21,827，转化率 1.75%；实际成交环比 56.6%，同比去年5月下降 51.4%。')
text=text.replace('- **淘宝**：实际成交 ¥21,005.72，占三渠道 33.0%；支付/成交金额 ¥22,974.12，退款 ¥1,968.40，访客 7,240，转化率 2.76%，实际成交环比 34.3%。',
                  '- **淘宝**：实际成交 ¥21,005.72，占三渠道 33.0%；支付/成交金额 ¥22,974.12，退款 ¥1,968.40，访客 7,240，转化率 2.76%；实际成交环比 34.3%，同比去年5月下降 41.6%。')
text=text.replace('- **京东**：实际成交 ¥6,569.10，占三渠道 10.3%；支付/成交金额 ¥7,229.10，退款 ¥660.00，访客 16,672，转化率 0.06%，实际成交环比 567.6%。',
                  '- **京东**：实际成交 ¥6,569.10，占三渠道 10.3%；支付/成交金额 ¥7,229.10，退款 ¥660.00，访客 16,672，转化率 0.06%；实际成交环比 567.6%。当前上传的京东店铺月报缺少2025年5月同口径数据，暂不做同比。')
text=text.replace('- 京东退款金额和成交件数由商品明细汇总补充，店铺月报原表未提供退款字段。',
                  '- 京东退款金额和成交件数由商品明细汇总补充，店铺月报原表未提供退款字段。\n- 店铺整体同比只看店铺总汇总，不延伸到商品维度、推广维度；同比目前按天猫+淘宝可比口径计算，京东缺去年同期店铺数据。')
md.write_text(text,encoding='utf-8')
# save summary for final
summary={'comparable_platforms':comp_plats,'may_comp':may_comp,'yoy_comp':yoy_comp,'actual_yoy':rate(may_comp.get('实际成交金额'),yoy_comp.get('实际成交金额')),'pay_yoy':rate(may_comp.get('支付金额'),yoy_comp.get('支付金额')),'uv_yoy':rate(may_comp.get('访客数'),yoy_comp.get('访客数')),'conv_yoy':rate(may_comp.get('转化率'),yoy_comp.get('转化率'))}
(W/'yoy_update_summary.json').write_text(json.dumps(summary,ensure_ascii=False,indent=2),encoding='utf-8')
print(xlsx)
print(md)
print(json.dumps(summary,ensure_ascii=False))