import json
from pathlib import Path
from datetime import datetime, timezone
from zoneinfo import ZoneInfo
import sqlite3, requests

DB='/var/lib/sma-data/signals.db'
now=datetime.now(timezone.utc); tz=ZoneInfo('Europe/Madrid')
local=now.astimezone(tz)
conn=sqlite3.connect(DB)
rows=[]
for d in [1,2,3,5,7,14]:
    start=datetime.combine(local.date(),datetime.min.time(),tzinfo=tz)
    from datetime import timedelta
    start -= timedelta(days=d-1)
    cut=start.astimezone(timezone.utc).isoformat()
    q="""select count(*) from signals where radar_score>=31
      and (mallorca_relevance>0 or director_action in ('pruefen','veroeffentlichen'))
      and ((lower(source_type)='meta_ad' and lower(coalesce(active_status,''))='active' and datetime(last_seen_at)>=datetime(?))
       or (lower(source_type)<>'meta_ad' and datetime(published_at) is not null and datetime(published_at)>=datetime(?)))
      and status not in ('ignoriert','veroeffentlicht') and coalesce(competition_signal,0)=0"""
    db=conn.execute(q,(cut,cut)).fetchone()[0]
    api=[]
    for sort in ['relevance','score','op_priority','newest','facebook','linkedin']:
        r=requests.get(f'https://sma.agentsolutions-mallorca.com/api/signals?days={d}&sort={sort}&limit=100',timeout=30)
        r.raise_for_status(); api.append((sort,len(r.json())))
    rows.append({'days':d,'cutoff_utc':cut,'db_raw':db,'api':dict(api)})
conn.close()
Path('/home/hermes/Antworten/AA-023-time-measurements.json').write_text(json.dumps({'now_utc':now.isoformat(),'timezone':'Europe/Madrid','rows':rows},ensure_ascii=False,indent=2))
print(json.dumps(rows,ensure_ascii=False))