#!/usr/bin/env python3
"""
KPI History Snapshot Daily - v2 with correct VM keys
"""
import psycopg2, urllib.request, json, sys
from datetime import date, datetime

try:
    resp = urllib.request.urlopen("http://127.0.0.1/api/visual-management-live.php", timeout=30)
    vm = json.loads(resp.read())
    
    biz = vm.get('business', {})
    flx = vm.get('flux', {})
    eth = vm.get('ethica', {}) if isinstance(vm.get('ethica'), dict) else {}
    off = vm.get('office', {}) if isinstance(vm.get('office'), dict) else {}
    qty = vm.get('quality', {})
    
    metrics = {
        'crm_deals': biz.get('crm_deals', 0),
        'crm_deals_amount_eur': biz.get('crm_deals_amount_eur', 0),
        'crm_companies': biz.get('crm_companies', 0),
        'crm_contacts': biz.get('crm_contacts', 0),
        'crm_contacts_b2b': biz.get('crm_contacts_b2b', 0),
        'crm_activities': biz.get('crm_activities', 0),
        'ethica_hcps': eth.get('total', biz.get('ethica_hcps', 0)),
        'office_active': off.get('actifs', off.get('active', 0)),
        'office_warming': off.get('warming', 0),
        'send_contacts_new_7d': flx.get('send_contacts_last_7d', 0),
        'graph_send_new_7d': flx.get('graph_send_last_7d', 0),
        'weval_leads_new_7d': flx.get('leads_last_7d', 0),
        'nonreg_score': qty.get('nonreg_score', 100),
        'l99_score': qty.get('l99_score', 100),
        'health_score': vm.get('health_score', 95),
        'andons_count': vm.get('andons_count', len(vm.get('andons', []))),
    }
    
    conn = psycopg2.connect("host=10.1.0.3 port=5432 dbname=adx_system user=admin password=admin123")
    cur = conn.cursor()
    today = date.today()
    cols = ['snap_date'] + list(metrics.keys()) + ['captured_at']
    ph = ','.join(['%s'] * len(cols))
    vals = [today] + list(metrics.values()) + [datetime.now()]
    update_set = ','.join([f"{k}=EXCLUDED.{k}" for k in metrics.keys()] + ["captured_at=EXCLUDED.captured_at"])
    cur.execute(f"INSERT INTO admin.kpi_history_daily ({','.join(cols)}) VALUES ({ph}) ON CONFLICT (snap_date) DO UPDATE SET {update_set}", vals)
    conn.commit()
    cur.execute("SELECT COUNT(*) FROM admin.kpi_history_daily"); total=cur.fetchone()[0]
    print(f"[OK] Snapshot {today} · rows={total} · deals={metrics['crm_deals']} companies={metrics['crm_companies']} hcps={metrics['ethica_hcps']} health={metrics['health_score']}")
    cur.close(); conn.close()
except Exception as e:
    print(f"[ERR] {e}", file=sys.stderr); sys.exit(1)