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

export interface GeneRow {
  id: string;
  hgnc_id: string | null;
  symbol: string;
  name: string | null;
  locus_type: string | null;
  locus_group: string | null;
  location: string | null;
  chromosome: string | null;
  ensembl_gene_id: string | null;
  ncbi_gene_id: string | null;
  omim_ids: string[];
  uniprot_ids: string[];
  refseq_accession: string | null;
  prev_symbols: string[];
  alias_symbols: string[];
  gene_families: string[];
  status: string;
  is_cancer_gene: boolean;
  civic_gene_id: number | null;
  description: string | null;
  updated_at: Date;
  evidence_count?: number;
  variant_count?: number;
  cohort_count?: number;
}

export async function getGeneBySymbol(symbol: string): Promise<GeneRow | null> {
  const rows = await safe(
    () =>
      run<GeneRow>(sql`
        SELECT g.*, (SELECT count(*) FROM civic_evidence_items e WHERE g.id = ANY(e.gene_ids) OR g.symbol = ANY(e.gene_symbols))::int AS evidence_count,
               (SELECT count(*) FROM variants v WHERE v.gene_id = g.id)::int AS variant_count,
               (SELECT count(DISTINCT f.cohort_id) FROM cancer_gene_frequencies f WHERE f.gene_id = g.id OR f.gene_symbol = g.symbol)::int AS cohort_count
        FROM genes g WHERE upper(g.symbol) = upper(${symbol}) LIMIT 1`),
    [] as GeneRow[],
  );
  return rows[0] ?? null;
}

export async function listGenes(opts: { q: string; cancerOnly: boolean; page: number; pageSize: number }): Promise<{ rows: GeneRow[]; total: number }> {
  const where = sql`${opts.cancerOnly ? sql`g.is_cancer_gene` : sql`true`} AND ${opts.q ? sql`(g.symbol ILIKE ${opts.q + '%'} OR g.name ILIKE ${'%' + opts.q + '%'} OR ${opts.q.toUpperCase()} = ANY(g.alias_symbols) OR ${opts.q.toUpperCase()} = ANY(g.prev_symbols))` : sql`true`}`;
  const total = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM genes g WHERE ${where}`), [{ n: '0' }]);
  const rows = await safe(
    () =>
      run<GeneRow>(sql`
        SELECT g.*, (SELECT count(*) FROM civic_evidence_items e WHERE g.id = ANY(e.gene_ids))::int AS evidence_count,
               (SELECT count(*) FROM variants v WHERE v.gene_id = g.id)::int AS variant_count
        FROM genes g WHERE ${where} ORDER BY g.is_cancer_gene DESC, g.symbol LIMIT ${opts.pageSize} OFFSET ${(opts.page - 1) * opts.pageSize}`),
    [] as GeneRow[],
  );
  return { rows, total: Number(total[0]?.n ?? 0) };
}

export interface FreqRow {
  id: number;
  cohort_id: string;
  study_id: string;
  cohort_name: string;
  program: string | null;
  data_release: string | null;
  cases_with_ssm: number | null;
  case_count: number | null;
  cancer_id: string | null;
  cancer_slug: string | null;
  cancer_name: string | null;
  cancer_match_type: string | null;
  gene_id: string | null;
  gene_symbol: string;
  alteration_type: string;
  cases_affected: number;
  cases_profiled: number;
  frequency: number;
  rank: number | null;
  provenance_id: number;
  source_slug: string;
  source_name: string;
  updated_at: Date;
}

export interface CohortSummary {
  cohort_id: string;
  study_id: string;
  cohort_name: string;
  program: string | null;
  data_release: string | null;
  cases_with_ssm: number | null;
  case_count: number | null;
  cancer_id: string | null;
  cancer_slug: string | null;
  cancer_name: string | null;
  cancer_match_type: string | null;
  /** Largest denominator among the cohort's frequency rows — drives the default cohort choice. */
  cases_profiled: number;
  frequency_rows: number;
}

/** Cohorts mapped to a cancer (and descendants), largest denominator first (§260-261). */
export async function cohortsForCancer(cancerIds: string[]): Promise<CohortSummary[]> {
  if (cancerIds.length === 0) return [];
  const rows = await safe(
    () =>
      run<CohortSummary & { cases_profiled: string; frequency_rows: string }>(sql`
        SELECT gc.id AS cohort_id, gc.study_id, gc.name AS cohort_name, gc.program, gc.data_release, gc.cases_with_ssm, gc.case_count, gc.cancer_id, gc.cancer_match_type,
               c.slug AS cancer_slug, c.canonical_name AS cancer_name,
               coalesce(max(f.cases_profiled), 0) AS cases_profiled, count(f.id) AS frequency_rows
        FROM genomic_cohorts gc LEFT JOIN cancers c ON c.id = gc.cancer_id LEFT JOIN cancer_gene_frequencies f ON f.cohort_id = gc.id
        WHERE gc.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)})
        GROUP BY gc.id, c.slug, c.canonical_name
        ORDER BY cases_profiled DESC, frequency_rows DESC, gc.name`),
    [],
  );
  return rows.map((r) => ({ ...r, cases_profiled: Number(r.cases_profiled), frequency_rows: Number(r.frequency_rows) }));
}

/**
 * Gene frequencies for a cancer (and descendants), grouped per cohort by the caller (§260-261).
 * `cohortId` restricts to one cohort (the default UI renders the largest cohort only).
 */
export async function frequenciesForCancer(cancerIds: string[], opts: { cohortId?: string | null; limitPerCohort?: number } = {}): Promise<FreqRow[]> {
  if (cancerIds.length === 0) return [];
  const limitPerCohort = opts.limitPerCohort ?? 30;
  const cohortFilter = opts.cohortId ? sql`AND gc.id = ${opts.cohortId}` : sql``;
  return safe(
    () =>
      run<FreqRow>(sql`
        SELECT * FROM (
          SELECT f.*, gc.study_id, gc.name AS cohort_name, gc.program, gc.cases_with_ssm, gc.case_count, gc.cancer_match_type,
                 c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, s.name AS source_name,
                 row_number() OVER (PARTITION BY f.cohort_id, f.alteration_type ORDER BY f.frequency DESC) AS rn
          FROM cancer_gene_frequencies f JOIN genomic_cohorts gc ON gc.id = f.cohort_id JOIN sources s ON s.id = gc.source_id
          LEFT JOIN cancers c ON c.id = gc.cancer_id
          WHERE gc.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}) ${cohortFilter}
        ) x WHERE rn <= ${limitPerCohort} ORDER BY cohort_name, alteration_type, frequency DESC`),
    [] as FreqRow[],
  );
}

export async function frequenciesForGene(geneId: string, symbol: string): Promise<FreqRow[]> {
  return safe(
    () =>
      run<FreqRow>(sql`
        SELECT f.*, gc.study_id, gc.name AS cohort_name, gc.program, gc.cases_with_ssm, gc.case_count, gc.cancer_match_type,
               c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, s.name AS source_name
        FROM cancer_gene_frequencies f JOIN genomic_cohorts gc ON gc.id = f.cohort_id JOIN sources s ON s.id = gc.source_id
        LEFT JOIN cancers c ON c.id = gc.cancer_id
        WHERE f.gene_id = ${geneId} OR f.gene_symbol = ${symbol} ORDER BY f.frequency DESC LIMIT 200`),
    [] as FreqRow[],
  );
}

export interface VariantRow {
  id: string;
  slug: string;
  gene_id: string | null;
  gene_symbol: string | null;
  name: string;
  variant_type: string | null;
  hgvs_g: string | null;
  hgvs_c: string | null;
  hgvs_p: string | null;
  assembly: string | null;
  chromosome: string | null;
  start: number | null;
  end: number | null;
  reference_bases: string | null;
  alternate_bases: string | null;
  coordinates: Array<Record<string, unknown>>;
  clinvar_variation_id: string | null;
  civic_variant_id: number | null;
  dbsnp_ids: string[];
  fusion_partners: string[];
  updated_at: Date;
  evidence_count?: number;
}

export async function getVariantBySlug(slug: string): Promise<VariantRow | null> {
  const rows = await safe(() => run<VariantRow>(sql`SELECT v.*, (SELECT count(*) FROM civic_evidence_items e WHERE v.id = ANY(e.variant_ids))::int AS evidence_count FROM variants v WHERE v.slug = ${slug} LIMIT 1`), [] as VariantRow[]);
  return rows[0] ?? null;
}

export const VARIANT_PAGE_SIZE = 50;

/** Compact variant rows for the gene page list (no coordinates JSON, no HGVS strings). */
export type VariantListRow = Pick<VariantRow, 'id' | 'slug' | 'name' | 'variant_type' | 'evidence_count'>;

export async function variantsForGene(geneId: string, p: { page: number; pageSize: number } = { page: 1, pageSize: VARIANT_PAGE_SIZE }): Promise<VariantListRow[]> {
  return safe(
    () =>
      run<VariantListRow>(sql`
        SELECT v.id, v.slug, v.name, v.variant_type, (SELECT count(*) FROM civic_evidence_items e WHERE v.id = ANY(e.variant_ids))::int AS evidence_count
        FROM variants v WHERE v.gene_id = ${geneId} ORDER BY evidence_count DESC, v.name LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`),
    [] as VariantListRow[],
  );
}
export async function variantsForGeneCount(geneId: string): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM variants v WHERE v.gene_id = ${geneId}`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

export async function variantAliases(variantId: string): Promise<string[]> {
  const rows = await safe(() => run<{ alias: string }>(sql`SELECT alias FROM variant_aliases WHERE variant_id = ${variantId} ORDER BY alias`), []);
  return rows.map((r) => r.alias);
}

export interface ClinSigRow {
  id: number;
  clinvar_variation_id: string;
  clinical_significance: string;
  review_status: string | null;
  star_rating: number | null;
  last_evaluated: string | null;
  conditions: string[];
  condition_cancer_ids: string[];
  origin_simple: string | null;
  number_submitters: number | null;
  provenance_id: number;
  updated_at: Date;
}
export async function clinicalSignificanceFor(variantId: string): Promise<ClinSigRow[]> {
  return safe(() => run<ClinSigRow>(sql`SELECT * FROM variant_clinical_significance WHERE variant_id = ${variantId} ORDER BY last_evaluated DESC NULLS LAST`), [] as ClinSigRow[]);
}

export async function geneSymbolsForSitemap(offset: number, limit: number): Promise<Array<{ symbol: string; updated_at: Date }>> {
  return safe(() => run<{ symbol: string; updated_at: Date }>(sql`SELECT symbol, updated_at FROM genes ORDER BY id LIMIT ${limit} OFFSET ${offset}`), []);
}
export async function countGenes(): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM genes`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}