/**
 * Project curation: hide / unhide (false positives are never deleted), merge (duplicate folded into a survivor),
 * PATCH curated fields. Every change writes src_manual provenance; only lifecycle-relevant changes emit an event
 * (status → project_status_changed, planned MW → planned_capacity_changed, hide/unhide → project_status_changed with
 * {hidden} values). Everything else is provenance-only.
 */
import { createHash } from "node:crypto";
import type postgres from "postgres";
import { newId, normalizeName } from "@dci/core";
import { pg } from "../../lib/sql.js";
import { str, type Row } from "../../lib/rows.js";
import { ensureManualSource } from "./merge.js";

const ADMIN_URL = (id: string) => `https://www.datacenterindex.io/admin/projects/${id}`;

export async function findProject(idOrSlug: string): Promise<Row | null> {
  const sql = pg();
  return (await sql<Row[]>`select * from projects where id = ${idOrSlug} or slug = ${idOrSlug} order by (id = ${idOrSlug}) desc limit 1`)[0] ?? null;
}

type Tx = postgres.TransactionSql;

async function manualProvenance(tx: Tx, projectId: string, field: string, value: unknown, now: string, note: string | null, scope: string | null = null): Promise<void> {
  await tx`insert into provenance (id, entity_type, entity_id, field, value, source_id, connector_id, document_id, url, first_observed, last_observed, retrieved_at, confidence, is_estimate, method, extractor_version, is_current, is_winner, scope, note)
    values (${newId("provenance")}, 'project', ${projectId}, ${field}, ${JSON.stringify(value)}::jsonb, 'src_manual', 'manual', null, ${ADMIN_URL(projectId)}, ${now}, ${now}, ${now}, 'high', false, 'manual', 'admin', true, true, ${scope}, ${note})
    on conflict (entity_type, entity_id, field, source_id, url) do update set value = excluded.value, last_observed = excluded.last_observed, retrieved_at = excluded.retrieved_at, is_current = true, is_winner = true, scope = excluded.scope, note = excluded.note`;
  // the manual value is the displayed one: other current rows for the field are no longer winners
  await tx`update provenance set is_winner = false where entity_type = 'project' and entity_id = ${projectId} and field = ${field} and source_id <> 'src_manual' and is_winner`;
}

export async function setProjectHidden(idOrSlug: string, hidden: boolean, reason: string | null): Promise<{ id: string; hidden: boolean; changed: boolean } | null> {
  const sql = pg();
  const cur = await findProject(idOrSlug);
  if (!cur) return null;
  const id = String(cur.id);
  if (Boolean(cur.hidden) === hidden) return { id, hidden, changed: false };
  await ensureManualSource();
  const now = new Date().toISOString();
  await sql.begin(async (tx) => {
    await tx`update projects set hidden = ${hidden}, updated_at = now() where id = ${id}`;
    await manualProvenance(tx, id, "hidden", hidden, now, reason);
    await tx`insert into events (id, entity_type, entity_id, event_type, detected_at, effective_date, old_value, new_value, source_id, document_id, url, title, summary, significance, confidence, review_status, country_iso2, operator_id, project_id, fingerprint)
      values (${newId("event")}, 'project', ${id}, 'project_status_changed', ${now}, ${now.slice(0, 10)}, ${JSON.stringify({ hidden: !hidden })}::jsonb, ${JSON.stringify({ hidden })}::jsonb, 'src_manual', null, ${ADMIN_URL(id)}, ${`${String(cur.name)}: ${hidden ? "hidden by review" : "restored by review"}${reason ? ` (${reason})` : ""}`}, ${reason}, 10, 'high', 'approved', ${str(cur.country_iso2)}, ${str(cur.operator_id)}, ${id}, ${`manual:${hidden ? "hide" : "unhide"}:${id}:${now.slice(0, 10)}`})
      on conflict (fingerprint) do nothing`;
  });
  return { id, hidden, changed: true };
}

export interface ProjectMergeResult { into: string; merged: string; moved: Record<string, number> }

export async function mergeProject(dupIdOrSlug: string, intoIdOrSlug: string): Promise<ProjectMergeResult> {
  const sql = pg();
  const [dup, into] = await Promise.all([findProject(dupIdOrSlug), findProject(intoIdOrSlug)]);
  if (!dup) throw new Error("project not found");
  if (!into) throw new Error("target project not found");
  const dupId = String(dup.id), intoId = String(into.id);
  if (dupId === intoId) throw new Error("cannot merge a project into itself");
  if (into.hidden === true) throw new Error("target project is hidden");
  if (into.merged_into) throw new Error(`target is itself merged into ${String(into.merged_into)}`);
  if (dup.merged_into) throw new Error(`project is already merged into ${String(dup.merged_into)}`);
  await ensureManualSource();
  return sql.begin(async (tx) => {
    const moved: Record<string, number> = {};
    const count = (r: { count: number }) => r.count;
    moved.events = count(await tx`update events set project_id = ${intoId} where project_id = ${dupId}`);
    moved.eventsEntity = count(await tx`update events set entity_id = ${intoId} where entity_type = 'project' and entity_id = ${dupId}`);
    moved.timeline = count(await tx`update project_timeline set project_id = ${intoId} where project_id = ${dupId}`);
    moved.claims = count(await tx`update claims k set subject_id = ${intoId} where k.subject_type = 'project' and k.subject_id = ${dupId} and not exists (select 1 from claims q where q.subject_type = 'project' and q.subject_id = ${intoId} and q.predicate = k.predicate and q.source_id = k.source_id and q.url = k.url and coalesce(q.value, 0) = coalesce(k.value, 0) and coalesce(q.value_text, '') = coalesce(k.value_text, ''))`);
    await tx`delete from claims where subject_type = 'project' and subject_id = ${dupId}`;
    moved.provenance = count(await tx`update provenance p set entity_id = ${intoId} where p.entity_type = 'project' and p.entity_id = ${dupId} and not exists (select 1 from provenance q where q.entity_type = 'project' and q.entity_id = ${intoId} and q.field = p.field and q.source_id = p.source_id and q.url = p.url)`);
    await tx`delete from provenance where entity_type = 'project' and entity_id = ${dupId}`;
    moved.entityKeys = count(await tx`update entity_keys set entity_id = ${intoId} where entity_type = 'project' and entity_id = ${dupId}`);
    moved.qualityFlags = count(await tx`update quality_flags set status = 'resolved', resolution = ${"merged into " + intoId}, resolved_by = 'admin', resolved_at = now(), updated_at = now() where entity_type = 'project' and entity_id = ${dupId} and status = 'open'`);
    await tx`update projects set merged_into = ${intoId}, updated_at = now() where id = ${dupId}`;
    await tx`update projects set last_update = now(), updated_at = now() where id = ${intoId}`;
    await tx`insert into events (id, entity_type, entity_id, event_type, detected_at, old_value, new_value, source_id, url, title, summary, significance, confidence, review_status, country_iso2, operator_id, project_id, fingerprint)
      values (${newId("event")}, 'project', ${intoId}, 'facility_updated', now(), ${JSON.stringify({ mergedProject: dupId, name: dup.name })}::jsonb, ${JSON.stringify({ into: intoId })}::jsonb, 'src_manual', ${ADMIN_URL(intoId)}, ${"Duplicate project merged: " + String(dup.name)}, 'Merged by admin', 20, 'high', 'approved', ${str(into.country_iso2)}, ${str(into.operator_id)}, ${intoId}, ${"merge:project:" + dupId + ":" + intoId})
      on conflict (fingerprint) do nothing`;
    return { into: intoId, merged: dupId, moved };
  });
}

/** snake_case column → provenance field name */
const COLUMN_TO_FIELD: Record<string, string> = { planned_mw: "plannedMw", investment_usd: "investmentUsd", expected_opening: "expectedOpening", announced_on: "announcedOn", construction_started_on: "constructionStartedOn", approved_on: "approvedOn", permit_filed_on: "permitFiledOn", opened_on: "openedOn", operator_id: "operatorId", country_iso2: "countryIso2", metro_id: "metroId", geo_precision: "geoPrecision", project_class: "projectClass", ai_evidence: "aiEvidence", is_ai: "isAi" };

export interface PatchChange { field: string; column: string; oldValue: unknown; newValue: unknown }

export async function patchProject(idOrSlug: string, fields: Record<string, unknown>, note: string | null): Promise<{ id: string; changed: PatchChange[] } | null> {
  const sql = pg();
  const cur = await findProject(idOrSlug);
  if (!cur) return null;
  const id = String(cur.id);
  const changed: PatchChange[] = [];
  const set: Record<string, unknown> = {};
  for (const [col, val] of Object.entries(fields)) {
    if (val === undefined) continue;
    const before = cur[col] ?? null;
    if (JSON.stringify(before) === JSON.stringify(val)) continue;
    set[col] = val;
    changed.push({ field: COLUMN_TO_FIELD[col] ?? col, column: col, oldValue: before, newValue: val });
  }
  if (!changed.length) return { id, changed };
  await ensureManualSource();
  const now = new Date().toISOString();
  if (set.name) set.normalized_name = normalizeName(String(set.name));
  if (set.planned_mw !== undefined) { set.capacity_scope = "facility"; set.capacity_semantics = "planned_power_mw"; }
  if (set.investment_usd !== undefined) { set.investment_scope = "facility"; set.investment_semantics = "project_investment_usd"; }
  if (set.lat !== undefined && set.lat !== null && set.geo_precision === undefined) set.geo_precision = "approximate";
  set.updated_at = now;
  set.last_update = now;
  set.confidence = "high";
  await sql.begin(async (tx) => {
    await tx`update projects set ${tx(set)} where id = ${id}`;
    for (const c of changed) await manualProvenance(tx, id, c.field, c.newValue, now, note, /mw$|investment/i.test(c.column) ? "facility" : null);
    const statusChange = changed.find((c) => c.column === "status");
    const mwChange = changed.find((c) => c.column === "planned_mw");
    const evt = statusChange ? { type: "project_status_changed", old: statusChange.oldValue, nw: statusChange.newValue, sig: 85 } : mwChange ? { type: "planned_capacity_changed", old: mwChange.oldValue, nw: mwChange.newValue, sig: 75 } : null;
    if (evt) {
      const digest = createHash("sha256").update(JSON.stringify([evt.type, evt.nw])).digest("hex").slice(0, 16);
      await tx`insert into events (id, entity_type, entity_id, event_type, detected_at, effective_date, old_value, new_value, source_id, document_id, url, title, summary, significance, confidence, review_status, country_iso2, operator_id, project_id, fingerprint)
        values (${newId("event")}, 'project', ${id}, ${evt.type}, ${now}, ${now.slice(0, 10)}, ${JSON.stringify(evt.old)}::jsonb, ${JSON.stringify(evt.nw)}::jsonb, 'src_manual', null, ${"https://www.datacenterindex.io/projects/" + String(cur.slug)}, ${`${String(set.name ?? cur.name)}: ${statusChange ? "status" : "planned capacity"} updated by curation`}, ${note}, ${evt.sig}, 'high', 'approved', ${(set.country_iso2 as string | undefined) ?? str(cur.country_iso2)}, ${(set.operator_id as string | undefined) ?? str(cur.operator_id)}, ${id}, ${"manual:project:" + id + ":" + now.slice(0, 10) + ":" + digest})
        on conflict (fingerprint) do nothing`;
    }
  });
  return { id, changed };
}