import type { GridConstraintDTO, MetroDetail, MetroSummary } from "@dci/core";
import { pg, mwExpr, yearExpr, cloudRegionCols, likePattern, facilityView, knownMwAgg, countedAgg, projectLive, facilityJoins, facilitySummaryCols, eventCols, eventJoins, gridConstraintCols, gridConstraintJoins, AI_LEVELS, GRID_EVENT_TYPES, OPERATIONAL_SET, type Fragment, type Sql } from "../lib/sql.js";
import { int, iso, num, reqStr, str, strArray, type Row } from "../lib/rows.js";
import { cloudRegionSummary, facilitySummary, gridConstraintDto, gridConstraintFromEvent, growthSeries, round2, share } from "../lib/dto.js";
import { findBySlugOrId } from "../lib/resolve.js";
import { listFacilities } from "./facilities.js";
import { operatorsForScope } from "./operators.js";
import { projectsWhere } from "./projects.js";
import { eventsForMetro, toEventDtos } from "./events.js";
import { rankingPositions } from "./rankings.js";
import { concentration, coverageRow, dimAggCols, momentum, pipelineBreakdown, scopeFor } from "../lib/pipeline.js";
import { claimsFor } from "../lib/quality.js";

function metroSummary(r: Row): MetroSummary {
  const n = int(r.facility_count);
  return {
    id: reqStr(r.id),
    slug: reqStr(r.slug),
    name: reqStr(r.name),
    countryIso2: reqStr(r.country_iso2),
    countryName: str(r.country_name) ?? undefined,
    regionName: str(r.region_name),
    lat: num(r.lat) ?? 0,
    lng: num(r.lng) ?? 0,
    facilityCount: n,
    operationalCount: int(r.operational),
    constructionCount: int(r.construction),
    plannedCount: int(r.planned),
    knownMw: round2(num(r.known_mw)),
    constructionMw: round2(num(r.construction_mw)),
    plannedMw: round2(num(r.planned_mw)),
    operatorCount: int(r.operator_count),
    cloudRegionCount: int(r.cloud_region_count),
    ixpCount: int(r.ixp_count),
    projectCount: int(r.project_count),
    projectPlannedMw: round2(num(r.project_planned_mw)),
    aiCount: int(r.ai),
    mwCoverage: n ? share(int(r.with_mw), n) : 0,
  };
}

const SELECT = (sql: Sql) => sql`
  select m.id, m.slug, m.name, m.country_iso2, c.name as country_name, m.region_name, m.lat, m.lng, m.aliases, m.description, m.updated_at,
    coalesce(fa.facilities, 0) as facility_count, coalesce(fa.operational, 0) as operational, coalesce(fa.construction, 0) as construction, coalesce(fa.planned, 0) as planned,
    fa.known_mw, fa.construction_mw, fa.planned_mw, coalesce(fa.operators, 0) as operator_count, coalesce(fa.with_mw, 0) as with_mw, coalesce(fa.ai, 0) as ai,
    coalesce(cr.n, 0) as cloud_region_count, coalesce(ix.n, 0) as ixp_count, coalesce(pj.n, 0) as project_count, pj.mw as project_planned_mw
  from metros m
  left join countries c on c.iso2 = m.country_iso2
  left join (select f.metro_id, ${dimAggCols(sql)} from ${facilityView(sql)} f where f.metro_id is not null group by f.metro_id) fa on fa.metro_id = m.id
  left join (select metro_id, count(*)::int as n from cloud_regions where metro_id is not null and status <> 'retired' group by 1) cr on cr.metro_id = m.id
  left join (select metro_id, count(*)::int as n from ixps where metro_id is not null group by 1) ix on ix.metro_id = m.id
  left join (select p.metro_id, count(*)::int as n, sum(p.planned_mw) filter (where p.status in ('rumored','proposed','announced','permitting','approved','delayed','under_construction'))::float as mw from projects p where p.metro_id is not null and ${projectLive(sql)} group by p.metro_id) pj on pj.metro_id = m.id`;

export async function listMetros(f: { country?: string; q?: string; limit?: number } = {}): Promise<MetroSummary[]> {
  const sql = pg();
  const conds: Fragment[] = [sql`true`];
  if (f.country) conds.push(sql`m.country_iso2 = ${f.country.toUpperCase()}`);
  if (f.q) conds.push(sql`(m.name ilike ${likePattern(f.q)} or similarity(m.name, ${f.q}) > 0.35 or exists (select 1 from unnest(m.aliases) a where a ilike ${likePattern(f.q)}))`);
  const rows = await sql<Row[]>`${SELECT(sql)} where ${conds.reduce<Fragment>((a, c) => sql`${a} and ${c}`, sql`true`)}
    order by coalesce(fa.facilities, 0) desc, coalesce(cr.n, 0) desc, m.name limit ${f.limit ?? 1000}`;
  return rows.map(metroSummary);
}

const CONSTRAINT_KINDS: Array<[RegExp, MetroDetail["constraints"][number]["kind"]]> = [
  [/\b(moratorium|pause on|halt(ed|s)? (new )?(data ?cent|approvals)|ban on)\b/i, "moratorium"],
  [/\b(grid (constraint|capacity|connection)|interconnection (queue|delay)|transmission)\b/i, "grid"],
  [/\b(power (shortage|constraint|crunch|capacity|availability)|electricity (shortage|constraint)|megawatts? (short|unavailable)|energy (crunch|shortage))\b/i, "power"],
  [/\b(water (usage|shortage|restriction|consumption|permit))\b/i, "water"],
  [/\b(land (scarcity|shortage|constraint|price|availability)|zoning|rezoning denied|land[- ]use)\b/i, "land"],
];

async function metroConstraints(metroId: string, name: string, aliases: string[], limit = 10): Promise<MetroDetail["constraints"]> {
  const sql = pg();
  const terms = [name, ...aliases].filter(Boolean);
  const nameCond = terms.length ? terms.map((t) => sql`(n.title ilike ${likePattern(t)} or n.summary ilike ${likePattern(t)})`).reduce<Fragment>((a, c) => sql`${a} or ${c}`, sql`false`) : sql`false`;
  const rows = await sql<Row[]>`
    select n.title, n.summary, n.url, n.published_at, n.created_at, s.name as source_name
    from news_items n left join sources s on s.id = n.source_id
    where (n.metro_id = ${metroId} or n.mentions->>'metroId' = ${metroId} or n.mentions->'metros' ? ${metroId} or n.mentions->'cities' ?| ${terms}::text[] or (${nameCond}))
      and (n.title ~* '(moratorium|grid|power|electricity|substation|water|zoning|land|transmission|interconnection)' or n.summary ~* '(moratorium|grid (constraint|capacity)|power (shortage|constraint|crunch)|water (usage|shortage|restriction)|zoning|land (scarcity|shortage|constraint))')
    order by n.published_at desc nulls last, n.created_at desc limit 40`;
  const out: MetroDetail["constraints"] = [];
  for (const r of rows) {
    const text = `${reqStr(r.title)} ${reqStr(r.summary)}`;
    const kind = CONSTRAINT_KINDS.find(([re]) => re.test(text))?.[1];
    if (!kind) continue;
    out.push({ kind, summary: reqStr(r.title), url: reqStr(r.url), sourceName: reqStr(r.source_name, "news"), date: iso(r.published_at) ?? iso(r.created_at) });
    if (out.length >= limit) break;
  }
  return out;
}

/** grid_constraints rows + grid / utility / power events for a metro or a country, deduplicated by event id. */
export async function gridConstraintsFor(scope: { metroId?: string; countryIso2?: string }, limit = 30): Promise<GridConstraintDTO[]> {
  const sql = pg();
  const gCond = scope.metroId ? sql`g.metro_id = ${scope.metroId}` : scope.countryIso2 ? sql`g.country_iso2 = ${scope.countryIso2}` : sql`true`;
  const eCond = scope.metroId ? sql`(e.metro_id = ${scope.metroId} or (e.entity_type = 'facility' and e.entity_id in (select id from facilities where metro_id = ${scope.metroId})))` : scope.countryIso2 ? sql`e.country_iso2 = ${scope.countryIso2}` : sql`true`;
  const [rows, evRows] = await Promise.all([
    sql<Row[]>`select ${gridConstraintCols(sql)} from grid_constraints g ${gridConstraintJoins(sql)} where ${gCond} order by g.effective_date desc nulls last, g.created_at desc limit ${limit}`,
    sql<Row[]>`select ${eventCols(sql)} from events e ${eventJoins(sql)} where ${eCond} and e.event_type = any(${GRID_EVENT_TYPES}) and e.review_status <> 'rejected' order by e.detected_at desc limit ${limit}`,
  ]);
  const out = rows.map(gridConstraintDto);
  const seen = new Set(out.map((g) => g.eventId).filter(Boolean));
  for (const e of await toEventDtos(evRows)) { if (seen.has(e.id)) continue; seen.add(e.id); out.push(gridConstraintFromEvent(e)); }
  return out.slice(0, limit);
}

export async function getMetroDetail(idOrSlug: string, fPage = 1): Promise<MetroDetail | null> {
  const sql = pg();
  const base = await findBySlugOrId("metros", idOrSlug);
  if (!base) return null;
  const id = String(base.id);
  const aliases = strArray(base.aliases);
  const scope = scopeFor(sql, { metroId: id });
  const [sumRows, operators, cloudProviderRows, cloudRegionRows, ixpRows, facilities, projects, recentEvents, constraints, growthRows, rankings, conc, mom, pipeline, gridConstraints, aiRows, openingRows, coverage, claims] = await Promise.all([
    sql<Row[]>`${SELECT(sql)} where m.id = ${id}`,
    operatorsForScope({ metroId: id }, 15),
    sql<Row[]>`select pr.id, pr.slug, pr.name, count(*)::int as n from cloud_regions r join operators pr on pr.id = r.provider_id where r.metro_id = ${id} group by 1, 2, 3 order by n desc, pr.name`,
    sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.metro_id = ${id} order by pr.name, r.code`,
    sql<Row[]>`select id, slug, name, network_count from ixps where metro_id = ${id} order by network_count desc nulls last, name`,
    listFacilities({ metroId: id, page: fPage, per_page: 50, sort: "mw", order: "desc" }),
    projectsWhere(sql`${projectLive(sql)} and p.metro_id = ${id}`, 20),
    eventsForMetro(id, 20),
    metroConstraints(id, String(base.name), aliases),
    sql<Row[]>`select ${yearExpr(sql, sql`f.opened_on`)} as year, count(*) filter (where ${countedAgg(sql)})::int as n, sum(${knownMwAgg(sql)})::float as mw from ${facilityView(sql)} f where f.metro_id = ${id} and f.opened_on ~ '^\\d{4}' group by 1 order by 1`,
    rankingPositions("metros", [id, String(base.slug)]),
    concentration(scope),
    momentum(scope),
    pipelineBreakdown(scope),
    gridConstraintsFor({ metroId: id }),
    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and f.metro_id = ${id} and f.ai_evidence = any(${AI_LEVELS}) order by ${mwExpr(sql)} desc nulls last, f.name limit 20`,
    sql<Row[]>`select ${yearExpr(sql, sql`f.opened_on`)} as year, count(*) filter (where ${countedAgg(sql)})::int as n, sum(${knownMwAgg(sql)}) filter (where f.status = any(${OPERATIONAL_SET}))::float as mw from ${facilityView(sql)} f where f.metro_id = ${id} and f.opened_on ~ '^\\d{4}' group by 1 order by 1`,
    coverageRow(id, String(base.name), String(base.slug), scope),
    Promise.all([claimsFor("metro", id), claimsFor("market", id)]).then(([a, b]) => [...a, ...b]),
  ]);
  const summary = metroSummary(sumRows[0] ?? { ...base, facility_count: 0 });
  return {
    ...summary,
    concentration: conc,
    momentum: mom,
    pipeline,
    gridConstraints,
    aiFacilities: aiRows.map(facilitySummary),
    openingTimeline: openingRows.filter((r) => int(r.year) > 0).map((r) => ({ year: int(r.year), opened: int(r.n), openedMw: round2(num(r.mw)) })),
    coverage,
    claims,
    aliases,
    description: str(base.description),
    operators,
    cloudProviders: cloudProviderRows.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), regionCount: int(r.n) })),
    cloudRegions: cloudRegionRows.map(cloudRegionSummary),
    ixps: ixpRows.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), networkCount: num(r.network_count) })),
    facilities: facilities.items,
    projects,
    recentEvents,
    constraints,
    growth: growthSeries(growthRows),
    rankings,
  };
}