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

export interface AdminOverview {
  tables: Array<{ table: string; n: number }>;
  runs24h: number;
  failedRuns7d: number;
  openUnresolved: number;
  currentSnapshots: number;
  auditRecent: Array<{ id: number; actor: string; action: string; entity_type: string | null; entity_id: string | null; reason: string | null; created_at: Date }>;
}

const COUNTED_TABLES = ['cancers', 'cancer_aliases', 'cancer_hierarchy', 'cancer_codes', 'anatomical_sites', 'geographies', 'sources', 'ingest_runs', 'source_records', 'provenance', 'genes', 'variants', 'drugs', 'drug_approvals', 'clinical_trials', 'trial_conditions', 'publications', 'literature_counts', 'civic_evidence_items', 'genomic_cohorts', 'cancer_gene_frequencies', 'epidemiology_observations', 'survival_observations', 'ranking_snapshots', 'rankings', 'entity_counters', 'unresolved_labels', 'change_events', 'audit_log'];

export async function adminOverview(): Promise<AdminOverview> {
  const tables = await safe(
    () => run<{ table: string; n: string }>(sql.join(COUNTED_TABLES.map((t) => sql`SELECT ${t}::text AS "table", (SELECT count(*) FROM ${sql.identifier(t)}) AS n`), sql` UNION ALL `)),
    [] as Array<{ table: string; n: string }>,
  );
  const [misc] = await safe(
    () =>
      run<{ runs24h: string; failed7d: string; open_unresolved: string; snaps: string }>(sql`
        SELECT (SELECT count(*) FROM ingest_runs WHERE started_at > now() - interval '24 hours') AS runs24h,
               (SELECT count(*) FROM ingest_runs WHERE status IN ('failed','aborted') AND started_at > now() - interval '7 days') AS failed7d,
               (SELECT count(*) FROM unresolved_labels WHERE status = 'open') AS open_unresolved,
               (SELECT count(*) FROM ranking_snapshots WHERE is_current) AS snaps`),
    [{ runs24h: '0', failed7d: '0', open_unresolved: '0', snaps: '0' }],
  );
  const auditRecent = await safe(() => run<AdminOverview['auditRecent'][number]>(sql`SELECT id, actor, action, entity_type, entity_id, reason, created_at FROM audit_log ORDER BY created_at DESC LIMIT 15`), []);
  return { tables: tables.map((t) => ({ table: t.table, n: Number(t.n) })), runs24h: Number(misc?.runs24h ?? 0), failedRuns7d: Number(misc?.failed7d ?? 0), openUnresolved: Number(misc?.open_unresolved ?? 0), currentSnapshots: Number(misc?.snaps ?? 0), auditRecent };
}

export interface UnresolvedRow {
  id: number;
  source_id: string;
  source_slug: string;
  entity_kind: string;
  source_text: string;
  normalized: string;
  context: Record<string, unknown>;
  count: number;
  status: string;
  suggested_id: string | null;
  suggested_name: string | null;
  suggested_match_type: string | null;
  suggested_score: number | null;
  resolved_id: string | null;
  resolved_by: string | null;
  created_at: Date;
  updated_at: Date;
}

export async function listUnresolved(opts: { entityKind: string; status: string; page: number; pageSize: number }): Promise<{ rows: UnresolvedRow[]; total: number; kinds: Array<{ k: string; n: number }> }> {
  const where = sql`${opts.entityKind ? sql`u.entity_kind = ${opts.entityKind}` : sql`true`} AND ${opts.status ? sql`u.status = ${opts.status}` : sql`true`}`;
  const total = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM unresolved_labels u WHERE ${where}`), [{ n: '0' }]);
  const rows = await safe(
    () =>
      run<UnresolvedRow>(sql`
        SELECT u.*, s.slug AS source_slug,
          CASE u.entity_kind WHEN 'cancer' THEN (SELECT canonical_name FROM cancers WHERE id = u.suggested_id) WHEN 'drug' THEN (SELECT name FROM drugs WHERE id = u.suggested_id) WHEN 'gene' THEN (SELECT symbol FROM genes WHERE id = u.suggested_id) END AS suggested_name
        FROM unresolved_labels u JOIN sources s ON s.id = u.source_id WHERE ${where} ORDER BY u.count DESC, u.updated_at DESC LIMIT ${opts.pageSize} OFFSET ${(opts.page - 1) * opts.pageSize}`),
    [] as UnresolvedRow[],
  );
  const kinds = await safe(() => run<{ k: string; n: string }>(sql`SELECT entity_kind AS k, count(*) AS n FROM unresolved_labels WHERE status = 'open' GROUP BY 1 ORDER BY n DESC`), [] as Array<{ k: string; n: string }>);
  return { rows, total: Number(total[0]?.n ?? 0), kinds: kinds.map((k) => ({ k: k.k, n: Number(k.n) })) };
}

export interface ConnectorHealthRow {
  slug: string;
  name: string;
  category: string;
  status: string;
  license_status: string;
  health: string | null;
  health_detail: string | null;
  paused: boolean | null;
  last_success_at: Date | null;
  last_attempt_at: Date | null;
  last_run_id: string | null;
  last_run_status: string | null;
  records_fetched: number | null;
  records_created: number | null;
  records_updated: number | null;
  records_rejected: number | null;
  schema_drift: unknown[] | null;
  runs_total: number;
  drift_fields: number;
}
export async function connectorHealth(): Promise<ConnectorHealthRow[]> {
  return safe(
    () =>
      run<ConnectorHealthRow>(sql`
        SELECT s.slug, s.name, s.category, s.status, s.license_status, cc.health, cc.health_detail, cc.paused, cc.last_success_at, cc.last_attempt_at,
               lr.id AS last_run_id, lr.status AS last_run_status, lr.records_fetched, lr.records_created, lr.records_updated, lr.records_rejected, lr.schema_drift,
               (SELECT count(*) FROM ingest_runs r WHERE r.source_id = s.id)::int AS runs_total,
               (SELECT count(*) FROM connector_field_stats f WHERE f.connector_id = s.slug)::int AS drift_fields
        FROM sources s LEFT JOIN connector_cursors cc ON cc.connector_id = s.slug
        LEFT JOIN LATERAL (SELECT * FROM ingest_runs r WHERE r.source_id = s.id ORDER BY r.started_at DESC LIMIT 1) lr ON true
        ORDER BY s.tier, s.name`),
    [] as ConnectorHealthRow[],
  );
}