// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
// Données agrégées par municipalité pour les pages programmatiques SEO.
// Les agrégats ne changent qu'au rebuild de la DB : cache mémoire par processus.

import { getDb } from "./db";
import { slugify } from "./seo";

export interface MunSummary {
  nom: string;
  slug: string;
  n: number;
  lat: number;
  lng: number;
}

export interface TypeStats {
  type: string;
  n: number;
  total2026: number;
  mediane2026: number | null;
}

export interface MunStats {
  nom: string;
  slug: string;
  n: number;
  total2026: number;
  total2021: number;
  mediane2026: number | null;
  croissancePct: number | null;
  parType: TypeStats[];
}

export interface SampleUnit {
  id_provinc: string;
  adresse: string | null;
  apt: string | null;
  type_prop: string;
  est_2026: number | null;
  annee_construction: number | null;
  aire_etages_m2: number | null;
}

let munList: MunSummary[] | null = null;
let munBySlugMap: Map<string, MunSummary> | null = null;
const statsCache = new Map<string, MunStats>();

/** Toutes les municipalités (nom, slug, nb d'unités, centroïde) — triées par taille. */
export function allMunicipalities(): MunSummary[] {
  if (!munList) {
    const rows = getDb()
      .prepare(
        `SELECT municipalite AS nom, COUNT(*) AS n,
                AVG(lat) AS lat, AVG(lng) AS lng
         FROM units WHERE municipalite IS NOT NULL AND municipalite != ''
         GROUP BY municipalite ORDER BY n DESC`
      )
      .all() as { nom: string; n: number; lat: number; lng: number }[];
    munList = rows.map((r) => ({ ...r, slug: slugify(r.nom) }));
    munBySlugMap = new Map(munList.map((m) => [m.slug, m]));
  }
  return munList;
}

export function munBySlug(slug: string): MunSummary | undefined {
  allMunicipalities();
  return munBySlugMap!.get(slug);
}

function median2026(nom: string, type?: string): number | null {
  const db = getDb();
  const where = type
    ? "municipalite = ? AND type_prop = ?"
    : "municipalite = ?";
  const args: (string | number)[] = type ? [nom, type] : [nom];
  const cnt = (
    db.prepare(`SELECT COUNT(*) AS c FROM units WHERE ${where} AND est_2026 IS NOT NULL`).get(...args) as { c: number }
  ).c;
  if (!cnt) return null;
  const row = db
    .prepare(
      `SELECT est_2026 AS v FROM units WHERE ${where} AND est_2026 IS NOT NULL
       ORDER BY est_2026 LIMIT 1 OFFSET ?`
    )
    .get(...args, Math.floor(cnt / 2)) as { v: number } | undefined;
  return row?.v ?? null;
}

/** Statistiques complètes d'une municipalité (cache mémoire). */
export function munStats(nom: string): MunStats | null {
  const cached = statsCache.get(nom);
  if (cached) return cached;
  const db = getDb();
  const base = db
    .prepare(
      `SELECT COUNT(*) AS n, SUM(est_2026) AS t26, SUM(est_2021) AS t21
       FROM units WHERE municipalite = ?`
    )
    .get(nom) as { n: number; t26: number | null; t21: number | null };
  if (!base.n) return null;
  const types = db
    .prepare(
      `SELECT type_prop AS type, COUNT(*) AS n, SUM(est_2026) AS total2026
       FROM units WHERE municipalite = ?
       GROUP BY type_prop ORDER BY n DESC`
    )
    .all(nom) as { type: string; n: number; total2026: number }[];
  const stats: MunStats = {
    nom,
    slug: slugify(nom),
    n: base.n,
    total2026: base.t26 ?? 0,
    total2021: base.t21 ?? 0,
    mediane2026: median2026(nom),
    croissancePct:
      base.t21 && base.t26 ? ((base.t26 - base.t21) / base.t21) * 100 : null,
    parType: types.map((t) => ({
      ...t,
      mediane2026: median2026(nom, t.type),
    })),
  };
  statsCache.set(nom, stats);
  return stats;
}

/** Échantillon de propriétés adressées (les plus valorisées) pour le maillage interne. */
export function sampleUnits(
  nom: string,
  type: string | null,
  limit = 24
): SampleUnit[] {
  const where = type
    ? "municipalite = ? AND type_prop = ?"
    : "municipalite = ?";
  const args: string[] = type ? [nom, type] : [nom];
  return getDb()
    .prepare(
      `SELECT id_provinc, adresse, apt, type_prop, est_2026,
              annee_construction, aire_etages_m2
       FROM units
       WHERE ${where} AND adresse IS NOT NULL AND est_2026 IS NOT NULL
       ORDER BY est_2026 DESC LIMIT ?`
    )
    .all(...args, limit) as SampleUnit[];
}

let typePairs: { nom: string; type: string }[] | null = null;

/** Paires municipalité × type existantes (pour le sitemap des pages types). */
export function allMunTypePairs(): { nom: string; type: string }[] {
  if (!typePairs) {
    typePairs = getDb()
      .prepare(
        `SELECT municipalite AS nom, type_prop AS type
         FROM units WHERE municipalite IS NOT NULL AND municipalite != ''
         GROUP BY municipalite, type_prop`
      )
      .all() as { nom: string; type: string }[];
  }
  return typePairs;
}

/** Les municipalités les plus proches du centroïde (maillage entre pages voisines). */
export function neighborMunicipalities(nom: string, k = 10): MunSummary[] {
  const all = allMunicipalities();
  const me = all.find((m) => m.nom === nom);
  if (!me) return [];
  return all
    .filter((m) => m.nom !== nom)
    .map((m) => ({
      m,
      d: (m.lat - me.lat) ** 2 + 0.49 * (m.lng - me.lng) ** 2,
    }))
    .sort((a, b) => a.d - b.d)
    .slice(0, k)
    .map((x) => x.m);
}