import { sql } from 'drizzle-orm';
import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';
import { z } from 'zod';
import { TtlCache } from '../lib/cache.js';
import { ok, respond } from '../lib/respond.js';

const Stats = z.object({
  cancers: z.number().describe('Active malignant canonical entities'),
  entities: z.number().describe('All active canonical entities (incl. precursor/benign)'),
  topLevel: z.number(),
  subtypes: z.number().describe('Hierarchy edges (child relationships)'),
  genes: z.number(),
  cancerGenes: z.number(),
  variants: z.number(),
  drugs: z.number(),
  trials: z.number(),
  activeTrials: z.number(),
  publications: z.number(),
  evidenceItems: z.number(),
  epidemiologyObservations: z.number(),
  survivalObservations: z.number(),
  countriesWithData: z.number(),
  sources: z.number(),
  activeSources: z.number(),
  rankingSnapshots: z.number(),
  unresolvedLabelsOpen: z.number(),
  lastIngestAt: z.string().nullable(),
  computedAt: z.string(),
});
type Stats = z.infer<typeof Stats>;

const cache = new TtlCache<Stats>(60_000);

export const statsRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get('/stats', { schema: { tags: ['stats'], summary: 'Live counts for the homepage ticker (computed from the database, cached 60 s)', response: ok(Stats) } }, async () => {
    const data = await cache.getOrLoad('stats', async () => {
      const rows = await app.db.execute<Record<string, string | null>>(sql`
        SELECT
          (SELECT count(*) FROM cancers WHERE status = 'active' AND malignant) AS cancers,
          (SELECT count(*) FROM cancers WHERE status = 'active') AS entities,
          (SELECT count(*) FROM cancers WHERE status = 'active' AND top_level) AS top_level,
          (SELECT count(DISTINCT child_id) FROM cancer_hierarchy) AS subtypes,
          (SELECT count(*) FROM genes) AS genes,
          (SELECT count(*) FROM genes WHERE is_cancer_gene) AS cancer_genes,
          (SELECT count(*) FROM variants) AS variants,
          (SELECT count(*) FROM drugs) AS drugs,
          (SELECT count(*) FROM clinical_trials) AS trials,
          (SELECT count(*) FROM clinical_trials WHERE overall_status IN ('RECRUITING','NOT_YET_RECRUITING','ENROLLING_BY_INVITATION','ACTIVE_NOT_RECRUITING')) AS active_trials,
          (SELECT count(*) FROM publications) AS publications,
          (SELECT count(*) FROM civic_evidence_items WHERE status = 'ACCEPTED') AS evidence_items,
          (SELECT count(*) FROM epidemiology_observations) AS epidemiology_observations,
          (SELECT count(*) FROM survival_observations) AS survival_observations,
          (SELECT count(DISTINCT o.geography_id) FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id WHERE g.kind = 'country') AS countries_with_data,
          (SELECT count(*) FROM sources) AS sources,
          (SELECT count(*) FROM sources WHERE status = 'active') AS active_sources,
          (SELECT count(*) FROM ranking_snapshots WHERE is_current) AS ranking_snapshots,
          (SELECT count(*) FROM unresolved_labels WHERE status = 'open') AS unresolved_labels_open,
          (SELECT max(finished_at)::text FROM ingest_runs WHERE status IN ('succeeded','partial')) AS last_ingest_at`);
      const r = rows[0]!;
      const n = (k: string) => Number(r[k] ?? 0);
      return {
        cancers: n('cancers'),
        entities: n('entities'),
        topLevel: n('top_level'),
        subtypes: n('subtypes'),
        genes: n('genes'),
        cancerGenes: n('cancer_genes'),
        variants: n('variants'),
        drugs: n('drugs'),
        trials: n('trials'),
        activeTrials: n('active_trials'),
        publications: n('publications'),
        evidenceItems: n('evidence_items'),
        epidemiologyObservations: n('epidemiology_observations'),
        survivalObservations: n('survival_observations'),
        countriesWithData: n('countries_with_data'),
        sources: n('sources'),
        activeSources: n('active_sources'),
        rankingSnapshots: n('ranking_snapshots'),
        unresolvedLabelsOpen: n('unresolved_labels_open'),
        lastIngestAt: r.last_ingest_at ? new Date(r.last_ingest_at).toISOString() : null,
        computedAt: new Date().toISOString(),
      };
    });
    const active = await app.db.execute<{ id: string }>(sql`SELECT id FROM sources WHERE status = 'active'`);
    return respond(
      app,
      data,
      active.map((s) => s.id),
    );
  });
};