// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
/**
 * scripts/build-stats-v2.mjs — agrégats v2 du module Stats commun Groupe KA.
 * Lit data/vraiprix.db (rôle d'évaluation + corpus de ventes + indice marché)
 * et écrit src/data/stats-v2.json : tout ce que /api/stats/dashboard v2 sert
 * en plus des agrégats historiques de src/data/stats.json.
 * Données 100 % réelles — aucune valeur inventée. À relancer quand la DB change :
 *   node scripts/build-stats-v2.mjs
 */
import Database from "better-sqlite3";
import { writeFileSync } from "fs";
import path from "path";

const db = new Database(path.join(process.cwd(), "data", "vraiprix.db"), { readonly: true });
const out = {};
out.generated = new Date().toISOString();

/* ---------------- unités (rôle d'évaluation, millésime 2026) ---------------- */
console.time("units");
out.units = db.prepare(`
  SELECT COUNT(*) AS total,
         SUM(lat IS NOT NULL AND lng IS NOT NULL AND lat != 0) AS geoloc,
         SUM(valeur_role IS NOT NULL AND valeur_role > 0) AS with_role,
         SUM(est_2026 IS NOT NULL AND est_2026 > 0) AS with_est2026,
         SUM(annee_construction IS NOT NULL AND annee_construction > 1500) AS with_year,
         SUM(aire_etages_m2 IS NOT NULL AND aire_etages_m2 > 0) AS with_area,
         CAST(AVG(CASE WHEN est_2026 > 0 THEN est_2026 END) AS INTEGER) AS avg_est2026,
         CAST(AVG(CASE WHEN valeur_role > 0 THEN valeur_role END) AS INTEGER) AS avg_role,
         CAST(SUM(CASE WHEN valeur_role > 0 THEN valeur_role ELSE 0 END) AS INTEGER) AS sum_role,
         SUM(COALESCE(nb_logements, 0)) AS logements
  FROM units`).get();
console.timeEnd("units");

/* ------------- distribution des valeurs estimées 2026 (histogramme) ------------- */
console.time("bins");
const BINS = [
  [0, 100e3, "< 100 k$"], [100e3, 200e3, "100-200 k$"], [200e3, 300e3, "200-300 k$"],
  [300e3, 400e3, "300-400 k$"], [400e3, 500e3, "400-500 k$"], [500e3, 750e3, "500-750 k$"],
  [750e3, 1e6, "750 k-1 M$"], [1e6, 2e6, "1-2 M$"], [2e6, 5e6, "2-5 M$"], [5e6, Infinity, "5 M$ +"],
];
const caseExpr = BINS.map(([lo, hi], i) =>
  hi === Infinity ? `WHEN est_2026 >= ${lo} THEN ${i}` : `WHEN est_2026 >= ${lo} AND est_2026 < ${hi} THEN ${i}`
).join(" ");
const binRows = db.prepare(`
  SELECT CASE ${caseExpr} END AS b, COUNT(*) AS n
  FROM units WHERE est_2026 > 0 GROUP BY b ORDER BY b`).all();
out.valeur_bins = BINS.map(([, , label], i) => ({
  label, value: binRows.find((r) => r.b === i)?.n ?? 0,
}));
console.timeEnd("bins");

/* ------------- parc par tranche d'année de construction ------------- */
console.time("construction");
const ERAS = [
  [0, 1900, "Avant 1900"], [1900, 1946, "1900-1945"], [1946, 1961, "1946-1960"],
  [1961, 1976, "1961-1975"], [1976, 1991, "1976-1990"], [1991, 2006, "1991-2005"],
  [2006, 2016, "2006-2015"], [2016, 3000, "2016 +"],
];
const eraExpr = ERAS.map(([lo, hi], i) => `WHEN annee_construction >= ${lo} AND annee_construction < ${hi} THEN ${i}`).join(" ");
const eraRows = db.prepare(`
  SELECT CASE ${eraExpr} END AS b, COUNT(*) AS n,
         CAST(SUM(CASE WHEN est_2026 > 0 THEN est_2026 ELSE 0 END) AS INTEGER) AS total,
         CAST(AVG(CASE WHEN est_2026 > 0 THEN est_2026 END) AS INTEGER) AS moyenne
  FROM units WHERE annee_construction > 1500 GROUP BY b ORDER BY b`).all();
out.construction = ERAS.map(([, , label], i) => {
  const r = eraRows.find((x) => x.b === i);
  return { label, n: r?.n ?? 0, total: r?.total ?? 0, moyenne: r?.moyenne ?? null };
});
console.timeEnd("construction");

/* ---------------- records réels du rôle ---------------- */
console.time("records");
out.rec = {};
out.rec.max_role = db.prepare(`
  SELECT municipalite, valeur_role AS v, cubf_libelle FROM units
  WHERE valeur_role IS NOT NULL ORDER BY valeur_role DESC LIMIT 1`).get();
out.rec.max_est = db.prepare(`
  SELECT municipalite, est_2026 AS v FROM units
  WHERE est_2026 IS NOT NULL ORDER BY est_2026 DESC LIMIT 1`).get();
out.rec.max_terrain = db.prepare(`
  SELECT municipalite, superficie_terrain_m2 AS m2 FROM units
  WHERE superficie_terrain_m2 IS NOT NULL ORDER BY superficie_terrain_m2 DESC LIMIT 1`).get();
out.rec.max_logements = db.prepare(`
  SELECT municipalite, nb_logements AS n FROM units
  WHERE nb_logements IS NOT NULL ORDER BY nb_logements DESC LIMIT 1`).get();
const oldest = db.prepare(`
  SELECT MIN(annee_construction) AS y FROM units WHERE annee_construction > 1600`).get();
out.rec.oldest = {
  y: oldest.y,
  n: db.prepare("SELECT COUNT(*) AS n FROM units WHERE annee_construction = ?").get(oldest.y).n,
};
console.timeEnd("records");

/* ---------------- transactions (corpus de ventes réelles 2021-2026) ---------------- */
console.time("tx");
const TX_TYPE_FR = { unifamilial: "Unifamiliale", condo: "Condo", plex: "Plex", "indéterminé": "Indéterminé" };
const txTypeKeys = ["unifamilial", "condo", "plex", "indéterminé"];
const monthly = new Map(); // m -> { n, total, amounts[], byType: [4] }
for (const r of db.prepare("SELECT substr(date,1,7) AS m, amount, property_type FROM transactions").iterate()) {
  let e = monthly.get(r.m);
  if (!e) { e = { n: 0, total: 0, amounts: [], byType: [0, 0, 0, 0] }; monthly.set(r.m, e); }
  e.n++; e.total += r.amount; e.amounts.push(r.amount);
  const ti = txTypeKeys.indexOf(r.property_type ?? "indéterminé");
  e.byType[ti === -1 ? 3 : ti]++;
}
const months = [...monthly.keys()].sort();
const median = (a) => { const s = [...a].sort((x, y) => x - y); return s[Math.floor(s.length / 2)]; };
out.tx = {
  monthly: months.map((m) => {
    const e = monthly.get(m);
    return { m, n: e.n, total: Math.round(e.total), median: Math.round(median(e.amounts)) };
  }),
  monthly_by_type: {
    keys: txTypeKeys.map((k) => TX_TYPE_FR[k]),
    points: months.map((m) => ({ t: m, values: monthly.get(m).byType })),
  },
};
// bins des montants de vente
const TXB = [
  [0, 200e3, "< 200 k$"], [200e3, 300e3, "200-300 k$"], [300e3, 400e3, "300-400 k$"],
  [400e3, 500e3, "400-500 k$"], [500e3, 700e3, "500-700 k$"], [700e3, 1e6, "700 k-1 M$"],
  [1e6, 2e6, "1-2 M$"], [2e6, Infinity, "2 M$ +"],
];
const txCase = TXB.map(([lo, hi], i) =>
  hi === Infinity ? `WHEN amount >= ${lo} THEN ${i}` : `WHEN amount >= ${lo} AND amount < ${hi} THEN ${i}`).join(" ");
const txBinRows = db.prepare(`SELECT CASE ${txCase} END AS b, COUNT(*) AS n FROM transactions GROUP BY b`).all();
out.tx.amount_bins = TXB.map(([, , label], i) => ({ label, value: txBinRows.find((r) => r.b === i)?.n ?? 0 }));
// activité quotidienne (26 dernières semaines du corpus) pour le calendrier
const maxDate = db.prepare("SELECT MAX(date) AS d FROM transactions").get().d;
const since = new Date(maxDate + "T12:00:00");
since.setDate(since.getDate() - 26 * 7);
out.tx.daily = db.prepare(`
  SELECT date, COUNT(*) AS n FROM transactions WHERE date >= ? GROUP BY date ORDER BY date`)
  .all(since.toISOString().slice(0, 10)).map((r) => ({ date: r.date, n: r.n }));
out.tx.max_date = maxDate;
out.tx.max = db.prepare("SELECT amount, city, date FROM transactions ORDER BY amount DESC LIMIT 1").get();
const best = out.tx.monthly.reduce((a, b) => (b.n > a.n ? b : a));
out.tx.best_month = { m: best.m, n: best.n };
// top villes par nombre de ventes
out.tx.top_villes = db.prepare(`
  SELECT city, COUNT(*) AS n, CAST(AVG(amount) AS INTEGER) AS moyenne
  FROM transactions WHERE city IS NOT NULL GROUP BY city ORDER BY n DESC LIMIT 20`).all();
console.timeEnd("tx");

/* ---------------- indice de marché mensuel par type ---------------- */
console.time("market");
const MI_TYPE_FR = { unifamilial: "Unifamiliale", condo: "Condo", plex: "Plex", "indéterminé": "Ensemble" };
const miTypes = db.prepare("SELECT DISTINCT type_prop FROM market_index ORDER BY type_prop").all().map((r) => r.type_prop);
out.market = {
  idx_by_type: miTypes.map((t) => ({
    label: MI_TYPE_FR[t] ?? t,
    points: db.prepare("SELECT month, idx FROM market_index WHERE type_prop = ? ORDER BY month").all(t)
      .map((r) => ({ t: r.month, v: Math.round(r.idx * 1000) / 1000 })),
  })),
  ppm2_by_type: miTypes.filter((t) => t !== "indéterminé").map((t) => ({
    label: MI_TYPE_FR[t] ?? t,
    points: db.prepare("SELECT month, ppm2 FROM market_index WHERE type_prop = ? AND ppm2 IS NOT NULL ORDER BY month").all(t)
      .map((r) => ({ t: r.month, v: Math.round(r.ppm2) })),
  })),
};
console.timeEnd("market");

writeFileSync(path.join(process.cwd(), "src", "data", "stats-v2.json"), JSON.stringify(out));
console.log("→ src/data/stats-v2.json écrit,", JSON.stringify(out).length, "octets");