import type { Ranking, RankingRow } from "@dci/core";
import { pg } from "../lib/sql.js";
import { int, json, num, reqIso, reqStr, str, type Row } from "../lib/rows.js";
import { hrefFor } from "../lib/resolve.js";

export interface RankingListItem { key: string; label: string; scope: Ranking["scope"]; unit: string; methodology: string; computedAt: string; total: number; minCoverage?: number }

const SCOPE_TYPE: Record<string, string> = { countries: "country", metros: "metro", operators: "operator", facilities: "facility" };

function asScope(v: unknown): Ranking["scope"] {
  const s = str(v);
  return s === "countries" || s === "metros" || s === "operators" || s === "facilities" ? s : "facilities";
}

export function toRankingRows(scope: string, raw: unknown, limit?: number): RankingRow[] {
  const arr = json<Array<Record<string, unknown>>>(raw, []);
  const rows: RankingRow[] = arr.map((r, i) => {
    const slug = reqStr(r.slug, reqStr(r.id));
    return {
      rank: int(r.rank, i + 1),
      id: reqStr(r.id),
      slug,
      name: reqStr(r.name, slug),
      href: reqStr(r.href, hrefFor(SCOPE_TYPE[scope] ?? "facility", slug)),
      value: num(r.value),
      secondary: num(r.secondary),
      countryIso2: str(r.countryIso2 ?? r.country_iso2),
      coverage: num(r.coverage),
      delta: num(r.delta),
    };
  });
  rows.sort((a, b) => a.rank - b.rank);
  return limit ? rows.slice(0, limit) : rows;
}

export async function listRankings(): Promise<RankingListItem[]> {
  const sql = pg();
  const rows = await sql<Row[]>`select key, label, scope, unit, methodology, computed_at, total, min_coverage from rankings where is_current and key not like '%:all' order by scope, key`;
  return rows.map((r) => ({ key: reqStr(r.key), label: reqStr(r.label), scope: asScope(r.scope), unit: reqStr(r.unit), methodology: reqStr(r.methodology), computedAt: reqIso(r.computed_at), total: int(r.total), ...(num(r.min_coverage) != null ? { minCoverage: num(r.min_coverage)! } : {}) }));
}

export async function getRanking(key: string, limit?: number): Promise<Ranking | null> {
  const sql = pg();
  const rows = await sql<Row[]>`select * from rankings where key = ${key} and is_current order by computed_at desc limit 1`;
  const r = rows[0];
  if (!r) return null;
  const scope = asScope(r.scope);
  const out: Ranking = { key: reqStr(r.key), label: reqStr(r.label), unit: reqStr(r.unit), scope, methodology: reqStr(r.methodology), computedAt: reqIso(r.computed_at), rows: toRankingRows(scope, r.rows, limit), total: int(r.total) };
  const mc = num(r.min_coverage);
  if (mc != null) out.minCoverage = mc;
  return out;
}

/** Positions of one entity (matched by id or slug) across all current rankings of a scope. */
export async function rankingPositions(scope: Ranking["scope"], ids: string[]): Promise<Array<{ key: string; label: string; rank: number; total: number; value: number | null }>> {
  const sql = pg();
  const rows = await sql<Row[]>`select key, label, total, rows from rankings where is_current and scope = ${scope} and key not like '%:all' order by key`;
  const wanted = new Set(ids.map((s) => s.toLowerCase()));
  const out: Array<{ key: string; label: string; rank: number; total: number; value: number | null }> = [];
  for (const r of rows) {
    const hit = toRankingRows(scope, r.rows).find((row) => wanted.has(row.id.toLowerCase()) || wanted.has(row.slug.toLowerCase()));
    if (hit) out.push({ key: reqStr(r.key), label: reqStr(r.label), rank: hit.rank, total: int(r.total), value: hit.value });
  }
  return out;
}

/** Top rows of the first current ranking matching one of the keys (dashboard). */
export async function topRows(keys: string[], scope: Ranking["scope"], limit: number): Promise<RankingRow[] | null> {
  const sql = pg();
  const rows = await sql<Row[]>`select key, rows from rankings where is_current and scope = ${scope} and key = any(${keys}) order by array_position(${keys}::text[], key) limit 1`;
  if (!rows[0]) return null;
  return toRankingRows(scope, rows[0].rows, limit);
}