import 'server-only';
import { run, sql, safe } from '@/lib/db';

export interface SourceRow {
  id: string;
  slug: string;
  name: string;
  organization: string | null;
  category: string;
  description: string | null;
  homepage: string | null;
  docs_url: string | null;
  terms_url: string | null;
  access_type: string;
  access_auth: string;
  license: string | null;
  license_status: string;
  commercial_use: string;
  redistribution: string;
  attribution: string | null;
  license_reviewed_at: Date | null;
  approved_for_production: boolean;
  update_frequency: string | null;
  supports_incremental: boolean;
  entities: string[];
  metrics: string[];
  rate_limit: string | null;
  status: string;
  tier: number;
  manifest: Record<string, unknown>;
  updated_at: Date;
  // joined
  health: string | null;
  health_detail: string | null;
  last_success_at: Date | null;
  last_attempt_at: Date | null;
  paused: boolean | null;
  record_count: string | number | null;
  last_run_id: string | null;
  last_run_status: string | null;
  last_run_finished_at: Date | null;
  last_run_fetched: number | null;
  last_run_created: number | null;
  last_run_updated: number | null;
  last_run_rejected: number | null;
  last_run_drift: unknown[] | null;
  last_dataset_version: string | null;
}

const SOURCE_SELECT = sql`
  SELECT s.*, cc.health, cc.health_detail, cc.last_success_at, cc.last_attempt_at, cc.paused,
         (SELECT count(*) FROM source_records r WHERE r.source_id = s.id) AS record_count,
         lr.id AS last_run_id, lr.status AS last_run_status, lr.finished_at AS last_run_finished_at,
         lr.records_fetched AS last_run_fetched, lr.records_created AS last_run_created, lr.records_updated AS last_run_updated,
         lr.records_rejected AS last_run_rejected, lr.schema_drift AS last_run_drift, lr.dataset_version AS last_dataset_version
  FROM sources s
  LEFT JOIN connector_cursors cc ON cc.connector_id = s.slug
  LEFT JOIN LATERAL (SELECT * FROM ingest_runs ir WHERE ir.source_id = s.id ORDER BY ir.started_at DESC LIMIT 1) lr ON true`;

export async function listSources(): Promise<SourceRow[]> {
  return safe(() => run<SourceRow>(sql`${SOURCE_SELECT} ORDER BY s.tier, s.category, s.name`), [] as SourceRow[]);
}

export async function getSourceBySlug(slug: string): Promise<SourceRow | null> {
  const rows = await safe(() => run<SourceRow>(sql`${SOURCE_SELECT} WHERE s.slug = ${slug} LIMIT 1`), [] as SourceRow[]);
  return rows[0] ?? null;
}

export interface RunRow {
  id: string;
  connector_id: string;
  source_id: string;
  mode: string;
  status: string;
  started_at: Date;
  finished_at: Date | null;
  duration_ms: number | null;
  records_fetched: number;
  records_created: number;
  records_updated: number;
  records_unchanged: number;
  records_rejected: number;
  http_requests: number;
  http_failures: number;
  rate_limit_events: number;
  validation_failures: number;
  schema_drift: unknown[];
  cursor_before: unknown;
  cursor_after: unknown;
  error: string | null;
  log: Array<{ t: string; level: string; msg: string }>;
  dataset_version: string | null;
  anomaly: string | null;
  source_slug?: string;
  source_name?: string;
}

export async function listRuns(opts: { sourceId?: string; connectorId?: string; limit?: number } = {}): Promise<RunRow[]> {
  const limit = opts.limit ?? 25;
  return safe(
    () =>
      run<RunRow>(sql`
        SELECT r.*, s.slug AS source_slug, s.name AS source_name FROM ingest_runs r LEFT JOIN sources s ON s.id = r.source_id
        WHERE ${opts.sourceId ? sql`r.source_id = ${opts.sourceId}` : sql`true`} AND ${opts.connectorId ? sql`r.connector_id = ${opts.connectorId}` : sql`true`}
        ORDER BY r.started_at DESC LIMIT ${limit}`),
    [] as RunRow[],
  );
}

export async function getRun(id: string): Promise<RunRow | null> {
  const rows = await safe(() => run<RunRow>(sql`SELECT r.*, s.slug AS source_slug, s.name AS source_name FROM ingest_runs r LEFT JOIN sources s ON s.id = r.source_id WHERE r.id = ${id}`), [] as RunRow[]);
  return rows[0] ?? null;
}

export async function recordCountsByKind(sourceId: string): Promise<Array<{ entity_kind: string; n: number; status: string; last_retrieved: Date | null }>> {
  const rows = await safe(
    () => run<{ entity_kind: string; n: string; status: string; last_retrieved: Date | null }>(sql`SELECT entity_kind, status, count(*) AS n, max(retrieved_at) AS last_retrieved FROM source_records WHERE source_id = ${sourceId} GROUP BY entity_kind, status ORDER BY entity_kind, status`),
    [] as Array<{ entity_kind: string; n: string; status: string; last_retrieved: Date | null }>,
  );
  return rows.map((r) => ({ ...r, n: Number(r.n) }));
}

/** Domains for the public coverage matrix (§307-308). */
export const COVERAGE_DOMAINS = ['Taxonomy', 'Epidemiology', 'Genomics', 'Variants', 'Trials', 'Drugs', 'Literature'] as const;
export type CoverageDomain = (typeof COVERAGE_DOMAINS)[number];

/** Which domains a source contributes to, from its declared category/entities/metrics (manifest). */
export function sourceDomains(s: Pick<SourceRow, 'category' | 'entities' | 'metrics'>): Set<CoverageDomain> {
  const out = new Set<CoverageDomain>();
  const ents = new Set([...(s.entities ?? []), ...(s.metrics ?? [])].map((e) => e.toLowerCase()));
  const has = (...keys: string[]) => keys.some((k) => [...ents].some((e) => e.includes(k)));
  if (s.category === 'terminology' || has('cancer', 'tumor_type', 'concept', 'disease')) out.add('Taxonomy');
  if (s.category === 'epidemiology' || has('incidence', 'mortality', 'survival', 'prevalence')) out.add('Epidemiology');
  if (s.category === 'genomics' || has('gene', 'cohort', 'frequency', 'mutation')) out.add('Genomics');
  if (s.category === 'variants' || has('variant', 'evidence', 'clinical_significance')) out.add('Variants');
  if (s.category === 'trials' || has('trial', 'study')) out.add('Trials');
  if (s.category === 'drugs' || s.category === 'regulatory' || has('drug', 'therapy', 'approval')) out.add('Drugs');
  if (s.category === 'literature' || has('publication', 'literature', 'pubmed')) out.add('Literature');
  return out;
}

/** Plain-language meaning of the license status (§308). */
export function licenseMeaning(status: string, redistribution: string, commercial: string): string {
  const base: Record<string, string> = {
    approved: 'Reviewed: CancerIndex may ingest and display this data with attribution.',
    review: 'Under review: terms are being assessed. No records are shown publicly until the review completes.',
    restricted: 'Restricted: data may be displayed with limits (e.g. no bulk redistribution).',
    blocked: 'Blocked: the license does not permit use by CancerIndex.',
  };
  const red: Record<string, string> = {
    allowed: 'Redistribution allowed.',
    attribution: 'Redistribution allowed with attribution.',
    restricted: 'Redistribution restricted — not included in downloads.',
    prohibited: 'Redistribution prohibited — not included in downloads.',
    unknown: 'Redistribution terms not yet determined.',
  };
  const com: Record<string, string> = { allowed: 'Commercial use allowed.', restricted: 'Commercial use restricted.', prohibited: 'Commercial use prohibited.', unknown: 'Commercial-use terms not yet determined.' };
  return [base[status] ?? status, red[redistribution] ?? '', com[commercial] ?? ''].filter(Boolean).join(' ');
}