// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
/**
 * Accès à la copie de la base Immo-Ka (data/immoka.db) : annonces réellement
 * à vendre au Québec + table `vp_eval` (estimation du moteur pour chaque
 * annonce, produite par scripts/eval-immoka.ts → build-marche-stats.mjs).
 *
 * Sert l'onglet « À vendre » : recherche/filtres, fiche complète, comparables
 * choisis par l'utilisateur, comparaison avec la mesure Vrai-Prix.
 */
import Database from "better-sqlite3";
import path from "path";

let db: Database.Database | null = null;

/** Filtre « annonce vivante » — identique à celui du pipeline eval-immoka.ts. */
const LIVE =
  "l.status = 'a-vendre' AND l.published = 1 AND l.active = 1 AND l.dup_hidden = 0 AND l.price > 0";

export function getImmoDb(): Database.Database {
  if (!db) {
    const p = process.env.IMMOKA_DB ?? path.join(process.cwd(), "data", "immoka.db");
    db = new Database(p, { fileMustExist: true });
    db.pragma("journal_mode = WAL");
    ensureImmoIndexes(db);
  }
  return db;
}

/**
 * Index et table FTS nécessaires aux requêtes de l'onglet (no-op si présents).
 * Appelé à l'ouverture et par le pipeline de rafraîchissement.
 */
export function ensureImmoIndexes(d: Database.Database): void {
  d.exec(`
    CREATE INDEX IF NOT EXISTS idx_listings_live
      ON listings(status, published, active, dup_hidden, price);
    CREATE INDEX IF NOT EXISTS idx_listings_city_nocase ON listings(city COLLATE NOCASE);
    CREATE INDEX IF NOT EXISTS idx_listings_last_seen ON listings(last_seen);
  `);
  const hasEval = d
    .prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name='vp_eval'")
    .get();
  if (hasEval) {
    d.exec("CREATE INDEX IF NOT EXISTS idx_vp_eval_muni ON vp_eval(municipalite)");
  }
  const hasFts = d
    .prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name='listings_fts'")
    .get();
  if (!hasFts) rebuildListingsFts(d);
}

/** Table FTS5 des annonces vivantes (adresse, ville, secteur, titre, MLS). */
export function rebuildListingsFts(d: Database.Database): void {
  d.exec(`
    DROP TABLE IF EXISTS listings_fts;
    CREATE VIRTUAL TABLE listings_fts USING fts5(
      uid UNINDEXED, address, city, sector, title, mls,
      tokenize = 'unicode61 remove_diacritics 2'
    );
    INSERT INTO listings_fts(uid, address, city, sector, title, mls)
      SELECT l.uid, COALESCE(l.address,''), COALESCE(l.city,''), COALESCE(l.sector,''),
             COALESCE(l.title,''), COALESCE(l.mls,'')
      FROM listings l WHERE ${LIVE};
  `);
}

/* ------------------------------------------------------------------ types */

export interface ListingRow {
  uid: string;
  source: string;
  external_id: string;
  url: string | null;
  title: string | null;
  address: string | null;
  sector: string | null;
  city: string | null;
  region: string | null;
  property_type: string | null;
  price: number;
  price_label: string | null;
  bedrooms: number | null;
  bathrooms: number | null;
  powder_rooms: number | null;
  area_sqft: number | null;
  lot_sqft: number | null;
  year_built: number | null;
  mls: string | null;
  status: string;
  broker_name: string | null;
  agency: string | null;
  description: string | null;
  features: string | null;
  details: string | null;
  images: string | null;
  lat: number | null;
  lng: number | null;
  first_seen: number | null;
  last_seen: number | null;
  updated_at: number | null;
  quality_score: number | null;
}

export interface EvalRow {
  uid: string;
  unit_id: string | null;
  match_m: number | null;
  price: number | null;
  est: number | null;
  low: number | null;
  high: number | null;
  confidence_pct: number | null;
  confidence: string | null;
  model_est: number | null;
  comps_est: number | null;
  model_weight: number | null;
  n_comps: number | null;
  cost_est: number | null;
  role_est: number | null;
  ens_est: number | null;
  valeur_role: number | null;
  type_prop: string | null;
  municipalite: string | null;
  ratio: number | null;
  diff: number | null;
  evaluated_at: string | null;
}

/** Carte d'annonce (liste, cartes, voisins). */
export interface ListingCard {
  uid: string;
  source: string;
  url: string | null;
  address: string | null;
  city: string | null;
  sector: string | null;
  propertyType: string | null;
  group: TypeGroupKey;
  price: number;
  bedrooms: number | null;
  bathrooms: number | null;
  areaSqft: number | null;
  lotSqft: number | null;
  yearBuilt: number | null;
  image: string | null;
  nImages: number;
  lat: number | null;
  lng: number | null;
  firstSeen: number | null;
  est: number | null;
  low: number | null;
  high: number | null;
  confidence: string | null;
  ratio: number | null; // est / prix demandé
  municipalite: string | null;
  distanceM?: number;
}

/* ----------------------------------------------------- groupes de types */
// (module pur src/lib/listing-types.ts — réexporté ici pour le serveur)
import { TYPE_GROUPS, groupToTypeProp, typeGroup, type TypeGroupKey } from "./listing-types";
export { TYPE_GROUPS, groupToTypeProp, typeGroup, type TypeGroupKey };

let typeMap: Map<TypeGroupKey, string[]> | null = null;
/** Types Immo-Ka bruts rattachés à chaque groupe (calculé une fois sur la copie). */
export function rawTypesByGroup(): Map<TypeGroupKey, string[]> {
  if (!typeMap) {
    const rows = getImmoDb()
      .prepare(`SELECT DISTINCT l.property_type AS t FROM listings l WHERE ${LIVE}`)
      .all() as { t: string | null }[];
    typeMap = new Map();
    for (const { t } of rows) {
      const g = typeGroup(t);
      if (!typeMap.has(g)) typeMap.set(g, []);
      typeMap.get(g)!.push(t ?? "");
    }
  }
  return typeMap;
}

/* --------------------------------------------------------------- helpers */

function firstImage(images: string | null): { first: string | null; n: number } {
  if (!images) return { first: null, n: 0 };
  try {
    const arr = JSON.parse(images);
    if (Array.isArray(arr) && arr.length) {
      const urls = arr.filter((x): x is string => typeof x === "string" && /^https?:/.test(x));
      return { first: urls[0] ?? null, n: urls.length };
    }
  } catch {}
  return { first: null, n: 0 };
}

export function parseImages(images: string | null): string[] {
  if (!images) return [];
  try {
    const arr = JSON.parse(images);
    return Array.isArray(arr) ? arr.filter((x): x is string => typeof x === "string" && /^https?:/.test(x)) : [];
  } catch {
    return [];
  }
}

type CardSrc = ListingRow & Partial<EvalRow> & { e_est?: number | null };

function toCard(r: CardSrc & { est?: number | null; low?: number | null; high?: number | null; confidence?: string | null; ratio?: number | null; municipalite?: string | null }): ListingCard {
  const img = firstImage(r.images);
  return {
    uid: r.uid,
    source: r.source,
    url: r.url,
    address: r.address,
    city: r.city,
    sector: r.sector,
    propertyType: r.property_type,
    group: typeGroup(r.property_type),
    price: r.price,
    bedrooms: r.bedrooms,
    bathrooms: r.bathrooms,
    areaSqft: r.area_sqft,
    lotSqft: r.lot_sqft,
    yearBuilt: r.year_built,
    image: img.first,
    nImages: img.n,
    lat: r.lat,
    lng: r.lng,
    firstSeen: r.first_seen,
    est: r.est ?? null,
    low: r.low ?? null,
    high: r.high ?? null,
    confidence: r.confidence ?? null,
    ratio: r.ratio ?? null,
    municipalite: r.municipalite ?? null,
  };
}

const CARD_COLS = `l.uid, l.source, l.url, l.address, l.city, l.sector, l.property_type, l.price,
  l.bedrooms, l.bathrooms, l.area_sqft, l.lot_sqft, l.year_built, l.images, l.lat, l.lng, l.first_seen,
  e.est, e.low, e.high, e.confidence, e.ratio, e.municipalite`;

/* -------------------------------------------------------------- recherche */

export type SortKey = "recent" | "price_asc" | "price_desc" | "deal" | "premium" | "est_desc";

export interface SearchParams {
  q?: string;
  muni?: string;
  group?: TypeGroupKey | "";
  min?: number;
  max?: number;
  beds?: number;
  evalOnly?: boolean;
  sort?: SortKey;
  page?: number;
  per?: number;
}

export interface SearchResult {
  total: number;
  page: number;
  per: number;
  items: ListingCard[];
  facets: {
    groups: { key: TypeGroupKey; n: number }[];
    munis: { name: string; n: number }[];
  };
  summary: { medianPrice: number | null; medianRatio: number | null; nEval: number };
}

function ftsQuery(q: string): string | null {
  const terms = q
    .replace(/[^\p{L}\p{N}\s'-]/gu, " ")
    .trim()
    .split(/\s+/)
    .filter((t) => t.length > 0)
    .map((t) => `"${t.replace(/"/g, "")}"*`);
  return terms.length ? terms.join(" ") : null;
}

export function searchListings(p: SearchParams): SearchResult {
  const d = getImmoDb();
  const per = Math.min(Math.max(p.per ?? 24, 6), 60);
  const page = Math.max(p.page ?? 1, 1);
  const where: string[] = [LIVE];
  const args: unknown[] = [];

  if (p.q && p.q.trim()) {
    const fq = ftsQuery(p.q);
    if (fq) {
      where.push("l.uid IN (SELECT uid FROM listings_fts WHERE listings_fts MATCH ?)");
      args.push(fq);
    }
  }
  if (p.muni) {
    where.push("(e.municipalite = ? COLLATE NOCASE OR l.city = ? COLLATE NOCASE)");
    args.push(p.muni, p.muni);
  }
  if (p.min && p.min > 0) {
    where.push("l.price >= ?");
    args.push(p.min);
  }
  if (p.max && p.max > 0) {
    where.push("l.price <= ?");
    args.push(p.max);
  }
  if (p.beds && p.beds > 0) {
    where.push("l.bedrooms >= ?");
    args.push(p.beds);
  }
  if (p.evalOnly) where.push("e.est IS NOT NULL AND e.est > 0");

  // le filtre de groupe s'applique via la liste des types bruts du groupe
  const groupWhere: string[] = [];
  const groupArgs: unknown[] = [];
  if (p.group) {
    const raws = rawTypesByGroup().get(p.group) ?? [];
    if (raws.length) {
      const hasNull = raws.includes("");
      const nn = raws.filter((r) => r !== "");
      const parts: string[] = [];
      if (nn.length) {
        parts.push(`l.property_type IN (${nn.map(() => "?").join(",")})`);
        groupArgs.push(...nn);
      }
      if (hasNull) parts.push("l.property_type IS NULL OR l.property_type = ''");
      groupWhere.push(`(${parts.join(" OR ")})`);
    } else {
      groupWhere.push("0");
    }
  }

  const base = `FROM listings l LEFT JOIN vp_eval e ON e.uid = l.uid WHERE ${where.join(" AND ")}`;
  const full = groupWhere.length ? `${base} AND ${groupWhere.join(" AND ")}` : base;
  const fullArgs = [...args, ...groupArgs];

  const total = (d.prepare(`SELECT COUNT(*) AS n ${full}`).get(...fullArgs) as { n: number }).n;

  const order: Record<SortKey, string> = {
    recent: "l.first_seen DESC",
    price_asc: "l.price ASC",
    price_desc: "l.price DESC",
    deal: "CASE WHEN e.ratio IS NULL THEN 1 ELSE 0 END, e.ratio DESC", // estimation ≫ prix demandé
    premium: "CASE WHEN e.ratio IS NULL THEN 1 ELSE 0 END, e.ratio ASC",
    est_desc: "CASE WHEN e.est IS NULL THEN 1 ELSE 0 END, e.est DESC",
  };
  const rows = d
    .prepare(`SELECT ${CARD_COLS} ${full} ORDER BY ${order[p.sort ?? "recent"]}, l.uid LIMIT ? OFFSET ?`)
    .all(...fullArgs, per, (page - 1) * per) as CardSrc[];

  // facettes : groupes (sans le filtre de groupe) et municipalités (avec)
  const typeCounts = d
    .prepare(`SELECT l.property_type AS t, COUNT(*) AS n ${base} GROUP BY l.property_type`)
    .all(...args) as { t: string | null; n: number }[];
  const gmap = new Map<TypeGroupKey, number>();
  for (const r of typeCounts) {
    const g = typeGroup(r.t);
    gmap.set(g, (gmap.get(g) ?? 0) + r.n);
  }
  const groups = TYPE_GROUPS.map((g) => ({ key: g.key, n: gmap.get(g.key) ?? 0 })).filter((g) => g.n > 0);

  const munis = d
    .prepare(
      `SELECT COALESCE(e.municipalite, l.city) AS name, COUNT(*) AS n ${full}
       AND COALESCE(e.municipalite, l.city) IS NOT NULL AND COALESCE(e.municipalite, l.city) != ''
       GROUP BY name ORDER BY n DESC LIMIT 60`
    )
    .all(...fullArgs) as { name: string; n: number }[];

  const summary = d.prepare(`SELECT COUNT(e.est) AS nEval ${full}`).get(...fullArgs) as { nEval: number };

  return {
    total,
    page,
    per,
    items: rows.map(toCard),
    facets: { groups, munis },
    summary: {
      medianPrice: medianOf(d, `SELECT l.price AS v ${full}`, fullArgs, total),
      medianRatio: medianOf(d, `SELECT e.ratio AS v ${full} AND e.ratio IS NOT NULL`, fullArgs, summary.nEval),
      nEval: summary.nEval,
    },
  };
}

/** Médiane par OFFSET (évite de charger la colonne entière). */
function medianOf(d: Database.Database, sql: string, args: unknown[], n: number): number | null {
  if (!n) return null;
  const row = d
    .prepare(`${sql} ORDER BY v LIMIT 1 OFFSET ?`)
    .get(...args, Math.floor(n / 2)) as { v: number } | undefined;
  return row?.v ?? null;
}

/* ------------------------------------------------------------------ fiche */

export interface PricePoint {
  ts: number;
  price: number;
}

export interface ListingDetail {
  listing: ListingRow;
  images: string[];
  features: string[];
  details: Record<string, unknown>;
  priceLog: PricePoint[];
  eval: EvalRow | null;
  group: TypeGroupKey;
}

export function getListing(uid: string): ListingDetail | null {
  const d = getImmoDb();
  const l = d.prepare("SELECT * FROM listings l WHERE l.uid = ?").get(uid) as ListingRow | undefined;
  if (!l) return null;
  const ev = (d.prepare("SELECT * FROM vp_eval WHERE uid = ?").get(uid) as EvalRow | undefined) ?? null;
  const priceLog = d
    .prepare("SELECT ts, price FROM price_log WHERE uid = ? AND price IS NOT NULL ORDER BY ts")
    .all(uid) as PricePoint[];
  let features: string[] = [];
  try {
    const f = l.features ? JSON.parse(l.features) : [];
    features = Array.isArray(f) ? f.filter((x): x is string => typeof x === "string") : [];
  } catch {}
  let details: Record<string, unknown> = {};
  try {
    const dd = l.details ? JSON.parse(l.details) : {};
    if (dd && typeof dd === "object" && !Array.isArray(dd)) details = dd as Record<string, unknown>;
  } catch {}
  return { listing: l, images: parseImages(l.images), features, details, priceLog, eval: ev, group: typeGroup(l.property_type) };
}

/** Annonces vivantes autour d'un point (même groupe optionnel), triées par distance. */
export function listingsNear(
  lat: number,
  lng: number,
  radiusKm: number,
  opts: { group?: TypeGroupKey; excludeUid?: string; limit?: number } = {}
): ListingCard[] {
  const d = getImmoDb();
  const halfLat = radiusKm / 111;
  const halfLng = radiusKm / (111 * Math.cos((lat * Math.PI) / 180));
  const rows = d
    .prepare(
      `SELECT ${CARD_COLS} FROM listings l LEFT JOIN vp_eval e ON e.uid = l.uid
       WHERE ${LIVE} AND l.lat BETWEEN ? AND ? AND l.lng BETWEEN ? AND ?
       ORDER BY (l.lat - ?) * (l.lat - ?) + ${Math.cos((lat * Math.PI) / 180) ** 2} * (l.lng - ?) * (l.lng - ?)
       LIMIT ?`
    )
    .all(lat - halfLat, lat + halfLat, lng - halfLng, lng + halfLng, lat, lat, lng, lng, (opts.limit ?? 40) * 4) as CardSrc[];
  const out: ListingCard[] = [];
  for (const r of rows) {
    if (opts.excludeUid && r.uid === opts.excludeUid) continue;
    const c = toCard(r);
    if (opts.group && c.group !== opts.group) continue;
    if (c.lat == null || c.lng == null) continue;
    c.distanceM = Math.round(haversine(lat, lng, c.lat, c.lng));
    if (c.distanceM > radiusKm * 1000) continue;
    out.push(c);
    if (out.length >= (opts.limit ?? 40)) break;
  }
  return out;
}

function haversine(lat1: number, lng1: number, lat2: number, lng2: number): number {
  const R = 6371000;
  const toRad = (x: number) => (x * Math.PI) / 180;
  const dLat = toRad(lat2 - lat1);
  const dLng = toRad(lng2 - lng1);
  const a = Math.sin(dLat / 2) ** 2 + Math.cos(toRad(lat1)) * Math.cos(toRad(lat2)) * Math.sin(dLng / 2) ** 2;
  return 2 * R * Math.asin(Math.sqrt(a));
}

/* ------------------------------------------------------- vue d'ensemble */

export interface AVendreOverview {
  total: number;
  evaluated: number;
  medianPrice: number | null;
  medianRatio: number | null;
  copiedAt: string | null;
  groups: { key: TypeGroupKey; n: number }[];
  topMunis: { name: string; n: number }[];
}

let overviewCache: { at: number; v: AVendreOverview } | null = null;
export function avendreOverview(): AVendreOverview {
  if (overviewCache && Date.now() - overviewCache.at < 10 * 60 * 1000) return overviewCache.v;
  const d = getImmoDb();
  const base = `FROM listings l LEFT JOIN vp_eval e ON e.uid = l.uid WHERE ${LIVE}`;
  const tot = d.prepare(`SELECT COUNT(*) AS n, COUNT(e.est) AS ne ${base}`).get() as { n: number; ne: number };
  const typeCounts = d.prepare(`SELECT l.property_type AS t, COUNT(*) AS n ${base} GROUP BY 1`).all() as {
    t: string | null;
    n: number;
  }[];
  const gmap = new Map<TypeGroupKey, number>();
  for (const r of typeCounts) gmap.set(typeGroup(r.t), (gmap.get(typeGroup(r.t)) ?? 0) + r.n);
  const evAt = d.prepare("SELECT MAX(evaluated_at) AS at FROM vp_eval").get() as { at: string | null } | undefined;
  const topMunis = d
    .prepare(
      `SELECT COALESCE(e.municipalite, l.city) AS name, COUNT(*) AS n ${base}
       AND COALESCE(e.municipalite, l.city) != '' GROUP BY name ORDER BY n DESC LIMIT 12`
    )
    .all() as { name: string; n: number }[];
  const v: AVendreOverview = {
    total: tot.n,
    evaluated: tot.ne,
    medianPrice: medianOf(d, `SELECT l.price AS v ${base}`, [], tot.n),
    medianRatio: medianOf(d, `SELECT e.ratio AS v ${base} AND e.ratio IS NOT NULL`, [], tot.ne),
    copiedAt: evAt?.at ?? null,
    groups: TYPE_GROUPS.map((g) => ({ key: g.key, n: gmap.get(g.key) ?? 0 })).filter((g) => g.n > 0),
    topMunis,
  };
  overviewCache = { at: Date.now(), v };
  return v;
}