from pathlib import Path
import datetime as dt
import re, math, copy
import xlrd
import openpyxl
from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

PROJECT = Path('/Users/bot1/Volumes/root_for_ai/AI工作区/良渚_周报_20260608-0614_20260615_0835')
SRC = PROJECT / 'source'
TEMPLATE = Path('/Users/bot1/.hermes/profiles/operations/cache/documents/doc_2c7a889008fb_良渚周报_2026-06-01_06-07.xlsx')
OUT = PROJECT / 'deliverables' / '良渚周报_2026-06-08_06-14.xlsx'
CUR_START, CUR_END = dt.date(2026,6,8), dt.date(2026,6,14)
PREV_START, PREV_END = dt.date(2026,6,1), dt.date(2026,6,7)
CUR_LABEL = '6.8-6.14'

ACTIONS = ['热销产品详情页更新', '618推广计划里的商品更新', '推广图更新', '已规划的产品继续开发']


def n(x):
    if x is None: return 0.0
    if isinstance(x, (int, float)):
        if isinstance(x, float) and math.isnan(x): return 0.0
        return float(x)
    s = str(x).strip().replace(',', '')
    if s in ('', '-', '--', '—', 'nan', 'None'):
        return 0.0
    if s.endswith('%'):
        try: return float(s[:-1]) / 100
        except Exception: return 0.0
    try: return float(s)
    except Exception: return 0.0


def fmt_num(x, digits=2):
    if x is None: return ''
    if isinstance(x, (int, float)):
        return round(float(x), digits)
    return x


def to_date(x):
    if isinstance(x, dt.datetime): return x.date()
    if isinstance(x, dt.date): return x
    if isinstance(x, (int, float)):
        if x > 20000000:
            return dt.datetime.strptime(str(int(x)), '%Y%m%d').date()
        try:
            return xlrd.xldate.xldate_as_datetime(x, 0).date()
        except Exception:
            return None
    s = str(x).strip().split()[0]
    if re.fullmatch(r'\d{8}\.0', s): s = s[:-2]
    for f in ('%Y-%m-%d', '%Y/%m/%d', '%Y%m%d'):
        try: return dt.datetime.strptime(s, f).date()
        except Exception: pass
    return None


def file_contains(key):
    m = [p for p in SRC.iterdir() if key in p.name]
    if not m: raise FileNotFoundError(key)
    return m[0]


def read_xlsx(path):
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb[wb.sheetnames[0]]
    rows = list(ws.iter_rows(values_only=True))
    headers = [str(x).strip() if x is not None else '' for x in rows[0]]
    return [dict(zip(headers, r)) for r in rows[1:] if any(v is not None and str(v).strip() for v in r)]


def read_xls(path):
    wb = xlrd.open_workbook(path)
    sh = wb.sheet_by_index(0)
    hi = 0
    for i in range(min(20, sh.nrows)):
        vals = [str(sh.cell_value(i,j)).strip() for j in range(sh.ncols)]
        if any(v in vals for v in ['统计日期','日期','时间']):
            hi = i; break
    headers = [str(sh.cell_value(hi,j)).strip() for j in range(sh.ncols)]
    out = []
    for i in range(hi+1, sh.nrows):
        vals = [sh.cell_value(i,j) for j in range(sh.ncols)]
        if any(str(v).strip() for v in vals): out.append(dict(zip(headers, vals)))
    return out


def in_period(d, start, end):
    return d and start <= d <= end

# Read source groups
tmall_daily_raw = read_xlsx(file_contains('天猫分天'))
taobao_daily_raw = read_xlsx(file_contains('淘宝分天'))
jd_trans_raw = read_xlsx(file_contains('交易概况'))
jd_traffic_raw = read_xls(file_contains('京东分天'))
tmall_prod_raw = read_xls(file_contains('天猫-周'))
taobao_prod_raw = read_xls(file_contains('淘宝-周'))

# Daily store sections

def tm_tb_store_rows(raw, platform, start=CUR_START, end=CUR_END):
    rows = []
    for r in raw:
        d = to_date(r.get('统计日期'))
        if not in_period(d, start, end): continue
        if platform == '天猫':
            rows.append([
                d, int(n(r.get('访客数'))), int(n(r.get('浏览量'))), fmt_num(n(r.get('客单价')), 2), int(n(r.get('加购人数'))),
                n(r.get('支付转化率')), fmt_num(n(r.get('平均停留时长')), 2), n(r.get('跳失率')),
                fmt_num(n(r.get('成功退款金额')), 2), fmt_num(n(r.get('支付金额')), 2), int(n(r.get('支付件数'))), int(n(r.get('支付买家数'))),
                fmt_num(n(r.get('描述相符评分')), 4), fmt_num(n(r.get('物流服务评分')), 4)
            ])
        else:
            pay = n(r.get('支付金额')); refund = n(r.get('成功退款金额'))
            rows.append([
                d, int(n(r.get('访客数'))), int(n(r.get('浏览量'))), fmt_num(n(r.get('客单价')), 2), int(n(r.get('加购件数'))),
                n(r.get('支付转化率')), fmt_num(n(r.get('平均停留时长')), 2), n(r.get('跳失率')),
                fmt_num(refund, 2), fmt_num(pay, 2), int(n(r.get('支付件数'))), int(n(r.get('支付买家数'))), int(n(r.get('支付买家数'))), fmt_num(pay - refund, 2)
            ])
    return sorted(rows, key=lambda x: x[0])


def jd_rows(start=CUR_START, end=CUR_END):
    by = {}
    for r in jd_trans_raw:
        d = to_date(r.get('时间'))
        if not in_period(d,start,end): continue
        by.setdefault(d,{})['trans'] = r
    for r in jd_traffic_raw:
        d = to_date(r.get('日期'))
        if not in_period(d,start,end): continue
        by.setdefault(d,{})['traffic'] = r
    out = []
    for d in sorted(by):
        tr = by[d].get('trans', {})
        tf = by[d].get('traffic', {})
        uv = n(tf.get('访客数-全部渠道')) or n(tr.get('店铺访客数'))
        pv = n(tf.get('浏览量-全部渠道')) or n(tr.get('店铺浏览量'))
        pay = n(tr.get('成交金额'))
        refund = n(tr.get('退款金额'))
        buyers = n(tr.get('成交客户数'))
        units = n(tr.get('成交商品件数'))
        unit_price = pay / buyers if buyers else ''
        conv = buyers / uv if uv and buyers else ''
        out.append([
            d, int(uv), int(pv), fmt_num(unit_price,2) if unit_price!='' else '', int(n(tr.get('加购商品件数'))), conv,
            fmt_num(n(tf.get('平均停留时长(秒)-全部渠道')) or n(tr.get('店铺平均停留时长')), 2), '', fmt_num(refund,2), fmt_num(pay,2), int(units), int(buyers), int(buyers), fmt_num(pay-refund,2)
        ])
    return out


def product_top(raw, platform, limit=15):
    items = []
    for r in raw:
        name = str(r.get('商品名称') or '').strip()
        if not name: continue
        pay = n(r.get('支付金额')); refund = n(r.get('成功退款金额')); actual = pay - refund
        if pay <= 0 and actual <= 0: continue
        items.append((actual, pay, {
            '统计日期': to_date(r.get('统计日期')) or CUR_END,
            '商品ID': str(r.get('商品ID') or '').strip().replace('.0',''),
            '商品名称': name,
            '货号': str(r.get('货号') or '').strip(),
            '商品加购件数': int(n(r.get('商品加购件数'))),
            '商品加购人数': int(n(r.get('商品加购人数'))),
            '下单买家数': int(n(r.get('下单买家数'))),
            '下单件数': int(n(r.get('下单件数'))),
            '下单金额': fmt_num(n(r.get('下单金额')), 2),
            '下单转化率': n(r.get('下单转化率')),
            '支付买家数': int(n(r.get('支付买家数'))),
            '支付件数': int(n(r.get('支付件数'))),
            '支付金额': fmt_num(pay, 2),
            '商品支付转化率': n(r.get('商品支付转化率')),
            '成功退款金额': fmt_num(refund, 2),
            '实际成交金额': fmt_num(actual, 2),
            '平台': platform,
        }))
    items.sort(reverse=True, key=lambda x: (x[0], x[1]))
    cols = ['统计日期','商品ID','商品名称','货号','商品加购件数','商品加购人数','下单买家数','下单件数','下单金额','下单转化率','支付买家数','支付件数','支付金额','商品支付转化率','成功退款金额','实际成交金额']
    return [[item[2][c] for c in cols] for item in items[:limit]], [item[2] for item in items[:limit]]

# Aggregates for summary sheet

def aggregate_store(platform, period):
    if period == 'cur': start,end = CUR_START,CUR_END
    else: start,end = PREV_START,PREV_END
    if platform in ('天猫','淘宝'):
        raw = tmall_daily_raw if platform=='天猫' else taobao_daily_raw
        out = {'uv':0,'pv':0,'pay':0,'refund':0,'actual':0,'units':0,'buyers':0}
        for r in raw:
            d = to_date(r.get('统计日期'))
            if not in_period(d,start,end): continue
            out['uv'] += n(r.get('访客数')); out['pv'] += n(r.get('浏览量')); out['pay'] += n(r.get('支付金额')); out['refund'] += n(r.get('成功退款金额'))
            out['units'] += n(r.get('支付件数')); out['buyers'] += n(r.get('支付买家数'))
        out['actual'] = out['pay'] - out['refund']
        return out
    else:
        out = {'uv':0,'pv':0,'pay':0,'refund':0,'actual':0,'units':0,'buyers':0}
        # traffic for uv/pv; transaction for sales/refund
        for r in jd_traffic_raw:
            d = to_date(r.get('日期'))
            if in_period(d,start,end):
                out['uv'] += n(r.get('访客数-全部渠道')); out['pv'] += n(r.get('浏览量-全部渠道'))
        for r in jd_trans_raw:
            d = to_date(r.get('时间'))
            if in_period(d,start,end):
                out['pay'] += n(r.get('成交金额')); out['refund'] += n(r.get('退款金额')); out['units'] += n(r.get('成交商品件数')); out['buyers'] += n(r.get('成交客户数'))
        out['actual'] = out['pay'] - out['refund']
        return out


def rate(cur, prev):
    return (cur - prev) / prev if prev else ''

cur = {p: aggregate_store(p,'cur') for p in ['天猫','淘宝','京东']}
prev = {p: aggregate_store(p,'prev') for p in ['天猫','淘宝','京东']}
cur['三店合计'] = {k: sum(cur[p][k] for p in ['天猫','淘宝','京东']) for k in cur['天猫']}
prev['三店合计'] = {k: sum(prev[p][k] for p in ['天猫','淘宝','京东']) for k in prev['天猫']}

def signed_pct(x):
    if x == '' or x is None: return '—'
    return ('+' if x >= 0 else '') + f'{x*100:.2f}%'

def money(x): return f'{x:,.2f}'

def short(s, l=28):
    s=str(s)
    return s if len(s)<=l else s[:l]+'…'

# Load template, clear values, rename sheets
wb = load_workbook(TEMPLATE)
ws = wb[wb.sheetnames[0]]
ws.title = '周报6.8-6.14'
ws2 = wb[wb.sheetnames[1]] if len(wb.sheetnames)>1 else wb.create_sheet('6.8-6.14数据增长与主要动作')
ws2.title = '6.8-6.14数据增长与主要动作'
for extra in wb.sheetnames[2:]:
    del wb[extra]

for sheet in [ws, ws2]:
    for row in sheet.iter_rows():
        for cell in row:
            if isinstance(cell, MergedCell): continue
            cell.value = None

# First sheet content
sec_fill = PatternFill('solid', fgColor='1F4E78')
header_fill = PatternFill('solid', fgColor='D9EAF7')
white_font = Font(color='FFFFFF', bold=True)
bold_font = Font(bold=True)
thin = Side(style='thin', color='D9D9D9')
border = Border(left=thin,right=thin,top=thin,bottom=thin)

def write_row(sheet, r, values):
    for c,v in enumerate(values,1):
        sheet.cell(r,c).value = v

# Section 1 Tmall
write_row(ws,1,[f'天猫｜店铺整体数据（{CUR_START} 至 {CUR_END}）'])
write_row(ws,3,['统计日期','访客数','浏览量','客单价','加购人数','支付转化率','平均停留时长','跳失率','成功退款金额','支付金额','支付件数','支付买家数','店铺商品体验分','店铺物流体验分'])
for i,row in enumerate(tm_tb_store_rows(tmall_daily_raw,'天猫'),4): write_row(ws,i,row)
write_row(ws,12,[f'天猫｜单品数据（{CUR_START} 至 {CUR_END}，按实际成交金额TOP15）'])
prod_headers=['统计日期','商品ID','商品名称','货号','商品加购件数','商品加购人数','下单买家数','下单件数','下单金额','下单转化率','支付买家数','支付件数','支付金额','商品支付转化率','成功退款金额','实际成交金额']
write_row(ws,14,prod_headers)
tmall_prod_rows, tmall_top = product_top(tmall_prod_raw,'天猫')
for i,row in enumerate(tmall_prod_rows,15): write_row(ws,i,row)
# Taobao
write_row(ws,31,[f'淘宝｜店铺整体数据（{CUR_START} 至 {CUR_END}）'])
write_row(ws,33,['日期','UV','PV','客单价','加购件数','转化率','人均停留时长(秒)','跳失率','退款金额','支付金额','支付商品件数','支付买家数','真实支付买家数','真实成交金额'])
for i,row in enumerate(tm_tb_store_rows(taobao_daily_raw,'淘宝'),34): write_row(ws,i,row)
write_row(ws,42,[f'淘宝｜单品数据（{CUR_START} 至 {CUR_END}，按实际成交金额TOP15）'])
write_row(ws,44,prod_headers)
taobao_prod_rows, taobao_top = product_top(taobao_prod_raw,'淘宝')
for i,row in enumerate(taobao_prod_rows,45): write_row(ws,i,row)
# JD
write_row(ws,61,[f'京东｜流量数据 + 交易数据（{CUR_START} 至 {CUR_END}）'])
write_row(ws,63,['日期','UV','PV','客单价','加购件数/客户数','转化率','人均停留时长(秒)','跳失率','退款金额','支付金额','支付商品件数','支付买家数','真实支付买家数','真实成交金额'])
for i,row in enumerate(jd_rows(),64): write_row(ws,i,row)

# Styling first sheet
for r in [1,12,31,42,61]:
    for c in range(1,17):
        cell=ws.cell(r,c); cell.fill=sec_fill; cell.font=white_font; cell.alignment=Alignment(horizontal='left', vertical='center')
for r in [3,14,33,44,63]:
    for c in range(1,17):
        cell=ws.cell(r,c); cell.fill=header_fill; cell.font=bold_font; cell.alignment=Alignment(horizontal='center', vertical='center', wrap_text=True)
for row in ws.iter_rows(min_row=1, max_row=70, min_col=1, max_col=16):
    for cell in row:
        cell.border=border; cell.alignment = Alignment(vertical='top', wrap_text=True)
        if isinstance(cell.value, dt.date): cell.number_format='yyyy-mm-dd'
# percentage cols
for col in [6,8,10,14]:
    pass
for col in [6,8]:
    for r in list(range(4,11))+list(range(34,41))+list(range(64,71)):
        ws.cell(r,col).number_format='0.00%'
for col in [10,14]:
    for r in list(range(15,30))+list(range(45,60)):
        ws.cell(r,col).number_format='0.00%'
for c,w in {1:14,2:10,3:38,4:14,5:12,6:12,7:14,8:12,9:14,10:14,11:12,12:12,13:14,14:14,15:14,16:14}.items():
    ws.column_dimensions[get_column_letter(c)].width=w
ws.sheet_view.showGridLines=False

# Second sheet fixed summary format
write_row(ws2,1,[f'良渚文化三店 {CUR_LABEL} 数据增长与主要优化动作'])
actual_rate = rate(cur['三店合计']['actual'], prev['三店合计']['actual'])
write_row(ws2,2,[f'说明：金额分析采用“实际成交金额 = 支付金额 - 退款金额”口径；京东退款金额取自用户提供的“交易概况”报表。三店本周实际成交金额 {money(cur["三店合计"]["actual"])} 元，较上周 {signed_pct(actual_rate)}。'])
write_row(ws2,3,['一、店铺数据增长点（所有字段已标明名称，金额为实际成交口径）'])
write_row(ws2,4,['店铺','本周访客数UV','上周访客数UV','UV环比','本周支付金额','本周退款金额','本周实际成交金额=支付金额-退款金额','上周实际成交金额','实际成交金额环比','本周支付/成交件数','支付/成交件数环比','数据判断'])

def judgment(p):
    uv_r=rate(cur[p]['uv'],prev[p]['uv']); act_r=rate(cur[p]['actual'],prev[p]['actual']); unit_r=rate(cur[p]['units'],prev[p]['units'])
    if p=='天猫':
        return f'UV环比{signed_pct(uv_r)}，实际成交金额环比{signed_pct(act_r)}；本周退款金额{money(cur[p]["refund"])}元，较上周下降，玉琮摆件、香氛、遮阳伞等商品带动净成交增长。'
    if p=='淘宝':
        return f'UV环比{signed_pct(uv_r)}，实际成交金额环比{signed_pct(act_r)}；退款金额降至{money(cur[p]["refund"])}元，玉琮王摆件和遮阳伞继续贡献主要成交。'
    if p=='京东':
        return f'UV环比{signed_pct(uv_r)}，实际成交金额环比{signed_pct(act_r)}；本周无退款，但成交金额较低，需要继续补充京东商品明细并加强商品承接。'
    return f'三店实际成交金额环比{signed_pct(act_r)}；增长主要由天猫和淘宝贡献，京东成交仍需重点跟进。'
for row_i,p in enumerate(['天猫','淘宝','京东','三店合计'],5):
    write_row(ws2,row_i,[p, int(cur[p]['uv']), int(prev[p]['uv']), rate(cur[p]['uv'],prev[p]['uv']), fmt_num(cur[p]['pay'],2), fmt_num(cur[p]['refund'],2), fmt_num(cur[p]['actual'],2), fmt_num(prev[p]['actual'],2), rate(cur[p]['actual'],prev[p]['actual']), int(cur[p]['units']), rate(cur[p]['units'],prev[p]['units']), judgment(p)])
write_row(ws2,10,['二、商品销售增长点（按实际成交金额=支付金额-退款金额排序）'])
write_row(ws2,11,['店铺','排名','商品ID','商品名称','支付金额','退款金额','实际成交金额=支付金额-退款金额','支付件数','说明'])
rr=12
for platform, tops in [('天猫', tmall_top[:5]), ('淘宝', taobao_top[:5])]:
    for i,p in enumerate(tops,1):
        desc = '实际成交金额TOP商品，用于判断本周商品贡献。'
        write_row(ws2,rr,[platform,i,p['商品ID'],p['商品名称'],p['支付金额'],p['成功退款金额'],p['实际成交金额'],p['支付件数'],desc])
        rr += 1
write_row(ws2,rr,['京东','-','-','本次未提供京东商品明细','-','-','-','-',''])
write_row(ws2,24,['三、本周主要优化动作/数据对应动作'])
write_row(ws2,25,['序号','优化动作','涉及店铺','当前状态','对应数据观察口径','本周数据对应关系','后续观察指标'])
activity_rows = [
    [1,'热销产品详情页更新','天猫/淘宝','已执行','TOP商品实际成交金额、支付转化率、退款金额',f'天猫TOP商品实际成交最高为{short(tmall_top[0]["商品名称"])}，淘宝TOP商品为{short(taobao_top[0]["商品名称"])}；详情页承接继续围绕高成交商品跟踪。','TOP商品访客数、支付转化率、退款率、实际成交金额'],
    [2,'618推广计划里的商品更新','天猫/淘宝/京东','已执行','支付金额、实际成交金额、支付/成交件数',f'三店本周实际成交金额{money(cur["三店合计"]["actual"])}元，环比{signed_pct(actual_rate)}；天猫实际成交环比{signed_pct(rate(cur["天猫"]["actual"],prev["天猫"]["actual"]))}。','推广商品成交、加购、支付转化、退款金额'],
    [3,'推广图更新','天猫/淘宝','已执行','UV/PV、商品访客、支付转化率',f'本周天猫UV环比{signed_pct(rate(cur["天猫"]["uv"],prev["天猫"]["uv"]))}，淘宝UV环比{signed_pct(rate(cur["淘宝"]["uv"],prev["淘宝"]["uv"]))}；后续看素材更新后的点击与成交承接。','商品访客数、点击/访问、支付转化率、UV价值'],
    [4,'已规划的产品继续开发','天猫/淘宝/京东','持续推进','商品结构、TOP商品、客单价',f'本周高客单玉琮/香氛/伞类商品仍为主要成交贡献，后续产品开发可继续围绕已验证品类推进。','新品上架后的访客、加购、转化、实际成交金额'],
    [5,'京东成交承接复盘','京东','建议重点跟进','京东成交金额、成交件数、商品明细',f'京东本周实际成交金额{money(cur["京东"]["actual"])}元，环比{signed_pct(rate(cur["京东"]["actual"],prev["京东"]["actual"]))}；当前缺京东商品明细，难以定位具体商品原因。','京东商品明细、退款/成交订单、商品转化率'],
]
for i,row in enumerate(activity_rows,26): write_row(ws2,i,row)

# style second sheet
for r in [1,2,3,10,24]:
    for c in range(1,13):
        cell=ws2.cell(r,c); cell.fill=sec_fill if r in [1,3,10,24] else PatternFill('solid', fgColor='EAF2F8'); cell.font=white_font if r in [1,3,10,24] else bold_font; cell.alignment=Alignment(vertical='center', wrap_text=True)
for r in [4,11,25]:
    for c in range(1,13):
        cell=ws2.cell(r,c); cell.fill=header_fill; cell.font=bold_font; cell.alignment=Alignment(horizontal='center', vertical='center', wrap_text=True)
for row in ws2.iter_rows(min_row=1, max_row=31, min_col=1, max_col=12):
    for cell in row:
        cell.border=border; cell.alignment=Alignment(vertical='top', wrap_text=True)
# percent columns second sheet
for r in range(5,9):
    for c in [4,9,11]: ws2.cell(r,c).number_format='0.00%'
for c,w in {1:12,2:16,3:16,4:12,5:14,6:14,7:20,8:18,9:14,10:16,11:16,12:58}.items():
    ws2.column_dimensions[get_column_letter(c)].width=w
ws2.sheet_view.showGridLines=False

# Fix merged title ranges if template ranges exist; title values use top-left.
# Save and verify
OUT.parent.mkdir(parents=True, exist_ok=True)
wb.save(OUT)

vwb = load_workbook(OUT, data_only=True)
assert vwb.sheetnames == ['周报6.8-6.14','6.8-6.14数据增长与主要动作']
assert OUT.stat().st_size > 0
# representative checks
s1=vwb['周报6.8-6.14']; s2=vwb['6.8-6.14数据增长与主要动作']
assert s1['A1'].value.startswith('天猫｜店铺整体数据')
assert s1['A61'].value.startswith('京东｜流量数据')
assert s2['A1'].value.startswith('良渚文化三店 6.8-6.14')
print(OUT)
print('size', OUT.stat().st_size)
print('sheets', vwb.sheetnames)
print('total_actual', round(cur['三店合计']['actual'],2), 'rate', actual_rate)
print('tmall_actual', round(cur['天猫']['actual'],2))
print('taobao_actual', round(cur['淘宝']['actual'],2))
print('jd_actual', round(cur['京东']['actual'],2))
print('top_tmall', tmall_top[0]['商品名称'], tmall_top[0]['实际成交金额'])
print('top_taobao', taobao_top[0]['商品名称'], taobao_top[0]['实际成交金额'])