import sqlite3,json,datetime,glob,os
from pathlib import Path
DB='/opt/struktur/youtube-research/knowledge.db'; CE='/opt/struktur/content-extraction/data/content_extraction.db'
out={'checked_at_utc':datetime.datetime.now(datetime.timezone.utc).isoformat()}
out['service']=__import__('subprocess').run(['systemctl','show','youtube-research-e2e-worker.service','-p','ActiveState','-p','SubState','-p','MainPID','-p','NRestarts','-p','ExecMainStatus'],capture_output=True,text=True).stdout
c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
for table,where in [('videos','id=1247'),('e2e_jobs','job_id=275'),('transcript_segments','video_id=1247'),('graphiti_import_queue',"video_id='1CLc-VeEivk'")]:
 try: out[table]=[dict(r) for r in c.execute(f'select * from {table} where {where}').fetchall()]
 except Exception as e: out[table]={'error':str(e)}
# table-level references by actual columns
refs=[]
for r in c.execute("select name from sqlite_master where type='table' and name not like 'sqlite_%'"):
 t=r[0]; cols=[x[1] for x in c.execute(f'pragma table_info("{t}")')]; hitcols=[x for x in cols if x in ('video_id','youtube_id','job_id','source_video_id','reconciliation_record_id','import_identity')]
 for col in hitcols:
  val='1247' if col in ('video_id','source_video_id') else ('275' if col=='job_id' else '1CLc-VeEivk')
  try:
   rs=c.execute(f'select * from "{t}" where "{col}"=? limit 20',(val,)).fetchall()
   if rs: refs.append({'table':t,'column':col,'rows':[dict(x) for x in rs]})
  except: pass
out['references']=refs
out['trigger_defs']=[dict(r) for r in c.execute("select name,sql from sqlite_master where type='trigger'")]
out['e2e_schema']=c.execute("select sql from sqlite_master where type='table' and name='e2e_jobs'").fetchone()[0]
c.close()
ce=sqlite3.connect('file:'+CE+'?mode=ro',uri=True); ce.row_factory=sqlite3.Row
out['ce_refs']=[dict(r) for r in ce.execute("select * from ce_sources where source_video_id=1247 or youtube_id='1CLc-VeEivk'").fetchall()]; ce.close()
# Obsidian exact id/path scan, bounded managed vault
paths=[]
for p in glob.glob('/opt/obsidian-vault/YouTube-Research/**/*.md',recursive=True):
 try:
  txt=Path(p).read_text(errors='ignore')
  if '1CLc-VeEivk' in txt or 'video_id: 1247' in txt: paths.append(p)
 except: pass
out['obsidian_refs']=paths
print(json.dumps(out,ensure_ascii=False,default=str))