import type { ClaimDTO, EntityHistory, EventDTO, FacilityStatus, HistoryPoint, ProjectDetail, ProjectStageDTO, ProjectSummary, SourceRef } from "@dci/core";
import { PROJECT_STAGES, parsePartialDate, partialDateSortKey, projectTransition } from "@dci/core";
import { pg, andAll, projectJoins, projectSummaryCols, projectLive, page, likePattern, yearExpr, AI_LEVELS, type Fragment } from "../lib/sql.js";
import { int, num, reqStr, str, type Row } from "../lib/rows.js";
import { asStatus, projectSummary } from "../lib/dto.js";
import { findBySlugOrId } from "../lib/resolve.js";
import { nearby } from "../lib/nearby.js";
import { capacityHistory, claimsFor, dataQualityFor, entityHistory, eventsForSubject, provenanceAll } from "../lib/quality.js";
import { eventsForProject } from "./events.js";
import { provenanceFor } from "./facilities.js";
import { buildSourceHistory, documentVersionsFor, sourceIdsOf, sourceRefsFor } from "../lib/source-history.js";

export interface ProjectFilters {
  status?: string[];
  country?: string; // iso2
  operator?: string; // slug or id
  metro?: string; // slug or id
  facilityId?: string;
  min_mw?: number;
  max_mw?: number;
  ai?: boolean;
  project_class?: string[];
  evidence_level?: string[];
  expected_from?: number;
  expected_to?: number;
  announced_since?: string;
  q?: string;
  sort?: "updated" | "mw" | "announced" | "opening";
  order?: "asc" | "desc";
  page?: number;
  per_page?: number;
}

export function projectConds(f: ProjectFilters): Fragment[] {
  const sql = pg();
  const c: Fragment[] = [projectLive(sql)];
  if (f.status?.length) c.push(sql`p.status = any(${f.status})`);
  if (f.country) c.push(sql`p.country_iso2 = ${f.country.toUpperCase()}`);
  if (f.operator) c.push(sql`(o.slug = ${f.operator} or o.id = ${f.operator})`);
  if (f.metro) c.push(sql`(m.slug = ${f.metro} or m.id = ${f.metro})`);
  if (f.facilityId) c.push(sql`p.facility_id = ${f.facilityId}`);
  if (f.min_mw != null) c.push(sql`p.planned_mw >= ${f.min_mw}`);
  if (f.max_mw != null) c.push(sql`p.planned_mw <= ${f.max_mw}`);
  if (f.ai === true) c.push(sql`(p.is_ai or p.ai_evidence = any(${AI_LEVELS}))`);
  if (f.ai === false) c.push(sql`(not p.is_ai and p.ai_evidence <> all(${AI_LEVELS}))`);
  if (f.project_class?.length) c.push(sql`p.project_class = any(${f.project_class})`);
  if (f.evidence_level?.length) c.push(sql`p.evidence_level = any(${f.evidence_level})`);
  if (f.expected_from != null) c.push(sql`${yearExpr(sql, sql`p.expected_opening`)} >= ${f.expected_from}`);
  if (f.expected_to != null) c.push(sql`${yearExpr(sql, sql`p.expected_opening`)} <= ${f.expected_to}`);
  if (f.announced_since) c.push(sql`(p.announced_on >= ${f.announced_since} or (p.announced_on is null and p.created_at >= ${f.announced_since}::timestamptz))`);
  if (f.q) { const t = f.q.trim(); if (t) c.push(sql`(p.name ilike ${likePattern(t)} or similarity(p.name, ${t}) > 0.3 or o.name ilike ${likePattern(t)})`); }
  return c;
}

function order(sort: ProjectFilters["sort"], dir: ProjectFilters["order"]): Fragment {
  const sql = pg();
  const asc = dir === "asc";
  switch (sort) {
    case "mw": return asc ? sql`p.planned_mw asc nulls last, p.name` : sql`p.planned_mw desc nulls last, p.name`;
    case "announced": return asc ? sql`p.announced_on asc nulls last, p.name` : sql`p.announced_on desc nulls last, p.name`;
    case "opening": return asc ? sql`p.expected_opening asc nulls last, p.name` : sql`p.expected_opening desc nulls last, p.name`;
    case "updated": default: return asc ? sql`p.last_update asc, p.id` : sql`p.last_update desc, p.id desc`;
  }
}

export async function listProjects(f: ProjectFilters): Promise<{ items: ProjectSummary[]; total: number; page: number; perPage: number }> {
  const sql = pg();
  const pg_ = page(f.page, f.per_page, 100, 24);
  const rows = await sql<Row[]>`
    select ${projectSummaryCols(sql)}, count(*) over() as total
    from projects p ${projectJoins(sql)}
    where ${andAll(sql, projectConds(f))}
    order by ${order(f.sort, f.order)}
    limit ${pg_.perPage} offset ${pg_.offset}`;
  return { items: rows.map(projectSummary), total: rows.length ? int(rows[0]!.total) : 0, page: pg_.page, perPage: pg_.perPage };
}

/** Projects linked to a facility: facility_id match, or same operator + metro. */
export async function projectsForFacility(facilityId: string, operatorId: string | null, metroId: string | null, limit = 12): Promise<ProjectSummary[]> {
  const sql = pg();
  const rows = await sql<Row[]>`
    select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)}
    where ${projectLive(sql)} and (p.facility_id = ${facilityId}
      ${operatorId && metroId ? sql`or (p.operator_id = ${operatorId} and p.metro_id = ${metroId})` : sql``})
    order by (p.facility_id = ${facilityId}) desc, p.last_update desc limit ${limit}`;
  return rows.map(projectSummary);
}

/** Live projects matching a condition over `projects p` (+ projectJoins aliases o / pf / m). */
export async function projectsWhere(where: Fragment, limit = 10, orderBy?: Fragment): Promise<ProjectSummary[]> {
  const sql = pg();
  const rows = await sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where ${projectLive(sql)} and (${where}) order by ${orderBy ?? sql`p.last_update desc`} limit ${limit}`;
  return rows.map(projectSummary);
}

/** Resolve a project by slug or id; merged projects redirect to their survivor, hidden projects are never served. */
export async function resolveProjectRow(idOrSlug: string): Promise<Row | null> {
  const sql = pg();
  const base = await findBySlugOrId("projects", idOrSlug);
  if (!base) return null;
  let row = base;
  for (let hops = 0; hops < 5 && str(row.merged_into); hops++) {
    const next = (await sql<Row[]>`select * from projects where id = ${String(row.merged_into)} limit 1`)[0];
    if (!next) break;
    row = next;
  }
  if (row.hidden === true) return null;
  return row;
}

// ─── lifecycle stages ──────────────────────────────────────────────────────────────────────────────

type Stage = ProjectStageDTO["stage"];
const STAGE_COLUMN: Partial<Record<Stage, string>> = { announced: "announced_on", permitting: "permit_filed_on", approved: "approved_on", under_construction: "construction_started_on", operational: "opened_on" };
const TIMELINE_STAGE: Array<[RegExp, Stage]> = [
  [/cancel/i, "cancelled"], [/delay|on hold|paused/i, "delayed"], [/open|launch|live|commission|energi[sz]ed/i, "operational"], [/partial/i, "partially_operational"],
  [/construction|ground|topped/i, "under_construction"], [/approv|consent|permit(ted)?\b|green/i, "approved"], [/planning|filed|permit|zoning|application/i, "permitting"],
  [/announce|unveil|reveal|plan/i, "announced"], [/propos/i, "proposed"], [/rumo/i, "rumored"],
];
const EVENT_STAGE: Record<string, Stage> = { construction_started: "under_construction", planning_filed: "permitting", planning_approved: "approved", project_delayed: "delayed", project_cancelled: "cancelled", facility_opened: "operational", project_announced: "announced" };

interface StageEvidence { date: string; url: string | null; sourceName: string | null; eventId: string | null }

function isStage(s: unknown): s is Stage { return typeof s === "string" && (PROJECT_STAGES as readonly string[]).includes(s); }

export function buildStages(row: Row, timeline: Array<{ date: string; type: string; url: string | null; sourceName: string | null }>, events: EventDTO[]): ProjectStageDTO[] {
  const current = asStatus(row.status);
  const ev = new Map<Stage, StageEvidence>();
  const put = (stage: Stage, e: StageEvidence) => {
    if (!e.date || !/^\d{4}/.test(e.date)) return;
    const prev = ev.get(stage);
    // earliest dated evidence wins; a dated column beats a detected_at fallback
    if (!prev || partialDateSortKey(e.date) < partialDateSortKey(prev.date)) ev.set(stage, e);
  };
  for (const [stage, col] of Object.entries(STAGE_COLUMN) as Array<[Stage, string]>) { const d = str(row[col]); if (d) put(stage, { date: d, url: str(row.source_url), sourceName: null, eventId: null }); }
  if (current === "operational" && !ev.has("operational") && str(row.expected_opening) && partialDateSortKey(str(row.expected_opening)) <= Date.now()) put("operational", { date: String(row.expected_opening), url: str(row.source_url), sourceName: null, eventId: null });
  for (const t of timeline) { const stage = TIMELINE_STAGE.find(([re]) => re.test(t.type))?.[1]; if (stage) put(stage, { date: t.date, url: t.url, sourceName: t.sourceName, eventId: null }); }
  for (const e of events) {
    let stage: Stage | undefined = EVENT_STAGE[e.eventType];
    if (e.eventType === "project_status_changed" || e.eventType === "status_changed") { const to = typeof e.newValue === "object" && e.newValue ? (e.newValue as Record<string, unknown>).status ?? (e.newValue as Record<string, unknown>).to : e.newValue; stage = isStage(to) ? to : undefined; }
    if (stage) put(stage, { date: e.effectiveDate ?? e.detectedAt.slice(0, 10), url: e.url || null, sourceName: e.sourceName, eventId: e.id });
  }
  // `expansion` is an operational site growing — it sits at the operational stage of the lifecycle view
  const currentStage: Stage | null = current === "expansion" ? "operational" : isStage(current) ? current : null;
  return PROJECT_STAGES.map((stage) => {
    const isCurrent = currentStage === stage;
    const side = stage === "delayed" || stage === "cancelled";
    // a stage is reached when it is the current one, when dated evidence placed the project there, or when the pipeline
    // moved forward past it (a project under construction was announced); side branches only when current or dated
    const forward = !side && currentStage != null && (currentStage === "delayed" || currentStage === "cancelled" ? ["rumored", "proposed", "announced"].includes(stage) : projectTransition(stage, currentStage) === "forward");
    const reached = isCurrent || ev.has(stage) || forward;
    const e = ev.get(stage);
    return { stage, date: e?.date ?? null, reached, current: isCurrent, url: e?.url ?? null, sourceName: e?.sourceName ?? null, eventId: e?.eventId ?? null };
  });
}

function daysBetween(a: string | null, b: string | null): number | null {
  if (!a || !b) return null;
  const pa = parsePartialDate(a), pb = parsePartialDate(b);
  if (!pa || !pb) return null;
  const da = new Date(pa.length === 4 ? `${pa}-01-01` : pa.length === 7 ? `${pa}-01` : pa.slice(0, 10));
  const db = new Date(pb.length === 4 ? `${pb}-01-01` : pb.length === 7 ? `${pb}-01` : pb.slice(0, 10));
  if (Number.isNaN(da.getTime()) || Number.isNaN(db.getTime())) return null;
  return Math.round((db.getTime() - da.getTime()) / 86_400_000);
}

export function velocityDays(stages: ProjectStageDTO[]): ProjectDetail["velocityDays"] {
  const d = (s: Stage) => stages.find((x) => x.stage === s)?.date ?? null;
  return {
    announcedToPermitting: daysBetween(d("announced"), d("permitting")),
    permittingToApproval: daysBetween(d("permitting"), d("approved")),
    approvalToConstruction: daysBetween(d("approved"), d("under_construction")),
    constructionToOpening: daysBetween(d("under_construction"), d("operational")),
    announcedToConstruction: daysBetween(d("announced"), d("under_construction")),
  };
}

const COMPLETENESS_COLS = ["operator_id", "country_iso2", "lat", "planned_mw", "expected_opening", "announced_on", "description"];
export function projectCompleteness(row: Row): number {
  return Math.round((COMPLETENESS_COLS.filter((c) => row[c] != null && row[c] !== "").length / COMPLETENESS_COLS.length) * 100);
}

function investmentPoints(claims: ClaimDTO[]): HistoryPoint[] {
  return claims.filter((c) => /usd$/.test(c.predicate) && c.status !== "rejected").map((c) => ({ date: c.publishedAt && /^\d{4}/.test(c.publishedAt) ? c.publishedAt : c.firstObserved, field: "investmentUsd", predicate: c.predicate, value: c.value ?? c.valueText, sourceId: c.sourceId, sourceName: c.sourceName, sourceKind: c.sourceKind, url: c.url, claimId: c.id, kind: "claim" as const }));
}

export async function getProjectDetail(idOrSlug: string, opts: { radiusKm?: number } = {}): Promise<{ detail: ProjectDetail; sources: SourceRef[] } | null> {
  const sql = pg();
  const row = await resolveProjectRow(idOrSlug);
  if (!row) return null;
  const id = String(row.id);
  const lat = num(row.lat), lng = num(row.lng);
  const [sumRows, timelineRows, provenance, events, versions, campusRows, claims, nearbyInfra, dataQuality] = await Promise.all([
    sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where p.id = ${id}`,
    sql<Row[]>`select t.event_date, t.event_type, t.description, t.url, s.name as source_name from project_timeline t left join sources s on s.id = t.source_id where t.project_id = ${id}`,
    provenanceFor("project", id),
    eventsForProject(id, 50),
    documentVersionsFor("project", id),
    row.campus_id ? sql<Row[]>`select id, slug, name from campuses where id = ${String(row.campus_id)}` : Promise.resolve([] as Row[]),
    claimsFor("project", id),
    lat != null && lng != null ? nearby({ lat, lng, radiusKm: opts.radiusKm ?? 25, excludeProjectId: id, limitPerType: 50 }) : Promise.resolve(null),
    dataQualityFor("project", id, projectCompleteness(row), null),
  ]);
  const summary = projectSummary(sumRows[0] ?? row);
  const timeline = timelineRows
    .map((t) => ({ date: reqStr(t.event_date), type: reqStr(t.event_type), description: reqStr(t.description), url: str(t.url), sourceName: str(t.source_name) }))
    .sort((a, b) => partialDateSortKey(a.date) - partialDateSortKey(b.date));
  const statusHistory = events
    .filter((e) => e.eventType === "project_status_changed" || e.eventType === "status_changed")
    .map((e) => ({ date: e.effectiveDate ?? e.detectedAt, from: e.oldValue != null ? asStatus(typeof e.oldValue === "object" ? (e.oldValue as Record<string, unknown>).status : e.oldValue) : null, to: asStatus(typeof e.newValue === "object" && e.newValue ? (e.newValue as Record<string, unknown>).status : e.newValue), url: e.url || null }))
    .sort((a, b) => (a.date < b.date ? -1 : a.date > b.date ? 1 : 0));
  const stages = buildStages(row, timeline, events);
  const relatedEvents = events.filter((e) => e.project && e.project.id === id && !(e.entityType === "project" && e.entityId === id));
  const history = [...capacityHistory(provenance, claims, events), ...investmentPoints(claims)].sort((a, b) => (a.date < b.date ? -1 : a.date > b.date ? 1 : 0));
  const detail: ProjectDetail = {
    ...summary,
    description: str(row.description),
    sourceUrl: str(row.source_url),
    campus: campusRows[0] ? { id: reqStr(campusRows[0].id), slug: reqStr(campusRows[0].slug), name: reqStr(campusRows[0].name) } : null,
    stages,
    velocityDays: velocityDays(stages),
    timeline,
    statusHistory,
    claims,
    history,
    nearbyInfrastructure: nearbyInfra,
    relatedEvents,
    dataQuality,
    provenance,
    events,
    sourceHistory: buildSourceHistory(provenance, events, versions),
  };
  const sources = await sourceRefsFor(sourceIdsOf(provenance, events, versions, claims));
  return { detail, sources };
}

/** /projects/:slug/history */
export async function getProjectHistory(idOrSlug: string): Promise<{ history: EntityHistory; sources: SourceRef[] } | null> {
  const row = await resolveProjectRow(idOrSlug);
  if (!row) return null;
  const id = String(row.id);
  const [prov, claims, events] = await Promise.all([provenanceAll("project", id), claimsFor("project", id), eventsForSubject("project", id)]);
  return { history: entityHistory("project", id, prov, claims, events), sources: await sourceRefsFor(sourceIdsOf(prov, claims, events)) };
}

/** /projects/:slug/claims */
export async function getProjectClaims(idOrSlug: string, opts: { status?: string[]; predicate?: string } = {}): Promise<{ id: string; claims: ClaimDTO[]; sources: SourceRef[] } | null> {
  const row = await resolveProjectRow(idOrSlug);
  if (!row) return null;
  const id = String(row.id);
  const claims = await claimsFor("project", id, opts);
  return { id, claims, sources: await sourceRefsFor(sourceIdsOf(claims)) };
}

export interface PipelineAggregates {
  byStatus: Array<{ status: FacilityStatus; count: number; mw: number | null; investmentUsd: number | null }>;
  byYear: Array<{ year: string; count: number; mw: number | null }>;
  byCountry: Array<{ countryIso2: string; name: string | null; slug: string | null; count: number; mw: number | null }>;
  totals: { projects: number; plannedMw: number | null; investmentUsd: number | null; ai: number };
}

export async function projectPipeline(): Promise<PipelineAggregates> {
  const sql = pg();
  const live = projectLive(sql);
  const [st, yr, co, tot] = await Promise.all([
    sql<Row[]>`select p.status, count(*)::int as n, sum(p.planned_mw)::float as mw, sum(p.investment_usd)::float as inv from projects p where ${live} group by p.status order by n desc`,
    sql<Row[]>`select left(p.expected_opening, 4) as year, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p where ${live} and p.expected_opening ~ '^\\d{4}' and p.status not in ('cancelled','closed') group by 1 order by 1`,
    sql<Row[]>`select p.country_iso2, c.name, c.slug, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p left join countries c on c.iso2 = p.country_iso2 where ${live} and p.country_iso2 is not null group by 1,2,3 order by n desc, mw desc nulls last limit 15`,
    sql<Row[]>`select count(*)::int as n, sum(p.planned_mw)::float as mw, sum(p.investment_usd)::float as inv, count(*) filter (where p.is_ai or p.ai_evidence = any(${AI_LEVELS}))::int as ai from projects p where ${live}`,
  ]);
  return {
    byStatus: st.map((r) => ({ status: asStatus(r.status), count: int(r.n), mw: num(r.mw), investmentUsd: num(r.inv) })),
    byYear: yr.map((r) => ({ year: reqStr(r.year), count: int(r.n), mw: num(r.mw) })),
    byCountry: co.map((r) => ({ countryIso2: reqStr(r.country_iso2), name: str(r.name), slug: str(r.slug), count: int(r.n), mw: num(r.mw) })),
    totals: { projects: int(tot[0]?.n), plannedMw: num(tot[0]?.mw), investmentUsd: num(tot[0]?.inv), ai: int(tot[0]?.ai) },
  };
}