spb/uqo-eval

1// UQO Éval — Métré : service des projets (CRUD, membres, préremplissage MAMH, tableau de bord). SERVEUR seulement.2import { randomUUID } from "crypto";3import type Database from "better-sqlite3";4import { audit, nowIso } from "./db";5import { HttpError, projectAccess, type Ctx } from "./auth";6import { copyPageTo } from "./plans";7import type { BuildingKind, MetreUser, Project, UnitSystem } from "./types";8import { getUnit } from "../db";9import { resolveLocation } from "../cost/catalog";10import { sqftFromM2 } from "../cost/units";11import { defaultSpec, type BuildingSpecQS } from "./templates";1213/** Fiche technique validée (bornes, listes) — fusionnée avec les défauts. */14export function sanitizeSpec(raw: Record<string, unknown>, base: Record<string, unknown> | null): Record<string, unknown> {15  const d = defaultSpec((base ?? {}) as Partial<BuildingSpecQS>);16  const pick = <T extends string>(k: string, allowed: T[], def: T): T => (allowed.includes(raw[k] as T) ? (raw[k] as T) : (d as unknown as Record<string, T>)[k] ?? def);17  const num = (k: string, lo: number, hi: number, def: number) => { const v = raw[k]; if (v == null || v === "") return (d as unknown as Record<string, number>)[k] ?? def; const n = Number(v); if (!Number.isFinite(n)) throw new HttpError(400, `Fiche : ${k} invalide.`); return Math.min(hi, Math.max(lo, n)); };18  const mix = (k: string) => { const v = raw[k]; if (!v || typeof v !== "object") return (d as unknown as Record<string, unknown>)[k]; return Object.fromEntries(Object.entries(v as Record<string, unknown>).filter(([, x]) => typeof x === "number" && x > 0).map(([kk, x]) => [kk.slice(0, 20), Math.min(1, Number(x))])); };19  const out: BuildingSpecQS = {20    units: num("units", 1, 60, 1), basement: pick("basement", ["none", "crawl", "unfinished", "finished"], "unfinished"), basementFinishedPct: num("basementFinishedPct", 0, 1, 0), foundation: pick("foundation", ["poured_concrete", "concrete_block", "slab_on_grade", "piers"], "poured_concrete"), foundationWallHeightM: num("foundationWallHeightM", 0.3, 4, 2.44),21    roofType: pick("roofType", ["asphalt_shingle", "metal", "membrane"], "asphalt_shingle"), roofGeometry: pick("roofGeometry", ["gable", "hip", "flat"], "gable"), roofPitch: num("roofPitch", 0, 18, 6), siding: mix("siding") as BuildingSpecQS["siding"], windows: pick("windows", ["pvc", "hybrid", "aluminum", "wood"], "pvc"),22    heating: pick("heating", ["electric_baseboard", "heat_pump", "furnace_electric", "furnace_gas"], "electric_baseboard"), hasAirConditioning: typeof raw.hasAirConditioning === "boolean" ? raw.hasAirConditioning : d.hasAirConditioning, hasAirExchanger: typeof raw.hasAirExchanger === "boolean" ? raw.hasAirExchanger : d.hasAirExchanger,23    quality: pick("quality", ["economy", "standard", "superior", "prestige"], "standard"), kitchenQuality: pick("kitchenQuality", ["economy", "standard", "superior", "prestige"], "standard"), bathroomQuality: pick("bathroomQuality", ["economy", "standard", "superior", "prestige"], "standard"), flooring: mix("flooring") as BuildingSpecQS["flooring"],24    ceilingHeightM: num("ceilingHeightM", 2, 6, 2.44), garage: pick("garage", ["none", "attached", "detached", "integrated"], "none"), deckM2: num("deckM2", 0, 2000, 0), drivewayM2: num("drivewayM2", 0, 5000, 0), driveway: pick("driveway", ["asphalt", "pavers", "gravel", "none"], "none"), fenceM: num("fenceM", 0, 2000, 0), landscapingM2: num("landscapingM2", 0, 50000, 0), notes: typeof raw.notes === "string" ? raw.notes.slice(0, 1000) : d.notes,25  };26  return out as unknown as Record<string, unknown>;27}2829const KINDS: BuildingKind[] = ["detached", "semi_detached", "row", "plex", "condo", "chalet", "commercial", "other"];3031interface Row { id: string; owner_id: string; name: string; address: string | null; municipality: string | null; region_code: string | null; property_id: string | null; valuation_date: string; building_kind: BuildingKind; stories: number; unit_system: UnitSystem; notes: string | null; is_template: number; locked: number; spec_json: string | null; course: string | null; created_at: string; updated_at: string }3233const map = (r: Row): Project => ({ id: r.id, ownerId: r.owner_id, name: r.name, address: r.address, municipality: r.municipality, regionCode: r.region_code, propertyId: r.property_id, valuationDate: r.valuation_date, buildingKind: r.building_kind, stories: r.stories, unitSystem: r.unit_system, notes: r.notes, isTemplate: !!r.is_template, locked: !!r.locked, spec: r.spec_json ? (JSON.parse(r.spec_json) as Record<string, unknown>) : null, createdAt: r.created_at, updatedAt: r.updated_at });3435export function getProject(d: Database.Database, id: string): Project | null {36  const r = d.prepare("SELECT * FROM metre_projects WHERE id=? AND deleted_at IS NULL").get(id) as Row | undefined;37  return r ? map(r) : null;38}3940export function sanitizeProject(raw: Record<string, unknown>, base?: Project): Omit<Project, "id" | "ownerId" | "createdAt" | "updatedAt"> {41  const s = (k: string, max: number, def: string | null = null): string | null => { const v = raw[k]; return typeof v === "string" && v.trim() ? v.trim().slice(0, max) : (base ? (base as unknown as Record<string, unknown>)[k] as string | null : def); };42  const name = s("name", 120, null);43  if (!name) throw new HttpError(400, "Nom de projet requis.");44  const vd = s("valuationDate", 10, nowIso().slice(0, 10))!;45  if (!/^\d{4}-\d{2}-\d{2}$/.test(vd)) throw new HttpError(400, "Date d'évaluation invalide (AAAA-MM-JJ).");46  const kind = raw.buildingKind ?? base?.buildingKind ?? "detached";47  const stories = Number(raw.stories ?? base?.stories ?? 1);48  return {49    name, address: s("address", 200), municipality: s("municipality", 120), regionCode: s("regionCode", 20), propertyId: s("propertyId", 40), valuationDate: vd,50    buildingKind: KINDS.includes(kind as BuildingKind) ? (kind as BuildingKind) : "detached", stories: Number.isFinite(stories) ? Math.min(60, Math.max(1, stories)) : 1,51    unitSystem: (raw.unitSystem ?? base?.unitSystem) === "imperial" ? "imperial" : "metric", notes: s("notes", 4000), isTemplate: typeof raw.isTemplate === "boolean" ? raw.isTemplate : base?.isTemplate ?? false, locked: base?.locked ?? false, spec: raw.spec && typeof raw.spec === "object" ? sanitizeSpec(raw.spec as Record<string, unknown>, base?.spec ?? null) : base?.spec ?? null,52  };53}5455export function createProject(ctx: Ctx, raw: Record<string, unknown>): Project {56  const p = sanitizeProject(raw);57  if (p.isTemplate && ctx.user.role === "student") throw new HttpError(403, "Seuls les enseignants créent des gabarits.");58  const id = randomUUID();59  const course = (ctx.db.prepare("SELECT course FROM metre_users WHERE id=?").get(ctx.user.id) as { course: string | null } | undefined)?.course ?? null;60  ctx.db.prepare("INSERT INTO metre_projects(id,owner_id,name,address,municipality,region_code,property_id,valuation_date,building_kind,stories,unit_system,notes,is_template,course,spec_json) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")61    .run(id, ctx.user.id, p.name, p.address, p.municipality, p.regionCode, p.propertyId, p.valuationDate, p.buildingKind, p.stories, p.unitSystem, p.notes, p.isTemplate ? 1 : 0, course, p.spec ? JSON.stringify(p.spec) : null);62  audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: "project.create", entityType: "project", entityId: id, projectId: id, ip: ctx.ip });63  return getProject(ctx.db, id)!;64}6566export function updateProject(ctx: Ctx, id: string, raw: Record<string, unknown>): Project {67  const base = getProject(ctx.db, id);68  if (!base) throw new HttpError(404, "Projet introuvable.");69  const p = sanitizeProject({ ...(base as unknown as Record<string, unknown>), ...raw }, base);70  if (p.isTemplate !== base.isTemplate && ctx.user.role === "student") throw new HttpError(403, "Seuls les enseignants gèrent les gabarits.");71  ctx.db.prepare("UPDATE metre_projects SET name=?,address=?,municipality=?,region_code=?,property_id=?,valuation_date=?,building_kind=?,stories=?,unit_system=?,notes=?,is_template=?,spec_json=?,updated_at=? WHERE id=?")72    .run(p.name, p.address, p.municipality, p.regionCode, p.propertyId, p.valuationDate, p.buildingKind, p.stories, p.unitSystem, p.notes, p.isTemplate ? 1 : 0, p.spec ? JSON.stringify(p.spec) : null, nowIso(), id);73  audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: "project.update", entityType: "project", entityId: id, projectId: id, details: Object.keys(raw), ip: ctx.ip });74  return getProject(ctx.db, id)!;75}7677export function deleteProject(ctx: Ctx, id: string): void {78  ctx.db.prepare("UPDATE metre_projects SET deleted_at=? WHERE id=?").run(nowIso(), id);79  audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: "project.delete", entityType: "project", entityId: id, projectId: id, ip: ctx.ip });80}8182/** Tous les projets (plateforme ouverte), avec le droit effectif de l'utilisateur et quelques compteurs. */83export function listProjects(d: Database.Database, user: MetreUser): (Project & { access: string; versions: number; lastVersionAt: string | null; ownerName: string; measurements: number; pages: number; mine: boolean })[] {84  const rows = d.prepare(`SELECT p.*, u.display_name owner_name,85      (SELECT COUNT(*) FROM metre_estimate_versions v WHERE v.project_id=p.id) versions, (SELECT MAX(created_at) FROM metre_estimate_versions v WHERE v.project_id=p.id) last_version,86      (SELECT COUNT(*) FROM metre_measurements m WHERE m.project_id=p.id AND m.deleted_at IS NULL) measurements, (SELECT COUNT(*) FROM metre_plan_pages g WHERE g.project_id=p.id) pages87    FROM metre_projects p JOIN metre_users u ON u.id=p.owner_id WHERE p.deleted_at IS NULL ORDER BY p.is_template DESC, p.updated_at DESC`).all() as (Row & { owner_name: string; versions: number; last_version: string | null; measurements: number; pages: number; locked: number })[];88  return rows.map((r) => ({ ...map(r), access: projectAccess(d, user, r.id) ?? "read", versions: r.versions, lastVersionAt: r.last_version, ownerName: r.owner_name, measurements: r.measurements, pages: r.pages, mine: r.owner_id === user.id }));89}9091/** Copie d'un projet (gabarit ou autre) : fiche, mesures manuelles, recettes, estimations (état de travail) — sans les plans ni les révisions. */92export function duplicateProject(ctx: Ctx, sourceId: string, name: string | null): Project {93  const src = getProject(ctx.db, sourceId);94  if (!src) throw new HttpError(404, "Projet introuvable.");95  const id = randomUUID();96  const d = ctx.db;97  const pagesToCopy: string[] = [];98  const tx = d.transaction(() => {99    d.prepare("INSERT INTO metre_projects(id,owner_id,name,address,municipality,region_code,property_id,valuation_date,building_kind,stories,unit_system,notes,is_template,course,spec_json) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,0,NULL,?)")100      .run(id, ctx.user.id, (name ?? `${src.name} — copie`).slice(0, 120), src.address, src.municipality, src.regionCode, src.propertyId, src.valuationDate, src.buildingKind, src.stories, src.unitSystem, src.notes, src.spec ? JSON.stringify(src.spec) : null);101    // plans copiés (pages, calibration, objets)102    for (const f of d.prepare("SELECT id FROM metre_plan_files WHERE project_id=?").all(sourceId) as { id: string }[]) for (const pg of d.prepare("SELECT id FROM metre_plan_pages WHERE file_id=?").all(f.id) as { id: string }[]) pagesToCopy.push(pg.id);103    const mmap = new Map<string, string>();104    for (const m of d.prepare("SELECT * FROM metre_measurements WHERE project_id=? AND deleted_at IS NULL AND object_id IS NULL").all(sourceId) as Record<string, unknown>[]) {105      const nid = randomUUID(); mmap.set(String(m.id), nid);106      d.prepare("INSERT INTO metre_measurements(id,project_id,object_id,page_id,name,formula,formula_text,inputs_json,value,unit,calibration_revision,stale,confidence,lineage_json,formula_version,computed_at) VALUES(?,?,NULL,NULL,?,?,?,?,?,?,NULL,0,?,'[]',?,?)")107        .run(nid, id, m.name, m.formula, m.formula_text, m.inputs_json, m.value, m.unit, m.confidence, m.formula_version, nowIso());108    }109    for (const a of d.prepare("SELECT * FROM metre_assemblies WHERE project_id=? AND deleted_at IS NULL").all(sourceId) as Record<string, unknown>[]) {110      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(?,?,?,?,?,?,?,?,1,?,?,?)").run(randomUUID(), id, a.code, a.name_fr, a.name_en, a.unit, a.output_quantity, a.category, a.components_json, a.exclusions, ctx.user.id);111    }112    for (const e of d.prepare("SELECT * FROM metre_estimates WHERE project_id=? AND deleted_at IS NULL").all(sourceId) as Record<string, unknown>[]) {113      const lines = (JSON.parse(String(e.lines_json)) as { id: string; measurementId: string | null }[]).map((l) => ({ ...l, id: randomUUID(), measurementId: l.measurementId ? mmap.get(l.measurementId) ?? null : null }));114      d.prepare("INSERT INTO metre_estimates(id,project_id,name,params_json,lines_json,created_by) VALUES(?,?,?,?,?,?)").run(randomUUID(), id, e.name, e.params_json, JSON.stringify(lines), ctx.user.id);115    }116  });117  tx();118  for (const pg of pagesToCopy) copyPageTo(ctx, pg, id);119  audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: "project.duplicate", entityType: "project", entityId: id, projectId: id, details: { from: sourceId, pages: pagesToCopy.length }, ip: ctx.ip });120  return getProject(ctx.db, id)!;121}122123export function setLocked(ctx: Ctx, id: string, locked: boolean): void {124  ctx.db.prepare("UPDATE metre_projects SET locked=?, updated_at=? WHERE id=?").run(locked ? 1 : 0, nowIso(), id);125  audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: locked ? "project.lock" : "project.unlock", entityType: "project", entityId: id, projectId: id, ip: ctx.ip });126}127128export function listMembers(d: Database.Database, projectId: string): { userId: string; displayName: string; role: string }[] {129  return (d.prepare("SELECT m.user_id userId, u.display_name displayName, m.role FROM metre_project_members m JOIN metre_users u ON u.id=m.user_id WHERE m.project_id=?").all(projectId) as { userId: string; displayName: string; role: string }[]);130}131132export function addMember(ctx: Ctx, projectId: string, displayName: string, role: "viewer" | "editor"): void {133  const u = ctx.db.prepare("SELECT id FROM metre_users WHERE display_name=? ORDER BY last_seen_at DESC LIMIT 1").get(displayName.trim()) as { id: string } | undefined;134  if (!u) throw new HttpError(404, "Utilisateur introuvable (nom d'affichage exact requis).");135  ctx.db.prepare("INSERT INTO metre_project_members(project_id,user_id,role,added_by) VALUES(?,?,?,?) ON CONFLICT(project_id,user_id) DO UPDATE SET role=excluded.role").run(projectId, u.id, role, ctx.user.id);136  audit(ctx.db, { userId: ctx.user.id, role: ctx.user.role, action: "project.member.add", entityType: "project", entityId: projectId, projectId, details: { user: u.id, role }, ip: ctx.ip });137}138139export function removeMember(ctx: Ctx, projectId: string, userId: string): void {140  ctx.db.prepare("DELETE FROM metre_project_members WHERE project_id=? AND user_id=?").run(projectId, userId);141}142143/** Préremplissage depuis une unité d'évaluation du rôle (MAMH) — lecture de vraiprix.db. */144export function prefillFromMamh(id: string): { address: string | null; municipality: string | null; regionCode: string | null; buildingKind: BuildingKind; stories: number; grossFloorAreaM2: number | null; grossFloorAreaSqft: number | null; yearBuilt: number | null; landValue: number | null; buildingValue: number | null; units: number | null; source: string } | null {145  const u = getUnit(id.replace(/\D/g, "") || id);146  if (!u) return null;147  const lien = (u.lien_physique ?? "").toLowerCase();148  const kind: BuildingKind = u.type_prop === "plex" ? "plex" : u.type_prop === "condo_ou_multi" ? "condo" : u.type_prop === "chalet" ? "chalet" : lien.includes("jumel") ? "semi_detached" : lien.includes("rang") ? "row" : u.type_prop === "unifamilial" ? "detached" : "other";149  let regionCode: string | null = null;150  try { regionCode = resolveLocation({ municipality: u.municipalite, lat: u.lat, lng: u.lng }).location.code; } catch { /* base de coûts indisponible */ }151  return {152    address: [u.adresse, u.apt ? `app. ${u.apt}` : null].filter(Boolean).join(", ") || null, municipality: u.municipalite, regionCode, buildingKind: kind, stories: u.nb_etages && u.nb_etages > 0 ? Math.round(u.nb_etages) : 1,153    grossFloorAreaM2: u.aire_etages_m2 ?? null, grossFloorAreaSqft: u.aire_etages_m2 ? Math.round(sqftFromM2(u.aire_etages_m2)) : null, yearBuilt: u.annee_construction ?? null, landValue: u.valeur_terrain ?? null, buildingValue: u.valeur_batiment ?? null, units: u.nb_logements ?? null,154    source: "Rôle d'évaluation foncière 2026 (MAMH, données ouvertes) via UQO Éval",155  };156}157