// UQO Éval — modélisation hédonique : constitution de l'échantillon (ventes réelles ou annonces).
import { getDb, getMarketIndex, municipalityCenter, type UnitRow } from "../db";
import { timeFactor } from "../engine";
import { indexType } from "../estimator";
import { getImmoDb } from "../immoka";
import { typeGroup } from "../listing-types";
import { LIMITS, type HedonicSpec } from "./spec";

export interface Obs {
  id: string;
  price: number;
  date: string; // YYYY-MM-DD
  year: number;
  month: string; // YYYY-MM
  quarter: string; // YYYY-Qn
  lat: number;
  lng: number;
  street: string | null;
  city: string | null;
  aire: number | null;
  terrain: number | null;
  annee: number | null;
  nb_logements: number | null;
  nb_etages: number | null;
  type: string; // unifamilial | condo | plex | indéterminé
  genre: string | null;
  lien: string | null;
  muni: string | null;
  arrond: string | null;
  voisinage: string | null;
  valeur_role: number | null;
  bedrooms: number | null;
  bathrooms: number | null;
  lab: number | null; // estimation du laboratoire comparable au prix (même date)
}

export interface Sample {
  obs: Obs[];
  available: number; // observations répondant aux filtres avant échantillonnage
  trimmed: number;
  center: { lat: number; lng: number } | null;
}

const SQFT = 0.092903;

function timeKeys(date: string) {
  const year = Number(date.slice(0, 4));
  const m = Number(date.slice(5, 7));
  return { year, month: date.slice(0, 7), quarter: `${year}-T${Math.floor((m - 1) / 3) + 1}` };
}

function haversineKm(lat1: number, lng1: number, lat2: number, lng2: number): number {
  const R = 6371;
  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));
}

function areaClause(spec: HedonicSpec, latCol: string, lngCol: string, muniCol: string, cityCol?: string) {
  const where: string[] = [];
  const args: unknown[] = [];
  let center: { lat: number; lng: number } | null = null;
  if (spec.area.mode === "muni" && spec.area.muni) {
    if (cityCol) {
      where.push(`(${muniCol} = ? COLLATE NOCASE OR ${cityCol} = ? COLLATE NOCASE)`);
      args.push(spec.area.muni, spec.area.muni);
    } else {
      where.push(`${muniCol} = ? COLLATE NOCASE`);
      args.push(spec.area.muni);
    }
    const c = municipalityCenter(spec.area.muni);
    if (c && c.n) center = { lat: c.lat, lng: c.lng };
  } else if (spec.area.mode === "radius" && spec.area.muni) {
    const c = municipalityCenter(spec.area.muni);
    if (!c || !c.n) throw new Error(`municipalité inconnue : ${spec.area.muni}`);
    center = { lat: c.lat, lng: c.lng };
    const r = Math.min(Math.max(spec.area.radiusKm ?? 10, 1), 150);
    const dLat = r / 111;
    const dLng = r / (111 * Math.cos((c.lat * Math.PI) / 180));
    where.push(`${latCol} BETWEEN ? AND ? AND ${lngCol} BETWEEN ? AND ?`);
    args.push(c.lat - dLat, c.lat + dLat, c.lng - dLng, c.lng + dLng);
  }
  return { where, args, center };
}

const clampN = (n: number) => Math.min(Math.max(Math.round(n) || 1000, LIMITS.minN), LIMITS.maxN);

/* --------------------------------- ventes --------------------------------- */
interface SaleRow {
  id: string;
  date: string;
  price: number;
  lat: number;
  lng: number;
  ttype: string | null;
  street: string | null;
  city: string | null;
  aire: number | null;
  terrain: number | null;
  annee: number | null;
  nb_logements: number | null;
  nb_etages: number | null;
  genre: string | null;
  lien: string | null;
  muni: string | null;
  arrond: string | null;
  voisinage: string | null;
  valeur_role: number | null;
  est_2026: number | null;
}

function sampleSales(spec: HedonicSpec): Sample {
  const d = getDb();
  const n = clampN(spec.maxN);
  const where = ["t.amount BETWEEN ? AND ?", "t.lat IS NOT NULL", "t.lng IS NOT NULL"];
  const args: unknown[] = [spec.price.min, spec.price.max];
  const from = /^\d{4}-\d{2}$/.test(spec.period.from) ? `${spec.period.from}-01` : "2021-01-01";
  const to = /^\d{4}-\d{2}$/.test(spec.period.to) ? `${spec.period.to}-31` : "2099-12-31";
  where.push("t.date BETWEEN ? AND ?");
  args.push(from, to);
  if (spec.types.length) {
    where.push(`t.property_type IN (${spec.types.map(() => "?").join(",")})`);
    args.push(...spec.types);
  }
  const ac = areaClause(spec, "t.lat", "t.lng", "u.municipalite");
  where.push(...ac.where);
  args.push(...ac.args);
  const base = `FROM transactions t JOIN units u ON u.id_provinc = t.id_provinc WHERE ${where.join(" AND ")}`;
  const available = (d.prepare(`SELECT COUNT(*) AS n ${base}`).get(...args) as { n: number }).n;
  // ordre pseudo-aléatoire déterministe (graine) → échantillon reproductible
  const seed = Math.abs(Math.round(spec.seed)) % 1000003;
  const over = spec.area.mode === "radius" ? 3 : 1.2; // la bbox du rayon est filtrée ensuite
  const rows = d
    .prepare(
      `SELECT t.id, t.date, t.amount AS price, t.lat, t.lng, t.property_type AS ttype, t.street, t.city,
              u.aire_etages_m2 AS aire, u.superficie_terrain_m2 AS terrain, u.annee_construction AS annee,
              u.nb_logements, u.nb_etages, u.genre_construction AS genre, u.lien_physique AS lien,
              u.municipalite AS muni, u.arrond, u.unite_voisinage AS voisinage, u.valeur_role, u.est_2026
       ${base}
       ORDER BY ((t.rowid * 2654435761 + ${seed}) % 4294967296) LIMIT ?`
    )
    .all(...args, Math.round(n * over * (1 + spec.price.trimPct / 50))) as SaleRow[];

  const idx = new Map<string, { month: string; idx: number }[]>();
  const idxFor = (t: string) => {
    const k = indexType(t === "condo" ? "condo_ou_multi" : t);
    if (!idx.has(k)) idx.set(k, getMarketIndex(k));
    return idx.get(k)!;
  };

  let obs: Obs[] = rows.map((r) => {
    const tk = timeKeys(r.date);
    const type = r.ttype && r.ttype !== "" ? r.ttype : "indéterminé";
    const tf = r.est_2026 ? timeFactor(tk.month, idxFor(type)) : 1;
    return {
      id: r.id,
      price: r.price,
      date: r.date,
      ...tk,
      lat: r.lat,
      lng: r.lng,
      street: r.street,
      city: r.city,
      aire: r.aire && r.aire > 10 ? r.aire : null,
      terrain: r.terrain && r.terrain > 10 ? r.terrain : null,
      annee: r.annee && r.annee > 1600 && r.annee <= tk.year ? r.annee : null,
      nb_logements: r.nb_logements,
      nb_etages: r.nb_etages,
      type,
      genre: r.genre || null,
      lien: r.lien || null,
      muni: r.muni,
      arrond: r.arrond || null,
      voisinage: r.voisinage || null,
      valeur_role: r.valeur_role && r.valeur_role > 1000 ? r.valeur_role : null,
      bedrooms: null,
      bathrooms: null,
      lab: r.est_2026 && tf > 0 ? r.est_2026 / tf : null,
    };
  });
  if (spec.area.mode === "radius" && ac.center) {
    const r = Math.min(Math.max(spec.area.radiusKm ?? 10, 1), 150);
    obs = obs.filter((o) => haversineKm(o.lat, o.lng, ac.center!.lat, ac.center!.lng) <= r);
  }
  return finalize(obs, n, spec, available, ac.center);
}

/* -------------------------------- annonces -------------------------------- */
interface ListingRowLite {
  id: string;
  price: number;
  first_seen: number | null;
  lat: number;
  lng: number;
  ptype: string | null;
  bedrooms: number | null;
  bathrooms: number | null;
  area_sqft: number | null;
  lot_sqft: number | null;
  year_built: number | null;
  street: string | null;
  city: string | null;
  lab: number | null;
  unit_id: string | null;
  muni_e: string | null;
}

const LIVE = "l.status = 'a-vendre' AND l.published = 1 AND l.active = 1 AND l.dup_hidden = 0 AND l.price > 0";

function sampleListings(spec: HedonicSpec): Sample {
  const im = getImmoDb();
  const vp = getDb();
  const n = clampN(spec.maxN);
  const where = [LIVE, "l.price BETWEEN ? AND ?", "l.lat IS NOT NULL", "l.lng IS NOT NULL"];
  const args: unknown[] = [spec.price.min, spec.price.max];
  const ac = areaClause(spec, "l.lat", "l.lng", "e.municipalite", "l.city");
  where.push(...ac.where);
  args.push(...ac.args);
  const base = `FROM listings l LEFT JOIN uqo_eval e ON e.uid = l.uid WHERE ${where.join(" AND ")}`;
  const seed = Math.abs(Math.round(spec.seed)) % 1000003;
  const rows = im
    .prepare(
      `SELECT l.uid AS id, l.price, l.first_seen, l.lat, l.lng, l.property_type AS ptype, l.bedrooms, l.bathrooms,
              l.area_sqft, l.lot_sqft, l.year_built, l.address AS street, l.city, e.est AS lab, e.unit_id, e.municipalite AS muni_e
       ${base} ORDER BY ((l.rowid * 2654435761 + ${seed}) % 4294967296)`
    )
    .all(...args) as ListingRowLite[];

  // types : groupe Immo-Ka → clé du moteur
  const toType = (pt: string | null): string => {
    const g = typeGroup(pt);
    if (g === "maison" || g === "chalet" || g === "mobile" || g === "fermette") return "unifamilial";
    if (g === "condo") return "condo";
    if (g === "plex") return "plex";
    return "indéterminé";
  };
  const wanted = new Set(spec.types.length ? spec.types : ["unifamilial", "condo", "plex"]);
  let pool = rows.filter((r) => wanted.has(toType(r.ptype)));
  if (spec.area.mode === "radius" && ac.center) {
    const r = Math.min(Math.max(spec.area.radiusKm ?? 10, 1), 150);
    pool = pool.filter((o) => haversineKm(o.lat, o.lng, ac.center!.lat, ac.center!.lng) <= r);
  }
  const available = pool.length;
  const take = pool.slice(0, Math.round(n * 1.2 * (1 + spec.price.trimPct / 50)));

  const getUnit = vp.prepare("SELECT * FROM units WHERE id_provinc = ?");
  const nowISO = new Date().toISOString().slice(0, 10);
  const obs: Obs[] = take.map((r) => {
    const u = r.unit_id ? (getUnit.get(r.unit_id) as UnitRow | undefined) : undefined;
    const date = r.first_seen ? new Date(r.first_seen * 1000).toISOString().slice(0, 10) : nowISO;
    const tk = timeKeys(date);
    const aireL = r.area_sqft && r.area_sqft > 200 && r.area_sqft < 20000 ? r.area_sqft * SQFT : null;
    const terrL = r.lot_sqft && r.lot_sqft > 100 ? r.lot_sqft * SQFT : null;
    const annee = u?.annee_construction && u.annee_construction > 1600 ? u.annee_construction : r.year_built && r.year_built > 1600 && r.year_built <= tk.year ? r.year_built : null;
    return {
      id: r.id,
      price: r.price,
      date,
      ...tk,
      lat: r.lat,
      lng: r.lng,
      street: r.street,
      city: r.city,
      aire: u?.aire_etages_m2 && u.aire_etages_m2 > 10 ? u.aire_etages_m2 : aireL,
      terrain: u?.superficie_terrain_m2 && u.superficie_terrain_m2 > 10 ? u.superficie_terrain_m2 : terrL,
      annee,
      nb_logements: u?.nb_logements ?? null,
      nb_etages: u?.nb_etages ?? null,
      type: toType(r.ptype),
      genre: u?.genre_construction || null,
      lien: u?.lien_physique || null,
      muni: r.muni_e ?? u?.municipalite ?? r.city,
      arrond: u?.arrond || null,
      voisinage: u?.unite_voisinage || null,
      valeur_role: u?.valeur_role && u.valeur_role > 1000 ? u.valeur_role : null,
      bedrooms: r.bedrooms,
      bathrooms: r.bathrooms,
      lab: r.lab,
    };
  });
  return finalize(obs, n, spec, available, ac.center);
}

/** Élagage des queues de prix puis plafond n. */
function finalize(obs: Obs[], n: number, spec: HedonicSpec, available: number, center: Sample["center"]): Sample {
  let trimmed = 0;
  const p = Math.min(Math.max(spec.price.trimPct, 0), 10) / 100;
  if (p > 0 && obs.length > 50) {
    const prices = obs.map((o) => o.price).sort((a, b) => a - b);
    const lo = prices[Math.floor(prices.length * p)];
    const hi = prices[Math.min(prices.length - 1, Math.ceil(prices.length * (1 - p)))];
    const before = obs.length;
    obs = obs.filter((o) => o.price >= lo && o.price <= hi);
    trimmed = before - obs.length;
  }
  return { obs: obs.slice(0, n), available, trimmed, center };
}

export function buildSample(spec: HedonicSpec): Sample {
  return spec.source === "listings" ? sampleListings(spec) : sampleSales(spec);
}