/**
 * Likely-duplicate facilities still stored as separate records, scored with the production matcher.
 *
 *   set -a; source .env; set +a
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-dedupe.ts            # report only
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-dedupe.ts --apply    # merge pairs scoring ≥ 0.92 (mergeFacilities)
 *   … --min 0.6 --limit 200                                                 # report threshold / max rows printed
 *
 * Candidate pairs: same operator + same country with name similarity ≥ 0.85, or any two live facilities less than
 * 300 m apart. Each pair is scored in both directions with `scoreFacilityMatch`; the best score decides.
 * `--apply` folds the younger / less-sourced record into the older one for scores ≥ AUTO_MERGE_THRESHOLD (0.92),
 * writing an `entity_matches` row (status approved, decided_by quality-dedupe) for audit. Nothing below 0.92 is touched.
 */
import { closeDb, getDb, sql, textArray } from "@dci/db";
import { haversineKm, newId } from "@dci/core";
import { AUTO_MERGE_THRESHOLD, decide, nameSimilarity, scoreFacilityMatch, type FacilityCandidate } from "../apps/worker/src/ingest/match.js";
import { mergeFacilities } from "../apps/worker/src/ingest/facilities.js";

const args = process.argv.slice(2);
const APPLY = args.includes("--apply");
const num = (flag: string, def: number) => { const i = args.indexOf(flag); return i >= 0 && args[i + 1] ? Number(args[i + 1]) : def; };
const MIN_REPORT = num("--min", 0.6);
const LIMIT = num("--limit", 400);

interface Row extends FacilityCandidate { sourceCount: number; createdAt: string; confidence: string }

async function loadFacilities(): Promise<Row[]> {
  const rows = await getDb().execute(sql`
    select f.id, f.name, f.normalized_name, f.operator_id, o.name as operator_name, f.country_iso2, f.city, f.address, f.lat, f.lng, f.geo_precision, f.external_ids,
      f.source_count, f.created_at, f.confidence,
      (select coalesce(array_agg(alias), '{}'::text[]) from facility_aliases a where a.facility_id = f.id) as aliases
    from facilities f left join operators o on o.id = f.operator_id where f.merged_into is null`);
  return rows.map((r) => ({
    id: String(r.id), name: String(r.name), normalizedName: String(r.normalized_name), aliases: Array.isArray(r.aliases) ? (r.aliases as string[]) : [],
    operatorId: r.operator_id == null ? null : String(r.operator_id), operatorName: r.operator_name == null ? null : String(r.operator_name),
    countryIso2: r.country_iso2 == null ? null : String(r.country_iso2), city: r.city == null ? null : String(r.city), address: r.address == null ? null : String(r.address),
    lat: r.lat == null ? null : Number(r.lat), lng: r.lng == null ? null : Number(r.lng), geoPrecision: r.geo_precision == null ? null : String(r.geo_precision),
    externalIds: (r.external_ids as Record<string, string | number>) ?? null, sourceCount: Number(r.source_count ?? 0), createdAt: String(r.created_at), confidence: String(r.confidence),
  }));
}

function probeOf(f: Row) {
  return { name: f.name, aliases: f.aliases, operatorId: f.operatorId, operatorName: f.operatorName, countryIso2: f.countryIso2, city: f.city, address: f.address, lat: f.lat, lng: f.lng, geoPrecision: (f.geoPrecision ?? null) as never };
}

async function main(): Promise<void> {
  const all = await loadFacilities();
  console.log(`live facilities: ${all.length}`);
  const pairs = new Map<string, [Row, Row, string]>();
  const key = (a: Row, b: Row) => (a.id < b.id ? `${a.id}|${b.id}` : `${b.id}|${a.id}`);

  // (a) same operator + same country, name similarity ≥ 0.85
  const byOpCountry = new Map<string, Row[]>();
  for (const f of all) if (f.operatorId && f.countryIso2) { const k = `${f.operatorId}|${f.countryIso2}`; (byOpCountry.get(k) ?? byOpCountry.set(k, []).get(k)!).push(f); }
  for (const group of byOpCountry.values()) {
    if (group.length < 2 || group.length > 600) continue;
    for (let i = 0; i < group.length; i++) for (let j = i + 1; j < group.length; j++) {
      const a = group[i]!, b = group[j]!;
      const n = nameSimilarity(probeOf(a), b);
      if (n.score >= 0.85 && !n.siblings) pairs.set(key(a, b), [a, b, `name:${n.score.toFixed(2)}`]);
    }
  }
  // (b) any two facilities < 300 m apart (bucketed by ~1 km cells)
  const cells = new Map<string, Row[]>();
  const cell = (lat: number, lng: number) => `${Math.floor(lat * 100)}|${Math.floor(lng * 100)}`;
  for (const f of all) if (f.lat != null && f.lng != null) { const k = cell(f.lat, f.lng); (cells.get(k) ?? cells.set(k, []).get(k)!).push(f); }
  for (const f of all) {
    if (f.lat == null || f.lng == null) continue;
    for (const dl of [-1, 0, 1]) for (const dg of [-1, 0, 1]) {
      const bucket = cells.get(`${Math.floor(f.lat * 100) + dl}|${Math.floor(f.lng * 100) + dg}`);
      if (!bucket) continue;
      for (const g of bucket) {
        if (g.id <= f.id) continue;
        const d = haversineKm(f.lat, f.lng, g.lat!, g.lng!);
        if (d < 0.3 && !pairs.has(key(f, g))) pairs.set(key(f, g), [f, g, `distance:${(d * 1000).toFixed(0)}m`]);
      }
    }
  }
  console.log(`candidate pairs: ${pairs.size}`);

  const scored: Array<{ a: Row; b: Row; why: string; score: number; reasons: string[] }> = [];
  for (const [a, b, why] of pairs.values()) {
    const m1 = scoreFacilityMatch(probeOf(a), b);
    const m2 = scoreFacilityMatch(probeOf(b), a);
    const m = m1.score >= m2.score ? m1 : m2;
    if (m.score >= MIN_REPORT) scored.push({ a, b, why, score: m.score, reasons: m.reasons });
  }
  scored.sort((x, y) => y.score - x.score);
  const mergeable = scored.filter((s) => s.score >= AUTO_MERGE_THRESHOLD);
  const pending = scored.filter((s) => s.score < AUTO_MERGE_THRESHOLD);
  console.log(`likely duplicates (score ≥ ${MIN_REPORT}): ${scored.length} — auto-mergeable (≥ ${AUTO_MERGE_THRESHOLD}): ${mergeable.length}, review (${MIN_REPORT}–${AUTO_MERGE_THRESHOLD}): ${pending.length}\n`);
  for (const s of scored.slice(0, LIMIT)) console.log(`${s.score.toFixed(3)} ${decide(s.score).padEnd(7)} | ${s.a.name} [${s.a.operatorName ?? "-"}, ${s.a.countryIso2 ?? "-"}] ⇄ ${s.b.name} [${s.b.operatorName ?? "-"}, ${s.b.countryIso2 ?? "-"}] | ${s.why} | ${s.reasons.join(",")}`);

  if (!APPLY) { console.log(`\n(dry run — pass --apply to merge the ${mergeable.length} pairs scoring ≥ ${AUTO_MERGE_THRESHOLD})`); return; }
  const gone = new Set<string>();
  let merged = 0;
  for (const s of mergeable) {
    if (gone.has(s.a.id) || gone.has(s.b.id)) continue;
    // survivor: more sources, then better confidence, then older
    const rank = (f: Row) => [f.sourceCount, ["verified", "high", "moderate", "estimated", "unverified"].indexOf(f.confidence) * -1, -Date.parse(f.createdAt)] as const;
    const [into, from] = rank(s.a) >= rank(s.b) ? [s.a, s.b] : [s.b, s.a];
    await getDb().transaction(async (tx) => {
      await tx.execute(sql`insert into entity_matches (id, connector_id, candidate_key, candidate, matched_facility_id, score, reasons, status, decided_by, decided_at)
        values (${newId("match")}, 'quality-dedupe', ${`quality-dedupe:${from.id}`}, ${JSON.stringify({ createdFacilityId: from.id, name: from.name, operatorName: from.operatorName, countryIso2: from.countryIso2, why: s.why })}::jsonb, ${into.id}, ${s.score}, ${textArray(s.reasons)}::text[], 'approved', 'quality-dedupe', now())`);
      await mergeFacilities(tx, from.id, into.id, "quality-dedupe");
    });
    gone.add(from.id);
    merged++;
    console.log(`merged ${from.name} (${from.id}) → ${into.name} (${into.id}) @ ${s.score}`);
  }
  console.log(`\nmerged ${merged} facilities`);
}

main().catch((e) => { console.error(e); process.exitCode = 1; }).finally(() => closeDb());