/** Slug-or-id resolution and entity reference lookups shared across repositories. */
import { pg } from "./sql.js";
import { reqStr, str, type Row } from "./rows.js";

const TABLES: Record<string, { table: string; idCol: string; slugCol: string; nameCol: string; extraSlugCol?: string }> = {
  facility: { table: "facilities", idCol: "id", slugCol: "slug", nameCol: "name" },
  campus: { table: "campuses", idCol: "id", slugCol: "slug", nameCol: "name" },
  operator: { table: "operators", idCol: "id", slugCol: "slug", nameCol: "name" },
  company: { table: "operators", idCol: "id", slugCol: "slug", nameCol: "name" },
  carrier: { table: "operators", idCol: "id", slugCol: "slug", nameCol: "name" },
  cloud_region: { table: "cloud_regions", idCol: "id", slugCol: "slug", nameCol: "name" },
  metro: { table: "metros", idCol: "id", slugCol: "slug", nameCol: "name" },
  country: { table: "countries", idCol: "iso2", slugCol: "slug", nameCol: "name" },
  ixp: { table: "ixps", idCol: "id", slugCol: "slug", nameCol: "name" },
  project: { table: "projects", idCol: "id", slugCol: "slug", nameCol: "name" },
  expansion: { table: "projects", idCol: "id", slugCol: "slug", nameCol: "name" },
  construction_phase: { table: "projects", idCol: "id", slugCol: "slug", nameCol: "name" },
  source: { table: "sources", idCol: "id", slugCol: "id", nameCol: "name" },
};

/** Whole row for an entity by slug or id (`null` when unknown). Only whitelisted tables. */
export async function findBySlugOrId(table: "facilities" | "operators" | "metros" | "projects" | "cloud_regions" | "ixps" | "campuses", idOrSlug: string): Promise<Row | null> {
  const sql = pg();
  const rows = await sql<Row[]>`select * from ${sql(table)} where slug = ${idOrSlug} or id = ${idOrSlug} order by (id = ${idOrSlug}) desc limit 1`;
  return rows[0] ?? null;
}

export async function findCountry(slugOrIso2: string): Promise<Row | null> {
  const sql = pg();
  const up = slugOrIso2.toUpperCase();
  const rows = await sql<Row[]>`select * from countries where slug = ${slugOrIso2.toLowerCase()} or iso2 = ${up} or iso3 = ${up} limit 1`;
  return rows[0] ?? null;
}

/** Resolve {slug, name} for (entityType, entityId) pairs in one query per entity type. */
export async function resolveEntityRefs(pairs: Array<{ type: string; id: string | null }>): Promise<Map<string, { slug: string; name: string }>> {
  const sql = pg();
  const out = new Map<string, { slug: string; name: string }>();
  const byTable = new Map<string, { def: (typeof TABLES)[string]; ids: Set<string> }>();
  for (const p of pairs) {
    if (!p.id) continue;
    const def = TABLES[p.type];
    if (!def) continue;
    let g = byTable.get(def.table);
    if (!g) { g = { def, ids: new Set() }; byTable.set(def.table, g); }
    g.ids.add(p.id);
  }
  await Promise.all(
    [...byTable.values()].map(async ({ def, ids }) => {
      const list = [...ids];
      const rows = await sql<Row[]>`select ${sql(def.idCol)} as id, ${sql(def.slugCol)} as slug, ${sql(def.nameCol)} as name from ${sql(def.table)} where ${sql(def.idCol)} = any(${list})`;
      for (const r of rows) out.set(`${def.table}:${reqStr(r.id)}`, { slug: reqStr(r.slug), name: reqStr(r.name) });
    }),
  );
  // re-key by (type,id) so callers can look up with their own entity type
  const keyed = new Map<string, { slug: string; name: string }>();
  for (const p of pairs) {
    if (!p.id) continue;
    const def = TABLES[p.type];
    if (!def) continue;
    const v = out.get(`${def.table}:${p.id}`);
    if (v) keyed.set(`${p.type}:${p.id}`, v);
  }
  return keyed;
}

export function entityKey(type: unknown, id: unknown): string {
  return `${str(type) ?? ""}:${str(id) ?? ""}`;
}

/** href for a resolved entity on the web app. */
export function hrefFor(type: string, slug: string): string {
  switch (type) {
    case "facility": case "campus": return `/datacenters/${slug}`;
    case "operator": case "company": case "carrier": return `/operators/${slug}`;
    case "country": return `/countries/${slug}`;
    case "metro": return `/datacenters/markets/${slug}`;
    case "cloud_region": return `/cloud-regions/${slug}`;
    case "project": case "expansion": case "construction_phase": return `/projects/${slug}`;
    case "ixp": return `/ixps/${slug}`;
    default: return `/search?q=${encodeURIComponent(slug)}`;
  }
}