import json,re
from compare import ROOT,equal
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 SQL mutation'
 assert re.match(r'^\s*(SELECT|SHOW|SET SCHEMA|CALL SP_TABLEDEF)\b',sql,re.I),'Unexpected statement kind'
def audit():
 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'])==2
  assert len(f['before'])==len(f['after'])==29 and set(f['owned'])=={'store','terminal'}
  for item in f['dml']:
   assert item['table']=='store_user';row=next(r for r in f['after']['store_user'] if int(r['store_user_id'])==item['generated_PK']);assert set(row)==set(item['values'])|{'store_user_id'} and len(item['values'])==12
   for k,v in item['values'].items():
    if type(v) is int:assert int(row[k])==v
    else:equal(row[k],v,lane+'.fixture_full_column.'+k)
   assert re.fullmatch(r'!SYNTHETIC_NON_LOGIN![0-9a-f]{32}',row['password'])
   assert int(row['store_id']) in (2147001111,2147001112) and int(row['is_super'])==1 and int(row['is_delete'])==0 and int(row['user_type'])==2
   assert 'CDXOP11F64C' in row['real_name'] and row['create_by']=='CDXOP11F64C_PREPARATION'
  for t,old in f['before'].items():
   if t=='store_user':assert old==[] and len(f['after'][t])==2
   else:equal(old,f['after'][t],lane+'.old_unchanged.'+t)
  for t in ('store','store_user_role','store_user_relation','store_role','store_role_menu'):assert f['after'][t]==[]
  equal(f['natural_before'],f['natural_after'],lane+'.fixture_natural')
  identity_keys={'store_user_id','store_id','is_super','is_delete','sort','user_type','create_time','update_time','username_digest','credential_digest','realname_digest','node_digest','creator_digest'}
  for row in f['natural_after']['operator_identity_digests']:
   assert set(row)==identity_keys
   for k,v in row.items():
    if k.endswith('_digest'):assert v is None or re.fullmatch('[0-9a-fA-F]{32}',v)
  counts={'HTTP':0,'HTTP_DML':0,'DDL':0,'preparation_INSERT':2,'preparation_UPDATE':0,'preparation_DELETE':0,'business_SELECT':0,'metadata_session':0,'independent_after_original_table_sets':0,'independent_before_original_table_sets':0,'independent_natural_sets_each_before_and_after':0}
  coverage={}
  for line in (p/'private-sql.jsonl').read_text().splitlines():
   r=json.loads(line);sql=r['sql'];assert r['layer']=='ThinkPHP framework listener debug interpolation, not PDO queryString/server audit';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['independent_after_original_table_sets']+=29;counts['independent_before_original_table_sets']+=29;counts['independent_natural_sets_each_before_and_after']+=4
   equal(w['before'],f['after'],w['scenario']+'.before29');equal(w['original'],f['after'],w['scenario']+'.after29');equal(w['natural'],f['natural_after'],w['scenario']+'.natural');equal(w['protect_before'],w['protect_after'],'natural/read');assert set(w['operations'])<={'select','other'}
   if not w['scenario'].endswith('_AUTH'):assert coverage.get(w['scenario'],0)>0,'Expected true database read'
  assert counts['HTTP']==13
  run=json.loads((p/'run-NA.json').read_text());assert run['native_exit']==0 and run['own_tokens']==3 and run['all_own_tokens_absent'];equal(run['after'],f['after'],lane+'.run_final29');equal(run['natural_after'],f['natural_after'],lane+'.run_natural')
  final=json.loads((p/'final-original.json').read_text());equal(final['original'],f['after'],lane+'.final29');equal(final['natural'],f['natural_after'],lane+'.final_natural');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 and cleanup['evidence_files']==7
  out[lane]={'counts':counts,'scenario_business_SELECT':coverage,'SQL_evidence_layer':'framework Db::listen getRealSql/getLastSql debug interpolation, NOT PDO prepared queryString or server trace; separate independent physical PDO readbacks','final_all29_and_natural4_exact':True,'operator_generated_IDs':f['owned'],'natural_credentials_values_consumed':0}
 return out
if __name__=='__main__':
 r=audit();(ROOT/'sql-audit.json').write_text(json.dumps(r,indent=2)+'\n');print(json.dumps(r))