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 d in [1,7,14,30]:
 rows=c.execute("select source_type,source_platform,source_name,watch_entity_id,count(*) n from signals where observed_at >= datetime('now',?) group by source_type,source_platform,source_name,watch_entity_id order by n desc",('-'+str(d)+' days',)).fetchall()
 print('WINDOW',d,json.dumps([dict(r) for r in rows],ensure_ascii=False))
rows=c.execute("select signal_id,topic,source_name,source_type,source_platform,watch_entity_id,observed_at,published_at,source_url from signals where source_type in ('facebook_public','meta_ad') and observed_at >= datetime('now','-30 days') order by observed_at desc").fetchall()
print('FACEBOOK_30D',json.dumps([dict(r) for r in rows],ensure_ascii=False))
# exact source-platform aggregates using read-time fallback
rows=c.execute("select source_type,source_platform, count(*) n from signals where observed_at >= datetime('now','-30 days') group by source_type,source_platform").fetchall()
print('PLATFORM_RAW',json.dumps([dict(r) for r in rows],ensure_ascii=False))
c.close()