"""User-authorized reference BOM configuration, using existing material IDs only."""
import sys,json,re,csv
from pathlib import Path
from collections import defaultdict
from datetime import datetime
from decimal import Decimal,ROUND_HALF_UP
sys.stdout.reconfigure(encoding='utf-8')
sys.path.insert(0,str(Path(__file__).parent/'iqc-import-20261012/deps'))
import pymysql
apply='--apply' in sys.argv
out=Path(__file__).parent/'schedule-diagnosis-20261015'
D=lambda v:Decimal(str(v))
tables=['mes_pro_route','mes_pro_route_process','mes_pro_route_product','mes_pro_route_product_bom','mes_md_product_bom','mes_md_item','mes_pro_work_order','mes_pro_process']
with pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',connect_timeout=15,read_timeout=60,cursorclass=pymysql.cursors.DictCursor) as c:
 with c.cursor() as q:
  q.execute('SELECT DATABASE() db');assert q.fetchone()['db']=='mom-hm'
  data={}
  for t in tables:
   q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id'+(' FOR UPDATE' if apply else ''));data[t]=q.fetchall()
  items={i['id']:i for i in data['mes_md_item']};process_names={p['id']:p['name'] for p in data['mes_pro_process']}
  base=defaultdict(list);rp=defaultdict(list);products=defaultdict(set);allocated=defaultdict(list)
  for b in data['mes_md_product_bom']:base[b['item_id']].append(b)
  for p in data['mes_pro_route_process']:rp[p['route_id']].append(p)
  for p in data['mes_pro_route_product']:products[p['route_id']].add(p['item_id'])
  for b in data['mes_pro_route_product_bom']:allocated[(b['route_id'],b['product_id'])].append(b)
  for ps in rp.values():ps.sort(key=lambda p:p['sort'])
  generated={};basis={}
  def custom(pid,components,why):
   generated[pid]={i:D(v) for i,v in components.items()};basis[pid]=why
  def clone(pid,source,mass_scale=1):
   assert base[source],source
   generated[pid]={b['bom_item_id']:D(b['quantity'])*(D(mass_scale) if items[b['bom_item_id']]['item_type_id'] in (3,4) else D(1)) for b in base[source]}
   basis[pid]=f'沿用同类产品{source}的BOM；食品原料按净含量比例{mass_scale}换算，独立包装件按单件保持'
  aliases={987:524,988:525,989:526,1151:962,1152:541,1153:540,974:523,1122:523,985:534,986:533,984:529,1155:535,973:528,976:536,983:531,1508:536,1509:536,1154:537}
  for pid,source in aliases.items():clone(pid,source)
  clone(979,484,D('0.9'))
  clone(975,484,D('0.6'))
  custom(1544,{575:'.1512',576:1,577:1,579:1,574:1},'150克黑果枸杞单瓶；沿用产品486原料比例0.252/0.25和单瓶包材')
  # Single-unit outputs use existing bulk ingredients and matching inner packaging.
  # Organic and ordinary fruit inputs are kept separate.
  for pid in (776,1013):custom(pid,{1064:'.00408',778:1,779:1},'有机黑果枸杞4克单罐；使用现有有机原料，净含量4克加2%用料损耗；小罐装饰及有机标贴各1')
  custom(860,{791:'.00408',778:1},'普通黑果枸杞4克单罐；沿用去柄半成品，净含量4克加2%用料损耗')
  custom(992,{773:'.0084',1199:'.00136'},'8克冻干枸杞；原料和复合膜沿用产品523的单盒BOM，按25袋均分')
  custom(2004,{575:'.09072',576:1,577:1,579:1,574:1},'90克黑果枸杞瓶装；沿用产品486的单瓶包材；原料按原250克耗料0.252千克等比换算')
  custom(2005,{604:'.050714',576:1,577:1,606:1,607:1},'50克雪菊瓶装；沿用产品492的原料比例0.142/0.14及单瓶内袋、瓶签、卡纸')
  # Liquid inputs are in kg. These coefficients express the configured nominal
  # single-fruit fill mass plus process allowance, never an invented approval.
  film_or_bottle={639:(650,'.051',),663:(650,'.051',),815:(650,'.0612',),643:(658,'.0306',),1157:(658,'.051',),807:(658,'.0612',),628:(1065,'.0306',),649:(1065,'.051',),811:(1065,'.0612',),835:(1065,'.0306',),641:(696,'.0306',)}
  for pid,(raw,mass) in film_or_bottle.items():
   comps={raw:mass}
   if pid in (639,663,1157,649):comps[1417]=1
   elif pid==641:comps[1294]='.0019'
   elif pid in (643,835,628):comps[656 if pid==643 else 1280]='.0019'
   custom(pid,comps,f'按现有原浆原料{raw}配置单一果浆投入，按标示毫升数折算名义灌装质量并计2%用料余量；未变更原有工序收率')
  custom(613,{614:'.0204'},'20克铁棍山药粉条装；采用现有山药粉原料，净含量加2%用料余量')
  # Reuse an existing finished recipe for the same fruit paste; all ingredient
  # ratios, including additives, are scaled together rather than invented.
  paste={b['bom_item_id']:(D(b['quantity'])/D(30)) for b in base[537] if items[b['bom_item_id']]['item_type_id'] in (3,4) or b['bom_item_id']==846}
  custom(963,paste,'沿用现有产品537（900ml）果膏BOM；按30ml单袋等比缩小为1/30，配方成分比例不变')
  custom(1158,{736:'.0331',737:'5.7'},'沿用现有产品520的300ml西梅原浆BOM；原料及内包装按30ml单袋缩小为1/10')
  custom(1543,{1158:10,738:1,610:1},'西梅原浆成品按现有300ml包装口径；10袋30ml原浆及原有外盒、防伪贴')
  custom(1540,{1157:10,1264:1,1265:10,630:1},'500ml黑果枸杞原浆：10瓶50ml，采用现有同规格10瓶装外盒及瓶贴')
  custom(1510,{685:'.0153',686:'.0153',687:'.00095',688:'.00095'},'30ml红黑枸杞组合装；按现有红、黑经济型原浆等量组配及配套膜材，各占一半，不改造为功能性复方')
  custom(2006,{696:'.51',1417:10,1293:10,626:'.028',627:'.05'},'500ml桑椹原浆（50ml×10）；采用现有桑葚原浆原料和50ml瓶盖瓶贴，名义灌装质量加2%用料余量')
  # Prebiotic drink derives the prebiotic ratio from an existing beverage BOM.
  ratios=[]
  for parent,bs in base.items():
   fructose=[b for b in bs if b['bom_item_id']==675]
   if not fructose:continue
   spec=(items.get(parent,{}).get('specification') or '')
   match=re.search(r'(\d+(?:\.\d+)?)\s*(?:ml|毫升)',spec,re.I)
   if match:ratios.append((parent,D(fructose[0]['quantity'])/(D(match[1])/1000)))
  assert ratios,'无已有低聚果糖用量来源，不能编造配方'
  source,fructose_per_kg=ratios[0]
  fructose=(fructose_per_kg*D('.035')).quantize(D('.000001'),rounding=ROUND_HALF_UP)
  assert D(0)<fructose<D('.0035'), '参考低聚果糖比例异常'
  custom(669,{736:D('.0357')-fructose,675:fructose,1354:'1.9'},f'35ml益生元西梅饮：原浆用量按单袋名义灌装量及2%余量；低聚果糖沿用产品{source}每kg饮品用量{fructose_per_kg}等比换算')
  newbase=[];newinputs=[];outputs=[];disabled=[]
  for r in data['mes_pro_route']:
   rid=r['id'];ps=rp[rid];assert ps and sum(p['key_flag']==b'\x01' for p in ps)==1
   for pid in sorted(products[rid]):
    if not base[pid] and not allocated[(rid,pid)]:
     assert pid in generated,('missing configuration',rid,pid)
     assert generated[pid],pid
     for item,quantity in generated[pid].items():
      assert item in items and items[item]['status']==0 and item!=pid,(pid,item)
      qty=quantity.quantize(D('.000001'),rounding=ROUND_HALF_UP);assert qty>0
      b={'item_id':pid,'bom_item_id':item,'quantity':qty,'status':0,'remark':'本次配置：'+basis[pid]}
      newbase.append(b);base[pid].append(b)
    if not allocated[(rid,pid)]:
     for b in base[pid]:
      name=items[b['bom_item_id']]['name'];ptype=items[b['bom_item_id']]['item_type_id']
      packaging=bool(re.search('盒|箱|垫板|标签|瓶贴|卡纸|封套|内托|保护|袋|瓶盖|膜|干燥剂|装饰|RFID|防伪|保障|瓶身|亚克力',name)) and ptype not in (3,4)
      if packaging:preferred=['外包装'] if re.search('盒|箱|垫板|标签|瓶贴|卡纸|封套|内托|保护|RFID|防伪|保障|装饰',name) else ['内包装','灌装','封口','外包装']
      elif items[b['bom_item_id']]['item_type_id'] in (20,23) or b['bom_item_id'] in products[rid]:preferred=['内包装','外包装']
      else:preferred=['调配','混合','投料','打浆','粉碎','人工选别','领料脱包','原料验收']
      p=next((p for n in preferred for p in ps if n in process_names[p['process_id']]),ps[0])
      newinputs.append({'route_id':rid,'process_id':p['process_id'],'product_id':pid,'item_id':b['bom_item_id'],'quantity':b['quantity'],'remark':'按产品BOM及对应投料/包装工序配置；'+(basis.get(pid) or f'沿用现有产品{pid}BOM')})
     allocated[(rid,pid)]=[b for b in newinputs if b['route_id']==rid and b['product_id']==pid]
   if r['status']!=0:disabled.append(rid)
   # Empty is the native per-work-order output. Only make the single-product
   # case explicit when all existing orders already use that same SKU.
   linked=products[rid];historical={o['product_id'] for o in data['mes_pro_work_order'] if o['route_id']==rid}
   if len(linked)==1 and historical<=linked:
    for p in ps:
     if not p['output_item_id']:outputs.append((p['id'],str(next(iter(linked)))))
  graph={pid:{b['bom_item_id'] for b in bs} for pid,bs in base.items()}
  def visit(pid,stack):
   assert pid not in stack,('BOM cycle',stack,pid)
   for child in graph.get(pid,set()):visit(child,stack|{pid})
  for pid in generated:visit(pid,set())
  report={'newProductBomRows':len(newbase),'productsConfigured':len({b['item_id'] for b in newbase}),'newRouteInputs':len(newinputs),'explicitOutputs':len(outputs),'routesToEnable':disabled,'totalRoutes':len(data['mes_pro_route'])}
  (out/'complete-route-plan.json').write_text(json.dumps({'report':report,'productBom':newbase,'routeInputs':newinputs,'outputs':outputs},ensure_ascii=False,default=str,indent=2),encoding='utf-8')
  print(json.dumps(report,ensure_ascii=False))
  if apply:
   stamp=datetime.now().strftime('%Y%m%d%H%M%S')
   (out/f'complete-route-before-{stamp}.json').write_text(json.dumps(data,ensure_ascii=False,default=str,indent=2),encoding='utf-8')
   def insert(table,row):
    cols=list(row)
    q.execute('INSERT INTO '+table+' ('+','.join(cols)+',create_time,update_time,deleted) VALUES ('+','.join(['%s']*len(cols))+',NOW(),NOW(),0)',list(row.values()))
   for b in newbase:insert('mes_md_product_bom',b)
   for b in newinputs:insert('mes_pro_route_product_bom',b)
   for pid,value in outputs:q.execute('UPDATE mes_pro_route_process SET output_item_id=%s,update_time=NOW() WHERE id=%s AND deleted=0',(value,pid))
   for rid in disabled:q.execute('UPDATE mes_pro_route SET status=0,update_time=NOW() WHERE id=%s AND deleted=0',(rid,))
   q.execute('SELECT COUNT(*) n FROM mes_pro_route WHERE deleted=0 AND status<>0');assert q.fetchone()['n']==0
   q.execute('SELECT COUNT(*) n FROM mes_pro_route_product p WHERE p.deleted=0 AND NOT EXISTS (SELECT 1 FROM mes_pro_route_product_bom b WHERE b.deleted=0 AND b.route_id=p.route_id AND b.product_id=p.item_id)');assert q.fetchone()['n']==0
   c.commit()
   (out/f'complete-route-result-{stamp}.json').write_text(json.dumps(report,ensure_ascii=False,indent=2),encoding='utf-8')
   print(json.dumps({'committed':True,'allRoutesEnabled':True,'allBoundProductsHaveInputs':True}))