import json
from pathlib import Path
import subprocess

root=Path(__file__).parent
baseline=json.loads((root/'baseline-lifecycle.json').read_text())
candidate=json.loads((root/'candidate.json').read_text())
windows={'baseline':(baseline['startedAt'],baseline['samples'][-1]['sampledAt']),
         'candidate':(candidate['start'],candidate['samples'][-1]['wall'])}
code='''import os,json,time,psycopg2
from pathlib import Path
from psycopg2.extras import RealDictCursor
from app.event_artifact_contract import ArtifactConfig
windows=WINDOWS
out={'sampledAt':time.time(),'windows':windows}
with psycopg2.connect(os.environ['DATABASE_URL'],connect_timeout=3,options='-c statement_timeout=3000 -c default_transaction_read_only=on') as c:
 with c.cursor(cursor_factory=RealDictCursor) as q:
  for name,(begin,end) in windows.items():
   q.execute("""SELECT payload->>'cameraId' camera,payload->>'streamId' stream,
     artifact_state,delivery_state,error,delivery_error,http_status,count(*) count,
     count(*) FILTER (WHERE media->>'clip'='true' AND media->>'thumbnail'='true') complete_media,
     count(*) FILTER (WHERE body->'events'->0->'detail'->'eventTime'->>'source'='AI_WALL_FALLBACK') fallback
     FROM contextra_event_artifacts WHERE confirmed_at >= %s AND confirmed_at <= %s
     GROUP BY 1,2,3,4,5,6,7 ORDER BY 1,3,4""",(begin,end))
   out[name]=[dict(r) for r in q.fetchall()]
  q.execute("""SELECT payload->>'cameraId' camera,relative_path FROM contextra_event_artifacts
      WHERE confirmed_at >= %s AND confirmed_at <= %s AND artifact_state='READY'
      ORDER BY confirmed_at DESC LIMIT 6""",windows['candidate'])
  config=ArtifactConfig.from_environment()
  out['fileSamples']=[]
  for r in q.fetchall():
   folder=config.root/r['relative_path']
   sizes={name:(folder/name).stat().st_size for name in ('clip.mp4','thumbnail.jpg') if (folder/name).is_file()}
   out['fileSamples'].append(dict(camera=r['camera'],sizes=sizes))
print(json.dumps(out))
'''.replace('WINDOWS',repr(windows))
cmd=['ssh','-T','-p','1022','-i','/root/.ssh/ltb1021_contextra_icerti2_si_ed25519','-o','BatchMode=yes','-o','StrictHostKeyChecking=yes','-o','UserKnownHostsFile=/root/.ssh/contextra_icerti2_known_hosts','si@58.233.207.31','docker exec -i contextra-ai2-analytics-1 python3 -B -']
p=subprocess.run(cmd,input=code,text=True,capture_output=True,timeout=20)
if p.returncode:raise SystemExit(p.stderr)
d=json.loads(p.stdout);(root/'cohorts-final.json').write_text(json.dumps(d,indent=2))
print(json.dumps(d,indent=2))