import json,re
from decimal import Decimal
from compare import ROOT,equal,second_datetime
WRITE=re.compile(r'^\s*(INSERT|UPDATE|DELETE|ALTER|DROP|CREATE|TRUNCATE|MERGE|REPLACE|GRANT|REVOKE)\b',re.I)
def check_read_sql(sql):
 assert not WRITE.match(sql),'Unexpected business SQL mutation'
 assert re.match(r'^\s*(SELECT|SHOW|SET SCHEMA|CALL SP_TABLEDEF)\b',sql,re.I),'Unexpected statement kind'
def audit(expected_each=10,final_required=True):
 out={}
 for lane in ('B','C'):
  p=ROOT/'private-evidence'/lane;f=json.loads((p/'fixture-journal.json').read_text());assert f['committed'] and len(f['dml'])==3 and len(f['before'])==len(f['after'])==32
  assert {x['table'] for x in f['dml']}=={'equipment','dishes','dishes_img'}
  for item in f['dml']:
   t=item['table'];r=next(r for r in f['after'][t] if int(r['id'])==item['generated_PK']);equal(r,item['full_physical_inserted_row'],lane+'.insert_physical_full_row');assert set(r)==set(item['values'])|{'id'} and len(item['values'])=={'equipment':12,'dishes':58,'dishes_img':8}[t]
   for k,v in item['values'].items():
    if type(v) is int:assert int(r[k])==v
    elif re.fullmatch(r'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}',v):assert second_datetime(r[k])==v
    elif v=='0.00':assert Decimal(str(r[k]))==Decimal(v)==0
    else:equal(r[k],v,lane+'.insert_literal.'+t+'.'+k)
   if t=='equipment':assert int(r['store_id'])==2147001113 and int(r['type'])==60 and int(r['status'])==0 and int(r['mate_flag'])==1 and r['matecode']=='!NO_PAIR_CDXAD11E70B!'
   else:assert int(r['store_id'])==2147001112 and int(r['file_id'])==(24452 if lane=='B' else 9715)
   if t=='dishes':assert int(r['source'])==3 and int(r['user_delete'])==1 and int(r['is_delete'])==0
   if t=='dishes_img':assert int(r['dishes_id'])==f['owned']['dishes'] and r['cont_sign']==''
  for t,old in f['before'].items():
   if t in ('equipment','dishes','dishes_img'):assert old==[] and len(f['after'][t])==1
   else:equal(old,f['after'][t],lane+'.old29_unchanged.'+t)
  equal(f['natural_before'],f['natural_after'],lane+'.preparation_natural')
  counts={'HTTP':0,'HTTP_DML':0,'HTTP_DDL':0,'preparation_INSERT':3,'preparation_UPDATE':0,'preparation_DELETE':0,'business_SELECT':0,'metadata_session':0,'before_original32_sets':0,'after_original32_sets':0,'natural4_each_before_after':0};coverage={}
  for line in (p/'private-sql.jsonl').read_text().splitlines():
   r=json.loads(line);assert r['layer']=='ThinkPHP framework listener debug interpolation, not PDO queryString/server audit';sql=r['sql'];check_read_sql(sql)
   if re.match(r'^\s*SELECT\b',sql,re.I) and not re.search(r'\bSYSOBJECTS\b|\bSYSCOLUMNS\b|USER_TAB_COLUMNS|USER_CONS_COLUMNS|USER_CONSTRAINTS|\bDUAL\b',sql,re.I):counts['business_SELECT']+=1;coverage[r['scenario']]=coverage.get(r['scenario'],0)+1
   else:counts['metadata_session']+=1
  for line in (p/'private-wire.jsonl').read_text().splitlines():
   w=json.loads(line);counts['HTTP']+=1;counts['before_original32_sets']+=32;counts['after_original32_sets']+=32;counts['natural4_each_before_after']+=4;equal(w['before'],f['after'],w['scenario']+'.before32');equal(w['original'],f['after'],w['scenario']+'.after32');equal(w['natural'],f['natural_after'],w['scenario']+'.natural4');equal(w['protect_before'],f['before_protect'],'original natural protection');equal(w['protect_before'],w['protect_after'],'natural unchanged');assert set(w['operations'])<={'select','other'} and coverage.get(w['scenario'],0)>0
  assert counts['HTTP']==expected_each
  for path in p.glob('run-*.json'):
   run=json.loads(path.read_text());assert run['native_exit']==0 and run['own_tokens']==2 and run['all_own_tokens_absent'];equal(run['after'],f['after'],lane+'.run32');equal(run['natural_after'],f['natural_after'],lane+'.run_natural')
  if final_required:
   final=json.loads((p/'final-original.json').read_text());equal(final['original'],f['after'],lane+'.final32');equal(final['natural'],f['natural_after'],lane+'.final_natural4');equal(final['protect'],f['before_protect'],lane+'.final_protection')
   cleanup=json.loads((ROOT/f'resource-cleanup-{lane}.json').read_text());assert all(cleanup[k] for k in ('port_closed','pid_gone','source_removed','private_cache_cli_header_native_removed','evidence_0600','evidence_dir_0700','preserved_master_original18102')) and cleanup['cache_files_before_cleanup']==0
  out[lane]={'counts':counts,'scenario_business_SELECT':coverage,'layer':'framework listener debug interpolation; separate independent physical PDO original readbacks; NOT PDO queryString or server trace','generated_own_PK':f['owned'],'natural_device_credentials_consumed':0}
 ddl=json.loads((ROOT/'ddl-apply-C.json').read_text());independent=json.loads((ROOT/'ddl-independent-readback-C.json').read_text());rehearsal=json.loads((ROOT/'ddl-rehearsal-C.json').read_text());assert ddl['state']=='APPLIED_VERIFIED' and ddl['business_DDL_attempts']==1 and ddl['business_DML']==0 and independent['independent_new_process_PDO'] and independent['whole_postDDL_metadata_exact'] and independent['original_rows0'];assert rehearsal['status']=='PASSED' and len(rehearsal['temporary_DDL'])==3 and len(rehearsal['cases'])==6 and rehearsal['temporary_rows_rolled_back'] and rehearsal['temporary_object_absent'] and rehearsal['business_original_schema_unchanged']
 out['C_approved_schema']={'business_DDL':1,'business_DML':0,'temporary_DDL':3,'temporary_rows_rollback0':True,'old12_columns_indexes_constraints_triggers_exact':True,'STATUS':'SMALLINT DEFAULT1 NOTNULL CHECK0..255','actual_DDL_SHA256':ddl['sha256']}
 return out
if __name__=='__main__':
 r=audit();p=ROOT/'sql-audit.json';assert not p.exists(),'Do not overwrite sealed SQL audit';p.write_text(json.dumps(r,indent=2)+'\n');print(json.dumps(r))