import sqlite3, sys, json
from datetime import datetime, timezone, timedelta
sys.path.insert(0, '/opt/struktur/social-media-radar')
from published_at_normalizer import normalize_published_at

path = '/var/lib/sma-data/signals.db'
now = datetime.now(timezone.utc)
cutoff = (now - timedelta(days=30)).isoformat()
con = sqlite3.connect(path)
con.row_factory = sqlite3.Row
rows = con.execute('SELECT signal_id, observed_at, published_at FROM signals WHERE observed_at >= ?', (cutoff,)).fetchall()
changed = normalized = nulled = errors = 0
for row in rows:
    old = row['published_at']
    new = normalize_published_at(old, row['observed_at'])
    if old != new:
        con.execute('UPDATE signals SET published_at=? WHERE signal_id=?', (new, row['signal_id']))
        changed += 1
    if new is None:
        nulled += 1
    else:
        normalized += 1
con.commit()
# Validate every changed-scope value is ISO parseable or NULL.
invalid = []
for row in con.execute('SELECT signal_id,published_at FROM signals WHERE observed_at >= ?', (cutoff,)):
    v = row['published_at']
    if v is not None:
        try:
            dt = datetime.fromisoformat(v.replace('Z', '+00:00'))
            if dt.tzinfo is None:
                invalid.append((row['signal_id'], v))
        except ValueError:
            invalid.append((row['signal_id'], v))
con.close()
print(json.dumps({'now': now.isoformat(), 'cutoff': cutoff, 'checked': len(rows), 'changed': changed, 'normalized_iso': normalized, 'set_null': nulled, 'invalid': invalid}, ensure_ascii=False))
if invalid:
    raise SystemExit(2)