// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
/**
 * Imports manuels (aucun scraping) :
 *  - Altus Group — Canadian Cost Guide : benchmarks $/pi² saisis après
 *    téléchargement légal du guide (formulaire) → cost_benchmarks ;
 *  - RSMeans / Gordian : données sous licence, import CSV/XLSX→CSV autorisé
 *    seulement → cost_item_prices (price_kind « reference », source rsmeans),
 *    jamais redistribuées telles quelles (seul le prix canonique agrégé sert).
 */
import type Database from "better-sqlite3";
import { getCostDb, sourceIdByKey } from "../db";
import { convertPrice } from "../units";
import type { CanonicalUnit } from "../types";

export const BENCHMARK_TYPES = ["single_family", "townhouse", "plex", "condo"] as const;
export type BenchmarkType = (typeof BENCHMARK_TYPES)[number];

export interface BenchmarkImportRow { source: string; building_type: BenchmarkType; market: string; unit?: string; low: number; high: number; year: number; notes?: string | null }

/** CSV simple (séparateur , ou ;) → objets ; guillemets gérés. */
export function parseCsv(text: string): Record<string, string>[] {
  const lines = text.replace(/\r/g, "").split("\n").filter((l) => l.trim());
  if (lines.length < 2) return [];
  const sep = lines[0].includes(";") && !lines[0].includes(",") ? ";" : ",";
  const split = (l: string): string[] => {
    const out: string[] = [];
    let cur = "";
    let q = false;
    for (const ch of l) {
      if (ch === '"') q = !q;
      else if (ch === sep && !q) { out.push(cur.trim()); cur = ""; }
      else cur += ch;
    }
    out.push(cur.trim());
    return out;
  };
  const head = split(lines[0]).map((h) => h.toLowerCase().replace(/\s+/g, "_"));
  return lines.slice(1).map((l) => Object.fromEntries(split(l).map((v, i) => [head[i] ?? `c${i}`, v])));
}

export function normalizeBenchmarkRows(input: unknown): { rows: BenchmarkImportRow[]; errors: string[] } {
  const raw: Record<string, unknown>[] = typeof input === "string" ? parseCsv(input) : Array.isArray(input) ? (input as Record<string, unknown>[]) : [];
  const rows: BenchmarkImportRow[] = [];
  const errors: string[] = [];
  raw.forEach((r, i) => {
    const bt = String(r.building_type ?? "").trim() as BenchmarkType;
    const low = Number(String(r.low ?? "").replace(",", "."));
    const high = Number(String(r.high ?? "").replace(",", "."));
    const year = Number(r.year);
    if (!BENCHMARK_TYPES.includes(bt)) { errors.push(`ligne ${i + 1} : building_type invalide « ${bt} »`); return; }
    if (!(low > 0 && high >= low && high < 5000)) { errors.push(`ligne ${i + 1} : bornes invalides`); return; }
    if (!(year >= 2015 && year <= 2035)) { errors.push(`ligne ${i + 1} : année invalide`); return; }
    if (!r.source || !r.market) { errors.push(`ligne ${i + 1} : source/market requis`); return; }
    rows.push({ source: String(r.source).trim(), building_type: bt, market: String(r.market).trim(), unit: String(r.unit ?? "$/pi2"), low, high, year, notes: r.notes ? String(r.notes) : null });
  });
  return { rows, errors };
}

export function importBenchmarks(input: unknown, d: Database.Database = getCostDb()): { imported: number; errors: string[] } {
  const { rows, errors } = normalizeBenchmarkRows(input);
  const altusId = sourceIdByKey("altus", d);
  const rsId = sourceIdByKey("rsmeans", d);
  const st = d.prepare(`INSERT INTO cost_benchmarks(source,source_id,building_type,market,unit,low,high,midpoint,year,notes,is_demo) VALUES(?,?,?,?,?,?,?,?,?,?,0)
    ON CONFLICT(source,building_type,market,year) DO UPDATE SET low=excluded.low, high=excluded.high, midpoint=excluded.midpoint, unit=excluded.unit, notes=excluded.notes, source_id=excluded.source_id`);
  const tx = d.transaction(() => { for (const r of rows) st.run(r.source, /altus/i.test(r.source) ? altusId : /rsmeans|gordian/i.test(r.source) ? rsId : null, r.building_type, r.market, r.unit, r.low, r.high, (r.low + r.high) / 2, r.year, r.notes ?? null); });
  tx();
  if (rows.length) d.prepare("UPDATE cost_sources SET last_successful_sync=datetime('now') WHERE key IN ('altus') AND EXISTS (SELECT 1 FROM cost_benchmarks WHERE source_id=cost_sources.id)").run();
  return { imported: rows.length, errors };
}

/** Import RSMeans/Gordian (CSV : item_code, price, unit, [pack_qty], [date], [notes]) → prix de référence licenciés. */
export function importRsmeans(input: unknown, d: Database.Database = getCostDb()): { imported: number; errors: string[] } {
  const raw: Record<string, unknown>[] = typeof input === "string" ? parseCsv(input) : Array.isArray(input) ? (input as Record<string, unknown>[]) : [];
  const sid = sourceIdByKey("rsmeans", d);
  const errors: string[] = [];
  let imported = 0;
  const tx = d.transaction(() => {
    raw.forEach((r, i) => {
      const code = String(r.item_code ?? r.canonical_code ?? "").trim();
      const item = d.prepare("SELECT id, unit FROM cost_items WHERE canonical_code=?").get(code) as { id: number; unit: CanonicalUnit } | undefined;
      if (!item) { errors.push(`ligne ${i + 1} : article inconnu ${code}`); return; }
      const price = Number(String(r.price ?? "").replace(",", "."));
      if (!(price > 0)) { errors.push(`ligne ${i + 1} : prix invalide`); return; }
      const date = String(r.date ?? new Date().toISOString().slice(0, 10));
      try {
        const conv = convertPrice(price, String(r.unit ?? item.unit), item.unit, r.pack_qty ? Number(r.pack_qty) : null);
        d.prepare(`INSERT INTO cost_item_prices(cost_item_id,source_id,location_code,observation_date,price_kind,material_cost,total_cost,currency,source_unit,conversion_factor,is_regular_price,confidence_score,source_url,verified) VALUES(?,?,'QC',?,'reference',?,?,'CAD',?,?,1,70,?,1)`)
          .run(item.id, sid, date, conv.price, conv.price, conv.conversion.sourceUnit, conv.conversion.factor, r.notes ? String(r.notes) : null);
        imported++;
      } catch (e) { errors.push(`ligne ${i + 1} : ${(e as Error).message}`); }
    });
  });
  tx();
  return { imported, errors };
}