#!/usr/bin/env python3
import json,re
from compare import ROOT,equal

def no_business_write(sql):return not re.match(r'^\s*(insert|update|delete|alter|drop|create|truncate|grant|revoke)\b',sql,re.I)
def audit():
 out={}
 for lane in ('B','C'):
  d=ROOT/'private-evidence'/lane;f=json.loads((d/'fixture-journal.json').read_text());assert f['committed'] and len(f['dml'])==6
  inserts=[r for r in f['dml'] if 'values' in r];updates=[r for r in f['dml'] if 'set' in r];assert len(inserts)==4 and len(updates)==2
  for module,tenant in [('store',2147001111),('terminal',2147001112)]:
   ids=f['owned'][module];u=next(r for r in updates if r['tenant']==tenant);assert u=={'table':'order','PK':ids['order'],'tenant':tenant,'set':{'is_delete':0}}
   old=next(r for r in f['before']['order'] if int(r['order_id'])==ids['order']);new=next(r for r in f['after']['order'] if int(r['order_id'])==ids['order']);expected=dict(old,is_delete=0 if type(old['is_delete']) is int else '0');equal(new,expected,lane+'.exact_one_column_fixture_update');assert int(new['order_status'])==20 and int(new['pay_status'])==10 and new['order_no']=='CDXCM11F782_'+module
   for name in ['total_price','order_price','coupon_money','points_money','pay_price','update_price','express_price']:assert float(new[name])==0
   for table in ('recharge_order','recharge_order_plan'):
    ins=next(r for r in inserts if r['table']==table and int(r['values']['store_id'])==tenant);pk='order_id' if table=='recharge_order' else 'order_plan_id';assert pk not in ins['values'] and ins['generated_PK']==ids[table]
    row=next(r for r in f['after'][table] if int(r[pk])==ids[table]);assert int(row['store_id'])==tenant and int(row['plan_id'])==0
    for key,value in ins['values'].items():
     if key.endswith('_money') or key in ('pay_price','actual_money','withdrawable_amount','activity_principal_amount','recharg_before','recharg_after','money'):assert float(value)==0 and float(row[key])==0
     elif key=='recharge_date':assert str(row[key])[:10]==value
     elif type(value) is int:assert int(row[key])==value
     else:equal(row[key],value,lane+'.fixture_insert.'+table+'.'+key)
    if table=='recharge_order':assert int(row['user_id'])==ids['user'] and int(row['pay_status'])==10 and int(row['is_delete'])==1 and int(row['trade_id'])==0
    else:assert int(row['order_id'])==ids['recharge_order']
  equal(f['before_protect'],f['after_protect'],lane+'.natural_counts')
  for table in ('user','goods','upload_file','order_goods'):equal(f['before'][table],f['after'][table],lane+'.reused_base_rows')
  count={'HTTP_business_DML':0,'DDL':0,'fixture_UPDATE':2,'fixture_INSERT':4,'SELECT':0,'metadata':0,'HTTP':0,'original_12_table_sets':0}
  for line in (d/'private-sql.jsonl').read_text().splitlines():
   sql=json.loads(line)['sql'];assert no_business_write(sql),'HTTP contains write: '+sql
   if re.match(r'^show\b',sql,re.I) or re.search(r'\bsyscolumns\b|\bsysobjects\b|USER_TAB_COLUMNS|SP_TABLEDEF|USER_CONS_COLUMNS|USER_CONSTRAINTS',sql,re.I):count['metadata']+=1
   elif re.match(r'^select\b',sql,re.I):count['SELECT']+=1
  for line in (d/'private-wire.jsonl').read_text().splitlines():
   w=json.loads(line);equal(w['original'],f['after'],w['scenario']+'.own_readonly');count['HTTP']+=1;count['original_12_table_sets']+=12
  for p in d.glob('run-*.json'):
   run=json.loads(p.read_text());assert run['own_unchanged'] and run['natural_count_unchanged'] and run['cache_cleared']
  out[lane]=count
 return out

if __name__=='__main__':
 r=audit();(ROOT/'sql-audit.json').write_text(json.dumps(r,indent=2)+'\n');print(json.dumps(r))