"""Set document/line creation dates to actual business dates as user requested.
User explicitly excluded planned receivables and expected opportunity closing dates.
"""
import sys,json,csv
from pathlib import Path
P=Path(__file__).parent
sys.stdout.reconfigure(encoding='utf8')
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
import pymysql
canon=lambda x:json.loads(json.dumps(x,ensure_ascii=False,default=str))
headers={'crm_sale_quotation':'quotation_time','crm_contract':'order_date','erp_sale_order':'order_time','crm_invoice':'invoice_time','crm_receivable':'return_time'}
children={'crm_sale_quotation_product':('crm_sale_quotation','quotation_id'),'crm_contract_product':('crm_contract','contract_id'),'erp_sale_order_items':('erp_sale_order','order_id')}
protected=['crm_receivable_plan','crm_business','crm_customer','crm_clue','crm_contact','crm_follow_up_record','crm_permission']
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)
try:
 with c.cursor() as q:
  q.execute('SELECT DATABASE() db,NOW() now,GET_LOCK(%s,10) locked',('sales-ledger-202609',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1
  before={};data={}
  for table in [*headers,*children,*protected]:
   q.execute('SELECT * FROM '+table+' WHERE deleted=0 ORDER BY id'+(' FOR UPDATE' if table not in protected else ''));rows=q.fetchall();before[table]=rows;data[table]={x['id']:x for x in rows}
  backup=P/('business-create-times-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')
  targets={};missing=[];changes=[];counts={}
  for table,field in headers.items():
   for x in before[table]:
    date=x[field]
    if date is None:missing.append({'table':table,'id':x['id'],'reason':'无明确业务日期，保留创建时间'});continue
    assert date<=state['now'],('Actual date in future',table,x['id'])
    targets[(table,x['id'])]=(date,field)
  for table,(parent,fk) in children.items():
   for x in before[table]:
    if (parent,x[fk]) in targets:targets[(table,x['id'])]=(targets[(parent,x[fk])][0],parent+'.'+headers[parent])
    else:missing.append({'table':table,'id':x['id'],'reason':'父单无有效业务日期，保留创建时间'})
  for (table,id),(date,basis) in targets.items():
   x=data[table][id]
   if x['create_time']==date:continue
   q.execute('UPDATE '+table+' SET create_time=%s,updater=%s,update_time=%s WHERE id=%s AND deleted=0',(date,'291',state['now'],id));assert q.rowcount==1
   changes.append({'table':table,'id':id,'old_create_time':str(x['create_time']),'create_time':str(date),'basis':basis});counts[table]=counts.get(table,0)+1
  expected_changed={(x['table'],x['id']) for x in changes};after={}
  for table,rows in before.items():
   q.execute('SELECT * FROM '+table+' WHERE deleted=0 ORDER BY id');newrows=q.fetchall();after[table]=newrows;assert len(rows)==len(newrows)
   for old,new in zip(rows,newrows):
    assert old['id']==new['id'];diff={k for k in old if old[k]!=new[k]}
    assert diff<=({'create_time','updater','update_time'} if (table,old['id']) in expected_changed else set()),(table,old['id'],diff)
    if (table,old['id']) in targets:assert new['create_time']==targets[(table,old['id'])][0]
  c.commit()
  for table,expected in after.items():
   q.execute('SELECT * FROM '+table+' WHERE deleted=0 ORDER BY id');assert q.fetchall()==expected,('Post-commit mismatch',table)
  result={'committed':True,'verified':True,'at':str(state['now']),'counts':counts,'headers_changed':sum(counts.get(t,0) for t in headers),'details_changed':sum(counts.get(t,0) for t in children),'changes':changes,'missing_preserved':missing,'excluded':'用户确认：回款计划和商机预计日期不改；客户/线索/跟进的预约时间不作创建日期','backup':backup.name,'other_business_fields_unchanged':True}
  (P/'business-create-times-result.json').write_text(json.dumps(result,ensure_ascii=False,indent=2),encoding='utf8')
  with (P/'营销业务创建时间同步清单.csv').open('w',encoding='utf-8-sig',newline='') as f:
   w=csv.writer(f);w.writerow(['业务表','记录ID','原创建时间','新创建时间','依据'])
   for x in changes:w.writerow([x['table'],x['id'],x['old_create_time'],x['create_time'],x['basis']])
  print(json.dumps({k:v for k,v in result.items() if k!='changes'},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()