/** Smaller public repositories: time series, sources, news, sitemap feeds. */
import type { SourceRef } from "@dci/core";
import { pg, page, likePattern, type Fragment } from "../lib/sql.js";
import { int, iso, json, num, reqIso, reqStr, str, strArray, type Row } from "../lib/rows.js";
import { sourceRef } from "../lib/dto.js";

export async function timeseries(metric: string, dim: string, days: number): Promise<Array<{ day: string; value: number }>> {
  const sql = pg();
  const rows = await sql<Row[]>`select day::text as day, value from daily_metrics where metric = ${metric} and dim = ${dim} and day >= (current_date - ${days}::int) order by day`;
  return rows.map((r) => ({ day: reqStr(r.day), value: num(r.value) ?? 0 }));
}

export async function availableMetrics(): Promise<Array<{ metric: string; dims: number; from: string | null; to: string | null }>> {
  const sql = pg();
  const rows = await sql<Row[]>`select metric, count(distinct dim)::int as dims, min(day)::text as from_day, max(day)::text as to_day from daily_metrics group by metric order by metric`;
  return rows.map((r) => ({ metric: reqStr(r.metric), dims: int(r.dims), from: str(r.from_day), to: str(r.to_day) }));
}

export interface SourceWithCounts extends SourceRef { connectorId: string; priority: number; robotsAllowed: boolean | null; notes: string | null; documents: number; provenanceRows: number; facilities: number; lastDocumentAt: string | null; updatedAt: string }

function sourceWithCounts(r: Row): SourceWithCounts {
  return { ...sourceRef(r), connectorId: reqStr(r.connector_id), priority: int(r.priority, 3), robotsAllowed: r.robots_allowed == null ? null : r.robots_allowed === true, notes: str(r.notes), documents: int(r.documents), provenanceRows: int(r.provenance_rows), facilities: int(r.facilities), lastDocumentAt: iso(r.last_document_at), updatedAt: reqIso(r.updated_at) };
}

const SOURCE_SELECT = (sql: ReturnType<typeof pg>) => sql`
  select s.*, coalesce(d.n, 0) as documents, d.last_document_at, coalesce(p.n, 0) as provenance_rows, coalesce(p.facilities, 0) as facilities
  from sources s
  left join (select source_id, count(*)::int as n, max(last_fetched) as last_document_at from documents group by source_id) d on d.source_id = s.id
  left join (select source_id, count(*)::int as n, count(distinct entity_id) filter (where entity_type = 'facility')::int as facilities from provenance where is_current group by source_id) p on p.source_id = s.id`;

export async function listSources(): Promise<SourceWithCounts[]> {
  const sql = pg();
  const rows = await sql<Row[]>`${SOURCE_SELECT(sql)} order by coalesce(p.n, 0) desc, s.name`;
  return rows.map(sourceWithCounts);
}

export async function getSource(id: string): Promise<(SourceWithCounts & { byPageType: Array<{ pageType: string; count: number }>; byEntityType: Array<{ entityType: string; count: number }> }) | null> {
  const sql = pg();
  const rows = await sql<Row[]>`${SOURCE_SELECT(sql)} where s.id = ${id}`;
  if (!rows[0]) return null;
  const [pt, et] = await Promise.all([
    sql<Row[]>`select page_type, count(*)::int as n from documents where source_id = ${id} group by page_type order by n desc`,
    sql<Row[]>`select entity_type, count(*)::int as n from provenance where source_id = ${id} and is_current group by entity_type order by n desc`,
  ]);
  return { ...sourceWithCounts(rows[0]), byPageType: pt.map((r) => ({ pageType: reqStr(r.page_type), count: int(r.n) })), byEntityType: et.map((r) => ({ entityType: reqStr(r.entity_type), count: int(r.n) })) };
}

export interface NewsItemDTO {
  id: string; title: string; url: string; publishedAt: string | null; summary: string | null; pageType: string; eventType: string | null; mw: number | null; significance: number;
  source: { id: string; name: string; kind: string } | null; countryIso2s: string[]; operators: Array<{ id: string; slug: string; name: string }>; projectId: string | null; mentions: Record<string, unknown>;
}

export async function listNews(f: { country?: string; operator?: string; since?: string; q?: string; page?: number; per_page?: number }): Promise<{ items: NewsItemDTO[]; total: number; page: number; perPage: number }> {
  const sql = pg();
  const pg_ = page(f.page, f.per_page, 100, 30);
  const c: Fragment[] = [sql`true`];
  if (f.country) c.push(sql`${f.country.toUpperCase()} = any(n.country_iso2s)`);
  if (f.operator) c.push(sql`exists (select 1 from operators o where o.id = any(n.operator_ids) and (o.slug = ${f.operator} or o.id = ${f.operator}))`);
  if (f.since) c.push(sql`coalesce(n.published_at, n.created_at) >= ${f.since}::timestamptz`);
  if (f.q) c.push(sql`(n.title ilike ${likePattern(f.q)} or n.summary ilike ${likePattern(f.q)})`);
  const rows = await sql<Row[]>`
    select n.*, s.name as source_name, s.kind as source_kind,
      (select coalesce(jsonb_agg(jsonb_build_object('id', o.id, 'slug', o.slug, 'name', o.name)), '[]'::jsonb) from operators o where o.id = any(n.operator_ids)) as ops,
      count(*) over() as total
    from news_items n left join sources s on s.id = n.source_id
    where ${c.reduce<Fragment>((a, x) => sql`${a} and ${x}`, sql`true`)}
    order by coalesce(n.published_at, n.created_at) desc limit ${pg_.perPage} offset ${pg_.offset}`;
  return {
    items: rows.map((r) => ({ id: reqStr(r.id), title: reqStr(r.title), url: reqStr(r.url), publishedAt: iso(r.published_at), summary: str(r.summary), pageType: reqStr(r.page_type, "unknown"), eventType: str(r.event_type), mw: num(r.mw), significance: int(r.significance, 20), source: r.source_id ? { id: reqStr(r.source_id), name: reqStr(r.source_name, reqStr(r.source_id)), kind: reqStr(r.source_kind, "news") } : null, countryIso2s: strArray(r.country_iso2s), operators: json<Array<{ id: string; slug: string; name: string }>>(r.ops, []), projectId: str(r.project_id), mentions: json<Record<string, unknown>>(r.mentions, {}) })),
    total: rows.length ? int(rows[0]!.total) : 0,
    page: pg_.page,
    perPage: pg_.perPage,
  };
}

export const SITEMAP_KINDS = ["facilities", "operators", "countries", "metros", "projects", "cloud-regions"] as const;
export type SitemapKind = (typeof SITEMAP_KINDS)[number];
export const SITEMAP_PAGE = 5000;

export async function sitemap(kind: SitemapKind, pageNo: number): Promise<{ items: Array<{ slug: string; updatedAt: string }>; total: number; page: number; perPage: number }> {
  const sql = pg();
  const offset = Math.max(0, pageNo - 1) * SITEMAP_PAGE;
  const table = kind === "cloud-regions" ? "cloud_regions" : kind;
  // facilities: merged duplicates hidden; projects: false positives (hidden) and merged duplicates never listed
  const where = kind === "facilities" ? sql`where merged_into is null` : kind === "projects" ? sql`where hidden = false and merged_into is null` : sql``;
  const rows = await sql<Row[]>`select slug, updated_at, count(*) over() as total from ${sql(table)} ${where} order by slug limit ${SITEMAP_PAGE} offset ${offset}`;
  const total = rows.length ? int(rows[0]!.total) : int((await sql<Row[]>`select count(*)::int as n from ${sql(table)} ${where}`)[0]?.n);
  return { items: rows.map((r) => ({ slug: reqStr(r.slug), updatedAt: reqIso(r.updated_at) })), total, page: pageNo, perPage: SITEMAP_PAGE };
}