import sqlite3, json, os, glob, hashlib, pathlib, sys, collections
p='/var/lib/sma-data/signals.db'; c=sqlite3.connect('file:'+p+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
print('TOTAL',c.execute('select count(*) from signals').fetchone()[0])
print('TEST_ROWS',[dict(r) for r in c.execute("select signal_id,mas_relevant,content_blocked from signals where signal_id like 'AA050_%'")])
print('COUNTS',[(r[0],r[1]) for r in c.execute('select mas_relevant,count(*) from signals group by mas_relevant')])
print('DATES',dict(c.execute('select min(created_at) mn,max(created_at) mx,min(observed_at) omn,max(observed_at) omx from signals').fetchone()))
for col in ['source_type','signal_category','mas_relevant','content_blocked','publish_status','status']:
 print('DIST_'+col,[(r[0] if r[0] is not None else 'NULL',r[1]) for r in c.execute(f'select {col},count(*) from signals group by {col} order by count(*) desc')])
for t in ['content_drafts','content_queue','content_packages','publications','posting_log']:
 print('MAS_'+t,c.execute('select count(*) from '+t).fetchone()[0])
# Packages in legacy cases DB
q='/opt/struktur/social-media-agent/cases.db'; x=sqlite3.connect('file:'+q+'?mode=ro',uri=True); x.row_factory=sqlite3.Row
for t in ['content_items','publication_packages','publication_events']:
 try: print('CASES_'+t,x.execute('select count(*) from '+t).fetchone()[0])
 except: pass
rows=x.execute('select signal_id,package_status,approval_status from publication_packages').fetchall()
print('CASES_RELEVANCE',[(r[0],r[1],r[2]) for r in rows if r[0] and r[0].startswith('AA050_')])
# count packages whose signal is currently irrelevant
bad=0
for r in rows:
 if not r[0] or r[0].startswith('evergreen-'): continue
 s=c.execute('select mas_relevant,content_blocked from signals where signal_id=?',(r[0],)).fetchone()
 if s and (s[0]!=1 or s[1]!=0): bad+=1
print('CASES_BAD_SIGNAL_PACKAGES',bad)
print('CASES_PUBLISHED_EVENTS',x.execute("select count(*) from publication_events where event_status='published'").fetchone()[0] if x.execute("select name from sqlite_master where type='table' and name='publication_events'").fetchone() else 'n/a')
x.close(); c.close()
# AS file metadata
for ap in ['/var/lib/sma-data/agent-solutions-signals.db','/var/lib/sma-data/agent-solutions-cases.db','/var/lib/sma-data/agent-solutions-posts.db']:
 st=os.stat(ap); print('AS_FILE',ap,st.st_size,st.st_mtime,hashlib.sha256(open(ap,'rb').read()).hexdigest())