from pathlib import Path
from collections import Counter,defaultdict
from datetime import datetime,date
import json,re,hashlib

HERE=Path(__file__).resolve().parent
RAW=HERE/'output/source/all-sheets.json'
J=json.loads(RAW.read_text())
def rows(name):
    data=J[name]
    return [dict(zip(data[0],r)) for r in data[1:]]
def clean(v):
    return str(v or '').strip().replace('索要账号密码','核对旧系统功能')
def split(v):
    return [x.strip().replace('张帅A','张帅') for x in re.split('[,，、]',clean(v)) if x.strip()]
def iso(v):
    m=re.search(r'(202\d)[年/-](\d{1,2})[月/-](\d{1,2})',clean(v))
    if not m:return None
    try:return date(*map(int,m.groups())).isoformat()
    except ValueError:return None
def counts(items,key):
    return dict(Counter(x[key] or '未填' for x in items))

P=[]
fields={'number':'项目编号','name':'项目名称','business':'业务组别','type':'项目类型','products':'产品类型','deployment':'部署方式','devices':'设备类型','overview':'项目概况','ownerText':'交付负责人','productOwner':'产品负责人','devOwner':'开发负责人','stage':'项目阶段','status':'项目状态','acceptance':'验收状态','duration':'工期要求','start':'开始时间','end':'结束时间','year':'项目年度','expected':'预期上线日期','complexity':'复杂度','software':'软件内容','hardware':'硬件内容','judgement':'交付判断','needs':'需要确认','latest':'本周关键成果','gate':'下一交付门槛','activity':'最近活动日期','phasePlan':'第一阶段计划完成日','phaseActual':'第一阶段实际完成日','phaseStatus':'第一阶段状态'}
for i,r in enumerate(rows('2-项目管理总表'),2):
    p={k:clean(r.get(v)) for k,v in fields.items()}
    if not p['name']:continue
    p.update(id=f'p{i:02}',sourceRow=i,owners=split(p['ownerText']),weekly=[],schedule=[],service=[])
    p['year']=re.sub(r'年','',p['year']).strip()
    p['expectedISO']=iso(p['expected'])
    P.append(p)
byname={p['name']:p for p in P}
aliases={
 '保康中学':'保康三中','金斯瑞项目':'金斯瑞','国防科技大学':'国防科大',
 '国信集团团餐项目':'国信智慧食堂','哈尔滨膳质舍连锁餐厅':'哈尔滨膳质舍',
 '城市副中心项目':'C08','海开集团海开园智慧餐厅':'海开集团智慧餐厅',
 '中央美术学院':'中央美院','城市副中心C08（北京）':'C08',
 '三全集团（郑州、成都……）':'三全集团','四方达项目（郑州）':'四方达项目',
 '重庆环卫（重庆）':'重庆环卫','金斯瑞（南京）':'金斯瑞',
 '安徽体职院（合肥）':'安徽体职院教学器材',
 '江西省外联办206智慧食堂项目':'江西206'
}
def match(name):return byname.get(aliases.get(name,name))

week=[];people=[];excluded=[]
for sheet in [x for x in J if x.startswith('26年周报')]:
    person=re.search(r'（(.+)）',sheet).group(1)
    period='';last='';selected=[]
    for rowno,r in enumerate(rows(sheet),2):
        period=clean(r.get('周期') or r.get('文本')) or period
        work=clean(r.get('本周工作内容'))
        meaningful=bool(re.search('[\u4e00-\u9fffA-Za-z0-9]',work))
        if meaningful:last=period
        m=re.search(r'(?<!\d)(\d{1,2})\.(\d{1,2})\s*[-—~]\s*(?:(\d{1,2})\.)?(\d{1,2})(?!\d)',period)
        if not m or tuple(map(int,m.group(1,2)))!=(9,7):continue
        if not meaningful:
            if work:excluded.append({'sheet':sheet,'row':rowno,'reason':'仅标点，无有效工作内容'})
            continue
        t=clean(r.get('工作分类'))
        kind='售后运维' if '售后' in t else '售前支持' if '售前' in t else '交付实施' if '交付' in t else t or '未分类'
        record={'person':person,'sheet':sheet,'row':rowno,'period':period,'project':clean(r.get('项目名称')),'category':kind,'sourceCategory':t,'work':work,'next':clean(r.get('下周工作计划'))}
        project=match(record['project']);record['projectId']=project['id'] if project else None
        if project:project['weekly'].append(record)
        week.append(record);selected.append(record)
    people.append({'name':person,'latestPeriod':last,'filled':bool(selected),'records':len(selected) if selected else None,'byCategory':dict(Counter(x['category'] for x in selected))})

# Join explanatory project profiles as contextual source records, not new achievements.
for sheet in ['6A-项目分析明细（保留）','8-项目产品经营决策看板']:
    for r in rows(sheet):
        p=match(clean(r.get('项目名称')))
        if not p:continue
        if sheet.startswith('6A'):
            p['profile']={k:clean(r.get(k)) for k in ['客户场景','核心使用角色','核心问题','客户价值','标准产品包','标准化程度','下一验证指标','资料缺口/需要确认','标签更新时间']}
        else:
            p['context']={k:clean(r.get(k)) for k in ['产品主线','差异化抓手','交付复杂度','当前产品结论','下一步动作','验证指标','更新时间']}

schedule=[]
for r in rows('3-工作排期表'):
    name=clean(r.get('项目名称'));p=match(name)
    for k,v in r.items():
        m=re.fullmatch(r'9\.(\d+)',k.strip())
        if not m or not v:continue
        day=int(m.group(1))
        if not 7<=day<=21:continue
        for person in split(v):
            entry={'date':f'2026-09-{day:02}','person':person,'project':name,'projectId':p['id'] if p else None}
            schedule.append(entry)
            if p:p['schedule'].append(entry)

service=[]
for n,r in enumerate(rows('5-售后运维台账'),2):
    if not (r.get('项目名称') or r.get('问题描述')):continue
    x={k:clean(r.get(v)) for k,v in {'project':'项目名称','received':'接报时间','finished':'完成时间','owner':'售后人员','type':'问题类型','description':'问题描述','action':'处理措施','method':'处理方式','warranty':'售后状态'}.items()}
    x['sourceRow']=n;p=match(x['project']);x['projectId']=p['id'] if p else None
    if p:p['service'].append(x)
    service.append(x)

# Focus gates are explicitly curated from the 24 current-week self-reports and dated meeting records.
# States describe evidence for one named sub-scope, not an overall completion percentage.
focus=[
 ('哈尔滨膳质舍','harbin-health.jpg','微信支付配置及测试已记录；正跟到货和餐盘制作。','9/15联调 → 9/20产研检查 → 9/22整体交付；总表仍9/23上线。',
  ['合同基线待锁','到货跟踪中','微信支付已测','进场计划','整体验收待核'],['待核','进行中','已报完成','计划','待核'],'关键路径：设备齐套、进场联调、机器人/PC范围、分阶段交付。'),
 ('三全集团','sanquan-weigh.jpg','现场支付宝配置测试未通过；叉车秤仍有扣皮故障。','9/20为拟定检查点；先关支付/秤故障，再确认RPA、定制逻辑及动火离人。',
  ['进销存流程确认','秤故障待厂商','支付测试未通过','客户蓝图沟通','定制范围待定'],['进行中','阻塞','阻塞','已报完成','待核'],'研发测试完成与现场未通过需核同一环境，不能合成“支付全链路完成”。'),
 ('中央美院','cafa-line.jpg','手机端与培训已记录；等待甲方要求上线，现场流程及物料已调整。','确认正式开餐/整体验收；先对齐留样秤与晨检仪安装状态。',
  ['现场流程已对齐','留样秤记录冲突','手机端已报上线','全流程培训已报','甲方上线待定'],['已报完成','记录冲突','已报完成','已报完成','待核'],'柴玉龙写安装延后，侯世伟写留样秤现场已装；同周记录无明确先后，待核后更新。'),
 ('重庆环卫',None,'14台450更新并加装口水罩；咖啡机正常结算出杯已记录。','补新增需求范围/报价；关闭体重管理推荐匹配偏差与正式交付凭证。',
  ['新增需求待定','14台450已升级','咖啡结算出杯','全流程培训已报','验收待签认'],['待核','已报完成','已报完成','已报完成','待核'],'数量14来自当周自报；测试/培训完成不替代合同验收。'),
 ('保康三中',None,'设备已到现场；经销商服务器未到，操作系统安装受阻。','服务器到位 → 环境部署 → 设备联调 → 现场验收，具体日期待确认。',
  ['余额功能待澄清','服务器未到','联调待环境','培训待环境','现场验收计划'],['待核','阻塞','计划','计划','计划'],'先解除服务器依赖，不把已有设备到货写成系统可用。'),
 ('四方达项目','sifangda-poster.jpg','微信商户证书获取、配置及测试验证已记录。','核先约200人试用结果及正式切换日，补现场问题和确认。',
  ['范围待核','终端联调前期记录','微信支付已测','试用结果待核','切换日期待核'],['待核','进行中','已报完成','待核','待核'],'本周新证据集中在支付；易拉宝是材料，不是试运行完成证据。'),
 ('金斯瑞','jsr-line.jpg','取餐柜叫号大屏测试联调获支持；退款状态问题已诊断。','补现场上线/验收；另排退款状态与并发防重修复。',
  ['批次范围待核','取餐柜联调','叫号大屏测试','现场支持','退款未修复'],['待核','进行中','已报完成','进行中','阻塞'],'张帅本周有测试记录；王金洋缺当周周报，不能据此否认现场工作。'),
 ('滨州健康科技职业学院','binzhou-line.jpg','总表已处售后阶段；产研仍列营养数据及硬件问题未闭环。','核厂家报告、现场复测及9月客户回访。',
  ['主流程前期通过','硬件复测待补','营养数据排查','培训前期记录','遗留验收待核'],['已报完成','待核','进行中','已报完成','待核'],'本轮5人周报未出现滨州新工作行；沿用9/11产研材料并标来源。')
]
F=[]
for name,image,latest,nxt,gates,states,note in focus:
    p=byname[name]
    x={'id':p['id'],'name':name,'image':image,'latest':latest,'next':nxt,'gates':[{'label':l,'state':s} for l,s in zip(gates,states)],'note':note}
    F.append(x);p['focus']=x

current=[x for x in schedule if x['date']<='2026-09-13']
groups=defaultdict(set)
for x in current:groups[(x['person'],x['date'])].add(x['project'])
multi=[{'person':person,'date':day,'projects':sorted(names)} for (person,day),names in groups.items() if len(names)>1]
unmatched=[x for x in week if x['projectId'] is None]
Q={
 'snapshotDate':'2026-09-12','period':'9月7—13日','projects':P,'weekly':week,'people':people,'focus':F,'schedule':schedule,'service':service,
 'metrics':{'projectRecords':len(P),'numbered':sum(bool(x['number']) for x in P),'unnumbered':sum(not x['number'] for x in P),'year2026':sum(x['year']=='2026' for x in P),'stages':counts(P,'stage'),'statuses':counts(P,'status'),'businesses':counts(P,'business'),'types':counts(P,'type'),'deployments':counts(P,'deployment'),'complexities':counts(P,'complexity'),'acceptance':counts(P,'acceptance'),'weeklyRecords':len(week),'filledPeople':sum(x['filled'] for x in people),'weeklyCategories':counts(week,'category'),'matchedWeeklyRecords':len(week)-len(unmatched),'unmatchedWeeklyRecords':len(unmatched),'matchedProjectRecords':len({x['projectId'] for x in week if x['projectId']}),'scheduleCells':len(current),'personDays':len(groups),'multiProjectDays':len(multi),'phaseActualFilled':sum(bool(x['phaseActual']) for x in P),'serviceRecords':len(service),'serviceFinishedMissing':sum(not x['finished'] for x in service)},
 'multiProjectDays':multi,'unmatchedWeekly':unmatched,'excludedWeekly':excluded,
 'source':{'method':'Browser native Excel export after existing enterprise identity login','table':'交付实施部-项目管理空间','exportedAt':datetime.now().astimezone().isoformat(),'sha256':hashlib.sha256((HERE/'output/source/delivery-space-20260912.xlsx').read_bytes()).hexdigest(),'scope':'54 total project rows; five complete weekly sheets read; current-week rows parsed by period start 2026-09-07','limitations':['项目总表状态为填报值，不能代替合同验收','王金洋最近填写周期8.31-9.6，本周未填写，不等于无工作','项目阶段图原画面49条，与导出54条口径不同；新版按54条统一重算','实际工时/第一阶段实际完成日不足，不计算人力饱和度或正式按期率','总表外名称保留待关联，安庆部队未自动合并安徽武警，广州两个名称未强行去重']}
}
assert len(P)==54 and len(week)==24 and len(people)==5
assert Q['metrics']['stages']=={'售后阶段':36,'交付阶段':14,'售前阶段':3,'筹备阶段':1}
assert sum(Q['metrics']['weeklyCategories'].values())==24
assert sum(Q['metrics']['deployments'].values())==54
assert sum(x['records'] or 0 for x in people)==24
# Retain the dated project diagnoses and meeting milestones already supplied in this task.
prior_path=HERE.parent/'weekly-report-draft/report-data.json'
prior_alias={'哈尔滨膳质舍':'哈尔滨膳质舍','三全':'三全集团','国康':'国康医院','国信':'国信智慧食堂','四方达':'四方达项目','安徽部队':'安徽武警部队','安徽体职院30台动感单车':'安徽体职院教学器材','江西体科医/江西体科一':'江西体科医-科训系统','城市副中心C08':'C08','无锡新吴区政府':'江苏无锡新吴区政府智慧餐厅项目','大兴区政府':'大兴','张家港':'张家港市农商行项目','保康中学':'保康三中'}
if prior_path.exists():
    for r in json.loads(prior_path.read_text())['projects']:
        p=byname.get(prior_alias.get(r[2],r[2]))
        if p:p['reportContext']={'actual':r[5],'next':r[6],'boundary':r[7],'source':r[8],'period':'9/7—9/12已读会议/产研/问题诊断'}
# A current R&D row changes the Zhangjiagang planning interpretation without overwriting its source stage.
zg=byname['张家港市农商行项目']
zg['rndNote']='9/12读取的开发二组第32/55行：刷卡、刷码链路已调试通过；优先级降低，记录“2个月后再继续”及项目延期。具体恢复日期待确认。'
serialized=json.dumps(Q,ensure_ascii=False,indent=2)
assert '候选密码' not in serialized and '候选账号' not in serialized and '系统访问地址' not in serialized
(HERE/'output/reviewed-projects.json').write_text(serialized)
(HERE/'output/source-metrics.json').write_text(json.dumps(Q['metrics'],ensure_ascii=False,indent=2))
print(json.dumps(Q['metrics'],ensure_ascii=False,indent=2))