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

export const ACTIVE_STATUSES = ['RECRUITING', 'NOT_YET_RECRUITING', 'ENROLLING_BY_INVITATION', 'ACTIVE_NOT_RECRUITING'];

export interface TrialRow {
  id: string;
  nct_id: string;
  brief_title: string;
  official_title: string | null;
  acronym: string | null;
  study_type: string | null;
  phases: string[];
  overall_status: string | null;
  why_stopped: string | null;
  start_date: string | null;
  primary_completion_date: string | null;
  completion_date: string | null;
  first_posted_date: string | null;
  last_update_posted_date: string | null;
  results_first_posted_date: string | null;
  has_results: boolean;
  enrollment_count: number | null;
  enrollment_type: string | null;
  lead_sponsor: string | null;
  lead_sponsor_class: string | null;
  collaborators: string[];
  conditions: string[];
  keywords: string[];
  interventions: Array<{ type: string; name: string; description?: string; otherNames?: string[] }>;
  arms: Array<Record<string, unknown>>;
  primary_outcomes: Array<Record<string, unknown>>;
  secondary_outcomes: Array<Record<string, unknown>>;
  eligibility: Record<string, unknown>;
  sex: string | null;
  minimum_age: string | null;
  maximum_age: string | null;
  countries: string[];
  locations_count: number;
  references: Array<{ pmid?: string; type?: string; citation?: string }>;
  brief_summary: string | null;
  is_oncology: boolean;
  source_record_id: number | null;
  ingest_run_id: string | null;
  created_at: Date;
  updated_at: Date;
}

export interface TrialFilters {
  q: string;
  status: string;
  phase: string;
  country: string;
  cancerIds: string[] | null; // null = no cancer filter
  page: number;
  pageSize: number;
}

function trialWhere(f: TrialFilters) {
  const parts = [sql`true`];
  if (f.q) parts.push(sql`(t.nct_id ILIKE ${f.q + '%'} OR t.brief_title ILIKE ${'%' + f.q + '%'} OR t.acronym ILIKE ${f.q} OR t.lead_sponsor ILIKE ${'%' + f.q + '%'})`);
  if (f.status === 'active') parts.push(sql`t.overall_status = ANY(${sql.param(ACTIVE_STATUSES)}::text[])`);
  else if (f.status) parts.push(sql`t.overall_status = ${f.status}`);
  if (f.phase) parts.push(sql`${f.phase} = ANY(t.phases)`);
  if (f.country) parts.push(sql`${f.country} = ANY(t.countries)`);
  if (f.cancerIds) parts.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id IN (${sql.join(f.cancerIds.map((i) => sql`${i}`), sql`, `)}))`);
  return sql.join(parts, sql` AND `);
}

export async function listTrials(f: TrialFilters): Promise<{ rows: TrialRow[]; total: number }> {
  if (f.cancerIds && f.cancerIds.length === 0) return { rows: [], total: 0 };
  const total = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM clinical_trials t WHERE ${trialWhere(f)}`), [{ n: '0' }]);
  const rows = await safe(
    () => run<TrialRow>(sql`SELECT t.* FROM clinical_trials t WHERE ${trialWhere(f)} ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${f.pageSize} OFFSET ${(f.page - 1) * f.pageSize}`),
    [] as TrialRow[],
  );
  return { rows, total: Number(total[0]?.n ?? 0) };
}

export async function trialFacets(cancerIds: string[] | null): Promise<{ statuses: Array<{ k: string; n: number }>; phases: Array<{ k: string; n: number }>; countries: Array<{ k: string; n: number }> }> {
  const scope = cancerIds && cancerIds.length ? sql`WHERE EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}))` : sql``;
  const statuses = await safe(() => run<{ k: string; n: string }>(sql`SELECT overall_status AS k, count(*) AS n FROM clinical_trials t ${scope} GROUP BY 1 ORDER BY n DESC`), [] as Array<{ k: string; n: string }>);
  const phases = await safe(() => run<{ k: string; n: string }>(sql`SELECT p AS k, count(*) AS n FROM clinical_trials t, unnest(t.phases) p ${scope} GROUP BY 1 ORDER BY 1`), [] as Array<{ k: string; n: string }>);
  const countries = await safe(() => run<{ k: string; n: string }>(sql`SELECT c AS k, count(*) AS n FROM clinical_trials t, unnest(t.countries) c ${scope} GROUP BY 1 ORDER BY n DESC LIMIT 60`), [] as Array<{ k: string; n: string }>);
  const num = (a: Array<{ k: string; n: string }>) => a.filter((x) => x.k).map((x) => ({ k: x.k, n: Number(x.n) }));
  return { statuses: num(statuses), phases: num(phases), countries: num(countries) };
}

export async function getTrialByNct(nct: string): Promise<TrialRow | null> {
  const rows = await safe(() => run<TrialRow>(sql`SELECT * FROM clinical_trials WHERE upper(nct_id) = upper(${nct}) LIMIT 1`), [] as TrialRow[]);
  return rows[0] ?? null;
}

export interface TrialCondition {
  condition_text: string;
  normalized: string;
  cancer_id: string | null;
  cancer_slug: string | null;
  cancer_name: string | null;
  match_type: string;
  confidence: number | null;
}
export async function trialConditionsFor(trialId: string): Promise<TrialCondition[]> {
  return safe(() => run<TrialCondition>(sql`SELECT tc.condition_text, tc.normalized, tc.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, tc.match_type, tc.confidence FROM trial_conditions tc LEFT JOIN cancers c ON c.id = tc.cancer_id WHERE tc.trial_id = ${trialId} ORDER BY tc.condition_text`), [] as TrialCondition[]);
}

export interface TrialIntervention {
  name: string;
  intervention_type: string | null;
  drug_id: string | null;
  drug_slug: string | null;
  drug_name: string | null;
  match_type: string;
}
export async function trialInterventionsFor(trialId: string): Promise<TrialIntervention[]> {
  return safe(() => run<TrialIntervention>(sql`SELECT ti.name, ti.intervention_type, ti.drug_id, d.slug AS drug_slug, d.name AS drug_name, ti.match_type FROM trial_interventions ti LEFT JOIN drugs d ON d.id = ti.drug_id WHERE ti.trial_id = ${trialId} ORDER BY ti.name`), [] as TrialIntervention[]);
}

export async function trialLocationsByCountry(trialId: string): Promise<Array<{ country: string; n: number; recruiting: number }>> {
  const rows = await safe(() => run<{ country: string; n: string; recruiting: string }>(sql`SELECT coalesce(country, 'Unknown') AS country, count(*) AS n, count(*) FILTER (WHERE status = 'RECRUITING') AS recruiting FROM trial_locations WHERE trial_id = ${trialId} GROUP BY 1 ORDER BY n DESC`), [] as Array<{ country: string; n: string; recruiting: string }>);
  return rows.map((r) => ({ country: r.country, n: Number(r.n), recruiting: Number(r.recruiting) }));
}

export const TRIAL_PAGE_SIZE = 50;

/** Columns the trial tables actually render (the full row carries arms, outcomes, eligibility JSON…). */
export type TrialListRow = Pick<TrialRow, 'id' | 'nct_id' | 'brief_title' | 'acronym' | 'phases' | 'overall_status' | 'enrollment_count' | 'lead_sponsor' | 'lead_sponsor_class' | 'countries' | 'last_update_posted_date' | 'updated_at'>;
const LIST_COLUMNS = sql`t.id, t.nct_id, t.brief_title, t.acronym, t.phases, t.overall_status, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries, t.last_update_posted_date, t.updated_at`;

export async function listTrialRows(f: TrialFilters): Promise<{ rows: TrialListRow[]; total: number }> {
  if (f.cancerIds && f.cancerIds.length === 0) return { rows: [], total: 0 };
  const [total, rows] = await Promise.all([
    safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM clinical_trials t WHERE ${trialWhere(f)}`), [{ n: '0' }]),
    safe(() => run<TrialListRow>(sql`SELECT ${LIST_COLUMNS} FROM clinical_trials t WHERE ${trialWhere(f)} ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${f.pageSize} OFFSET ${(Math.max(1, f.page) - 1) * f.pageSize}`), [] as TrialListRow[]),
  ]);
  return { rows, total: Number(total[0]?.n ?? 0) };
}

const whereDrug = (drugId: string) => sql`EXISTS (SELECT 1 FROM trial_interventions ti WHERE ti.trial_id = t.id AND ti.drug_id = ${drugId})`;

export async function trialsForDrug(drugId: string, p: { page: number; pageSize: number } = { page: 1, pageSize: TRIAL_PAGE_SIZE }): Promise<TrialListRow[]> {
  return safe(() => run<TrialListRow>(sql`SELECT ${LIST_COLUMNS} FROM clinical_trials t WHERE ${whereDrug(drugId)} ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`), [] as TrialListRow[]);
}
export async function trialsForDrugCount(drugId: string): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM clinical_trials t WHERE ${whereDrug(drugId)}`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

/** Aggregate view for the home "Most active clinical research" module. */
export async function mostActiveResearch(limit = 10): Promise<Array<{ slug: string; canonical_name: string; active_trial_count: number; recruiting_trial_count: number; computed_at: Date }>> {
  return safe(
    () =>
      run<{ slug: string; canonical_name: string; active_trial_count: number; recruiting_trial_count: number; computed_at: Date }>(sql`
        SELECT c.slug, c.canonical_name, ec.active_trial_count, ec.recruiting_trial_count, ec.updated_at AS computed_at FROM entity_counters ec JOIN cancers c ON c.id = ec.entity_id
        WHERE ec.entity_type = 'cancer' AND c.status = 'active' AND c.malignant AND ec.active_trial_count > 0 ORDER BY ec.active_trial_count DESC, c.canonical_name LIMIT ${limit}`),
    [],
  );
}

export async function mostCuratedEvidence(limit = 10): Promise<Array<{ slug: string; canonical_name: string; evidence_count: number; gene_count: number; computed_at: Date }>> {
  return safe(
    () =>
      run<{ slug: string; canonical_name: string; evidence_count: number; gene_count: number; computed_at: Date }>(sql`
        SELECT c.slug, c.canonical_name, ec.evidence_count, ec.gene_count, ec.updated_at AS computed_at FROM entity_counters ec JOIN cancers c ON c.id = ec.entity_id
        WHERE ec.entity_type = 'cancer' AND c.status = 'active' AND c.malignant AND ec.evidence_count > 0 ORDER BY ec.evidence_count DESC, c.canonical_name LIMIT ${limit}`),
    [],
  );
}

export async function trialNctForSitemap(offset: number, limit: number): Promise<Array<{ nct_id: string; updated_at: Date }>> {
  return safe(() => run<{ nct_id: string; updated_at: Date }>(sql`SELECT nct_id, updated_at FROM clinical_trials ORDER BY id LIMIT ${limit} OFFSET ${offset}`), []);
}
export async function countTrials(): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM clinical_trials`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}