JavaScript 65.5%
Python 17.8%
CSS 13%
HTML 3.7%
1// admin-ka — Analytics first-party v2 : trafic HUMAIN crédible par site KA.2//3// Pipeline : beacon (v1 legacy 60 s, ou v2 ka-a.js pv/heartbeat) → /collect →4// classification à l'insertion (humain / crawler / datacenter / automation /5// interne) via User-Agent + IP-intelligence (ip-api, cache SQLite, hosting/6// proxy/ASN) → événements typés (page_view / heartbeat) + sessions → agrégats7// quotidiens par classe. Les métriques principales n'affichent QUE les humains.8//9// Définitions (documentées dans CLAUDE.md) :10// - visiteur humain : vid dont les événements sont classés `human`11// - visiteur unique : vid humain distinct dans la fenêtre12// - session : suite d'événements d'un même vid sans trou > sessionMin (30 min)13// - page vue : événement `pv` (navigation réelle, SPA incluse) — jamais un heartbeat14// - en ligne / actif : humain dont le dernier événement < activeSec (120 s) ;15// expire tout seul, un onglet fermé disparaît en ~2 min16import fs from 'node:fs';17import path from 'node:path';18import crypto from 'node:crypto';19import { fileURLToPath } from 'node:url';20import { DatabaseSync } from 'node:sqlite';2122const __dirname = path.dirname(fileURLToPath(import.meta.url));23const DATA_DIR = path.join(__dirname, '..', 'data');24const DB_PATH = path.join(DATA_DIR, 'analytics.sqlite');25const CONFIG_PATH = path.join(DATA_DIR, 'config.json');2627let db = null;2829// GIF transparent 1x1 renvoyé au beacon30export const PIXEL = Buffer.from('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7', 'base64');3132// ---------- configuration (surchageable via config.json → "analytics") ----------33export const DEFAULTS = {34 sessionMin: 30, // minutes d'inactivité avant nouvelle session35 activeSec: 120, // fenêtre « en ligne » (dernier événement < N s)36 retentionDays: 45, // rétention des événements bruts37 maxIpPerHour: 1200, // au-delà : trafic reclassé automation (flood)38 internalIps: [], // IPs/préfixes internes additionnels39 internalUAs: [], // fragments UA internes additionnels40 extraBotUAs: [], // fragments UA bots additionnels41 blockedASNs: [], // ASNs (numéros) forcés en datacenter42 debug: false,43};44let cfg = { ...DEFAULTS };45function loadCfg() {46 try {47 const j = JSON.parse(fs.readFileSync(CONFIG_PATH, 'utf8'));48 cfg = { ...DEFAULTS, ...(j.analytics || {}) };49 } catch { cfg = { ...DEFAULTS }; }50}51export function analyticsConfig() { return { ...cfg }; }52export function setAnalyticsConfig(patch) {53 const j = JSON.parse(fs.readFileSync(CONFIG_PATH, 'utf8'));54 j.analytics = { ...(j.analytics || {}) };55 for (const k of Object.keys(DEFAULTS)) {56 if (!(k in patch)) continue;57 if (Array.isArray(DEFAULTS[k])) j.analytics[k] = Array.isArray(patch[k]) ? patch[k].map(String).filter(Boolean).slice(0, 200) : [];58 else if (typeof DEFAULTS[k] === 'number') { const n = Number(patch[k]); if (Number.isFinite(n) && n > 0) j.analytics[k] = n; }59 else if (typeof DEFAULTS[k] === 'boolean') j.analytics[k] = !!patch[k];60 }61 fs.writeFileSync(CONFIG_PATH, JSON.stringify(j, null, 2));62 loadCfg();63 return analyticsConfig();64}6566// ---------- classification ----------67// Crawlers connus (moteurs, IA, SEO, réseaux sociaux, uptime)68const 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;69// Outils / scripts / navigateurs headless (pas un navigateur humain)70const 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;71// Hébergeurs / clouds — filet quand ip-api n'a pas le drapeau hosting72const 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;73// UA interne Groupe KA (monitoring, gardiens, scripts maison)74const INTERNAL_UA_RE = /admin-ka|ka-guardian|maclustr|adminka/i;75// IP locales / cluster / tailscale76const 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\.)/;77const BROWSER_RE = /mozilla\/5\.0/i;7879export const CLASSES = ['human', 'crawler', 'datacenter', 'automation', 'internal', 'unknown'];8081function classify(ua, ip, ipRow, internalHeader) {82 const reasons = [];83 ua = String(ua || '');84 // 1) interne (header dédié, IP cluster/LAN, UA maison, listes config)85 if (internalHeader) return { cls: 'internal', reasons: ['en-tête X-KA-Internal'] };86 if (INTERNAL_IP_RE.test(ip)) return { cls: 'internal', reasons: ['IP locale/cluster (' + ip + ')'] };87 if (cfg.internalIps.some(p => p && ip.startsWith(p))) return { cls: 'internal', reasons: ['IP interne (réglages)'] };88 if (INTERNAL_UA_RE.test(ua) || cfg.internalUAs.some(p => p && ua.toLowerCase().includes(p.toLowerCase())))89 return { cls: 'internal', reasons: ['User-Agent interne'] };90 // 2) crawlers connus91 if (BOT_RE.test(ua) || cfg.extraBotUAs.some(p => p && ua.toLowerCase().includes(p.toLowerCase()))) {92 const m = ua.match(BOT_RE);93 return { cls: 'crawler', reasons: ['User-Agent crawler connu' + (m ? ' (' + m[0] + ')' : '')] };94 }95 // 3) outils / headless / pas un navigateur96 if (TOOL_RE.test(ua)) return { cls: 'automation', reasons: ['User-Agent outil/headless : ' + (ua.slice(0, 60) || '(vide)')] };97 if (!BROWSER_RE.test(ua)) return { cls: 'automation', reasons: ['User-Agent non-navigateur : ' + ua.slice(0, 60)] };98 // 4) réseau datacenter / hosting / proxy / ASN bloqué99 if (ipRow) {100 if (cfg.blockedASNs.includes(ipRow.asn)) { reasons.push('ASN ' + ipRow.asn + ' exclu (réglages)'); return { cls: 'datacenter', reasons }; }101 if (ipRow.hosting) { reasons.push('réseau hosting/datacenter (' + (ipRow.org || ipRow.isp || 'AS' + ipRow.asn) + ')'); return { cls: 'datacenter', reasons }; }102 if (ipRow.proxy) { reasons.push('proxy/VPN détecté (' + (ipRow.org || ipRow.isp || '') + ')'); return { cls: 'datacenter', reasons }; }103 if (DC_ORG_RE.test((ipRow.org || '') + ' ' + (ipRow.isp || ''))) { reasons.push('organisation hébergeur : ' + (ipRow.org || ipRow.isp)); return { cls: 'datacenter', reasons }; }104 reasons.push('réseau résidentiel/mobile (' + (ipRow.isp || ipRow.org || 'AS' + ipRow.asn) + ')');105 } else {106 reasons.push('IP pas encore classifiée (reclassement auto dès résolution)');107 }108 reasons.push('navigateur + JS exécuté');109 return { cls: 'human', reasons };110}111112// ---------- init ----------113export function initAnalytics() {114 fs.mkdirSync(DATA_DIR, { recursive: true });115 loadCfg();116 db = new DatabaseSync(DB_PATH);117 db.exec(`118 PRAGMA journal_mode = WAL;119 -- v2 : événements typés + classe de trafic120 CREATE TABLE IF NOT EXISTS events (121 site TEXT, vid TEXT, sid TEXT, ts INTEGER, ev TEXT, path TEXT, ref TEXT,122 ip TEXT, cls TEXT, dev TEXT, v INTEGER123 );124 CREATE INDEX IF NOT EXISTS idx_ev_site_ts ON events(site, ts);125 CREATE INDEX IF NOT EXISTS idx_ev_ts ON events(ts);126 CREATE INDEX IF NOT EXISTS idx_ev_ip ON events(ip);127 CREATE INDEX IF NOT EXISTS idx_ev_sid ON events(sid);128 CREATE INDEX IF NOT EXISTS idx_ev_vid ON events(vid);129 -- IP intelligence (cache — un lookup par IP, jamais par hit)130 CREATE TABLE IF NOT EXISTS ipinfo (131 ip TEXT PRIMARY KEY, asn INTEGER, org TEXT, isp TEXT,132 cc TEXT, country TEXT, region TEXT, city TEXT,133 hosting INTEGER, proxy INTEGER, mobile INTEGER, ts INTEGER134 );135 -- agrégats quotidiens v2 (long terme, par classe)136 CREATE TABLE IF NOT EXISTS daily2 (137 site TEXT, day TEXT, humans INTEGER, sessions INTEGER, pv INTEGER,138 crawler INTEGER, datacenter INTEGER, automation INTEGER, internal INTEGER,139 total_vids INTEGER, PRIMARY KEY(site, day)140 );141 CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT);142 -- héritées de v1 (historique conservé, plus alimentées) : hits, daily, geo143 CREATE TABLE IF NOT EXISTS hits (site TEXT, vid TEXT, ts INTEGER, path TEXT);144 CREATE TABLE IF NOT EXISTS daily (site TEXT, day TEXT, uniques INTEGER, pv INTEGER, PRIMARY KEY(site, day));145 `);146 if (!db.prepare('SELECT value FROM meta WHERE key=?').get('migrated_at')) {147 db.prepare('INSERT INTO meta (key,value) VALUES (?,?)').run('migrated_at', String(Date.now()));148 }149 rollup();150 setInterval(rollup, 10 * 60 * 1000);151 setInterval(purge, 6 * 60 * 60 * 1000);152 setInterval(resolveIps, 30 * 1000);153 setInterval(() => { ipHourly.clear(); }, 60 * 60 * 1000); // fenêtre anti-flood glissante grossière154 purge(); resolveIps();155}156157export function migratedAt() {158 const r = db.prepare('SELECT value FROM meta WHERE key=?').get('migrated_at');159 return r ? Number(r.value) : null;160}161162// ---------- ingestion ----------163// état de session en mémoire pour le beacon v1 (pas de sid côté client)164const sessState = new Map(); // site|vid -> { sid, lastTs, lastPath }165const ipHourly = new Map(); // ip -> compteur d'événements (fenêtre ~1 h)166let insEv = null;167168function getIpRow(ip) {169 try { return db.prepare('SELECT * FROM ipinfo WHERE ip=?').get(ip) || null; } catch { return null; }170}171172export function recordHit(q, req) {173 if (!db) return;174 const site = String(q.get('site') || '').slice(0, 40);175 const vid = String(q.get('vid') || '').slice(0, 64);176 if (!site || !vid) return;177 const now = Date.now();178 const pathP = String(q.get('p') || '/').slice(0, 200);179 const ref = String(q.get('r') || '').slice(0, 200);180 const dev = ['m', 't', 'd'].includes(q.get('d')) ? q.get('d') : null;181 const v = q.get('sid') ? 2 : 1;182 const ua = String(req.headers['user-agent'] || '');183 const xf = req.headers['x-forwarded-for'];184 const ip = ((xf ? String(xf).split(',')[0] : req.socket.remoteAddress) || '').trim().slice(0, 45);185 const internalHeader = req.headers['x-ka-internal'] === '1';186187 // classe de trafic (IP-intelligence si déjà en cache, sinon provisoire)188 let { cls } = classify(ua, ip, getIpRow(ip), internalHeader);189 // anti-flood : une IP qui dépasse maxIpPerHour passe en automation190 const cnt = (ipHourly.get(ip) || 0) + 1;191 ipHourly.set(ip, cnt);192 if (cls === 'human' && cnt > cfg.maxIpPerHour) cls = 'automation';193194 // session + type d'événement195 let sid, ev;196 if (v === 2) {197 sid = String(q.get('sid')).slice(0, 64);198 ev = q.get('ev') === 'hb' ? 'hb' : 'pv';199 } else {200 // beacon v1 : ping au chargement + toutes les 60 s → on sessionne et on201 // dé-duplique côté serveur (un ping d'intervalle N'EST PAS une page vue)202 const key = site + '|' + vid;203 const st = sessState.get(key);204 if (!st || now - st.lastTs > cfg.sessionMin * 60_000) {205 sid = vid + '.' + now.toString(36);206 ev = 'pv';207 } else {208 sid = st.sid;209 // nouvelle page = pv ; re-ping rapide (<50 s) même page = reload = pv ;210 // ping d'intervalle (~60 s+) même page = heartbeat211 ev = (pathP !== st.lastPath || now - st.lastTs < 50_000) ? 'pv' : 'hb';212 }213 sessState.set(key, { sid, lastTs: now, lastPath: pathP });214 if (sessState.size > 50_000) sessState.clear();215 }216217 try {218 if (!insEv) insEv = db.prepare('INSERT INTO events (site,vid,sid,ts,ev,path,ref,ip,cls,dev,v) VALUES (?,?,?,?,?,?,?,?,?,?,?)');219 insEv.run(site, vid, sid, now, ev, pathP, ref || null, ip, cls, dev, v);220 } catch {}221}222223// ---------- IP intelligence (ip-api.com, gratuit ≤45 req/min, cache) ----------224let ipBusy = false;225async function resolveIps() {226 if (!db || ipBusy) return;227 ipBusy = true;228 try {229 const rows = db.prepare(`230 SELECT DISTINCT e.ip FROM events e231 WHERE e.ip <> '' AND e.ip NOT IN (SELECT ip FROM ipinfo)232 ORDER BY e.ts DESC LIMIT 18`).all();233 for (const r of rows) {234 const ip = r.ip;235 if (INTERNAL_IP_RE.test(ip)) {236 db.prepare('INSERT OR REPLACE INTO ipinfo (ip,asn,org,isp,cc,country,region,city,hosting,proxy,mobile,ts) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')237 .run(ip, 0, 'Réseau local', 'Local', 'XX', 'Local', '', '', 0, 0, 0, Date.now());238 continue;239 }240 try {241 const res = await fetch('http://ip-api.com/json/' + encodeURIComponent(ip)242 + '?fields=status,country,countryCode,regionName,city,isp,org,as,hosting,proxy,mobile',243 { signal: AbortSignal.timeout(8000) });244 const d = await res.json();245 if (d && d.status === 'success') {246 const asn = Number((String(d.as || '').match(/^AS(\d+)/) || [])[1] || 0);247 db.prepare('INSERT OR REPLACE INTO ipinfo (ip,asn,org,isp,cc,country,region,city,hosting,proxy,mobile,ts) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')248 .run(ip, asn, d.org || '', d.isp || '', d.countryCode || '', d.country || '', d.regionName || '', d.city || '',249 d.hosting ? 1 : 0, d.proxy ? 1 : 0, d.mobile ? 1 : 0, Date.now());250 reclassifyIp(ip);251 } else {252 db.prepare('INSERT OR REPLACE INTO ipinfo (ip,asn,org,isp,cc,country,region,city,hosting,proxy,mobile,ts) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)')253 .run(ip, 0, '', '', '', '', '', '', 0, 0, 0, Date.now());254 }255 } catch { break; } // réseau indisponible — on réessaiera au prochain cycle256 await new Promise(r2 => setTimeout(r2, 1500)); // respecter la limite ip-api257 }258 } catch {}259 ipBusy = false;260}261262// une IP vient d'être résolue : reclasse rétroactivement ses événements263// provisoirement « human » si le réseau est un datacenter/proxy/ASN exclu264function reclassifyIp(ip) {265 const row = getIpRow(ip);266 if (!row) return;267 const isDc = row.hosting || row.proxy || cfg.blockedASNs.includes(row.asn) || DC_ORG_RE.test((row.org || '') + ' ' + (row.isp || ''));268 if (isDc) {269 try { db.prepare(`UPDATE events SET cls='datacenter' WHERE ip=? AND cls='human'`).run(ip); } catch {}270 }271}272273// ---------- entretien ----------274function purge() {275 if (!db) return;276 const cutoff = Date.now() - cfg.retentionDays * 864e5;277 try {278 db.prepare('DELETE FROM events WHERE ts < ?').run(cutoff);279 db.prepare('DELETE FROM hits WHERE ts < ?').run(cutoff);280 db.prepare('DELETE FROM ipinfo WHERE ts < ?').run(Date.now() - 90 * 864e5); // re-résolution périodique281 } catch {}282}283284function dayKey(ts) {285 const d = new Date(ts);286 return d.getFullYear() + '-' + String(d.getMonth() + 1).padStart(2, '0') + '-' + String(d.getDate()).padStart(2, '0');287}288function dayStart(offsetDays = 0) {289 const d = new Date(); d.setHours(0, 0, 0, 0); d.setDate(d.getDate() - offsetDays);290 return d.getTime();291}292293// fige les agrégats quotidiens par classe (3 derniers jours recalculés)294function rollup() {295 if (!db) return;296 try {297 const since = dayStart(2);298 const rows = db.prepare('SELECT site, ts, vid, sid, ev, cls FROM events WHERE ts >= ?').all(since);299 const agg = {}; // site|day -> accumulateurs300 for (const r of rows) {301 const k = r.site + '|' + dayKey(r.ts);302 let a = agg[k];303 if (!a) a = agg[k] = { hv: new Set(), hs: new Set(), pv: 0, crawler: 0, datacenter: 0, automation: 0, internal: 0, av: new Set() };304 a.av.add(r.vid);305 if (r.cls === 'human') {306 a.hv.add(r.vid); a.hs.add(r.sid);307 if (r.ev === 'pv') a.pv++;308 } else if (a[r.cls] !== undefined) a[r.cls]++;309 }310 const up = db.prepare('INSERT OR REPLACE INTO daily2 (site,day,humans,sessions,pv,crawler,datacenter,automation,internal,total_vids) VALUES (?,?,?,?,?,?,?,?,?,?)');311 for (const k in agg) {312 const [site, day] = k.split('|');313 const a = agg[k];314 up.run(site, day, a.hv.size, a.hs.size, a.pv, a.crawler, a.datacenter, a.automation, a.internal, a.av.size);315 }316 } catch {}317}318319// ---------- requêtes ----------320const H = `cls='human'`;321322function q1(sql, ...args) { try { return db.prepare(sql).get(...args); } catch { return null; } }323function qa(sql, ...args) { try { return db.prepare(sql).all(...args); } catch { return []; } }324325function humanUniques(site, a, b) {326 return q1(`SELECT COUNT(DISTINCT vid) c FROM events WHERE site=? AND ${H} AND ts>=? AND ts<?`, site, a, b)?.c || 0;327}328329// résumé global + par site (métriques HUMAINES + répartition des classes)330export function analyticsSummary(sites) {331 const now = Date.now();332 const t0 = dayStart(0);333 const y0 = dayStart(1);334 const activeCut = now - cfg.activeSec * 1000;335 const out = [];336 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 };337 for (const s of sites) {338 const site = s.app || s;339 const t = q1(`SELECT COUNT(DISTINCT vid) uv, COUNT(DISTINCT sid) ss, SUM(CASE WHEN ev='pv' THEN 1 ELSE 0 END) pv, COUNT(*) hits340 FROM events WHERE site=? AND ${H} AND ts>=?`, site, t0) || {};341 const other = q1(`SELECT342 SUM(CASE WHEN cls='crawler' THEN 1 ELSE 0 END) crawler,343 SUM(CASE WHEN cls='datacenter' THEN 1 ELSE 0 END) datacenter,344 SUM(CASE WHEN cls IN ('automation','unknown') THEN 1 ELSE 0 END) automation,345 SUM(CASE WHEN cls='internal' THEN 1 ELSE 0 END) internal,346 COUNT(DISTINCT vid) total_vids347 FROM events WHERE site=? AND ts>=?`, site, t0) || {};348 const row = {349 site, label: s.label || site, domain: s.domain || '',350 online: q1(`SELECT COUNT(DISTINCT vid) c FROM events WHERE site=? AND ${H} AND ts>=?`, site, activeCut)?.c || 0,351 today: t.uv || 0,352 yesterdaySame: humanUniques(site, y0, y0 + (now - t0)), // hier à la même heure (delta honnête)353 sessions: t.ss || 0,354 pv: t.pv || 0,355 humanHits: t.hits || 0,356 d7: humanUniques(site, now - 7 * 864e5, now + 1),357 d30: humanUniques(site, now - 30 * 864e5, now + 1),358 crawler: other.crawler || 0, datacenter: other.datacenter || 0,359 automation: other.automation || 0, internal: other.internal || 0,360 total_vids: other.total_vids || 0,361 };362 out.push(row);363 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];364 }365 out.sort((a, b) => b.online - a.online || b.today - a.today);366 return { sites: out, global: g, updated: now, activeSec: cfg.activeSec, migratedAt: migratedAt() };367}368369// série quotidienne 30 j (v2 par classe + héritage v1 avant migration)370export function analyticsSeries(site, days = 30) {371 const out = [];372 const mig = migratedAt();373 const migDay = mig ? dayKey(mig) : null;374 const isAll = site === '_all';375 for (let i = days - 1; i >= 0; i--) {376 const start = dayStart(i);377 const key = dayKey(start);378 let r;379 if (isAll) {380 r = q1('SELECT SUM(humans) humans, SUM(sessions) sessions, SUM(pv) pv, SUM(crawler+datacenter+automation+internal) other FROM daily2 WHERE day=?', key);381 } else {382 r = q1('SELECT humans, sessions, pv, (crawler+datacenter+automation+internal) other FROM daily2 WHERE site=? AND day=?', site, key);383 }384 let legacy = null;385 if (migDay && key <= migDay) {386 legacy = isAll387 ? (q1('SELECT SUM(uniques) u FROM daily WHERE day=?', key)?.u ?? null)388 : (q1('SELECT uniques u FROM daily WHERE site=? AND day=?', site, key)?.u ?? null);389 }390 // aujourd'hui : recalcul en direct (le rollup 10 min peut être en retard)391 if (i === 0) {392 const live = isAll393 ? q1(`SELECT COUNT(DISTINCT vid) c FROM events WHERE ${H} AND ts>=?`, start)394 : q1(`SELECT COUNT(DISTINCT vid) c FROM events WHERE site=? AND ${H} AND ts>=?`, site, start);395 r = { ...(r || {}), humans: Math.max(r?.humans || 0, live?.c || 0) };396 }397 out.push({ day: key, humans: r?.humans || 0, sessions: r?.sessions || 0, pv: r?.pv || 0, other: r?.other || 0, legacy });398 }399 return { series: out, migratedAt: mig };400}401402// fiche détaillée d'un site : top pages, referrers, devices, géo, horaires, sessions403export function analyticsSite(site, days = 7) {404 const now = Date.now();405 const since = now - days * 864e5;406 const t0 = dayStart(0);407 const pages = qa(`SELECT path, COUNT(*) pv, COUNT(DISTINCT vid) uv FROM events408 WHERE site=? AND ${H} AND ev='pv' AND ts>=? GROUP BY path ORDER BY pv DESC LIMIT 12`, site, since);409 const refs = qa(`SELECT ref, COUNT(DISTINCT vid) uv FROM events410 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);411 const devs = qa(`SELECT COALESCE(dev,'?') dev, COUNT(DISTINCT vid) uv FROM events412 WHERE site=? AND ${H} AND ts>=? GROUP BY dev ORDER BY uv DESC`, site, since);413 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.ip414 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);415 const countries = qa(`SELECT i.cc, i.country, COUNT(DISTINCT e.vid) uv FROM events e JOIN ipinfo i ON i.ip=e.ip416 WHERE e.site=? AND e.${H} AND e.ts>=? AND i.country<>'' GROUP BY i.cc ORDER BY uv DESC LIMIT 8`, site, since);417 // 24 dernières heures (humains) : uniques + pv par heure418 const hourly = qa(`SELECT (ts/3600000)*3600000 h, COUNT(DISTINCT vid) uv, SUM(CASE WHEN ev='pv' THEN 1 ELSE 0 END) pv419 FROM events WHERE site=? AND ${H} AND ts>=? GROUP BY h ORDER BY h`, site, now - 24 * 3600_000);420 // sessions du jour : durée moyenne + pages/session + rebond421 const sess = qa(`SELECT sid, COUNT(CASE WHEN ev='pv' THEN 1 END) pv, MAX(ts)-MIN(ts) dur422 FROM events WHERE site=? AND ${H} AND ts>=? GROUP BY sid`, site, t0);423 let durSum = 0, pvSum = 0, bounce = 0;424 for (const s of sess) { durSum += s.dur; pvSum += s.pv; if (s.pv <= 1) bounce++; }425 const nS = sess.length || 1;426 // répartition des classes sur la fenêtre427 const classes = qa(`SELECT cls, COUNT(*) n, COUNT(DISTINCT vid) uv FROM events WHERE site=? AND ts>=? GROUP BY cls`, site, since);428 // nouveaux vs récurrents (parmi les humains du jour : vid vu avant aujourd'hui ?)429 const newRet = q1(`SELECT430 SUM(CASE WHEN first_ts >= ? THEN 1 ELSE 0 END) new_v,431 SUM(CASE WHEN first_ts < ? THEN 1 ELSE 0 END) ret_v432 FROM (SELECT vid, MIN(ts) first_ts FROM events WHERE site=? AND ${H} GROUP BY vid HAVING MAX(ts) >= ?)`, t0, t0, site, t0) || {};433 return {434 site, days, pages, refs, devs, geo, countries, hourly, classes,435 sessionsToday: sess.length,436 avgSessionSec: Math.round(durSum / nS / 1000),437 pagesPerSession: Math.round(pvSum / nS * 100) / 100,438 bouncePct: Math.round(bounce / nS * 100),439 newToday: newRet.new_v || 0, returningToday: newRet.ret_v || 0,440 };441}442443// temps réel : humains actifs (dernier événement < activeSec)444export function analyticsRealtime(sites) {445 const cut = Date.now() - cfg.activeSec * 1000;446 const labels = {}; for (const s of sites) labels[s.app] = s.label || s.app;447 const rows = qa(`448 SELECT e.site, e.vid, e.dev, e.sid, e.ip, MAX(e.ts) last_ts,449 (SELECT MIN(ts) FROM events WHERE sid=e.sid) since,450 (SELECT path FROM events WHERE vid=e.vid AND site=e.site AND ts>=? ORDER BY ts DESC LIMIT 1) path,451 (SELECT i.city || CASE WHEN i.region<>'' THEN ', ' || i.region ELSE '' END FROM ipinfo i WHERE i.ip=e.ip AND i.city<>'') loc,452 (SELECT i.cc FROM ipinfo i WHERE i.ip=e.ip) cc453 FROM events e454 WHERE e.${H} AND e.ts >= ?455 GROUP BY e.site, e.vid ORDER BY last_ts DESC LIMIT 120`, cut, cut);456 return {457 active: rows.map(r => ({458 site: r.site, label: labels[r.site] || r.site, vid: r.vid.slice(0, 6),459 path: r.path, loc: r.loc || '—', cc: r.cc || '', dev: r.dev || '?',460 since: r.since, lastTs: r.last_ts,461 })),462 activeSec: cfg.activeSec, ts: Date.now(),463 };464}465466// anomalies simples : IP bavardes, pics de trafic, poussée datacenter467export function analyticsAnomalies() {468 const now = Date.now();469 const h1 = now - 3600_000;470 const topIps = qa(`SELECT e.ip, COUNT(*) n, COUNT(DISTINCT e.site) sites, MAX(e.cls) cls,471 (SELECT org FROM ipinfo WHERE ip=e.ip) org, (SELECT city FROM ipinfo WHERE ip=e.ip) city472 FROM events e WHERE e.ts>=? GROUP BY e.ip HAVING n > 120 ORDER BY n DESC LIMIT 10`, h1);473 // pic : événements de la dernière heure vs moyenne horaire des 24 h précédentes474 const spikes = [];475 const sitesH = qa(`SELECT site, COUNT(*) n FROM events WHERE ts>=? GROUP BY site`, h1);476 for (const s of sitesH) {477 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;478 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 });479 }480 const dcSurge = q1(`SELECT COUNT(*) n FROM events WHERE ts>=? AND cls='datacenter'`, h1)?.n || 0;481 const dcAvg = q1(`SELECT COUNT(*)/24.0 a FROM events WHERE ts>=? AND ts<? AND cls='datacenter'`, now - 25 * 3600_000, h1)?.a || 0;482 return {483 topIps, spikes,484 datacenter: { lastHour: dcSurge, hourlyAvg: Math.round(dcAvg), surge: dcAvg >= 2 && dcSurge > dcAvg * 5 },485 ts: now,486 };487}488489// mode debug : pourquoi une visite est classée comme elle l'est490export function analyticsInspect(q) {491 const byIp = q.ip ? String(q.ip) : null;492 const byVid = q.vid ? String(q.vid) : null;493 if (!byIp && !byVid) return { error: 'fournir ip= ou vid=' };494 const where = byIp ? 'ip=?' : 'vid LIKE ?';495 const arg = byIp || (byVid + '%');496 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);497 if (!events.length) return { events: [], reasons: ['aucun événement trouvé'] };498 const ip = events[0].ip;499 const ipRow = getIpRow(ip);500 const cnt1h = q1('SELECT COUNT(*) c FROM events WHERE ip=? AND ts>=?', ip, Date.now() - 3600_000)?.c || 0;501 const stored = events[0].cls;502 const reasons = [];503 // raisons dérivées de l'état réel (l'UA n'est pas conservé par événement)504 if (q.ua) {505 const replay = classify(q.ua, ip, ipRow, false);506 reasons.push('rejeu avec UA fourni → ' + replay.cls, ...replay.reasons);507 }508 if (INTERNAL_IP_RE.test(ip)) reasons.push('IP locale/cluster');509 if (ipRow) {510 reasons.push(`réseau : AS${ipRow.asn} ${ipRow.org || ipRow.isp || '?'} (${[ipRow.city, ipRow.country].filter(Boolean).join(', ') || 'lieu inconnu'})`);511 if (ipRow.hosting) reasons.push('drapeau hosting/datacenter → exclu des humains');512 if (ipRow.proxy) reasons.push('drapeau proxy/VPN → exclu des humains');513 if (cfg.blockedASNs.includes(ipRow.asn)) reasons.push('ASN exclu (réglages)');514 if (DC_ORG_RE.test((ipRow.org || '') + ' ' + (ipRow.isp || ''))) reasons.push('organisation de type hébergeur');515 if (!ipRow.hosting && !ipRow.proxy) reasons.push('réseau résidentiel/mobile');516 } else reasons.push('IP pas encore résolue (file ip-api — reclassement auto ensuite)');517 if (stored === 'crawler') reasons.push('User-Agent de crawler connu au moment du hit');518 if (stored === 'automation') reasons.push('User-Agent outil/headless (ou flood) au moment du hit');519 if (stored === 'internal') reasons.push('trafic interne Groupe KA (IP/UA/en-tête)');520 if (cnt1h > cfg.maxIpPerHour) reasons.push(`${cnt1h} événements dans l'heure (> ${cfg.maxIpPerHour}) → automation`);521 const sids = new Set(events.map(e => e.sid));522 return {523 ip, ipinfo: ipRow, storedClass: stored,524 reasons: [...reasons, `${cnt1h} evt/h pour cette IP`, `${sids.size} session(s) dans les événements récents`],525 events,526 };527}528529// répartition des classes (période paramétrable) pour le toggle Humains|Tout530export function analyticsClasses(sinceMs) {531 const since = Date.now() - sinceMs;532 const rows = qa(`SELECT cls, COUNT(*) hits, COUNT(DISTINCT vid) vids FROM events WHERE ts>=? GROUP BY cls`, since);533 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 } };534 for (const r of rows) {535 const k = r.cls === 'unknown' ? 'automation' : r.cls;536 if (out[k]) { out[k].hits += r.hits; out[k].vids += r.vids; }537 }538 return out;539}540