/**
 * One-shot data repair after the claim-first upgrade (safe to re-run; every step is idempotent and reversible —
 * projects are hidden, never deleted; figures are nulled only when no site-scoped current claim backs them and the
 * old value stays in provenance).
 *
 *   set -a; source .env; set +a
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts                       # report only
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --hide-false-positives  # hide vetoed projects
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --null-unbacked-mw      # after `dci reprocess … --stale`
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --fix-slugs             # non-Latin operator slugs ("item")
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --link-campuses         # OSM buildings inside a campus footprint
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --hide-unconfirmed     # after reprocess: source re-extracted, no project record / no claim → hide
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --all
 */
import { closeDb, getDb, sql } from "@dci/db";
import { classifyProjectEvent, PHYSICAL_CLASSES, slugify, sha256 } from "@dci/core";
import { isNonProjectTitle, OPENING_TITLE_RE } from "../apps/worker/src/connectors/news/extract-project.js";
import { hideProject } from "../apps/worker/src/ingest/projects.js";

const args = process.argv.slice(2);
const ALL = args.includes("--all");
const has = (f: string) => ALL || args.includes(f);

async function hideFalsePositives(): Promise<void> {
  const db = getDb();
  const rows = await db.execute<{ id: string; name: string; slug: string; planned_mw: number | null; project_class: string | null; status: string; title: string | null; source_kind: string | null; summary: string | null; operator_id: string | null; city: string | null; country_iso2: string | null }>(sql`
    select p.id, p.name, p.slug, p.planned_mw, p.project_class, p.status, n.title, n.summary, p.operator_id, p.city, p.country_iso2, c.kind as source_kind
    from projects p left join news_items n on n.url = p.source_url left join connectors c on c.id = n.connector_id
    where p.merged_into is null and not p.hidden`);
  let hidden = 0, kept = 0;
  const reasons: Record<string, number> = {};
  for (const r of rows) {
    const title = r.title ?? r.name;
    const cls = classifyProjectEvent({ title, lead: r.summary ?? "", hasOperator: !!r.operator_id, hasLocation: !!(r.city || r.country_iso2), hasExplicitName: !/ \| |—| - /.test(r.name) && r.name.length < 90, status: r.status, trustLead: ["operator", "cloud_provider", "government", "utility"].includes(r.source_kind ?? "") });
    let reason: string | null = null;
    if (!PHYSICAL_CLASSES.has(cls.class)) reason = `class:${cls.class}`;
    else if (isNonProjectTitle(title)) reason = "veto:title";
    else if (OPENING_TITLE_RE.test(title) && !/\b(to|will|set to|plans? to|due to|expected to|slated to) (open|be online|go live)\b/i.test(title)) reason = "existing-facility";
    else if (cls.evidence.strength === "none") reason = "evidence:none";
    if (!reason) { kept++; continue; }
    reasons[reason] = (reasons[reason] ?? 0) + 1;
    console.log(`  hide ${r.slug.padEnd(70)} ${String(r.planned_mw ?? "").padStart(7)} MW  ${reason}  «${title.slice(0, 80)}»`);
    if (has("--hide-false-positives")) await db.transaction((tx) => hideProject(tx, r.id, `${reason}: ${title.slice(0, 200)}`, "quality-apply"));
    hidden++;
  }
  console.log(`\nprojects: ${kept} kept · ${hidden} ${has("--hide-false-positives") ? "hidden" : "would be hidden"} · ${JSON.stringify(reasons)}`);
}

async function nullUnbackedMw(): Promise<void> {
  const db = getDb();
  // projects whose planned_mw / investment is not backed by any current site-scoped claim (after reprocessing) → null
  const mw = await db.execute<{ id: string; slug: string; planned_mw: number }>(sql`
    select p.id, p.slug, p.planned_mw from projects p where p.merged_into is null and p.planned_mw is not null
      and exists (select 1 from claims c where c.subject_type = 'project' and c.subject_id = p.id and c.predicate like '%_mw')
      and not exists (select 1 from claims c where c.subject_type = 'project' and c.subject_id = p.id and c.predicate like '%_mw' and c.status = 'current' and c.scope in ('building','facility','campus') and abs(c.value - p.planned_mw) < 0.01)`);
  for (const r of mw) console.log(`  null planned_mw ${r.slug} (${r.planned_mw} MW: only unscoped / rejected claims back it)`);
  const inv = await db.execute<{ id: string; slug: string; investment_usd: number }>(sql`
    select p.id, p.slug, p.investment_usd from projects p where p.merged_into is null and p.investment_usd is not null
      and exists (select 1 from claims c where c.subject_type = 'project' and c.subject_id = p.id and c.predicate like '%_usd')
      and not exists (select 1 from claims c where c.subject_type = 'project' and c.subject_id = p.id and c.predicate = 'project_investment_usd' and c.status = 'current' and c.scope in ('building','facility','campus') and abs(c.value - p.investment_usd) < 1)`);
  for (const r of inv) console.log(`  null investment_usd ${r.slug} ($${(r.investment_usd / 1e9).toFixed(2)}B: only unscoped / deal-value claims back it)`);
  if (has("--null-unbacked-mw")) {
    for (const r of mw) await db.execute(sql`update projects set planned_mw = null, capacity_scope = null, capacity_semantics = null, updated_at = now() where id = ${r.id}`);
    for (const r of inv) await db.execute(sql`update projects set investment_usd = null, investment_scope = null, investment_semantics = null, updated_at = now() where id = ${r.id}`);
  }
  console.log(`\nunbacked figures: ${mw.length} planned_mw · ${inv.length} investment_usd ${has("--null-unbacked-mw") ? "nulled" : "would be nulled"}`);
}

/**
 * After a full reprocess, a live project whose source document was re-extracted with the current parser but produced
 * no claim at all (not even a status claim) is one the new pipeline no longer recognises as a project — the article
 * was vetoed by class or evidence. Hide it (kept for audit; the news item and its event remain).
 */
async function hideUnconfirmed(): Promise<void> {
  const db = getDb();
  const rows = await db.execute<{ id: string; slug: string; name: string; planned_mw: number | null; url: string; ev: string | null; cur: string | null }>(sql`
    select p.id, p.slug, p.name, p.planned_mw, p.source_url as url, d.extractor_version as ev, c.parser_version as cur
    from projects p
    join documents d on d.canonical_url = p.source_url or d.url = p.source_url
    join connectors c on c.id = d.connector_id
    where p.merged_into is null and not p.hidden and p.project_class is null
      and d.extractor_version = c.parser_version and d.extract_ok
      and not exists (select 1 from claims cl where cl.subject_type = 'project' and cl.subject_id = p.id)
      and p.updated_at < d.updated_at`);
  for (const r of rows) console.log(`  hide ${r.slug.padEnd(70)} ${String(r.planned_mw ?? "").padStart(7)} MW  re-extraction produced no project record  «${r.name.slice(0, 70)}»`);
  if (has("--hide-unconfirmed")) for (const r of rows) await db.transaction((tx) => hideProject(tx, r.id, "re-extraction with the current parser produced no project record (class veto / evidence threshold)", "quality-apply"));
  console.log(`\nunconfirmed projects: ${rows.length} ${has("--hide-unconfirmed") ? "hidden" : "would be hidden"}`);
}

async function fixSlugs(): Promise<void> {
  const db = getDb();
  const rows = await db.execute<{ id: string; name: string; slug: string; website: string | null; aliases: string[] }>(sql`select id, name, slug, website, aliases from operators where slug ~ '^(item|op|operator)(-\\d+)?$' or slug = ''`);
  for (const r of rows) {
    const latin = (r.aliases ?? []).find((a) => /^[A-Za-z0-9 .&'-]+$/.test(a) && slugify(a)) ?? (r.website ? r.website.replace(/^https?:\/\/(www\.)?/, "").split("/")[0]?.split(".")[0] : null);
    const base = latin ? slugify(latin) : `operator-${sha256(r.name).slice(0, 8)}`;
    let slug = base, i = 2;
    while ((await db.execute(sql`select 1 from operators where slug = ${slug} and id <> ${r.id}`)).length) slug = `${base}-${i++}`;
    console.log(`  slug ${r.slug} → ${slug}  (${r.name})`);
    if (has("--fix-slugs")) await db.execute(sql`update operators set slug = ${slug}, updated_at = now() where id = ${r.id}`);
  }
  console.log(`\nslugs: ${rows.length} ${has("--fix-slugs") ? "fixed" : "would be fixed"}`);
}

async function linkCampuses(): Promise<void> {
  const db = getDb();
  // Containment, not proximity: a building joins a campus only when it is within 400 m AND (same non-null operator, or a
  // nameless / operator-less record whose name shares a distinctive token with the campus name). Nearest campus wins.
  const rows = await db.execute<{ campus_id: string; campus: string; building_id: string; building: string; m: number; same_op: boolean; campus_operator: string | null }>(sql`
    with pairs as (
      select c.id as campus_id, c.name as campus, b.id as building_id, b.name as building,
        6371000 * acos(least(1, cos(radians(c.lat)) * cos(radians(b.lat)) * cos(radians(b.lng - c.lng)) + sin(radians(c.lat)) * sin(radians(b.lat)))) as m,
        (b.operator_id is not null and b.operator_id = c.operator_id) as same_op,
        (select o.name from operators o where o.id = c.operator_id) as campus_operator,
        row_number() over (partition by b.id order by 6371000 * acos(least(1, cos(radians(c.lat)) * cos(radians(b.lat)) * cos(radians(b.lng - c.lng)) + sin(radians(c.lat)) * sin(radians(b.lat))))) as rn
      from facilities c join facilities b on b.id <> c.id and b.merged_into is null and b.parent_facility_id is null and b.lat is not null
        and abs(b.lat - c.lat) < 0.005 and abs(b.lng - c.lng) < 0.007
        and b.name !~* '(campus|park|complex|hub|cluster|mega ?site)'
      where c.merged_into is null and c.lat is not null and c.name ~* '(campus|park|complex|hub|cluster|mega ?site)')
    select campus_id, campus, building_id, building, round(m) as m, same_op, campus_operator from pairs where rn = 1 and m <= 400 order by campus, m`);
  const GENERIC = new Set(["data", "center", "centre", "campus", "park", "complex", "hub", "cluster", "site", "mega", "the", "of", "at", "dc", "datacenter", "datacentre", "building", "and", "&", "inc", "ltd", "gmbh", "sa", "llc", "co", "corp", "global", "digital", "cloud", "services", "technologies", "technology", "group", "holdings"]);
  const tokens = (s: string) => new Set(s.toLowerCase().normalize("NFKD").replace(/[^a-z0-9 ]/g, " ").split(/\s+/).filter((t) => t.length >= 3 && !GENERIC.has(t)));
  // an operator-less record (OSM building tagged only with a name) joins when its name carries the campus operator's name
  const keep = rows.filter((r) => { if (r.same_op) return true; if (!r.campus_operator) return false; const a = tokens(r.building), b = tokens(r.campus_operator); return b.size > 0 && [...b].every((t) => a.has(t)); });
  for (const r of keep) console.log(`  ${r.building.padEnd(50)} → building of ${r.campus} (${r.m} m${r.same_op ? ", same operator" : ", operator name in building name"})`);
  if (has("--link-campuses")) {
    for (const r of keep) await db.execute(sql`update facilities set parent_facility_id = ${r.campus_id}, record_scope = 'building', updated_at = now() where id = ${r.building_id} and parent_facility_id is null`);
    const campusIds = [...new Set(keep.map((r) => r.campus_id))];
    if (campusIds.length) await db.execute(sql`update facilities set record_scope = 'campus', updated_at = now() where id in ${campusIds}`);
  }
  console.log(`\ncampus links: ${keep.length} ${has("--link-campuses") ? "applied" : "would be applied"} (${rows.length - keep.length} nearby pairs rejected: different or unknown operator)`);
}

async function main(): Promise<void> {
  console.log("── projects: announcement class veto");
  await hideFalsePositives();
  console.log("\n── unbacked figures");
  await nullUnbackedMw();
  console.log("\n── unconfirmed after re-extraction");
  await hideUnconfirmed();
  console.log("\n── operator slugs");
  await fixSlugs();
  console.log("\n── campus containment");
  await linkCampuses();
  await closeDb();
}
main().catch(async (e) => { console.error(e); await closeDb(); process.exit(1); });