import { pgTable, text, integer, bigserial, index, uniqueIndex, real, jsonb, boolean, timestamp } from 'drizzle-orm/pg-core';
import { ciId, createdAt, updatedAt } from './_common.js';

/** Genes — HGNC is authoritative for symbols (CLAUDE.md §11, §143). */
export const genes = pgTable(
  'genes',
  {
    id: ciId().primaryKey(), // CI-GENE-…
    hgncId: text('hgnc_id'), // HGNC:11998
    symbol: text('symbol').notNull(),
    name: text('name'),
    locusType: text('locus_type'),
    locusGroup: text('locus_group'),
    location: text('location'),
    chromosome: text('chromosome'),
    ensemblGeneId: text('ensembl_gene_id'),
    ncbiGeneId: text('ncbi_gene_id'),
    omimIds: text('omim_ids').array().notNull().default([]),
    uniprotIds: text('uniprot_ids').array().notNull().default([]),
    refseqAccession: text('refseq_accession'),
    prevSymbols: text('prev_symbols').array().notNull().default([]),
    aliasSymbols: text('alias_symbols').array().notNull().default([]),
    geneFamilies: text('gene_families').array().notNull().default([]),
    status: text('status').notNull().default('Approved'),
    isCancerGene: boolean('is_cancer_gene').notNull().default(false), // has ≥1 curated cancer edge (derived)
    civicGeneId: integer('civic_gene_id'),
    description: text('description'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('genes_symbol_uq').on(t.symbol), uniqueIndex('genes_hgnc_uq').on(t.hgncId), index('genes_ensembl_idx').on(t.ensemblGeneId), index('genes_ncbi_idx').on(t.ncbiGeneId)],
);

export const geneAliases = pgTable(
  'gene_aliases',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    geneId: ciId('gene_id').notNull(),
    alias: text('alias').notNull(),
    aliasType: text('alias_type').notNull(), // prev_symbol | alias_symbol | prev_name | alias_name
    sourceId: ciId('source_id'),
  },
  (t) => [uniqueIndex('gene_aliases_uq').on(t.geneId, t.alias, t.aliasType), index('gene_aliases_alias_idx').on(t.alias)],
);

/** Variants (CLAUDE.md §235-239): coordinates always carry assembly; original + normalized kept. */
export const variants = pgTable(
  'variants',
  {
    id: ciId().primaryKey(), // CI-VAR-…
    slug: text('slug').notNull(),
    geneId: ciId('gene_id'),
    geneSymbol: text('gene_symbol'),
    name: text('name').notNull(), // e.g. "V600E", "Exon 19 Deletion", "Amplification"
    variantType: text('variant_type'), // SO-style: SNV | MNV | insertion | deletion | indel | fusion | amplification | deletion_cna | loss_of_heterozygosity | promoter_mutation | splice | expression | epigenetic | structural | other
    hgvsG: text('hgvs_g'),
    hgvsC: text('hgvs_c'),
    hgvsP: text('hgvs_p'),
    assembly: text('assembly'), // GRCh37 | GRCh38
    chromosome: text('chromosome'),
    start: integer('start'),
    end: integer('end'),
    referenceBases: text('reference_bases'),
    alternateBases: text('alternate_bases'),
    coordinates: jsonb('coordinates').$type<Array<Record<string, unknown>>>().notNull().default([]), // per-assembly list
    clinvarVariationId: text('clinvar_variation_id'),
    civicVariantId: integer('civic_variant_id'),
    dbsnpIds: text('dbsnp_ids').array().notNull().default([]),
    fusionPartners: text('fusion_partners').array().notNull().default([]), // [5' gene, 3' gene]
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('variants_slug_uq').on(t.slug), index('variants_gene_idx').on(t.geneId), index('variants_clinvar_idx').on(t.clinvarVariationId), index('variants_civic_idx').on(t.civicVariantId)],
);

export const variantAliases = pgTable(
  'variant_aliases',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    variantId: ciId('variant_id').notNull(),
    alias: text('alias').notNull(),
    sourceId: ciId('source_id'),
  },
  (t) => [uniqueIndex('variant_aliases_uq').on(t.variantId, t.alias)],
);

/** ClinVar interpretations (CLAUDE.md §10.7): structured, never flattened. */
export const variantClinicalSignificance = pgTable(
  'variant_clinical_significance',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    variantId: ciId('variant_id').notNull(),
    clinvarVariationId: text('clinvar_variation_id').notNull(),
    clinicalSignificance: text('clinical_significance').notNull(),
    reviewStatus: text('review_status'),
    starRating: integer('star_rating'),
    lastEvaluated: text('last_evaluated'),
    conditions: text('conditions').array().notNull().default([]),
    conditionCancerIds: text('condition_cancer_ids').array().notNull().default([]),
    originSimple: text('origin_simple'),
    numberSubmitters: integer('number_submitters'),
    provenanceId: integer('provenance_id').notNull(),
    ingestRunId: text('ingest_run_id'),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('variant_clinsig_uq').on(t.clinvarVariationId)],
);

export const biomarkers = pgTable(
  'biomarkers',
  {
    id: ciId().primaryKey(), // CI-BIO-…
    slug: text('slug').notNull(),
    name: text('name').notNull(),
    kind: text('kind').notNull(), // gene_mutation | protein_expression | hormone_receptor | immune_marker | msi | tmb | hrd | ctdna | methylation | signature | cell_surface | other
    geneId: ciId('gene_id'),
    ncitCode: text('ncit_code'),
    description: text('description'),
    measurement: jsonb('measurement').$type<Record<string, unknown>>().notNull().default({}), // assay/clone/scoring/thresholds (§241-243)
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('biomarkers_slug_uq').on(t.slug)],
);

/** Genomic studies / cohorts (GDC projects, cBioPortal studies…) — original study IDs preserved. */
export const genomicCohorts = pgTable(
  'genomic_cohorts',
  {
    id: ciId().primaryKey(), // CI-STUDY-…
    sourceId: ciId('source_id').notNull(),
    studyId: text('study_id').notNull(), // TCGA-PAAD
    name: text('name').notNull(),
    program: text('program'),
    primarySites: text('primary_sites').array().notNull().default([]),
    diseaseTypes: text('disease_types').array().notNull().default([]),
    cancerId: ciId('cancer_id'),
    cancerMatchType: text('cancer_match_type'),
    caseCount: integer('case_count'),
    casesWithSsm: integer('cases_with_ssm'), // denominator for mutation frequencies
    dataRelease: text('data_release'),
    accessLevel: text('access_level').notNull().default('open'),
    url: text('url'),
    provenanceId: integer('provenance_id'),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('genomic_cohorts_uq').on(t.sourceId, t.studyId), index('genomic_cohorts_cancer_idx').on(t.cancerId)],
);

/** Gene alteration frequency per cohort — denominator is mandatory (CLAUDE.md §261-262). */
export const cancerGeneFrequencies = pgTable(
  'cancer_gene_frequencies',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    cohortId: ciId('cohort_id').notNull(),
    cancerId: ciId('cancer_id'),
    geneId: ciId('gene_id'),
    geneSymbol: text('gene_symbol').notNull(),
    alterationType: text('alteration_type').notNull().default('ssm'), // ssm | cnv_gain | cnv_loss | fusion
    casesAffected: integer('cases_affected').notNull(),
    casesProfiled: integer('cases_profiled').notNull(),
    frequency: real('frequency').notNull(),
    rank: integer('rank'),
    dataRelease: text('data_release'),
    provenanceId: integer('provenance_id').notNull(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('cancer_gene_freq_uq').on(t.cohortId, t.geneSymbol, t.alterationType), index('cancer_gene_freq_cancer_idx').on(t.cancerId, t.frequency), index('cancer_gene_freq_gene_idx').on(t.geneId)],
);

export const entityEmbeddings = pgTable(
  'entity_embeddings',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    entityType: text('entity_type').notNull(),
    entityId: text('entity_id').notNull(),
    model: text('model').notNull(),
    dimensions: integer('dimensions').notNull(),
    textHash: text('text_hash').notNull(),
    embedding: text('embedding'), // stored via raw SQL cast to vector(n); model recorded on the row
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('entity_embeddings_uq').on(t.entityType, t.entityId, t.model)],
);