import 'server-only';
import { run, sql, safe } from '@/lib/db';
import type { SQL } from 'drizzle-orm';
import type { ApprovalRow } from '@/lib/queries/drugs';

export const APPROVALS_PAGE_SIZE = 50;
export const PIPELINE_PAGE_SIZE = 50;
/** Funnel order (least → most advanced); `withdrawn` is shown apart from the funnel. */
export const PIPELINE_FUNNEL = ['phase1', 'phase2', 'phase3', 'phase4', 'approved'] as const;
export const PIPELINE_STAGES = ['phase_not_stated', ...PIPELINE_FUNNEL, 'withdrawn'] as const;
export type PipelineStage = (typeof PIPELINE_STAGES)[number];
export const APPROVED_STATUSES = ['approved', 'accelerated', 'conditional'] as const;
const MAX_DEPTH = 12;

/** Feed row: an approval record plus the verbatim upstream status when the source has one (DPD). */
export interface FeedApprovalRow extends ApprovalRow {
  source_status: string | null;
}

export interface ApprovalFilters {
  authority: string;
  jurisdiction: string;
  cancer: string; // slug
  status: string;
  year: string;
  q: string;
}

const FEED_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`;

function feedWhere(f: ApprovalFilters): SQL {
  const parts: SQL[] = [sql`true`];
  if (f.authority) parts.push(sql`lower(a.authority) = lower(${f.authority})`);
  if (f.jurisdiction) parts.push(sql`upper(a.jurisdiction) = upper(${f.jurisdiction})`);
  if (f.status) parts.push(sql`a.status = ${f.status}`);
  if (/^\d{4}$/.test(f.year)) parts.push(sql`a.approval_date >= ${`${f.year}-01-01`} AND a.approval_date <= ${`${f.year}-12-31`}`);
  if (f.cancer) {
    parts.push(sql`a.cancer_id IN (
      WITH RECURSIVE dsc AS (
        SELECT id, 0 AS depth FROM cancers WHERE slug = ${f.cancer}
        UNION
        SELECT h.child_id, dsc.depth + 1 FROM dsc JOIN cancer_hierarchy h ON h.parent_id = dsc.id WHERE dsc.depth < ${MAX_DEPTH}
      ) SELECT id FROM dsc)`);
  }
  if (f.q) {
    const like = `%${f.q}%`;
    parts.push(sql`(d.name ILIKE ${like} OR a.indication ILIKE ${like} OR EXISTS (SELECT 1 FROM drug_aliases al WHERE al.drug_id = a.drug_id AND al.alias ILIKE ${like}))`);
  }
  return sql.join(parts, sql` AND `);
}

export async function countApprovals(f: ApprovalFilters): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id WHERE ${feedWhere(f)}`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

/** Feed page, latest approval date first (undated records last). */
export async function listApprovals(f: ApprovalFilters, page: number, pageSize = APPROVALS_PAGE_SIZE): Promise<FeedApprovalRow[]> {
  return safe(() => run<FeedApprovalRow>(sql`${FEED_SELECT} WHERE ${feedWhere(f)} ORDER BY a.approval_date DESC NULLS LAST, a.id DESC LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}`), [] as FeedApprovalRow[]);
}

export interface AuthorityStat {
  authority: string;
  jurisdiction: string;
  n: number;
  distinct_drugs: number;
  approved_like: number;
  withdrawn: number;
  last_12m: number;
  latest_date: string | null;
  updated_at: Date | null;
}

export async function approvalStats(): Promise<{ byAuthority: AuthorityStat[]; total: number; distinctDrugs: number; withCancer: number; last12m: number }> {
  const since = new Date(Date.now() - 365 * 86_400_000).toISOString().slice(0, 10);
  const byAuthority = await safe(
    () =>
      run<AuthorityStat>(sql`
        SELECT a.authority, a.jurisdiction, count(*)::int AS n, count(DISTINCT a.drug_id)::int AS distinct_drugs,
          count(*) FILTER (WHERE a.status IN ('approved','accelerated','conditional'))::int AS approved_like,
          count(*) FILTER (WHERE a.status = 'withdrawn')::int AS withdrawn,
          count(*) FILTER (WHERE a.approval_date >= ${since} AND a.approval_date <= to_char(now(), 'YYYY-MM-DD'))::int AS last_12m,
          max(a.approval_date) FILTER (WHERE a.approval_date <= to_char(now(), 'YYYY-MM-DD')) AS latest_date, max(a.updated_at) AS updated_at
        FROM drug_approvals a GROUP BY a.authority, a.jurisdiction ORDER BY n DESC`),
    [] as AuthorityStat[],
  );
  const totals = await safe(() => run<{ total: string; drugs: string; with_cancer: string }>(sql`SELECT count(*) AS total, count(DISTINCT drug_id) AS drugs, count(cancer_id) AS with_cancer FROM drug_approvals`), [{ total: '0', drugs: '0', with_cancer: '0' }]);
  return { byAuthority, total: Number(totals[0]?.total ?? 0), distinctDrugs: Number(totals[0]?.drugs ?? 0), withCancer: Number(totals[0]?.with_cancer ?? 0), last12m: byAuthority.reduce((s, a) => s + a.last_12m, 0) };
}

export async function approvalFacets(): Promise<{ authorities: Array<{ authority: string; jurisdiction: string; n: number }>; statuses: Array<{ status: string; n: number }>; years: Array<{ year: string; n: number }> }> {
  const [authorities, statuses, years] = await Promise.all([
    safe(() => run<{ authority: string; jurisdiction: string; n: number }>(sql`SELECT authority, jurisdiction, count(*)::int AS n FROM drug_approvals GROUP BY 1, 2 ORDER BY n DESC`), []),
    safe(() => run<{ status: string; n: number }>(sql`SELECT status, count(*)::int AS n FROM drug_approvals GROUP BY 1 ORDER BY n DESC`), []),
    safe(() => run<{ year: string; n: number }>(sql`SELECT left(approval_date, 4) AS year, count(*)::int AS n FROM drug_approvals WHERE approval_date ~ '^\\d{4}' GROUP BY 1 ORDER BY 1 DESC`), []),
  ]);
  return { authorities, statuses, years };
}

/** Last dated approvals across authorities (no future-dated records). */
export async function recentApprovals(limit = 8): Promise<FeedApprovalRow[]> {
  return safe(() => run<FeedApprovalRow>(sql`${FEED_SELECT} WHERE a.approval_date IS NOT NULL AND a.approval_date <= to_char(now(), 'YYYY-MM-DD') ORDER BY a.approval_date DESC, a.id DESC LIMIT ${limit}`), [] as FeedApprovalRow[]);
}

/* ------------------------------------------------------------------------------------------------
 * Pipeline (derived, drug_pipeline)
 * ---------------------------------------------------------------------------------------------- */

export interface PipelineRow {
  id: number;
  drug_id: string;
  drug_slug: string;
  drug_name: string;
  drug_kind: string | null;
  cancer_id: string | null;
  cancer_slug: string | null;
  cancer_name: string | null;
  stage: PipelineStage;
  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;
}

const PIPELINE_SELECT = sql`
  SELECT p.*, p.updated_at AS computed_at, d.slug AS drug_slug, d.name AS drug_name, d.kind AS drug_kind, c.slug AS cancer_slug, c.canonical_name AS cancer_name
  FROM drug_pipeline p JOIN drugs d ON d.id = p.drug_id LEFT JOIN cancers c ON c.id = p.cancer_id`;
const STAGE_RANK = sql`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`;
const scopeWhere = (cancerId: string | null) => (cancerId ? sql`p.cancer_id = ${cancerId}` : sql`p.cancer_id IS NULL`);

export async function topLevelCancerOptions(): Promise<Array<{ id: string; slug: string; canonical_name: string; drugs: number }>> {
  return safe(
    () =>
      run<{ id: string; slug: string; canonical_name: string; drugs: number }>(sql`
        SELECT c.id, c.slug, c.canonical_name, (SELECT count(*) FROM drug_pipeline p WHERE p.cancer_id = c.id)::int AS drugs
        FROM cancers c WHERE c.top_level AND c.status = 'active' ORDER BY c.canonical_name`),
    [],
  );
}

export async function pipelineSummary(cancerId: string | null): Promise<{ stages: Array<{ stage: PipelineStage; drugs: number; active_trials: number }>; drugs: number; formulaVersion: string | null; computedAt: Date | null }> {
  const rows = await safe(() => run<{ stage: PipelineStage; drugs: number; active_trials: number }>(sql`SELECT p.stage, count(*)::int AS drugs, coalesce(sum(p.active_trials), 0)::int AS active_trials FROM drug_pipeline p WHERE ${scopeWhere(cancerId)} GROUP BY p.stage`), []);
  const meta = await safe(() => run<{ formula_version: string | null; computed_at: Date | null }>(sql`SELECT max(formula_version) AS formula_version, max(updated_at) AS computed_at FROM drug_pipeline`), [{ formula_version: null, computed_at: null }]);
  const by = new Map(rows.map((r) => [r.stage, r]));
  return {
    stages: PIPELINE_STAGES.map((stage) => ({ stage, drugs: by.get(stage)?.drugs ?? 0, active_trials: by.get(stage)?.active_trials ?? 0 })),
    drugs: rows.reduce((s, r) => s + r.drugs, 0),
    formulaVersion: meta[0]?.formula_version ?? null,
    computedAt: meta[0]?.computed_at ?? null,
  };
}

/** Up to `perStage` representative drugs per stage (most active trials first). */
export async function pipelineTopDrugsPerStage(cancerId: string | null, perStage = 5): Promise<Map<PipelineStage, PipelineRow[]>> {
  const rows = await safe(
    () =>
      run<PipelineRow>(sql`
        SELECT * FROM (SELECT p.*, p.updated_at AS computed_at, d.slug AS drug_slug, d.name AS drug_name, d.kind AS drug_kind, c.slug AS cancer_slug, c.canonical_name AS cancer_name,
          row_number() OVER (PARTITION BY p.stage ORDER BY p.active_trials DESC, p.total_trials DESC, d.name) AS rn
          FROM drug_pipeline p JOIN drugs d ON d.id = p.drug_id LEFT JOIN cancers c ON c.id = p.cancer_id WHERE ${scopeWhere(cancerId)}) x
        WHERE rn <= ${perStage} ORDER BY stage, rn`),
    [] as PipelineRow[],
  );
  const out = new Map<PipelineStage, PipelineRow[]>();
  for (const r of rows) out.set(r.stage, [...(out.get(r.stage) ?? []), r]);
  return out;
}

export async function countPipelineRows(cancerId: string | null, stage: string): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM drug_pipeline p WHERE ${scopeWhere(cancerId)} ${stage ? sql`AND p.stage = ${stage}` : sql``}`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

export async function listPipelineRows(cancerId: string | null, stage: string, page: number, pageSize = PIPELINE_PAGE_SIZE): Promise<PipelineRow[]> {
  return safe(
    () => run<PipelineRow>(sql`${PIPELINE_SELECT} WHERE ${scopeWhere(cancerId)} ${stage ? sql`AND p.stage = ${stage}` : sql``} ORDER BY ${STAGE_RANK} DESC, p.active_trials DESC, p.total_trials DESC, d.name LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}`),
    [] as PipelineRow[],
  );
}

/** Sources feeding the derived pipeline (registry + regulatory sources present in drug_approvals). */
export async function pipelineSourceSlugs(): Promise<string[]> {
  const rows = await safe(() => run<{ slug: string }>(sql`SELECT DISTINCT s.slug FROM sources s WHERE s.slug = 'clinicaltrials' OR s.id IN (SELECT DISTINCT source_id FROM drug_approvals) ORDER BY 1`), [] as Array<{ slug: string }>);
  return rows.map((r) => r.slug);
}