import type { CountryDetail, CountrySummary, EnergyContext } from "@dci/core";
import { pg, mwExpr, yearExpr, facilityView, knownMwAgg, countedAgg, projectLive, facilityJoins, facilitySummaryCols, projectJoins, projectSummaryCols, cloudRegionCols, AI_LEVELS, type Sql } from "../lib/sql.js";
import { int, num, reqStr, str, type Row } from "../lib/rows.js";
import { growthSeries, statusBreakdown, typeBreakdown, cloudRegionSummary, facilitySummary, projectSummary, ixpSummary, round2, share } from "../lib/dto.js";
import { findCountry } from "../lib/resolve.js";
import { listFacilities } from "./facilities.js";
import { operatorsForScope } from "./operators.js";
import { gridConstraintsFor, listMetros } from "./metros.js";
import { projectsWhere } from "./projects.js";
import { eventsForCountry } from "./events.js";
import { rankingPositions } from "./rankings.js";
import { coverageRow, dimAggCols, pipelineBreakdown, scopeFor } from "../lib/pipeline.js";
import { claimsFor } from "../lib/quality.js";

export const ENERGY_NOTE = "National grid averages (renewable share, generation) describe the country's electricity mix, not the electricity a facility contracts; utility/grid MW on facilities are supply figures, not IT load.";

function countrySummary(r: Row): CountrySummary {
  const n = int(r.facility_count);
  const withMw = int(r.with_mw);
  return {
    iso2: reqStr(r.iso2),
    iso3: str(r.iso3),
    slug: reqStr(r.slug),
    name: reqStr(r.name),
    region: str(r.region),
    subregion: str(r.subregion),
    population: num(r.population),
    gdpUsd: num(r.gdp_usd),
    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),
    hyperscaleCount: int(r.hyperscale),
    aiCount: int(r.ai),
    projectCount: int(r.project_count),
    projectPlannedMw: round2(num(r.project_planned_mw)),
    projectConstructionMw: round2(num(r.project_construction_mw)),
    ixpCount: int(r.ixp_count),
    mwCoverage: n ? share(withMw, n) : 0,
    lat: num(r.lat),
    lng: num(r.lng),
  };
}

const SELECT = (sql: Sql) => sql`
  select c.iso2, c.iso3, c.slug, c.name, c.region, c.subregion, c.population, c.gdp_usd, c.lat, c.lng, c.electricity_twh, c.renewable_share, c.stats_year, c.stats,
    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.hyperscale, 0) as hyperscale, coalesce(fa.ai, 0) as ai, coalesce(fa.with_mw, 0) as with_mw,
    coalesce(cr.n, 0) as cloud_region_count, coalesce(pj.n, 0) as project_count, pj.planned_mw as project_planned_mw, pj.construction_mw as project_construction_mw, coalesce(ix.n, 0) as ixp_count
  from countries c
  left join (select f.country_iso2, ${dimAggCols(sql)} from ${facilityView(sql)} f where f.country_iso2 is not null group by f.country_iso2) fa on fa.country_iso2 = c.iso2
  left join (select country_iso2, count(*)::int as n from cloud_regions where status <> 'retired' group by 1) cr on cr.country_iso2 = c.iso2
  left join (select country_iso2, count(*)::int as n from ixps group by 1) ix on ix.country_iso2 = c.iso2
  left join (select p.country_iso2, count(*)::int as n,
      sum(p.planned_mw) filter (where p.status in ('rumored','proposed','announced','permitting','approved','delayed'))::float as planned_mw,
      sum(p.planned_mw) filter (where p.status = 'under_construction')::float as construction_mw
    from projects p where ${projectLive(sql)} group by 1) pj on pj.country_iso2 = c.iso2`;

export async function listCountries(opts: { all?: boolean; region?: string } = {}): Promise<CountrySummary[]> {
  const sql = pg();
  const rows = await sql<Row[]>`${SELECT(sql)}
    where ${opts.all ? sql`true` : sql`(coalesce(fa.facilities, 0) > 0 or coalesce(cr.n, 0) > 0)`}
      and ${opts.region ? sql`(c.region ilike ${opts.region} or c.subregion ilike ${opts.region})` : sql`true`}
    order by coalesce(fa.facilities, 0) desc, coalesce(cr.n, 0) desc, c.name`;
  return rows.map(countrySummary);
}

async function energyContext(base: Row): Promise<EnergyContext> {
  const sql = pg();
  const stats = (base.stats as Record<string, unknown> | null) ?? {};
  let sourceName: string | null = null;
  let sourceUrl: string | null = null;
  const es = stats.energySource;
  if (es && typeof es === "object") { sourceName = str((es as Record<string, unknown>).name); sourceUrl = str((es as Record<string, unknown>).url); }
  else if (typeof es === "string") sourceName = es;
  if (!sourceName) {
    const rows = await sql<Row[]>`select s.name, p.url from provenance p left join sources s on s.id = p.source_id where p.entity_type = 'country' and p.entity_id = ${String(base.iso2)} and p.is_current and p.field in ('renewableShare', 'electricityTwh', 'renewable_share', 'electricity_twh') order by p.last_observed desc limit 1`;
    if (rows[0]) { sourceName = str(rows[0].name); sourceUrl = str(rows[0].url); }
  }
  return { renewableShare: num(base.renewable_share), electricityTwh: num(base.electricity_twh), statsYear: num(base.stats_year), gridCarbonIntensity: null, sourceName, sourceUrl, note: ENERGY_NOTE };
}

export async function getCountryDetail(slugOrIso2: string, fPage = 1): Promise<CountryDetail | null> {
  const sql = pg();
  const base = await findCountry(slugOrIso2);
  if (!base) return null;
  const iso2 = String(base.iso2);
  const scope = scopeFor(sql, { countryIso2: iso2 });
  const [sumRows, topOperators, metros, cloudRegionRows, recentProjects, recentEvents, growthRows, statusRows, typeRows, invRows, rankings, facilities, ixpRows, gridConstraints, energy, aiFacRows, aiPrjRows, pipeline, coverage, claims] = await Promise.all([
    sql<Row[]>`${SELECT(sql)} where c.iso2 = ${iso2}`,
    operatorsForScope({ countryIso2: iso2 }, 10),
    listMetros({ country: iso2 }),
    sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.country_iso2 = ${iso2} order by pr.name, r.code`,
    projectsWhere(sql`${projectLive(sql)} and p.country_iso2 = ${iso2}`, 10),
    eventsForCountry(iso2, 20),
    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.country_iso2 = ${iso2} and f.opened_on ~ '^\\d{4}' group by 1 order by 1`,
    sql<Row[]>`select status, count(*)::int as n from facilities where country_iso2 = ${iso2} and merged_into is null group by status`,
    sql<Row[]>`select facility_type, count(*)::int as n from facilities where country_iso2 = ${iso2} and merged_into is null group by facility_type`,
    sql<Row[]>`select sum(p.investment_usd)::float as inv from projects p where ${projectLive(sql)} and p.country_iso2 = ${iso2} and p.status not in ('cancelled') and (p.investment_scope is null or p.investment_scope in ('facility', 'campus', 'building'))`,
    rankingPositions("countries", [iso2, String(base.slug)]),
    listFacilities({ countryIso2: iso2, page: fPage, per_page: 50, sort: "mw", order: "desc" }),
    sql<Row[]>`select x.id, x.slug, x.name, x.name_long, x.city, x.country_iso2, x.website, x.network_count, (select count(*)::int from facility_ixps fx where fx.ixp_id = x.id) as facility_count, m.id as met_id, m.slug as met_slug, m.name as met_name
      from ixps x left join metros m on m.id = x.metro_id where x.country_iso2 = ${iso2} order by x.network_count desc nulls last, x.name limit 500`,
    gridConstraintsFor({ countryIso2: iso2 }),
    energyContext(base),
    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and f.country_iso2 = ${iso2} and f.ai_evidence = any(${AI_LEVELS}) order by ${mwExpr(sql)} desc nulls last, f.name limit 20`,
    sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where ${projectLive(sql)} and p.country_iso2 = ${iso2} and (p.ai_evidence = any(${AI_LEVELS}) or p.is_ai) order by p.planned_mw desc nulls last, p.last_update desc limit 20`,
    pipelineBreakdown(scope),
    coverageRow(iso2, String(base.name), String(base.slug), scope),
    claimsFor("country", iso2),
  ]);
  const summary = countrySummary(sumRows[0] ?? { ...base, facility_count: 0 });
  return {
    ...summary,
    ixps: ixpRows.map(ixpSummary),
    gridConstraints,
    energy,
    aiFacilities: aiFacRows.map(facilitySummary),
    aiProjects: aiPrjRows.map(projectSummary),
    pipeline,
    coverage,
    claims,
    topOperators,
    metros,
    cloudRegions: cloudRegionRows.map(cloudRegionSummary),
    recentProjects,
    recentEvents,
    growth: growthSeries(growthRows),
    statusBreakdown: statusBreakdown(statusRows),
    typeBreakdown: typeBreakdown(typeRows),
    announcedInvestmentUsd: num(invRows[0]?.inv),
    renewableShare: num(base.renewable_share),
    electricityTwh: num(base.electricity_twh),
    rankings,
    facilities: facilities.items,
  };
}

/** Countries matching a text for search (name / iso / slug). */
export async function searchCountries(text: string, limit = 3): Promise<Array<{ iso2: string; slug: string; name: string; facilityCount: number; exact: boolean }>> {
  const sql = pg();
  const up = text.toUpperCase();
  const rows = await sql<Row[]>`
    select c.iso2, c.slug, c.name, (select count(*)::int from facilities f where f.country_iso2 = c.iso2 and f.merged_into is null) as n,
      (lower(c.name) = lower(${text}) or c.iso2 = ${up} or c.iso3 = ${up} or c.slug = lower(${text})) as exact
    from countries c
    where c.name ilike ${"%" + text + "%"} or c.iso2 = ${up} or c.iso3 = ${up} or c.slug = lower(${text})
    order by exact desc, n desc, c.name limit ${limit}`;
  return rows.map((r) => ({ iso2: reqStr(r.iso2), slug: reqStr(r.slug), name: reqStr(r.name), facilityCount: int(r.n), exact: r.exact === true }));
}