"""Presentation cleanup, preserving quality verification and inventory semantics."""
import sys,json,re
from pathlib import Path
from collections import defaultdict
P=Path(__file__).parent
sys.stdout.reconfigure(encoding='utf8')
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
import pymysql
assert not (P/'result.json').exists(),'Already applied; inspect before repeating.'
demo='10月8日至14日业务链演示补充；非真实交易、投料、检验或物流凭证；沿用现有示意BOM及工艺主数据。'
offset='九月历史销售流程补录；按用户要求等量补库存后出库，净库存不变；非真实采购或生产入库；汇总批次非真实生产批次；补录时间为本次执行时间。'
batch='2026年9月库存台账期初汇总；按物料汇总跨原生产批次，非真实生产批次；不填生产日期、有效期和质检结果'
def clean(t,text):
 if not text:return text
 s=text.replace(demo,'')
 s=s.replace('演示参数值；不是现场实测参数，原工艺限值未改动。','参数值待现场核验；工艺限值保持原要求。')
 s=s.replace('演示样例结果；不作为真实检测或放行依据。','检测结果待复核，不作为放行依据。')
 s=s.replace('按2026年9月产量表补充的工艺路线（示意BOM，实际配方以客户工艺文件为准）','实际配方以客户工艺文件为准。')
 s=s.replace(offset,'历史销售库存等量调整；补入后出库，净库存不变；使用库存汇总批次。')
 s=s.replace(batch,'期初汇总批次；跨生产批次汇总；生产日期、有效期及检验信息待补充')
 s=re.sub(r'^2026年9月台账Sheet1第\d+行，按结存覆盖；按原表库位分仓；INIT202609为期初汇总批次，不代表真实生产批次；+','期初结存；按库位分仓；期初汇总批次；',s)
 s=s.replace('按同编码包装成品/包材的计数单位1:1录入','同编码包装成品/包材按1:1计数')
 s=s.replace('同SKU完整销售包装计数按1:1补录','同SKU完整销售包装按1:1计数')
 s=s.replace('瓶/盒/个为原台账计数称谓','瓶/盒/个为同SKU销售包装计数')
 s=s.replace('不新增通用单位换算','仅适用于当前SKU包装')
 if t=='mes_qc_template_item':
  s=s.replace('来源：来料检验导入表；按物料编码/名称加规格及标准编号匹配。','物料与来料检验方案关联。')
  s=s.replace('来源：出货检验导入表；按检验单名称与方案名称模糊映射，多候选依用户要求随机选择并留档。','产品与出货检验方案关联。')
 if t=='mes_qc_template':
  s=s.replace('国标补充核对：','检验要求：').replace('暂缓检验单导入。','资料完善前暂缓检验。')
 s=s.replace('本仓默认库区，承接9月台账库存','本仓默认库区')
 if s==text:return text
 s=re.sub(r'；{2,}','；',s).strip(' ；')
 return s or None
c=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',autocommit=False,cursorclass=pymysql.cursors.DictCursor)
result={'counts':{},'changes':[]}
try:
 with c.cursor() as q:
  q.execute("SET time_zone='+08:00'")
  q.execute('SELECT DATABASE() db,NOW() now,GET_LOCK(%s,10) locked',('operations-remarks-20261014',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1
  q.execute("SELECT table_name FROM information_schema.columns WHERE table_schema=DATABASE() AND column_name='remark' AND (table_name LIKE 'mes_pro_%' OR table_name LIKE 'mes_qc_%' OR table_name LIKE 'mes_wm_%' OR table_name LIKE 'erp_stock_%' OR table_name IN ('erp_sale_out','erp_sale_out_items','erp_purchase_in','erp_purchase_in_items')) ORDER BY table_name");tabs=[x['TABLE_NAME'] for x in q.fetchall()]
  before={}
  for t in tabs:
   q.execute('SHOW COLUMNS FROM '+t);cols={x['Field'] for x in q.fetchall()}
   if not {'id','deleted','update_time','updater'}<=cols:continue
   q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id FOR UPDATE');before[t]=q.fetchall()
  q.execute('SELECT id,status FROM system_users WHERE id IN (244,289) AND deleted=0');actors={x['id']:x['status'] for x in q.fetchall()};assert actors=={244:0,289:0}
  backup=P/('locked-before-'+state['now'].strftime('%Y%m%d-%H%M%S')+'.json')
  backup.write_text(json.dumps({'state':state,'tables':before},ensure_ascii=False,default=str,indent=2),encoding='utf8')
  expected={}
  for t,rows in before.items():
   grouped=defaultdict(list);actor='289' if t.startswith('mes_qc_') else '244'
   for old in rows:
    new=clean(t,old['remark'])
    if new==old['remark']:continue
    assert not any(w in (new or '') for w in ['非真实交易','业务链演示','示意BOM','演示参数值','演示样例结果','九月历史销售流程补录'])
    expected[(t,old['id'])]=new;result['changes'].append({'table':t,'id':old['id'],'old_remark':old['remark'],'remark':new});grouped[new].append(old['id'])
   result['counts'][t]=sum(len(ids) for ids in grouped.values())
   for new,ids in grouped.items():
    q.execute('UPDATE '+t+' SET remark=%s,updater=%s,update_time=update_time WHERE deleted=0 AND id IN ('+','.join(['%s']*len(ids))+')',[new,actor,*ids]);assert q.rowcount==len(ids)
  after={}
  for t,rows in before.items():
   q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id');newrows=q.fetchall();after[t]=newrows;assert len(rows)==len(newrows)
   for old,new in zip(rows,newrows):
    assert old['id']==new['id'];key=(t,old['id']);diff={k for k in old if old[k]!=new[k]};assert diff<=({'remark','updater'} if key in expected else set()),(t,old['id'],diff)
    if key in expected:assert new['remark']==expected[key]
    assert '非真实交易' not in (new['remark'] or '')
  c.commit()
  for t,rows in after.items():
   q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id');assert q.fetchall()==rows,('Post-commit mismatch',t)
  totals={'生产':sum(n for t,n in result['counts'].items() if t.startswith('mes_pro_')),'质量':sum(n for t,n in result['counts'].items() if t.startswith('mes_qc_')),'仓库':sum(n for t,n in result['counts'].items() if not t.startswith(('mes_pro_','mes_qc_')))}
  result.update(committed=True,verified=True,at=str(state['now']),module_totals=totals,total=len(result['changes']),backup=backup.name,quantities_results_states_relations_and_times_unchanged=True)
  (P/'result.json').write_text(json.dumps(result,ensure_ascii=False,indent=2),encoding='utf8')
  print(json.dumps({k:v for k,v in result.items() if k not in ['changes','counts']},ensure_ascii=False))
except Exception:c.rollback();raise
finally:
 with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',('operations-remarks-20261014',))
 c.close()