import 'server-only';
import { run, sql, safe } from '@/lib/db';
import { STOP_REASON_CATEGORIES, classifyStopReason, type StopReasonCategory } from '@cancerindex/ranking';

/** One `trial_intelligence` row joined with its cancer (snake_case as returned by the driver). */
export interface TrialIntelRow {
  cancer_id: string;
  cancer_slug: string;
  cancer_name: string;
  top_level: boolean;
  entity_level: 'top' | 'all';
  total_trials: number;
  active_trials: number;
  recruiting_trials: number;
  phase1_active: number;
  phase2_active: number;
  phase3_active: number;
  phase3_recruiting: number;
  phase4_active: number;
  completed_trials: number;
  terminated_trials: number;
  withdrawn_trials: number;
  suspended_trials: number;
  with_results: number;
  new_trials_12m: number;
  new_trials_prior_12m: number;
  trial_growth_yoy: number | null;
  avg_enrollment: number | null;
  median_enrollment: number | null;
  total_enrollment_active: number | null;
  distinct_sponsors: number;
  industry_share: number | null;
  sponsor_hhi: number | null;
  top_sponsor: string | null;
  top_sponsor_share: number | null;
  distinct_countries: number;
  us_share: number | null;
  top_country: string | null;
  top_country_share: number | null;
  country_hhi: number | null;
  termination_share: number | null;
  why_stopped_breakdown: Record<string, number>;
  trials_per_1000_deaths: number | null;
  trials_per_100k_cases: number | null;
  burden_geography: string | null;
  burden_year: number | null;
  burden_source_id: string | null;
  burden_source_slug: string | null;
  formula_version: string;
  inputs: Record<string, unknown>;
  computed_at: Date | string;
}

const COLS = sql`ti.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, c.top_level, ti.entity_level,
  ti.total_trials, ti.active_trials, ti.recruiting_trials, ti.phase1_active, ti.phase2_active, ti.phase3_active, ti.phase3_recruiting, ti.phase4_active,
  ti.completed_trials, ti.terminated_trials, ti.withdrawn_trials, ti.suspended_trials, ti.with_results,
  ti.new_trials_12m, ti.new_trials_prior_12m, ti.trial_growth_yoy, ti.avg_enrollment, ti.median_enrollment, ti.total_enrollment_active,
  ti.distinct_sponsors, ti.industry_share, ti.sponsor_hhi, ti.top_sponsor, ti.top_sponsor_share,
  ti.distinct_countries, ti.us_share, ti.top_country, ti.top_country_share, ti.country_hhi,
  ti.termination_share, ti.why_stopped_breakdown, ti.trials_per_1000_deaths, ti.trials_per_100k_cases, ti.burden_geography, ti.burden_year, ti.burden_source_id,
  s.slug AS burden_source_slug, ti.formula_version, ti.inputs, ti.updated_at AS computed_at`;
const FROM = sql`FROM trial_intelligence ti JOIN cancers c ON c.id = ti.cancer_id LEFT JOIN sources s ON s.id = ti.burden_source_id`;

/** Rows for one entity level, most active first (`limit` caps the "all" level to the most active entities). */
export async function listTrialIntelligence(level: 'top' | 'all', limit = 10_000): Promise<TrialIntelRow[]> {
  return safe(() => run<TrialIntelRow>(sql`SELECT ${COLS} ${FROM} WHERE ti.entity_level = ${level} AND c.status = 'active' ORDER BY ti.active_trials DESC, c.canonical_name LIMIT ${limit}`), [] as TrialIntelRow[]);
}

/** Both levels for one cancer (a top-level cancer has two rows with identical figures). */
export async function trialIntelligenceFor(cancerId: string): Promise<{ all: TrialIntelRow | null; top: TrialIntelRow | null; primary: TrialIntelRow | null }> {
  const rows = await safe(() => run<TrialIntelRow>(sql`SELECT ${COLS} ${FROM} WHERE ti.cancer_id = ${cancerId}`), [] as TrialIntelRow[]);
  const all = rows.find((r) => r.entity_level === 'all') ?? null;
  const top = rows.find((r) => r.entity_level === 'top') ?? null;
  return { all, top, primary: all ?? top };
}

/** Home module: top-level cancers with the most recruiting Phase III studies. */
export async function topPhase3Recruiting(limit = 8): Promise<TrialIntelRow[]> {
  return safe(() => run<TrialIntelRow>(sql`SELECT ${COLS} ${FROM} WHERE ti.entity_level = 'top' AND c.status = 'active' AND ti.phase3_recruiting > 0 ORDER BY ti.phase3_recruiting DESC, ti.recruiting_trials DESC, c.canonical_name LIMIT ${limit}`), [] as TrialIntelRow[]);
}

export const STOPPED_STATUSES = ['TERMINATED', 'WITHDRAWN', 'SUSPENDED'] as const;

export interface TerminatedFilters {
  cancerIds: string[] | null;
  reason: StopReasonCategory | '';
  status: string;
  since: number | null;
  page: number;
  pageSize: number;
}

export interface TerminatedRow {
  id: string;
  nct_id: string;
  brief_title: string;
  acronym: string | null;
  study_type: string | null;
  phases: string[];
  overall_status: string | null;
  why_stopped: string | null;
  first_posted_date: string | null;
  last_update_posted_date: string | null;
  enrollment_count: number | null;
  lead_sponsor: string | null;
  lead_sponsor_class: string | null;
  updated_at: Date | string;
  reason_category: StopReasonCategory;
  reason_matches: string[];
}

function stoppedWhere(f: TerminatedFilters) {
  const parts = [sql`t.overall_status IN ('TERMINATED','WITHDRAWN','SUSPENDED')`];
  if (f.status && (STOPPED_STATUSES as readonly string[]).includes(f.status)) parts.push(sql`t.overall_status = ${f.status}`);
  if (f.since) parts.push(sql`t.first_posted_date >= ${`${f.since}-01-01`}`);
  if (f.cancerIds) parts.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id = ANY(${sql.param(f.cancerIds)}::text[]))`);
  return sql.join(parts, sql` AND `);
}

/**
 * Terminated / withdrawn / suspended studies with their registrant-reported reason classified by
 * keyword rules. The category is not stored, so a light pass classifies the whole filtered set (id +
 * text) to build the breakdown and apply the reason filter; full columns are fetched for the page only.
 */
export async function listTerminated(f: TerminatedFilters): Promise<{ rows: TerminatedRow[]; total: number; stopped: number; breakdown: Record<StopReasonCategory, number> }> {
  const breakdown = Object.fromEntries(STOP_REASON_CATEGORIES.map((c) => [c, 0])) as Record<StopReasonCategory, number>;
  if (f.cancerIds && f.cancerIds.length === 0) return { rows: [], total: 0, stopped: 0, breakdown };
  const light = await safe(() => run<{ id: string; why_stopped: string | null }>(sql`SELECT t.id, t.why_stopped FROM clinical_trials t WHERE ${stoppedWhere(f)} ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id`), [] as Array<{ id: string; why_stopped: string | null }>);
  const classified = light.map((r) => {
    const c = classifyStopReason(r.why_stopped);
    breakdown[c.category] += 1;
    return { id: r.id, category: c.category, matched: c.matched };
  });
  const filtered = f.reason ? classified.filter((r) => r.category === f.reason) : classified;
  const offset = (Math.max(1, f.page) - 1) * f.pageSize;
  const page = filtered.slice(offset, offset + f.pageSize);
  if (page.length === 0) return { rows: [], total: filtered.length, stopped: light.length, breakdown };
  const full = await safe(
    () =>
      run<Omit<TerminatedRow, 'reason_category' | 'reason_matches'>>(sql`
        SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.study_type, t.phases, t.overall_status, t.why_stopped, t.first_posted_date, t.last_update_posted_date, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.updated_at
        FROM clinical_trials t WHERE t.id = ANY(${sql.param(page.map((p) => p.id))}::text[])`),
    [] as Array<Omit<TerminatedRow, 'reason_category' | 'reason_matches'>>,
  );
  const order = new Map(page.map((p, i) => [p.id, i]));
  const byId = new Map(page.map((p) => [p.id, p]));
  const rows = full
    .sort((a, b) => (order.get(a.id) ?? 0) - (order.get(b.id) ?? 0))
    .map((r) => {
      const c = byId.get(r.id)!;
      return { ...r, reason_category: c.category, reason_matches: c.matched } satisfies TerminatedRow;
    });
  return { rows, total: filtered.length, stopped: light.length, breakdown };
}

/** Distinct first-posted years among stopped studies (for the year filter). */
export async function stoppedYears(): Promise<number[]> {
  const rows = await safe(() => run<{ y: number }>(sql`SELECT DISTINCT left(first_posted_date, 4)::int AS y FROM clinical_trials WHERE overall_status IN ('TERMINATED','WITHDRAWN','SUSPENDED') AND first_posted_date ~ '^\\d{4}' ORDER BY 1 DESC`), [] as Array<{ y: number }>);
  return rows.map((r) => Number(r.y)).filter((y) => Number.isFinite(y));
}