import sqlite3,json,collections
p='/var/lib/sma-data/signals.db'
c=sqlite3.connect('file:'+p+'?mode=ro',uri=True); c.row_factory=sqlite3.Row
for label,where in [('observed30',"observed_at >= datetime('now','-30 days')"),('published14',"datetime(published_at) >= datetime('now','-14 days')")]:
    rows=c.execute('select signal_id,topic,source_type,source_name,source_url,image_url,image_source,image_found,cached_image_path,final_article_url,image_checked_at from signals where '+where).fetchall()
    print('###',label,'TOTAL',len(rows))
    by=collections.defaultdict(lambda:collections.Counter())
    for r in rows:
        real=bool((r['image_url'] or '').strip()) and bool(r['image_found'])
        key=(r['source_type'],r['source_name'])
        by[key]['total']+=1; by[key]['real']+=int(real); by[key]['no']+=int(not real)
        by[key]['url_any']+=int(bool((r['image_url'] or '').strip())); by[key]['cached']+=int(bool((r['cached_image_path'] or '').strip()))
    print(json.dumps({str(k):dict(v) for k,v in by.items()},ensure_ascii=False,indent=2))
    print('SAMPLES',json.dumps([dict(r) for r in rows[:30]],ensure_ascii=False,indent=2))
c.close()