// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
// Service d'estimation : relie la base SQLite au moteur (engine.ts).
import {
  getCandidates,
  getMarketIndex,
  getUnit,
  municipalityCenter,
  type TxRow,
  type UnitRow,
} from "./db";
import { estimate, type CompInput, type EstimateResult, type Subject } from "./engine";

export interface UnitSpecs {
  cubf: number | null;
  cubfLibelle: string | null;
  anneeEstimee: string | null;
  frontTerrainM: number | null;
  nbEtages: number | null;
  nbLocauxNonResid: number | null;
  nbChambresLocatives: number | null;
  lienPhysique: string | null;
  genreConstruction: string | null;
  matricule: string | null;
  uniteVoisinage: string | null;
  nAdresses: number | null;
  datCondMarche: string | null;
  valeurTerrain: number | null;
  valeurBatiment: number | null;
  valeurAnterieure: number | null;
  arrond: string | null;
  apt: string | null;
}

export interface UnitEstimate {
  unit: {
    id: string;
    adresse: string | null;
    municipalite: string | null;
    typeProp: string;
    cubfLibelle: string | null;
    anneeConstruction: number | null;
    aireEtagesM2: number | null;
    superficieTerrainM2: number | null;
    nbLogements: number | null;
    valeurRole: number | null;
    lat: number;
    lng: number;
    history: { year: number; value: number | null }[];
    specs: UnitSpecs;
  } | null;
  result: EstimateResult;
}

/** Type de comparable attendu pour le type de tx du marché de l'indice. */
export function indexType(typeProp: string): string {
  if (typeProp === "condo_ou_multi") return "condo";
  if (typeProp === "plex") return "plex";
  return "unifamilial";
}

function toComps(rows: TxRow[]): CompInput[] {
  return rows.map((r) => ({
    id: r.id,
    date: r.date,
    amount: r.amount,
    lat: r.lat,
    lng: r.lng,
    propertyType: r.property_type,
    yearBuilt: r.year_built,
    floorArea: r.floor_area,
    street: r.street,
    city: r.city,
  }));
}

function nowISO(): string {
  return new Date().toISOString().slice(0, 10);
}

function since(months: number): string {
  const d = new Date();
  d.setMonth(d.getMonth() - months);
  return d.toISOString().slice(0, 10);
}

function candidatesAround(lat: number, lng: number): TxRow[] {
  // ~2,2 km puis élargit à ~11 km si marché mince
  for (const halfDeg of [0.02, 0.1]) {
    const rows = getCandidates(lat, lng, halfDeg, since(30), 600);
    if (rows.length >= 30) return rows;
  }
  return getCandidates(lat, lng, 0.25, since(36), 600);
}

export function estimateByUnitId(id: string): UnitEstimate | null {
  const u = getUnit(id);
  if (!u) return null;
  const subject: Subject = {
    lat: u.lat,
    lng: u.lng,
    typeProp: u.type_prop,
    floorArea: u.aire_etages_m2,
    yearBuilt: u.annee_construction,
    landArea: u.superficie_terrain_m2,
    modelEstimate: u.est_2026,
    modelP10: u.p10,
    modelP90: u.p90,
  };
  const result = estimate(
    subject,
    toComps(candidatesAround(u.lat, u.lng)),
    getMarketIndex(indexType(u.type_prop)),
    nowISO()
  );
  return {
    unit: {
      id: u.id_provinc,
      adresse: u.adresse,
      municipalite: u.municipalite,
      typeProp: u.type_prop,
      cubfLibelle: u.cubf_libelle,
      anneeConstruction: u.annee_construction,
      aireEtagesM2: u.aire_etages_m2,
      superficieTerrainM2: u.superficie_terrain_m2,
      nbLogements: u.nb_logements,
      valeurRole: u.valeur_role,
      lat: u.lat,
      lng: u.lng,
      history: ([2021, 2022, 2023, 2024, 2025, 2026] as const).map((y) => ({
        year: y,
        value: u[`est_${y}` as keyof UnitRow] as number | null,
      })),
      specs: {
        cubf: u.cubf,
        cubfLibelle: u.cubf_libelle,
        anneeEstimee: u.annee_estimee,
        frontTerrainM: u.front_terrain_m,
        nbEtages: u.nb_etages,
        nbLocauxNonResid: u.nb_locaux_non_resid,
        nbChambresLocatives: u.nb_chambres_locatives,
        lienPhysique: u.lien_physique,
        genreConstruction: u.genre_construction,
        matricule: u.matricule,
        uniteVoisinage: u.unite_voisinage,
        nAdresses: u.n_adresses,
        datCondMarche: u.dat_cond_marche,
        valeurTerrain: u.valeur_terrain,
        valeurBatiment: u.valeur_batiment,
        valeurAnterieure: u.valeur_anterieure,
        arrond: u.arrond,
        apt: u.apt,
      },
    },
    result,
  };
}

export interface PortfolioAggregates {
  count: number;
  totalEstimate: number;
  totalLow: number;
  totalHigh: number;
  totalRole: number;
  ecartRolePct: number | null;
  confidencePct: number; // moyenne pondérée par valeur
  confidenceLevel: "A" | "B" | "C" | "D";
  totalFloorArea: number;
  totalLandArea: number;
  totalDwellings: number;
  avgYearBuilt: number | null;
  municipalities: { name: string; count: number; total: number }[];
  types: { type: string; count: number; total: number }[];
  history: { year: number; total: number; nCovered: number }[];
  growthPct: number | null; // croissance du parc 2021→2026 (unités couvertes)
}

export interface PortfolioResult {
  items: UnitEstimate[];
  aggregates: PortfolioAggregates;
}

export function estimatePortfolio(ids: string[]): PortfolioResult {
  const items = ids
    .slice(0, 40)
    .map((id) => estimateByUnitId(id))
    .filter((x): x is UnitEstimate => x !== null && x.unit !== null);

  const val = (n: number | null | undefined) => n ?? 0;
  const totalEstimate = items.reduce((s, i) => s + val(i.result.estimate), 0);
  const totalRole = items.reduce((s, i) => s + val(i.unit!.valeurRole), 0);
  const wConf =
    totalEstimate > 0
      ? items.reduce((s, i) => s + i.result.confidencePct * val(i.result.estimate), 0) /
        totalEstimate
      : 0;
  const level = wConf >= 75 ? "A" : wConf >= 60 ? "B" : wConf >= 45 ? "C" : "D";

  const byKey = (key: (i: UnitEstimate) => string) => {
    const m = new Map<string, { count: number; total: number }>();
    for (const i of items) {
      const k = key(i);
      const e = m.get(k) ?? { count: 0, total: 0 };
      e.count += 1;
      e.total += val(i.result.estimate);
      m.set(k, e);
    }
    return [...m.entries()]
      .map(([name, v]) => ({ name, ...v }))
      .sort((a, b) => b.total - a.total);
  };

  const years = [2021, 2022, 2023, 2024, 2025, 2026] as const;
  const history = years.map((y) => {
    let total = 0;
    let n = 0;
    for (const i of items) {
      const h = i.unit!.history.find((hh) => hh.year === y);
      if (h?.value != null) {
        total += h.value;
        n += 1;
      }
    }
    return { year: y, total, nCovered: n };
  });
  // croissance : somme 2026 vs 2021 sur les unités couvertes aux deux bornes
  let g21 = 0;
  let g26 = 0;
  for (const i of items) {
    const a = i.unit!.history.find((h) => h.year === 2021)?.value;
    const b = i.unit!.history.find((h) => h.year === 2026)?.value;
    if (a != null && b != null) {
      g21 += a;
      g26 += b;
    }
  }

  const yb = items
    .map((i) => i.unit!.anneeConstruction)
    .filter((v): v is number => v != null && v > 1600);

  return {
    items,
    aggregates: {
      count: items.length,
      totalEstimate,
      totalLow: items.reduce((s, i) => s + val(i.result.low), 0),
      totalHigh: items.reduce((s, i) => s + val(i.result.high), 0),
      totalRole,
      ecartRolePct: totalRole > 0 ? (totalEstimate / totalRole - 1) * 100 : null,
      confidencePct: Math.round(wConf),
      confidenceLevel: level,
      totalFloorArea: Math.round(items.reduce((s, i) => s + val(i.unit!.aireEtagesM2), 0)),
      totalLandArea: Math.round(items.reduce((s, i) => s + val(i.unit!.superficieTerrainM2), 0)),
      totalDwellings: items.reduce((s, i) => s + val(i.unit!.nbLogements), 0),
      avgYearBuilt: yb.length ? Math.round(yb.reduce((s, v) => s + v, 0) / yb.length) : null,
      municipalities: byKey((i) => i.unit!.municipalite ?? "—"),
      types: byKey((i) => i.unit!.typeProp).map(({ name, ...v }) => ({ type: name, ...v })),
      history,
      growthPct: g21 > 0 ? (g26 / g21 - 1) * 100 : null,
    },
  };
}

export interface ManualParams {
  municipality: string;
  typeProp: string;
  floorArea?: number;
  yearBuilt?: number;
  landArea?: number;
}

export function estimateManual(p: ManualParams): UnitEstimate | null {
  const center = municipalityCenter(p.municipality);
  if (!center || !center.n) return null;
  const subject: Subject = {
    lat: center.lat,
    lng: center.lng,
    typeProp: p.typeProp,
    floorArea: p.floorArea ?? null,
    yearBuilt: p.yearBuilt ?? null,
    landArea: p.landArea ?? null,
    modelEstimate: null,
    modelP10: null,
    modelP90: null,
  };
  const result = estimate(
    subject,
    toComps(candidatesAround(center.lat, center.lng)),
    getMarketIndex(indexType(p.typeProp)),
    nowISO()
  );
  if (!Number.isFinite(result.estimate)) return null;
  return { unit: null, result };
}