// UQO Éval — laboratoire pédagogique d'évaluation immobilière
import Database from "better-sqlite3";
import path from "path";

let db: Database.Database | null = null;

export function getDb(): Database.Database {
  if (!db) {
    const p =
      process.env.VRAIPRIX_DB ?? path.join(process.cwd(), "data", "vraiprix.db");
    db = new Database(p, { fileMustExist: true });
    db.pragma("journal_mode = WAL");
    // Index requis par les requêtes ci-dessous (COLLATE NOCASE : l'index BINARY
    // idx_units_mun est inutilisable → scan de 3,7 M de lignes qui gelait
    // l'event loop sous trafic, panne du 2026-08-25). No-op si déjà présents.
    db.exec(`
      CREATE INDEX IF NOT EXISTS idx_units_mun_nocase_geo
        ON units(municipalite COLLATE NOCASE, lat, lng);
      CREATE INDEX IF NOT EXISTS idx_units_mun_nocase_est
        ON units(municipalite COLLATE NOCASE, est_2026);
      CREATE INDEX IF NOT EXISTS idx_tx_recent
        ON transactions(date DESC, amount DESC)
        WHERE id_provinc IS NOT NULL AND street IS NOT NULL AND amount >= 50000;
    `);
  }
  return db;
}

export interface UnitRow {
  id_provinc: string;
  adresse: string | null;
  apt: string | null;
  municipalite: string | null;
  arrond: string | null;
  code_mun: string | null;
  lat: number;
  lng: number;
  cubf: number | null;
  cubf_libelle: string | null;
  type_prop: string;
  annee_construction: number | null;
  annee_estimee: string | null;
  aire_etages_m2: number | null;
  superficie_terrain_m2: number | null;
  front_terrain_m: number | null;
  nb_etages: number | null;
  nb_logements: number | null;
  nb_locaux_non_resid: number | null;
  nb_chambres_locatives: number | null;
  lien_physique: string | null;
  genre_construction: string | null;
  matricule: string | null;
  unite_voisinage: string | null;
  n_adresses: number | null;
  dat_cond_marche: string | null;
  valeur_terrain: number | null;
  valeur_batiment: number | null;
  valeur_role: number | null;
  valeur_anterieure: number | null;
  est_2021: number | null;
  est_2022: number | null;
  est_2023: number | null;
  est_2024: number | null;
  est_2025: number | null;
  est_2026: number | null;
  p10: number | null;
  p90: number | null;
  est_hedo: number | null;
}

export interface TxRow {
  id: string;
  date: string;
  amount: number;
  street: string | null;
  city: string | null;
  lat: number;
  lng: number;
  property_type: string | null;
  year_built: number | null;
  floor_area: number | null;
  building_type: string | null;
  id_provinc: string | null;
  valeur_role: number | null;
  land_area: number | null;
}

export function getUnit(id: string): UnitRow | undefined {
  return getDb()
    .prepare("SELECT * FROM units WHERE id_provinc = ?")
    .get(id) as UnitRow | undefined;
}

/** Candidats comparables dans une boîte englobante autour du point. */
export function getCandidates(
  lat: number,
  lng: number,
  halfDeg: number,
  sinceDate: string,
  limit = 500
): TxRow[] {
  return getDb()
    .prepare(
      `SELECT * FROM transactions
       WHERE lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?
         AND date >= ? AND amount >= 50000
       LIMIT ?`
    )
    .all(lat - halfDeg, lat + halfDeg, lng - halfDeg, lng + halfDeg, sinceDate, limit) as TxRow[];
}

export function getMarketIndex(typeProp: string): { month: string; idx: number }[] {
  return getDb()
    .prepare(
      "SELECT month, idx FROM market_index WHERE type_prop = ? ORDER BY month"
    )
    .all(typeProp) as { month: string; idx: number }[];
}

export function searchUnits(q: string, limit = 8): (UnitRow & { score: number })[] {
  const cleaned = q
    .replace(/[^\p{L}\p{N}\s'-]/gu, " ")
    .trim()
    .split(/\s+/)
    .filter((t) => t.length > 0)
    .map((t) => `"${t}"*`)
    .join(" ");
  if (!cleaned) return [];
  return getDb()
    .prepare(
      `SELECT u.*, bm25(units_fts) AS score
       FROM units_fts JOIN units u ON u.rowid = units_fts.rowid
       WHERE units_fts MATCH ?
       ORDER BY score LIMIT ?`
    )
    .all(cleaned, limit) as (UnitRow & { score: number })[];
}

export interface NearbyUnitRow {
  id_provinc: string;
  adresse: string | null;
  apt: string | null;
  municipalite: string | null;
  type_prop: string;
  lat: number;
  lng: number;
  est_2026: number | null;
}

/** Unités d'évaluation dans une boîte englobante (recherche par carte). */
export function unitsNear(
  lat: number,
  lng: number,
  halfLat: number,
  halfLng: number,
  limit = 300
): NearbyUnitRow[] {
  // ORDER BY distance au centre : sans tri, l'index (lat, lng) renverrait
  // les N unités les plus au sud de la boîte (bande horizontale).
  return getDb()
    .prepare(
      `SELECT id_provinc, adresse, apt, municipalite, type_prop, lat, lng, est_2026
       FROM units
       WHERE lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?
       ORDER BY (lat - ?) * (lat - ?) + 0.49 * (lng - ?) * (lng - ?)
       LIMIT ?`
    )
    .all(
      lat - halfLat,
      lat + halfLat,
      lng - halfLng,
      lng + halfLng,
      lat,
      lat,
      lng,
      lng,
      limit
    ) as NearbyUnitRow[];
}

export function municipalityCenter(
  name: string
): { lat: number; lng: number; n: number } | undefined {
  return getDb()
    .prepare(
      `SELECT AVG(lat) AS lat, AVG(lng) AS lng, COUNT(*) AS n
       FROM units WHERE municipalite = ? COLLATE NOCASE`
    )
    .get(name) as { lat: number; lng: number; n: number } | undefined;
}

/** Rang de la valeur dans la municipalité (percentile sur est_2026). */
export function municipalityPercentile(
  municipalite: string,
  value: number
): { below: number; total: number } | null {
  const row = getDb()
    .prepare(
      `SELECT COUNT(*) AS total,
              SUM(CASE WHEN est_2026 < ? THEN 1 ELSE 0 END) AS below
       FROM units
       WHERE municipalite = ? COLLATE NOCASE
         AND est_2026 IS NOT NULL AND est_2026 > 0`
    )
    .get(value, municipalite) as { total: number; below: number | null } | undefined;
  if (!row || row.total < 30) return null;
  return { below: row.below ?? 0, total: row.total };
}

export interface RecentSaleRow {
  id: string;
  date: string;
  amount: number;
  street: string | null;
  city: string | null;
  property_type: string | null;
  id_provinc: string | null;
  valeur_role: number | null;
}

/** Dernières ventes publiées appariées à une unité (fil « ventes récentes »). */
export function recentSales(limit = 6): RecentSaleRow[] {
  return getDb()
    .prepare(
      `SELECT id, date, amount, street, city, property_type, id_provinc, valeur_role
       FROM transactions
       WHERE id_provinc IS NOT NULL AND street IS NOT NULL AND amount >= 50000
       ORDER BY date DESC, amount DESC
       LIMIT ?`
    )
    .all(limit) as RecentSaleRow[];
}

export function salesCountSince(sinceDate: string): number {
  const row = getDb()
    .prepare("SELECT COUNT(*) AS n FROM transactions WHERE date >= ?")
    .get(sinceDate) as { n: number };
  return row.n;
}

export function saveLead(email: string, unitId: string | null, estimate: number | null): void {
  getDb()
    .prepare("INSERT INTO leads (email, unit_id, estimate) VALUES (?, ?, ?)")
    .run(email, unitId, estimate);
}