/**
 * Facility sanity report: implausible values and inconsistencies, grouped by the connector that produced them.
 *
 *   set -a; source .env; set +a
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-facilities.ts            # report
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-facilities.ts --apply    # apply the safe repairs (see below)
 *
 * Checks: single-facility MW > 1 000 without a campus designation · building_sqm > 500 000 · opened_on < 1980 from a
 * non-Wikidata source · coordinates outside the stated country (Natural Earth polygons, `countryFromPoint`) ·
 * names that are page / article titles or another kind of business · confidence inconsistent with the source mix.
 * `--apply` only performs repairs whose provenance is kept: drops pre-1980 OSM opening dates from the column, drops
 * coordinates that fall in another country (geo_precision → unknown), and recomputes confidence/completeness.
 */
import { closeDb, getDb, sql } from "@dci/db";
import { TITLE_LIKE_NAME_RE, NOT_A_FACILITY_NAME_RE } from "@dci/connectors";
import { countryFromPoint } from "../apps/worker/src/ingest/country-lookup.js";
import { BORDER_TOLERANT, refreshDerived } from "../apps/worker/src/ingest/facilities.js";

const APPLY = process.argv.includes("--apply");

interface F { id: string; name: string; connector: string; kind: string | null; country: string | null; lat: number | null; lng: number | null; precision: string | null; it: number | null; total: number | null; planned: number | null; sqm: number | null; opened: string | null; campus: string | null; confidence: string; sourceCount: number }

async function main(): Promise<void> {
  const db = getDb();
  const rows = await db.execute(sql`
    select f.id, f.name, f.country_iso2, f.lat, f.lng, f.geo_precision, f.it_capacity_mw, f.total_power_mw, f.planned_power_mw, f.building_sqm, f.opened_on, f.confidence, f.source_count, c.name as campus,
      (select k.connector_id from entity_keys k where k.entity_id = f.id and k.entity_type = 'facility' order by k.created_at limit 1) as connector,
      (select string_agg(distinct s.kind::text, ',') from provenance p join sources s on s.id = p.source_id where p.entity_type = 'facility' and p.entity_id = f.id and p.is_current) as kinds
    from facilities f left join campuses c on c.id = f.campus_id where f.merged_into is null`);
  const fs: F[] = rows.map((r) => ({ id: String(r.id), name: String(r.name), connector: String(r.connector ?? "-"), kind: r.kinds == null ? null : String(r.kinds), country: r.country_iso2 == null ? null : String(r.country_iso2), lat: r.lat == null ? null : Number(r.lat), lng: r.lng == null ? null : Number(r.lng), precision: r.geo_precision == null ? null : String(r.geo_precision), it: r.it_capacity_mw == null ? null : Number(r.it_capacity_mw), total: r.total_power_mw == null ? null : Number(r.total_power_mw), planned: r.planned_power_mw == null ? null : Number(r.planned_power_mw), sqm: r.building_sqm == null ? null : Number(r.building_sqm), opened: r.opened_on == null ? null : String(r.opened_on), campus: r.campus == null ? null : String(r.campus), confidence: String(r.confidence), sourceCount: Number(r.source_count ?? 0) }));
  console.log(`live facilities: ${fs.length}`);

  const groups = new Map<string, Array<{ f: F; detail: string }>>();
  const flag = (rule: string, f: F, detail: string) => (groups.get(rule) ?? groups.set(rule, []).get(rule)!).push({ f, detail });
  const geoDrops: F[] = [];
  const openedDrops: F[] = [];
  for (const f of fs) {
    const isCampus = /\b(campus|park|cluster|hub|complex)\b/i.test(`${f.name} ${f.campus ?? ""}`);
    for (const [k, v] of [["it_capacity_mw", f.it], ["total_power_mw", f.total]] as Array<[string, number | null]>) if (v != null && v > 1000 && !isCampus) flag("mw>1000 single facility", f, `${k}=${v}`);
    if (f.planned != null && f.planned > 5000 && !isCampus) flag("planned>5000 single facility", f, `planned=${f.planned}`);
    if (f.sqm != null && f.sqm > 500_000) flag("building_sqm>500000", f, `sqm=${f.sqm}`);
    if (f.opened && Number(f.opened.slice(0, 4)) < 1980 && f.connector !== "wikidata") { flag("opened_on<1980 non-wikidata", f, `opened=${f.opened}`); openedDrops.push(f); }
    if (f.lat != null && f.lng != null && f.country && f.precision !== "metro") {
      const pc = countryFromPoint(f.lat, f.lng);
      if (pc && pc !== f.country && !BORDER_TOLERANT.has(`${f.country}:${pc}`)) { flag("coordinates outside stated country", f, `stated=${f.country} point=${pc} (${f.lat},${f.lng}) ${f.precision}`); geoDrops.push(f); }
    }
    if (TITLE_LIKE_NAME_RE.test(f.name)) flag("name looks like a page title", f, "");
    if (NOT_A_FACILITY_NAME_RE.test(f.name)) flag("name describes another business", f, "");
    const kinds = new Set((f.kind ?? "").split(",").filter(Boolean));
    const authoritative = ["operator", "government", "filing", "utility", "cloud_provider"].some((k) => kinds.has(k));
    if ((f.confidence === "high" || f.confidence === "verified") && !authoritative) flag("confidence high without an authoritative source", f, `kinds=${f.kind}`);
    if (f.sourceCount <= 1 && ["community", "secondary", "news"].some((k) => kinds.has(k)) && kinds.size === 1 && f.confidence !== "unverified" && f.confidence !== "estimated") flag("single community source not unverified", f, `confidence=${f.confidence}`);
  }
  for (const [rule, items] of [...groups].sort((a, b) => b[1].length - a[1].length)) {
    const byConnector = new Map<string, number>();
    for (const { f } of items) byConnector.set(f.connector, (byConnector.get(f.connector) ?? 0) + 1);
    console.log(`\n== ${rule}: ${items.length}  (${[...byConnector].map(([c, n]) => `${c} ${n}`).join(", ")})`);
    for (const { f, detail } of items.slice(0, 25)) console.log(`   ${f.connector.padEnd(18)} ${f.name.slice(0, 60).padEnd(60)} ${detail}`);
    if (items.length > 25) console.log(`   … ${items.length - 25} more`);
  }

  if (!APPLY) { console.log("\n(dry run — pass --apply to drop pre-1980 non-Wikidata opening dates and out-of-country coordinates, then recompute confidence)"); return; }
  let n = 0;
  for (const f of openedDrops) { await db.execute(sql`update facilities set opened_on = null, updated_at = now() where id = ${f.id}`); n++; }
  for (const f of geoDrops) { await db.execute(sql`update facilities set lat = null, lng = null, geo_precision = 'unknown', geo_source = null, geohash = null, updated_at = now() where id = ${f.id}`); n++; }
  console.log(`\nrepaired ${n} columns; recomputing confidence / completeness for ${fs.length} facilities…`);
  let i = 0;
  for (const f of fs) { await db.transaction(async (tx) => refreshDerived(tx, f.id)); if (++i % 500 === 0) console.log(`  ${i}`); }
  console.log("done");
}

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