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

/**
 * Country-level research and regulatory activity (SPEC §22 "Clinical trial activity", §13 jurisdiction-aware
 * approvals). Trial sites come from the derived `trial_site_country_counts` (ClinicalTrials.gov locations,
 * matched to the geography by ISO 3166-1 alpha-3); approvals from `drug_approvals` for the jurisdiction code.
 */

export interface CountryTrialTotals {
  sites: number;
  trials: number;
  recruiting_sites: number;
  recruiting_trials: number;
  computed_at: Date | string | null;
}
export async function countryTrialTotals(iso3: string): Promise<CountryTrialTotals | null> {
  const rows = await safe(
    () =>
      run<CountryTrialTotals>(sql`
        SELECT coalesce(max(sites) FILTER (WHERE NOT recruiting_only), 0)::int AS sites, coalesce(max(trials) FILTER (WHERE NOT recruiting_only), 0)::int AS trials,
               coalesce(max(sites) FILTER (WHERE recruiting_only), 0)::int AS recruiting_sites, coalesce(max(trials) FILTER (WHERE recruiting_only), 0)::int AS recruiting_trials,
               max(updated_at) AS computed_at
        FROM trial_site_country_counts WHERE iso3 = ${iso3} AND cancer_id IS NULL AND phase IS NULL`),
    [] as CountryTrialTotals[],
  );
  const r = rows[0];
  return r && r.sites > 0 ? r : null;
}

export interface CountryCancerSites {
  cancer_id: string;
  slug: string;
  canonical_name: string;
  sites: number;
  trials: number;
  recruiting_sites: number;
  recruiting_trials: number;
}
/** Sites and studies per top-level cancer in this country (any phase). */
export async function countrySitesByCancer(iso3: string, limit = 40): Promise<CountryCancerSites[]> {
  return safe(
    () =>
      run<CountryCancerSites>(sql`
        SELECT c.id AS cancer_id, c.slug, c.canonical_name,
               coalesce(max(t.sites) FILTER (WHERE NOT t.recruiting_only), 0)::int AS sites, coalesce(max(t.trials) FILTER (WHERE NOT t.recruiting_only), 0)::int AS trials,
               coalesce(max(t.sites) FILTER (WHERE t.recruiting_only), 0)::int AS recruiting_sites, coalesce(max(t.trials) FILTER (WHERE t.recruiting_only), 0)::int AS recruiting_trials
        FROM trial_site_country_counts t JOIN cancers c ON c.id = t.cancer_id
        WHERE t.iso3 = ${iso3} AND t.phase IS NULL
        GROUP BY c.id, c.slug, c.canonical_name ORDER BY sites DESC, c.canonical_name LIMIT ${limit}`),
    [] as CountryCancerSites[],
  );
}

export interface CountryPhaseSites {
  phase: string;
  sites: number;
  trials: number;
  recruiting_trials: number;
}
export async function countrySitesByPhase(iso3: string): Promise<CountryPhaseSites[]> {
  return safe(
    () =>
      run<CountryPhaseSites>(sql`
        SELECT phase, coalesce(max(sites) FILTER (WHERE NOT recruiting_only), 0)::int AS sites, coalesce(max(trials) FILTER (WHERE NOT recruiting_only), 0)::int AS trials,
               coalesce(max(trials) FILTER (WHERE recruiting_only), 0)::int AS recruiting_trials
        FROM trial_site_country_counts WHERE iso3 = ${iso3} AND cancer_id IS NULL AND phase IS NOT NULL
        GROUP BY phase ORDER BY phase`),
    [] as CountryPhaseSites[],
  );
}

export interface JurisdictionApprovalSummary {
  authority: string;
  status: string;
  n: number;
  drugs: number;
  latest: string | null;
}
export async function jurisdictionApprovalSummary(jurisdiction: string): Promise<JurisdictionApprovalSummary[]> {
  return safe(
    () =>
      run<JurisdictionApprovalSummary>(sql`
        SELECT authority, status, count(*)::int AS n, count(DISTINCT drug_id)::int AS drugs, max(approval_date) FILTER (WHERE approval_date ~ '^\\d{4}-\\d{2}-\\d{2}$') AS latest
        FROM drug_approvals WHERE jurisdiction = ${jurisdiction} GROUP BY authority, status ORDER BY authority, n DESC`),
    [] as JurisdictionApprovalSummary[],
  );
}

export interface JurisdictionApproval {
  id: number;
  approval_date: string | null;
  authority: string;
  status: string;
  indication: string;
  drug_slug: string;
  drug_name: string;
  cancer_slug: string | null;
  cancer_name: string | null;
  source_slug: string;
}
export async function latestJurisdictionApprovals(jurisdiction: string, limit = 12): Promise<JurisdictionApproval[]> {
  return safe(
    () =>
      run<JurisdictionApproval>(sql`
        SELECT a.id, a.approval_date, a.authority, a.status, a.indication, 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.jurisdiction = ${jurisdiction} AND a.status IN ('approved','accelerated','conditional')
        ORDER BY a.approval_date DESC NULLS LAST, a.id DESC LIMIT ${limit}`),
    [] as JurisdictionApproval[],
  );
}