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

/**
 * "What changed in cancer" (SPEC §42-43, §62): every item is a dated record already in the database
 * (an approval row, a registered study, a ranking row, an ingest run). Nothing is generated; the
 * page only orders and groups existing evidence by recency.
 */

export interface PulseApproval {
  id: number;
  approval_date: string;
  authority: string;
  jurisdiction: string;
  status: string;
  approval_type: string | null;
  indication: string;
  drug_id: string;
  drug_slug: string;
  drug_name: string;
  cancer_id: string | null;
  cancer_slug: string | null;
  cancer_name: string | null;
  tumor_agnostic: boolean;
  source_slug: string;
  provenance_id: number;
}
export async function recentApprovals(days = 90, limit = 40): Promise<PulseApproval[]> {
  return safe(
    () =>
      run<PulseApproval>(sql`
        SELECT a.id, a.approval_date, a.authority, a.jurisdiction, a.status, a.approval_type, a.indication, a.tumor_agnostic, a.provenance_id,
               d.id AS drug_id, d.slug AS drug_slug, d.name AS drug_name, c.id AS cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug
        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
        WHERE a.approval_date ~ '^\\d{4}-\\d{2}-\\d{2}$' AND a.approval_date::date >= current_date - ${days}::int AND a.approval_date::date <= current_date
        ORDER BY a.approval_date DESC, a.id DESC LIMIT ${limit}`),
    [] as PulseApproval[],
  );
}

export interface PulseTrial {
  id: string;
  nct_id: string;
  brief_title: string;
  acronym: string | null;
  phases: string[];
  overall_status: string | null;
  first_posted_date: string | null;
  enrollment_count: number | null;
  lead_sponsor: string | null;
  lead_sponsor_class: string | null;
  countries: string[];
  cancer_slug: string | null;
  cancer_name: string | null;
  updated_at: Date | string;
}
/** Interventional Phase III studies first posted in the window and recruiting now, with their first mapped cancer. */
export async function newPhase3Recruiting(days = 30, limit = 25): Promise<PulseTrial[]> {
  return safe(
    () =>
      run<PulseTrial>(sql`
        SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.phases, t.overall_status, t.first_posted_date, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries, t.updated_at,
               m.slug AS cancer_slug, m.canonical_name AS cancer_name
        FROM clinical_trials t
        LEFT JOIN LATERAL (
          SELECT c.slug, c.canonical_name FROM trial_conditions tc JOIN cancers c ON c.id = tc.cancer_id
          WHERE tc.trial_id = t.id ORDER BY (tc.match_type = 'PROBABILISTIC'), c.depth, c.canonical_name LIMIT 1) m ON true
        WHERE t.study_type = 'INTERVENTIONAL' AND 'PHASE3' = ANY(t.phases) AND t.overall_status = 'RECRUITING'
          AND t.first_posted_date ~ '^\\d{4}-\\d{2}-\\d{2}$' AND t.first_posted_date::date >= current_date - ${days}::int
        ORDER BY t.first_posted_date DESC, t.nct_id LIMIT ${limit}`),
    [] as PulseTrial[],
  );
}

export interface PhasePulse {
  phase: string;
  current: number;
  previous: number;
}
/** New oncology studies per phase: last `days` vs the preceding `days` (from trial_pulse, cancer_id NULL = all studies). */
export async function trialPulseByPhase(days = 30): Promise<{ rows: PhasePulse[]; asOf: Date | string | null }> {
  const rows = await safe(
    () =>
      run<{ phase: string; current: string; previous: string; as_of: Date | string | null }>(sql`
        SELECT phase,
               sum(new_trials) FILTER (WHERE day > current_date - ${days}::int) AS current,
               sum(new_trials) FILTER (WHERE day <= current_date - ${days}::int AND day > current_date - ${days * 2}::int) AS previous,
               max(updated_at) AS as_of
        FROM trial_pulse WHERE cancer_id IS NULL AND day > current_date - ${days * 2}::int
        GROUP BY phase ORDER BY CASE phase WHEN 'ALL' THEN 0 WHEN 'EARLY_PHASE1' THEN 1 WHEN 'PHASE1' THEN 2 WHEN 'PHASE2' THEN 3 WHEN 'PHASE3' THEN 4 WHEN 'PHASE4' THEN 5 ELSE 9 END`),
    [] as Array<{ phase: string; current: string; previous: string; as_of: Date | string | null }>,
  );
  return { rows: rows.map((r) => ({ phase: r.phase, current: Number(r.current ?? 0), previous: Number(r.previous ?? 0) })), asOf: rows[0]?.as_of ?? null };
}

export interface CancerPulse {
  id: string;
  slug: string;
  canonical_name: string;
  current: number;
  previous: number;
}
/** Top-level cancers with the most newly registered studies in the window (any phase), with the previous window for comparison. */
export async function newTrialsByCancer(days = 30, limit = 10): Promise<CancerPulse[]> {
  return safe(
    () =>
      run<CancerPulse>(sql`
        SELECT c.id, c.slug, c.canonical_name,
               coalesce(sum(p.new_trials) FILTER (WHERE p.day > current_date - ${days}::int), 0)::int AS current,
               coalesce(sum(p.new_trials) FILTER (WHERE p.day <= current_date - ${days}::int), 0)::int AS previous
        FROM trial_pulse p JOIN cancers c ON c.id = p.cancer_id
        WHERE p.phase = 'ALL' AND c.top_level AND c.status = 'active' AND p.day > current_date - ${days * 2}::int
        GROUP BY c.id, c.slug, c.canonical_name ORDER BY current DESC, c.canonical_name LIMIT ${limit}`),
    [] as CancerPulse[],
  );
}

export interface RankingMove {
  metric_slug: string;
  metric_name: string;
  unit: string;
  scope_key: string;
  cancer_id: string;
  slug: string;
  canonical_name: string;
  rank: number;
  previous_rank: number;
  value: number;
  eligible_entities: number;
  generated_at: Date | string;
}
/** Largest rank changes between the current snapshot and the previous one for the same metric and scope (§131). */
export async function rankingMoves(minDelta = 3, limit = 20): Promise<RankingMove[]> {
  return safe(
    () =>
      run<RankingMove>(sql`
        SELECT r.metric_slug, m.name AS metric_name, r.unit, r.scope_key, r.cancer_id, c.slug, c.canonical_name, r.rank, r.previous_rank, r.value, r.eligible_entities, s.generated_at
        FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id JOIN cancers c ON c.id = r.cancer_id JOIN metric_definitions m ON m.slug = r.metric_slug
        WHERE s.is_current AND r.previous_rank IS NOT NULL AND abs(r.previous_rank - r.rank) >= ${minDelta} AND s.entity_level = 'top'
        ORDER BY abs(r.previous_rank - r.rank) DESC, s.generated_at DESC, r.metric_slug, r.rank LIMIT ${limit}`),
    [] as RankingMove[],
  );
}

export interface PulsePublication {
  id: string;
  pmid: string | null;
  doi: string | null;
  title: string;
  journal: string | null;
  pub_date: string | null;
  publication_types: string[];
  retracted: boolean;
}
/** Most recently published records indexed (PubMed), favouring trials, reviews and meta-analyses. */
export async function recentPublications(days = 60, limit = 15): Promise<PulsePublication[]> {
  return safe(
    () =>
      run<PulsePublication>(sql`
        SELECT id, pmid, doi, title, journal, pub_date, publication_types, retracted FROM publications
        WHERE pub_date ~ '^\\d{4}-\\d{2}-\\d{2}$' AND pub_date::date >= current_date - ${days}::int AND pub_date::date <= current_date AND NOT retracted
        ORDER BY (publication_types && ARRAY['Randomized Controlled Trial','Clinical Trial, Phase III','Meta-Analysis','Systematic Review','Clinical Trial']::text[]) DESC, pub_date DESC LIMIT ${limit}`),
    [] as PulsePublication[],
  );
}

export interface IngestRow {
  id: string;
  connector_id: string;
  source_name: string | null;
  mode: string;
  status: string;
  started_at: Date | string;
  finished_at: Date | string | null;
  duration_ms: number | null;
  records_fetched: number;
  records_created: number;
  records_updated: number;
  records_unchanged: number;
  records_rejected: number;
  dataset_version: string | null;
  anomaly: string | null;
}
/** Ingest runs of the last `days` days (all statuses), most recent first (§62 data update log). */
export async function recentIngests(days = 30, limit = 200): Promise<IngestRow[]> {
  return safe(
    () =>
      run<IngestRow>(sql`
        SELECT r.id, r.connector_id, s.name AS source_name, r.mode, r.status, r.started_at, r.finished_at, r.duration_ms, r.records_fetched, r.records_created, r.records_updated, r.records_unchanged, r.records_rejected, r.dataset_version, r.anomaly
        FROM ingest_runs r LEFT JOIN sources s ON s.slug = r.connector_id
        WHERE r.started_at >= now() - (${days}::text || ' days')::interval AND r.mode NOT IN ('dry_run','probe')
        ORDER BY r.started_at DESC LIMIT ${limit}`),
    [] as IngestRow[],
  );
}

export interface ConnectorState {
  connector_id: string;
  source_name: string | null;
  category: string | null;
  status: string | null;
  health: string;
  last_success_at: Date | string | null;
  last_attempt_at: Date | string | null;
  paused: boolean;
  schedule: string | null;
  last_dataset_version: string | null;
  record_count: number;
}
/** One line per connector: health, last success, schedule, latest dataset version, source records held. */
export async function connectorStates(): Promise<ConnectorState[]> {
  return safe(
    () =>
      run<ConnectorState>(sql`
        SELECT s.slug AS connector_id, s.name AS source_name, s.category, s.status, coalesce(cc.health, 'unknown') AS health, cc.last_success_at, cc.last_attempt_at, coalesce(cc.paused, false) AS paused,
               s.manifest->>'schedule' AS schedule,
               (SELECT r.dataset_version FROM ingest_runs r WHERE r.connector_id = s.slug AND r.status IN ('succeeded','partial') ORDER BY r.started_at DESC LIMIT 1) AS last_dataset_version,
               (SELECT count(*) FROM source_records sr WHERE sr.source_id = s.id)::int AS record_count
        FROM sources s LEFT JOIN connector_cursors cc ON cc.connector_id = s.slug
        ORDER BY s.status = 'active' DESC, s.tier, s.name`),
    [] as ConnectorState[],
  );
}

/** Recent change events other than bulk creations (approval_added, alias_added, deprecated, merged…). */
export interface PulseEvent {
  id: number;
  entity_type: string;
  entity_id: string;
  kind: string;
  summary: string;
  created_at: Date | string;
  entity_name: string | null;
  entity_href: string | null;
}
export async function recentEvents(days = 30, limit = 40): Promise<PulseEvent[]> {
  return safe(
    () =>
      run<PulseEvent>(sql`
        SELECT e.id, e.entity_type, e.entity_id, e.kind, e.summary, e.created_at,
          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 '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 entity_name,
          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 '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 entity_href
        FROM change_events e
        WHERE e.created_at >= now() - (${days}::text || ' days')::interval AND e.kind <> 'created'
        ORDER BY e.created_at DESC, e.id DESC LIMIT ${limit}`),
    [] as PulseEvent[],
  );
}