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

/** Canonical cancer entity (CLAUDE.md §5). One row per recognized disease concept, never per alias. */
export const cancers = pgTable(
  'cancers',
  {
    id: ciId().primaryKey(), // CI-CAN-…
    slug: text('slug').notNull(),
    canonicalName: text('canonical_name').notNull(),
    shortName: text('short_name'),
    entityType: text('entity_type').notNull().default('cancer'), // cancer | cancer_family | histology | subtype | molecular_subtype | hematologic_malignancy | precursor_condition | other
    malignant: boolean('malignant').notNull().default(true),
    solidTumor: boolean('solid_tumor').notNull().default(true),
    hematologic: boolean('hematologic').notNull().default(false),
    pediatricRelevant: boolean('pediatric_relevant').notNull().default(false),
    rareCancer: boolean('rare_cancer'), // null = unknown (no incidence data yet)
    topLevel: boolean('top_level').notNull().default(false), // member of the mutually exclusive global ranking set (§247)
    description: text('description'),
    descriptionProvenanceId: integer('description_provenance_id'),
    primaryNcitCode: text('primary_ncit_code'),
    primaryOncotreeCode: text('primary_oncotree_code'),
    depth: integer('depth').notNull().default(0), // depth in the NCIt-derived hierarchy from root "Neoplasm"
    status: text('status').notNull().default('active'), // active | deprecated | merged
    mergedInto: ciId('merged_into'),
    deprecatedReason: text('deprecated_reason'),
    classificationVersion: text('classification_version'),
    semanticTypes: text('semantic_types').array().notNull().default([]),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('cancers_slug_uq').on(t.slug),
    uniqueIndex('cancers_ncit_uq').on(t.primaryNcitCode),
    index('cancers_name_idx').on(t.canonicalName),
    index('cancers_type_idx').on(t.entityType, t.malignant, t.topLevel),
  ],
);

export const cancerAliases = pgTable(
  'cancer_aliases',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    cancerId: ciId('cancer_id').notNull(),
    alias: text('alias').notNull(),
    normalized: text('normalized').notNull(),
    aliasType: text('alias_type').notNull().default('synonym'), // preferred | synonym | abbreviation | historical | deprecated | display
    sourceId: ciId('source_id'),
    sourceTerminology: text('source_terminology'), // NCIt synonym source (e.g. CTRP, caDSR) or connector id
    language: text('language').notNull().default('en'),
  },
  (t) => [uniqueIndex('cancer_aliases_uq').on(t.cancerId, t.normalized, t.aliasType), index('cancer_aliases_norm_idx').on(t.normalized)],
);

/** Multi-dimensional hierarchy (CLAUDE.md §4): several trees coexist. */
export const cancerHierarchy = pgTable(
  'cancer_hierarchy',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    parentId: ciId('parent_id').notNull(),
    childId: ciId('child_id').notNull(),
    hierarchyType: text('hierarchy_type').notNull(), // ncit | oncotree | anatomical | histological | molecular | who | icd | seer
    sourceId: ciId('source_id'),
  },
  (t) => [uniqueIndex('cancer_hierarchy_uq').on(t.parentId, t.childId, t.hierarchyType), index('cancer_hierarchy_child_idx').on(t.childId)],
);

/** Cross-reference codes (CLAUDE.md §220-221, §347): searchable, never buried in JSON. */
export const cancerCodes = pgTable(
  'cancer_codes',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    cancerId: ciId('cancer_id').notNull(),
    system: text('system').notNull(), // ncit | icd10 | icd10cm | icdo_topography | icdo_morphology | doid | oncotree | umls | mesh | mondo | seer_site | efo | orphanet | gdc_project
    code: text('code').notNull(),
    matchType: text('match_type').notNull().default('EXACT_IDENTIFIER'),
    sourceId: ciId('source_id'),
    validFrom: text('valid_from'),
    validTo: text('valid_to'),
  },
  (t) => [uniqueIndex('cancer_codes_uq').on(t.cancerId, t.system, t.code), index('cancer_codes_lookup_idx').on(t.system, t.code)],
);

export const anatomicalSites = pgTable(
  'anatomical_sites',
  {
    id: ciId().primaryKey(), // CI-ANAT-…
    name: text('name').notNull(),
    slug: text('slug').notNull(),
    ncitCode: text('ncit_code'),
    uberonId: text('uberon_id'),
    parentId: ciId('parent_id'),
    system: text('system'), // e.g. Digestive, Respiratory, Hematopoietic
  },
  (t) => [uniqueIndex('anatomical_sites_slug_uq').on(t.slug)],
);

export const cancerAnatomy = pgTable(
  'cancer_anatomy',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    cancerId: ciId('cancer_id').notNull(),
    siteId: ciId('site_id').notNull(),
    relation: text('relation').notNull().default('primary'), // primary | metastatic
    sourceId: ciId('source_id'),
  },
  (t) => [uniqueIndex('cancer_anatomy_uq').on(t.cancerId, t.siteId, t.relation)],
);

/** Canonical geography (CLAUDE.md §117). */
export const geographies = pgTable(
  'geographies',
  {
    id: ciId().primaryKey(), // CI-GEO-…
    slug: text('slug').notNull(),
    name: text('name').notNull(),
    kind: text('kind').notNull(), // world | region | who_region | country | subdivision
    iso2: text('iso2'),
    iso3: text('iso3'),
    parentId: ciId('parent_id'),
    whoRegion: text('who_region'),
    population: integer('population'),
    populationYear: integer('population_year'),
  },
  (t) => [uniqueIndex('geographies_slug_uq').on(t.slug), index('geographies_iso3_idx').on(t.iso3)],
);

/** Cohort definitions (CLAUDE.md §218): attribute combinations that are not taxonomy nodes. */
export const cohortDefinitions = pgTable('cohort_definitions', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  name: text('name').notNull(),
  cancerId: ciId('cancer_id').notNull(),
  biomarkerIds: text('biomarker_ids').array().notNull().default([]),
  variantIds: text('variant_ids').array().notNull().default([]),
  stage: text('stage'),
  attributes: jsonb('attributes').$type<Record<string, unknown>>().notNull().default({}),
  confidence: real('confidence'),
  createdAt: createdAt(),
});