import { sql } from 'drizzle-orm';
import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';
import { z } from 'zod';
import { paginate } from '../lib/envelope.js';
import { descendantIds } from '../lib/descendants.js';
import { BadRequest } from '../lib/errors.js';
import { boolQuery, pageQuery } from '../lib/pagination.js';
import { resolveCancer } from '../lib/resolve.js';
import { AnyList, num, ok, respond } from '../lib/respond.js';

const PHASES = ['PHASE1', 'PHASE2', 'PHASE3', 'PHASE4'] as const;
const FORMULA = 'ci-trial-sites-v1';

/**
 * GET /trials/sites — trial site aggregates for the map (SPEC §11).
 *   level=country (default): from the derived table trial_site_country_counts (recomputed by `cix intel`),
 *     cancer = top-level cancer id/slug (descendants included) or omitted for every oncology trial.
 *   level=city: live aggregate of trial_locations (registrant city/state, mean lat/lng); requires
 *     `cancer` or `recruiting=true` (the unfiltered registry-wide aggregate is too slow per request).
 * Every row is a computed_metric with its formula version; sites are registrant-entered locations.
 * NOT registered in app.ts by this module — the integrator adds `await app.register(trialSiteRoutes)`.
 */
export const trialSiteRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get(
    '/trials/sites',
    {
      schema: {
        tags: ['trials'],
        summary: 'Trial sites per country (precomputed) or per city (live) — the trial map data',
        querystring: z.object({
          cancer: z.string().optional().describe('Top-level cancer id/slug (country level) or any cancer id/slug (city level); descendants included'),
          phase: z.enum(PHASES).optional().describe('PHASE1 includes EARLY_PHASE1; omitted = any phase'),
          recruiting: boolQuery.describe('true = location status RECRUITING (or study RECRUITING when the location has no status)'),
          level: z.enum(['country', 'city']).default('country'),
          ...pageQuery,
        }),
        response: ok(AnyList, true),
      },
    },
    async (req) => {
      const q = req.query;
      const recruiting = q.recruiting ?? false;
      const phase = q.phase ?? null;
      const cancer = q.cancer ? await resolveCancer(app.db, q.cancer) : null;

      if (q.level === 'country') {
        const rows = await app.db.execute<{ country: string; iso3: string | null; sites: number; trials: number; formula_version: string; updated_at: string; total: string }>(sql`
          SELECT s.country, s.iso3, s.sites, s.trials, s.formula_version, s.updated_at, count(*) OVER() AS total
          FROM trial_site_country_counts s
          WHERE s.cancer_id IS NOT DISTINCT FROM ${cancer?.id ?? null} AND s.phase IS NOT DISTINCT FROM ${phase} AND s.recruiting_only = ${recruiting}
          ORDER BY s.sites DESC, s.country LIMIT ${q.limit} OFFSET ${q.offset}`);
        if (cancer && rows.length === 0 && q.offset === 0) {
          const top = await app.db.execute<{ top_level: boolean }>(sql`SELECT top_level FROM cancers WHERE id = ${cancer.id}`);
          if (top[0] && !top[0].top_level) throw new BadRequest(`Country aggregates are precomputed for top-level cancers only; ${cancer.slug} is not top-level. Use level=city or a top-level ancestor.`);
        }
        const total = rows.length ? num(rows[0]!.total) : 0;
        const data = rows.map((r) => ({
          level: 'country' as const,
          country: r.country,
          iso3: r.iso3,
          sites: num(r.sites),
          trials: num(r.trials),
          cancer: cancer ? { id: cancer.id, slug: cancer.slug } : null,
          phase,
          recruitingOnly: recruiting,
          claim: 'computed_metric',
          formulaVersion: r.formula_version,
          computedAt: r.updated_at,
        }));
        return respond(app, data, data.length ? ['clinicaltrials'] : [], paginate(total, q.limit, q.offset));
      }

      // level=city — live aggregate.
      if (!cancer && !recruiting) throw new BadRequest('level=city requires a cancer filter or recruiting=true (the registry-wide city aggregate is not served per request).');
      const conds = [sql`l.country IS NOT NULL AND l.country <> '' AND l.lat IS NOT NULL AND l.lng IS NOT NULL AND l.city IS NOT NULL`];
      if (phase) conds.push(phase === 'PHASE1' ? sql`(t.phases && ARRAY['PHASE1','EARLY_PHASE1']::text[])` : sql`${phase} = ANY(t.phases)`);
      if (recruiting) conds.push(sql`coalesce(l.status, t.overall_status) = 'RECRUITING'`);
      if (cancer) {
        const ids = await descendantIds(app.db, cancer.id);
        conds.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = l.trial_id AND tc.cancer_id = ANY(${sql.param(ids)}::text[]))`);
      }
      const rows = await app.db.execute<{ country: string; city: string; state: string | null; lat: number; lng: number; sites: string; trials: string; total: string }>(sql`
        SELECT l.country, l.city, l.state, avg(l.lat)::float8 AS lat, avg(l.lng)::float8 AS lng, count(*) AS sites, count(DISTINCT l.trial_id) AS trials, count(*) OVER() AS total
        FROM trial_locations l JOIN clinical_trials t ON t.id = l.trial_id
        WHERE ${sql.join(conds, sql` AND `)}
        GROUP BY l.country, l.city, l.state
        ORDER BY sites DESC, trials DESC, l.country, l.city LIMIT ${q.limit} OFFSET ${q.offset}`);
      const total = rows.length ? num(rows[0]!.total) : 0;
      const data = rows.map((r) => ({
        level: 'city' as const,
        country: r.country,
        city: r.city,
        state: r.state,
        lat: Number(r.lat),
        lng: Number(r.lng),
        sites: num(r.sites),
        trials: num(r.trials),
        cancer: cancer ? { id: cancer.id, slug: cancer.slug } : null,
        phase,
        recruitingOnly: recruiting,
        claim: 'computed_metric',
        formulaVersion: FORMULA,
        computedAt: new Date().toISOString(),
      }));
      return respond(app, data, data.length ? ['clinicaltrials'] : [], paginate(total, q.limit, q.offset));
    },
  );
};