/**
 * Project-pipeline precision audit. Every project is re-judged from the headline / summary of the article it came
 * from, with the same gates the extractors now apply:
 *
 *   veto:title          people / awards / partnerships / financing / market research / policy / opinion headline
 *   existing-facility   opening, "we are online", delivered — the article is about an operating facility
 *   fallback:no-signal  created by the GenericConnector fallback (key …:project) without a MW figure AND a pipeline
 *                       status in the headline (the new fallback gate)
 *   news-kind:fallback  fallback project from a third-party publisher (the fallback no longer creates those)
 *   keep                passes every gate
 *
 *   set -a; source .env; set +a
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-projects.ts               # report
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-projects.ts --apply       # delete flagged projects (keys, provenance, timeline, events)
 *   node node_modules/tsx/dist/cli.mjs scripts/quality-projects.ts --sample 60   # print a random sample for hand classification
 */
import { closeDb, getDb, sql } from "@dci/db";
import { fallbackProjectGate } from "@dci/connectors";
import { isNonProjectTitle, OPENING_TITLE_RE, STATUS_RULES } from "../apps/worker/src/connectors/news/extract-project.js";

const args = process.argv.slice(2);
const APPLY = args.includes("--apply");
const SAMPLE = args.includes("--sample") ? Number(args[args.indexOf("--sample") + 1] ?? 60) : 0;
const FUTURE_OPEN_RE = /\b(to|will|set to|plans? to|due to|expected to|slated to) (open|be online|go live)\b|\b(open|opening|online|live) (in|by) (Q[1-4]|20\d\d|early|late|mid)\b/i;

interface P { id: string; key: string | null; connector: string | null; kind: string | null; name: string; status: string; mw: number | null; title: string | null; summary: string | null }

function verdict(p: P): string {
  const title = p.title ?? p.name;
  if (isNonProjectTitle(title)) return "veto:title";
  if (OPENING_TITLE_RE.test(title) && !FUTURE_OPEN_RE.test(title) && !STATUS_RULES.slice(0, 3).some(([re]) => re.test(title))) return "existing-facility";
  if (p.key?.endsWith(":project")) {
    if (p.kind === "news") return "news-kind:fallback";
    if (!fallbackProjectGate(title, p.summary)) return "fallback:no-signal";
  }
  return "keep";
}

async function main(): Promise<void> {
  const db = getDb();
  const rows = await db.execute(sql`
    select p.id, p.name, p.status, p.planned_mw, k.key, k.connector_id, s.kind, n.title, n.summary
    from projects p
    left join entity_keys k on k.entity_id = p.id and k.entity_type = 'project'
    left join sources s on s.connector_id = k.connector_id
    left join news_items n on n.url = p.source_url
    order by p.created_at`);
  const projects: P[] = rows.map((r) => ({ id: String(r.id), key: r.key == null ? null : String(r.key), connector: r.connector_id == null ? null : String(r.connector_id), kind: r.kind == null ? null : String(r.kind), name: String(r.name), status: String(r.status), mw: r.planned_mw == null ? null : Number(r.planned_mw), title: r.title == null ? null : String(r.title), summary: r.summary == null ? null : String(r.summary) }));

  if (SAMPLE) {
    const shuffled = [...projects].sort(() => Math.random() - 0.5).slice(0, SAMPLE);
    for (const p of shuffled) console.log(`${p.id} | ${p.connector} | ${p.status} | ${p.mw ?? "-"} MW | ${p.name} | ${p.title ?? "-"} | ${(p.summary ?? "").slice(0, 140)}`);
    return;
  }

  const counts = new Map<string, number>();
  const flagged: Array<P & { verdict: string }> = [];
  for (const p of projects) {
    const v = verdict(p);
    counts.set(v, (counts.get(v) ?? 0) + 1);
    if (v !== "keep") flagged.push({ ...p, verdict: v });
  }
  console.log(`projects: ${projects.length}`);
  for (const [v, n] of [...counts].sort((a, b) => b[1] - a[1])) console.log(`  ${v.padEnd(20)} ${n}`);
  console.log("\nflagged:");
  for (const p of flagged) console.log(`  ${p.verdict.padEnd(20)} ${p.connector ?? "-"} | ${p.status} | ${p.mw ?? "-"} MW | ${p.name.slice(0, 70)} | ${(p.title ?? "").slice(0, 90)}`);
  const kept = projects.length - flagged.length;
  console.log(`\nprecision proxy: kept ${kept}/${projects.length} (${((kept / Math.max(1, projects.length)) * 100).toFixed(0)} % of stored projects pass the headline gates)`);

  if (!APPLY) { console.log("(dry run — pass --apply to delete the flagged projects)"); return; }
  let deleted = 0;
  for (const p of flagged) {
    await db.transaction(async (tx) => {
      await tx.execute(sql`update events set project_id = null where project_id = ${p.id}`);
      await tx.execute(sql`delete from events where entity_type = 'project' and entity_id = ${p.id}`);
      await tx.execute(sql`update news_items set project_id = null where project_id = ${p.id}`);
      await tx.execute(sql`delete from provenance where entity_type = 'project' and entity_id = ${p.id}`);
      await tx.execute(sql`delete from entity_keys where entity_type = 'project' and entity_id = ${p.id}`);
      await tx.execute(sql`delete from projects where id = ${p.id}`); // project_timeline cascades
    });
    deleted++;
  }
  console.log(`deleted ${deleted} projects`);
}

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