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

export interface PublicationRow {
  id: string;
  pmid: string | null;
  doi: string | null;
  pmcid: string | null;
  title: string;
  abstract: string | null;
  journal: string | null;
  journal_iso: string | null;
  pub_date: string | null;
  pub_year: number | null;
  publication_types: string[];
  mesh_terms: Array<{ descriptor: string; ui?: string; major: boolean; qualifiers?: string[] }>;
  authors: Array<{ name: string; affiliation?: string; orcid?: string }>;
  language: string | null;
  is_preprint: boolean;
  retracted: boolean;
  retraction_notice: string | null;
  nct_ids: string[];
  cited_by_count: number | null;
  ingest_run_id: string | null;
  updated_at: Date;
}

export async function getPublicationByPmid(pmid: string): Promise<PublicationRow | null> {
  const rows = await safe(() => run<PublicationRow>(sql`SELECT * FROM publications WHERE pmid = ${pmid} LIMIT 1`), [] as PublicationRow[]);
  return rows[0] ?? null;
}

export interface PubEdge {
  entity_type: string;
  entity_id: string;
  method: string;
  confidence: number | null;
  status: string;
  label: string | null;
  href: string | null;
}
export async function publicationEntities(publicationId: string): Promise<PubEdge[]> {
  return safe(
    () =>
      run<PubEdge>(sql`
        SELECT e.entity_type, e.entity_id, e.method, e.confidence, e.status,
          CASE e.entity_type WHEN 'cancer' THEN (SELECT canonical_name FROM cancers WHERE id = e.entity_id)
                             WHEN 'gene' THEN (SELECT symbol FROM genes WHERE id = e.entity_id)
                             WHEN 'variant' THEN (SELECT coalesce(gene_symbol || ' ', '') || name FROM variants WHERE id = e.entity_id)
                             WHEN 'drug' THEN (SELECT name FROM drugs WHERE id = e.entity_id)
                             WHEN 'trial' THEN (SELECT nct_id FROM clinical_trials WHERE id = e.entity_id) END AS label,
          CASE e.entity_type WHEN 'cancer' THEN (SELECT '/cancer/' || slug FROM cancers WHERE id = e.entity_id)
                             WHEN 'gene' THEN (SELECT '/gene/' || symbol FROM genes WHERE id = e.entity_id)
                             WHEN 'variant' THEN (SELECT '/variant/' || slug FROM variants WHERE id = e.entity_id)
                             WHEN 'drug' THEN (SELECT '/drug/' || slug FROM drugs WHERE id = e.entity_id)
                             WHEN 'trial' THEN (SELECT '/trial/' || nct_id FROM clinical_trials WHERE id = e.entity_id) END AS href
        FROM publication_entity_edges e WHERE e.publication_id = ${publicationId} ORDER BY e.status, e.entity_type, label`),
    [] as PubEdge[],
  );
}

export interface LitCount {
  id: number;
  window_key: string;
  window_start: string | null;
  window_end: string | null;
  query: string;
  count: number;
  provenance_id: number;
  updated_at: Date; // schema `computedAt` uses the updatedAt() helper → column `updated_at`
}
export async function literatureCountsFor(cancerId: string): Promise<LitCount[]> {
  return safe(() => run<LitCount>(sql`SELECT * FROM literature_counts WHERE cancer_id = ${cancerId} ORDER BY CASE window_key WHEN 'all' THEN 0 WHEN '10y' THEN 1 WHEN '5y' THEN 2 WHEN '5y_prior' THEN 3 WHEN '12m' THEN 4 ELSE 5 END, window_key`), [] as LitCount[]);
}

export const PUBLICATION_PAGE_SIZE = 25;

/** Columns the publication lists render (never the abstract or the full MeSH/author arrays). */
export type PublicationListRow = Pick<PublicationRow, 'id' | 'pmid' | 'title' | 'journal' | 'journal_iso' | 'pub_date' | 'pub_year' | 'is_preprint' | 'retracted' | 'updated_at' | 'ingest_run_id'> & {
  /** First three authors only. */
  authors: Array<{ name: string }>;
  author_count: number;
  method?: string;
  edge_status?: string;
};

const LIST_COLUMNS = sql`p.id, p.pmid, p.title, p.journal, p.journal_iso, p.pub_date, p.pub_year, p.is_preprint, p.retracted, p.updated_at, p.ingest_run_id,
  (SELECT coalesce(jsonb_agg(jsonb_build_object('name', a->>'name')), '[]'::jsonb) FROM (SELECT a FROM jsonb_array_elements(p.authors) WITH ORDINALITY x(a, i) ORDER BY i LIMIT 3) s) AS authors,
  jsonb_array_length(p.authors) AS author_count`;

const whereEntity = (entityType: string, entityIds: string[]) => sql`e.entity_type = ${entityType} AND e.entity_id IN (${sql.join(entityIds.map((i) => sql`${i}`), sql`, `)}) AND e.status <> 'rejected'`;

/** Publications linked to any of the entities, newest first, one row per publication (best edge status). */
export async function recentPublicationsFor(entityType: string, entityIds: string[], p: { page: number; pageSize: number } = { page: 1, pageSize: PUBLICATION_PAGE_SIZE }): Promise<PublicationListRow[]> {
  if (entityIds.length === 0) return [];
  return safe(
    () =>
      run<PublicationListRow>(sql`
        SELECT ${LIST_COLUMNS}, x.method, x.edge_status FROM (
          SELECT DISTINCT ON (e.publication_id) e.publication_id, e.method, e.status AS edge_status
          FROM publication_entity_edges e WHERE ${whereEntity(entityType, entityIds)} ORDER BY e.publication_id, e.status
        ) x JOIN publications p ON p.id = x.publication_id
        ORDER BY p.pub_date DESC NULLS LAST, p.id LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`),
    [] as PublicationListRow[],
  );
}
export async function recentPublicationsForCount(entityType: string, entityIds: string[]): Promise<number> {
  if (entityIds.length === 0) return 0;
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(DISTINCT e.publication_id) AS n FROM publication_entity_edges e WHERE ${whereEntity(entityType, entityIds)}`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

export async function publicationsForTrial(nctId: string, p: { page: number; pageSize: number } = { page: 1, pageSize: PUBLICATION_PAGE_SIZE }): Promise<PublicationListRow[]> {
  return safe(() => run<PublicationListRow>(sql`SELECT ${LIST_COLUMNS} FROM publications p WHERE ${nctId} = ANY(p.nct_ids) ORDER BY p.pub_date DESC NULLS LAST, p.id LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`), [] as PublicationListRow[]);
}
export async function publicationsForTrialCount(nctId: string): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM publications p WHERE ${nctId} = ANY(p.nct_ids)`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}