import sqlite3,re,json,collections,datetime
p='/var/lib/sma-data/signals.db'
con=sqlite3.connect('file:'+p+'?mode=ro',uri=True)
con.row_factory=sqlite3.Row
now=datetime.datetime.now(datetime.timezone.utc)
cut=(now-datetime.timedelta(days=30)).isoformat()
rows=con.execute('select signal_id,source_type,source_platform,source_name,observed_at,published_at,source_url from signals where observed_at >= ? order by observed_at desc',(cut,)).fetchall()
counts=collections.Counter(); groups=collections.Counter(); invalid=[]
for row in rows:
    value=row['published_at']
    key='NULL' if value is None else 'ISO' if re.match(r'^\d{4}-\d{2}-\d{2}T',value) else 'INVALID'
    counts[key]+=1; groups[value if value is not None else 'NULL']+=1
    if key=='INVALID': invalid.append(dict(row))
print(json.dumps({'total':len(rows),'counts':dict(counts),'groups':dict(groups),'invalid':invalid[:20]},ensure_ascii=False))
con.close()