import type { ClaimDTO, CloudRegionSummary, EntityHistory, EventDTO, FacilityDetail, FacilityFilters, FacilitySummary, GridConstraintDTO, ProvenanceDTO, SourceRef } from "@dci/core";
import { pg, andAll, facilityJoins, facilitySummaryCols, mwExpr, page, bboxAround, haversineExpr, yearExpr, likePattern, PIPELINE_SET, cloudRegionCols, gridConstraintCols, gridConstraintJoins, POWER_EVENT_TYPES, GRID_EVENT_TYPES, AI_LEVELS, type Fragment, type Sql } from "../lib/sql.js";
import { int, num, record, reqIso, reqStr, str, strArray, type Row } from "../lib/rows.js";
import { cloudRegionSummary, facilitySummary, gridConstraintDto, gridConstraintFromEvent, provenanceDto } from "../lib/dto.js";
import { csv } from "../lib/http.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 { eventsForEntity, eventsWhere } from "./events.js";
import { projectsForFacility } from "./projects.js";
import { buildSourceHistory, documentVersionsFor, sourceIdsOf, sourceRefsFor } from "../lib/source-history.js";

export type FacilitySort = NonNullable<FacilityFilters["sort"]>;

export interface FacilityQuery extends Omit<FacilityFilters, "country" | "status" | "type" | "confidence"> {
  country?: string[];
  status?: string[];
  type?: string[];
  confidence?: string[];
  /** extra: restrict to ids */
  ids?: string[];
  /** extra: restrict to a metro id / country iso2 / operator id directly */
  metroId?: string;
  countryIso2?: string;
  operatorId?: string;
  /** extra: AI evidence levels (confirmed, likely, associated, unknown) */
  aiEvidence?: string[];
  /** extra: geo precision values */
  locationPrecision?: string[];
  /** extra: record scope (building | facility | campus) */
  recordScope?: string[];
}

/** Translate raw FacilityFilters (csv strings) into a FacilityQuery. */
export function toFacilityQuery(f: FacilityFilters): FacilityQuery {
  return { ...f, country: csv(f.country).map((s) => s.toUpperCase()), status: csv(f.status), type: csv(f.type), confidence: csv(f.confidence) };
}

/** WHERE fragment for a FacilityQuery (aliases f/o/m/c as in facilityJoins). */
export function facilityConds(sql: Sql, q: FacilityQuery): Fragment[] {
  const conds: Fragment[] = [sql`f.merged_into is null`];
  const mw = mwExpr(sql);
  if (q.q) {
    const text = q.q.trim();
    if (text) conds.push(sql`(f.search @@ plainto_tsquery('simple', ${text}) or f.name ilike ${likePattern(text)} or f.city ilike ${likePattern(text)} or o.name ilike ${likePattern(text)})`);
  }
  if (q.ids?.length) conds.push(sql`f.id = any(${q.ids})`);
  if (q.country?.length) conds.push(sql`f.country_iso2 = any(${q.country})`);
  if (q.countryIso2) conds.push(sql`f.country_iso2 = ${q.countryIso2}`);
  if (q.region) conds.push(sql`(c.region ilike ${q.region} or c.subregion ilike ${q.region} or f.region_name ilike ${q.region})`);
  if (q.metro) conds.push(sql`(m.slug = ${q.metro} or m.id = ${q.metro})`);
  if (q.metroId) conds.push(sql`f.metro_id = ${q.metroId}`);
  if (q.operator) conds.push(sql`(o.slug = ${q.operator} or o.id = ${q.operator} or f.owner_id in (select id from operators where slug = ${q.operator}))`);
  if (q.operatorId) conds.push(sql`(f.operator_id = ${q.operatorId} or f.owner_id = ${q.operatorId})`);
  if (q.status?.length) conds.push(sql`f.status = any(${q.status})`);
  if (q.type?.length) conds.push(sql`f.facility_type = any(${q.type})`);
  if (q.min_mw != null) conds.push(sql`${mw} >= ${q.min_mw}`);
  if (q.max_mw != null) conds.push(sql`${mw} <= ${q.max_mw}`);
  if (q.opened_from != null) conds.push(sql`${yearExpr(sql, sql`f.opened_on`)} >= ${q.opened_from}`);
  if (q.opened_to != null) conds.push(sql`${yearExpr(sql, sql`f.opened_on`)} <= ${q.opened_to}`);
  if (q.planned_from != null || q.planned_to != null) {
    const y = yearExpr(sql, sql`coalesce(f.opened_on, f.construction_started_on, f.announced_on)`);
    conds.push(sql`f.status = any(${PIPELINE_SET})`);
    if (q.planned_from != null) conds.push(sql`${y} >= ${q.planned_from}`);
    if (q.planned_to != null) conds.push(sql`${y} <= ${q.planned_to}`);
  }
  if (q.cloud) {
    conds.push(sql`(exists (select 1 from facility_tenants ft join operators p on p.id = ft.operator_id where ft.facility_id = f.id and ft.role = 'cloud' and (p.slug = ${q.cloud} or p.id = ${q.cloud}))
      or (f.metro_id is not null and exists (select 1 from cloud_regions r join operators p on p.id = r.provider_id where r.metro_id = f.metro_id and (p.slug = ${q.cloud} or p.id = ${q.cloud}))))`);
  }
  if (q.hyperscale === true) conds.push(sql`(f.is_hyperscale or f.facility_type = 'hyperscale')`);
  if (q.hyperscale === false) conds.push(sql`(not f.is_hyperscale and f.facility_type <> 'hyperscale')`);
  if (q.colocation === true) conds.push(sql`f.facility_type in ('colocation', 'carrier_hotel', 'wholesale')`);
  if (q.ai === true) conds.push(sql`(f.is_ai or f.facility_type = 'ai' or f.ai_evidence = any(${AI_LEVELS}))`);
  if (q.ai === false) conds.push(sql`(not f.is_ai and f.facility_type <> 'ai' and f.ai_evidence <> all(${AI_LEVELS}))`);
  if (q.aiEvidence?.length) conds.push(sql`f.ai_evidence = any(${q.aiEvidence})`);
  if (q.locationPrecision?.length) conds.push(sql`f.geo_precision = any(${q.locationPrecision})`);
  if (q.recordScope?.length) conds.push(sql`f.record_scope = any(${q.recordScope})`);
  if (q.renewable === true) conds.push(sql`f.renewable_claim is not null`);
  if (q.has_mw === true) conds.push(sql`${mw} is not null`);
  if (q.has_mw === false) conds.push(sql`${mw} is null`);
  if (q.has_geo === true) conds.push(sql`f.lat is not null and f.lng is not null`);
  if (q.has_geo === false) conds.push(sql`f.lat is null`);
  if (q.confidence?.length) conds.push(sql`f.confidence = any(${q.confidence})`);
  return conds;
}

function orderBy(sql: Sql, sort: FacilitySort | undefined, order: "asc" | "desc" | undefined): Fragment {
  const dir = order ?? (sort === "name" ? "asc" : "desc");
  const asc = dir === "asc";
  switch (sort) {
    case "name": return asc ? sql`f.name asc, f.id asc` : sql`f.name desc, f.id desc`;
    case "mw": return asc ? sql`${mwExpr(sql)} asc nulls last, f.name asc` : sql`${mwExpr(sql)} desc nulls last, f.name asc`;
    case "opened": return asc ? sql`f.opened_on asc nulls last, f.name asc` : sql`f.opened_on desc nulls last, f.name asc`;
    case "completeness": return asc ? sql`f.completeness asc, f.name asc` : sql`f.completeness desc, f.name asc`;
    case "updated": default: return asc ? sql`f.updated_at asc, f.id asc` : sql`f.updated_at desc, f.id desc`;
  }
}

export async function listFacilities(q: FacilityQuery): Promise<{ items: FacilitySummary[]; total: number; page: number; perPage: number }> {
  const sql = pg();
  const pg_ = page(q.page, q.per_page, 100, 24);
  const rows = await sql<Row[]>`
    select ${facilitySummaryCols(sql)}, count(*) over() as total
    from facilities f ${facilityJoins(sql)}
    where ${andAll(sql, facilityConds(sql, q))}
    order by ${orderBy(sql, q.sort, q.order)}
    limit ${pg_.perPage} offset ${pg_.offset}`;
  const total = rows.length ? int(rows[0]!.total) : pg_.page > 1 ? await countFacilities(q) : 0;
  return { items: rows.map(facilitySummary), total, page: pg_.page, perPage: pg_.perPage };
}

export async function countFacilities(q: FacilityQuery): Promise<number> {
  const sql = pg();
  const rows = await sql<Row[]>`select count(*)::int as n from facilities f ${facilityJoins(sql)} where ${andAll(sql, facilityConds(sql, q))}`;
  return int(rows[0]?.n);
}

export async function facilitiesByIds(ids: string[]): Promise<FacilitySummary[]> {
  if (!ids.length) return [];
  const sql = pg();
  const rows = await sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.id = any(${ids})`;
  const by = new Map(rows.map((r) => [String(r.id), facilitySummary(r)]));
  return ids.map((id) => by.get(id)).filter((x): x is FacilitySummary => Boolean(x));
}

/** Facilities matching an arbitrary condition over `facilities f` (+ facilityJoins aliases), ordered by MW. */
export async function facilitiesWhere(cond: Fragment, limit = 20, orderBy?: Fragment): Promise<FacilitySummary[]> {
  const sql = pg();
  const rows = await sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and (${cond}) order by ${orderBy ?? sql`${mwExpr(sql)} desc nulls last, f.name`} limit ${limit}`;
  return rows.map(facilitySummary);
}

export async function recentlyVerifiedFacilities(limit = 10): Promise<FacilitySummary[]> {
  const sql = pg();
  const rows = await sql<Row[]>`
    select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)}
    where f.merged_into is null and f.last_verified is not null
    order by f.last_verified desc limit ${limit}`;
  return rows.map(facilitySummary);
}

async function nearbyFacilities(id: string, lat: number, lng: number, km = 25, limit = 8): Promise<FacilitySummary[]> {
  const sql = pg();
  const b = bboxAround(lat, lng, km);
  const dist = haversineExpr(sql, lat, lng);
  const rows = await sql<Row[]>`
    select ${facilitySummaryCols(sql)}, ${dist} as distance_km
    from facilities f ${facilityJoins(sql)}
    where f.merged_into is null and f.id <> ${id} and f.lat between ${b.minLat} and ${b.maxLat} and f.lng between ${b.minLng} and ${b.maxLng} and ${dist} <= ${km}
    order by ${dist} asc limit ${limit}`;
  return rows.map(facilitySummary);
}

async function cloudRegionsInMetro(metroId: string | null, limit = 12): Promise<CloudRegionSummary[]> {
  if (!metroId) return [];
  const sql = pg();
  const rows = await sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.metro_id = ${metroId} order by pr.name, r.code limit ${limit}`;
  return rows.map(cloudRegionSummary);
}

export async function provenanceFor(entityType: string, entityId: string, limit = 400): Promise<ProvenanceDTO[]> {
  const sql = pg();
  const rows = await sql<Row[]>`
    select p.field, p.value, p.source_id, s.name as source_name, s.kind as source_kind, p.url, p.first_observed, p.last_observed, p.retrieved_at, p.confidence, p.is_estimate, p.method, p.is_winner, p.scope, p.run_id, p.document_id
    from provenance p left join sources s on s.id = p.source_id
    where p.entity_type = ${entityType} and p.entity_id = ${entityId} and p.is_current
    order by p.field, p.is_winner desc, p.last_observed desc limit ${limit}`;
  return rows.map(provenanceDto);
}

/** Grid constraints (grid_constraints rows + grid / utility / power events) for a metro and/or country. */
export async function gridConstraintsFor(scope: { metroId?: string | null; countryIso2?: string | null }, limit = 20): Promise<GridConstraintDTO[]> {
  const sql = pg();
  const conds: Fragment[] = [];
  if (scope.metroId) conds.push(sql`g.metro_id = ${scope.metroId}`);
  if (scope.countryIso2 && !scope.metroId) conds.push(sql`g.country_iso2 = ${scope.countryIso2}`);
  if (!conds.length) return [];
  const [rows, events] = await Promise.all([
    sql<Row[]>`select ${gridConstraintCols(sql)} from grid_constraints g ${gridConstraintJoins(sql)} where ${conds.reduce<Fragment>((a, c) => sql`${a} or ${c}`, sql`false`)} order by g.effective_date desc nulls last, g.created_at desc limit ${limit}`,
    scope.metroId ? eventsWhere(sql`e.metro_id = ${scope.metroId} and e.event_type = any(${GRID_EVENT_TYPES})`, limit) : Promise.resolve([] as EventDTO[]),
  ]);
  const out = rows.map(gridConstraintDto);
  const seen = new Set(out.map((g) => g.eventId).filter(Boolean));
  for (const e of events) if (!seen.has(e.id)) out.push(gridConstraintFromEvent(e));
  return out.slice(0, limit);
}

export const POWER_CONTEXT_NOTE = "utilityCapacityMw / gridConnectionMw are utility-side figures (power available or contracted from the grid), not IT load — never compare them with itCapacityMw. countryEnergy shows national grid averages (renewable share, generation), which do not describe this facility's contracted electricity or power purchase agreements. Grid constraints are public reports attached to the facility's market or country, not to the site itself.";

/** Resolve a facility by slug or id, following merged_into to the survivor. */
export async function resolveFacilityRow(idOrSlug: string): Promise<Row | null> {
  const sql = pg();
  const base = await findBySlugOrId("facilities", idOrSlug);
  if (!base) return null;
  const mergedInto = str(base.merged_into);
  return mergedInto ? ((await sql<Row[]>`select * from facilities where id = ${mergedInto} limit 1`)[0] ?? base) : base;
}

export async function getFacilityDetail(idOrSlug: string, opts: { radiusKm?: number } = {}): Promise<{ detail: FacilityDetail; sources: SourceRef[] } | null> {
  const sql = pg();
  const row = await resolveFacilityRow(idOrSlug);
  if (!row) return null;
  const id = String(row.id);
  const lat = num(row.lat), lng = num(row.lng);
  const metroId = str(row.metro_id);
  const countryIso2 = str(row.country_iso2);
  const operatorId = str(row.operator_id);
  const [sumRows, aliasRows, tenantRows, ixpRows, cloudRegions, nearbyRows, projects, provenance, events, versions, ownerRows, campusRows, buildingRows, roleRows, claims, nearbyInfra, powerEvents, gridConstraints, energyRows, dataQuality] = await Promise.all([
    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.id = ${id}`,
    sql<Row[]>`select alias from facility_aliases where facility_id = ${id} order by alias`,
    sql<Row[]>`select t.role, t.asn, o.id, o.slug, o.name from facility_tenants t join operators o on o.id = t.operator_id where t.facility_id = ${id} order by o.name`,
    sql<Row[]>`select x.id, x.slug, x.name from facility_ixps fx join ixps x on x.id = fx.ixp_id where fx.facility_id = ${id} order by x.name`,
    cloudRegionsInMetro(metroId),
    lat != null && lng != null ? nearbyFacilities(id, lat, lng) : Promise.resolve([] as FacilitySummary[]),
    projectsForFacility(id, operatorId, metroId),
    provenanceFor("facility", id),
    eventsForEntity("facility", id, 50),
    documentVersionsFor("facility", id),
    row.owner_id ? sql<Row[]>`select id, slug, name from operators where id = ${String(row.owner_id)}` : Promise.resolve([] as Row[]),
    row.campus_id ? sql<Row[]>`select id, slug, name from campuses where id = ${String(row.campus_id)}` : Promise.resolve([] as Row[]),
    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.parent_facility_id = ${id} and f.merged_into is null order by f.name`,
    sql<Row[]>`select o.id, o.slug, o.name, (o.id = ${str(row.developer_id)}) as is_developer, (o.id = ${str(row.landowner_id)}) as is_landowner from operators o where o.id in (${str(row.developer_id) ?? ""}, ${str(row.landowner_id) ?? ""})`,
    claimsFor("facility", id),
    lat != null && lng != null ? nearby({ lat, lng, radiusKm: opts.radiusKm ?? 25, excludeFacilityId: id, limitPerType: 50 }) : Promise.resolve(null),
    eventsWhere(sql`e.event_type = any(${POWER_EVENT_TYPES}) and ((e.entity_type = 'facility' and e.entity_id = ${id}) ${operatorId && metroId ? sql`or (e.operator_id = ${operatorId} and e.metro_id = ${metroId})` : sql``})`, 20),
    gridConstraintsFor({ metroId, countryIso2 }),
    countryIso2 ? sql<Row[]>`select renewable_share, electricity_twh, stats_year from countries where iso2 = ${countryIso2}` : Promise.resolve([] as Row[]),
    dataQualityFor("facility", id, int(row.completeness), str(row.last_verified)),
  ]);
  const summary = facilitySummary(sumRows[0] ?? row);
  const carriers = tenantRows.filter((t) => t.role !== "cloud").map((t) => ({ id: String(t.id), slug: String(t.slug), name: String(t.name), asn: num(t.asn) }));
  const cloudProviders = tenantRows.filter((t) => t.role === "cloud").map((t) => ({ id: String(t.id), slug: String(t.slug), name: String(t.name) }));
  const owner = ownerRows[0] ? { id: String(ownerRows[0].id), slug: String(ownerRows[0].slug), name: String(ownerRows[0].name) } : null;
  const campus = campusRows[0] ? { id: String(campusRows[0].id), slug: String(campusRows[0].slug), name: String(campusRows[0].name) } : null;
  const refOf = (r: Row | undefined) => (r ? { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) } : null);
  const developer = refOf(roleRows.find((r) => r.is_developer === true));
  const landowner = refOf(roleRows.find((r) => r.is_landowner === true));
  const energy = energyRows[0];
  const detail: FacilityDetail = {
    ...summary,
    buildings: buildingRows.map(facilitySummary),
    developer,
    landowner,
    tenants: tenantRows.map((t) => ({ id: String(t.id), slug: String(t.slug), name: String(t.name), role: reqStr(t.role, "tenant") })),
    claims,
    capacityHistory: capacityHistory(provenance, claims, events),
    nearbyInfrastructure: nearbyInfra,
    powerContext: {
      utilityCapacityMw: num(row.utility_capacity_mw),
      gridConnectionMw: num(row.grid_connection_mw),
      powerEvents,
      gridConstraints,
      countryEnergy: energy ? { renewableShare: num(energy.renewable_share), electricityTwh: num(energy.electricity_twh), statsYear: num(energy.stats_year) } : null,
      note: POWER_CONTEXT_NOTE,
    },
    dataQuality,
    aliases: aliasRows.map((a) => String(a.alias)),
    owner,
    campus,
    address: str(row.address),
    postalCode: str(row.postal_code),
    geoSource: str(row.geo_source),
    tier: str(row.tier),
    buildingSqm: num(row.building_sqm),
    siteAreaHa: num(row.site_area_ha),
    rackCount: num(row.rack_count),
    pue: num(row.pue),
    coolingType: str(row.cooling_type),
    renewableClaim: str(row.renewable_claim),
    constructionStartedOn: str(row.construction_started_on),
    announcedOn: str(row.announced_on),
    website: str(row.website),
    description: str(row.description),
    certifications: strArray(row.certifications),
    carriers,
    cloudProviders,
    ixps: ixpRows.map((x) => ({ id: String(x.id), slug: String(x.slug), name: String(x.name) })),
    cloudRegions,
    nearby: nearbyRows,
    projects,
    provenance,
    events,
    sourceHistory: buildSourceHistory(provenance, events, versions),
    externalIds: record(row.external_ids),
    firstSeen: reqIso(row.first_seen),
    updatedAt: reqIso(row.updated_at),
  };
  const sources = await sourceRefsFor(sourceIdsOf(provenance, events, versions, claims));
  return { detail, sources };
}

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

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

/** /datacenters/:id/provenance — every observation, current and superseded. */
export async function getFacilityProvenance(idOrSlug: string): Promise<{ id: string; provenance: ProvenanceDTO[]; current: number; sources: SourceRef[] } | null> {
  const row = await resolveFacilityRow(idOrSlug);
  if (!row) return null;
  const id = String(row.id);
  const provenance = await provenanceAll("facility", id);
  const sql = pg();
  const cur = await sql<Row[]>`select count(*)::int as n from provenance where entity_type = 'facility' and entity_id = ${id} and is_current`;
  return { id, provenance, current: int(cur[0]?.n), sources: await sourceRefsFor(sourceIdsOf(provenance)) };
}

/** Distinct facility cities matching a text (for search). */
export async function searchCities(text: string, limit = 5): Promise<Array<{ city: string; countryIso2: string | null; count: number }>> {
  const sql = pg();
  const rows = await sql<Row[]>`
    select f.city, f.country_iso2, count(*)::int as n
    from facilities f
    where f.merged_into is null and f.city is not null and (f.city ilike ${likePattern(text)} or similarity(f.city, ${text}) > 0.45)
    group by f.city, f.country_iso2
    order by (lower(f.city) = lower(${text})) desc, n desc limit ${limit}`;
  return rows.map((r) => ({ city: String(r.city), countryIso2: str(r.country_iso2), count: int(r.n) }));
}