1// admin-ka — Analytics first-party : visiteurs uniques par site KA.2// Un beacon (1px) sur chaque site envoie GET /collect?site=..&vid=..&p=..3// On stocke les hits (SQLite), on compte les uniques (vid distincts) en live,4// et on fige un rollup quotidien pour l'historique long terme.5import fs from 'node:fs';6import path from 'node:path';7import { fileURLToPath } from 'node:url';8import { DatabaseSync } from 'node:sqlite';910const __dirname = path.dirname(fileURLToPath(import.meta.url));11const DATA_DIR = path.join(__dirname, '..', 'data');12const DB_PATH = path.join(DATA_DIR, 'analytics.sqlite');1314let db = null;15const RETENTION_DAYS = 45;1617// GIF transparent 1x1 renvoyé au beacon18export const PIXEL = Buffer.from('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7', 'base64');1920function dayKey(ts) {21 // jour local (Amérique/Montréal ~ heure du serveur)22 const d = new Date(ts);23 return d.getFullYear() + '-' + String(d.getMonth() + 1).padStart(2, '0') + '-' + String(d.getDate()).padStart(2, '0');24}2526export function initAnalytics() {27 fs.mkdirSync(DATA_DIR, { recursive: true });28 db = new DatabaseSync(DB_PATH);29 db.exec(`30 CREATE TABLE IF NOT EXISTS hits (site TEXT, vid TEXT, ts INTEGER, path TEXT);31 CREATE INDEX IF NOT EXISTS idx_hits_site_ts ON hits(site, ts);32 CREATE TABLE IF NOT EXISTS daily (site TEXT, day TEXT, uniques INTEGER, pv INTEGER, PRIMARY KEY(site, day));33 CREATE TABLE IF NOT EXISTS geo (ip TEXT PRIMARY KEY, label TEXT, country TEXT, ts INTEGER);34 `);35 try { db.exec('ALTER TABLE hits ADD COLUMN ip TEXT'); } catch {}36 rollup();37 setInterval(rollup, 10 * 60 * 1000); // fige les stats quotidiennes38 setInterval(purge, 6 * 60 * 60 * 1000); // purge le brut ancien39 setInterval(resolveGeo, 60 * 1000); // géolocalise les nouvelles IP40 purge(); resolveGeo();41}4243// enregistre un hit (appelé par /collect)44export function recordHit(site, vid, p, ip) {45 if (!db || !site || !vid) return;46 site = String(site).slice(0, 40);47 vid = String(vid).slice(0, 64);48 p = String(p || '/').slice(0, 200);49 ip = (String(ip || '').split(',')[0] || '').trim().slice(0, 45);50 try { db.prepare('INSERT INTO hits (site,vid,ts,path,ip) VALUES (?,?,?,?,?)').run(site, vid, Date.now(), p, ip); } catch {}51}5253// --- géolocalisation des IP (ip-api.com, gratuit, mis en cache) ---54let geoBusy = false;55async function resolveGeo() {56 if (!db || geoBusy) return;57 geoBusy = true;58 try {59 const rows = db.prepare(`SELECT DISTINCT ip FROM hits WHERE ip IS NOT NULL AND ip<>'' AND ip NOT IN (SELECT ip FROM geo) LIMIT 12`).all();60 for (const r of rows) {61 const ip = r.ip;62 if (/^(127\.|10\.|192\.168\.|::1|172\.(1[6-9]|2\d|3[01])\.)/.test(ip) || ip === 'localhost') {63 db.prepare('INSERT OR REPLACE INTO geo (ip,label,country,ts) VALUES (?,?,?,?)').run(ip, 'Local', 'Local', Date.now());64 continue;65 }66 try {67 const res = await fetch('http://ip-api.com/json/' + encodeURIComponent(ip) + '?fields=status,country,countryCode,regionName,city', { signal: AbortSignal.timeout(8000) });68 const d = await res.json();69 let label = 'Inconnu', country = '';70 if (d && d.status === 'success') {71 country = d.country || '';72 label = [d.city, d.regionName].filter(Boolean).join(', ') || d.country || 'Inconnu';73 if (d.countryCode && d.countryCode !== 'CA') label = (d.city ? d.city + ', ' : '') + (d.country || '');74 }75 db.prepare('INSERT OR REPLACE INTO geo (ip,label,country,ts) VALUES (?,?,?,?)').run(ip, label, country, Date.now());76 } catch {77 db.prepare('INSERT OR REPLACE INTO geo (ip,label,country,ts) VALUES (?,?,?,?)').run(ip, 'Inconnu', '', Date.now());78 }79 await new Promise(r => setTimeout(r, 1500)); // respecter la limite ip-api80 }81 } catch {}82 geoBusy = false;83}8485// top emplacements (visiteurs uniques) d'un site86export function analyticsGeo(site, days = 30) {87 try {88 return db.prepare(`89 SELECT g.label label, COUNT(DISTINCT h.vid) c90 FROM hits h JOIN geo g ON g.ip=h.ip91 WHERE h.site=? AND h.ts>=? AND g.label IS NOT NULL AND g.label<>'Local'92 GROUP BY g.label ORDER BY c DESC LIMIT 8`).all(site, Date.now() - days * 864e5);93 } catch { return []; }94}9596function purge() {97 if (!db) return;98 const cutoff = Date.now() - RETENTION_DAYS * 864e5;99 try { db.prepare('DELETE FROM hits WHERE ts < ?').run(cutoff); } catch {}100}101102// fige les uniques/pv par jour dans `daily` (sauvegarde long terme)103function rollup() {104 if (!db) return;105 try {106 const rows = db.prepare(`107 SELECT site, ts, vid FROM hits WHERE ts >= ?`).all(Date.now() - 3 * 864e5);108 const agg = {}; // site|day -> {set, pv}109 for (const r of rows) {110 const k = r.site + '|' + dayKey(r.ts);111 if (!agg[k]) agg[k] = { set: new Set(), pv: 0 };112 agg[k].set.add(r.vid); agg[k].pv++;113 }114 const up = db.prepare('INSERT OR REPLACE INTO daily (site,day,uniques,pv) VALUES (?,?,?,?)');115 for (const k in agg) { const [site, day] = k.split('|'); up.run(site, day, agg[k].set.size, agg[k].pv); }116 } catch {}117}118119function uniquesSince(site, sinceTs) {120 try { return db.prepare('SELECT COUNT(DISTINCT vid) c FROM hits WHERE site=? AND ts>=?').get(site, sinceTs).c; } catch { return 0; }121}122function pvSince(site, sinceTs) {123 try { return db.prepare('SELECT COUNT(*) c FROM hits WHERE site=? AND ts>=?').get(site, sinceTs).c; } catch { return 0; }124}125126export function analyticsSummary(sites) {127 const now = Date.now();128 const startToday = new Date(); startToday.setHours(0, 0, 0, 0);129 const t0 = startToday.getTime();130 const out = [];131 let g = { online: 0, today: 0, d7: 0, pv_today: 0 };132 for (const s of sites) {133 const site = s.app || s;134 const row = {135 site, label: s.label || site, domain: s.domain || '',136 online: uniquesSince(site, now - 5 * 60 * 1000), // uniques dans les 5 dernières min137 today: uniquesSince(site, t0),138 d7: uniquesSince(site, now - 7 * 864e5),139 d30: uniquesSince(site, now - 30 * 864e5),140 pv_today: pvSince(site, t0),141 pv_total: pvSince(site, now - RETENTION_DAYS * 864e5),142 };143 out.push(row);144 g.online += row.online; g.today += row.today; g.d7 += row.d7; g.pv_today += row.pv_today;145 }146 out.sort((a, b) => b.online - a.online || b.today - a.today);147 return { sites: out, global: g, updated: now };148}149150// série quotidienne (uniques) des N derniers jours pour un site151export function analyticsSeries(site, days = 30) {152 const out = [];153 for (let i = days - 1; i >= 0; i--) {154 const d = new Date(); d.setHours(0, 0, 0, 0); d.setDate(d.getDate() - i);155 const key = dayKey(d.getTime());156 let u = 0;157 try { const r = db.prepare('SELECT uniques FROM daily WHERE site=? AND day=?').get(site, key); if (r) u = r.uniques; } catch {}158 // aujourd'hui : recalcule en direct (le rollup peut être en retard)159 if (i === 0) u = Math.max(u, uniquesSince(site, d.getTime()));160 out.push({ day: key, uniques: u });161 }162 return out;163}164