spb/vrai-prix

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2/**3 * Base de coûts `data/cost.db` (SQLite, better-sqlite3) : schéma versionné,4 * migrations forward-only, seeds idempotents (articles, assemblages,5 * localisations, sources, règles de condition). SERVEUR seulement.6 *7 * L'interface ne lit que cette base : aucun connecteur web n'est appelé dans8 * le chemin utilisateur (scripts/cost-sync.ts alimente la base en arrière-plan).9 */10import Database from "better-sqlite3";11import path from "path";12import fs from "fs";13import { ITEM_SEEDS } from "./seed/items";14import { ASSEMBLY_SEEDS, ASSEMBLY_VERSION } from "./seed/assemblies";15import { LOCATION_SEEDS } from "./seed/locations";16import { SOURCE_SEEDS } from "./seed/sources";17import { CONDITIONS, CONDITION_GROUPS, MASTERFORMAT } from "./taxonomy";1819export const COST_DB_SCHEMA_VERSION = 3;2021let db: Database.Database | null = null;2223export function costDbPath(): string {24  return process.env.COST_DB ?? path.join(process.cwd(), "data", "cost.db");25}2627export function getCostDb(): Database.Database {28  if (db) return db;29  const p = costDbPath();30  fs.mkdirSync(path.dirname(p), { recursive: true });31  db = new Database(p);32  db.pragma("journal_mode = WAL");33  db.pragma("foreign_keys = ON");34  migrate(db);35  seed(db);36  return db;37}3839/** Pour les tests : base en mémoire isolée. */40export function openCostDbAt(p: string): Database.Database {41  const d = new Database(p);42  d.pragma("journal_mode = MEMORY");43  d.pragma("foreign_keys = ON");44  migrate(d);45  seed(d);46  return d;47}4849/* ----------------------------------------------------------------- schéma */5051const SCHEMA = `52CREATE TABLE IF NOT EXISTS cost_meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);5354CREATE TABLE IF NOT EXISTS cost_sources (55  id INTEGER PRIMARY KEY AUTOINCREMENT,56  key TEXT UNIQUE NOT NULL,57  name TEXT NOT NULL,58  source_type TEXT NOT NULL,59  base_url TEXT,60  jurisdiction TEXT,61  license_status TEXT NOT NULL DEFAULT 'unknown',62  license_notes TEXT,63  terms_url TEXT,64  is_active INTEGER NOT NULL DEFAULT 1,65  priority INTEGER NOT NULL DEFAULT 5,66  quality REAL NOT NULL DEFAULT 0.5,67  refresh_frequency TEXT,68  last_successful_sync TEXT,69  last_error TEXT,70  created_at TEXT NOT NULL DEFAULT (datetime('now')),71  updated_at TEXT NOT NULL DEFAULT (datetime('now'))72);7374CREATE TABLE IF NOT EXISTS cost_raw_observations (75  id INTEGER PRIMARY KEY AUTOINCREMENT,76  source_id INTEGER NOT NULL REFERENCES cost_sources(id),77  external_id TEXT,78  source_url TEXT,79  retrieved_at TEXT NOT NULL,80  effective_date TEXT,81  raw_title TEXT,82  raw_description TEXT,83  raw_unit TEXT,84  raw_price REAL,85  raw_regular_price REAL,86  raw_sale_price REAL,87  raw_currency TEXT,88  raw_location TEXT,89  raw_category TEXT,90  raw_payload_json TEXT,91  content_hash TEXT,92  parser_version TEXT,93  status TEXT NOT NULL DEFAULT 'new',94  reject_reason TEXT,95  created_at TEXT NOT NULL DEFAULT (datetime('now'))96);97CREATE INDEX IF NOT EXISTS idx_raw_obs_source_date ON cost_raw_observations(source_id, retrieved_at);98CREATE INDEX IF NOT EXISTS idx_raw_obs_hash ON cost_raw_observations(content_hash);99100CREATE TABLE IF NOT EXISTS cost_items (101  id INTEGER PRIMARY KEY AUTOINCREMENT,102  canonical_code TEXT UNIQUE NOT NULL,103  masterformat_code TEXT NOT NULL,104  division TEXT NOT NULL,105  category TEXT NOT NULL,106  subcategory TEXT,107  name_fr TEXT NOT NULL,108  name_en TEXT NOT NULL,109  description_fr TEXT,110  description_en TEXT,111  unit TEXT NOT NULL,112  material_class TEXT,113  trade TEXT,114  default_waste_pct REAL NOT NULL DEFAULT 0,115  residential_relevance INTEGER NOT NULL DEFAULT 1,116  quality_level TEXT,117  reference_price REAL,118  reference_note TEXT,119  retail_query TEXT,120  retail_pack_qty REAL,121  retail_pack_unit TEXT,122  active INTEGER NOT NULL DEFAULT 1,123  created_at TEXT NOT NULL DEFAULT (datetime('now')),124  updated_at TEXT NOT NULL DEFAULT (datetime('now'))125);126127/* URL produit par détaillant pour un article (découverte par connecteur / matching validé) */128CREATE TABLE IF NOT EXISTS cost_item_sources (129  id INTEGER PRIMARY KEY AUTOINCREMENT,130  cost_item_id INTEGER NOT NULL REFERENCES cost_items(id),131  source_id INTEGER NOT NULL REFERENCES cost_sources(id),132  product_url TEXT NOT NULL,133  product_title TEXT,134  external_id TEXT,135  pack_qty REAL,136  pack_unit TEXT,137  match_method TEXT,138  match_confidence REAL,139  approved INTEGER NOT NULL DEFAULT 0,140  active INTEGER NOT NULL DEFAULT 1,141  created_at TEXT NOT NULL DEFAULT (datetime('now')),142  UNIQUE(source_id, product_url)143);144145CREATE TABLE IF NOT EXISTS cost_item_prices (146  id INTEGER PRIMARY KEY AUTOINCREMENT,147  cost_item_id INTEGER NOT NULL REFERENCES cost_items(id),148  source_id INTEGER NOT NULL REFERENCES cost_sources(id),149  location_code TEXT,150  observation_date TEXT NOT NULL,151  price_kind TEXT NOT NULL DEFAULT 'observed',152  material_cost REAL,153  labour_cost REAL,154  equipment_cost REAL,155  total_cost REAL NOT NULL,156  low_cost REAL,157  median_cost REAL,158  high_cost REAL,159  currency TEXT NOT NULL DEFAULT 'CAD',160  source_unit TEXT,161  conversion_factor REAL NOT NULL DEFAULT 1,162  is_regular_price INTEGER NOT NULL DEFAULT 1,163  is_outlier INTEGER NOT NULL DEFAULT 0,164  outlier_reason TEXT,165  confidence_score REAL,166  source_url TEXT,167  raw_observation_id INTEGER REFERENCES cost_raw_observations(id),168  verified INTEGER NOT NULL DEFAULT 0,169  created_at TEXT NOT NULL DEFAULT (datetime('now'))170);171CREATE INDEX IF NOT EXISTS idx_item_prices_item_date ON cost_item_prices(cost_item_id, observation_date);172173CREATE TABLE IF NOT EXISTS labour_rates (174  id INTEGER PRIMARY KEY AUTOINCREMENT,175  trade_code TEXT NOT NULL,176  trade_name_fr TEXT NOT NULL,177  trade_name_en TEXT,178  sector TEXT NOT NULL,179  classification TEXT NOT NULL DEFAULT 'compagnon',180  region TEXT NOT NULL DEFAULT 'QC',181  effective_from TEXT NOT NULL,182  effective_to TEXT,183  base_wage REAL NOT NULL,184  vacation_cost REAL NOT NULL DEFAULT 0,185  benefits_cost REAL NOT NULL DEFAULT 0,186  employer_contributions REAL NOT NULL DEFAULT 0,187  other_contributions REAL NOT NULL DEFAULT 0,188  total_employer_cost REAL NOT NULL,189  source_id INTEGER REFERENCES cost_sources(id),190  source_url TEXT,191  confidence_score REAL,192  raw_observation_id INTEGER REFERENCES cost_raw_observations(id),193  created_at TEXT NOT NULL DEFAULT (datetime('now')),194  UNIQUE(trade_code, sector, classification, region, effective_from, source_id)195);196197CREATE TABLE IF NOT EXISTS cost_locations (198  id INTEGER PRIMARY KEY AUTOINCREMENT,199  code TEXT UNIQUE NOT NULL,200  municipality_code TEXT,201  municipality_name TEXT,202  name_fr TEXT NOT NULL,203  name_en TEXT NOT NULL,204  region_code TEXT,205  region_name TEXT,206  latitude REAL,207  longitude REAL,208  radius_km REAL,209  municipalities_json TEXT,210  material_factor REAL NOT NULL DEFAULT 1,211  labour_factor REAL NOT NULL DEFAULT 1,212  equipment_factor REAL NOT NULL DEFAULT 1,213  overall_factor REAL NOT NULL DEFAULT 1,214  effective_date TEXT NOT NULL,215  source_method TEXT,216  confidence_score REAL,217  created_at TEXT NOT NULL DEFAULT (datetime('now'))218);219220CREATE TABLE IF NOT EXISTS cost_assemblies (221  id INTEGER PRIMARY KEY AUTOINCREMENT,222  assembly_code TEXT UNIQUE NOT NULL,223  masterformat_code TEXT NOT NULL,224  category TEXT NOT NULL,225  name_fr TEXT NOT NULL,226  name_en TEXT NOT NULL,227  description_fr TEXT,228  description_en TEXT,229  unit TEXT NOT NULL,230  building_type TEXT,231  quality_level TEXT,232  economic_life INTEGER,233  condition_group TEXT,234  version INTEGER NOT NULL DEFAULT 1,235  active INTEGER NOT NULL DEFAULT 1,236  created_at TEXT NOT NULL DEFAULT (datetime('now')),237  updated_at TEXT NOT NULL DEFAULT (datetime('now'))238);239240CREATE TABLE IF NOT EXISTS cost_assembly_components (241  id INTEGER PRIMARY KEY AUTOINCREMENT,242  assembly_id INTEGER NOT NULL REFERENCES cost_assemblies(id) ON DELETE CASCADE,243  cost_item_id INTEGER REFERENCES cost_items(id),244  quantity_per_assembly_unit REAL NOT NULL DEFAULT 0,245  waste_factor REAL NOT NULL DEFAULT 0,246  labour_hours REAL NOT NULL DEFAULT 0,247  trade TEXT,248  equipment_cost REAL NOT NULL DEFAULT 0,249  sequence INTEGER NOT NULL DEFAULT 0,250  notes TEXT251);252CREATE INDEX IF NOT EXISTS idx_asm_comp ON cost_assembly_components(assembly_id);253254CREATE TABLE IF NOT EXISTS construction_cost_indices (255  id INTEGER PRIMARY KEY AUTOINCREMENT,256  source TEXT NOT NULL,257  index_code TEXT NOT NULL,258  geography TEXT NOT NULL,259  building_type TEXT NOT NULL,260  division TEXT NOT NULL DEFAULT 'aggregate',261  period TEXT NOT NULL,262  index_value REAL NOT NULL,263  pct_change_qoq REAL,264  pct_change_yoy REAL,265  retrieved_at TEXT NOT NULL,266  UNIQUE(index_code, period)267);268269CREATE TABLE IF NOT EXISTS cost_benchmarks (270  id INTEGER PRIMARY KEY AUTOINCREMENT,271  source TEXT NOT NULL,272  source_id INTEGER REFERENCES cost_sources(id),273  building_type TEXT NOT NULL,274  market TEXT NOT NULL,275  unit TEXT NOT NULL DEFAULT '$/pi2',276  low REAL NOT NULL,277  high REAL NOT NULL,278  midpoint REAL,279  year INTEGER NOT NULL,280  notes TEXT,281  is_demo INTEGER NOT NULL DEFAULT 0,282  created_at TEXT NOT NULL DEFAULT (datetime('now')),283  UNIQUE(source, building_type, market, year)284);285286CREATE TABLE IF NOT EXISTS component_condition_rules (287  id INTEGER PRIMARY KEY AUTOINCREMENT,288  condition_group TEXT NOT NULL,289  condition TEXT NOT NULL,290  effective_age_ratio REAL NOT NULL,291  economic_life INTEGER NOT NULL,292  notes TEXT,293  UNIQUE(condition_group, condition)294);295296CREATE TABLE IF NOT EXISTS cost_estimates (297  id TEXT PRIMARY KEY,298  property_id TEXT,299  listing_uid TEXT,300  user_session_id TEXT,301  mode TEXT NOT NULL,302  location_code TEXT,303  municipality TEXT,304  estimate_date TEXT NOT NULL,305  price_date TEXT NOT NULL,306  building_type TEXT,307  quality_level TEXT,308  area_sqft REAL,309  direct_cost REAL,310  indirect_cost REAL,311  contractor_overhead REAL,312  contractor_profit REAL,313  contingency REAL,314  replacement_cost_new REAL,315  rcn_low REAL,316  rcn_high REAL,317  physical_depreciation REAL,318  functional_obsolescence REAL,319  external_obsolescence REAL,320  depreciated_improvement_value REAL,321  land_value REAL,322  cost_approach_value REAL,323  confidence_score REAL,324  confidence_letter TEXT,325  method_version TEXT NOT NULL,326  cost_database_version TEXT NOT NULL,327  assembly_version TEXT NOT NULL,328  input_json TEXT NOT NULL,329  result_json TEXT NOT NULL,330  assumptions_json TEXT,331  created_at TEXT NOT NULL DEFAULT (datetime('now'))332);333CREATE INDEX IF NOT EXISTS idx_estimates_prop ON cost_estimates(property_id);334CREATE INDEX IF NOT EXISTS idx_estimates_listing ON cost_estimates(listing_uid);335336CREATE TABLE IF NOT EXISTS cost_estimate_lines (337  id INTEGER PRIMARY KEY AUTOINCREMENT,338  estimate_id TEXT NOT NULL REFERENCES cost_estimates(id) ON DELETE CASCADE,339  assembly_code TEXT NOT NULL,340  category TEXT NOT NULL,341  quantity REAL NOT NULL,342  unit TEXT NOT NULL,343  material_cost REAL,344  labour_cost REAL,345  equipment_cost REAL,346  direct_cost REAL,347  location_adjustment REAL,348  adjusted_cost REAL,349  source_summary TEXT,350  confidence_score REAL,351  calculation_json TEXT352);353CREATE INDEX IF NOT EXISTS idx_est_lines ON cost_estimate_lines(estimate_id);354355/* instantané des prix canoniques utilisés par une estimation (reproductibilité) */356CREATE TABLE IF NOT EXISTS cost_estimate_snapshots (357  estimate_id TEXT PRIMARY KEY REFERENCES cost_estimates(id) ON DELETE CASCADE,358  snapshot_date TEXT NOT NULL,359  prices_json TEXT NOT NULL,360  labour_json TEXT NOT NULL,361  location_json TEXT NOT NULL,362  created_at TEXT NOT NULL DEFAULT (datetime('now'))363);364365CREATE TABLE IF NOT EXISTS connector_runs (366  id INTEGER PRIMARY KEY AUTOINCREMENT,367  connector TEXT NOT NULL,368  started_at TEXT NOT NULL,369  finished_at TEXT,370  status TEXT NOT NULL DEFAULT 'running',371  pages INTEGER NOT NULL DEFAULT 0,372  observations INTEGER NOT NULL DEFAULT 0,373  accepted INTEGER NOT NULL DEFAULT 0,374  rejected INTEGER NOT NULL DEFAULT 0,375  unchanged INTEGER NOT NULL DEFAULT 0,376  errors INTEGER NOT NULL DEFAULT 0,377  duration_ms INTEGER,378  log_json TEXT379);380381/* correspondances produit détaillant → article canonique proposées (IA/déterministe) à valider */382CREATE TABLE IF NOT EXISTS product_mappings (383  id INTEGER PRIMARY KEY AUTOINCREMENT,384  source_id INTEGER NOT NULL REFERENCES cost_sources(id),385  product_url TEXT NOT NULL,386  product_title TEXT,387  proposed_item_code TEXT,388  pack_qty REAL,389  pack_unit TEXT,390  method TEXT NOT NULL,391  confidence REAL NOT NULL,392  rationale TEXT,393  status TEXT NOT NULL DEFAULT 'pending',394  reviewed_at TEXT,395  created_at TEXT NOT NULL DEFAULT (datetime('now')),396  UNIQUE(source_id, product_url)397);398399/* ---------------------------------------------- analyse IA des annonces */400CREATE TABLE IF NOT EXISTS listing_images (401  id INTEGER PRIMARY KEY AUTOINCREMENT,402  listing_uid TEXT NOT NULL,403  photo_id TEXT NOT NULL,404  source_url TEXT NOT NULL,405  position INTEGER,406  width INTEGER,407  height INTEGER,408  bytes INTEGER,409  hash TEXT,410  media_type TEXT,411  room_guess TEXT,412  quality_score REAL,413  selected_for_ai INTEGER NOT NULL DEFAULT 0,414  fetched_at TEXT NOT NULL,415  UNIQUE(listing_uid, source_url)416);417418CREATE TABLE IF NOT EXISTS listing_ai_analyses (419  id TEXT PRIMARY KEY,420  listing_uid TEXT NOT NULL,421  version INTEGER NOT NULL,422  model TEXT NOT NULL,423  prompt_version TEXT NOT NULL,424  schema_version TEXT NOT NULL,425  input_hash TEXT NOT NULL,426  status TEXT NOT NULL DEFAULT 'queued',427  stage TEXT,428  error TEXT,429  image_count INTEGER NOT NULL DEFAULT 0,430  output_json TEXT,431  compact_json TEXT,432  vector_json TEXT,433  confidence REAL,434  estimate_id TEXT,435  cost_snapshot_date TEXT,436  created_at TEXT NOT NULL DEFAULT (datetime('now')),437  completed_at TEXT438);439CREATE INDEX IF NOT EXISTS idx_ai_analyses_listing ON listing_ai_analyses(listing_uid, version);440CREATE INDEX IF NOT EXISTS idx_ai_analyses_hash ON listing_ai_analyses(listing_uid, input_hash, prompt_version, model);441442CREATE TABLE IF NOT EXISTS listing_ai_overrides (443  id INTEGER PRIMARY KEY AUTOINCREMENT,444  analysis_id TEXT NOT NULL REFERENCES listing_ai_analyses(id) ON DELETE CASCADE,445  field_path TEXT NOT NULL,446  original_value TEXT,447  new_value TEXT,448  user_id TEXT,449  created_at TEXT NOT NULL DEFAULT (datetime('now'))450);451452CREATE TABLE IF NOT EXISTS analysis_conflicts (453  id INTEGER PRIMARY KEY AUTOINCREMENT,454  analysis_id TEXT NOT NULL REFERENCES listing_ai_analyses(id) ON DELETE CASCADE,455  field TEXT NOT NULL,456  source_a TEXT NOT NULL,457  value_a TEXT,458  source_b TEXT NOT NULL,459  value_b TEXT,460  severity TEXT NOT NULL DEFAULT 'medium',461  status TEXT NOT NULL DEFAULT 'open'462);463464CREATE TABLE IF NOT EXISTS property_embeddings (465  id INTEGER PRIMARY KEY AUTOINCREMENT,466  listing_uid TEXT NOT NULL,467  analysis_id TEXT NOT NULL REFERENCES listing_ai_analyses(id) ON DELETE CASCADE,468  embedding_model TEXT NOT NULL,469  embedding_dimension INTEGER NOT NULL,470  embedding_json TEXT NOT NULL,471  canonical_text TEXT,472  created_at TEXT NOT NULL DEFAULT (datetime('now')),473  UNIQUE(analysis_id, embedding_model)474);475476CREATE TABLE IF NOT EXISTS ai_usage (477  id INTEGER PRIMARY KEY AUTOINCREMENT,478  analysis_id TEXT,479  purpose TEXT NOT NULL,480  model TEXT NOT NULL,481  input_images INTEGER NOT NULL DEFAULT 0,482  input_tokens INTEGER NOT NULL DEFAULT 0,483  output_tokens INTEGER NOT NULL DEFAULT 0,484  cache_read_tokens INTEGER NOT NULL DEFAULT 0,485  estimated_cost_usd REAL,486  latency_ms INTEGER,487  created_at TEXT NOT NULL DEFAULT (datetime('now'))488);489`;490491function migrate(d: Database.Database): void {492  d.exec(SCHEMA);493  const cur = Number((d.prepare("SELECT value FROM cost_meta WHERE key='schema_version'").get() as { value: string } | undefined)?.value ?? 0);494  // migrations incrémentales (forward-only) : ajouter ici les ALTER TABLE futurs495  if (cur < 2) {496    // v2 : colonnes de prix promotionnel déjà dans SCHEMA (création) — no-op pour bases existantes récentes497  }498  if (cur < 3) {499    // v3 : ai_usage.cache_read_tokens (ajout tolérant)500    try { d.exec("ALTER TABLE ai_usage ADD COLUMN cache_read_tokens INTEGER NOT NULL DEFAULT 0"); } catch { /* déjà présent */ }501  }502  d.prepare("INSERT INTO cost_meta(key,value) VALUES('schema_version',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(String(COST_DB_SCHEMA_VERSION));503}504505/* ------------------------------------------------------------------ seeds */506507function seed(d: Database.Database): void {508  const seedVersion = `${COST_DB_SCHEMA_VERSION}.${ASSEMBLY_VERSION}.${ITEM_SEEDS.length}.${ASSEMBLY_SEEDS.length}.${LOCATION_SEEDS.length}`;509  const done = (d.prepare("SELECT value FROM cost_meta WHERE key='seed_version'").get() as { value: string } | undefined)?.value;510  if (done === seedVersion) return;511512  const tx = d.transaction(() => {513    // sources514    const upSrc = d.prepare(`INSERT INTO cost_sources(key,name,source_type,base_url,jurisdiction,license_status,license_notes,terms_url,is_active,priority,quality,refresh_frequency)515      VALUES(@key,@name,@sourceType,@baseUrl,@jurisdiction,@licenseStatus,@licenseNotes,@termsUrl,@isActive,@priority,@quality,@refreshFrequency)516      ON CONFLICT(key) DO UPDATE SET name=excluded.name, source_type=excluded.source_type, base_url=excluded.base_url, license_status=excluded.license_status,517        license_notes=excluded.license_notes, terms_url=excluded.terms_url, priority=excluded.priority, quality=excluded.quality, refresh_frequency=excluded.refresh_frequency, updated_at=datetime('now')`);518    for (const s of SOURCE_SEEDS) upSrc.run({ ...s, isActive: s.isActive ? 1 : 0 });519520    // articles521    const upItem = d.prepare(`INSERT INTO cost_items(canonical_code,masterformat_code,division,category,name_fr,name_en,unit,material_class,trade,default_waste_pct,quality_level,reference_price,reference_note,retail_query,retail_pack_qty,retail_pack_unit)522      VALUES(@code,@mf,@division,@cat,@fr,@en,@unit,@cls,@trade,@waste,@quality,@ref,@refNote,@rq,@rpack,@rpu)523      ON CONFLICT(canonical_code) DO UPDATE SET masterformat_code=excluded.masterformat_code, division=excluded.division, category=excluded.category, name_fr=excluded.name_fr, name_en=excluded.name_en,524        unit=excluded.unit, material_class=excluded.material_class, trade=excluded.trade, default_waste_pct=excluded.default_waste_pct, quality_level=excluded.quality_level,525        reference_price=excluded.reference_price, reference_note=excluded.reference_note, retail_query=excluded.retail_query, retail_pack_qty=excluded.retail_pack_qty, retail_pack_unit=excluded.retail_pack_unit, updated_at=datetime('now')`);526    for (const it of ITEM_SEEDS) {527      upItem.run({528        code: it.code, mf: it.mf, division: MASTERFORMAT[it.mf]?.fr ?? it.mf, cat: it.cat, fr: it.fr, en: it.en, unit: it.unit,529        cls: it.cls ?? "material", trade: it.trade ?? null, waste: it.waste ?? 0, quality: it.quality ?? null,530        ref: it.ref, refNote: it.refNote ?? null, rq: it.retail?.q ?? null, rpack: it.retail?.pack ?? null, rpu: it.retail?.packUnit ?? null,531      });532    }533    const itemId = new Map<string, number>((d.prepare("SELECT id, canonical_code FROM cost_items").all() as { id: number; canonical_code: string }[]).map((r) => [r.canonical_code, r.id]));534535    // assemblages (remplacement complet des composants à chaque version de seed)536    const upAsm = d.prepare(`INSERT INTO cost_assemblies(assembly_code,masterformat_code,category,name_fr,name_en,description_fr,description_en,unit,building_type,quality_level,economic_life,condition_group,version)537      VALUES(@code,@mf,@cat,@fr,@en,@descFr,@descEn,@unit,@buildingType,@quality,@life,@group,@version)538      ON CONFLICT(assembly_code) DO UPDATE SET masterformat_code=excluded.masterformat_code, category=excluded.category, name_fr=excluded.name_fr, name_en=excluded.name_en,539        description_fr=excluded.description_fr, description_en=excluded.description_en, unit=excluded.unit, building_type=excluded.building_type, quality_level=excluded.quality_level,540        economic_life=excluded.economic_life, condition_group=excluded.condition_group, version=excluded.version, updated_at=datetime('now')`);541    const delComp = d.prepare("DELETE FROM cost_assembly_components WHERE assembly_id = ?");542    const insComp = d.prepare(`INSERT INTO cost_assembly_components(assembly_id,cost_item_id,quantity_per_assembly_unit,waste_factor,labour_hours,trade,equipment_cost,sequence,notes)543      VALUES(?,?,?,?,?,?,?,?,?)`);544    for (const a of ASSEMBLY_SEEDS) {545      upAsm.run({ code: a.code, mf: a.mf, cat: a.cat, fr: a.fr, en: a.en, descFr: a.descFr, descEn: a.descEn, unit: a.unit, buildingType: a.buildingType ?? null, quality: a.quality ?? null, life: a.life, group: a.group, version: ASSEMBLY_VERSION });546      const aid = (d.prepare("SELECT id FROM cost_assemblies WHERE assembly_code=?").get(a.code) as { id: number }).id;547      delComp.run(aid);548      for (const c of a.components) {549        const iid = c.itemCode ? itemId.get(c.itemCode) : null;550        if (c.itemCode && !iid) throw new Error(`Assemblage ${a.code} : article inconnu ${c.itemCode}`);551        insComp.run(aid, iid ?? null, c.quantity, c.wasteFactor, c.labourHours, c.trade, c.equipmentCost, c.sequence, c.notes);552      }553    }554555    // localisations556    const upLoc = d.prepare(`INSERT INTO cost_locations(code,name_fr,name_en,region_code,latitude,longitude,radius_km,municipalities_json,material_factor,labour_factor,equipment_factor,overall_factor,effective_date,source_method,confidence_score)557      VALUES(@code,@nameFr,@nameEn,@regionCode,@lat,@lng,@radiusKm,@munis,@materialFactor,@labourFactor,@equipmentFactor,@overallFactor,'2026-09-01',@method,@conf)558      ON CONFLICT(code) DO UPDATE SET name_fr=excluded.name_fr, name_en=excluded.name_en, region_code=excluded.region_code, latitude=excluded.latitude, longitude=excluded.longitude, radius_km=excluded.radius_km,559        municipalities_json=excluded.municipalities_json, material_factor=excluded.material_factor, labour_factor=excluded.labour_factor, equipment_factor=excluded.equipment_factor, overall_factor=excluded.overall_factor,560        source_method=excluded.source_method, confidence_score=excluded.confidence_score`);561    for (const l of LOCATION_SEEDS) {562      upLoc.run({ ...l, munis: JSON.stringify(l.municipalities), method: "Main-d'œuvre : conventions collectives CCQ uniformes au Québec (APCHQ) ; matériaux/équipement : surcoût de transport — hypothèse Vrai-Prix", conf: l.labourFactor === 1 ? 75 : 55 });563    }564565    // règles condition → âge effectif566    const upRule = d.prepare(`INSERT INTO component_condition_rules(condition_group,condition,effective_age_ratio,economic_life,notes) VALUES(?,?,?,?,?)567      ON CONFLICT(condition_group,condition) DO UPDATE SET effective_age_ratio=excluded.effective_age_ratio, economic_life=excluded.economic_life`);568    for (const g of CONDITION_GROUPS) for (const c of CONDITIONS) upRule.run(g.key, c.key, c.effectiveAgeRatio, g.economicLife, "Hypothèse documentée Vrai-Prix — âge effectif = ratio × vie économique de la composante");569570    d.prepare("INSERT INTO cost_meta(key,value) VALUES('seed_version',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(seedVersion);571    d.prepare("INSERT INTO cost_meta(key,value) VALUES('seeded_at',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(new Date().toISOString());572  });573  tx();574}575576/** Version de la base de coûts (pour la reproductibilité des estimations). */577export function costDatabaseVersion(d: Database.Database = getCostDb()): string {578  const seedV = (d.prepare("SELECT value FROM cost_meta WHERE key='seed_version'").get() as { value: string } | undefined)?.value ?? "0";579  const last = (d.prepare("SELECT MAX(observation_date) m, COUNT(*) n FROM cost_item_prices").get() as { m: string | null; n: number });580  const lab = (d.prepare("SELECT MAX(effective_from) m FROM labour_rates").get() as { m: string | null });581  return `${seedV}|prices:${last.n}@${last.m ?? "none"}|labour:${lab.m ?? "none"}`;582}583584export function sourceIdByKey(key: string, d: Database.Database = getCostDb()): number {585  const r = d.prepare("SELECT id FROM cost_sources WHERE key=?").get(key) as { id: number } | undefined;586  if (!r) throw new Error(`Source inconnue : ${key}`);587  return r.id;588}589590export function metaGet(key: string, d: Database.Database = getCostDb()): string | null {591  return (d.prepare("SELECT value FROM cost_meta WHERE key=?").get(key) as { value: string } | undefined)?.value ?? null;592}593export function metaSet(key: string, value: string, d: Database.Database = getCostDb()): void {594  d.prepare("INSERT INTO cost_meta(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(key, value);595}596