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

export interface DrugRow {
  id: string;
  slug: string;
  name: string;
  kind: string | null;
  ncit_code: string | null;
  chembl_id: string | null;
  civic_therapy_id: number | null;
  drugbank_id: string | null;
  pubchem_cid: string | null;
  unii: string | null;
  mechanism: string | null;
  target_gene_ids: string[];
  development_status: string | null;
  description: string | null;
  updated_at: Date;
  approval_count?: number;
  evidence_count?: number;
  trial_count?: number;
  aliases?: string[] | null;
}

export async function getDrugBySlug(slug: string): Promise<DrugRow | null> {
  const rows = await safe(
    () =>
      run<DrugRow>(sql`
        SELECT d.*, (SELECT count(*) FROM drug_approvals a WHERE a.drug_id = d.id)::int AS approval_count,
               (SELECT count(*) FROM civic_evidence_items e WHERE d.id = ANY(e.therapy_ids))::int AS evidence_count,
               (SELECT count(DISTINCT ti.trial_id) FROM trial_interventions ti WHERE ti.drug_id = d.id)::int AS trial_count,
               (SELECT array_agg(DISTINCT a.alias ORDER BY a.alias) FROM drug_aliases a WHERE a.drug_id = d.id) AS aliases
        FROM drugs d WHERE d.slug = ${slug} LIMIT 1`),
    [] as DrugRow[],
  );
  return rows[0] ?? null;
}

export async function listDrugs(opts: { q: string; kind: string; page: number; pageSize: number }): Promise<{ rows: DrugRow[]; total: number; kinds: Array<{ kind: string; n: number }> }> {
  const where = sql`${opts.kind ? sql`d.kind = ${opts.kind}` : sql`true`} AND ${opts.q ? sql`(d.name ILIKE ${'%' + opts.q + '%'} OR EXISTS (SELECT 1 FROM drug_aliases a WHERE a.drug_id = d.id AND a.alias ILIKE ${'%' + opts.q + '%'}))` : sql`true`}`;
  const total = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM drugs d WHERE ${where}`), [{ n: '0' }]);
  const rows = await safe(
    () =>
      run<DrugRow>(sql`
        SELECT d.*, (SELECT count(*) FROM drug_approvals a WHERE a.drug_id = d.id)::int AS approval_count,
               (SELECT count(*) FROM civic_evidence_items e WHERE d.id = ANY(e.therapy_ids))::int AS evidence_count,
               (SELECT count(DISTINCT ti.trial_id) FROM trial_interventions ti WHERE ti.drug_id = d.id)::int AS trial_count
        FROM drugs d WHERE ${where} ORDER BY approval_count DESC, evidence_count DESC, d.name LIMIT ${opts.pageSize} OFFSET ${(opts.page - 1) * opts.pageSize}`),
    [] as DrugRow[],
  );
  const kinds = await safe(() => run<{ kind: string; n: string }>(sql`SELECT kind, count(*) AS n FROM drugs WHERE kind IS NOT NULL GROUP BY kind ORDER BY n DESC`), [] as Array<{ kind: string; n: string }>);
  return { rows, total: Number(total[0]?.n ?? 0), kinds: kinds.map((k) => ({ kind: k.kind, n: Number(k.n) })) };
}

export interface ApprovalRow {
  id: number;
  drug_id: string;
  drug_slug: string;
  drug_name: string;
  cancer_id: string | null;
  cancer_slug: string | null;
  cancer_name: string | null;
  biomarker_ids: string[];
  tumor_agnostic: boolean;
  jurisdiction: string;
  authority: string;
  indication: string;
  line_of_therapy: string | null;
  disease_stage: string | null;
  approval_type: string | null;
  accelerated: boolean | null;
  conditional: boolean | null;
  approval_date: string | null;
  withdrawal_date: string | null;
  status: string;
  application_number: string | null;
  source_slug: string;
  source_name: string;
  provenance_id: number;
  updated_at: Date;
  /** Verbatim upstream status when the source publishes one (Health Canada DPD: "Cancelled Post Market", "Dormant"…). */
  source_status?: string | null;
}

const APPROVAL_SELECT = sql`
  SELECT a.*, a.raw->>'dpdStatus' AS source_status, d.slug AS drug_slug, d.name AS drug_name, c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, s.name AS source_name
  FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN cancers c ON c.id = a.cancer_id JOIN sources s ON s.id = a.source_id`;

export async function approvalsForDrug(drugId: string): Promise<ApprovalRow[]> {
  return safe(() => run<ApprovalRow>(sql`${APPROVAL_SELECT} WHERE a.drug_id = ${drugId} ORDER BY a.jurisdiction, a.approval_date DESC NULLS LAST`), [] as ApprovalRow[]);
}

export async function approvalsForCancer(cancerIds: string[]): Promise<ApprovalRow[]> {
  if (cancerIds.length === 0) return [];
  return safe(() => run<ApprovalRow>(sql`${APPROVAL_SELECT} WHERE a.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}) OR a.tumor_agnostic ORDER BY d.name, a.jurisdiction, a.approval_date DESC NULLS LAST LIMIT 500`), [] as ApprovalRow[]);
}

/* ------------------------------------------------------------------------------------------------
 * Identifiers (drug_codes) and development pipeline (drug_pipeline) for the drug page
 * ---------------------------------------------------------------------------------------------- */

export interface DrugCodeRow {
  id: number;
  system: string;
  code: string;
  label: string | null;
  match_type: string;
  source_slug: string | null;
  source_name: string | null;
}

/** Display order of identifier systems on the drug page. */
export const CODE_SYSTEM_ORDER = ['atc', 'din', 'hc_drug_code', 'unii', 'rxcui', 'ncit', 'chembl', 'drugbank', 'pubchem_cid', 'civic_therapy', 'ema_product', 'mhra', 'tga'] as const;

export async function codesForDrug(drugId: string): Promise<DrugCodeRow[]> {
  const order = sql.raw(`CASE k.system ${CODE_SYSTEM_ORDER.map((s, i) => `WHEN '${s}' THEN ${i}`).join(' ')} ELSE 99 END`);
  return safe(
    () =>
      run<DrugCodeRow>(sql`
        SELECT k.id, k.system, k.code, k.label, k.match_type, s.slug AS source_slug, s.name AS source_name
        FROM drug_codes k LEFT JOIN sources s ON s.id = k.source_id WHERE k.drug_id = ${drugId} ORDER BY ${order}, k.code`),
    [] as DrugCodeRow[],
  );
}

export interface DrugPipelineRow {
  id: number;
  cancer_id: string | null;
  cancer_slug: string | null;
  cancer_name: string | null;
  stage: string;
  max_phase: string | null;
  active_trials: number;
  recruiting_trials: number;
  phase3_trials: number;
  total_trials: number;
  approvals: number;
  jurisdictions: string[];
  first_approval_date: string | null;
  latest_approval_date: string | null;
  first_trial_date: string | null;
  formula_version: string;
  computed_at: Date;
}

/** Across-all-cancers row first, then one row per top-level cancer (most advanced stage first). */
export async function pipelineForDrug(drugId: string): Promise<DrugPipelineRow[]> {
  return safe(
    () =>
      run<DrugPipelineRow>(sql`
        SELECT p.id, p.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, p.stage, p.max_phase, p.active_trials, p.recruiting_trials, p.phase3_trials, p.total_trials, p.approvals, p.jurisdictions,
          p.first_approval_date, p.latest_approval_date, p.first_trial_date, p.formula_version, p.updated_at AS computed_at
        FROM drug_pipeline p LEFT JOIN cancers c ON c.id = p.cancer_id WHERE p.drug_id = ${drugId}
        ORDER BY (p.cancer_id IS NOT NULL), CASE p.stage WHEN 'approved' THEN 6 WHEN 'phase4' THEN 5 WHEN 'phase3' THEN 4 WHEN 'phase2' THEN 3 WHEN 'phase1' THEN 2 WHEN 'phase_not_stated' THEN 1 ELSE 0 END DESC, p.active_trials DESC, c.canonical_name`),
    [] as DrugPipelineRow[],
  );
}

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