import sqlite3,collections,json
c=sqlite3.connect('file:/var/lib/sma-data/signals.db?mode=ro',uri=True); c.row_factory=sqlite3.Row
rows=c.execute("select source_type,source_name,image_url,image_found from signals where observed_at >= datetime('now','-30 days')").fetchall();
a=collections.defaultdict(lambda:[0,0,0])
for r in rows:
 k=(r['source_type'],r['source_name']); a[k][0]+=1; a[k][1]+=int(bool(r['image_found']) and bool((r['image_url'] or '').strip())); a[k][2]+=int(not(bool(r['image_found']) and bool((r['image_url'] or '').strip())))
agg=collections.defaultdict(lambda:[0,0,0])
for (typ,name),v in a.items(): agg[typ][0]+=v[0]; agg[typ][1]+=v[1]; agg[typ][2]+=v[2]
print(json.dumps({'aggregate':dict(agg),'by_name':{str(k):v for k,v in a.items()}},ensure_ascii=False,indent=2)); c.close()