/**
 * Raw SQL layer on postgres.js (the same client drizzle uses, so timestamps come back as strings).
 * Repositories compose fragments with the tagged template; every value is a bound parameter.
 */
import type postgres from "postgres";
import { getDb, getSql } from "@dci/db";
import type { FacilityStatus } from "@dci/core";

export type Sql = ReturnType<typeof getSql>;
export type Fragment = postgres.Fragment;

/** postgres.js client (drizzle initialised first so its transparent date parsers are installed). */
export function pg(): Sql {
  getDb();
  return getSql();
}

/** Facilities counted as operational capacity. */
export const OPERATIONAL_SET: FacilityStatus[] = ["operational", "expansion", "partially_operational"];
export const CONSTRUCTION_SET: FacilityStatus[] = ["under_construction"];
/** Pipeline statuses before construction (announced / permitting …). */
export const PLANNED_SET: FacilityStatus[] = ["rumored", "proposed", "announced", "permitting", "approved", "delayed"];
export const PIPELINE_SET: FacilityStatus[] = [...PLANNED_SET, ...CONSTRUCTION_SET, "partially_operational"];

export const METHODOLOGY_MW = "mw = COALESCE(it_capacity_mw, total_power_mw, planned_power_mw). knownMw sums operational/expansion/partially_operational facilities; constructionMw sums under_construction; plannedMw sums rumored/proposed/announced/permitting/approved/delayed (planned_power_mw first). Estimates are flagged per facility (mw_is_estimate).";

/** Best known MW for a facility row aliased `f`. */
export function mwExpr(sql: Sql): Fragment {
  return sql`coalesce(f.it_capacity_mw, f.total_power_mw, f.planned_power_mw)`;
}

/** MW expression used for planned capacity (planned figure first). */
export function plannedMwExpr(sql: Sql): Fragment {
  return sql`coalesce(f.planned_power_mw, f.it_capacity_mw, f.total_power_mw)`;
}

/** AND-join a list of conditions (empty → true). */
export function andAll(sql: Sql, conds: Fragment[]): Fragment {
  return conds.reduce<Fragment>((acc, c) => sql`${acc} and ${c}`, sql`true`);
}

export function orAll(sql: Sql, conds: Fragment[]): Fragment {
  if (!conds.length) return sql`false`;
  return conds.slice(1).reduce<Fragment>((acc, c) => sql`${acc} or ${c}`, conds[0]!);
}

/** Live project rows: false positives hidden by review and merged duplicates are never listed, counted or summed. */
export function projectLive(sql: Sql, alias = "p"): Fragment {
  const a = sql(alias);
  return sql`(${a}.hidden = false and ${a}.merged_into is null)`;
}

/** AI evidence levels that qualify a facility / project as AI infrastructure (keyword mentions alone never do). */
export const AI_LEVELS = ["confirmed", "likely"];

/**
 * Containment-aware facility view (mirrors apps/worker/src/rankings.ts FACILITY_VIEW): a campus row whose buildings
 * publish their own MW contributes nothing, a building without a figure under a campus with one is "covered", and a
 * campus with building rows is not counted as a facility (its buildings are).
 */
export function facilityView(sql: Sql): Fragment {
  return sql`(
    select f.*,
      exists (select 1 from facilities ch where ch.parent_facility_id = f.id and ch.merged_into is null) as has_children,
      exists (select 1 from facilities ch where ch.parent_facility_id = f.id and ch.merged_into is null and coalesce(ch.it_capacity_mw, ch.total_power_mw, ch.planned_power_mw) is not null) as children_have_mw,
      (f.parent_facility_id is not null and exists (select 1 from facilities pp where pp.id = f.parent_facility_id and pp.merged_into is null and coalesce(pp.it_capacity_mw, pp.total_power_mw, pp.planned_power_mw) is not null)
        and coalesce(f.it_capacity_mw, f.total_power_mw, f.planned_power_mw) is null) as covered_by_parent
    from facilities f where f.merged_into is null
  )`;
}
/** Known (operational) MW of a facilityView row `f`, null when its buildings carry the figure. */
export function knownMwAgg(sql: Sql): Fragment {
  return sql`(case when f.children_have_mw then null else coalesce(f.it_capacity_mw, f.total_power_mw) end)`;
}
/** Pipeline MW of a facilityView row `f` (planned figure first). */
export function pipelineMwAgg(sql: Sql): Fragment {
  return sql`(case when f.children_have_mw then null else coalesce(f.planned_power_mw, f.it_capacity_mw, f.total_power_mw) end)`;
}
/** Rows counted as one facility (campus rows with building rows are not). */
export function countedAgg(sql: Sql): Fragment {
  return sql`(not f.has_children)`;
}
/** A facilityView row has a known MW figure (own figure or covered by its campus). */
export function hasMwAgg(sql: Sql): Fragment {
  return sql`(coalesce(f.it_capacity_mw, f.total_power_mw, f.planned_power_mw) is not null or f.covered_by_parent)`;
}

export const METHODOLOGY_CONTAINMENT = "Aggregates are containment-aware: a campus and its buildings are never both counted or summed (buildings with their own figures win; otherwise the campus figure stands for them). Published figures only — no extrapolation. Coverage = share of counted facilities with any MW figure.";

/** Columns for a FacilitySummary from `facilities f` joined with operators o, metros m, countries c. */
export function facilitySummaryCols(sql: Sql): Fragment {
  return sql`
    f.id, f.slug, f.name, f.city, f.region_name, f.country_iso2, c.name as country_name,
    f.lat, f.lng, f.geo_precision, f.status, f.facility_type, f.it_capacity_mw, f.total_power_mw, f.planned_power_mw,
    f.is_ai, f.is_hyperscale, f.confidence, f.completeness, f.opened_on, f.last_verified, f.carriers_count, f.ixp_count, f.networks_count,
    f.mw_is_estimate, f.record_scope, f.ai_evidence, f.capacity_scope, f.capacity_semantics, f.utility_capacity_mw, f.grid_connection_mw, f.ultimate_campus_mw, f.source_count,
    f.parent_facility_id as parent_id, (select x.slug from facilities x where x.id = f.parent_facility_id) as parent_slug, (select x.name from facilities x where x.id = f.parent_facility_id) as parent_name,
    o.id as op_id, o.slug as op_slug, o.name as op_name,
    m.id as met_id, m.slug as met_slug, m.name as met_name`;
}

export function facilityJoins(sql: Sql): Fragment {
  return sql`
    left join operators o on o.id = f.operator_id
    left join metros m on m.id = f.metro_id
    left join countries c on c.iso2 = f.country_iso2`;
}

/** Columns for a ProjectSummary from `projects p` joined with operators o, facilities pf, metros m. */
export function projectSummaryCols(sql: Sql): Fragment {
  return sql`
    p.id, p.slug, p.name, p.city, p.region_name, p.country_iso2, p.lat, p.lng, p.geo_precision, p.status, p.announced_on, p.expected_opening,
    p.planned_mw, p.investment_usd, p.acreage, p.phase_count, p.confidence, p.last_update, p.is_ai, p.description, p.source_url, p.updated_at,
    p.project_class, p.evidence_level, p.ai_evidence, p.capacity_scope, p.capacity_semantics, p.investment_scope, p.investment_semantics, p.investment_currency, p.investment_original,
    p.construction_started_on, p.approved_on, p.permit_filed_on, p.opened_on, p.hidden, p.merged_into, p.campus_id,
    p.developer_id as dev_id, (select x.slug from operators x where x.id = p.developer_id) as dev_slug, (select x.name from operators x where x.id = p.developer_id) as dev_name,
    p.tenant_id as ten_id, (select x.slug from operators x where x.id = p.tenant_id) as ten_slug, (select x.name from operators x where x.id = p.tenant_id) as ten_name,
    o.id as op_id, o.slug as op_slug, o.name as op_name,
    pf.id as fac_id, pf.slug as fac_slug, pf.name as fac_name,
    m.id as met_id, m.slug as met_slug, m.name as met_name`;
}

export function projectJoins(sql: Sql): Fragment {
  return sql`
    left join operators o on o.id = p.operator_id
    left join facilities pf on pf.id = p.facility_id
    left join metros m on m.id = p.metro_id`;
}

/** Columns for a CloudRegionSummary from `cloud_regions r` joined with operators pr. */
export function cloudRegionCols(sql: Sql): Fragment {
  return sql`
    r.id, r.slug, r.code, r.name, r.city, r.country_iso2, r.lat, r.lng, r.geo_precision, r.availability_zones, r.launched_on, r.status, r.is_sovereign, r.source_url, r.metro_id, r.region_name,
    pr.id as pr_id, pr.slug as pr_slug, pr.name as pr_name`;
}

/** Columns for an EventDTO from `events e` joined with sources s and operators o. */
export function eventCols(sql: Sql): Fragment {
  return sql`
    e.id, e.entity_type, e.entity_id, e.event_type, e.detected_at, e.effective_date, e.old_value, e.new_value, e.source_id, e.document_id, e.url, e.title, e.summary,
    e.significance, e.confidence, e.review_status, e.country_iso2, e.metro_id, e.project_id, e.cluster_id, e.evidence_count, e.is_ai, e.run_id,
    s.name as source_name, coalesce(e.source_kind, s.kind) as source_kind,
    o.id as op_id, o.slug as op_slug, o.name as op_name,
    em.id as met_id, em.slug as met_slug, em.name as met_name,
    ep.id as prj_id, ep.slug as prj_slug, ep.name as prj_name`;
}

export function eventJoins(sql: Sql): Fragment {
  return sql`
    left join sources s on s.id = e.source_id
    left join operators o on o.id = e.operator_id
    left join metros em on em.id = e.metro_id
    left join projects ep on ep.id = e.project_id`;
}

/** Event types describing corporate life rather than physical infrastructure. */
export const CORPORATE_EVENT_TYPES = ["acquisition", "investment_announced", "partnership", "executive_change", "customer_agreement", "power_agreement"];
/** Event types about electricity supply and grid constraints. */
export const POWER_EVENT_TYPES = ["power_agreement", "grid_connection", "grid_constraint", "utility_event"];
export const GRID_EVENT_TYPES = ["grid_constraint", "utility_event", "power_agreement"];

/** Columns for a ClaimDTO from `claims k` joined with sources s. */
export function claimCols(sql: Sql): Fragment {
  return sql`
    k.id, k.subject_type, k.subject_id, k.predicate, k.value, k.value_text, k.unit, k.scope, k.scope_reason, k.source_id, k.connector_id, k.document_id, k.url, k.published_at, k.retrieved_at,
    k.confidence, k.is_estimate, k.authority_tier, k.evidence_text, k.parser_name, k.parser_version, k.run_id, k.status, k.rejection_reason, k.first_observed, k.last_observed,
    s.name as source_name, s.kind as source_kind`;
}

/** Columns for a GridConstraintDTO from `grid_constraints g` joined with sources s and metros gm. */
export function gridConstraintCols(sql: Sql): Fragment {
  return sql`
    g.id, g.kind, g.title, g.summary, g.effective_date, g.url, g.country_iso2, g.confidence, g.event_id, g.source_id,
    s.name as source_name, s.kind as source_kind,
    gm.id as met_id, gm.slug as met_slug, gm.name as met_name`;
}
export function gridConstraintJoins(sql: Sql): Fragment {
  return sql`left join sources s on s.id = g.source_id left join metros gm on gm.id = g.metro_id`;
}

export interface Page { page: number; perPage: number; offset: number }
export function page(pageNo: number | undefined, perPage: number | undefined, maxPer = 100, defaultPer = 24): Page {
  const p = Math.max(1, Math.trunc(pageNo ?? 1) || 1);
  const per = Math.max(1, Math.min(maxPer, Math.trunc(perPage ?? defaultPer) || defaultPer));
  return { page: p, perPage: per, offset: (p - 1) * per };
}

/** Bounding box (degrees) around a point for a radius in km — for pre-filtering before haversine. */
export function bboxAround(lat: number, lng: number, km: number): { minLat: number; maxLat: number; minLng: number; maxLng: number } {
  const dLat = km / 111.32;
  const dLng = km / (111.32 * Math.max(0.05, Math.cos((lat * Math.PI) / 180)));
  return { minLat: lat - dLat, maxLat: lat + dLat, minLng: lng - dLng, maxLng: lng + dLng };
}

/** Haversine distance in km as SQL (facility alias `f`). */
export function haversineExpr(sql: Sql, lat: number, lng: number): Fragment {
  return haversineFor(sql, lat, lng, sql`f.lat`, sql`f.lng`);
}

/** Haversine distance in km as SQL for arbitrary lat / lng columns. */
export function haversineFor(sql: Sql, lat: number, lng: number, latCol: Fragment, lngCol: Fragment): Fragment {
  return sql`(2 * 6371.0088 * asin(sqrt(least(1.0, power(sin(radians(${latCol} - ${lat}) / 2), 2) + cos(radians(${lat})) * cos(radians(${latCol})) * power(sin(radians(${lngCol} - ${lng}) / 2), 2)))))`;
}

/** `lat between … and lng between …` for a bbox around a point (pre-filter before haversine). */
export function withinBbox(sql: Sql, lat: number, lng: number, km: number, latCol: Fragment, lngCol: Fragment): Fragment {
  const b = bboxAround(lat, lng, km);
  return sql`(${latCol} between ${b.minLat} and ${b.maxLat} and ${lngCol} between ${b.minLng} and ${b.maxLng})`;
}

/** `left(col, 4)::int` when the partial date starts with a year, else null. */
export function yearExpr(sql: Sql, col: Fragment): Fragment {
  return sql`(case when ${col} ~ '^\\d{4}' then left(${col}, 4)::int else null end)`;
}

export function likePattern(q: string): string {
  return `%${q.replace(/[\\%_]/g, (m) => `\\${m}`)}%`;
}