/**
 * /explore — structured query over facilities or live projects with facets, charts (containment-aware MW) and
 * map points. Facets and charts are computed on the FULL filtered set; `items` is one page.
 */
import type { ExploreQuery, ExploreResponse, MapPoint } from "@dci/core";
import { pg, andAll, facilityJoins, facilitySummaryCols, facilityView, knownMwAgg, pipelineMwAgg, countedAgg, hasMwAgg, mwExpr, projectJoins, projectSummaryCols, projectLive, yearExpr, likePattern, page, PIPELINE_SET, OPERATIONAL_SET, type Fragment, type Sql } from "../lib/sql.js";
import { int, num, reqStr, str, type Row } from "../lib/rows.js";
import { asPrecision, asStatus, asType, facilitySummary, projectSummary, round2, share } from "../lib/dto.js";
import { csv } from "../lib/http.js";
import { facilityConds, toFacilityQuery } from "./facilities.js";
import { MAX_POINTS } from "./map.js";

export const EXPLORE_METHODOLOGY = "Facets and charts cover the whole filtered set (items are one page). Facility MW in charts is containment-aware (a campus and its buildings are never both summed) and uses published figures only: IT capacity, else total power, else planned power. Project MW is the planned MW published on live project records (false positives hidden by review and merged duplicates excluded). mwCoverage is the share of rows with any MW figure. Map points are capped at 5 000 (degraded = true when the cap was hit).";

const AI_LEVELS_FOR: Record<string, string[]> = { confirmed: ["confirmed"], likely: ["confirmed", "likely"], associated: ["confirmed", "likely", "associated"] };

function aiCond(sql: Sql, alias: Fragment, ai: ExploreQuery["ai"], legacyFlag: Fragment): Fragment | null {
  if (!ai) return null;
  if (ai === "any") return sql`(${alias}.ai_evidence <> 'unknown' or ${legacyFlag})`;
  const levels = AI_LEVELS_FOR[ai] ?? ["confirmed", "likely"];
  return ai === "confirmed" ? sql`${alias}.ai_evidence = any(${levels})` : sql`(${alias}.ai_evidence = any(${levels}) or ${legacyFlag})`;
}

function facilityExploreConds(sql: Sql, q: ExploreQuery): Fragment[] {
  const conds = facilityConds(sql, toFacilityQuery({ q: q.q, country: q.country, metro: q.metro, operator: q.operator, status: q.status, type: q.type, min_mw: q.min_mw, max_mw: q.max_mw, hyperscale: q.hyperscale, has_mw: q.has_mw, confidence: q.confidence, opened_from: q.opened_from, opened_to: q.opened_to }));
  const ai = aiCond(sql, sql`f`, q.ai, sql`f.is_ai`);
  if (ai) conds.push(ai);
  const prec = csv(q.location_precision);
  if (prec.length) conds.push(sql`f.geo_precision = any(${prec})`);
  const expected = yearExpr(sql, sql`coalesce(f.opened_on, f.construction_started_on, f.announced_on)`);
  if (q.expected_before != null) conds.push(sql`f.status = any(${PIPELINE_SET}) and ${expected} <= ${q.expected_before}`);
  if (q.expected_after != null) conds.push(sql`f.status = any(${PIPELINE_SET}) and ${expected} >= ${q.expected_after}`);
  if (q.announced_since) conds.push(sql`f.announced_on >= ${q.announced_since}`);
  return conds;
}

function projectExploreConds(sql: Sql, q: ExploreQuery): Fragment[] {
  const conds: Fragment[] = [projectLive(sql)];
  const statuses = csv(q.project_status ?? q.status);
  if (statuses.length) conds.push(sql`p.status = any(${statuses})`);
  const countries = csv(q.country).map((c) => c.toUpperCase());
  if (countries.length) conds.push(sql`p.country_iso2 = any(${countries})`);
  if (q.metro) conds.push(sql`(m.slug = ${q.metro} or m.id = ${q.metro})`);
  if (q.operator) conds.push(sql`(o.slug = ${q.operator} or o.id = ${q.operator})`);
  if (q.min_mw != null) conds.push(sql`p.planned_mw >= ${q.min_mw}`);
  if (q.max_mw != null) conds.push(sql`p.planned_mw <= ${q.max_mw}`);
  const ai = aiCond(sql, sql`p`, q.ai, sql`p.is_ai`);
  if (ai) conds.push(ai);
  if (q.hyperscale === true) conds.push(sql`exists (select 1 from operators ho where ho.id = p.operator_id and ho.kind = 'hyperscaler')`);
  const expected = yearExpr(sql, sql`p.expected_opening`);
  if (q.expected_before != null) conds.push(sql`${expected} <= ${q.expected_before}`);
  if (q.expected_after != null) conds.push(sql`${expected} >= ${q.expected_after}`);
  if (q.announced_since) conds.push(sql`p.announced_on >= ${q.announced_since}`);
  const conf = csv(q.confidence);
  if (conf.length) conds.push(sql`p.confidence = any(${conf})`);
  const cls = csv(q.project_class);
  if (cls.length) conds.push(sql`p.project_class = any(${cls})`);
  const prec = csv(q.location_precision);
  if (prec.length) conds.push(sql`p.geo_precision = any(${prec})`);
  if (q.has_mw === true) conds.push(sql`p.planned_mw is not null`);
  if (q.has_mw === false) conds.push(sql`p.planned_mw is null`);
  if (q.q) conds.push(sql`(p.name ilike ${likePattern(q.q)} or o.name ilike ${likePattern(q.q)} or p.city ilike ${likePattern(q.q)})`);
  return conds;
}

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

function projectOrder(sql: Sql, sort: string | undefined, order: "asc" | "desc" | undefined): Fragment {
  const asc = (order ?? (sort === "name" ? "asc" : "desc")) === "asc";
  const dir = asc ? sql`asc` : sql`desc`;
  switch (sort) {
    case "name": return sql`p.name ${dir}, p.id`;
    case "mw": return sql`p.planned_mw ${dir} nulls last, p.name`;
    case "announced": return sql`p.announced_on ${dir} nulls last, p.name`;
    case "opening": case "opened": return sql`p.expected_opening ${dir} nulls last, p.name`;
    default: return sql`p.last_update ${dir}, p.id`;
  }
}

export async function explore(q: ExploreQuery): Promise<ExploreResponse> {
  const sql = pg();
  const pg_ = page(q.page, q.per_page, 100, 24);
  const entity = q.entity ?? "facilities";
  if (entity === "projects") {
    const where = andAll(sql, projectExploreConds(sql, q));
    const [items, facets, charts, pts, cov] = await Promise.all([
      sql<Row[]>`select ${projectSummaryCols(sql)}, count(*) over() as total from projects p ${projectJoins(sql)} where ${where} order by ${projectOrder(sql, q.sort, q.order)} limit ${pg_.perPage} offset ${pg_.offset}`,
      Promise.all([
        sql<Row[]>`select p.status as key, count(*)::int as n from projects p ${projectJoins(sql)} where ${where} group by 1 order by n desc`,
        sql<Row[]>`select p.country_iso2 as key, c.name, count(*)::int as n from projects p ${projectJoins(sql)} left join countries c on c.iso2 = p.country_iso2 where ${where} and p.country_iso2 is not null group by 1, 2 order by n desc limit 30`,
        sql<Row[]>`select o.slug as key, o.name, count(*)::int as n from projects p ${projectJoins(sql)} where ${where} and o.id is not null group by 1, 2 order by n desc limit 15`,
        sql<Row[]>`select p.ai_evidence as key, count(*)::int as n from projects p ${projectJoins(sql)} where ${where} group by 1 order by n desc`,
      ]),
      Promise.all([
        sql<Row[]>`select p.status as key, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p ${projectJoins(sql)} where ${where} group by 1 order by n desc`,
        sql<Row[]>`select p.country_iso2 as key, c.name, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p ${projectJoins(sql)} left join countries c on c.iso2 = p.country_iso2 where ${where} and p.country_iso2 is not null group by 1, 2 order by n desc limit 15`,
        sql<Row[]>`select ${yearExpr(sql, sql`p.expected_opening`)} as year, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p ${projectJoins(sql)} where ${where} and p.expected_opening ~ '^\\d{4}' group by 1 order by 1`,
      ]),
      sql<Row[]>`select p.id, p.slug, p.name, o.name as op_name, p.lat, p.lng, p.status, p.planned_mw, p.geo_precision, p.is_ai, p.ai_evidence, p.country_iso2, ${yearExpr(sql, sql`p.expected_opening`)} as y from projects p ${projectJoins(sql)} where ${where} and p.lat is not null and p.lng is not null order by p.planned_mw desc nulls last limit ${MAX_POINTS + 1}`,
      sql<Row[]>`select count(*)::int as n, count(*) filter (where p.planned_mw is not null)::int as with_mw from projects p ${projectJoins(sql)} where ${where}`,
    ]);
    const [fStatus, fCountry, fOperator, fAi] = facets;
    const [cStatus, cCountry, cYear] = charts;
    const degraded = pts.length > MAX_POINTS;
    const points: MapPoint[] = (degraded ? [] : pts).map((r) => {
      const p: MapPoint = { id: reqStr(r.id), slug: reqStr(r.slug), n: reqStr(r.name), o: str(r.op_name), lat: num(r.lat) ?? 0, lng: num(r.lng) ?? 0, s: asStatus(r.status), t: "unknown", mw: num(r.planned_mw), p: asPrecision(r.geo_precision), c: str(r.country_iso2), k: "project" };
      const ai = str(r.ai_evidence);
      if (r.is_ai === true || ai === "confirmed" || ai === "likely") p.ai = 1;
      const y = num(r.y);
      if (y != null) p.y = y;
      return p;
    });
    return {
      query: q,
      total: items.length ? int(items[0]!.total) : int(cov[0]?.n),
      items: items.map(projectSummary),
      facets: {
        status: fStatus.map((r) => ({ key: reqStr(r.key), count: int(r.n) })),
        country: fCountry.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name, reqStr(r.key)), count: int(r.n) })),
        operator: fOperator.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name), count: int(r.n) })),
        ai: fAi.map((r) => ({ key: reqStr(r.key, "unknown"), count: int(r.n) })),
      },
      charts: {
        byStatus: cStatus.map((r) => ({ key: reqStr(r.key), count: int(r.n), mw: round2(num(r.mw)) })),
        byCountry: cCountry.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name, reqStr(r.key)), count: int(r.n), mw: round2(num(r.mw)) })),
        byYear: cYear.map((r) => ({ year: int(r.year), count: int(r.n), mw: round2(num(r.mw)) })).filter((x) => x.year > 0),
      },
      map: { points, total: degraded ? int(cov[0]?.n) : points.length, degraded },
      mwCoverage: share(int(cov[0]?.with_mw), int(cov[0]?.n)),
    };
  }

  const conds = facilityExploreConds(sql, q);
  const where = andAll(sql, conds);
  const known = knownMwAgg(sql), pipe = pipelineMwAgg(sql), counted = countedAgg(sql), hasMw = hasMwAgg(sql);
  const chartMw = sql`sum(case when f.status = any(${OPERATIONAL_SET}) then ${known} else ${pipe} end)::float`;
  const [items, facets, charts, pts, cov] = await Promise.all([
    sql<Row[]>`select ${facilitySummaryCols(sql)}, count(*) over() as total from facilities f ${facilityJoins(sql)} where ${where} order by ${facilityOrder(sql, q.sort, q.order)} limit ${pg_.perPage} offset ${pg_.offset}`,
    Promise.all([
      sql<Row[]>`select f.status as key, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} group by 1 order by n desc`,
      sql<Row[]>`select f.country_iso2 as key, c.name, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} and f.country_iso2 is not null group by 1, 2 order by n desc limit 30`,
      sql<Row[]>`select o.slug as key, o.name, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} and o.id is not null group by 1, 2 order by n desc limit 15`,
      sql<Row[]>`select f.facility_type as key, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} group by 1 order by n desc`,
      sql<Row[]>`select f.ai_evidence as key, count(*)::int as n from facilities f ${facilityJoins(sql)} where ${where} group by 1 order by n desc`,
    ]),
    Promise.all([
      sql<Row[]>`select f.status as key, count(*) filter (where ${counted})::int as n, ${chartMw} as mw from ${facilityView(sql)} f ${facilityJoins(sql)} where ${where} group by 1 order by n desc`,
      sql<Row[]>`select f.country_iso2 as key, c.name, count(*) filter (where ${counted})::int as n, ${chartMw} as mw from ${facilityView(sql)} f ${facilityJoins(sql)} where ${where} and f.country_iso2 is not null group by 1, 2 order by n desc limit 15`,
      sql<Row[]>`select ${yearExpr(sql, sql`f.opened_on`)} as year, count(*) filter (where ${counted})::int as n, sum(${known})::float as mw from ${facilityView(sql)} f ${facilityJoins(sql)} where ${where} and f.opened_on ~ '^\\d{4}' group by 1 order by 1`,
    ]),
    sql<Row[]>`select f.id, f.slug, f.name, o.name as op_name, f.lat, f.lng, f.status, f.facility_type, ${mwExpr(sql)} as mw, f.geo_precision, f.is_ai, f.ai_evidence, f.is_hyperscale, f.country_iso2, f.record_scope, ${yearExpr(sql, sql`f.opened_on`)} as y from facilities f ${facilityJoins(sql)} where ${where} and f.lat is not null and f.lng is not null order by ${mwExpr(sql)} desc nulls last, f.id limit ${MAX_POINTS + 1}`,
    sql<Row[]>`select count(*) filter (where ${counted})::int as n, count(*) filter (where ${counted} and ${hasMw})::int as with_mw, count(*)::int as rows from ${facilityView(sql)} f ${facilityJoins(sql)} where ${where}`,
  ]);
  const [fStatus, fCountry, fOperator, fType, fAi] = facets;
  const [cStatus, cCountry, cYear] = charts;
  const degraded = pts.length > MAX_POINTS;
  const points: MapPoint[] = (degraded ? [] : pts).map((r) => {
    const p: MapPoint = { id: reqStr(r.id), slug: reqStr(r.slug), n: reqStr(r.name), o: str(r.op_name), lat: num(r.lat) ?? 0, lng: num(r.lng) ?? 0, s: asStatus(r.status), t: asType(r.facility_type), mw: num(r.mw), p: asPrecision(r.geo_precision), c: str(r.country_iso2) };
    const ai = str(r.ai_evidence);
    if (r.is_ai === true || ai === "confirmed" || ai === "likely") p.ai = 1;
    if (r.is_hyperscale === true) p.hs = 1;
    const y = num(r.y);
    if (y != null) p.y = y;
    const rs = str(r.record_scope);
    if (rs === "building" || rs === "campus") p.rs = rs;
    return p;
  });
  return {
    query: q,
    total: items.length ? int(items[0]!.total) : int(cov[0]?.rows),
    items: items.map(facilitySummary),
    facets: {
      status: fStatus.map((r) => ({ key: reqStr(r.key), count: int(r.n) })),
      country: fCountry.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name, reqStr(r.key)), count: int(r.n) })),
      operator: fOperator.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name), count: int(r.n) })),
      type: fType.map((r) => ({ key: reqStr(r.key), count: int(r.n) })),
      ai: fAi.map((r) => ({ key: reqStr(r.key, "unknown"), count: int(r.n) })),
    },
    charts: {
      byStatus: cStatus.map((r) => ({ key: reqStr(r.key), count: int(r.n), mw: round2(num(r.mw)) })),
      byCountry: cCountry.map((r) => ({ key: reqStr(r.key), name: reqStr(r.name, reqStr(r.key)), count: int(r.n), mw: round2(num(r.mw)) })),
      byYear: cYear.map((r) => ({ year: int(r.year), count: int(r.n), mw: round2(num(r.mw)) })).filter((x) => x.year > 0),
    },
    map: { points, total: degraded ? int(cov[0]?.rows) : points.length, degraded },
    mwCoverage: share(int(cov[0]?.with_mw), int(cov[0]?.n)),
  };
}