from pathlib import Path
import sqlite3, re, json, os
DBS=[Path('/opt/struktur/social-media-agent/cases.db'),Path('/opt/struktur/social-media-agent/posts.db'),Path('/opt/struktur/social-media-radar/signals/radar.db'),Path('/opt/struktur/knowledge.db')]
roots=[Path('/opt/struktur/social-media-agent'),Path('/opt/struktur/social-media-radar')]
# Collect table names from application SQL, excluding tests, caches and backups.
code_tables={}
rx=re.compile(r'(?i)\b(?:INSERT\s+(?:OR\s+IGNORE\s+)?INTO|UPDATE|DELETE\s+FROM|CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?)\s+[`\"]?([A-Za-z_][A-Za-z0-9_]*)')
for root in roots:
  for p in root.rglob('*.py'):
    s=str(p)
    if any(x in s for x in ('/.venv/','/__pycache__/','/tests/')) or '.bak' in p.name: continue
    try:t=p.read_text(errors='replace')
    except OSError: continue
    for table in rx.findall(t): code_tables.setdefault(table,set()).add(str(p.relative_to(root)))
print('RAW_DB_SCHEMA_AUDIT_V1')
print('CODE_TABLE_REFERENCES')
for t,paths in sorted(code_tables.items()): print(t+'\t'+';'.join(sorted(paths)))
for db in DBS:
 print('\nDB\t'+str(db))
 print('EXISTS\t'+str(db.exists()))
 if not db.exists(): continue
 try:
  uri='file:'+str(db.resolve())+'?mode=ro'
  con=sqlite3.connect(uri,uri=True,timeout=3)
  con.row_factory=sqlite3.Row
  print('INTEGRITY_CHECK')
  print('\n'.join(str(r[0]) for r in con.execute('PRAGMA integrity_check').fetchall()))
  print('FOREIGN_KEY_CHECK')
  rows=con.execute('PRAGMA foreign_key_check').fetchall(); print('ok' if not rows else '\n'.join('|'.join(str(x) for x in r) for r in rows))
  tables=[r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")]
  print('TABLES\t'+json.dumps(tables,ensure_ascii=False))
  for t in tables:
   safe=t.replace('"','""')
   cols=[dict(r) for r in con.execute('PRAGMA table_info("'+safe+'")')]
   fks=[dict(r) for r in con.execute('PRAGMA foreign_key_list("'+safe+'")')]
   idx=[dict(r) for r in con.execute('PRAGMA index_list("'+safe+'")')]
   print('TABLE_DETAIL\t'+t+'\tCOLUMNS\t'+json.dumps(cols,ensure_ascii=False,separators=(',',':')))
   print('TABLE_DETAIL\t'+t+'\tFKS\t'+json.dumps(fks,ensure_ascii=False,separators=(',',':')))
   print('TABLE_DETAIL\t'+t+'\tINDEXES\t'+json.dumps(idx,ensure_ascii=False,separators=(',',':')))
  con.close()
 except Exception as e: print('ERROR\t'+type(e).__name__+'\t'+str(e))