import { sql } from 'drizzle-orm';
import type { Database } from '@cancerindex/database';
import type { SourceRef } from './envelope.js';

/**
 * Load source references for the envelope. Accepts CI-SOURCE ids and/or connector slugs (ranking
 * snapshots store slugs for count metrics and ids for epidemiology scopes).
 */
export async function loadSources(db: Database, refs: Iterable<string | null | undefined>): Promise<SourceRef[]> {
  const ids = new Set<string>();
  const slugs = new Set<string>();
  for (const r of refs) {
    if (!r) continue;
    if (r.startsWith('CI-SOURCE-')) ids.add(r);
    else slugs.add(r);
  }
  if (ids.size === 0 && slugs.size === 0) return [];
  const rows = await db.execute<{ id: string; slug: string; name: string; license: string | null; attribution: string | null; homepage: string | null }>(sql`
    SELECT id, slug, name, license, attribution, homepage FROM sources
    WHERE id = ANY(${sql.param([...ids])}::text[]) OR slug = ANY(${sql.param([...slugs])}::text[])
    ORDER BY slug`);
  return rows.map((r) => ({ id: r.id, slug: r.slug, name: r.name, license: r.license, attribution: r.attribution, url: r.homepage }));
}

/** Collect a column from rows (null-safe). */
export function pluck<T extends Record<string, unknown>>(rows: readonly T[], ...keys: Array<keyof T>): string[] {
  const out: string[] = [];
  for (const r of rows) for (const k of keys) if (typeof r[k] === 'string') out.push(r[k] as string);
  return out;
}

/** Sources that contributed codes, aliases or hierarchy edges to a set of cancers. */
export async function cancerStructuralSourceIds(db: Database, cancerIds: string[]): Promise<string[]> {
  if (cancerIds.length === 0) return [];
  const rows = await db.execute<{ source_id: string }>(sql`
    SELECT DISTINCT source_id FROM (
      SELECT source_id FROM cancer_codes WHERE cancer_id = ANY(${sql.param(cancerIds)}::text[]) AND source_id IS NOT NULL
      UNION SELECT source_id FROM cancer_aliases WHERE cancer_id = ANY(${sql.param(cancerIds)}::text[]) AND source_id IS NOT NULL
      UNION SELECT source_id FROM cancer_hierarchy WHERE (child_id = ANY(${sql.param(cancerIds)}::text[]) OR parent_id = ANY(${sql.param(cancerIds)}::text[])) AND source_id IS NOT NULL
    ) s`);
  return rows.map((r) => r.source_id);
}

/** Most recent successful ingest finish — used for the dataRelease label. Cached by the caller. */
export async function latestDataAsOf(db: Database): Promise<Date | undefined> {
  const rows = await db.execute<{ t: string | null }>(sql`SELECT max(finished_at)::text AS t FROM ingest_runs WHERE status IN ('succeeded','partial')`);
  const t = rows[0]?.t;
  return t ? new Date(t) : undefined;
}