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

/**
 * Trial map queries. Country aggregates come from the DERIVED table `trial_site_country_counts`
 * (rebuilt by `pnpm cix intel`, formula ci-trial-sites-v1); the city layer and the distinct-trial
 * headline are read live from `trial_locations` because they are not precomputed.
 */

export const SITE_PHASES = ['PHASE1', 'PHASE2', 'PHASE3', 'PHASE4'] as const;
export type SitePhase = (typeof SITE_PHASES)[number];
export const SITE_METRICS = ['sites', 'trials'] as const;
export type SiteMetric = (typeof SITE_METRICS)[number];

export interface SiteScope {
  /** Top-level cancer id, or null for every oncology trial. */
  cancerId: string | null;
  phase: SitePhase | null;
  recruitingOnly: boolean;
}

export interface SiteCountryRow {
  country: string;
  iso3: string | null;
  sites: number;
  trials: number;
  formula_version: string;
  computed_at: Date | string;
}

/** Country aggregates for one scope, sorted by sites desc (≈ 180 rows at most). */
export async function countryCounts(s: SiteScope): Promise<SiteCountryRow[]> {
  const rows = await safe(
    () =>
      run<SiteCountryRow & { sites: string | number; trials: string | number }>(sql`
        SELECT country, iso3, sites, trials, formula_version, updated_at AS computed_at
        FROM trial_site_country_counts
        WHERE cancer_id IS NOT DISTINCT FROM ${s.cancerId} AND phase IS NOT DISTINCT FROM ${s.phase} AND recruiting_only = ${s.recruitingOnly}
        ORDER BY sites DESC, country`),
    [],
  );
  return rows.map((r) => ({ ...r, sites: Number(r.sites), trials: Number(r.trials) }));
}

/** True when the derived table has been populated at all (distinguishes "not computed" from "no match"). */
export async function siteCountsAvailable(): Promise<boolean> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM trial_site_country_counts`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0) > 0;
}

export interface TopLevelCancerOption {
  id: string;
  slug: string;
  canonical_name: string;
}

/** Active top-level cancers (the only cancer scopes precomputed for the map). */
export async function listTopLevelCancers(): Promise<TopLevelCancerOption[]> {
  return safe(() => run<TopLevelCancerOption>(sql`SELECT id, slug, canonical_name FROM cancers WHERE status = 'active' AND top_level ORDER BY canonical_name`), []);
}

export interface LiveScope {
  /** Cancer + descendants (semi-join on trial_conditions), or null for every trial. */
  cancerIds: string[] | null;
  phase: SitePhase | null;
  recruitingOnly: boolean;
}

/** WHERE fragment shared by the live queries; alias `l` = trial_locations, `t` = clinical_trials (joined only when needed). */
function liveWhere(s: LiveScope): { where: ReturnType<typeof sql>; needsTrial: boolean } {
  const parts = [sql`l.country IS NOT NULL AND l.country <> ''`];
  let needsTrial = false;
  if (s.phase) {
    needsTrial = true;
    parts.push(s.phase === 'PHASE1' ? sql`(t.phases && ARRAY['PHASE1','EARLY_PHASE1']::text[])` : sql`${s.phase} = ANY(t.phases)`);
  }
  if (s.recruitingOnly) {
    needsTrial = true;
    parts.push(sql`coalesce(l.status, t.overall_status) = 'RECRUITING'`);
  }
  if (s.cancerIds) parts.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = l.trial_id AND tc.cancer_id IN (${sql.join(s.cancerIds.map((i) => sql`${i}`), sql`, `)}))`);
  return { where: sql.join(parts, sql` AND `), needsTrial };
}

/** Distinct studies with ≥ 1 site in a named country for the scope (live; ≈ 100–150 ms on 1.2 M rows). */
export async function distinctTrialCount(s: LiveScope): Promise<number> {
  if (s.cancerIds && s.cancerIds.length === 0) return 0;
  const { where, needsTrial } = liveWhere(s);
  const join = needsTrial ? sql`JOIN clinical_trials t ON t.id = l.trial_id` : sql``;
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(DISTINCT l.trial_id) AS n FROM trial_locations l ${join} WHERE ${where}`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

export interface SiteCityRow {
  country: string;
  city: string;
  state: string | null;
  lat: number;
  lng: number;
  sites: number;
  trials: number;
}

export const CITY_LIMIT = 300;

/**
 * City aggregates (registrant-entered city/state, mean of geocoded lat/lng, sites, distinct trials),
 * top `limit` by sites. Live on trial_locations: ≈ 0.3–0.5 s with a cancer or recruiting filter,
 * but ≈ 3 s for the whole registry without any filter — that case returns [] and the caller omits
 * the layer (documented in docs/methodology/trial-map.md).
 */
export async function cityCounts(s: LiveScope, limit = CITY_LIMIT): Promise<SiteCityRow[]> {
  if (s.cancerIds && s.cancerIds.length === 0) return [];
  if (!s.cancerIds && !s.recruitingOnly) return [];
  const { where, needsTrial } = liveWhere(s);
  const join = needsTrial ? sql`JOIN clinical_trials t ON t.id = l.trial_id` : sql``;
  const rows = await safe(
    () =>
      run<{ country: string; city: string; state: string | null; lat: number; lng: number; sites: string; trials: 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
        FROM trial_locations l ${join}
        WHERE l.lat IS NOT NULL AND l.lng IS NOT NULL AND l.city IS NOT NULL AND ${where}
        GROUP BY l.country, l.city, l.state
        ORDER BY sites DESC, trials DESC, l.country, l.city
        LIMIT ${limit}`),
    [],
  );
  return rows.map((r) => ({ ...r, lat: Number(r.lat), lng: Number(r.lng), sites: Number(r.sites), trials: Number(r.trials) }));
}