// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
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.VALOPLEX_DB ?? path.join(process.cwd(), "data", "valoplex.db");
    db = new Database(p, { fileMustExist: true });
    db.pragma("journal_mode = WAL");
  }
  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;
  adresses_json: string | 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;
  portes: 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 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;
}

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);
}