import json,re
from pathlib import Path
P=Path(__file__).resolve().parent
TABLES={'user','upload_file','user_grade','user_points_log','user_balance_log','user_feedback','dishes','store_user','store_user_role','store_user_relation','store_role','store_role_menu','store_menu','store_menu_api','store_api'}
def audit(events,final=False):
 counts={'business_SELECT':0,'metadata_SELECT':0,'metadata_SHOW':0,'metadata_CALL':0,'prepare_failures':0,'execute_failures':0,'actual_prepares':0,'actual_executes':0,'bind_plans':0,'DML_DDL':0}
 plans={};pending={}
 for e in events:
  layer=e['layer'];scenario=e['scenario']
  if layer.startswith('framework original bind plan'):
   counts['bind_plans']+=1;plans[scenario,e['sql_input']]=e['bind_plan'];continue
  sql=e.get('queryString','');plain=re.sub(r"'(?:''|[^'])*'",'?',sql).strip();op=e.get('operation')
  if op=='metadata_SHOW':assert re.fullmatch(r'SHOW FULL COLUMNS FROM `yoshop_([a-z_]+)`',sql) and re.fullmatch(r'SHOW FULL COLUMNS FROM `yoshop_([a-z_]+)`',sql)[1] in TABLES
  elif op=='metadata_CALL':assert sql=='CALL SP_TABLEDEF(?, ?)'
  else:assert op=='SELECT' and plain.upper().startswith('SELECT ')
  assert not re.search(r'\b(?:INSERT|UPDATE|DELETE|REPLACE|MERGE|CREATE|ALTER|DROP|TRUNCATE|INTO|OUTFILE|EXEC|DO)\b|;',plain,re.I)
  if layer=='actual PDO.prepare':
   counts['actual_prepares']+=1;counts['prepare_failures']+=e.get('prepared') is False
   if e.get('prepared'):pending.setdefault((scenario,sql),[]).append(e)
   continue
  assert layer=='actual PDOStatement prepare/bindValue/execute'
  counts['actual_executes']+=1;counts['execute_failures']+=not e['success'];assert e['success']
  assert pending.get((scenario,sql)), 'Actual execute without matching successful prepare'
  pending[scenario,sql].pop(0)
  if op=='metadata_CALL':
   b=e['bindValue'];assert set(b)=={'1','2'} and b['1']['value']=='AIZHCT_APP' and all(x['pdo_type']==2 for x in b.values());assert b['2']['value'].startswith('YOSHOP_') and b['2']['value'][7:].lower() in TABLES;counts['metadata_CALL']+=1
  elif op=='metadata_SHOW':counts['metadata_SHOW']+=1
  elif 'yoshop_' in plain.lower():counts['business_SELECT']+=1
  else:counts['metadata_SELECT']+=1
  if final:
   assert (scenario,sql) in plans
   expected={}
   for k,v in plans[scenario,sql].items() if isinstance(plans[scenario,sql],dict) else enumerate(plans[scenario,sql]):
    param=str(int(k)+1) if str(k).isdigit() else ':'+k
    if isinstance(v,list):value,typ=v
    else:value,typ=v,2
    if typ==1 and value=='':value=0
    elif typ==21:value=float(value) if isinstance(value,str) else value;typ=2
    expected[param]=(value,typ)
   assert set(expected)==set(e['bindValue'])
   for k,(value,typ) in expected.items():assert e['bindValue'][k]['value']==value and e['bindValue'][k]['pdo_type']==typ and e['bindValue'][k]['ok'] is True
 assert all(not v for v in pending.values()),'Successful prepare not followed by recorded execute'
 return counts
def all_audits():
 out={}
 for lane in 'BC':
  for final in (False,True):
   path=P/'private-evidence'/(lane+'-final' if final else lane)/('pdo-native-final.jsonl' if final else 'pdo-native.jsonl')
   events=[json.loads(l) for l in path.read_text().splitlines() if l.strip()];key=lane+('-final' if final else '-original');out[key]={'all_preflight_auth_HTTP':audit(events,final),'HTTP_only':audit([e for e in events if e['scenario'].startswith('PILOT_LF_')],final)}
 return out
if __name__=='__main__':
 out=all_audits();(P/'sql-audit.json').write_text(json.dumps(out,indent=2)+'\n');print(json.dumps(out))