/**
 * Repair facilities that swallowed several connector keys through a shared, non-identifying external id
 * (`operator_wikidata`, `google_location`, `investment_currency`…): 121 AWS OpenStreetMap features folded into one
 * "Amazon Web Services" row, 28 Meta sites into "Meta Cheyenne Data Center", Google's Texas sites into Wilbarger County.
 *
 *   set -a; source .env; set +a
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-unfold.ts                 # report
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-unfold.ts --apply         # detach the extra keys
 *   then: pnpm dci run <connector> --task reprocess    (re-extracts from archived bodies → the detached keys become facilities again)
 *
 * For each (facility, connector) holding > 1 key the script keeps the key that names the facility (the `osm`
 * external id for OSM, the key slug closest to the current name otherwise), deletes the other keys, the connector's
 * aliases (all renames from the fold) and the connector's provenance rows (restored by the reprocess), and clears the
 * facility's external ids. Nothing else is touched; reprocessing recreates the detached facilities through the normal
 * reconciliation path, which now only links on identifying external ids.
 */
import { closeDb, getDb, sql } from "@dci/db";
import { normalizeName } from "@dci/core";
import { refreshDerived } from "../apps/worker/src/ingest/facilities.js";

const APPLY = process.argv.includes("--apply");
const ONLY = process.argv.includes("--connector") ? process.argv[process.argv.indexOf("--connector") + 1] : null;

async function main(): Promise<void> {
  const db = getDb();
  const rows = await db.execute(sql`
    select f.id, f.name, f.external_ids, k.connector_id, (select s.id from sources s where s.connector_id = k.connector_id limit 1) as source_id, array_agg(k.key order by k.key) as keys
    from entity_keys k join facilities f on f.id = k.entity_id
    where k.entity_type = 'facility' and f.merged_into is null ${ONLY ? sql`and k.connector_id = ${ONLY}` : sql``}
    group by f.id, f.name, f.external_ids, k.connector_id having count(*) > 1
    order by count(*) desc`);
  console.log(`facilities holding several keys of one connector: ${rows.length}`);
  const perConnector = new Map<string, { facilities: number; extraKeys: number }>();
  let detached = 0;
  for (const r of rows) {
    const id = String(r.id), name = String(r.name), connector = String(r.connector_id);
    const keys = r.keys as string[];
    const ext = (r.external_ids as Record<string, string | number>) ?? {};
    // the key that names this facility
    let keep: string | undefined;
    if (ext.osm) keep = keys.find((k) => k === `osm:${ext.osm}`);
    if (!keep) {
      const nameTokens = new Set(normalizeName(name).split(" ").filter(Boolean));
      let best = -1;
      for (const k of keys) {
        const slug = k.split(":").pop() ?? "";
        const toks = slug.split("-").filter(Boolean);
        const overlap = toks.filter((t) => nameTokens.has(t)).length;
        if (overlap > best) { best = overlap; keep = k; }
      }
    }
    keep ??= keys[0]!;
    const extra = keys.filter((k) => k !== keep);
    const stat = perConnector.get(connector) ?? { facilities: 0, extraKeys: 0 };
    stat.facilities++; stat.extraKeys += extra.length; perConnector.set(connector, stat);
    console.log(`${connector.padEnd(18)} ${name} — ${keys.length} keys, keep ${keep}, detach ${extra.length}`);
    if (!APPLY) continue;
    await db.transaction(async (tx) => {
      await tx.execute(sql`delete from entity_keys where entity_type = 'facility' and entity_id = ${id} and connector_id = ${connector} and key <> ${keep}`);
      if (r.source_id != null) await tx.execute(sql`delete from facility_aliases where facility_id = ${id} and source_id = ${String(r.source_id)}`);
      await tx.execute(sql`delete from provenance where entity_type = 'facility' and entity_id = ${id} and connector_id = ${connector}`);
      await tx.execute(sql`update facilities set external_ids = '{}'::jsonb, updated_at = now() where id = ${id}`);
      await refreshDerived(tx, id);
    });
    detached += extra.length;
  }
  console.log("\nper connector:");
  for (const [c, s] of perConnector) console.log(`  ${c.padEnd(18)} facilities ${s.facilities}, keys to detach ${s.extraKeys}`);
  console.log(APPLY ? `\ndetached ${detached} keys — now run: pnpm dci run <connector> --task reprocess for ${[...perConnector.keys()].join(", ")}` : "\n(dry run — pass --apply to detach)");
}

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