import sqlite3, os, re, glob, json, datetime, collections
DB='/opt/struktur/youtube-research/knowledge.db'
VAULT='/opt/obsidian-vault/YouTube-Research'
print('remote_time_utc', datetime.datetime.now(datetime.timezone.utc).isoformat())
print('db_path', DB)
print('db_size', os.path.getsize(DB), 'db_mtime_utc', datetime.datetime.fromtimestamp(os.path.getmtime(DB), datetime.timezone.utc).isoformat())
con=sqlite3.connect('file:'+DB+'?mode=ro',uri=True)
con.row_factory=sqlite3.Row
q=lambda sql: [dict(r) for r in con.execute(sql)]
print('videos_total', con.execute('select count(*) from videos').fetchone()[0])
print('videos_distinct_youtube_id', con.execute('select count(distinct youtube_id) from videos where youtube_id is not null and youtube_id != ""').fetchone()[0])
print('videos_transcript_status', json.dumps(q('select coalesce(transcript_status,"<NULL>") status, count(*) count, count(distinct youtube_id) distinct_youtube_ids from videos group by coalesce(transcript_status,"<NULL>") order by status'), ensure_ascii=False))
print('registry_rows', con.execute('select count(*) from source_processing_registry').fetchone()[0])
print('registry_distinct_source_external_id', con.execute('select count(distinct source_external_id) from source_processing_registry where source_external_id is not null and source_external_id != ""').fetchone()[0])
print('registry_status_combo', json.dumps(q('select coalesce(artifact_status,"<NULL>") artifact_status, coalesce(quality_status,"<NULL>") quality_status, coalesce(graphiti_status,"<NULL>") graphiti_status, count(*) count, count(distinct source_external_id) distinct_external_ids from source_processing_registry group by 1,2,3 order by count desc, 1,2,3'), ensure_ascii=False))
print('registry_quality_passed_graphiti_imported_rows', con.execute("select count(*) from source_processing_registry where quality_status='passed' and graphiti_status='imported'").fetchone()[0])
print('registry_quality_passed_graphiti_imported_distinct_external_ids', con.execute("select count(distinct source_external_id) from source_processing_registry where quality_status='passed' and graphiti_status='imported' and source_external_id is not null and source_external_id!=''").fetchone()[0])
print('queue_rows', con.execute('select count(*) from graphiti_import_queue').fetchone()[0])
print('queue_distinct_video_id', con.execute('select count(distinct video_id) from graphiti_import_queue where video_id is not null and video_id != ""').fetchone()[0])
print('queue_status', json.dumps(q('select coalesce(graphiti_status,"<NULL>") graphiti_status, count(*) count, count(distinct video_id) distinct_video_ids from graphiti_import_queue group by 1 order by count desc'), ensure_ascii=False))
print('queue_duplicates', json.dumps(q('select video_id, count(*) count from graphiti_import_queue where video_id is not null and video_id != "" group by video_id having count(*)>1 order by count desc, video_id limit 20'), ensure_ascii=False))
con.close()
mds=glob.glob(VAULT+'/**/*.md', recursive=True)
ids=[]
missing=[]
for p in mds:
    m=re.search(r'(?:^|/)ID-(\d+)-', p)
    if m: ids.append(m.group(1))
    else: missing.append(p)
print('obsidian_vault_path', VAULT)
print('obsidian_md_files', len(mds))
print('obsidian_unique_numeric_ids_from_filename', len(set(ids)))
print('obsidian_duplicate_numeric_ids', len(ids)-len(set(ids)))
print('obsidian_md_without_id_filename', len(missing))