import { sql } from 'drizzle-orm';
import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';
import { z } from 'zod';
import { paginate } from '../lib/envelope.js';
import { boolQuery, pageQuery } from '../lib/pagination.js';
import { resolveGene } from '../lib/resolve.js';
import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';
import { pluck } from '../lib/sources.js';

export const geneRoutes: FastifyPluginAsyncZod = async (app) => {
  app.get('/genes', { schema: { tags: ['genes'], summary: 'List genes (HGNC symbols)', querystring: z.object({ q: z.string().trim().min(1).max(50).optional().describe('Symbol/alias prefix'), cancerOnly: boolQuery.describe('Only genes with curated or cohort cancer evidence'), sort: z.enum(['symbol', 'evidence']).default('symbol'), ...pageQuery }), response: ok(AnyList, true) } }, async (req) => {
    const q = req.query;
    const conds = [sql`g.status = 'Approved'`];
    if (q.cancerOnly) conds.push(sql`g.is_cancer_gene`);
    if (q.q) {
      const up = q.q.toUpperCase();
      conds.push(sql`(upper(g.symbol) LIKE ${up + '%'} OR EXISTS (SELECT 1 FROM gene_aliases a WHERE a.gene_id = g.id AND upper(a.alias) LIKE ${up + '%'}))`);
    }
    const order = q.sort === 'evidence' ? sql`ec.evidence_count DESC NULLS LAST, g.symbol` : sql`g.symbol`;
    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`
      SELECT g.id, g.hgnc_id, g.symbol, g.name, g.locus_type, g.location, g.chromosome, g.ensembl_gene_id, g.ncbi_gene_id, g.is_cancer_gene, g.civic_gene_id,
        ec.evidence_count, ec.variant_count, ec.drug_count, ec.updated_at AS counters_computed_at, count(*) OVER() AS total
      FROM genes g LEFT JOIN entity_counters ec ON ec.entity_type = 'gene' AND ec.entity_id = g.id
      WHERE ${sql.join(conds, sql` AND `)} ORDER BY ${order} 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, ['hgnc'], paginate(total, q.limit, q.offset));
  });

  app.get('/genes/:symbol', { schema: { tags: ['genes'], summary: 'Gene: aliases, counters, linked cancers (curated evidence + cohort frequencies), variants, drugs, literature edges', params: z.object({ symbol: z.string().min(1).describe('HGNC symbol, alias, HGNC:id or CI-GENE-… id') }), response: ok(AnyRecord) } }, async (req) => {
    const { id } = await resolveGene(app.db, req.params.symbol);
    const db = app.db;
    const [gene, aliases, civicCancers, cohortCancers, variants, drugs, pubs] = await Promise.all([
      db.execute<Record<string, unknown>>(sql`SELECT g.*, ec.evidence_count, ec.variant_count, ec.drug_count, ec.updated_at AS counters_computed_at
        FROM genes g LEFT JOIN entity_counters ec ON ec.entity_type = 'gene' AND ec.entity_id = g.id WHERE g.id = ${id}`),
      db.execute<Record<string, unknown>>(sql`SELECT alias, alias_type, source_id FROM gene_aliases WHERE gene_id = ${id} ORDER BY alias_type, alias`),
      db.execute<Record<string, unknown>>(sql`
        SELECT c.id, c.slug, c.canonical_name AS name, c.top_level, count(*) AS evidence_items,
          count(*) FILTER (WHERE e.evidence_type = 'PREDICTIVE') AS predictive, count(*) FILTER (WHERE e.evidence_type = 'PROGNOSTIC') AS prognostic,
          count(*) FILTER (WHERE e.evidence_type = 'DIAGNOSTIC') AS diagnostic, count(*) FILTER (WHERE e.evidence_type = 'PREDISPOSING') AS predisposing,
          count(*) FILTER (WHERE e.evidence_level IN ('A','B')) AS level_ab, min(p.source_id) AS source_id
        FROM civic_evidence_items e JOIN cancers c ON c.id = e.cancer_id LEFT JOIN provenance p ON p.id = e.provenance_id
        WHERE e.status = 'ACCEPTED' AND ${id} = ANY(e.gene_ids)
        GROUP BY c.id ORDER BY evidence_items DESC, c.canonical_name LIMIT 200`),
      db.execute<Record<string, unknown>>(sql`
        SELECT f.id, f.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, f.alteration_type, f.cases_affected, f.cases_profiled, f.frequency, f.rank,
          co.id AS cohort_id, co.study_id, co.name AS cohort_name, co.program, co.source_id, p.retrieved_at, p.source_url, p.dataset_version
        FROM cancer_gene_frequencies f JOIN genomic_cohorts co ON co.id = f.cohort_id LEFT JOIN cancers c ON c.id = f.cancer_id LEFT JOIN provenance p ON p.id = f.provenance_id
        WHERE f.gene_id = ${id} ORDER BY f.frequency DESC LIMIT 200`),
      db.execute<Record<string, unknown>>(sql`
        SELECT v.id, v.slug, v.name, v.variant_type, v.hgvs_p, v.hgvs_c, v.clinvar_variation_id, v.civic_variant_id,
          (SELECT count(*) FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND v.id = ANY(e.variant_ids)) AS evidence_items,
          (SELECT cs.clinical_significance FROM variant_clinical_significance cs WHERE cs.variant_id = v.id LIMIT 1) AS clinical_significance
        FROM variants v WHERE v.gene_id = ${id} ORDER BY evidence_items DESC, v.name LIMIT 500`),
      db.execute<Record<string, unknown>>(sql`
        SELECT d.id, d.slug, d.name, d.kind, count(*) AS evidence_items,
          count(*) FILTER (WHERE e.significance = 'SENSITIVITYRESPONSE') AS sensitivity, count(*) FILTER (WHERE e.significance = 'RESISTANCE') AS resistance,
          array_agg(DISTINCT e.cancer_id) FILTER (WHERE e.cancer_id IS NOT NULL) AS cancer_ids, min(p.source_id) AS source_id
        FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.therapy_ids) tid JOIN drugs d ON d.id = tid LEFT JOIN provenance p ON p.id = e.provenance_id
        WHERE e.status = 'ACCEPTED' AND ${id} = ANY(e.gene_ids)
        GROUP BY d.id ORDER BY evidence_items DESC, d.name 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.confidence, 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 = 'gene' AND e.entity_id = ${id} AND e.status <> 'rejected' ORDER BY p.pub_year DESC NULLS LAST LIMIT 100`),
    ]);
    const g = camel<Record<string, unknown>>(gene[0]!);
    const counters = g.countersComputedAt ? { evidenceItems: num(g.evidenceCount), variants: num(g.variantCount), drugs: num(g.drugCount), computedAt: g.countersComputedAt } : null;
    delete g.evidenceCount;
    delete g.variantCount;
    delete g.drugCount;
    delete g.countersComputedAt;
    const data = {
      ...g,
      counters,
      aliases: camelRows(aliases),
      cancers: {
        curatedEvidence: civicCancers.map((r) => ({ cancer: { id: r.id, slug: r.slug, name: r.name, topLevel: r.top_level }, evidenceItems: num(r.evidence_items), byType: { predictive: num(r.predictive), prognostic: num(r.prognostic), diagnostic: num(r.diagnostic), predisposing: num(r.predisposing) }, levelAB: num(r.level_ab), sourceId: r.source_id, category: 'curated_evidence' })),
        cohortFrequencies: cohortCancers.map((r) => ({ id: r.id, cancer: r.cancer_id ? { id: r.cancer_id, slug: r.cancer_slug, name: r.cancer_name } : null, alterationType: r.alteration_type, casesAffected: r.cases_affected, casesProfiled: r.cases_profiled, frequency: r.frequency, rank: r.rank, cohort: { id: r.cohort_id, studyId: r.study_id, name: r.cohort_name, program: r.program }, provenance: { sourceId: r.source_id, retrievedAt: r.retrieved_at, url: r.source_url, datasetVersion: r.dataset_version, category: 'observed_data' } })),
      },
      variants: camelRows(variants),
      drugs: drugs.map((r) => ({ drug: { id: r.id, slug: r.slug, name: r.name, kind: r.kind }, evidenceItems: num(r.evidence_items), sensitivity: num(r.sensitivity), resistance: num(r.resistance), cancerIds: r.cancer_ids ?? [], sourceId: r.source_id })),
      publications: pubs.map((r) => {
        const { method, confidence, edge_status, source_id, ...rest } = r;
        return { ...camel(rest), edge: { method, confidence, status: edge_status, sourceId: source_id } };
      }),
    };
    return respond(app, data, ['hgnc', ...pluck(civicCancers, 'source_id'), ...pluck(cohortCancers, 'source_id'), ...pluck(drugs, 'source_id'), ...pluck(pubs, 'source_id'), ...pluck(aliases, 'source_id')]);
  });
};