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 { pageQuery } from '../lib/pagination.js';
import { resolveCancer, resolveTrial } from '../lib/resolve.js';
import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';
import { pluck } from '../lib/sources.js';

export const trialRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get('/trials', { schema: { tags: ['trials'], summary: 'Search clinical trials', querystring: z.object({ q: z.string().trim().min(1).max(200).optional().describe('NCT id, acronym or title words'), status: z.string().optional(), phase: z.string().optional(), cancer: z.string().optional().describe('Cancer id/slug — includes descendants'), country: z.string().optional(), studyType: z.string().optional(), ...pageQuery }), response: ok(AnyList, true) } }, async (req) => {
    const q = req.query;
    const conds = [sql`true`];
    if (q.status) conds.push(sql`t.overall_status = ${q.status.toUpperCase()}`);
    if (q.phase) conds.push(sql`${q.phase.toUpperCase()} = ANY(t.phases)`);
    if (q.studyType) conds.push(sql`t.study_type = ${q.studyType.toUpperCase()}`);
    if (q.country) conds.push(sql`${q.country} = ANY(t.countries)`);
    if (q.q) {
      const raw = q.q.trim();
      if (/^NCT\d+$/i.test(raw)) conds.push(sql`t.nct_id LIKE ${raw.toUpperCase() + '%'}`);
      else conds.push(sql`(upper(coalesce(t.acronym,'')) = ${raw.toUpperCase()} OR to_tsvector('english', t.brief_title || ' ' || coalesce(t.official_title,'')) @@ plainto_tsquery('english', ${raw}))`);
    }
    if (q.cancer) {
      const { id } = await resolveCancer(app.db, q.cancer);
      const ids = await descendantIds(app.db, id);
      conds.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id = ANY(${sql.param(ids)}::text[]))`);
    }
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.study_type, t.phases, t.overall_status, t.start_date, t.primary_completion_date, t.last_update_posted_date, t.has_results, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.conditions, t.countries, t.locations_count,
        count(*) OVER() AS total
      FROM clinical_trials t WHERE ${sql.join(conds, sql` AND `)}
      ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${q.limit} OFFSET ${q.offset}`);
    const total = rows.length ? num(rows[0]!.total) : 0;
    const data = rows.map((r) => {
      const { total: _t, ...rest } = r;
      return camel(rest);
    });
    return respond(app, data, data.length ? ['clinicaltrials'] : [], paginate(total, q.limit, q.offset));
  });

  app.get('/trials/:nct', { schema: { tags: ['trials'], summary: 'Trial: conditions with cancer mapping (match type), interventions with drug mapping, locations (first 200), linked publications', params: z.object({ nct: z.string().min(1).describe('NCT id or CI-TRIAL-… id') }), response: ok(AnyRecord) } }, async (req) => {
    const { id } = await resolveTrial(app.db, req.params.nct);
    const db = app.db;
    const [trial, conditions, interventions, locations, pubs, source] = await Promise.all([
      db.execute<Record<string, unknown>>(sql`SELECT * FROM clinical_trials WHERE id = ${id}`),
      db.execute<Record<string, unknown>>(sql`SELECT tc.id, tc.condition_text, tc.normalized, tc.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, tc.match_type, tc.confidence FROM trial_conditions tc LEFT JOIN cancers c ON c.id = tc.cancer_id WHERE tc.trial_id = ${id} ORDER BY tc.id`),
      db.execute<Record<string, unknown>>(sql`SELECT ti.id, ti.name, ti.intervention_type, ti.drug_id, d.slug AS drug_slug, d.name AS drug_name, ti.match_type FROM trial_interventions ti LEFT JOIN drugs d ON d.id = ti.drug_id WHERE ti.trial_id = ${id} ORDER BY ti.id`),
      db.execute<Record<string, unknown>>(sql`SELECT facility, city, state, zip, country, status, lat, lng FROM trial_locations WHERE trial_id = ${id} ORDER BY country, city LIMIT 200`),
      db.execute<Record<string, unknown>>(sql`
        SELECT p.id, p.pmid, p.doi, p.title, p.journal, p.pub_year, p.retracted, e.method, e.status AS edge_status, e.source_id
        FROM publication_entity_edges e JOIN publications p ON p.id = e.publication_id WHERE e.entity_type = 'trial' AND e.entity_id = ${id} AND e.status <> 'rejected' ORDER BY p.pub_year DESC NULLS LAST LIMIT 100`),
      db.execute<{ source_id: string; retrieved_at: string; raw_path: string | null }>(sql`SELECT sr.source_id, sr.retrieved_at, sr.raw_path FROM clinical_trials t JOIN source_records sr ON sr.id = t.source_record_id WHERE t.id = ${id}`),
    ]);
    const t = camel<Record<string, unknown>>(trial[0]!);
    const data = {
      ...t,
      provenance: source[0] ? { sourceId: source[0].source_id, retrievedAt: source[0].retrieved_at, url: `https://clinicaltrials.gov/study/${t.nctId as string}`, category: 'observed_data' } : { sourceSlug: 'clinicaltrials', url: `https://clinicaltrials.gov/study/${t.nctId as string}` },
      conditionMappings: conditions.map((r) => ({ id: r.id, conditionText: r.condition_text, normalized: r.normalized, cancer: r.cancer_id ? { id: r.cancer_id, slug: r.cancer_slug, name: r.cancer_name } : null, matchType: r.match_type, confidence: r.confidence })),
      interventionMappings: interventions.map((r) => ({ id: r.id, name: r.name, interventionType: r.intervention_type, drug: r.drug_id ? { id: r.drug_id, slug: r.drug_slug, name: r.drug_name } : null, matchType: r.match_type })),
      locations: camelRows(locations),
      locationsShown: locations.length,
      publications: pubs.map((r) => {
        const { method, edge_status, source_id, ...rest } = r;
        return { ...camel(rest), edge: { method, status: edge_status, sourceId: source_id } };
      }),
    };
    return respond(app, data, [source[0]?.source_id ?? 'clinicaltrials', ...pluck(pubs, 'source_id')]);
  });
};