Repository Public
1// UQO Éval — Métré : service d'estimation (contexte de prix depuis cost.db + recettes, lignes, calcul, révisions immuables, évaluation). SERVEUR seulement.2import { createHash, randomUUID } from "crypto";3import type Database from "better-sqlite3";4import { audit, nowIso } from "./db";5import { HttpError, type Ctx } from "./auth";6import { AssemblyCycleError, detectCycle, quoteCustomAssembly, type AssemblyQuote, type AssemblyResolver } from "./assemblies";7import { ESTIMATE_FORMULA_VERSION, computeEstimate, defaultParams, type EngineContext, type UnitQuote } from "./estimate-engine";8import { rsmLine, rsmLocationForProject, rsmMeta, rsmPrice, rsmUnit, rsmeansAvailable } from "./rsmeans";9import { QS_RSMEANS_MAP } from "./rsmeans-qs-map";10import { catalogueUnitToMeasure } from "./units";11import { computeValuation } from "./valuation";12import type { CustomAssembly, CustomComponent, EstimateLineInput, EstimateParams, EstimateResult, EstimateVersion, FunctionalItem, MeasureUnit, PriceBasis, ValuationInput, ValuationResult, Project } from "./types";13import { getCostDb, costDatabaseVersion } from "../cost/db";14import { buildContext, loadAssemblies, loadLabour, residentialIndexSeries, searchItems, today } from "../cost/catalog";15import { assemblyUnitCost, FALLBACK_HOURLY_RATE, type PricingContext } from "../cost/engine";16import { indexFactor } from "../cost/pricing";17import { getMeasurement } from "./plans";1819/* ---------------------------------------------------------- contexte prix */2021export interface QuoteContext extends EngineContext { resolver: AssemblyResolver; pricing: PricingContext; snapshot(): Record<string, unknown> }2223/** Construit le contexte de prix (instantané) : catalogue cost.db à la date demandée + recettes du module. */24/** Devis unitaire d'une ligne RSMeans : coûts « bare » (matériau, MO, équipement) localisés — les majorations du module s'appliquent ensuite. */25export function rsmeansQuote(l: EstimateLineInput, project: Pick<Project, "municipality" | "regionCode" | "address"> | null, params: EstimateParams): UnitQuote | null {26 if (!l.rsmeansLine || !rsmeansAvailable()) return null;27 const line = rsmLine(l.rsmeansLine); if (!line) return null;28 const um = rsmUnit(line.unit); if (!um) return null;29 const locId = params.rsmeansLocationId ?? (project ? rsmLocationForProject(project).id : 419);30 const labor = params.rsmeansLabor ?? "STD";31 const p = rsmPrice(l.rsmeansLine, locId, labor); if (!p || p.total == null) return null;32 const meta = rsmMeta();33 const w: string[] = [];34 if (p.labor !== p.laborRequested) w.push(`type de MO RSMeans substitué : ${p.labor} (demandé ${p.laborRequested})`);35 if (um.note) w.push(um.note);36 const d = um.divisor;37 return { unit: um.unit, material: (p.material ?? 0) / d, labour: (p.labor_cost ?? 0) / d, equipment: (p.equipment ?? 0) / d, other: 0, installed: false,38 provenance: { basis: "rsmeans", kind: "official", evidenceClass: "published_schedule", source: `RSMeans ${p.release} · ${p.locationName} · ${p.labor}${p.method === "factor" ? " · national × facteur de division" : ""}`, sourceUrl: "https://www.rsmeansonline.com/", observedAt: typeof meta.extracted_at === "string" ? meta.extracted_at : null, note: `ligne ${line.line} — ${line.fullDesc}${p.totalOp != null ? ` — total O&P RSMeans ${p.totalOp} $/${line.unit}` : ""}${p.method === "factor" && p.factor ? ` — facteurs mat. ${p.factor.material} / MO ${p.factor.labor ?? "—"} (n = ${p.factor.n})` : ""}`, confidence: p.method === "full" ? 85 : 72, observedShare: 1 },39 warnings: w.length ? w : undefined };40}4142export function buildQuoteContext(d: Database.Database, projectId: string | null, params: EstimateParams): QuoteContext {43 const project = projectId ? ((d.prepare("SELECT municipality, region_code AS regionCode, address FROM metre_projects WHERE id = ?").get(projectId) as Pick<Project, "municipality" | "regionCode" | "address"> | undefined) ?? null) : null;44 const cdb = getCostDb();45 const asOf = /^\d{4}-\d{2}-\d{2}$/.test(params.priceDate) ? params.priceDate : today();46 const pc = buildContext({ asOf, locationCode: params.regionCode }, cdb);47 const customs = new Map<string, CustomAssembly>();48 for (const a of listCustomAssemblies(d, projectId)) customs.set(a.code, a);49 const catQuotes = new Map<string, AssemblyQuote>();50 const used = new Set<string>();51 const resolver: AssemblyResolver = {52 item: (code) => {53 const it = pc.items.get(code); const p = pc.prices.get(code);54 if (!it) return null;55 used.add(code);56 if (!p) return null;57 return { unitPrice: p.price, unit: it.unit, kind: p.kind, source: p.sources.map((s) => s.source).join(", ") || "—", sourceUrl: p.sources[0]?.url ?? null, observedAt: p.observedAt, confidence: p.confidence, isEquipment: it.materialClass === "equipment" || it.materialClass === "rental", defaultTrade: it.trade };58 },59 labourRate: (trade) => { const r = trade ? pc.labour.get(trade) : undefined; return r ? { rate: r.totalEmployerCost, source: r.source, sourceUrl: r.sourceUrl, effectiveFrom: r.effectiveFrom, confidence: r.confidence } : null; },60 catalogueAssembly: (code) => {61 if (catQuotes.has(code)) return catQuotes.get(code)!;62 const a = pc.assemblies.get(code);63 if (!a) return null;64 const u = assemblyUnitCost(a, pc);65 for (const c of a.components) if (c.itemCode) used.add(c.itemCode);66 const q: AssemblyQuote = { code, unit: catalogueUnitToMeasure(a.unit) ?? a.unit, material: u.material, labour: u.labour, equipment: u.equipment, other: 0, direct: u.direct, observedShare: u.observedShare, confidence: Math.round(100 * (0.6 * u.observedShare + 0.4 * (1 - Math.min(1, u.sigmaPct / 0.3)))), source: `catalogue UQO Éval — ${a.nameFr}`, components: [] };67 catQuotes.set(code, q);68 return q;69 },70 customAssembly: (code) => customs.get(code) ?? null,71 fallbackHourlyRate: FALLBACK_HOURLY_RATE,72 };73 const quote = (l: EstimateLineInput): UnitQuote | null => {74 if (l.basis === "rsmeans") return rsmeansQuote(l, project, params);75 if (!l.assemblyCode) return null;76 const custom = customs.get(l.assemblyCode);77 if (custom) {78 const q = quoteCustomAssembly(custom, resolver);79 const w = q.components.flatMap((c) => c.warnings);80 return { unit: q.unit, material: q.material, labour: q.labour, equipment: q.equipment, other: q.other, installed: false, provenance: { basis: "custom_assembly", kind: q.observedShare >= 0.5 ? "observed" : q.observedShare > 0 ? "derived" : "reference", evidenceClass: null, source: q.source, sourceUrl: null, observedAt: null, note: custom.exclusions, confidence: q.confidence, observedShare: q.observedShare }, warnings: w.length ? [...new Set(w)].slice(0, 5) : undefined };81 }82 const cq = resolver.catalogueAssembly(l.assemblyCode);83 if (!cq) return null;84 const a = pc.assemblies.get(l.assemblyCode)!;85 const u = assemblyUnitCost(a, pc);86 const fresh = u.freshestObservation;87 const kinds = new Set(u.components.map((c) => c.provenance.kind));88 const kind = kinds.has("observed") || kinds.has("official") ? (u.observedShare >= 0.5 ? "observed" : "derived") : kinds.has("indexed") ? "indexed" : "reference";89 return { unit: cq.unit, material: cq.material, labour: cq.labour, equipment: cq.equipment, other: 0, installed: false, provenance: { basis: "catalogue", kind, evidenceClass: kind === "observed" ? "supplier_quote" : null, source: cq.source, sourceUrl: null, observedAt: fresh, note: u.observedShare < 0.5 ? "majorité de prix de référence (hypothèses documentées)" : null, confidence: cq.confidence, observedShare: u.observedShare }, warnings: u.direct <= 0 ? ["assemblage sans prix"] : undefined };90 };91 return {92 quote, resolver, pricing: pc, catalogueVersion: costDatabaseVersion(cdb), now: nowIso(),93 location: { code: pc.location.code, nameFr: pc.location.nameFr, nameEn: pc.location.nameEn, materialFactor: pc.location.materialFactor, labourFactor: pc.location.labourFactor, equipmentFactor: pc.location.equipmentFactor, confidence: pc.location.confidence, method: pc.locationMethod },94 snapshot: () => ({ asOf, location: pc.location, prices: Object.fromEntries([...used].map((k) => [k, pc.prices.get(k) ?? null])), labour: Object.fromEntries(pc.labour), customAssemblies: [...customs.values()] }),95 };96}9798/** Facteur d'actualisation StatCan entre la date des prix et une date cible (null si indisponible). */99export function escalationFactor(priceDate: string, toDate: string): { factor: number; source: string } | null {100 try {101 const series = residentialIndexSeries(getCostDb());102 const f = indexFactor(series, priceDate, toDate);103 return f ? { factor: f.factor, source: `StatCan 18-10-0289 (bâtiments résidentiels, Montréal) ${f.from.period} → ${f.to.period}` } : null;104 } catch { return null; }105}106107/* ---------------------------------------------------------- recettes */108109export function listCustomAssemblies(d: Database.Database, projectId: string | null): CustomAssembly[] {110 const rows = d.prepare("SELECT * FROM metre_assemblies WHERE deleted_at IS NULL AND (project_id IS NULL OR project_id=?) ORDER BY code").all(projectId) as Record<string, unknown>[];111 return rows.map(mapAssembly);112}113114function mapAssembly(r: Record<string, unknown>): CustomAssembly {115 return { id: String(r.id), projectId: r.project_id as string | null, code: String(r.code), nameFr: String(r.name_fr), nameEn: String(r.name_en), unit: r.unit as MeasureUnit, outputQuantity: Number(r.output_quantity), category: String(r.category), version: Number(r.version), components: JSON.parse(String(r.components_json)), exclusions: r.exclusions as string | null, createdBy: String(r.created_by), createdAt: String(r.created_at), updatedAt: String(r.updated_at) };116}117118function sanitizeAssembly(raw: Record<string, unknown>): Omit<CustomAssembly, "id" | "projectId" | "version" | "createdBy" | "createdAt" | "updatedAt"> {119 const code = String(raw.code ?? "").trim().toUpperCase().replace(/[^A-Z0-9-]/g, "-").slice(0, 40);120 if (code.length < 2) throw new HttpError(400, "Code d'assemblage requis (lettres, chiffres, tirets).");121 const units: MeasureUnit[] = ["m", "ft", "m2", "ft2", "m3", "ft3", "yd3", "unit", "h", "lump"];122 if (!units.includes(raw.unit as MeasureUnit)) throw new HttpError(400, "Unité invalide.");123 const out = Number(raw.outputQuantity ?? 1);124 if (!(out > 0) || out > 1e6) throw new HttpError(400, "Quantité de sortie invalide.");125 const comps = (Array.isArray(raw.components) ? raw.components : []).slice(0, 60).map((c): CustomComponent => {126 const x = c as Record<string, unknown>;127 const n = (k: string, max: number, def = 0) => { const v = Number(x[k] ?? def); if (!Number.isFinite(v) || v < 0 || v > max) throw new HttpError(400, `Composant : ${k} invalide.`); return v; };128 const refType = x.refType === "assembly" ? "assembly" : "item";129 const refCode = String(x.refCode ?? "").trim().toUpperCase().slice(0, 40);130 if (!refCode) throw new HttpError(400, "Composant : référence requise.");131 return { id: typeof x.id === "string" ? x.id.slice(0, 36) : randomUUID(), refType, refCode, quantity: n("quantity", 1e6), wasteRate: n("wasteRate", 1), labourHours: n("labourHours", 1000), trade: typeof x.trade === "string" && x.trade ? x.trade.slice(0, 40) : null, equipmentHours: n("equipmentHours", 1000), equipmentRate: n("equipmentRate", 10000), subcontract: n("subcontract", 1e7), transport: n("transport", 1e7), installed: !!x.installed, notes: typeof x.notes === "string" ? x.notes.slice(0, 300) : null };132 });133 return { code, nameFr: String(raw.nameFr ?? code).trim().slice(0, 160), nameEn: String(raw.nameEn ?? raw.nameFr ?? code).trim().slice(0, 160), unit: raw.unit as MeasureUnit, outputQuantity: out, category: String(raw.category ?? "custom").slice(0, 40), components: comps, exclusions: typeof raw.exclusions === "string" ? raw.exclusions.slice(0, 1000) : null };134}135136export function saveCustomAssembly(ctx: Ctx, projectId: string | null, raw: Record<string, unknown>, existingId?: string): CustomAssembly {137 if (projectId === null && ctx.user.role === "student") throw new HttpError(403, "Seuls les enseignants publient des recettes partagées.");138 const a = sanitizeAssembly(raw);139 const d = ctx.db;140 const resolverForCycle = { customAssembly: (code: string) => (code === a.code ? ({ ...a, id: "tmp", projectId, version: 0, createdBy: "", createdAt: "", updatedAt: "" } as CustomAssembly) : listCustomAssemblies(d, projectId).find((x) => x.code === code) ?? null) };141 const cycle = detectCycle(a.code, resolverForCycle);142 if (cycle) throw new HttpError(400, new AssemblyCycleError(cycle).message);143 const cur = existingId ? (d.prepare("SELECT * FROM metre_assemblies WHERE id=? AND deleted_at IS NULL").get(existingId) as Record<string, unknown> | undefined) : undefined;144 const id = cur ? String(cur.id) : randomUUID();145 const version = cur ? Number(cur.version) + 1 : 1;146 const tx = d.transaction(() => {147 if (cur) d.prepare("UPDATE metre_assemblies SET code=?,name_fr=?,name_en=?,unit=?,output_quantity=?,category=?,version=?,components_json=?,exclusions=?,updated_at=? WHERE id=?").run(a.code, a.nameFr, a.nameEn, a.unit, a.outputQuantity, a.category, version, JSON.stringify(a.components), a.exclusions, nowIso(), id);148 else d.prepare("INSERT INTO metre_assemblies(id,project_id,code,name_fr,name_en,unit,output_quantity,category,version,components_json,exclusions,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)").run(id, projectId, a.code, a.nameFr, a.nameEn, a.unit, a.outputQuantity, a.category, version, JSON.stringify(a.components), a.exclusions, ctx.user.id);149 d.prepare("INSERT INTO metre_assembly_versions(id,assembly_id,version,snapshot_json,created_by) VALUES(?,?,?,?,?)").run(randomUUID(), id, version, JSON.stringify({ ...a, version }), ctx.user.id);150 });151 try { tx(); } catch (e) { if (String((e as Error).message).includes("UNIQUE")) throw new HttpError(409, `Le code ${a.code} existe déjà dans ce périmètre.`); throw e; }152 audit(d, { userId: ctx.user.id, role: ctx.user.role, action: cur ? "assembly.update" : "assembly.create", entityType: "assembly", entityId: id, projectId, details: { code: a.code, version }, ip: ctx.ip });153 return mapAssembly(d.prepare("SELECT * FROM metre_assemblies WHERE id=?").get(id) as Record<string, unknown>);154}155156export function assemblyVersions(d: Database.Database, id: string): { version: number; createdBy: string; createdAt: string; snapshot: unknown }[] {157 return (d.prepare("SELECT version, created_by createdBy, created_at createdAt, snapshot_json s FROM metre_assembly_versions WHERE assembly_id=? ORDER BY version DESC").all(id) as Record<string, unknown>[]).map((r) => ({ version: Number(r.version), createdBy: String(r.createdBy), createdAt: String(r.createdAt), snapshot: JSON.parse(String(r.s)) }));158}159160export function deleteCustomAssembly(ctx: Ctx, id: string): void {161 ctx.db.prepare("UPDATE metre_assemblies SET deleted_at=? WHERE id=?").run(nowIso(), id);162}163164/** Chiffre une recette (pour l'affichage) sans l'enregistrer. */165export function quoteAssemblyForDisplay(d: Database.Database, projectId: string | null, code: string, params: EstimateParams): AssemblyQuote | null {166 const ctx = buildQuoteContext(d, projectId, params);167 const custom = ctx.resolver.customAssembly(code);168 return custom ? quoteCustomAssembly(custom, ctx.resolver) : ctx.resolver.catalogueAssembly(code);169}170171/* ------------------------------------------------------------ catalogue */172173export function catalogueSearch(d: Database.Database, projectId: string | null, q: string, params: EstimateParams, limit = 30): { assemblies: { code: string; nameFr: string; nameEn: string; unit: string; category: string; source: "catalogue" | "custom"; direct: number | null; observedShare: number | null }[]; items: { code: string; nameFr: string; nameEn: string; unit: string; price: number | null; kind: string | null }[] } {174 const cdb = getCostDb();175 const needle = q.trim().toLowerCase();176 const ctx = buildQuoteContext(d, projectId, params);177 const assemblies: ReturnType<typeof catalogueSearch>["assemblies"] = [];178 for (const a of loadAssemblies(cdb).values()) {179 if (needle && !`${a.code} ${a.nameFr} ${a.nameEn} ${a.category}`.toLowerCase().includes(needle)) continue;180 const qt = ctx.resolver.catalogueAssembly(a.code);181 assemblies.push({ code: a.code, nameFr: a.nameFr, nameEn: a.nameEn, unit: qt?.unit ?? a.unit, category: a.category, source: "catalogue", direct: qt?.direct ?? null, observedShare: qt?.observedShare ?? null });182 if (assemblies.length >= limit) break;183 }184 for (const a of listCustomAssemblies(d, projectId)) {185 if (needle && !`${a.code} ${a.nameFr} ${a.nameEn}`.toLowerCase().includes(needle)) continue;186 let qt: AssemblyQuote | null = null;187 try { qt = quoteCustomAssembly(a, ctx.resolver); } catch { /* cycle / profondeur */ }188 assemblies.unshift({ code: a.code, nameFr: a.nameFr, nameEn: a.nameEn, unit: a.unit, category: a.category, source: "custom", direct: qt?.direct ?? null, observedShare: qt?.observedShare ?? null });189 }190 const items = (needle ? searchItems(needle, limit, cdb) : [...ctx.pricing.items.values()].slice(0, limit)).map((it) => { const p = ctx.pricing.prices.get(it.code); return { code: it.code, nameFr: it.nameFr, nameEn: it.nameEn, unit: it.unit, price: p?.price ?? null, kind: p?.kind ?? null }; });191 return { assemblies, items };192}193194export function labourTable(params: EstimateParams): { tradeCode: string; tradeNameFr: string; tradeNameEn: string; totalEmployerCost: number; baseWage: number; effectiveFrom: string; source: string; sourceUrl: string | null }[] {195 const asOf = /^\d{4}-\d{2}-\d{2}$/.test(params.priceDate) ? params.priceDate : today();196 return [...loadLabour(asOf, getCostDb()).values()].map((r) => ({ tradeCode: r.tradeCode, tradeNameFr: r.tradeNameFr, tradeNameEn: r.tradeNameEn, totalEmployerCost: r.totalEmployerCost, baseWage: r.baseWage, effectiveFrom: r.effectiveFrom, source: r.source, sourceUrl: r.sourceUrl }));197}198199/* ------------------------------------------------------------ estimation */200201export interface EstimateRow { id: string; projectId: string; name: string; params: EstimateParams; lines: EstimateLineInput[]; createdBy: string; createdAt: string; updatedAt: string }202203function mapEstimate(r: Record<string, unknown>): EstimateRow {204 return { id: String(r.id), projectId: String(r.project_id), name: String(r.name), params: JSON.parse(String(r.params_json)), lines: JSON.parse(String(r.lines_json)), createdBy: String(r.created_by), createdAt: String(r.created_at), updatedAt: String(r.updated_at) };205}206207export function listEstimates(d: Database.Database, projectId: string): (EstimateRow & { versions: number })[] {208 return (d.prepare("SELECT e.*, (SELECT COUNT(*) FROM metre_estimate_versions v WHERE v.estimate_id=e.id) versions FROM metre_estimates e WHERE e.project_id=? AND e.deleted_at IS NULL ORDER BY e.created_at").all(projectId) as Record<string, unknown>[]).map((r) => ({ ...mapEstimate(r), versions: Number(r.versions) }));209}210211export function getEstimate(d: Database.Database, id: string): EstimateRow | null {212 const r = d.prepare("SELECT * FROM metre_estimates WHERE id=? AND deleted_at IS NULL").get(id) as Record<string, unknown> | undefined;213 return r ? mapEstimate(r) : null;214}215216export function createEstimate(ctx: Ctx, projectId: string, name: string, regionCode: string | null, valuationDate: string): EstimateRow {217 const id = randomUUID();218 const params = defaultParams(today(), regionCode);219 params.escalateTo = null;220 void valuationDate;221 ctx.db.prepare("INSERT INTO metre_estimates(id,project_id,name,params_json,lines_json,created_by) VALUES(?,?,?,?,'[]',?)").run(id, projectId, name.trim().slice(0, 120) || "Estimation", JSON.stringify(params), ctx.user.id);222 audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: "estimate.create", entityType: "estimate", entityId: id, projectId, ip: ctx.ip });223 return getEstimate(ctx.db, id)!;224}225226export function sanitizeParams(raw: Record<string, unknown>, base: EstimateParams): EstimateParams {227 const pct = (k: keyof EstimateParams, max = 50) => { const v = raw[k]; if (v == null) return base[k] as number; const x = Number(v); if (!Number.isFinite(x) || x < 0 || x > max) throw new HttpError(400, `Paramètre ${k} invalide (0–${max}).`); return x; };228 const date = (k: "priceDate" | "escalateTo") => { const v = raw[k]; if (v === undefined) return base[k]; if (v === null || v === "") return k === "priceDate" ? today() : null; if (typeof v !== "string" || !/^\d{4}-\d{2}-\d{2}$/.test(v)) throw new HttpError(400, `Date ${k} invalide.`); return v; };229 const p: EstimateParams = {230 regionCode: raw.regionCode === undefined ? base.regionCode : raw.regionCode ? String(raw.regionCode).slice(0, 20) : null,231 priceDate: date("priceDate") as string, generalConditionsPct: pct("generalConditionsPct"), overheadPct: pct("overheadPct"), profitPct: pct("profitPct"), contingencyPct: pct("contingencyPct"),232 escalateTo: date("escalateTo") as string | null, escalationFactor: raw.escalationFactor === undefined ? base.escalationFactor : raw.escalationFactor == null ? null : Number(raw.escalationFactor), escalationSource: raw.escalationSource === undefined ? base.escalationSource : raw.escalationSource ? String(raw.escalationSource).slice(0, 200) : null,233 applyGst: typeof raw.applyGst === "boolean" ? raw.applyGst : base.applyGst, applyQst: typeof raw.applyQst === "boolean" ? raw.applyQst : base.applyQst, roundingCents: [1, 100, 10000].includes(Number(raw.roundingCents)) ? Number(raw.roundingCents) : base.roundingCents,234 rsmeansLocationId: raw.rsmeansLocationId === undefined ? (base.rsmeansLocationId ?? null) : raw.rsmeansLocationId == null || raw.rsmeansLocationId === "" ? null : Number(raw.rsmeansLocationId) || null,235 rsmeansLabor: ["STD", "OPN", "RES", "FMR"].includes(String(raw.rsmeansLabor)) ? (raw.rsmeansLabor as EstimateParams["rsmeansLabor"]) : (base.rsmeansLabor ?? "STD"),236 };237 if (p.escalationFactor != null && (!Number.isFinite(p.escalationFactor) || p.escalationFactor <= 0 || p.escalationFactor > 5)) throw new HttpError(400, "Facteur d'actualisation invalide.");238 // facteur automatique (indice) si une date cible est donnée sans facteur saisi239 if (p.escalateTo && (raw.escalationFactor === undefined || raw.escalationFactor === null) && raw.escalateTo !== undefined) {240 const f = escalationFactor(p.priceDate, p.escalateTo);241 p.escalationFactor = f?.factor ?? null; p.escalationSource = f?.source ?? null;242 }243 if (!p.escalateTo) { p.escalationFactor = null; p.escalationSource = null; }244 return p;245}246247const BASES: PriceBasis[] = ["catalogue", "custom_assembly", "assumption", "observation", "rsmeans"];248249export function sanitizeLines(raw: unknown, d: Database.Database, projectId: string): EstimateLineInput[] {250 if (!Array.isArray(raw)) throw new HttpError(400, "Lignes invalides.");251 if (raw.length > 2000) throw new HttpError(400, "Trop de lignes (max 2 000).");252 return raw.map((x, i) => {253 const r = x as Record<string, unknown>;254 const num = (k: string, def: number, lo: number, hi: number) => { const v = r[k]; if (v == null || v === "") return def; const n = Number(v); if (!Number.isFinite(n) || n < lo || n > hi) throw new HttpError(400, `Ligne ${i + 1} : ${k} invalide.`); return n; };255 const measurementId = typeof r.measurementId === "string" && /^[0-9a-f-]{36}$/i.test(r.measurementId) ? r.measurementId : null;256 let quantity = num("quantity", 0, 0, 1e9);257 let unit = typeof r.unit === "string" ? r.unit.slice(0, 10) : "unit";258 if (measurementId) {259 const m = getMeasurement(d, measurementId);260 if (!m || m.projectId !== projectId) throw new HttpError(400, `Ligne ${i + 1} : mesure inconnue ou d'un autre projet.`);261 quantity = m.value; unit = m.unit; // la quantité liée vient TOUJOURS du registre262 }263 const basis = BASES.includes(r.basis as PriceBasis) ? (r.basis as PriceBasis) : "catalogue";264 return {265 id: typeof r.id === "string" && /^[0-9a-f-]{36}$/i.test(r.id) ? r.id : randomUUID(), wbs: String(r.wbs ?? "00").slice(0, 60), description: String(r.description ?? "").slice(0, 200),266 assemblyCode: typeof r.assemblyCode === "string" && r.assemblyCode ? r.assemblyCode.toUpperCase().slice(0, 40) : null, measurementId, quantity, unit, adjustmentFactor: num("adjustmentFactor", 1, 0.0001, 1000),267 basis, unitPrice: r.unitPrice == null || r.unitPrice === "" ? null : num("unitPrice", 0, 0, 1e8), installed: !!r.installed, assumptionNote: typeof r.assumptionNote === "string" ? r.assumptionNote.slice(0, 300) : null, excluded: !!r.excluded, sortOrder: num("sortOrder", i, 0, 1e6),268 ...(r.origin === "qs" ? { origin: "qs" as const } : {}),269 ...(typeof r.rsmeansLine === "string" && /^\d{12}$/.test(r.rsmeansLine) ? { rsmeansLine: r.rsmeansLine } : {}),270 };271 });272}273274export function updateEstimate(ctx: Ctx, id: string, raw: { name?: unknown; params?: unknown; lines?: unknown }): EstimateRow {275 const e = getEstimate(ctx.db, id);276 if (!e) throw new HttpError(404, "Estimation introuvable.");277 const name = typeof raw.name === "string" && raw.name.trim() ? raw.name.trim().slice(0, 120) : e.name;278 const params = raw.params && typeof raw.params === "object" ? sanitizeParams(raw.params as Record<string, unknown>, e.params) : e.params;279 const lines = raw.lines !== undefined ? sanitizeLines(raw.lines, ctx.db, e.projectId) : e.lines;280 ctx.db.prepare("UPDATE metre_estimates SET name=?, params_json=?, lines_json=?, updated_at=? WHERE id=?").run(name, JSON.stringify(params), JSON.stringify(lines), nowIso(), id);281 return getEstimate(ctx.db, id)!;282}283284export function deleteEstimate(ctx: Ctx, id: string): void {285 ctx.db.prepare("UPDATE metre_estimates SET deleted_at=? WHERE id=?").run(nowIso(), id);286}287288/** Recalcul (les quantités liées sont relues dans le registre, les prix dans cost.db). */289export function computeEstimateRow(d: Database.Database, e: EstimateRow, overrideParams?: Partial<EstimateParams>): { result: EstimateResult; lines: EstimateLineInput[]; ctx: QuoteContext; staleLinks: string[] } {290 const params = { ...e.params, ...(overrideParams ?? {}) };291 const ctx = buildQuoteContext(d, e.projectId, params);292 const staleLinks: string[] = [];293 const lines = e.lines.map((l) => {294 if (!l.measurementId) return l;295 const m = getMeasurement(d, l.measurementId);296 if (!m) return { ...l, excluded: true, description: `${l.description} (mesure supprimée)` };297 if (m.stale) staleLinks.push(l.id);298 return { ...l, quantity: m.value, unit: m.unit };299 });300 const result = computeEstimate(lines, params, ctx);301 if (staleLinks.length) result.warnings.unshift(`${staleLinks.length} ligne(s) liée(s) à une mesure PÉRIMÉE (recalibration) : recalculez le registre`);302 return { result, lines, ctx, staleLinks };303}304305/** Révision immuable (instantané complet : lignes, paramètres, prix utilisés, versions). */306export function freezeVersion(ctx: Ctx, estimateId: string, label: string, scenario: string | null): EstimateVersion {307 const e = getEstimate(ctx.db, estimateId);308 if (!e) throw new HttpError(404, "Estimation introuvable.");309 const { result, lines, ctx: q, staleLinks } = computeEstimateRow(ctx.db, e);310 if (staleLinks.length) throw new HttpError(409, "Des lignes reposent sur des mesures périmées : recalculez le registre avant de figer une révision.");311 const unverified = lines.filter((l) => l.measurementId && !l.excluded).map((l) => getMeasurement(ctx.db, l.measurementId!)).filter((m) => m && !m.verifiedAt);312 if (unverified.length) result.warnings.push(`${unverified.length} mesure(s) liée(s) non vérifiée(s) manuellement`);313 const number = ((ctx.db.prepare("SELECT MAX(number) m FROM metre_estimate_versions WHERE estimate_id=?").get(estimateId) as { m: number | null }).m ?? 0) + 1;314 const snapshot = q.snapshot();315 const payload = JSON.stringify({ lines, params: e.params, result, snapshot, catalogueVersion: q.catalogueVersion, formulaVersion: ESTIMATE_FORMULA_VERSION });316 const hash = createHash("sha256").update(payload).digest("hex");317 const id = randomUUID();318 ctx.db.prepare("INSERT INTO metre_estimate_versions(id,estimate_id,project_id,number,label,scenario,lines_json,params_json,result_json,pricing_snapshot_json,catalogue_version,formula_version,content_hash,total,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")319 .run(id, estimateId, e.projectId, number, label.trim().slice(0, 120) || `Révision ${number}`, scenario ? scenario.slice(0, 60) : null, JSON.stringify(lines), JSON.stringify(e.params), JSON.stringify(result), JSON.stringify(snapshot), q.catalogueVersion, ESTIMATE_FORMULA_VERSION, hash, result.totals.total, ctx.user.id);320 audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: "estimate.freeze", entityType: "estimate_version", entityId: id, projectId: e.projectId, details: { number, total: result.totals.total, hash }, ip: ctx.ip });321 return getVersion(ctx.db, id)!;322}323324export function getVersion(d: Database.Database, id: string): EstimateVersion | null {325 const r = d.prepare("SELECT * FROM metre_estimate_versions WHERE id=?").get(id) as Record<string, unknown> | undefined;326 if (!r) return null;327 return { id: String(r.id), estimateId: String(r.estimate_id), projectId: String(r.project_id), number: Number(r.number), label: String(r.label), scenario: r.scenario as string | null, createdBy: String(r.created_by), createdAt: String(r.created_at), contentHash: String(r.content_hash), total: Number(r.total), result: JSON.parse(String(r.result_json)), lines: JSON.parse(String(r.lines_json)) };328}329330export function listVersions(d: Database.Database, projectId: string): Omit<EstimateVersion, "result" | "lines">[] {331 return (d.prepare("SELECT id, estimate_id, project_id, number, label, scenario, created_by, created_at, content_hash, total FROM metre_estimate_versions WHERE project_id=? ORDER BY created_at DESC").all(projectId) as Record<string, unknown>[])332 .map((r) => ({ id: String(r.id), estimateId: String(r.estimate_id), projectId: String(r.project_id), number: Number(r.number), label: String(r.label), scenario: r.scenario as string | null, createdBy: String(r.created_by), createdAt: String(r.created_at), contentHash: String(r.content_hash), total: Number(r.total) }));333}334335/** Compare des scénarios de paramètres sur la même estimation (sans sauvegarde). */336export function scenarios(d: Database.Database, e: EstimateRow, variants: { label: string; params: Partial<EstimateParams> }[]): { label: string; totals: EstimateResult["totals"]; deltaPct: number }[] {337 const base = computeEstimateRow(d, e).result.totals.total;338 return variants.slice(0, 8).map((v) => { const r = computeEstimateRow(d, e, sanitizeParams(v.params as Record<string, unknown>, e.params)).result; return { label: v.label.slice(0, 60), totals: r.totals, deltaPct: base > 0 ? Math.round(1000 * (r.totals.total / base - 1)) / 10 : 0 }; });339}340341/** Sensibilité ± sur les principaux paramètres. */342export function sensitivity(d: Database.Database, e: EstimateRow): { param: string; minus: number; plus: number; base: number }[] {343 const base = computeEstimateRow(d, e).result.totals.total;344 const keys: (keyof EstimateParams)[] = ["generalConditionsPct", "overheadPct", "profitPct", "contingencyPct"];345 const out = keys.map((k) => { const v = e.params[k] as number; const minus = computeEstimateRow(d, e, { [k]: Math.max(0, v - 2) }).result.totals.total; const plus = computeEstimateRow(d, e, { [k]: v + 2 }).result.totals.total; return { param: `${k} ±2 pts`, minus, plus, base }; });346 const q = (f: number) => computeEstimate(e.lines.map((l) => ({ ...l, adjustmentFactor: l.adjustmentFactor * f })), e.params, buildQuoteContext(d, e.projectId, e.params)).totals.total;347 out.push({ param: "quantités ±5 %", minus: q(0.95), plus: q(1.05), base });348 return out;349}350351/* ------------------------------------------------------------ évaluation */352353export interface ValuationCase { id: string; projectId: string; estimateVersionId: string | null; name: string; input: ValuationInput; result: ValuationResult | null; createdBy: string; createdAt: string; updatedAt: string }354355function mapCase(r: Record<string, unknown>): ValuationCase {356 return { id: String(r.id), projectId: String(r.project_id), estimateVersionId: r.estimate_version_id as string | null, name: String(r.name), input: JSON.parse(String(r.input_json)), result: r.result_json ? JSON.parse(String(r.result_json)) : null, createdBy: String(r.created_by), createdAt: String(r.created_at), updatedAt: String(r.updated_at) };357}358359export function listValuationCases(d: Database.Database, projectId: string): ValuationCase[] {360 return (d.prepare("SELECT * FROM metre_valuation_cases WHERE project_id=? AND deleted_at IS NULL ORDER BY created_at").all(projectId) as Record<string, unknown>[]).map(mapCase);361}362363export function sanitizeValuation(raw: Record<string, unknown>, costNew: number): ValuationInput {364 const n = (v: unknown, def: number, lo = 0, hi = 1e9) => { if (v == null || v === "") return def; const x = Number(v); if (!Number.isFinite(x) || x < lo || x > hi) throw new HttpError(400, "Valeur numérique invalide dans l'évaluation."); return x; };365 const ph = (raw.physical ?? {}) as Record<string, unknown>;366 const ex = (raw.external ?? {}) as Record<string, unknown>;367 const land = (raw.land ?? {}) as Record<string, unknown>;368 const functional: FunctionalItem[] = (Array.isArray(raw.functional) ? raw.functional : []).slice(0, 30).map((f) => { const x = f as Record<string, unknown>; return { id: typeof x.id === "string" ? x.id.slice(0, 36) : randomUUID(), label: String(x.label ?? "").slice(0, 160), curable: !!x.curable, amount: n(x.amount, 0), eliminatedByReplacement: !!x.eliminatedByReplacement }; });369 return {370 basis: raw.basis === "reproduction" ? "reproduction" : "replacement", costNew, basisAdjustmentPct: n(raw.basisAdjustmentPct, 0, -50, 50), siteImprovements: n(raw.siteImprovements, 0),371 physical: { method: ph.method === "percent" ? "percent" : "age_life", effectiveAge: ph.effectiveAge == null || ph.effectiveAge === "" ? null : n(ph.effectiveAge, 0, 0, 300), economicLife: n(ph.economicLife, 60, 1, 300), percent: ph.percent == null || ph.percent === "" ? null : n(ph.percent, 0, 0, 100) },372 functional, external: { amount: n(ex.amount, 0), note: typeof ex.note === "string" ? ex.note.slice(0, 500) : null }, land: { value: n(land.value, 0), source: ["role", "user", "market", "residual"].includes(String(land.source)) ? (land.source as ValuationInput["land"]["source"]) : "user", note: typeof land.note === "string" ? land.note.slice(0, 300) : null },373 };374}375376export function saveValuationCase(ctx: Ctx, projectId: string, raw: Record<string, unknown>, existingId?: string): ValuationCase {377 const vid = typeof raw.estimateVersionId === "string" && /^[0-9a-f-]{36}$/i.test(raw.estimateVersionId) ? raw.estimateVersionId : null;378 let costNew = 0;379 if (vid) { const v = getVersion(ctx.db, vid); if (!v || v.projectId !== projectId) throw new HttpError(400, "Révision d'estimation inconnue pour ce projet."); costNew = v.result.totals.subtotalBeforeTax; }380 else costNew = Number(raw.costNew ?? 0);381 if (!Number.isFinite(costNew) || costNew < 0) throw new HttpError(400, "Coût à neuf invalide.");382 const input = sanitizeValuation(raw, costNew);383 const result = computeValuation(input);384 const id = existingId ?? randomUUID();385 const name = String(raw.name ?? "Méthode du coût").trim().slice(0, 120);386 if (existingId) ctx.db.prepare("UPDATE metre_valuation_cases SET estimate_version_id=?, name=?, input_json=?, result_json=?, updated_at=? WHERE id=? AND project_id=?").run(vid, name, JSON.stringify(input), JSON.stringify(result), nowIso(), id, projectId);387 else ctx.db.prepare("INSERT INTO metre_valuation_cases(id,project_id,estimate_version_id,name,input_json,result_json,created_by) VALUES(?,?,?,?,?,?,?)").run(id, projectId, vid, name, JSON.stringify(input), JSON.stringify(result), ctx.user.id);388 audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: existingId ? "valuation.update" : "valuation.create", entityType: "valuation_case", entityId: id, projectId, details: { indicatedValue: result.indicatedValue }, ip: ctx.ip });389 return mapCase(ctx.db.prepare("SELECT * FROM metre_valuation_cases WHERE id=?").get(id) as Record<string, unknown>);390}391392export function deleteValuationCase(ctx: Ctx, id: string): void {393 ctx.db.prepare("UPDATE metre_valuation_cases SET deleted_at=? WHERE id=?").run(nowIso(), id);394}395396/* ------------------------------------------------ relevé des quantités (QS) */397398/**399 * Génère le relevé des quantités depuis tous les plans calibrés du projet et sa fiche technique :400 * une mesure « dérivée des plans » par ligne (formule, entrées, pages sources) + les lignes d'estimation401 * correspondantes (origine « qs », remplacées à chaque génération ; les lignes saisies sont conservées).402 */403export async function generateQuantitySurvey(ctx: Ctx, projectId: string, estimateId: string | null, opts: { pricing?: "catalogue" | "rsmeans" } = {}): Promise<{ estimate: EstimateRow; result: EstimateResult; qs: import("./quantity-survey").QsResult }> {404 const { quantitySurvey } = await import("./quantity-survey");405 const { defaultSpec } = await import("./templates");406 const { listPlanFiles, listObjects, createManualMeasurement, listMeasurements, deleteMeasurement } = await import("./plans");407 const { getProject } = await import("./projects");408 const d = ctx.db;409 const project = getProject(d, projectId); if (!project) throw new HttpError(404, "Projet introuvable.");410 const spec = defaultSpec((project.spec ?? {}) as Parameters<typeof defaultSpec>[0]);411 const levels = listPlanFiles(d, projectId).flatMap((f) => f.pages.map((pg) => ({ pageId: pg.id, level: pg.level ?? pg.label ?? "Rez-de-chaussée", objects: listObjects(d, pg.id), pxPerMeter: pg.calibration?.pxPerMeter ?? null })));412 if (!levels.length) throw new HttpError(400, "Aucun plan : dessinez ou importez au moins un niveau calibré.");413 const available = new Set(loadAssemblies(getCostDb()).keys());414 const qs = quantitySurvey(levels, spec, available);415 if (!qs.lines.length) throw new HttpError(400, `Relevé vide : ${qs.warnings.join(" ; ")}`);416 let e = estimateId ? getEstimate(d, estimateId) : listEstimates(d, projectId)[0] ?? null;417 if (!e) e = createEstimate(ctx, projectId, "Relevé des quantités", project.regionCode, project.valuationDate);418 // retirer l'ancien relevé (lignes « qs » + mesures « QS · »)419 for (const m of listMeasurements(d, projectId)) if (m.name.startsWith("QS · ")) deleteMeasurement(ctx, m.id);420 const kept = e.lines.filter((l) => l.origin !== "qs");421 const lines: EstimateLineInput[] = [...kept];422 for (const q of qs.lines) {423 const m = createManualMeasurement(ctx, projectId, { name: `QS · ${q.description}`, value: q.quantity, unit: q.unit, confidence: q.origin === "plan" ? "estimated" : "assumed", note: `${q.formula} — ${Object.entries(q.inputs).map(([k, v]) => `${k}=${v}`).join(", ")}${q.pageIds.length ? ` — pages ${q.pageIds.map((x) => x.slice(0, 8)).join(",")}` : ""} (${qs.version})` });424 const parts = opts.pricing === "rsmeans" && rsmeansAvailable() ? QS_RSMEANS_MAP[q.assemblyCode] : undefined;425 if (opts.pricing === "rsmeans" && !parts) qs.warnings.push(`sans correspondance RSMeans (catalogue conservé) : ${q.assemblyCode}`);426 if (!parts) { lines.push({ id: randomUUID(), wbs: q.wbs, description: q.description, assemblyCode: q.assemblyCode, measurementId: m.id, quantity: q.quantity, unit: q.unit, adjustmentFactor: 1, basis: "catalogue", unitPrice: null, installed: false, assumptionNote: null, excluded: false, sortOrder: lines.length, origin: "qs" }); continue; }427 for (const part of parts) {428 const f = part.qtyFactor ?? 1;429 const desc = `${q.description} — ${part.label} — RSMeans ${part.line}`.slice(0, 200);430 if (part.unit) lines.push({ id: randomUUID(), wbs: q.wbs, description: desc, assemblyCode: q.assemblyCode, measurementId: null, quantity: Math.round(q.quantity * f * 1000) / 1000, unit: part.unit, adjustmentFactor: 1, basis: "rsmeans", unitPrice: null, installed: false, assumptionNote: `${q.quantity} ${q.unit} × ${f} (${q.formula})`, excluded: false, sortOrder: lines.length, origin: "qs", rsmeansLine: part.line });431 else lines.push({ id: randomUUID(), wbs: q.wbs, description: desc, assemblyCode: q.assemblyCode, measurementId: m.id, quantity: q.quantity, unit: q.unit, adjustmentFactor: f, basis: "rsmeans", unitPrice: null, installed: false, assumptionNote: null, excluded: false, sortOrder: lines.length, origin: "qs", rsmeansLine: part.line });432 }433 }434 const upd = updateEstimate(ctx, e.id, { lines });435 audit(d, { userId: ctx.user.id, role: ctx.user.role, action: "estimate.quantity_survey", entityType: "estimate", entityId: e.id, projectId, details: { lines: qs.lines.length, warnings: qs.warnings, summary: qs.summary, pricing: opts.pricing ?? "catalogue" }, ip: ctx.ip });436 const { result } = computeEstimateRow(d, upd);437 return { estimate: upd, result, qs };438}439