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

/**
 * "Year in cancer" (SPEC §112): everything dated within one calendar year, from records already in
 * the index — approvals, registered studies, literature counts per entity, epidemiology observations
 * published for that year. No synthesis; each block names its source and rule.
 */

export const YEAR_MIN = 1999;

export interface YearApprovalCounts {
  authority: string;
  jurisdiction: string;
  n: number;
  drugs: number;
  with_cancer: number;
}
export async function yearApprovalCounts(year: number): Promise<YearApprovalCounts[]> {
  return safe(
    () =>
      run<YearApprovalCounts>(sql`
        SELECT authority, jurisdiction, count(*)::int AS n, count(DISTINCT drug_id)::int AS drugs, count(*) FILTER (WHERE cancer_id IS NOT NULL)::int AS with_cancer
        FROM drug_approvals WHERE approval_date LIKE ${`${year}-%`} AND status IN ('approved','accelerated','conditional')
        GROUP BY authority, jurisdiction ORDER BY n DESC`),
    [] as YearApprovalCounts[],
  );
}

export interface YearApproval {
  id: number;
  approval_date: string;
  authority: string;
  jurisdiction: string;
  status: string;
  approval_type: string | null;
  accelerated: boolean | null;
  indication: string;
  drug_slug: string;
  drug_name: string;
  cancer_slug: string | null;
  cancer_name: string | null;
  tumor_agnostic: boolean;
  source_slug: string;
}
/** Original approvals first (new molecules / first indications), then supplements; all with a mapped cancer first. */
export async function yearApprovals(year: number, limit = 60): Promise<YearApproval[]> {
  return safe(
    () =>
      run<YearApproval>(sql`
        SELECT a.id, a.approval_date, a.authority, a.jurisdiction, a.status, a.approval_type, a.accelerated, a.indication, a.tumor_agnostic,
               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
        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 LIKE ${`${year}-%`} AND a.status IN ('approved','accelerated','conditional')
        ORDER BY (a.approval_type = 'ORIG') DESC, (a.cancer_id IS NOT NULL) DESC, a.approval_date DESC LIMIT ${limit}`),
    [] as YearApproval[],
  );
}

export interface YearTrialPhase {
  phase: string;
  n: number;
  interventional: number;
  industry: number;
}
export async function yearTrialsByPhase(year: number): Promise<YearTrialPhase[]> {
  return safe(
    () =>
      run<YearTrialPhase>(sql`
        SELECT ph AS phase, count(*)::int AS n, count(*) FILTER (WHERE t.study_type = 'INTERVENTIONAL')::int AS interventional, count(*) FILTER (WHERE t.lead_sponsor_class = 'INDUSTRY')::int AS industry
        FROM clinical_trials t, unnest(CASE WHEN cardinality(t.phases) = 0 THEN ARRAY['NA'] ELSE t.phases END) ph
        WHERE t.first_posted_date LIKE ${`${year}-%`}
        GROUP BY ph ORDER BY CASE ph 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 YearTrialPhase[],
  );
}

export interface YearTrialTotals {
  total: number;
  interventional: number;
  phase3: number;
  industry: number;
  with_results: number;
  countries: number;
}
export async function yearTrialTotals(year: number): Promise<YearTrialTotals | null> {
  const rows = await safe(
    () =>
      run<YearTrialTotals>(sql`
        SELECT count(*)::int AS total, count(*) FILTER (WHERE study_type = 'INTERVENTIONAL')::int AS interventional, count(*) FILTER (WHERE 'PHASE3' = ANY(phases))::int AS phase3,
               count(*) FILTER (WHERE lead_sponsor_class = 'INDUSTRY')::int AS industry, count(*) FILTER (WHERE has_results)::int AS with_results,
               (SELECT count(DISTINCT c) FROM clinical_trials t2, unnest(t2.countries) c WHERE t2.first_posted_date LIKE ${`${year}-%`})::int AS countries
        FROM clinical_trials WHERE first_posted_date LIKE ${`${year}-%`}`),
    [] as YearTrialTotals[],
  );
  const r = rows[0];
  return r && r.total > 0 ? r : null;
}

export interface YearCancerTrials {
  id: string;
  slug: string;
  canonical_name: string;
  trials: number;
  phase3: number;
}
/** Top-level cancers by studies first posted in the year (conditions mapped to the cancer or any descendant, distinct studies). */
export async function yearTrialsByCancer(year: number, limit = 12): Promise<YearCancerTrials[]> {
  return safe(
    () =>
      run<YearCancerTrials>(sql`
        WITH RECURSIVE tops AS (
          SELECT id AS top_id, id AS cancer_id, 0 AS depth FROM cancers WHERE top_level AND status = 'active'
          UNION
          SELECT tops.top_id, h.child_id, tops.depth + 1 FROM tops JOIN cancer_hierarchy h ON h.parent_id = tops.cancer_id WHERE tops.depth < 12
        ),
        yr AS (SELECT id, phases FROM clinical_trials WHERE first_posted_date LIKE ${`${year}-%`}),
        m AS (
          SELECT DISTINCT tops.top_id, yr.id AS trial_id, ('PHASE3' = ANY(yr.phases)) AS p3
          FROM yr JOIN trial_conditions tc ON tc.trial_id = yr.id AND tc.cancer_id IS NOT NULL JOIN tops ON tops.cancer_id = tc.cancer_id
        )
        SELECT c.id, c.slug, c.canonical_name, count(*)::int AS trials, count(*) FILTER (WHERE p3)::int AS phase3
        FROM m JOIN cancers c ON c.id = m.top_id GROUP BY c.id, c.slug, c.canonical_name ORDER BY trials DESC LIMIT ${limit}`),
    [] as YearCancerTrials[],
  );
}

export interface YearSponsor {
  lead_sponsor: string;
  lead_sponsor_class: string | null;
  n: number;
  phase3: number;
}
export async function yearTopSponsors(year: number, limit = 10): Promise<YearSponsor[]> {
  return safe(
    () =>
      run<YearSponsor>(sql`
        SELECT lead_sponsor, min(lead_sponsor_class) AS lead_sponsor_class, count(*)::int AS n, count(*) FILTER (WHERE 'PHASE3' = ANY(phases))::int AS phase3
        FROM clinical_trials WHERE first_posted_date LIKE ${`${year}-%`} AND study_type = 'INTERVENTIONAL' AND lead_sponsor IS NOT NULL
        GROUP BY lead_sponsor ORDER BY n DESC LIMIT ${limit}`),
    [] as YearSponsor[],
  );
}

export interface YearLiterature {
  id: string;
  slug: string;
  canonical_name: string;
  count: number;
  prev_count: number | null;
  query: string;
  computed_at: Date | string;
}
/** Top-level cancers by PubMed records for the year (window_key yYYYY, query stored with each count). */
export async function yearLiterature(year: number, limit = 15): Promise<YearLiterature[]> {
  return safe(
    () =>
      run<YearLiterature>(sql`
        SELECT c.id, c.slug, c.canonical_name, l.count, p.count AS prev_count, l.query, l.updated_at AS computed_at
        FROM literature_counts l JOIN cancers c ON c.id = l.cancer_id
        LEFT JOIN literature_counts p ON p.cancer_id = l.cancer_id AND p.window_key = ${`y${year - 1}`}
        WHERE l.window_key = ${`y${year}`} AND c.top_level AND c.status = 'active'
        ORDER BY l.count DESC LIMIT ${limit}`),
    [] as YearLiterature[],
  );
}

export interface YearEpi {
  source_slug: string;
  source_name: string;
  metric: string;
  geography_name: string;
  geography_slug: string;
  n: number;
  cancers: number;
}
/** Epidemiology observations whose reference year is this year (what registries published for it). */
export async function yearEpidemiology(year: number): Promise<YearEpi[]> {
  return safe(
    () =>
      run<YearEpi>(sql`
        SELECT s.slug AS source_slug, s.name AS source_name, o.metric, g.name AS geography_name, g.slug AS geography_slug, count(*)::int AS n, count(DISTINCT o.cancer_id)::int AS cancers
        FROM epidemiology_observations o JOIN sources s ON s.id = o.source_id JOIN geographies g ON g.id = o.geography_id
        WHERE o.year = ${year} GROUP BY s.slug, s.name, o.metric, g.name, g.slug ORDER BY g.name, o.metric, s.slug`),
    [] as YearEpi[],
  );
}

/** Years for which at least one dated fact exists (approvals, trials, literature or observations). */
export async function yearsWithData(): Promise<number[]> {
  const rows = await safe(
    () =>
      run<{ y: number }>(sql`
        SELECT DISTINCT y FROM (
          SELECT substr(approval_date, 1, 4)::int AS y FROM drug_approvals WHERE approval_date ~ '^\\d{4}'
          UNION SELECT substr(first_posted_date, 1, 4)::int FROM clinical_trials WHERE first_posted_date ~ '^\\d{4}'
          UNION SELECT year FROM epidemiology_observations
          UNION SELECT substr(window_key, 2)::int FROM literature_counts WHERE window_key ~ '^y\\d{4}$'
        ) x WHERE y >= ${YEAR_MIN} AND y <= extract(year FROM now())::int ORDER BY y DESC`),
    [] as Array<{ y: number }>,
  );
  return rows.map((r) => Number(r.y));
}