import pathlib,re,json
R=pathlib.Path('/Users/dave/Code/pom-vault')
funcs='has_view is_empty throws_matching pass skip ok is isnt like unlike matches imatches doesnt_match doesnt_imatch cmp_ok throws_ok lives_ok results_eq results_ne set_eq set_ne bag_eq bag_ne has_table hasnt_table has_column hasnt_column col_type_is col_not_null col_is_null col_default_is has_function hasnt_function function_returns function_privs_are table_privs_are column_privs_are has_index has_constraint has_trigger is_definer isnt_definer policies_are columns_are'.split()
# Lexical tokens retain offsets, ignoring comments; quoted SQL bodies stay atomic.
pat=re.compile(r"--[^\n]*|/\*[\s\S]*?\*/|'(?:''|[^'])*'|\$([A-Za-z_][A-Za-z_0-9]*|)\$[\s\S]*?\$\1\$|[A-Za-z_][A-Za-z_0-9]*|[^\s]",re.M)
rows=[]
paths=list((R/'supabase/tests').rglob('*.sql'))+[R/'product/persynl-physical-data-model/smoke-test.sql',R/'product/persynl-physical-data-model/smoke-g6.sql']
for p in paths:
 s=p.read_text(); toks=[m for m in pat.finditer(s) if not m[0].startswith(('--','/*'))]
 for i,t in enumerate(toks):
  fn=t[0].lower()
  if fn not in funcs or i+1==len(toks) or toks[i+1][0]!='(':continue
  depth=0; args=[]; start=toks[i+1].end(); end=None
  for z in toks[i+1:]:
   if z[0]=='(':depth+=1
   elif z[0]==')':
    depth-=1
    if depth==0:args.append(s[start:z.start()].strip());end=z.end();break
   elif z[0]==',' and depth==1:args.append(s[start:z.start()].strip());start=z.end()
  if end is None:continue
  desc=args[-1] if args else ''
  desc=re.sub(r"^'|'$",'',desc).replace("''", "'")
  if len(desc)>350:desc=f'{fn}: '+desc[:200]
  body=s[t.start():end]
  source=bool(re.search('pg_get_functiondef|prosrc',body,re.I))
  schema=bool(re.search('information_schema|pg_proc|pg_class|pg_constraint|pg_index|to_regprocedure|to_regclass',body,re.I)) or fn.startswith(('has_','hasnt_','col_','columns_','is_definer','isnt_definer'))
  classification='internals' if source or schema else 'behavior'
  reason='Inspects stored implementation text, so matching text can pass without the promised runtime behavior.' if source else ('Checks database schema/metadata rather than exercising the operation; may be a legitimate migration or security contract.' if schema else 'Asserts query results, mutation effects, privileges, or accepted/rejected operations against SQL fixtures.')
  path=str(p.relative_to(R)); notes='SQL assertion declaration; not executed. '
  if '/upgrade/' in path:notes+='Requires historical fixture and migration upgrade harness.'
  elif '/pgtap/helpers/' in path or '/upgrade/data/' in path:notes+='Fixture/helper file; execution depends on including harness.'
  elif path.endswith('smoke-test.sql'):notes+='Historical G5 schema harness; not current-schema proof.'
  else:notes+='Local PostgreSQL and applicable fixtures required.'
  rows.append(dict(file=path,line=s.count('\n',0,t.start())+1,name=desc,purpose=desc,classification=classification,restates='yes' if source else 'no',reason=reason,execution_notes=notes,evidence=body,confidence='rule-based; assertion retained for review',framework='SQL / pgTAP'))
 # procedural smoke assertions cannot be tokenized as pgTAP: identify failure checks individually
 if 'smoke-' in p.name or '/revert/' in str(p):
  for m in re.finditer(r"RAISE\s+EXCEPTION\s+'((?:''|[^'])*)'",s,re.I):
   desc=m[1].replace("''", "'"); rows.append(dict(file=str(p.relative_to(R)),line=s.count('\n',0,m.start())+1,name=desc,purpose='Guard against: '+desc,classification='mixed',restates='unknown',reason='Procedural SQL failure guard; surrounding condition requires semantic review.',execution_notes='Smoke/revert harness, individual exception guard; not executed.',evidence=s[max(0,m.start()-350):m.end()],confidence='extracted, not individually reviewed',framework='SQL procedural'))
pathlib.Path('/tmp/pom-tests-sql.json').write_text(json.dumps(rows,indent=2))
from collections import Counter
print(len(rows),Counter(x['classification'] for x in rows),Counter(x['restates'] for x in rows))
print('SOURCE CHECKS')
for x in rows:
 if x['restates']=='yes':print(x['file'],x['line'],x['name'])