// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
/**
 * Accès à la base de coûts (serveur) : articles + prix canoniques, assemblages,
 * taux de main-d'œuvre, localisations, indices, benchmarks → PricingContext.
 * Toute lecture est un INSTANTANÉ à une date (`asOf`) : reproductible.
 */
import type Database from "better-sqlite3";
import { getCostDb } from "./db";
import { canonicalPrice, indexFactor, type PriceObservation } from "./pricing";
import { FALLBACK_HOURLY_RATE, assemblyUnitCost, type BenchmarkRow, type ConditionRule, type PricingContext } from "./engine";
import type { Assembly, CanonicalUnit, Condition, CostItem, CostLocation, CostOverview, IndexSeries, ItemPrice, LabourRate, PriceKind, Quality, UxCategory } from "./types";
import { TRADES } from "./taxonomy";

type Row = Record<string, unknown>;
const s = (v: unknown) => (v == null ? null : String(v));
const n = (v: unknown) => (v == null ? null : Number(v));

export const today = (): string => new Date().toISOString().slice(0, 10);

/* ---------------------------------------------------------------- articles */

function rowToItem(r: Row): CostItem {
  return {
    id: Number(r.id), code: String(r.canonical_code), masterformat: String(r.masterformat_code), division: String(r.division), category: r.category as UxCategory, subcategory: s(r.subcategory),
    nameFr: String(r.name_fr), nameEn: String(r.name_en), descriptionFr: s(r.description_fr), descriptionEn: s(r.description_en), unit: r.unit as CanonicalUnit, materialClass: s(r.material_class),
    trade: s(r.trade), defaultWastePct: Number(r.default_waste_pct), qualityLevel: (r.quality_level as Quality) ?? null, referencePrice: n(r.reference_price), referenceNote: s(r.reference_note), active: Number(r.active) === 1,
  };
}

export function loadItems(d: Database.Database = getCostDb()): Map<string, CostItem> {
  const rows = d.prepare("SELECT * FROM cost_items WHERE active=1").all() as Row[];
  return new Map(rows.map((r) => [String(r.canonical_code), rowToItem(r)]));
}

export function getItem(code: string, d: Database.Database = getCostDb()): CostItem | null {
  const r = d.prepare("SELECT * FROM cost_items WHERE canonical_code=?").get(code) as Row | undefined;
  return r ? rowToItem(r) : null;
}

export function searchItems(q: string, limit = 20, d: Database.Database = getCostDb()): CostItem[] {
  const like = `%${q.trim().normalize("NFD").replace(/[̀-ͯ]/g, "").toLowerCase()}%`;
  const rows = d.prepare(`SELECT * FROM cost_items WHERE active=1 AND (lower(name_fr) LIKE ? OR lower(name_en) LIKE ? OR lower(canonical_code) LIKE ? OR lower(category) LIKE ?) ORDER BY name_fr LIMIT ?`)
    .all(like, like, like, like, limit) as Row[];
  // repli sans accents : SQLite lower() ignore les accents des noms → seconde passe JS
  if (rows.length) return rows.map(rowToItem);
  const all = loadItems(d);
  const fold = (x: string) => x.normalize("NFD").replace(/[̀-ͯ]/g, "").toLowerCase();
  const needle = fold(q);
  return [...all.values()].filter((i) => fold(i.nameFr).includes(needle) || fold(i.nameEn).includes(needle) || fold(i.code).includes(needle)).slice(0, limit);
}

/* ------------------------------------------------------------------- prix */

interface PriceRow { code: string; unit: string; source: string; quality: number; total_cost: number; observation_date: string; source_url: string | null; source_unit: string | null; conversion_factor: number; is_regular_price: number; price_kind: string; location_code: string | null; is_outlier: number }

/** Série d'indice résidentiel utilisée pour l'actualisation (Montréal, bâtiments résidentiels, agrégat). */
export function residentialIndexSeries(d: Database.Database = getCostDb(), code = "statcan:18100289:10:1:1"): { period: string; value: number }[] {
  return d.prepare("SELECT period, index_value AS value FROM construction_cost_indices WHERE index_code=? ORDER BY period").all(code) as { period: string; value: number }[];
}

/**
 * Prix canonique de chaque article à la date `asOf` : observations des 120
 * derniers jours → médiane robuste ; sinon dernière observation actualisée par
 * l'indice StatCan (kind « indexed », ≤ 3 ans) ; sinon prix de référence
 * interne (kind « reference », hypothèse).
 */
export function loadPrices(asOf: string, locationCode: string | null, d: Database.Database = getCostDb(), items = loadItems(d)): Map<string, ItemPrice> {
  const rows = d.prepare(`SELECT i.canonical_code code, i.unit, s.name source, s.quality, p.total_cost, p.observation_date, p.source_url, p.source_unit, p.conversion_factor, p.is_regular_price, p.price_kind, p.location_code, p.is_outlier
    FROM cost_item_prices p JOIN cost_items i ON i.id=p.cost_item_id JOIN cost_sources s ON s.id=p.source_id
    WHERE p.observation_date <= ? AND s.is_active=1 AND p.is_outlier=0 AND p.price_kind IN ('observed','official','indexed','derived') ORDER BY p.observation_date DESC`).all(asOf) as PriceRow[];
  const byItem = new Map<string, PriceObservation[]>();
  for (const r of rows) {
    const arr = byItem.get(r.code) ?? [];
    arr.push({ source: r.source, sourceQuality: r.quality, price: r.total_cost, observedAt: r.observation_date, url: r.source_url, sourceUnit: r.source_unit ?? r.unit, conversionFactor: r.conversion_factor, regular: r.is_regular_price === 1, kind: r.price_kind as PriceKind, locationMatch: !r.location_code || r.location_code === "QC" || r.location_code === locationCode });
    byItem.set(r.code, arr);
  }
  const series = residentialIndexSeries(d);
  const out = new Map<string, ItemPrice>();
  for (const item of items.values()) {
    const obs = byItem.get(item.code) ?? [];
    const cp = canonicalPrice(obs, asOf, 120);
    if (cp) {
      out.set(item.code, {
        itemCode: item.code, unit: item.unit, price: cp.price, low: cp.low, high: cp.high, kind: cp.kind, sourceCount: cp.sourceCount, observedAt: cp.observedAt,
        sources: cp.used.map((o) => ({ source: o.source, price: o.price, url: o.url, observedAt: o.observedAt, sourceUnit: o.sourceUnit, conversionFactor: o.conversionFactor, regular: o.regular })),
        confidence: cp.confidence, indexed: null, note: cp.outliers.length ? `${cp.outliers.length} observation(s) aberrante(s) écartée(s) : ${cp.outliers.map((o) => `${o.obs.source} ${o.obs.price.toFixed(2)} $ (${o.reason})`).join(" ; ")}` : null,
      });
      continue;
    }
    // observation ancienne → actualisation par indice
    const old = obs.filter((o) => o.observedAt <= asOf).sort((a, b) => (a.observedAt < b.observedAt ? 1 : -1))[0];
    if (old && series.length >= 2) {
      const f = indexFactor(series, old.observedAt, asOf);
      const ageDays = (new Date(asOf).getTime() - new Date(old.observedAt).getTime()) / 86400000;
      if (f && ageDays <= 3 * 365) {
        const price = old.price * f.factor;
        out.set(item.code, {
          itemCode: item.code, unit: item.unit, price, low: price * 0.9, high: price * 1.1, kind: "indexed", sourceCount: 1, observedAt: old.observedAt,
          sources: [{ source: old.source, price: old.price, url: old.url, observedAt: old.observedAt, sourceUnit: old.sourceUnit, conversionFactor: old.conversionFactor, regular: old.regular }],
          confidence: Math.round(45 * Math.max(0.3, 1 - ageDays / 1095)), indexed: { fromDate: old.observedAt, toDate: asOf, factor: f.factor, index: "StatCan 18-10-0289 — bâtiments résidentiels, Montréal" },
          note: `Prix du ${old.observedAt} actualisé au ${asOf} par l'indice StatCan (× ${f.factor.toFixed(3)}).`,
        });
        continue;
      }
    }
    if (item.referencePrice != null) {
      out.set(item.code, {
        itemCode: item.code, unit: item.unit, price: item.referencePrice, low: item.referencePrice * 0.85, high: item.referencePrice * 1.15, kind: "reference", sourceCount: 0, observedAt: null,
        sources: [{ source: "Vrai-Prix — prix de référence (hypothèse)", price: item.referencePrice, url: null, observedAt: "2026-09-01", sourceUnit: item.unit, conversionFactor: 1, regular: true }],
        confidence: 35, indexed: null, note: item.referenceNote ?? "Prix de référence interne : aucune observation détaillant pour cet article — hypothèse documentée, non observée.",
      });
    }
  }
  return out;
}

/** Historique des prix (toutes observations) d'un article. */
export function priceHistory(code: string, d: Database.Database = getCostDb()): { date: string; price: number; source: string; url: string | null; regular: boolean; kind: string; outlier: boolean; sourceUnit: string | null; conversionFactor: number }[] {
  return (d.prepare(`SELECT p.observation_date date, p.total_cost price, s.name source, p.source_url url, p.is_regular_price regular, p.price_kind kind, p.is_outlier outlier, p.source_unit su, p.conversion_factor cf
    FROM cost_item_prices p JOIN cost_items i ON i.id=p.cost_item_id JOIN cost_sources s ON s.id=p.source_id WHERE i.canonical_code=? ORDER BY p.observation_date`).all(code) as Row[])
    .map((r) => ({ date: String(r.date), price: Number(r.price), source: String(r.source), url: s(r.url), regular: Number(r.regular) === 1, kind: String(r.kind), outlier: Number(r.outlier) === 1, sourceUnit: s(r.su), conversionFactor: Number(r.cf) }));
}

/* ----------------------------------------------------------- main-d'œuvre */

export function loadLabour(asOf: string, d: Database.Database = getCostDb(), sector = "residentiel_leger", region = "QC"): Map<string, LabourRate> {
  const rows = d.prepare(`SELECT l.*, s.name source_name FROM labour_rates l LEFT JOIN cost_sources s ON s.id=l.source_id
    WHERE l.effective_from <= ? AND l.classification='compagnon' AND l.sector=? AND l.region=? ORDER BY l.effective_from DESC, COALESCE(s.priority, 9) ASC`).all(asOf, sector, region) as Row[];
  const out = new Map<string, LabourRate>();
  for (const r of rows) {
    const code = String(r.trade_code);
    if (out.has(code)) continue;
    out.set(code, {
      tradeCode: code, tradeNameFr: String(r.trade_name_fr), tradeNameEn: s(r.trade_name_en) ?? String(r.trade_name_fr), sector: String(r.sector), classification: String(r.classification), region: String(r.region),
      effectiveFrom: String(r.effective_from), effectiveTo: s(r.effective_to), baseWage: Number(r.base_wage), vacationCost: Number(r.vacation_cost), benefitsCost: Number(r.benefits_cost), employerContributions: Number(r.employer_contributions),
      otherContributions: Number(r.other_contributions), totalEmployerCost: Number(r.total_employer_cost), source: s(r.source_name) ?? "—", sourceUrl: s(r.source_url), confidence: Number(r.confidence_score ?? 80),
    });
  }
  // alias : poseur de bardeaux → couvreur
  if (!out.has("poseur_bardeaux") && out.has("couvreur")) out.set("poseur_bardeaux", { ...out.get("couvreur")!, tradeCode: "poseur_bardeaux" });
  return out;
}

/** Toutes les grilles (historique) d'un métier. */
export function labourHistory(tradeCode: string, d: Database.Database = getCostDb()): LabourRate[] {
  const rows = d.prepare(`SELECT l.*, s.name source_name FROM labour_rates l LEFT JOIN cost_sources s ON s.id=l.source_id WHERE l.trade_code=? ORDER BY l.effective_from`).all(tradeCode) as Row[];
  return rows.map((r) => ({
    tradeCode: String(r.trade_code), tradeNameFr: String(r.trade_name_fr), tradeNameEn: s(r.trade_name_en) ?? String(r.trade_name_fr), sector: String(r.sector), classification: String(r.classification), region: String(r.region),
    effectiveFrom: String(r.effective_from), effectiveTo: s(r.effective_to), baseWage: Number(r.base_wage), vacationCost: Number(r.vacation_cost), benefitsCost: Number(r.benefits_cost), employerContributions: Number(r.employer_contributions),
    otherContributions: Number(r.other_contributions), totalEmployerCost: Number(r.total_employer_cost), source: s(r.source_name) ?? "—", sourceUrl: s(r.source_url), confidence: Number(r.confidence_score ?? 80),
  }));
}

export function allLabourRates(asOf: string, d: Database.Database = getCostDb()): LabourRate[] {
  const out: LabourRate[] = [];
  for (const sector of ["residentiel_leger", "residentiel_lourd", "ic"]) {
    const rows = d.prepare(`SELECT l.*, s.name source_name FROM labour_rates l LEFT JOIN cost_sources s ON s.id=l.source_id WHERE l.effective_from <= ? AND l.sector=? AND l.region='QC' ORDER BY l.effective_from DESC`).all(asOf, sector) as Row[];
    const seen = new Set<string>();
    for (const r of rows) {
      const k = `${r.trade_code}|${r.classification}`;
      if (seen.has(k)) continue;
      seen.add(k);
      out.push({
        tradeCode: String(r.trade_code), tradeNameFr: String(r.trade_name_fr), tradeNameEn: s(r.trade_name_en) ?? String(r.trade_name_fr), sector: String(r.sector), classification: String(r.classification), region: String(r.region),
        effectiveFrom: String(r.effective_from), effectiveTo: s(r.effective_to), baseWage: Number(r.base_wage), vacationCost: Number(r.vacation_cost), benefitsCost: Number(r.benefits_cost), employerContributions: Number(r.employer_contributions),
        otherContributions: Number(r.other_contributions), totalEmployerCost: Number(r.total_employer_cost), source: s(r.source_name) ?? "—", sourceUrl: s(r.source_url), confidence: Number(r.confidence_score ?? 80),
      });
    }
  }
  return out;
}

/* ------------------------------------------------------------ assemblages */

export function loadAssemblies(d: Database.Database = getCostDb()): Map<string, Assembly> {
  const asms = d.prepare("SELECT * FROM cost_assemblies WHERE active=1").all() as Row[];
  const comps = d.prepare(`SELECT c.*, i.canonical_code FROM cost_assembly_components c LEFT JOIN cost_items i ON i.id=c.cost_item_id ORDER BY c.assembly_id, c.sequence`).all() as Row[];
  const byAsm = new Map<number, Assembly["components"]>();
  for (const c of comps) {
    const arr = byAsm.get(Number(c.assembly_id)) ?? [];
    arr.push({ itemCode: s(c.canonical_code) ?? "", quantity: Number(c.quantity_per_assembly_unit), wasteFactor: Number(c.waste_factor), labourHours: Number(c.labour_hours), trade: s(c.trade), equipmentCost: Number(c.equipment_cost), sequence: Number(c.sequence), notes: s(c.notes) });
    byAsm.set(Number(c.assembly_id), arr);
  }
  return new Map(asms.map((a) => [String(a.assembly_code), {
    code: String(a.assembly_code), masterformat: String(a.masterformat_code), category: a.category as UxCategory, nameFr: String(a.name_fr), nameEn: String(a.name_en), descriptionFr: s(a.description_fr) ?? "", descriptionEn: s(a.description_en) ?? "",
    unit: a.unit as CanonicalUnit, buildingType: s(a.building_type), quality: (a.quality_level as Quality) ?? null, version: Number(a.version), economicLife: n(a.economic_life), conditionGroup: s(a.condition_group), components: byAsm.get(Number(a.id)) ?? [], active: true,
  } satisfies Assembly]));
}

/* ----------------------------------------------------------- localisation */

function rowToLocation(r: Row): CostLocation {
  return { code: String(r.code), nameFr: String(r.name_fr), nameEn: String(r.name_en), regionCode: s(r.region_code) ?? "", materialFactor: Number(r.material_factor), labourFactor: Number(r.labour_factor), equipmentFactor: Number(r.equipment_factor), overallFactor: Number(r.overall_factor), effectiveDate: String(r.effective_date), sourceMethod: s(r.source_method) ?? "", confidence: Number(r.confidence_score ?? 50) };
}

export function loadLocations(d: Database.Database = getCostDb()): CostLocation[] {
  return (d.prepare("SELECT * FROM cost_locations ORDER BY code").all() as Row[]).map(rowToLocation);
}

const fold = (x: string) => x.normalize("NFD").replace(/[̀-ͯ]/g, "").toLowerCase().replace(/[^a-z0-9]+/g, " ").trim();

/** Localisation d'une propriété : code forcé → municipalité → centre le plus proche (rayon) → rural. */
export function resolveLocation(opts: { code?: string | null; municipality?: string | null; lat?: number | null; lng?: number | null }, d: Database.Database = getCostDb()): { location: CostLocation; method: string } {
  const rows = d.prepare("SELECT * FROM cost_locations").all() as Row[];
  if (opts.code) {
    const r = rows.find((x) => x.code === opts.code);
    if (r) return { location: rowToLocation(r), method: "code imposé" };
  }
  if (opts.municipality) {
    const m = fold(opts.municipality);
    for (const r of rows) {
      const munis = JSON.parse(String(r.municipalities_json ?? "[]")) as string[];
      if (munis.some((x) => fold(x) === m)) return { location: rowToLocation(r), method: `municipalité « ${opts.municipality} »` };
    }
  }
  if (opts.lat != null && opts.lng != null) {
    let best: { r: Row; km: number } | null = null;
    for (const r of rows) {
      if (!r.latitude || !r.radius_km || Number(r.radius_km) <= 0) continue;
      const km = haversineKm(opts.lat, opts.lng, Number(r.latitude), Number(r.longitude));
      if (km <= Number(r.radius_km) && (!best || km < best.km)) best = { r, km };
    }
    if (best) return { location: rowToLocation(best.r), method: `centre le plus proche (${best.km.toFixed(0)} km de ${best.r.name_fr})` };
  }
  const rural = rows.find((x) => x.code === "QC-RUR") ?? rows[0];
  return { location: rowToLocation(rural), method: "défaut rural (aucune correspondance)" };
}

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

/* ------------------------------------------------- règles, benchmarks, indices */

export function loadConditionRules(d: Database.Database = getCostDb()): Map<string, Map<Condition, ConditionRule>> {
  const rows = d.prepare("SELECT * FROM component_condition_rules").all() as Row[];
  const out = new Map<string, Map<Condition, ConditionRule>>();
  for (const r of rows) {
    const g = String(r.condition_group);
    if (!out.has(g)) out.set(g, new Map());
    out.get(g)!.set(r.condition as Condition, { effectiveAgeRatio: Number(r.effective_age_ratio), economicLife: Number(r.economic_life) });
  }
  return out;
}

export function loadBenchmarks(d: Database.Database = getCostDb()): BenchmarkRow[] {
  return (d.prepare("SELECT * FROM cost_benchmarks WHERE is_demo=0 ORDER BY year DESC").all() as Row[]).map((r) => ({ source: String(r.source), buildingType: String(r.building_type), market: String(r.market), unit: String(r.unit), low: Number(r.low), high: Number(r.high), midpoint: n(r.midpoint), year: Number(r.year), notes: s(r.notes) }));
}

export function loadIndexSeries(d: Database.Database = getCostDb()): IndexSeries[] {
  const rows = d.prepare("SELECT * FROM construction_cost_indices ORDER BY index_code, period").all() as Row[];
  const map = new Map<string, IndexSeries>();
  for (const r of rows) {
    const code = String(r.index_code);
    if (!map.has(code)) map.set(code, { code, source: String(r.source), geography: String(r.geography), buildingType: String(r.building_type), division: String(r.division), points: [], retrievedAt: s(r.retrieved_at) });
    map.get(code)!.points.push({ period: String(r.period), value: Number(r.index_value), pctYoy: n(r.pct_change_yoy) });
  }
  return [...map.values()];
}

/* ---------------------------------------------------------------- contexte */

export function buildContext(opts: { asOf?: string | null; locationCode?: string | null; municipality?: string | null; lat?: number | null; lng?: number | null }, d: Database.Database = getCostDb()): PricingContext & { locationMethod: string } {
  const asOf = opts.asOf ?? today();
  const items = loadItems(d);
  const { location, method } = resolveLocation({ code: opts.locationCode, municipality: opts.municipality, lat: opts.lat, lng: opts.lng }, d);
  return {
    asOf, items, prices: loadPrices(asOf, location.code, d, items), labour: loadLabour(asOf, d), assemblies: loadAssemblies(d), location,
    conditionRules: loadConditionRules(d), benchmarks: loadBenchmarks(d), fallbackHourlyRate: FALLBACK_HOURLY_RATE, locationMethod: method,
  };
}

/* ------------------------------------------------------------- tableau de bord */

const WATCHED_MATERIALS = ["LUM-2X4-8", "LUM-2X6-8", "OSB-7/16-4X8", "GYP-1/2-4X8", "INS-BATT-R24-2X6", "CONC-BAG-30KG", "SHINGLE-ARCH", "SIDING-VINYL", "PLY-1/2-4X8", "POLY-6MIL", "FLOOR-VINYL-PLANK", "TILE-CERAMIC-12X24"];
const POPULAR_ASSEMBLIES = ["STR-WALL-2X6-EXT", "ENV-INSUL-WALL-R24", "ENV-SIDING-VINYL", "ENV-SIDING-BRICK", "ROOF-ASPHALT-ARCH", "FND-WALL-POURED-8IN", "INT-DRYWALL-1/2-FINISHED", "STR-FLOOR-IJOIST", "OPN-WINDOW-PVC-STD", "KIT-STANDARD", "BATH-STANDARD", "INT-FLOOR-HARDWOOD"];

export function buildOverview(d: Database.Database = getCostDb()): CostOverview {
  const asOf = today();
  const ctx = buildContext({ asOf, locationCode: "QC-MTL" }, d);
  const indices = loadIndexSeries(d);
  const resi = indices.find((i) => i.code === "statcan:18100289:10:1:1") ?? indices.find((i) => i.buildingType.includes("résidentiel")) ?? null;
  const wood = indices.find((i) => i.code === "statcan:18100289:10:1:8") ?? null;
  const last = (sr: IndexSeries | null) => (sr && sr.points.length ? sr.points[sr.points.length - 1] : null);
  const rp = last(resi);
  const wp = last(wood);
  const carp = ctx.labour.get("charpentier") ?? null;
  let labourYoy: number | null = null;
  if (carp) {
    const hist = labourHistory("charpentier", d).filter((h) => h.sector === carp.sector && h.classification === "compagnon");
    const prev = [...hist].reverse().find((h) => h.effectiveFrom <= shiftDays(carp.effectiveFrom, -300));
    if (prev && prev.totalEmployerCost > 0) labourYoy = Math.round(((carp.totalEmployerCost / prev.totalEmployerCost) - 1) * 1000) / 10;
  }
  const obsCount = (d.prepare("SELECT COUNT(*) n FROM cost_item_prices WHERE price_kind IN ('observed','official')").get() as { n: number }).n;
  const itemsObserved = (d.prepare("SELECT COUNT(DISTINCT cost_item_id) n FROM cost_item_prices WHERE price_kind IN ('observed','official') AND observation_date >= date('now','-120 days')").get() as { n: number }).n;
  const lastSync = (d.prepare("SELECT MAX(last_successful_sync) m FROM cost_sources").get() as { m: string | null }).m;
  const materials = WATCHED_MATERIALS.filter((c) => ctx.items.has(c)).map((code) => {
    const it = ctx.items.get(code)!;
    const p = ctx.prices.get(code) ?? null;
    const hist = priceHistory(code, d).filter((h) => !h.outlier && h.kind !== "reference");
    // médiane par date
    const byDate = new Map<string, number[]>();
    for (const h of hist) byDate.set(h.date, [...(byDate.get(h.date) ?? []), h.price]);
    const history = [...byDate.entries()].map(([date, ps]) => ({ date, price: Math.round(100 * ps.sort((a, b) => a - b)[Math.floor(ps.length / 2)]) / 100 })).sort((a, b) => a.date.localeCompare(b.date));
    return { itemCode: code, nameFr: it.nameFr, nameEn: it.nameEn, unit: it.unit, price: p ? Math.round(p.price * 100) / 100 : null, kind: p?.kind ?? null, sourceCount: p?.sourceCount ?? 0, observedAt: p?.observedAt ?? null, history };
  });
  const popularAssemblies = POPULAR_ASSEMBLIES.filter((c) => ctx.assemblies.has(c)).map((code) => {
    const a = ctx.assemblies.get(code)!;
    const u = assemblyUnitCost(a, ctx);
    return { code, nameFr: a.nameFr, nameEn: a.nameEn, unit: a.unit, direct: u.direct, material: u.material, labour: u.labour, equipment: u.equipment, observedShare: Math.round(u.observedShare * 100) / 100 };
  });
  const sources = (d.prepare(`SELECT s.*, (SELECT COUNT(*) FROM cost_item_prices p WHERE p.source_id=s.id) + (SELECT COUNT(*) FROM labour_rates l WHERE l.source_id=s.id) obs FROM cost_sources s ORDER BY s.priority, s.name`).all() as Row[])
    .map((r) => ({ name: String(r.name), type: String(r.source_type), license: String(r.license_status), lastSync: s(r.last_successful_sync), active: Number(r.is_active) === 1, observations: Number(r.obs), url: s(r.base_url) }));
  return {
    generatedAt: new Date().toISOString(), indices,
    kpis: {
      residentialIndex: rp ? { value: rp.value, period: rp.period, pctYoy: rp.pctYoy, geography: resi!.geography } : null,
      materialsProxy: wp ? { value: wp.value, period: wp.period, pctYoy: wp.pctYoy, label: wood!.division } : null,
      labour: carp ? { trade: carp.tradeNameFr, totalEmployerCost: carp.totalEmployerCost, effectiveFrom: carp.effectiveFrom, pctYoy: labourYoy, source: carp.source } : null,
      observations: obsCount, itemsObserved, itemsTotal: ctx.items.size, lastSync,
    },
    materials, popularAssemblies, locations: loadLocations(d), sources,
  };
}

function shiftDays(iso: string, days: number): string {
  const d = new Date(iso);
  d.setDate(d.getDate() + days);
  return d.toISOString().slice(0, 10);
}

/** Libellés des métiers pour l'UI (réexport). */
export const TRADE_LIST = TRADES;