import sqlite3,json,collections
con=sqlite3.connect('file:/var/lib/sma-data/signals.db?mode=ro',uri=True); con.row_factory=sqlite3.Row
for label,where in [('observed30',"observed_at >= datetime('now','-30 days')"),('published14',"datetime(published_at) >= datetime('now','-14 days')")]:
 rows=con.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()
 by=collections.defaultdict(lambda: [0,0,0])
 for r in rows:
  k=(r['source_type'],r['source_name']); by[k][0]+=1; by[k][1]+=int(bool(r['image_found']) and bool((r['image_url'] or '').strip() or (r['cached_image_path'] or '').strip())); by[k][2]+=int(not (bool(r['image_found']) and bool((r['image_url'] or '').strip() or (r['cached_image_path'] or '').strip())))
 print(label,'total',len(rows),'with_real',sum(x[1] for x in by.values()),'without',sum(x[2] for x in by.values()))
 print(json.dumps({str(k):{'total':v[0],'with_real':v[1],'without':v[2]} for k,v in by.items()},ensure_ascii=False,indent=2))
con.close()