/**
 * Admin data-quality reads: QualityOverview (largest values, review queue, counters) and the quality_flags list.
 * Every "largest" query is a plain ORDER BY on published figures — the point is to surface outliers for review.
 */
import type { QualityFlagDTO, QualityOverview } from "@dci/core";
import { pg, facilityView, pipelineMwAgg, projectLive, andAll, page, PLANNED_SET, CONSTRUCTION_SET, type Fragment } from "../../lib/sql.js";
import { int, iso, num, reqStr, str, type Row } from "../../lib/rows.js";
import { qualityFlagDto } from "../../lib/dto.js";
import { entityKey, resolveEntityRefs } from "../../lib/resolve.js";

const CODE_LABELS: Record<string, string> = {
  mw_single_site_gt_1000: "Single site > 1 000 MW",
  mw_building_gt_500: "One building > 500 MW",
  mw_market_statistic: "MW figure is a market statistic",
  mw_change_5x: "MW figure changed > 5×",
  mw_money_collision: "MW / money figure collision",
  mw_semantics_default: "MW semantics defaulted",
  mw_utility_not_it: "Utility / grid MW (not IT load)",
  mw_project_gt_2000: "Project > 2 000 MW",
  mw_density_implausible: "Implausible power density",
  mw_invalid: "Invalid MW figure",
  inv_single_site_gt_50b: "Single site investment > $50B",
  inv_gt_500b: "Investment > $500B (industry statistic)",
  inv_change_5x: "Investment changed > 5×",
  inv_money_mw_collision: "Investment / MW figure collision",
  inv_invalid: "Invalid investment figure",
  project_false_positive_candidate: "Project false-positive candidate",
  project_title_like_name: "Project named after a headline",
  project_no_location: "Project without a location",
  project_unknown_scope: "Project figure with unknown scope",
  facility_no_country: "Facility without a country",
  operator_bad_slug: "Operator with a broken slug",
  claim_rejected_backing_value: "Rejected claim was backing the displayed value",
};

export function flagLabel(code: string): string {
  if (CODE_LABELS[code]) return CODE_LABELS[code]!;
  if (code.startsWith("scope_")) return `Figure scope: ${code.slice(6).replace(/_/g, " ")}`;
  if (code.startsWith("inv_scope_")) return `Investment scope: ${code.slice(10).replace(/_/g, " ")}`;
  if (code.startsWith("inv_")) return `Investment: ${code.slice(4).replace(/_/g, " ")}`;
  if (code.startsWith("duplicate")) return "Possible duplicate";
  return code.replace(/_/g, " ").replace(/^\w/, (c) => c.toUpperCase());
}

function named(r: Row): { id: string; slug: string; name: string } {
  return { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) };
}

/** Attach {slug,name} refs to quality flag rows (one lookup per entity type). */
export async function flagsWithRefs(rows: Row[]): Promise<QualityFlagDTO[]> {
  const refs = await resolveEntityRefs(rows.map((r) => ({ type: reqStr(r.entity_type), id: str(r.entity_id) })));
  return rows.map((r) => qualityFlagDto(r, refs.get(entityKey(r.entity_type, r.entity_id)) ?? null));
}

export async function qualityOverview(): Promise<QualityOverview> {
  const sql = pg();
  const live = projectLive(sql);
  const [counts, sev, codes, largeOps, largeCon, largePrj, largeInv, opPipe, metPipe, queue, alerts] = await Promise.all([
    sql<Row[]>`select
        (select count(*) from quality_flags where status = 'open')::int as open_flags,
        (select count(*) from entity_matches where status = 'pending')::int + (select count(*) from quality_flags where status = 'open' and code like 'duplicate%')::int as possible_duplicates,
        (select count(*) from quality_flags where status = 'open' and code like 'mw\\_%')::int as suspicious_mw,
        (select count(*) from quality_flags where status = 'open' and code like 'inv\\_%')::int as suspicious_inv,
        (select count(*) from quality_flags where status = 'open' and code = 'project_false_positive_candidate')::int as project_fp,
        (select count(*) from claims where status = 'unscoped')::int as unscoped_claims,
        (select count(*) from claims where status = 'review')::int as claims_review,
        (select count(*) from projects p where ${live} and p.planned_mw >= 500 and (coalesce(p.evidence_level, 'none') <> 'strong' or p.confidence in ('moderate', 'estimated', 'unverified')))::int as unverified_large,
        (select count(*) from facilities where merged_into is null and country_iso2 is null)::int as fac_no_country,
        (select count(*) from projects p where ${live} and p.facility_id is null)::int as prj_no_facility,
        (select count(*) from operators o where not exists (select 1 from facilities f where (f.operator_id = o.id or f.owner_id = o.id) and f.merged_into is null)
            and not exists (select 1 from projects p where p.operator_id = o.id and ${live})
            and not exists (select 1 from cloud_regions r where r.provider_id = o.id)
            and not exists (select 1 from facility_tenants t where t.operator_id = o.id))::int as orphan_operators`,
    sql<Row[]>`select severity, count(*)::int as n from quality_flags where status = 'open' group by 1`,
    sql<Row[]>`select code, count(*)::int as n from quality_flags where status = 'open' group by 1 order by n desc`,
    sql<Row[]>`select id, slug, name, coalesce(it_capacity_mw, total_power_mw) as mw, capacity_scope as scope, capacity_semantics as semantics from facilities where merged_into is null and status in ('operational', 'partially_operational', 'expansion') and coalesce(it_capacity_mw, total_power_mw) is not null order by 4 desc limit 15`,
    sql<Row[]>`select id, slug, name, coalesce(planned_power_mw, it_capacity_mw, total_power_mw) as mw from facilities where merged_into is null and status = 'under_construction' and coalesce(planned_power_mw, it_capacity_mw, total_power_mw) is not null order by 4 desc limit 15`,
    sql<Row[]>`select p.id, p.slug, p.name, p.planned_mw as mw, p.capacity_scope as scope from projects p where ${live} and p.planned_mw is not null order by p.planned_mw desc limit 15`,
    sql<Row[]>`select p.id, p.slug, p.name, p.investment_usd as inv, p.investment_scope as scope from projects p where ${live} and p.investment_usd is not null order by p.investment_usd desc limit 15`,
    sql<Row[]>`select o.id, o.slug, o.name,
        coalesce((select sum(${pipelineMwAgg(sql)}) from ${facilityView(sql)} f where f.operator_id = o.id and f.status = any(${[...PLANNED_SET, ...CONSTRUCTION_SET]})), 0)
        + coalesce((select sum(p.planned_mw) from projects p where p.operator_id = o.id and ${live} and p.status = any(${[...PLANNED_SET, ...CONSTRUCTION_SET]})), 0) as mw
      from operators o order by mw desc nulls last limit 15`,
    sql<Row[]>`select m.id, m.slug, m.name,
        coalesce((select sum(${pipelineMwAgg(sql)}) from ${facilityView(sql)} f where f.metro_id = m.id and f.status = any(${[...PLANNED_SET, ...CONSTRUCTION_SET]})), 0)
        + coalesce((select sum(p.planned_mw) from projects p where p.metro_id = m.id and ${live} and p.status = any(${[...PLANNED_SET, ...CONSTRUCTION_SET]})), 0) as mw
      from metros m order by mw desc nulls last limit 15`,
    sql<Row[]>`select * from quality_flags where status = 'open' order by priority desc, created_at desc limit 50`,
    sql<Row[]>`select id, level, component, message, created_at from system_alerts where resolved_at is null order by created_at desc limit 20`,
  ]);
  const c = counts[0] ?? {};
  const bySeverity: Record<string, number> = {};
  for (const r of sev) bySeverity[reqStr(r.severity)] = int(r.n);
  return {
    openFlags: int(c.open_flags),
    bySeverity,
    byCode: codes.map((r) => ({ code: reqStr(r.code), count: int(r.n), label: flagLabel(reqStr(r.code)) })),
    possibleDuplicates: int(c.possible_duplicates),
    suspiciousMw: int(c.suspicious_mw),
    suspiciousInvestment: int(c.suspicious_inv),
    projectFalsePositiveCandidates: int(c.project_fp),
    unscopedClaims: int(c.unscoped_claims),
    claimsInReview: int(c.claims_review),
    unverifiedLargeProjects: int(c.unverified_large),
    facilitiesMissingCountry: int(c.fac_no_country),
    projectsWithoutFacility: int(c.prj_no_facility),
    orphanOperators: int(c.orphan_operators),
    largest: {
      operationalFacilityMw: largeOps.map((r) => ({ ...named(r), mw: num(r.mw) ?? 0, scope: str(r.scope), semantics: str(r.semantics) })),
      constructionFacilityMw: largeCon.map((r) => ({ ...named(r), mw: num(r.mw) ?? 0 })),
      projectMw: largePrj.map((r) => ({ ...named(r), mw: num(r.mw) ?? 0, scope: str(r.scope) })),
      investment: largeInv.map((r) => ({ ...named(r), investmentUsd: num(r.inv) ?? 0, scope: str(r.scope) })),
      operatorPipelineMw: opPipe.filter((r) => (num(r.mw) ?? 0) > 0).map((r) => ({ ...named(r), mw: Math.round((num(r.mw) ?? 0) * 100) / 100 })),
      metroPipelineMw: metPipe.filter((r) => (num(r.mw) ?? 0) > 0).map((r) => ({ ...named(r), mw: Math.round((num(r.mw) ?? 0) * 100) / 100 })),
    },
    reviewQueue: await flagsWithRefs(queue),
    alerts: alerts.map((a) => ({ id: reqStr(a.id), level: reqStr(a.level), component: reqStr(a.component), message: reqStr(a.message), createdAt: iso(a.created_at) ?? "" })),
  };
}

export interface FlagFilters { status?: string; code?: string; entityType?: string; minPriority?: number; page?: number; perPage?: number }

export async function listFlags(f: FlagFilters): Promise<{ items: QualityFlagDTO[]; total: number; page: number; perPage: number }> {
  const sql = pg();
  const pg_ = page(f.page, f.perPage, 200, 50);
  const c: Fragment[] = [];
  if (f.status && f.status !== "all") c.push(sql`q.status = ${f.status}`);
  if (f.code) c.push(sql`q.code = ${f.code}`);
  if (f.entityType) c.push(sql`q.entity_type = ${f.entityType}`);
  if (f.minPriority != null) c.push(sql`q.priority >= ${f.minPriority}`);
  const rows = await sql<Row[]>`select q.*, count(*) over() as total from quality_flags q where ${andAll(sql, c)} order by q.priority desc, q.created_at desc limit ${pg_.perPage} offset ${pg_.offset}`;
  return { items: await flagsWithRefs(rows), total: rows.length ? int(rows[0]!.total) : 0, page: pg_.page, perPage: pg_.perPage };
}

export async function setFlagStatus(id: string, status: "resolved" | "dismissed", resolution: string | null): Promise<QualityFlagDTO | null> {
  const sql = pg();
  const rows = await sql<Row[]>`update quality_flags set status = ${status}, resolution = ${resolution}, resolved_by = 'admin', resolved_at = now(), updated_at = now() where id = ${id} returning *`;
  if (!rows[0]) return null;
  return (await flagsWithRefs(rows))[0] ?? null;
}