import sqlite3,json,datetime,subprocess,glob,os,re
from pathlib import Path
DB='/opt/struktur/youtube-research/knowledge.db'; CE='/opt/struktur/content-extraction/data/content_extraction.db'; checked=datetime.datetime.now(datetime.timezone.utc).isoformat(); out={'checked_at_utc':checked}
# source db
c=sqlite3.connect('file:'+DB+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
out['videos']={r['transcript_status']:r['n'] for r in c.execute('select coalesce(transcript_status,"NULL") transcript_status,count(*) n from videos group by transcript_status')}; out['videos']['total']=c.execute('select count(*) from videos').fetchone()[0]
out['e2e']={r['status']:r['n'] for r in c.execute('select status,count(*) n from e2e_jobs group by status')}
out['job274']=dict(c.execute('select e.job_id,e.video_id,e.youtube_id,e.status,e.step,e.attempts,e.lease_until,v.transcript_status,v.transcript_source,length(trim(coalesce(v.transcript,""))) transcript_len,v.transcript_hash from e2e_jobs e join videos v on v.id=e.video_id where e.job_id=274').fetchone())
out['job275']=dict(c.execute('select job_id,status,step,attempts from e2e_jobs where job_id=275').fetchone())
out['segments274']=c.execute('select count(*) from transcript_segments where video_id=1246').fetchone()[0]
q=[dict(r) for r in c.execute('select * from graphiti_import_queue where video_id=?',('d2MiSnElwrw',))]; out['queue274']=q
out['queue']={r['graphiti_status']:r['n'] for r in c.execute('select graphiti_status,count(*) n from graphiti_import_queue group by graphiti_status')}; out['queue']['total']=sum(out['queue'].values())
out['queue']['duplicate_import_identities']=c.execute('select count(*) from (select import_identity from graphiti_import_queue where import_identity is not null group by import_identity having count(*)>1)').fetchone()[0]; out['queue']['duplicate_reconciliation_ids']=c.execute('select count(*) from (select reconciliation_record_id from graphiti_import_queue where reconciliation_record_id is not null group by reconciliation_record_id having count(*)>1)').fetchone()[0]; out['queue']['release_nonzero']=c.execute('select count(*) from graphiti_import_queue where graphiti_release<>0').fetchone()[0]
# active transcript candidates excludes all terminal statuses
allowed=('queued','supadata_retry','supadata_quota_wait','blocked_transcript_fetch','local_transcript_pending','local_transcript_retry','local_transcript_processing')
ph=','.join('?'*len(allowed)); out['active_transcript_candidates']=c.execute(f"select count(*) from e2e_jobs e join videos v on v.id=e.video_id where e.status in ({ph}) and length(trim(coalesce(v.transcript,'')))=0 and coalesce(v.transcript_status,'') not in ('done','unavailable')",allowed).fetchone()[0]
out['special']= [dict(r) for r in c.execute("select e.job_id,e.youtube_id,e.status,v.transcript_status,length(trim(coalesce(v.transcript,''))) n from e2e_jobs e join videos v on v.id=e.video_id where e.job_id in (198,245,253,263) order by e.job_id")]
out['native_unavailable']= [dict(r) for r in c.execute("select e.job_id,e.status,v.transcript_status from e2e_jobs e join videos v on v.id=e.video_id where e.status='supadata_native_unavailable' order by e.job_id")]
out['worker_source_direct_fetch']='YouTubeTranscriptApi' in Path('/opt/struktur/youtube-research/e2e_worker.py').read_text(errors='ignore')
out['worker_source_supadata']='fetch_native' in Path('/opt/struktur/youtube-research/e2e_worker.py').read_text(errors='ignore')
# CE
ce=sqlite3.connect('file:'+CE+'?mode=ro',uri=True); ce.row_factory=sqlite3.Row; rows=ce.execute("select source_video_id,youtube_id,transcript_status,obsidian_artifact_path,length(transcript) transcript_len,transcript_hash,content_hash from ce_sources where source_system='youtube-research' and source_video_id=1246").fetchall(); out['ce274']=[dict(r) for r in rows]; ce.close()
# vault
paths=[]
for p in glob.glob('/opt/obsidian-vault/YouTube-Research/**/*.md',recursive=True):
 try:
  if 'youtube_id: d2MiSnElwrw' in Path(p).read_text(errors='ignore'): paths.append(p)
 except: pass
out['obsidian274']=paths
# account (active key value never printed)
try:
 import sys; sys.path.insert(0,'/opt/struktur/youtube-research'); from supadata_native import account_status
 out['account']=account_status()
except Exception as e: out['account']={'error_class':type(e).__name__}
# service + journal bounded
out['service']=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
out['journal_since_restart']=subprocess.run(['journalctl','-u','youtube-research-e2e-worker.service','--since','2026-08-27 15:21:45','--no-pager'],capture_output=True,text=True).stdout
# dashboard service/routing summary
out['units']=subprocess.run(['systemctl','list-units','--type=service','--all','--no-pager'],capture_output=True,text=True).stdout
out['listeners']=subprocess.run(['ss','-ltnp'],capture_output=True,text=True).stdout
print(json.dumps(out,ensure_ascii=False,default=str))