"""Prepare native notice -> outbound drafts; do not post stock or claim goods shipped."""
import sys,json,csv
from pathlib import Path
from decimal import Decimal
P=Path(__file__).parent
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
import pymysql
source=json.loads((P/'source-order-regroup-result.json').read_text(encoding='utf8'))
assert not (P/'shipping-drafts-result.json').exists(),'Drafts already generated; do not duplicate.'
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)
D=Decimal
result={'orders':[],'excluded':[],'stock_posted':False,'workflow':'发货通知提交待出库 -> 出库草稿；等待历史/实际扣库口径确认；未拣货、未填写运单、未执行库存事务'}
try:
 with c.cursor() as q:
  q.execute('SELECT NOW() now,DATABASE() db,GET_LOCK(%s,10) locked',('sales-ledger-202609',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1;now=state['now']
  q.execute('SELECT id,status FROM system_users WHERE id=244 AND deleted=0');assert q.fetchone()['status']==0
  tabs=['erp_sale_order','erp_sale_order_items','mes_wm_sales_notice','mes_wm_sales_notice_line','mes_wm_product_sales','mes_wm_product_sales_line','mes_wm_product_sales_detail','mes_wm_stock_reserve','mes_wm_transaction','mes_wm_material_stock','mes_wm_batch','mdm_item','mes_wm_warehouse','mes_wm_warehouse_location','mes_wm_warehouse_area','mes_qc_oqc']
  backup={'state':state,'tables':{}};data={};schemas={}
  for t in tabs:
   q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');rs=q.fetchall();backup['tables'][t]=rs;data[t]={r['id']:r for r in rs}
   q.execute('SHOW COLUMNS FROM `'+t+'`');schemas[t]={r['Field']:r for r in q.fetchall()}
  (P/'shipping-drafts-locked-before.json').write_text(json.dumps(backup,ensure_ascii=False,default=str,indent=2),encoding='utf8')
  def insert(t,v):
   v={**v,'creator':'244','updater':'244','create_time':now,'update_time':now,'deleted':0}
   assert all(k in schemas[t] for k in v)
   q.execute('INSERT INTO `'+t+'` ('+','.join('`'+k+'`' for k in v)+') VALUES ('+','.join(['%s']*len(v))+')',list(v.values()));return q.lastrowid
  for entry in source['orders']:
   order=data['erp_sale_order'][entry['order_id']];no=order['no'];lines=[x for x in data['erp_sale_order_items'].values() if x['order_id']==order['id']]
   assert no==entry['source_no'] and order['out_count']==0 and all(x['out_count']==0 for x in lines)
   if order['total_count']<=0:result['excluded'].append({'no':no,'reason':'退货负数量单，不生成正向发货'});continue
   assert all(x['count']>0 for x in lines) and order['status']==20
   assert not any(x['sale_order_id']==order['id'] for x in data['mes_wm_sales_notice'].values()),('Notice exists',no)
   assert not any(x['sale_order_id']==order['id'] for x in data['mes_wm_product_sales'].values()),('Outbound exists',no)
   q.execute('SELECT * FROM crm_customer WHERE id=%s AND deleted=0',(order['customer_id'],));customer=q.fetchone();assert customer
   code='FHTZ-202609-'+no;outcode='XSCK-202609-'+no
   note=f'九月销售原单{no}发货流程补录；库存为9月底结存，历史补录/实际扣库口径待确认；暂无承运商、运单和真实生产批次；尚未扣库。'
   notice=insert('mes_wm_sales_notice',{'code':code,'name':'销售订单['+no+']发货通知','client_id':customer['id'],'sale_order_id':order['id'],'sale_order_code':no,'sales_date':now,'recipient_telephone':customer['telephone'] or customer['mobile'],'recipient_address':customer['detail_address'],'status':3,'out_status':0,'remark':note})
   sales=insert('mes_wm_product_sales',{'code':outcode,'name':'销售订单['+no+']出库草稿','client_id':customer['id'],'sale_order_id':order['id'],'notice_id':notice,'sales_order_code':no,'sales_date':now,'contact_telephone':customer['telephone'] or customer['mobile'],'contact_address':customer['detail_address'],'status':0,'remark':note})
   o={'no':no,'order_id':order['id'],'customer_id':customer['id'],'customer':customer['name'],'notice_id':notice,'notice_code':code,'sales_id':sales,'sales_code':outcode,'lines':[]}
   for l in lines:
    item=data['mdm_item'][l['product_id']];assert item['status']==0
    unitmatch=l['product_unit_id']==item['unit_measure_id']
    ln=f'原订单明细{l["id"]}；销售单位ID{l["product_unit_id"]}；物料主单位ID{item["unit_measure_id"]}；'+('单位一致。' if unitmatch else '单位换算待确认，草稿数量按原销售数保留，禁止据此扣库。')
    nl=insert('mes_wm_sales_notice_line',{'notice_id':notice,'sale_order_item_id':l['id'],'item_id':item['id'],'quantity':l['count'],'out_count':0,'oqc_check_flag':0,'remark':ln})
    sl=insert('mes_wm_product_sales_line',{'sales_id':sales,'sale_order_item_id':l['id'],'notice_line_id':nl,'item_id':item['id'],'enter_unit_measure_id':l['product_unit_id'],'enter_quantity':l['count'],'quantity':l['count'],'oqc_check_flag':0,'quality_status':None,'remark':ln})
    o['lines'].append({'order_item_id':l['id'],'notice_line_id':nl,'sales_line_id':sl,'item_id':item['id'],'quantity':str(l['count']),'sale_unit_id':l['product_unit_id'],'primary_unit_id':item['unit_measure_id'],'unit_match':unitmatch})
   # Native submitSalesNotice sets RESERVED=1 while no physical stock reservation has yet occurred.
   q.execute('UPDATE erp_sale_order SET out_status=1,updater=%s,update_time=%s WHERE id=%s',('244',now,order['id']))
   result['orders'].append(o)
  protected=['erp_sale_order_items','mes_wm_product_sales_detail','mes_wm_stock_reserve','mes_wm_transaction','mes_wm_material_stock','mes_wm_batch','mdm_item','mes_wm_warehouse','mes_wm_warehouse_location','mes_wm_warehouse_area','mes_qc_oqc']
  for t in protected:
   q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');assert q.fetchall()==backup['tables'][t],('Unintended stock/quality changes',t)
  assert len(result['orders'])==23 and sum(len(o['lines']) for o in result['orders'])==75
  c.commit();result.update(committed=True,at=str(now),counts={'notices':23,'outbound_drafts':23,'lines':75,'unit_mismatch_lines':sum(not l['unit_match'] for o in result['orders'] for l in o['lines'])})
  (P/'shipping-drafts-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!='orders'},ensure_ascii=False))
except Exception:c.rollback();raise
finally:
 with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',('sales-ledger-202609',))
 c.close()