from pathlib import Path
import os,re,sqlite3,tarfile,hashlib,json,subprocess,tempfile
DBS=[Path('/opt/struktur/social-media-agent/db/posts.db'),Path('/var/lib/sma-data/signals.db'),Path('/opt/struktur/social-media-agent/cases.db')]
ROOTS=[Path('/opt/struktur/social-media-agent'),Path('/opt/struktur/social-media-radar')]
print('AA053_PERSISTENCE_AUDIT_V1')
print('STATIC_READER_WRITER_ANALYSIS')
for db in DBS:
 print('\nDB',db,'EXISTS',db.exists())
 needle=str(db); base=db.name
 for root in ROOTS:
  for p in root.rglob('*'):
   if not p.is_file() or any(x in str(p) for x in ('/.venv/','/__pycache__/','/.git/')) or '.bak' in p.name: continue
   if p.suffix.lower() not in {'.py','.sh','.html','.js','.json','.yaml','.yml','.conf','.service','.md'}: continue
   try:t=p.read_text(errors='replace')
   except OSError: continue
   if needle in t or base in t and ('sqlite' in t or 'DB' in t or 'database' in t.lower()):
    lines=[f'{i+1}:{line.strip()}' for i,line in enumerate(t.splitlines()) if needle in line or (base in line and ('sqlite' in line or 'DB' in line or 'database' in line.lower()))]
    if lines: print('REF',p,'\n  '+'\n  '.join(lines[:20]))
 if db.exists():
  c=sqlite3.connect('file:'+str(db)+'?mode=ro',uri=True)
  print('TABLES', [r[0] for r in c.execute("select name from sqlite_master where type='table' order by name")])
  print('INTEGRITY',[r[0] for r in c.execute('pragma integrity_check')])
  print('FOREIGN_KEYS','ok' if not list(c.execute('pragma foreign_key_check')) else 'FAIL')
  c.close()
print('\nGENERIC_SQLITE_CONNECT_AND_DB_PATH_REFERENCES')
for root in ROOTS:
 for p in root.rglob('*'):
  if not p.is_file() or any(x in str(p) for x in ('/.venv/','/__pycache__/','/.git/')) or '.bak' in p.name: continue
  if p.suffix.lower() not in {'.py','.sh','.html','.js','.json','.yaml','.yml','.conf','.service'}: continue
  try:t=p.read_text(errors='replace')
  except OSError: continue
  hits=[f'{i+1}:{line.strip()}' for i,line in enumerate(t.splitlines()) if re.search(r'sqlite3?\.connect|sqlite|_DATABASE_PATH|DB_PATH|\.db',line,re.I)]
  if hits: print('FILE',p,'\n  '+'\n  '.join(hits[:30]))
print('\nBRAND_CONFIGS')
for p in [Path('/opt/struktur/brands/mallorca-airservices/config/brand.json'),Path('/opt/struktur/brands/agent-solutions/config/brand.json')]:
 print('CONFIG',p,'EXISTS',p.exists())
 if p.exists():
  try:
   d=json.loads(p.read_text()); print(json.dumps({k:d.get(k) for k in ['brand_id','brand_name','signals_database','cases_database','artifact_path','facebook_output_path','linkedin_output_path']},ensure_ascii=False,sort_keys=True))
  except Exception as e: print('ERROR',e)
print('\nPROCESS_FD_BINDINGS')
try:
 out=subprocess.run(['lsof','-nP'],capture_output=True,text=True,timeout=20).stdout
 for line in out.splitlines():
  if any(str(x) in line for x in DBS): print(line)
except Exception as e: print('L SOF_UNAVAILABLE',type(e).__name__,e)
print('\nSHADOW_DB_SEARCH')
search_roots=[Path('/opt/struktur/social-media-agent'),Path('/opt/struktur/social-media-radar'),Path('/tmp'),Path('/var/log')]
for root in search_roots:
 count=0
 try:
  for p in root.rglob('*.db'):
   try: st=p.stat()
   except OSError: continue
   print('DB_FILE',p,'SIZE',st.st_size,'MTIME',st.st_mtime)
   count+=1
   if count>=300: print('TRUNCATED_ROOT',root); break
 except OSError as e: print('SEARCH_ERROR',root,type(e).__name__,e)
print('\nBACKUP_VERIFICATION')
backup=Path('/root/backups-h012/AA-053/20260829T110406Z/sma-code-baseline.tar.gz')
print('ARCHIVE',backup,'EXISTS',backup.exists(),'SIZE',backup.stat().st_size if backup.exists() else None)
if backup.exists():
 print('ARCHIVE_SHA256',hashlib.sha256(backup.read_bytes()).hexdigest())
 with tarfile.open(backup,'r:gz') as tf:
  members=tf.getmembers(); print('MEMBER_COUNT',len(members)); print('MEMBERS_HEAD',[(m.name,m.size) for m in members[:30]])
# inspect nearby backup tree and manifests without assuming names
base=Path('/root/backups-h012/AA-053')
if base.exists():
 for p in sorted(base.rglob('*')):
  if p.is_file():
   try: print('BACKUP_FILE',p,'SIZE',p.stat().st_size,'SHA256',hashlib.sha256(p.read_bytes()).hexdigest() if p.stat().st_size<100_000_000 else 'SKIPPED_LARGE')
   except OSError as e: print('BACKUP_FILE_ERROR',p,e)
print('\nEND_AUDIT')