/**
 * Coverage report: how much of the index is actually known, per country / metro / operator / field / source kind.
 * Containment-aware (campus rows with buildings are not counted). Shares are 0..1.
 */
import type { CoverageReport, CoverageRow, SourceCoverage } from "@dci/core";
import { authorityTier } from "@dci/core";
import { pg, facilityView, countedAgg, hasMwAgg, projectLive, type Fragment, type Sql } from "../lib/sql.js";
import { int, iso, reqStr, str, type Row } from "../lib/rows.js";
import { asSourceKind, share } from "../lib/dto.js";
import { coverageRow, scopeFor } from "../lib/pipeline.js";

const PRIMARY_KINDS = ["operator", "government", "filing", "utility", "cloud_provider", "registry"];

/** Grouped version of lib/pipeline coverageRow: one row per dimension value. */
function coverageCols(sql: Sql): Fragment {
  const counted = countedAgg(sql), hasMw = hasMwAgg(sql);
  return sql`
    count(*) filter (where ${counted})::int as n,
    count(*) filter (where ${counted} and ${hasMw})::int as with_mw,
    count(*) filter (where ${counted} and f.operator_id is not null)::int as with_op,
    count(*) filter (where ${counted} and f.lat is not null and f.geo_precision in ('exact', 'parcel', 'street'))::int as precise,
    count(*) filter (where ${counted} and f.lat is not null)::int as any_loc,
    count(*) filter (where ${counted} and f.status <> 'unknown')::int as with_status,
    count(*) filter (where ${counted} and f.opened_on ~ '^\\d{4}')::int as with_open,
    count(*) filter (where ${counted} and f.source_count >= 2)::int as multi,
    count(*) filter (where ${counted} and (coalesce(f.carriers_count, 0) > 0 or coalesce(f.ixp_count, 0) > 0 or exists (select 1 from facility_tenants t where t.facility_id = f.id) or exists (select 1 from facility_ixps x where x.facility_id = f.id)))::int as conn,
    count(*) filter (where ${counted} and exists (select 1 from provenance p join sources s on s.id = p.source_id where p.entity_type = 'facility' and p.entity_id = f.id and p.is_current and s.kind = any(${PRIMARY_KINDS})))::int as primary_src`;
}

function toRow(r: Row, projects: { n: number; loc: number }): CoverageRow {
  const n = int(r.n);
  return {
    key: reqStr(r.key), name: reqStr(r.name), slug: reqStr(r.slug),
    facilities: n,
    capacityCoverage: share(int(r.with_mw), n),
    operatorCoverage: share(int(r.with_op), n),
    preciseLocationCoverage: share(int(r.precise), n),
    anyLocationCoverage: share(int(r.any_loc), n),
    statusCoverage: share(int(r.with_status), n),
    openingDateCoverage: share(int(r.with_open), n),
    multiSourceCoverage: share(int(r.multi), n),
    projects: projects.n,
    projectsWithLocation: projects.loc,
    connectivityCoverage: share(int(r.conn), n),
    primarySourceShare: share(int(r.primary_src), n),
  };
}

async function projectCounts(sql: Sql, col: "country_iso2" | "metro_id" | "operator_id"): Promise<Map<string, { n: number; loc: number }>> {
  const rows = await sql<Row[]>`select p.${sql(col)} as key, count(*)::int as n, count(*) filter (where p.lat is not null)::int as loc from projects p where ${projectLive(sql)} and p.${sql(col)} is not null group by 1`;
  return new Map(rows.map((r) => [reqStr(r.key), { n: int(r.n), loc: int(r.loc) }]));
}

export const COVERAGE_METHODOLOGY = "Shares of counted facilities (campus rows with building rows excluded; merged duplicates excluded) with each field known. capacityCoverage counts a building covered by its campus figure as covered. Precise location = exact / parcel / street. Primary sources = operator, government, filing, utility, cloud provider, registry. Nothing is extrapolated: a low share means the index does not know, not that the value is zero.";

export async function coverageReport(): Promise<CoverageReport> {
  const sql = pg();
  const [global, countries, metros, operators, pc, pm, po, fields, bySourceKind] = await Promise.all([
    coverageRow("global", "Global", "global", scopeFor(sql, {})),
    sql<Row[]>`select c.iso2 as key, c.name, c.slug, ${coverageCols(sql)} from ${facilityView(sql)} f join countries c on c.iso2 = f.country_iso2 group by c.iso2, c.name, c.slug having count(*) filter (where ${countedAgg(sql)}) >= 1 order by n desc, c.name`,
    sql<Row[]>`select m.id as key, m.name, m.slug, ${coverageCols(sql)} from ${facilityView(sql)} f join metros m on m.id = f.metro_id group by m.id, m.name, m.slug having count(*) filter (where ${countedAgg(sql)}) >= 1 order by n desc, m.name`,
    sql<Row[]>`select o.id as key, o.name, o.slug, ${coverageCols(sql)} from ${facilityView(sql)} f join operators o on o.id = f.operator_id group by o.id, o.name, o.slug having count(*) filter (where ${countedAgg(sql)}) >= 5 order by n desc, o.name`,
    projectCounts(sql, "country_iso2"),
    projectCounts(sql, "metro_id"),
    projectCounts(sql, "operator_id"),
    sql<Row[]>`select count(*)::int as n,
        count(*) filter (where f.operator_id is not null)::int as operator,
        count(*) filter (where f.lat is not null)::int as coordinates,
        count(*) filter (where f.lat is not null and f.geo_precision in ('exact', 'parcel', 'street'))::int as precise_coordinates,
        count(*) filter (where f.status <> 'unknown')::int as status,
        count(*) filter (where f.facility_type <> 'unknown')::int as facility_type,
        count(*) filter (where f.it_capacity_mw is not null)::int as it_capacity_mw,
        count(*) filter (where f.total_power_mw is not null)::int as total_power_mw,
        count(*) filter (where f.planned_power_mw is not null)::int as planned_power_mw,
        count(*) filter (where f.opened_on ~ '^\\d{4}')::int as opened_on,
        count(*) filter (where f.address is not null)::int as address,
        count(*) filter (where coalesce(f.carriers_count, 0) > 0 or coalesce(f.ixp_count, 0) > 0 or exists (select 1 from facility_tenants t where t.facility_id = f.id) or exists (select 1 from facility_ixps x where x.facility_id = f.id))::int as connectivity,
        count(*) filter (where f.website is not null)::int as website,
        count(*) filter (where f.description is not null)::int as description,
        count(*) filter (where f.tier is not null)::int as tier
      from ${facilityView(sql)} f where ${countedAgg(sql)}`,
    sql<Row[]>`select s.kind, count(distinct p.entity_id) filter (where p.entity_type = 'facility')::int as facilities, count(*)::int as fields from provenance p join sources s on s.id = p.source_id where p.is_current group by s.kind order by facilities desc`,
  ]);
  const fr = fields[0] ?? {};
  const total = int(fr.n);
  const FIELDS: Array<[string, string]> = [["operator", "Operator"], ["coordinates", "Coordinates (any precision)"], ["precise_coordinates", "Precise coordinates (exact / parcel / street)"], ["status", "Lifecycle status"], ["facility_type", "Facility type"], ["it_capacity_mw", "IT capacity (MW)"], ["total_power_mw", "Total power (MW)"], ["planned_power_mw", "Planned power (MW)"], ["opened_on", "Opening date"], ["address", "Street address"], ["connectivity", "Carriers / IXPs"], ["website", "Website"], ["description", "Description"], ["tier", "Tier / certification"]];
  return {
    global,
    countries: countries.map((r) => toRow(r, pc.get(reqStr(r.key)) ?? { n: 0, loc: 0 })),
    metros: metros.map((r) => toRow(r, pm.get(reqStr(r.key)) ?? { n: 0, loc: 0 })),
    operators: operators.map((r) => toRow(r, po.get(reqStr(r.key)) ?? { n: 0, loc: 0 })),
    fields: FIELDS.map(([field, label]) => ({ field, label, coverage: share(int(fr[field]), total), count: int(fr[field]) })),
    bySourceKind: bySourceKind.map((r) => ({ kind: asSourceKind(r.kind), facilities: int(r.facilities), fields: int(r.fields) })),
    generatedAt: new Date().toISOString(),
  };
}

export async function sourceCoverage(): Promise<SourceCoverage[]> {
  const sql = pg();
  const rows = await sql<Row[]>`
    with cur as (select source_id, entity_type, entity_id from provenance where is_current),
    per_entity as (select entity_type, entity_id, count(distinct source_id) as sources from cur group by 1, 2)
    select s.id, s.name, s.kind, s.license, s.redistribution, s.connector_id,
      (select count(distinct (c.entity_type, c.entity_id)) from cur c where c.source_id = s.id)::int as records,
      (select count(*) from cur c where c.source_id = s.id)::int as fields,
      (select count(distinct (c.entity_type, c.entity_id)) from cur c join per_entity pe on pe.entity_type = c.entity_type and pe.entity_id = c.entity_id where c.source_id = s.id and pe.sources = 1)::int as unique_records,
      (select max(r.finished_at) from connector_runs r where r.connector_id = s.connector_id and r.status in ('ok', 'partial')) as last_ok,
      (select count(*) from connector_runs r where r.connector_id = s.connector_id and r.started_at >= now() - interval '30 days' and r.status <> 'running')::int as runs30,
      (select count(*) from connector_runs r where r.connector_id = s.connector_id and r.started_at >= now() - interval '30 days' and r.status in ('failed', 'aborted'))::int as failed30
    from sources s order by records desc, s.name`;
  return rows.map((r) => {
    const lastOk = iso(r.last_ok);
    const runs = int(r.runs30);
    return {
      id: reqStr(r.id), name: reqStr(r.name), kind: asSourceKind(r.kind),
      recordsContributed: int(r.records), fieldsContributed: int(r.fields), uniqueRecords: int(r.unique_records),
      lastSuccessfulCrawl: lastOk,
      freshnessDays: lastOk ? Math.max(0, Math.round((Date.now() - new Date(lastOk).getTime()) / 86_400_000)) : null,
      authority: authorityTier({ field: "identity", sourceKind: str(r.kind) }),
      failureRate: runs ? Math.round((int(r.failed30) / runs) * 1000) / 1000 : null,
      license: str(r.license), redistribution: str(r.redistribution),
    };
  });
}