"""Link non-Beijing-Tongrentang quotations to existing contract opportunities."""
import sys,json
from pathlib import Path
from datetime import datetime
from decimal import Decimal
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 processed; verify instead of repeating.'
minimum=datetime(2026,9,17)
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={'quotes':[],'new_business_product_ids':[],'new_permission_ids':[],'business_headers_created':0,'excluded_customer_keyword':'北京同仁堂'}
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',('quotation-opportunities-20261014',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1;now=state['now']
  tabs=['crm_customer','crm_business','crm_business_product','crm_sale_quotation','crm_sale_quotation_product','crm_contract','crm_contract_product','erp_sale_order','erp_sale_order_items','crm_permission','crm_invoice','crm_receivable','crm_receivable_plan']
  before={};data={};schemas={}
  for t in tabs:
   q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id FOR UPDATE');rs=q.fetchall();before[t]=rs;data[t]={x['id']:x for x in rs}
   q.execute('SHOW COLUMNS FROM '+t);schemas[t]={x['Field'] for x in q.fetchall()}
  q.execute('SELECT status FROM system_users WHERE id=291 AND deleted=0');assert q.fetchone()['status']==0
  backup=P/('locked-before-'+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')
  allowed={};expected={}
  def insert(t,v,day):
   v={**v,'creator':'291','updater':'291','create_time':day,'update_time':day,'deleted':0};assert set(v)<=schemas[t]
   q.execute('INSERT INTO '+t+' ('+','.join('`'+k+'`' for k in v)+') VALUES ('+','.join(['%s']*len(v))+')',list(v.values()));return q.lastrowid
  def update(t,id,v,day):
   v={**v,'updater':'291','update_time':day};assert set(v)<=schemas[t]
   q.execute('UPDATE '+t+' SET '+','.join('`'+k+'`=%s' for k in v)+' WHERE deleted=0 AND id=%s',[*v.values(),id]);assert q.rowcount==1
   allowed.setdefault((t,id),set()).update(v);expected.setdefault((t,id),{}).update(v)
  quotes=[x for x in before['crm_sale_quotation'] if '北京同仁堂' not in data['crm_customer'][x['customer_id']]['name']]
  seen=set()
  for qt in quotes:
   ct=data['crm_contract'][qt['contract_id']];assert ct['customer_id']==qt['customer_id'] and ct['total_price']==qt['total_price']
   b=data['crm_business'].get(ct['business_id']);assert b and b['customer_id']==qt['customer_id'] and b['total_price']==qt['total_price']
   assert qt['business_id'] in (None,b['id']) and b['id'] not in seen;seen.add(b['id'])
   order=data['erp_sale_order'][ct['order_id']];assert order['contract_id']==ct['id'] and order['customer_id']==qt['customer_id']
   assert b['order_id'] in (None,order['id']) and b['order_no'] in (None,'',order['no'])
   day=max(qt['quotation_time'],qt['create_time'],minimum);assert day<=now
   update('crm_sale_quotation',qt['id'],{'business_id':b['id'],'quotation_time':day,'create_time':day},max(qt['update_time'],day))
   quote_lines=[x for x in before['crm_sale_quotation_product'] if x['quotation_id']==qt['id']]
   contract_lines=[x for x in before['crm_contract_product'] if x['contract_id']==ct['id']];assert contract_lines
   assert sorted((x['item_id'],x['item_unit_id'],x['count']) for x in quote_lines)==sorted((x['item_id'],x['item_unit_id'],x['count']) for x in contract_lines)
   for l in quote_lines:
    if l['create_time']!=day:update('crm_sale_quotation_product',l['id'],{'create_time':day},max(l['update_time'],day))
   existing_products=[x for x in before['crm_business_product'] if x['business_id']==b['id']]
   assert not existing_products,'Existing business product rows require separate reconciliation'
   total=sum(x['total_price'] for x in contract_lines);assert total==ct['total_product_price']==ct['total_price'] and ct['discount_percent']==0
   for l in contract_lines:
    id=insert('crm_business_product',{'business_id':b['id'],'item_id':l['item_id'],'item_unit_id':l['item_unit_id'],'item_price':l['item_price'],'business_price':l['contract_price'],'count':l['count'],'total_price':l['total_price']},day);result['new_business_product_ids'].append(id)
   update('crm_business',b['id'],{'order_id':order['id'],'order_no':order['no'],'total_product_price':total,'discount_percent':Decimal(0)},now)
   permissions=[x for x in before['crm_permission'] if x['biz_type']==4 and x['biz_id']==b['id'] and x['user_id']==b['owner_user_id']]
   if not permissions:result['new_permission_ids'].append(insert('crm_permission',{'biz_type':4,'biz_id':b['id'],'user_id':b['owner_user_id'],'level':1},day))
   else:assert any(x['level']==1 for x in permissions),'Business owner permission is incomplete'
   result['quotes'].append({'id':qt['id'],'no':qt['no'],'customer':data['crm_customer'][qt['customer_id']]['name'],'business_id':b['id'],'contract_id':ct['id'],'order_id':order['id'],'date':str(day),'old_date':str(qt['quotation_time']),'date_changed':day!=qt['quotation_time'] or day!=qt['create_time'],'product_lines':len(contract_lines)})
  assert len(quotes)==16 and len(result['new_business_product_ids'])==34
  after={}
  for t,rows in before.items():
   q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id');rs=q.fetchall();after[t]=rs;byid={x['id']:x for x in rs}
   for old in rows:
    new=byid[old['id']];diff={k for k in old if old[k]!=new[k]};assert diff<=allowed.get((t,old['id']),set()),(t,old['id'],diff)
    for k,v in expected.get((t,old['id']),{}).items():assert new[k]==v
   newids=set(result['new_business_product_ids']) if t=='crm_business_product' else set(result['new_permission_ids']) if t=='crm_permission' else set()
   assert set(byid)-{x['id'] for x in rows}==newids
  for x in result['quotes']:
   qt=next(r for r in after['crm_sale_quotation'] if r['id']==x['id']);b=next(r for r in after['crm_business'] if r['id']==x['business_id'])
   assert minimum<=qt['quotation_time']==qt['create_time']<=now and qt['business_id']==b['id'] and qt['customer_id']==b['customer_id']
   assert sum(r['total_price'] for r in after['crm_business_product'] if r['business_id']==b['id'])==b['total_product_price']==b['total_price']
  c.commit()
  for t,rs in after.items():
   q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id');assert q.fetchall()==rs
  result.update(committed=True,verified=True,at=str(now),quotes_linked=len(quotes),quote_dates_changed=sum(x['date_changed'] for x in result['quotes']),business_products_added=len(result['new_business_product_ids']),backup=backup.name,beijing_tongrentang_data_unchanged=True,contract_invoice_receipt_order_amounts_dates_statuses_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 ['quotes','new_business_product_ids','new_permission_ids']},ensure_ascii=False))
except Exception:c.rollback();raise
finally:
 with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',('quotation-opportunities-20261014',))
 c.close()