// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
/**
 * Base de coûts `data/cost.db` (SQLite, better-sqlite3) : schéma versionné,
 * migrations forward-only, seeds idempotents (articles, assemblages,
 * localisations, sources, règles de condition). SERVEUR seulement.
 *
 * L'interface ne lit que cette base : aucun connecteur web n'est appelé dans
 * le chemin utilisateur (scripts/cost-sync.ts alimente la base en arrière-plan).
 */
import Database from "better-sqlite3";
import path from "path";
import fs from "fs";
import { ITEM_SEEDS } from "./seed/items";
import { ASSEMBLY_SEEDS, ASSEMBLY_VERSION } from "./seed/assemblies";
import { LOCATION_SEEDS } from "./seed/locations";
import { SOURCE_SEEDS } from "./seed/sources";
import { CONDITIONS, CONDITION_GROUPS, MASTERFORMAT } from "./taxonomy";

export const COST_DB_SCHEMA_VERSION = 3;

let db: Database.Database | null = null;

export function costDbPath(): string {
  return process.env.COST_DB ?? path.join(process.cwd(), "data", "cost.db");
}

export function getCostDb(): Database.Database {
  if (db) return db;
  const p = costDbPath();
  fs.mkdirSync(path.dirname(p), { recursive: true });
  db = new Database(p);
  db.pragma("journal_mode = WAL");
  db.pragma("foreign_keys = ON");
  migrate(db);
  seed(db);
  return db;
}

/** Pour les tests : base en mémoire isolée. */
export function openCostDbAt(p: string): Database.Database {
  const d = new Database(p);
  d.pragma("journal_mode = MEMORY");
  d.pragma("foreign_keys = ON");
  migrate(d);
  seed(d);
  return d;
}

/* ----------------------------------------------------------------- schéma */

const SCHEMA = `
CREATE TABLE IF NOT EXISTS cost_meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);

CREATE TABLE IF NOT EXISTS cost_sources (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  key TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  source_type TEXT NOT NULL,
  base_url TEXT,
  jurisdiction TEXT,
  license_status TEXT NOT NULL DEFAULT 'unknown',
  license_notes TEXT,
  terms_url TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  priority INTEGER NOT NULL DEFAULT 5,
  quality REAL NOT NULL DEFAULT 0.5,
  refresh_frequency TEXT,
  last_successful_sync TEXT,
  last_error TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS cost_raw_observations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source_id INTEGER NOT NULL REFERENCES cost_sources(id),
  external_id TEXT,
  source_url TEXT,
  retrieved_at TEXT NOT NULL,
  effective_date TEXT,
  raw_title TEXT,
  raw_description TEXT,
  raw_unit TEXT,
  raw_price REAL,
  raw_regular_price REAL,
  raw_sale_price REAL,
  raw_currency TEXT,
  raw_location TEXT,
  raw_category TEXT,
  raw_payload_json TEXT,
  content_hash TEXT,
  parser_version TEXT,
  status TEXT NOT NULL DEFAULT 'new',
  reject_reason TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_raw_obs_source_date ON cost_raw_observations(source_id, retrieved_at);
CREATE INDEX IF NOT EXISTS idx_raw_obs_hash ON cost_raw_observations(content_hash);

CREATE TABLE IF NOT EXISTS cost_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  canonical_code TEXT UNIQUE NOT NULL,
  masterformat_code TEXT NOT NULL,
  division TEXT NOT NULL,
  category TEXT NOT NULL,
  subcategory TEXT,
  name_fr TEXT NOT NULL,
  name_en TEXT NOT NULL,
  description_fr TEXT,
  description_en TEXT,
  unit TEXT NOT NULL,
  material_class TEXT,
  trade TEXT,
  default_waste_pct REAL NOT NULL DEFAULT 0,
  residential_relevance INTEGER NOT NULL DEFAULT 1,
  quality_level TEXT,
  reference_price REAL,
  reference_note TEXT,
  retail_query TEXT,
  retail_pack_qty REAL,
  retail_pack_unit TEXT,
  active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

/* URL produit par détaillant pour un article (découverte par connecteur / matching validé) */
CREATE TABLE IF NOT EXISTS cost_item_sources (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  cost_item_id INTEGER NOT NULL REFERENCES cost_items(id),
  source_id INTEGER NOT NULL REFERENCES cost_sources(id),
  product_url TEXT NOT NULL,
  product_title TEXT,
  external_id TEXT,
  pack_qty REAL,
  pack_unit TEXT,
  match_method TEXT,
  match_confidence REAL,
  approved INTEGER NOT NULL DEFAULT 0,
  active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(source_id, product_url)
);

CREATE TABLE IF NOT EXISTS cost_item_prices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  cost_item_id INTEGER NOT NULL REFERENCES cost_items(id),
  source_id INTEGER NOT NULL REFERENCES cost_sources(id),
  location_code TEXT,
  observation_date TEXT NOT NULL,
  price_kind TEXT NOT NULL DEFAULT 'observed',
  material_cost REAL,
  labour_cost REAL,
  equipment_cost REAL,
  total_cost REAL NOT NULL,
  low_cost REAL,
  median_cost REAL,
  high_cost REAL,
  currency TEXT NOT NULL DEFAULT 'CAD',
  source_unit TEXT,
  conversion_factor REAL NOT NULL DEFAULT 1,
  is_regular_price INTEGER NOT NULL DEFAULT 1,
  is_outlier INTEGER NOT NULL DEFAULT 0,
  outlier_reason TEXT,
  confidence_score REAL,
  source_url TEXT,
  raw_observation_id INTEGER REFERENCES cost_raw_observations(id),
  verified INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_item_prices_item_date ON cost_item_prices(cost_item_id, observation_date);

CREATE TABLE IF NOT EXISTS labour_rates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trade_code TEXT NOT NULL,
  trade_name_fr TEXT NOT NULL,
  trade_name_en TEXT,
  sector TEXT NOT NULL,
  classification TEXT NOT NULL DEFAULT 'compagnon',
  region TEXT NOT NULL DEFAULT 'QC',
  effective_from TEXT NOT NULL,
  effective_to TEXT,
  base_wage REAL NOT NULL,
  vacation_cost REAL NOT NULL DEFAULT 0,
  benefits_cost REAL NOT NULL DEFAULT 0,
  employer_contributions REAL NOT NULL DEFAULT 0,
  other_contributions REAL NOT NULL DEFAULT 0,
  total_employer_cost REAL NOT NULL,
  source_id INTEGER REFERENCES cost_sources(id),
  source_url TEXT,
  confidence_score REAL,
  raw_observation_id INTEGER REFERENCES cost_raw_observations(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(trade_code, sector, classification, region, effective_from, source_id)
);

CREATE TABLE IF NOT EXISTS cost_locations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT UNIQUE NOT NULL,
  municipality_code TEXT,
  municipality_name TEXT,
  name_fr TEXT NOT NULL,
  name_en TEXT NOT NULL,
  region_code TEXT,
  region_name TEXT,
  latitude REAL,
  longitude REAL,
  radius_km REAL,
  municipalities_json TEXT,
  material_factor REAL NOT NULL DEFAULT 1,
  labour_factor REAL NOT NULL DEFAULT 1,
  equipment_factor REAL NOT NULL DEFAULT 1,
  overall_factor REAL NOT NULL DEFAULT 1,
  effective_date TEXT NOT NULL,
  source_method TEXT,
  confidence_score REAL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS cost_assemblies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  assembly_code TEXT UNIQUE NOT NULL,
  masterformat_code TEXT NOT NULL,
  category TEXT NOT NULL,
  name_fr TEXT NOT NULL,
  name_en TEXT NOT NULL,
  description_fr TEXT,
  description_en TEXT,
  unit TEXT NOT NULL,
  building_type TEXT,
  quality_level TEXT,
  economic_life INTEGER,
  condition_group TEXT,
  version INTEGER NOT NULL DEFAULT 1,
  active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS cost_assembly_components (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  assembly_id INTEGER NOT NULL REFERENCES cost_assemblies(id) ON DELETE CASCADE,
  cost_item_id INTEGER REFERENCES cost_items(id),
  quantity_per_assembly_unit REAL NOT NULL DEFAULT 0,
  waste_factor REAL NOT NULL DEFAULT 0,
  labour_hours REAL NOT NULL DEFAULT 0,
  trade TEXT,
  equipment_cost REAL NOT NULL DEFAULT 0,
  sequence INTEGER NOT NULL DEFAULT 0,
  notes TEXT
);
CREATE INDEX IF NOT EXISTS idx_asm_comp ON cost_assembly_components(assembly_id);

CREATE TABLE IF NOT EXISTS construction_cost_indices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source TEXT NOT NULL,
  index_code TEXT NOT NULL,
  geography TEXT NOT NULL,
  building_type TEXT NOT NULL,
  division TEXT NOT NULL DEFAULT 'aggregate',
  period TEXT NOT NULL,
  index_value REAL NOT NULL,
  pct_change_qoq REAL,
  pct_change_yoy REAL,
  retrieved_at TEXT NOT NULL,
  UNIQUE(index_code, period)
);

CREATE TABLE IF NOT EXISTS cost_benchmarks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source TEXT NOT NULL,
  source_id INTEGER REFERENCES cost_sources(id),
  building_type TEXT NOT NULL,
  market TEXT NOT NULL,
  unit TEXT NOT NULL DEFAULT '$/pi2',
  low REAL NOT NULL,
  high REAL NOT NULL,
  midpoint REAL,
  year INTEGER NOT NULL,
  notes TEXT,
  is_demo INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(source, building_type, market, year)
);

CREATE TABLE IF NOT EXISTS component_condition_rules (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  condition_group TEXT NOT NULL,
  condition TEXT NOT NULL,
  effective_age_ratio REAL NOT NULL,
  economic_life INTEGER NOT NULL,
  notes TEXT,
  UNIQUE(condition_group, condition)
);

CREATE TABLE IF NOT EXISTS cost_estimates (
  id TEXT PRIMARY KEY,
  property_id TEXT,
  listing_uid TEXT,
  user_session_id TEXT,
  mode TEXT NOT NULL,
  location_code TEXT,
  municipality TEXT,
  estimate_date TEXT NOT NULL,
  price_date TEXT NOT NULL,
  building_type TEXT,
  quality_level TEXT,
  area_sqft REAL,
  direct_cost REAL,
  indirect_cost REAL,
  contractor_overhead REAL,
  contractor_profit REAL,
  contingency REAL,
  replacement_cost_new REAL,
  rcn_low REAL,
  rcn_high REAL,
  physical_depreciation REAL,
  functional_obsolescence REAL,
  external_obsolescence REAL,
  depreciated_improvement_value REAL,
  land_value REAL,
  cost_approach_value REAL,
  confidence_score REAL,
  confidence_letter TEXT,
  method_version TEXT NOT NULL,
  cost_database_version TEXT NOT NULL,
  assembly_version TEXT NOT NULL,
  input_json TEXT NOT NULL,
  result_json TEXT NOT NULL,
  assumptions_json TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_estimates_prop ON cost_estimates(property_id);
CREATE INDEX IF NOT EXISTS idx_estimates_listing ON cost_estimates(listing_uid);

CREATE TABLE IF NOT EXISTS cost_estimate_lines (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  estimate_id TEXT NOT NULL REFERENCES cost_estimates(id) ON DELETE CASCADE,
  assembly_code TEXT NOT NULL,
  category TEXT NOT NULL,
  quantity REAL NOT NULL,
  unit TEXT NOT NULL,
  material_cost REAL,
  labour_cost REAL,
  equipment_cost REAL,
  direct_cost REAL,
  location_adjustment REAL,
  adjusted_cost REAL,
  source_summary TEXT,
  confidence_score REAL,
  calculation_json TEXT
);
CREATE INDEX IF NOT EXISTS idx_est_lines ON cost_estimate_lines(estimate_id);

/* instantané des prix canoniques utilisés par une estimation (reproductibilité) */
CREATE TABLE IF NOT EXISTS cost_estimate_snapshots (
  estimate_id TEXT PRIMARY KEY REFERENCES cost_estimates(id) ON DELETE CASCADE,
  snapshot_date TEXT NOT NULL,
  prices_json TEXT NOT NULL,
  labour_json TEXT NOT NULL,
  location_json TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS connector_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  connector TEXT NOT NULL,
  started_at TEXT NOT NULL,
  finished_at TEXT,
  status TEXT NOT NULL DEFAULT 'running',
  pages INTEGER NOT NULL DEFAULT 0,
  observations INTEGER NOT NULL DEFAULT 0,
  accepted INTEGER NOT NULL DEFAULT 0,
  rejected INTEGER NOT NULL DEFAULT 0,
  unchanged INTEGER NOT NULL DEFAULT 0,
  errors INTEGER NOT NULL DEFAULT 0,
  duration_ms INTEGER,
  log_json TEXT
);

/* correspondances produit détaillant → article canonique proposées (IA/déterministe) à valider */
CREATE TABLE IF NOT EXISTS product_mappings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source_id INTEGER NOT NULL REFERENCES cost_sources(id),
  product_url TEXT NOT NULL,
  product_title TEXT,
  proposed_item_code TEXT,
  pack_qty REAL,
  pack_unit TEXT,
  method TEXT NOT NULL,
  confidence REAL NOT NULL,
  rationale TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  reviewed_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(source_id, product_url)
);

/* ---------------------------------------------- analyse IA des annonces */
CREATE TABLE IF NOT EXISTS listing_images (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  listing_uid TEXT NOT NULL,
  photo_id TEXT NOT NULL,
  source_url TEXT NOT NULL,
  position INTEGER,
  width INTEGER,
  height INTEGER,
  bytes INTEGER,
  hash TEXT,
  media_type TEXT,
  room_guess TEXT,
  quality_score REAL,
  selected_for_ai INTEGER NOT NULL DEFAULT 0,
  fetched_at TEXT NOT NULL,
  UNIQUE(listing_uid, source_url)
);

CREATE TABLE IF NOT EXISTS listing_ai_analyses (
  id TEXT PRIMARY KEY,
  listing_uid TEXT NOT NULL,
  version INTEGER NOT NULL,
  model TEXT NOT NULL,
  prompt_version TEXT NOT NULL,
  schema_version TEXT NOT NULL,
  input_hash TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'queued',
  stage TEXT,
  error TEXT,
  image_count INTEGER NOT NULL DEFAULT 0,
  output_json TEXT,
  compact_json TEXT,
  vector_json TEXT,
  confidence REAL,
  estimate_id TEXT,
  cost_snapshot_date TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  completed_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_ai_analyses_listing ON listing_ai_analyses(listing_uid, version);
CREATE INDEX IF NOT EXISTS idx_ai_analyses_hash ON listing_ai_analyses(listing_uid, input_hash, prompt_version, model);

CREATE TABLE IF NOT EXISTS listing_ai_overrides (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  analysis_id TEXT NOT NULL REFERENCES listing_ai_analyses(id) ON DELETE CASCADE,
  field_path TEXT NOT NULL,
  original_value TEXT,
  new_value TEXT,
  user_id TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS analysis_conflicts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  analysis_id TEXT NOT NULL REFERENCES listing_ai_analyses(id) ON DELETE CASCADE,
  field TEXT NOT NULL,
  source_a TEXT NOT NULL,
  value_a TEXT,
  source_b TEXT NOT NULL,
  value_b TEXT,
  severity TEXT NOT NULL DEFAULT 'medium',
  status TEXT NOT NULL DEFAULT 'open'
);

CREATE TABLE IF NOT EXISTS property_embeddings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  listing_uid TEXT NOT NULL,
  analysis_id TEXT NOT NULL REFERENCES listing_ai_analyses(id) ON DELETE CASCADE,
  embedding_model TEXT NOT NULL,
  embedding_dimension INTEGER NOT NULL,
  embedding_json TEXT NOT NULL,
  canonical_text TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(analysis_id, embedding_model)
);

CREATE TABLE IF NOT EXISTS ai_usage (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  analysis_id TEXT,
  purpose TEXT NOT NULL,
  model TEXT NOT NULL,
  input_images INTEGER NOT NULL DEFAULT 0,
  input_tokens INTEGER NOT NULL DEFAULT 0,
  output_tokens INTEGER NOT NULL DEFAULT 0,
  cache_read_tokens INTEGER NOT NULL DEFAULT 0,
  estimated_cost_usd REAL,
  latency_ms INTEGER,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`;

function migrate(d: Database.Database): void {
  d.exec(SCHEMA);
  const cur = Number((d.prepare("SELECT value FROM cost_meta WHERE key='schema_version'").get() as { value: string } | undefined)?.value ?? 0);
  // migrations incrémentales (forward-only) : ajouter ici les ALTER TABLE futurs
  if (cur < 2) {
    // v2 : colonnes de prix promotionnel déjà dans SCHEMA (création) — no-op pour bases existantes récentes
  }
  if (cur < 3) {
    // v3 : ai_usage.cache_read_tokens (ajout tolérant)
    try { d.exec("ALTER TABLE ai_usage ADD COLUMN cache_read_tokens INTEGER NOT NULL DEFAULT 0"); } catch { /* déjà présent */ }
  }
  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));
}

/* ------------------------------------------------------------------ seeds */

function seed(d: Database.Database): void {
  const seedVersion = `${COST_DB_SCHEMA_VERSION}.${ASSEMBLY_VERSION}.${ITEM_SEEDS.length}.${ASSEMBLY_SEEDS.length}.${LOCATION_SEEDS.length}`;
  const done = (d.prepare("SELECT value FROM cost_meta WHERE key='seed_version'").get() as { value: string } | undefined)?.value;
  if (done === seedVersion) return;

  const tx = d.transaction(() => {
    // sources
    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)
      VALUES(@key,@name,@sourceType,@baseUrl,@jurisdiction,@licenseStatus,@licenseNotes,@termsUrl,@isActive,@priority,@quality,@refreshFrequency)
      ON CONFLICT(key) DO UPDATE SET name=excluded.name, source_type=excluded.source_type, base_url=excluded.base_url, license_status=excluded.license_status,
        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')`);
    for (const s of SOURCE_SEEDS) upSrc.run({ ...s, isActive: s.isActive ? 1 : 0 });

    // articles
    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)
      VALUES(@code,@mf,@division,@cat,@fr,@en,@unit,@cls,@trade,@waste,@quality,@ref,@refNote,@rq,@rpack,@rpu)
      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,
        unit=excluded.unit, material_class=excluded.material_class, trade=excluded.trade, default_waste_pct=excluded.default_waste_pct, quality_level=excluded.quality_level,
        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')`);
    for (const it of ITEM_SEEDS) {
      upItem.run({
        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,
        cls: it.cls ?? "material", trade: it.trade ?? null, waste: it.waste ?? 0, quality: it.quality ?? null,
        ref: it.ref, refNote: it.refNote ?? null, rq: it.retail?.q ?? null, rpack: it.retail?.pack ?? null, rpu: it.retail?.packUnit ?? null,
      });
    }
    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]));

    // assemblages (remplacement complet des composants à chaque version de seed)
    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)
      VALUES(@code,@mf,@cat,@fr,@en,@descFr,@descEn,@unit,@buildingType,@quality,@life,@group,@version)
      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,
        description_fr=excluded.description_fr, description_en=excluded.description_en, unit=excluded.unit, building_type=excluded.building_type, quality_level=excluded.quality_level,
        economic_life=excluded.economic_life, condition_group=excluded.condition_group, version=excluded.version, updated_at=datetime('now')`);
    const delComp = d.prepare("DELETE FROM cost_assembly_components WHERE assembly_id = ?");
    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)
      VALUES(?,?,?,?,?,?,?,?,?)`);
    for (const a of ASSEMBLY_SEEDS) {
      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 });
      const aid = (d.prepare("SELECT id FROM cost_assemblies WHERE assembly_code=?").get(a.code) as { id: number }).id;
      delComp.run(aid);
      for (const c of a.components) {
        const iid = c.itemCode ? itemId.get(c.itemCode) : null;
        if (c.itemCode && !iid) throw new Error(`Assemblage ${a.code} : article inconnu ${c.itemCode}`);
        insComp.run(aid, iid ?? null, c.quantity, c.wasteFactor, c.labourHours, c.trade, c.equipmentCost, c.sequence, c.notes);
      }
    }

    // localisations
    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)
      VALUES(@code,@nameFr,@nameEn,@regionCode,@lat,@lng,@radiusKm,@munis,@materialFactor,@labourFactor,@equipmentFactor,@overallFactor,'2026-09-01',@method,@conf)
      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,
        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,
        source_method=excluded.source_method, confidence_score=excluded.confidence_score`);
    for (const l of LOCATION_SEEDS) {
      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 });
    }

    // règles condition → âge effectif
    const upRule = d.prepare(`INSERT INTO component_condition_rules(condition_group,condition,effective_age_ratio,economic_life,notes) VALUES(?,?,?,?,?)
      ON CONFLICT(condition_group,condition) DO UPDATE SET effective_age_ratio=excluded.effective_age_ratio, economic_life=excluded.economic_life`);
    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");

    d.prepare("INSERT INTO cost_meta(key,value) VALUES('seed_version',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(seedVersion);
    d.prepare("INSERT INTO cost_meta(key,value) VALUES('seeded_at',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(new Date().toISOString());
  });
  tx();
}

/** Version de la base de coûts (pour la reproductibilité des estimations). */
export function costDatabaseVersion(d: Database.Database = getCostDb()): string {
  const seedV = (d.prepare("SELECT value FROM cost_meta WHERE key='seed_version'").get() as { value: string } | undefined)?.value ?? "0";
  const last = (d.prepare("SELECT MAX(observation_date) m, COUNT(*) n FROM cost_item_prices").get() as { m: string | null; n: number });
  const lab = (d.prepare("SELECT MAX(effective_from) m FROM labour_rates").get() as { m: string | null });
  return `${seedV}|prices:${last.n}@${last.m ?? "none"}|labour:${lab.m ?? "none"}`;
}

export function sourceIdByKey(key: string, d: Database.Database = getCostDb()): number {
  const r = d.prepare("SELECT id FROM cost_sources WHERE key=?").get(key) as { id: number } | undefined;
  if (!r) throw new Error(`Source inconnue : ${key}`);
  return r.id;
}

export function metaGet(key: string, d: Database.Database = getCostDb()): string | null {
  return (d.prepare("SELECT value FROM cost_meta WHERE key=?").get(key) as { value: string } | undefined)?.value ?? null;
}
export function metaSet(key: string, value: string, d: Database.Database = getCostDb()): void {
  d.prepare("INSERT INTO cost_meta(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(key, value);
}