// admin-ka — Analytics first-party v2 : trafic HUMAIN crédible par site KA.
//
// Pipeline : beacon (v1 legacy 60 s, ou v2 ka-a.js pv/heartbeat) → /collect →
// classification à l'insertion (humain / crawler / datacenter / automation /
// interne) via User-Agent + IP-intelligence (ip-api, cache SQLite, hosting/
// proxy/ASN) → événements typés (page_view / heartbeat) + sessions → agrégats
// quotidiens par classe. Les métriques principales n'affichent QUE les humains.
//
// Définitions (documentées dans CLAUDE.md) :
//  - visiteur humain : vid dont les événements sont classés `human`
//  - visiteur unique : vid humain distinct dans la fenêtre
//  - session : suite d'événements d'un même vid sans trou > sessionMin (30 min)
//  - page vue : événement `pv` (navigation réelle, SPA incluse) — jamais un heartbeat
//  - en ligne / actif : humain dont le dernier événement < activeSec (120 s) ;
//    expire tout seul, un onglet fermé disparaît en ~2 min
import fs from 'node:fs';
import path from 'node:path';
import crypto from 'node:crypto';
import { fileURLToPath } from 'node:url';
import { DatabaseSync } from 'node:sqlite';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const DATA_DIR = path.join(__dirname, '..', 'data');
const DB_PATH = path.join(DATA_DIR, 'analytics.sqlite');
const CONFIG_PATH = path.join(DATA_DIR, 'config.json');

let db = null;

// GIF transparent 1x1 renvoyé au beacon
export const PIXEL = Buffer.from('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7', 'base64');

// ---------- configuration (surchageable via config.json → "analytics") ----------
export const DEFAULTS = {
  sessionMin: 30,        // minutes d'inactivité avant nouvelle session
  activeSec: 120,        // fenêtre « en ligne » (dernier événement < N s)
  retentionDays: 45,     // rétention des événements bruts
  maxIpPerHour: 1200,    // au-delà : trafic reclassé automation (flood)
  internalIps: [],       // IPs/préfixes internes additionnels
  internalUAs: [],       // fragments UA internes additionnels
  extraBotUAs: [],       // fragments UA bots additionnels
  blockedASNs: [],       // ASNs (numéros) forcés en datacenter
  debug: false,
};
let cfg = { ...DEFAULTS };
function loadCfg() {
  try {
    const j = JSON.parse(fs.readFileSync(CONFIG_PATH, 'utf8'));
    cfg = { ...DEFAULTS, ...(j.analytics || {}) };
  } catch { cfg = { ...DEFAULTS }; }
}
export function analyticsConfig() { return { ...cfg }; }
export function setAnalyticsConfig(patch) {
  const j = JSON.parse(fs.readFileSync(CONFIG_PATH, 'utf8'));
  j.analytics = { ...(j.analytics || {}) };
  for (const k of Object.keys(DEFAULTS)) {
    if (!(k in patch)) continue;
    if (Array.isArray(DEFAULTS[k])) j.analytics[k] = Array.isArray(patch[k]) ? patch[k].map(String).filter(Boolean).slice(0, 200) : [];
    else if (typeof DEFAULTS[k] === 'number') { const n = Number(patch[k]); if (Number.isFinite(n) && n > 0) j.analytics[k] = n; }
    else if (typeof DEFAULTS[k] === 'boolean') j.analytics[k] = !!patch[k];
  }
  fs.writeFileSync(CONFIG_PATH, JSON.stringify(j, null, 2));
  loadCfg();
  return analyticsConfig();
}

// ---------- classification ----------
// Crawlers connus (moteurs, IA, SEO, réseaux sociaux, uptime)
const BOT_RE = /googlebot|bingbot|slurp|duckduckbot|baiduspider|yandex(bot)?|applebot|gptbot|oai-searchbot|chatgpt-user|claudebot|claude-web|anthropic-ai|perplexitybot|youbot|cohere-ai|ccbot|bytespider|petalbot|amazonbot|ahrefsbot|semrushbot|mj12bot|dotbot|rogerbot|screaming frog|dataforseo|serpstatbot|blexbot|facebookexternalhit|meta-externalagent|facebookcatalog|twitterbot|linkedinbot|pinterestbot|slackbot|discordbot|telegrambot|whatsapp|skypeuripreview|redditbot|uptimerobot|uptime-kuma|pingdom|statuscake|site24x7|betteruptime|checkly|freshping|hetrixtools|gtmetrix|lighthouse|chrome-lighthouse|pagespeed|ptst|crawler|spider|scrapy|feedfetcher|bitlybot|embedly|quora link preview|ia_archiver|archive\.org_bot|wayback|semanticscholar|turnitin|dataminr/i;
// Outils / scripts / navigateurs headless (pas un navigateur humain)
const TOOL_RE = /^$|curl|wget|python-requests|python-urllib|python-httpx|aiohttp|httpx\/|go-http-client|node-fetch|undici|axios\/|libwww|okhttp|java\/|apache-httpclient|guzzlehttp|ruby|perl|php\/|headlesschrome|phantomjs|puppeteer|playwright|selenium|splash|cypress|jsdom|wkhtmltopdf|nutch|masscan|zgrab|nmap|dirbuster|nikto|sqlmap|censys|shodan|paloaltonetworks|expanse|internet-measurement|probe|monitor(ing)?\b|synthetic|datadog|newrelic|grafana/i;
// Hébergeurs / clouds — filet quand ip-api n'a pas le drapeau hosting
const DC_ORG_RE = /amazon|aws |a100 row|google (cloud|llc)|gcp|microsoft|azure|digitalocean|hetzner|ovh|linode|akamai|vultr|oracle|cloudflare|fastly|fly\.io|render|railway|vercel|netlify|scaleway|leaseweb|tencent|alibaba|aliyun|huawei cloud|choopa|contabo|m247|datacamp|packethub|gcore|stackpath|upcloud|kamatera|ionos|godaddy|hostinger|namecheap|dreamhost|liquid web|colocrossing|psychz|quadranet|serverius|worldstream|datapacket|cdn77|edgecast|limelight|zenlayer|host(ing|ed|s)\b|data ?cent(er|re)|serveur|dedibox|kimsufi|so-?you-?start/i;
// UA interne Groupe KA (monitoring, gardiens, scripts maison)
const INTERNAL_UA_RE = /admin-ka|ka-guardian|maclustr|adminka/i;
// IP locales / cluster / tailscale
const INTERNAL_IP_RE = /^(127\.|10\.|192\.168\.|172\.(1[6-9]|2\d|3[01])\.|100\.(6[4-9]|[7-9]\d|1[01]\d|12[0-7])\.|::1$|fe80:|fc|fd|::ffff:127\.|::ffff:10\.|::ffff:192\.168\.)/;
const BROWSER_RE = /mozilla\/5\.0/i;

export const CLASSES = ['human', 'crawler', 'datacenter', 'automation', 'internal', 'unknown'];

function classify(ua, ip, ipRow, internalHeader) {
  const reasons = [];
  ua = String(ua || '');
  // 1) interne (header dédié, IP cluster/LAN, UA maison, listes config)
  if (internalHeader) return { cls: 'internal', reasons: ['en-tête X-KA-Internal'] };
  if (INTERNAL_IP_RE.test(ip)) return { cls: 'internal', reasons: ['IP locale/cluster (' + ip + ')'] };
  if (cfg.internalIps.some(p => p && ip.startsWith(p))) return { cls: 'internal', reasons: ['IP interne (réglages)'] };
  if (INTERNAL_UA_RE.test(ua) || cfg.internalUAs.some(p => p && ua.toLowerCase().includes(p.toLowerCase())))
    return { cls: 'internal', reasons: ['User-Agent interne'] };
  // 2) crawlers connus
  if (BOT_RE.test(ua) || cfg.extraBotUAs.some(p => p && ua.toLowerCase().includes(p.toLowerCase()))) {
    const m = ua.match(BOT_RE);
    return { cls: 'crawler', reasons: ['User-Agent crawler connu' + (m ? ' (' + m[0] + ')' : '')] };
  }
  // 3) outils / headless / pas un navigateur
  if (TOOL_RE.test(ua)) return { cls: 'automation', reasons: ['User-Agent outil/headless : ' + (ua.slice(0, 60) || '(vide)')] };
  if (!BROWSER_RE.test(ua)) return { cls: 'automation', reasons: ['User-Agent non-navigateur : ' + ua.slice(0, 60)] };
  // 4) réseau datacenter / hosting / proxy / ASN bloqué
  if (ipRow) {
    if (cfg.blockedASNs.includes(ipRow.asn)) { reasons.push('ASN ' + ipRow.asn + ' exclu (réglages)'); return { cls: 'datacenter', reasons }; }
    if (ipRow.hosting) { reasons.push('réseau hosting/datacenter (' + (ipRow.org || ipRow.isp || 'AS' + ipRow.asn) + ')'); return { cls: 'datacenter', reasons }; }
    if (ipRow.proxy) { reasons.push('proxy/VPN détecté (' + (ipRow.org || ipRow.isp || '') + ')'); return { cls: 'datacenter', reasons }; }
    if (DC_ORG_RE.test((ipRow.org || '') + ' ' + (ipRow.isp || ''))) { reasons.push('organisation hébergeur : ' + (ipRow.org || ipRow.isp)); return { cls: 'datacenter', reasons }; }
    reasons.push('réseau résidentiel/mobile (' + (ipRow.isp || ipRow.org || 'AS' + ipRow.asn) + ')');
  } else {
    reasons.push('IP pas encore classifiée (reclassement auto dès résolution)');
  }
  reasons.push('navigateur + JS exécuté');
  return { cls: 'human', reasons };
}

// ---------- init ----------
export function initAnalytics() {
  fs.mkdirSync(DATA_DIR, { recursive: true });
  loadCfg();
  db = new DatabaseSync(DB_PATH);
  db.exec(`
    PRAGMA journal_mode = WAL;
    -- v2 : événements typés + classe de trafic
    CREATE TABLE IF NOT EXISTS events (
      site TEXT, vid TEXT, sid TEXT, ts INTEGER, ev TEXT, path TEXT, ref TEXT,
      ip TEXT, cls TEXT, dev TEXT, v INTEGER
    );
    CREATE INDEX IF NOT EXISTS idx_ev_site_ts ON events(site, ts);
    CREATE INDEX IF NOT EXISTS idx_ev_ts ON events(ts);
    CREATE INDEX IF NOT EXISTS idx_ev_ip ON events(ip);
    CREATE INDEX IF NOT EXISTS idx_ev_sid ON events(sid);
    CREATE INDEX IF NOT EXISTS idx_ev_vid ON events(vid);
    -- IP intelligence (cache — un lookup par IP, jamais par hit)
    CREATE TABLE IF NOT EXISTS ipinfo (
      ip TEXT PRIMARY KEY, asn INTEGER, org TEXT, isp TEXT,
      cc TEXT, country TEXT, region TEXT, city TEXT,
      hosting INTEGER, proxy INTEGER, mobile INTEGER, ts INTEGER
    );
    -- agrégats quotidiens v2 (long terme, par classe)
    CREATE TABLE IF NOT EXISTS daily2 (
      site TEXT, day TEXT, humans INTEGER, sessions INTEGER, pv INTEGER,
      crawler INTEGER, datacenter INTEGER, automation INTEGER, internal INTEGER,
      total_vids INTEGER, PRIMARY KEY(site, day)
    );
    CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT);
    -- héritées de v1 (historique conservé, plus alimentées) : hits, daily, geo
    CREATE TABLE IF NOT EXISTS hits (site TEXT, vid TEXT, ts INTEGER, path TEXT);
    CREATE TABLE IF NOT EXISTS daily (site TEXT, day TEXT, uniques INTEGER, pv INTEGER, PRIMARY KEY(site, day));
  `);
  if (!db.prepare('SELECT value FROM meta WHERE key=?').get('migrated_at')) {
    db.prepare('INSERT INTO meta (key,value) VALUES (?,?)').run('migrated_at', String(Date.now()));
  }
  rollup();
  setInterval(rollup, 10 * 60 * 1000);
  setInterval(purge, 6 * 60 * 60 * 1000);
  setInterval(resolveIps, 30 * 1000);
  setInterval(() => { ipHourly.clear(); }, 60 * 60 * 1000); // fenêtre anti-flood glissante grossière
  purge(); resolveIps();
}

export function migratedAt() {
  const r = db.prepare('SELECT value FROM meta WHERE key=?').get('migrated_at');
  return r ? Number(r.value) : null;
}

// ---------- ingestion ----------
// état de session en mémoire pour le beacon v1 (pas de sid côté client)
const sessState = new Map();   // site|vid -> { sid, lastTs, lastPath }
const ipHourly = new Map();    // ip -> compteur d'événements (fenêtre ~1 h)
let insEv = null;

function getIpRow(ip) {
  try { return db.prepare('SELECT * FROM ipinfo WHERE ip=?').get(ip) || null; } catch { return null; }
}

export function recordHit(q, req) {
  if (!db) return;
  const site = String(q.get('site') || '').slice(0, 40);
  const vid = String(q.get('vid') || '').slice(0, 64);
  if (!site || !vid) return;
  const now = Date.now();
  const pathP = String(q.get('p') || '/').slice(0, 200);
  const ref = String(q.get('r') || '').slice(0, 200);
  const dev = ['m', 't', 'd'].includes(q.get('d')) ? q.get('d') : null;
  const v = q.get('sid') ? 2 : 1;
  const ua = String(req.headers['user-agent'] || '');
  const xf = req.headers['x-forwarded-for'];
  const ip = ((xf ? String(xf).split(',')[0] : req.socket.remoteAddress) || '').trim().slice(0, 45);
  const internalHeader = req.headers['x-ka-internal'] === '1';

  // classe de trafic (IP-intelligence si déjà en cache, sinon provisoire)
  let { cls } = classify(ua, ip, getIpRow(ip), internalHeader);
  // anti-flood : une IP qui dépasse maxIpPerHour passe en automation
  const cnt = (ipHourly.get(ip) || 0) + 1;
  ipHourly.set(ip, cnt);
  if (cls === 'human' && cnt > cfg.maxIpPerHour) cls = 'automation';

  // session + type d'événement
  let sid, ev;
  if (v === 2) {
    sid = String(q.get('sid')).slice(0, 64);
    ev = q.get('ev') === 'hb' ? 'hb' : 'pv';
  } else {
    // beacon v1 : ping au chargement + toutes les 60 s → on sessionne et on
    // dé-duplique côté serveur (un ping d'intervalle N'EST PAS une page vue)
    const key = site + '|' + vid;
    const st = sessState.get(key);
    if (!st || now - st.lastTs > cfg.sessionMin * 60_000) {
      sid = vid + '.' + now.toString(36);
      ev = 'pv';
    } else {
      sid = st.sid;
      // nouvelle page = pv ; re-ping rapide (<50 s) même page = reload = pv ;
      // ping d'intervalle (~60 s+) même page = heartbeat
      ev = (pathP !== st.lastPath || now - st.lastTs < 50_000) ? 'pv' : 'hb';
    }
    sessState.set(key, { sid, lastTs: now, lastPath: pathP });
    if (sessState.size > 50_000) sessState.clear();
  }

  try {
    if (!insEv) insEv = db.prepare('INSERT INTO events (site,vid,sid,ts,ev,path,ref,ip,cls,dev,v) VALUES (?,?,?,?,?,?,?,?,?,?,?)');
    insEv.run(site, vid, sid, now, ev, pathP, ref || null, ip, cls, dev, v);
  } catch {}
}

// ---------- IP intelligence (ip-api.com, gratuit ≤45 req/min, cache) ----------
let ipBusy = false;
async function resolveIps() {
  if (!db || ipBusy) return;
  ipBusy = true;
  try {
    const rows = db.prepare(`
      SELECT DISTINCT e.ip FROM events e
      WHERE e.ip <> '' AND e.ip NOT IN (SELECT ip FROM ipinfo)
      ORDER BY e.ts DESC LIMIT 18`).all();
    for (const r of rows) {
      const ip = r.ip;
      if (INTERNAL_IP_RE.test(ip)) {
        db.prepare('INSERT OR REPLACE INTO ipinfo (ip,asn,org,isp,cc,country,region,city,hosting,proxy,mobile,ts) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')
          .run(ip, 0, 'Réseau local', 'Local', 'XX', 'Local', '', '', 0, 0, 0, Date.now());
        continue;
      }
      try {
        const res = await fetch('http://ip-api.com/json/' + encodeURIComponent(ip)
          + '?fields=status,country,countryCode,regionName,city,isp,org,as,hosting,proxy,mobile',
          { signal: AbortSignal.timeout(8000) });
        const d = await res.json();
        if (d && d.status === 'success') {
          const asn = Number((String(d.as || '').match(/^AS(\d+)/) || [])[1] || 0);
          db.prepare('INSERT OR REPLACE INTO ipinfo (ip,asn,org,isp,cc,country,region,city,hosting,proxy,mobile,ts) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')
            .run(ip, asn, d.org || '', d.isp || '', d.countryCode || '', d.country || '', d.regionName || '', d.city || '',
              d.hosting ? 1 : 0, d.proxy ? 1 : 0, d.mobile ? 1 : 0, Date.now());
          reclassifyIp(ip);
        } else {
          db.prepare('INSERT OR REPLACE INTO ipinfo (ip,asn,org,isp,cc,country,region,city,hosting,proxy,mobile,ts) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')
            .run(ip, 0, '', '', '', '', '', '', 0, 0, 0, Date.now());
        }
      } catch { break; } // réseau indisponible — on réessaiera au prochain cycle
      await new Promise(r2 => setTimeout(r2, 1500)); // respecter la limite ip-api
    }
  } catch {}
  ipBusy = false;
}

// une IP vient d'être résolue : reclasse rétroactivement ses événements
// provisoirement « human » si le réseau est un datacenter/proxy/ASN exclu
function reclassifyIp(ip) {
  const row = getIpRow(ip);
  if (!row) return;
  const isDc = row.hosting || row.proxy || cfg.blockedASNs.includes(row.asn) || DC_ORG_RE.test((row.org || '') + ' ' + (row.isp || ''));
  if (isDc) {
    try { db.prepare(`UPDATE events SET cls='datacenter' WHERE ip=? AND cls='human'`).run(ip); } catch {}
  }
}

// ---------- entretien ----------
function purge() {
  if (!db) return;
  const cutoff = Date.now() - cfg.retentionDays * 864e5;
  try {
    db.prepare('DELETE FROM events WHERE ts < ?').run(cutoff);
    db.prepare('DELETE FROM hits WHERE ts < ?').run(cutoff);
    db.prepare('DELETE FROM ipinfo WHERE ts < ?').run(Date.now() - 90 * 864e5); // re-résolution périodique
  } catch {}
}

function dayKey(ts) {
  const d = new Date(ts);
  return d.getFullYear() + '-' + String(d.getMonth() + 1).padStart(2, '0') + '-' + String(d.getDate()).padStart(2, '0');
}
function dayStart(offsetDays = 0) {
  const d = new Date(); d.setHours(0, 0, 0, 0); d.setDate(d.getDate() - offsetDays);
  return d.getTime();
}

// fige les agrégats quotidiens par classe (3 derniers jours recalculés)
function rollup() {
  if (!db) return;
  try {
    const since = dayStart(2);
    const rows = db.prepare('SELECT site, ts, vid, sid, ev, cls FROM events WHERE ts >= ?').all(since);
    const agg = {}; // site|day -> accumulateurs
    for (const r of rows) {
      const k = r.site + '|' + dayKey(r.ts);
      let a = agg[k];
      if (!a) a = agg[k] = { hv: new Set(), hs: new Set(), pv: 0, crawler: 0, datacenter: 0, automation: 0, internal: 0, av: new Set() };
      a.av.add(r.vid);
      if (r.cls === 'human') {
        a.hv.add(r.vid); a.hs.add(r.sid);
        if (r.ev === 'pv') a.pv++;
      } else if (a[r.cls] !== undefined) a[r.cls]++;
    }
    const up = db.prepare('INSERT OR REPLACE INTO daily2 (site,day,humans,sessions,pv,crawler,datacenter,automation,internal,total_vids) VALUES (?,?,?,?,?,?,?,?,?,?)');
    for (const k in agg) {
      const [site, day] = k.split('|');
      const a = agg[k];
      up.run(site, day, a.hv.size, a.hs.size, a.pv, a.crawler, a.datacenter, a.automation, a.internal, a.av.size);
    }
  } catch {}
}

// ---------- requêtes ----------
const H = `cls='human'`;

function q1(sql, ...args) { try { return db.prepare(sql).get(...args); } catch { return null; } }
function qa(sql, ...args) { try { return db.prepare(sql).all(...args); } catch { return []; } }

function humanUniques(site, a, b) {
  return q1(`SELECT COUNT(DISTINCT vid) c FROM events WHERE site=? AND ${H} AND ts>=? AND ts<?`, site, a, b)?.c || 0;
}

// résumé global + par site (métriques HUMAINES + répartition des classes)
export function analyticsSummary(sites) {
  const now = Date.now();
  const t0 = dayStart(0);
  const y0 = dayStart(1);
  const activeCut = now - cfg.activeSec * 1000;
  const out = [];
  const g = { online: 0, today: 0, yesterdaySame: 0, sessions: 0, pv: 0, d7: 0, d30: 0, crawler: 0, datacenter: 0, automation: 0, internal: 0, total_vids: 0 };
  for (const s of sites) {
    const site = s.app || s;
    const t = q1(`SELECT COUNT(DISTINCT vid) uv, COUNT(DISTINCT sid) ss, SUM(CASE WHEN ev='pv' THEN 1 ELSE 0 END) pv, COUNT(*) hits
                  FROM events WHERE site=? AND ${H} AND ts>=?`, site, t0) || {};
    const other = q1(`SELECT
        SUM(CASE WHEN cls='crawler' THEN 1 ELSE 0 END) crawler,
        SUM(CASE WHEN cls='datacenter' THEN 1 ELSE 0 END) datacenter,
        SUM(CASE WHEN cls IN ('automation','unknown') THEN 1 ELSE 0 END) automation,
        SUM(CASE WHEN cls='internal' THEN 1 ELSE 0 END) internal,
        COUNT(DISTINCT vid) total_vids
      FROM events WHERE site=? AND ts>=?`, site, t0) || {};
    const row = {
      site, label: s.label || site, domain: s.domain || '',
      online: q1(`SELECT COUNT(DISTINCT vid) c FROM events WHERE site=? AND ${H} AND ts>=?`, site, activeCut)?.c || 0,
      today: t.uv || 0,
      yesterdaySame: humanUniques(site, y0, y0 + (now - t0)),  // hier à la même heure (delta honnête)
      sessions: t.ss || 0,
      pv: t.pv || 0,
      humanHits: t.hits || 0,
      d7: humanUniques(site, now - 7 * 864e5, now + 1),
      d30: humanUniques(site, now - 30 * 864e5, now + 1),
      crawler: other.crawler || 0, datacenter: other.datacenter || 0,
      automation: other.automation || 0, internal: other.internal || 0,
      total_vids: other.total_vids || 0,
    };
    out.push(row);
    for (const k of ['online', 'today', 'yesterdaySame', 'sessions', 'pv', 'humanHits', 'd7', 'd30', 'crawler', 'datacenter', 'automation', 'internal', 'total_vids']) g[k] = (g[k] || 0) + row[k];
  }
  out.sort((a, b) => b.online - a.online || b.today - a.today);
  return { sites: out, global: g, updated: now, activeSec: cfg.activeSec, migratedAt: migratedAt() };
}

// série quotidienne 30 j (v2 par classe + héritage v1 avant migration)
export function analyticsSeries(site, days = 30) {
  const out = [];
  const mig = migratedAt();
  const migDay = mig ? dayKey(mig) : null;
  const isAll = site === '_all';
  for (let i = days - 1; i >= 0; i--) {
    const start = dayStart(i);
    const key = dayKey(start);
    let r;
    if (isAll) {
      r = q1('SELECT SUM(humans) humans, SUM(sessions) sessions, SUM(pv) pv, SUM(crawler+datacenter+automation+internal) other FROM daily2 WHERE day=?', key);
    } else {
      r = q1('SELECT humans, sessions, pv, (crawler+datacenter+automation+internal) other FROM daily2 WHERE site=? AND day=?', site, key);
    }
    let legacy = null;
    if (migDay && key <= migDay) {
      legacy = isAll
        ? (q1('SELECT SUM(uniques) u FROM daily WHERE day=?', key)?.u ?? null)
        : (q1('SELECT uniques u FROM daily WHERE site=? AND day=?', site, key)?.u ?? null);
    }
    // aujourd'hui : recalcul en direct (le rollup 10 min peut être en retard)
    if (i === 0) {
      const live = isAll
        ? q1(`SELECT COUNT(DISTINCT vid) c FROM events WHERE ${H} AND ts>=?`, start)
        : q1(`SELECT COUNT(DISTINCT vid) c FROM events WHERE site=? AND ${H} AND ts>=?`, site, start);
      r = { ...(r || {}), humans: Math.max(r?.humans || 0, live?.c || 0) };
    }
    out.push({ day: key, humans: r?.humans || 0, sessions: r?.sessions || 0, pv: r?.pv || 0, other: r?.other || 0, legacy });
  }
  return { series: out, migratedAt: mig };
}

// fiche détaillée d'un site : top pages, referrers, devices, géo, horaires, sessions
export function analyticsSite(site, days = 7) {
  const now = Date.now();
  const since = now - days * 864e5;
  const t0 = dayStart(0);
  const pages = qa(`SELECT path, COUNT(*) pv, COUNT(DISTINCT vid) uv FROM events
    WHERE site=? AND ${H} AND ev='pv' AND ts>=? GROUP BY path ORDER BY pv DESC LIMIT 12`, site, since);
  const refs = qa(`SELECT ref, COUNT(DISTINCT vid) uv FROM events
    WHERE site=? AND ${H} AND ev='pv' AND ts>=? AND ref IS NOT NULL AND ref<>'' GROUP BY ref ORDER BY uv DESC LIMIT 10`, site, since);
  const devs = qa(`SELECT COALESCE(dev,'?') dev, COUNT(DISTINCT vid) uv FROM events
    WHERE site=? AND ${H} AND ts>=? GROUP BY dev ORDER BY uv DESC`, site, since);
  const geo = qa(`SELECT i.cc, i.country, i.region, i.city, COUNT(DISTINCT e.vid) uv FROM events e JOIN ipinfo i ON i.ip=e.ip
    WHERE e.site=? AND e.${H} AND e.ts>=? AND i.city<>'' GROUP BY i.cc,i.region,i.city ORDER BY uv DESC LIMIT 12`, site, since);
  const countries = qa(`SELECT i.cc, i.country, COUNT(DISTINCT e.vid) uv FROM events e JOIN ipinfo i ON i.ip=e.ip
    WHERE e.site=? AND e.${H} AND e.ts>=? AND i.country<>'' GROUP BY i.cc ORDER BY uv DESC LIMIT 8`, site, since);
  // 24 dernières heures (humains) : uniques + pv par heure
  const hourly = qa(`SELECT (ts/3600000)*3600000 h, COUNT(DISTINCT vid) uv, SUM(CASE WHEN ev='pv' THEN 1 ELSE 0 END) pv
    FROM events WHERE site=? AND ${H} AND ts>=? GROUP BY h ORDER BY h`, site, now - 24 * 3600_000);
  // sessions du jour : durée moyenne + pages/session + rebond
  const sess = qa(`SELECT sid, COUNT(CASE WHEN ev='pv' THEN 1 END) pv, MAX(ts)-MIN(ts) dur
    FROM events WHERE site=? AND ${H} AND ts>=? GROUP BY sid`, site, t0);
  let durSum = 0, pvSum = 0, bounce = 0;
  for (const s of sess) { durSum += s.dur; pvSum += s.pv; if (s.pv <= 1) bounce++; }
  const nS = sess.length || 1;
  // répartition des classes sur la fenêtre
  const classes = qa(`SELECT cls, COUNT(*) n, COUNT(DISTINCT vid) uv FROM events WHERE site=? AND ts>=? GROUP BY cls`, site, since);
  // nouveaux vs récurrents (parmi les humains du jour : vid vu avant aujourd'hui ?)
  const newRet = q1(`SELECT
      SUM(CASE WHEN first_ts >= ? THEN 1 ELSE 0 END) new_v,
      SUM(CASE WHEN first_ts < ? THEN 1 ELSE 0 END) ret_v
    FROM (SELECT vid, MIN(ts) first_ts FROM events WHERE site=? AND ${H} GROUP BY vid HAVING MAX(ts) >= ?)`, t0, t0, site, t0) || {};
  return {
    site, days, pages, refs, devs, geo, countries, hourly, classes,
    sessionsToday: sess.length,
    avgSessionSec: Math.round(durSum / nS / 1000),
    pagesPerSession: Math.round(pvSum / nS * 100) / 100,
    bouncePct: Math.round(bounce / nS * 100),
    newToday: newRet.new_v || 0, returningToday: newRet.ret_v || 0,
  };
}

// temps réel : humains actifs (dernier événement < activeSec)
export function analyticsRealtime(sites) {
  const cut = Date.now() - cfg.activeSec * 1000;
  const labels = {}; for (const s of sites) labels[s.app] = s.label || s.app;
  const rows = qa(`
    SELECT e.site, e.vid, e.dev, e.sid, e.ip, MAX(e.ts) last_ts,
           (SELECT MIN(ts) FROM events WHERE sid=e.sid) since,
           (SELECT path FROM events WHERE vid=e.vid AND site=e.site AND ts>=? ORDER BY ts DESC LIMIT 1) path,
           (SELECT i.city || CASE WHEN i.region<>'' THEN ', ' || i.region ELSE '' END FROM ipinfo i WHERE i.ip=e.ip AND i.city<>'') loc,
           (SELECT i.cc FROM ipinfo i WHERE i.ip=e.ip) cc
    FROM events e
    WHERE e.${H} AND e.ts >= ?
    GROUP BY e.site, e.vid ORDER BY last_ts DESC LIMIT 120`, cut, cut);
  return {
    active: rows.map(r => ({
      site: r.site, label: labels[r.site] || r.site, vid: r.vid.slice(0, 6),
      path: r.path, loc: r.loc || '—', cc: r.cc || '', dev: r.dev || '?',
      since: r.since, lastTs: r.last_ts,
    })),
    activeSec: cfg.activeSec, ts: Date.now(),
  };
}

// anomalies simples : IP bavardes, pics de trafic, poussée datacenter
export function analyticsAnomalies() {
  const now = Date.now();
  const h1 = now - 3600_000;
  const topIps = qa(`SELECT e.ip, COUNT(*) n, COUNT(DISTINCT e.site) sites, MAX(e.cls) cls,
      (SELECT org FROM ipinfo WHERE ip=e.ip) org, (SELECT city FROM ipinfo WHERE ip=e.ip) city
    FROM events e WHERE e.ts>=? GROUP BY e.ip HAVING n > 120 ORDER BY n DESC LIMIT 10`, h1);
  // pic : événements de la dernière heure vs moyenne horaire des 24 h précédentes
  const spikes = [];
  const sitesH = qa(`SELECT site, COUNT(*) n FROM events WHERE ts>=? GROUP BY site`, h1);
  for (const s of sitesH) {
    const avg = q1(`SELECT COUNT(*)/24.0 a FROM events WHERE site=? AND ts>=? AND ts<?`, s.site, now - 25 * 3600_000, h1)?.a || 0;
    if (avg >= 2 && s.n > avg * 5) spikes.push({ site: s.site, lastHour: s.n, hourlyAvg: Math.round(avg), factor: Math.round(s.n / avg * 10) / 10 });
  }
  const dcSurge = q1(`SELECT COUNT(*) n FROM events WHERE ts>=? AND cls='datacenter'`, h1)?.n || 0;
  const dcAvg = q1(`SELECT COUNT(*)/24.0 a FROM events WHERE ts>=? AND ts<? AND cls='datacenter'`, now - 25 * 3600_000, h1)?.a || 0;
  return {
    topIps, spikes,
    datacenter: { lastHour: dcSurge, hourlyAvg: Math.round(dcAvg), surge: dcAvg >= 2 && dcSurge > dcAvg * 5 },
    ts: now,
  };
}

// mode debug : pourquoi une visite est classée comme elle l'est
export function analyticsInspect(q) {
  const byIp = q.ip ? String(q.ip) : null;
  const byVid = q.vid ? String(q.vid) : null;
  if (!byIp && !byVid) return { error: 'fournir ip= ou vid=' };
  const where = byIp ? 'ip=?' : 'vid LIKE ?';
  const arg = byIp || (byVid + '%');
  const events = qa(`SELECT site, vid, sid, ts, ev, path, ref, ip, cls, dev, v FROM events WHERE ${where} ORDER BY ts DESC LIMIT 40`, arg);
  if (!events.length) return { events: [], reasons: ['aucun événement trouvé'] };
  const ip = events[0].ip;
  const ipRow = getIpRow(ip);
  const cnt1h = q1('SELECT COUNT(*) c FROM events WHERE ip=? AND ts>=?', ip, Date.now() - 3600_000)?.c || 0;
  const stored = events[0].cls;
  const reasons = [];
  // raisons dérivées de l'état réel (l'UA n'est pas conservé par événement)
  if (q.ua) {
    const replay = classify(q.ua, ip, ipRow, false);
    reasons.push('rejeu avec UA fourni → ' + replay.cls, ...replay.reasons);
  }
  if (INTERNAL_IP_RE.test(ip)) reasons.push('IP locale/cluster');
  if (ipRow) {
    reasons.push(`réseau : AS${ipRow.asn} ${ipRow.org || ipRow.isp || '?'} (${[ipRow.city, ipRow.country].filter(Boolean).join(', ') || 'lieu inconnu'})`);
    if (ipRow.hosting) reasons.push('drapeau hosting/datacenter → exclu des humains');
    if (ipRow.proxy) reasons.push('drapeau proxy/VPN → exclu des humains');
    if (cfg.blockedASNs.includes(ipRow.asn)) reasons.push('ASN exclu (réglages)');
    if (DC_ORG_RE.test((ipRow.org || '') + ' ' + (ipRow.isp || ''))) reasons.push('organisation de type hébergeur');
    if (!ipRow.hosting && !ipRow.proxy) reasons.push('réseau résidentiel/mobile');
  } else reasons.push('IP pas encore résolue (file ip-api — reclassement auto ensuite)');
  if (stored === 'crawler') reasons.push('User-Agent de crawler connu au moment du hit');
  if (stored === 'automation') reasons.push('User-Agent outil/headless (ou flood) au moment du hit');
  if (stored === 'internal') reasons.push('trafic interne Groupe KA (IP/UA/en-tête)');
  if (cnt1h > cfg.maxIpPerHour) reasons.push(`${cnt1h} événements dans l'heure (> ${cfg.maxIpPerHour}) → automation`);
  const sids = new Set(events.map(e => e.sid));
  return {
    ip, ipinfo: ipRow, storedClass: stored,
    reasons: [...reasons, `${cnt1h} evt/h pour cette IP`, `${sids.size} session(s) dans les événements récents`],
    events,
  };
}

// répartition des classes (période paramétrable) pour le toggle Humains|Tout
export function analyticsClasses(sinceMs) {
  const since = Date.now() - sinceMs;
  const rows = qa(`SELECT cls, COUNT(*) hits, COUNT(DISTINCT vid) vids FROM events WHERE ts>=? GROUP BY cls`, since);
  const out = { human: { hits: 0, vids: 0 }, crawler: { hits: 0, vids: 0 }, datacenter: { hits: 0, vids: 0 }, automation: { hits: 0, vids: 0 }, internal: { hits: 0, vids: 0 } };
  for (const r of rows) {
    const k = r.cls === 'unknown' ? 'automation' : r.cls;
    if (out[k]) { out[k].hits += r.hits; out[k].vids += r.vids; }
  }
  return out;
}