"""Plan only explicit sales -> MPS -> production -> quality -> warehouse links."""
import json,math
from pathlib import Path
from datetime import datetime,date,time,timedelta
from decimal import Decimal
P=Path(__file__).parent
s=json.loads((P/'snapshot.json').read_text(encoding='utf8'));tables=s['tables']
rows=lambda t:tables.get(t,{}).get('rows',[])
data={t:{r['id']:r for r in v['rows']} for t,v in tables.items()}
dates={t:{c['Field']:c['Type'] for c in v['columns'] if c['Type'].startswith(('date','timestamp'))} for t,v in tables.items()}
now=datetime.fromisoformat(s['state']['now'])
holiday={date(2026,9,d) for d in range(25,28)}|{date(2026,10,d) for d in range(1,8)}
workday=lambda d:d.weekday()<5 and d not in holiday
def at(d,h,m=0):return datetime.combine(d,time(h,m))
def backwards(end,n):
 days=[];d=end
 while len(days)<n:
  if workday(d):days.append(d)
  d-=timedelta(days=1)
 return list(reversed(days))
changes={};owner={};summary=[];task_times={};qc_times={};doc_events={}
def setvals(t,id,chain,v):
 if (t,id) in owner:assert owner[t,id]==chain,('Shared row',t,id,owner[t,id],chain)
 owner[t,id]=chain
 old=data[t][id]
 for field,value in v.items():
  assert field in old,(t,field)
  if isinstance(value,datetime):
   if dates[t].get(field)=='date':value=value.date()
   value=str(value)
  if old[field]!=value:changes.setdefault((t,id),{})[field]=value
def event(t,id,chain,created,updated=None,**fields):
 assert workday(created.date()) and 9<=created.hour<18 and created<=now,(t,id,created)
 updated=updated or created
 assert created<=updated<=now and workday(updated.date()) and 9<=updated.hour<18
 setvals(t,id,chain,{'create_time':created,'update_time':updated,**fields})
 doc_events[t,id]=created
def children(t,fk,pid,chain,eventtime):
 for r in rows(t):
  if r[fk]==pid:event(t,r['id'],chain,eventtime)
def slots(days):
 out=[]
 for i,d in enumerate(days):
  for h0,m0,h1,m1 in [(9,0,12,0),(13,0,17,30)]:
   start=at(d,h0,m0);end=at(d,h1,m1)
   if i==0:start=max(start,at(d,13,30))
   if i==len(days)-1:end=min(end,at(d,12,30))
   while start<=end:
    if start<end:out.append(start)
    start+=timedelta(minutes=1)
 return out
required=[r for r in rows('erp_sale_order_items') if r['need_production'] in (1,True,"b'\\x01'")]
order_ids={r['order_id'] for r in required};assert len(order_ids)==8
tx_kind={110:'mes_wm_item_receipt',115:'mes_wm_misc_receipt',116:'mes_wm_product_issue',118:'mes_wm_product_receipt',119:'mes_wm_product_sales',123:'mes_wm_item_consume',124:'mes_wm_product_produce'}
for oid in sorted(order_ids):
 o=data['erp_sale_order'][oid];quantity=Decimal(o['total_count']);cycle=2 if quantity<=30 else (4 if oid==1060 else 3) if quantity<=1000 else (10 if quantity>10000 else 8)
 end=date(2026,10,13) if oid<1059 else date(2026,10,12) if oid==1059 else date(2026,10,14)
 days=backwards(end,cycle);start=days[0];order_time=at(start,9,20);close=at(end,13);inbound=at(end,13,30);notice_time=at(end,14);outbound=at(end,16,30)
 assert outbound<=now and start>=date(2026,9,17)
 mps=[r for r in rows('mes_pro_mps') if r['sale_order_id']==oid];assert mps
 wo_ids={r['work_order_id'] for r in mps};assert None not in wo_ids
 event('erp_sale_order',oid,oid,order_time,outbound,order_time=order_time,production_finish_time=close)
 children('erp_sale_order_items','order_id',oid,oid,order_time)
 # User explicitly allowed this single upstream exception to maintain chronology.
 if oid==1062:
  ct=data['crm_contract'][o['contract_id']];qt=next(r for r in rows('crm_sale_quotation') if r['contract_id']==ct['id']);b=data['crm_business'].get(ct['business_id'])
  if b:
   event('crm_business',b['id'],oid,at(start,9),at(start,9))
   children('crm_business_product','business_id',b['id'],oid,at(start,9))
  event('crm_sale_quotation',qt['id'],oid,at(start,9,5),quotation_time=at(start,9,5));children('crm_sale_quotation_product','quotation_id',qt['id'],oid,at(start,9,5))
  event('crm_contract',ct['id'],oid,at(start,9,10),order_date=at(start,9,10),**({'start_time':at(start,9,10)} if ct['start_time'] else {}));children('crm_contract_product','contract_id',ct['id'],oid,at(start,9,10))
 for mp in mps:event('mes_pro_mps',mp['id'],oid,at(start,9,30),close,issue_date=at(start,9,40),request_date=close,finish_date=close)
 chain_tasks=[]
 for wid in sorted(wo_ids):
  event('mes_pro_work_order',wid,oid,at(start,9,45),close,request_date=close,finish_date=close)
  for t in ['mes_pro_work_order_bom','mes_pro_work_order_process']:children(t,'work_order_id',wid,oid,at(start,9,45))
  tasks=sorted([r for r in rows('mes_pro_task') if r['work_order_id']==wid],key=lambda x:(x['start_time'] or x['create_time'],x['id']));assert tasks
  timeline=slots(days);assert len(timeline)>len(tasks)*20
  for j,r in enumerate(tasks):
   a=int(j*len(timeline)/len(tasks));bnd=int((j+.75)*len(timeline)/len(tasks));bnd=min(bnd,len(timeline)-7)
   begin=timeline[a];finish=timeline[bnd];output=timeline[bnd+5];inspect=timeline[bnd+2];issue_create=timeline[max(0,a-15)] if a>=15 else at(start,13,10);issue=timeline[max(0,a-10)] if a>=10 else at(start,13,20)
   task_times[r['id']]={'start':begin,'end':finish,'output':output,'inspect':inspect,'issue':issue,'issue_create':issue_create,'oid':oid}
   chain_tasks.append(r['id'])
   event('mes_pro_task',r['id'],oid,begin,output,start_time=begin,end_time=finish,finish_date=output,first_feedback_time=finish,last_feedback_time=finish)
   children('mes_pro_task_assignee','task_id',r['id'],oid,begin)
   for f in rows('mes_pro_feedback'):
    if f['task_id']==r['id']:event('mes_pro_feedback',f['id'],oid,finish,feedback_time=finish)
   for p in rows('mes_pro_work_order_param_record'):
    if p['task_id']==r['id']:
     event('mes_pro_work_order_param_record',p['id'],oid,finish);children('mes_pro_work_order_param_record_detail','record_id',p['id'],oid,finish)
 # Strong production-document links, including legacy headers without task IDs.
 for t in ['mes_wm_product_issue','mes_wm_item_consume','mes_wm_product_produce','mes_wm_product_receipt']:
  for r in rows(t):
   if r.get('work_order_id') not in wo_ids:continue
   tid=r.get('task_id');tt=task_times.get(tid)
   if not tt:
    same=[x for x in chain_tasks if data['mes_pro_task'][x]['work_order_id']==r['work_order_id']]
    tt=task_times[same[0] if t in ['mes_wm_product_issue','mes_wm_item_consume'] else same[-1]]
   if t=='mes_wm_product_issue':created=tt['issue_create'];updated=tt['issue'];fields={'issue_date':updated,'required_time':tt['start']};fk='issue_id'
   elif t=='mes_wm_item_consume':created=updated=tt['start'];fields={'consume_date':created};fk='consume_id'
   elif t=='mes_wm_product_produce':created=tt['end'];updated=tt['output'];fields={'produce_date':updated};fk='produce_id'
   else:created=at(end,13,20);updated=inbound;fields={'receipt_date':inbound};fk='receipt_id'
   event(t,r['id'],oid,created,updated,**fields)
   doc_events[t,r['id']]=updated
   for child in [t+'_line',t+'_detail']:children(child,fk,r['id'],oid,updated)
 for r in rows('mes_pro_task_issue'):
  if r['task_id'] in chain_tasks:event('mes_pro_task_issue',r['id'],oid,task_times[r['task_id']]['issue'])
 # Sales documents are scoped to the selected order, not product-name matches.
 for t in ['mes_wm_sales_notice','mes_wm_product_sales']:
  for r in rows(t):
   if r['sale_order_id']!=oid:continue
   ct=notice_time if t.endswith('notice') else at(end,14,10)
   event(t,r['id'],oid,ct,outbound,sales_date=notice_time if t.endswith('notice') else outbound,**({'delivery_date':outbound} if t.endswith('notice') and r.get('delivery_date') else {}))
   fk='notice_id' if t.endswith('notice') else 'sales_id'
   for child in [t+'_line',t+'_detail']:children(child,fk,r['id'],oid,ct if child.endswith('_line') else outbound)
   doc_events[t,r['id']]=outbound
 sales_ids={id for t,id in owner if t=='mes_wm_product_sales' and owner[t,id]==oid}
 for r in rows('mes_wm_stock_reserve'):
  if r['biz_type']==1 and r['biz_id'] in sales_ids:event('mes_wm_stock_reserve',r['id'],oid,at(end,16),outbound)
 # Include incoming documents only for stocks exclusively consumed in this chain.
 inputs=set()
 for t in ['mes_wm_product_issue_detail','mes_wm_item_consume_detail']:
  inputs|={r['material_stock_id'] for r in rows(t) if owner.get((t,r['id']))==oid}
 for sid in inputs:
  movements=[r for r in rows('mes_wm_transaction') if r['material_stock_id']==sid]
  negatives=[r for r in movements if Decimal(r['quantity'])<0]
  if not negatives or any(owner.get((tx_kind.get(r['biz_type']),r['biz_id']))!=oid for r in negatives):continue
  for tx in movements:
   if tx['biz_type']!=110 or Decimal(tx['quantity'])<=0:continue
   receipt=data['mes_wm_item_receipt'][tx['biz_id']]
   event('mes_wm_item_receipt',receipt['id'],oid,at(start,10,50),at(start,11),receipt_date=at(start,11));doc_events['mes_wm_item_receipt',receipt['id']]=at(start,11)
   for child in ['mes_wm_item_receipt_line','mes_wm_item_receipt_detail']:
    children(child,'receipt_id',receipt['id'],oid,at(start,11))
    for r in rows(child):
     if r['receipt_id']==receipt['id'] and r.get('production_date'):setvals(child,r['id'],oid,{'production_date':at(start,9)})
   if receipt['notice_id']:
    nid=receipt['notice_id'];event('mes_wm_arrival_notice',nid,oid,at(start,10),arrival_date=at(start,10));children('mes_wm_arrival_notice_line','notice_id',nid,oid,at(start,10))
   if receipt['iqc_id']:qc_times['mes_qc_iqc',receipt['iqc_id']]=(oid,at(start,10,30),at(start,10),None)
 # Direct QC links and their result/header/line relationships.
 for r in rows('mes_qc_ipqc'):
  if r['work_order_id'] in wo_ids:
   tt=task_times.get(r['task_id']) or task_times[next(x for x in chain_tasks if data['mes_pro_task'][x]['work_order_id']==r['work_order_id'])]
   qc_times['mes_qc_ipqc',r['id']]=(oid,tt['inspect'],None,None)
 oqc_ids={r['oqc_id'] for r in rows('mes_wm_product_sales_line') if owner.get(('mes_wm_product_sales_line',r['id']))==oid and r['oqc_id']}
 for r in rows('mes_qc_oqc'):
  if r['id'] in oqc_ids or (r['source_doc_type']==119 and r['source_doc_id'] in sales_ids):qc_times['mes_qc_oqc',r['id']]=(oid,at(end,15),None,outbound)
 summary.append({'order_id':oid,'order_no':o['no'],'quantity':str(quantity),'cycle_workdays':cycle,'order_time':str(order_time),'production_finish':str(close),'outbound_time':str(outbound),'calendar_days':(end-start).days+1,'workdays':[str(d) for d in days],'mps_ids':[r['id'] for r in mps],'work_order_ids':sorted(wo_ids),'task_ids':chain_tasks})
for (t,id),(oid,inspect,received,out) in qc_times.items():
 vals={'inspect_date':inspect}
 if received:vals['receive_date']=received
 if out:vals['out_date']=out
 event(t,id,oid,inspect,**vals)
 fk={'mes_qc_iqc':'iqc_id','mes_qc_ipqc':'ipqc_id','mes_qc_oqc':'oqc_id'}[t];children(t+'_line',fk,id,oid,inspect)
 kind={'mes_qc_iqc':1,'mes_qc_ipqc':2,'mes_qc_oqc':3}[t]
 for r in rows('mes_qc_indicator_result'):
  if r['qc_id']==id and r['qc_type']==kind:
   event('mes_qc_indicator_result',r['id'],oid,inspect);children('mes_qc_indicator_result_detail','result_id',r['id'],oid,inspect)
# Transactions must occur at their source document's business event, not creation.
for r in rows('mes_wm_transaction'):
 key=(tx_kind.get(r['biz_type']),r['biz_id'])
 if key in owner:
  oid=owner[key];dt=doc_events[key];event('mes_wm_transaction',r['id'],oid,dt,transaction_time=dt,**({'erp_time':dt} if r['erp_time'] else {}))
# Retimestamp only dedicated stock/batches; shared opening inventory is protected.
def get(t,id,field):return changes.get((t,id),{}).get(field,data[t][id][field])
stock_ids={r['material_stock_id'] for r in rows('mes_wm_transaction') if ('mes_wm_transaction',r['id']) in owner}
for sid in stock_ids:
 movements=[r for r in rows('mes_wm_transaction') if r['material_stock_id']==sid]
 chainset={owner.get(('mes_wm_transaction',r['id'])) for r in movements}
 if len(chainset)!=1 or None in chainset:continue
 oid=next(iter(chainset));stock=data['mes_wm_material_stock'][sid]
 incoming=[datetime.fromisoformat(get('mes_wm_transaction',r['id'],'transaction_time')) for r in movements if Decimal(r['quantity'])>0]
 if not incoming:continue
 first=min(incoming);last=max(datetime.fromisoformat(get('mes_wm_transaction',r['id'],'transaction_time')) for r in movements)
 event('mes_wm_material_stock',sid,oid,first,last,receipt_time=first,**({'erp_time':first} if stock['erp_time'] else {}))
 for r in movements:
  if r['receipt_time']:setvals('mes_wm_transaction',r['id'],oid,{'receipt_time':first})
 batchid=stock['batch_id']
 if not batchid:continue
 allstocks=[r for r in rows('mes_wm_material_stock') if r['batch_id']==batchid]
 if not all(owner.get(('mes_wm_material_stock',r['id']))==oid or r['id']==sid for r in allstocks):continue
 b=data['mes_wm_batch'][batchid];source=[x for x in rows('mes_wm_product_produce_line') if x['batch_id']==batchid and owner.get(('mes_wm_product_produce_line',x['id']))==oid]
 produced=doc_events.get(('mes_wm_product_produce',source[0]['produce_id'])) if source else at(datetime.fromisoformat(next(x['order_time'] for x in summary if x['order_id']==oid)).date(),9)
 # Multiple warehouse stock dimensions of the same batch need its earliest receipt.
 earliest=min([datetime.fromisoformat(get('mes_wm_material_stock',r['id'],'receipt_time')) for r in allstocks if ('mes_wm_material_stock',r['id']) in owner]+[first])
 event('mes_wm_batch',batchid,oid,produced,max(produced,earliest),**({'produce_date':produced} if b['produce_date'] else {}),**({'receipt_date':earliest} if b['receipt_date'] else {}))
for (t,id),v in changes.items():
 for field,val in v.items():
  if field in dates[t] and val is not None:
   d=datetime.fromisoformat(val) if ' ' in val else datetime.combine(date.fromisoformat(val),time.min)
   assert workday(d.date()) and d<=now,(t,id,field,val)
 planned={**data[t][id],**v}
 assert planned['create_time']<=planned['update_time']
plan={'generated_from':s['state'],'quantity_basis':'销售订单总数量','minimum_quote_date':'2026-09-17','holiday_source':'https://www.beijing.gov.cn/cs/gncs/zcwj/202603/t20260327_4568275.html','upstream_exception_order':1062,'chains':summary,'changes':[{'table':t,'id':id,'order_id':owner[t,id],'before':{k:data[t][id][k] for k in v},'after':v} for (t,id),v in changes.items()]}
(P/'plan.json').write_text(json.dumps(plan,ensure_ascii=False,indent=2),encoding='utf8')
print(json.dumps({'orders':summary,'changed_rows':len(changes),'changed_tables':len({t for t,id in changes})},ensure_ascii=False))