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

/** ClinicalTrials.gov studies (CLAUDE.md §10.5). */
export const clinicalTrials = pgTable(
  'clinical_trials',
  {
    id: ciId().primaryKey(), // CI-TRIAL-…
    nctId: text('nct_id').notNull(),
    briefTitle: text('brief_title').notNull(),
    officialTitle: text('official_title'),
    acronym: text('acronym'),
    studyType: text('study_type'), // INTERVENTIONAL | OBSERVATIONAL | EXPANDED_ACCESS
    phases: text('phases').array().notNull().default([]), // EARLY_PHASE1 | PHASE1 | PHASE2 | PHASE3 | PHASE4 | NA
    overallStatus: text('overall_status'), // RECRUITING | ACTIVE_NOT_RECRUITING | COMPLETED | …
    whyStopped: text('why_stopped'),
    startDate: text('start_date'),
    primaryCompletionDate: text('primary_completion_date'),
    completionDate: text('completion_date'),
    firstPostedDate: text('first_posted_date'),
    lastUpdatePostedDate: text('last_update_posted_date'),
    resultsFirstPostedDate: text('results_first_posted_date'),
    hasResults: boolean('has_results').notNull().default(false),
    enrollmentCount: integer('enrollment_count'),
    enrollmentType: text('enrollment_type'),
    leadSponsor: text('lead_sponsor'),
    leadSponsorClass: text('lead_sponsor_class'),
    collaborators: text('collaborators').array().notNull().default([]),
    conditions: text('conditions').array().notNull().default([]), // raw free text
    keywords: text('keywords').array().notNull().default([]),
    interventions: jsonb('interventions').$type<Array<{ type: string; name: string; description?: string; otherNames?: string[] }>>().notNull().default([]),
    arms: jsonb('arms').$type<Array<Record<string, unknown>>>().notNull().default([]),
    primaryOutcomes: jsonb('primary_outcomes').$type<Array<Record<string, unknown>>>().notNull().default([]),
    secondaryOutcomes: jsonb('secondary_outcomes').$type<Array<Record<string, unknown>>>().notNull().default([]),
    eligibility: jsonb('eligibility').$type<Record<string, unknown>>().notNull().default({}),
    sex: text('sex'),
    minimumAge: text('minimum_age'),
    maximumAge: text('maximum_age'),
    countries: text('countries').array().notNull().default([]),
    locationsCount: integer('locations_count').notNull().default(0),
    references: jsonb('references').$type<Array<{ pmid?: string; type?: string; citation?: string }>>().notNull().default([]),
    briefSummary: text('brief_summary'),
    isOncology: boolean('is_oncology').notNull().default(true),
    sourceRecordId: integer('source_record_id'),
    ingestRunId: text('ingest_run_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('clinical_trials_nct_uq').on(t.nctId),
    index('clinical_trials_status_idx').on(t.overallStatus),
    index('clinical_trials_updated_idx').on(t.lastUpdatePostedDate),
    index('clinical_trials_sponsor_idx').on(t.leadSponsor),
  ],
);

/** trial → cancer mapping from free-text conditions (CLAUDE.md §10.5, §221). */
export const trialConditions = pgTable(
  'trial_conditions',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    trialId: ciId('trial_id').notNull(),
    conditionText: text('condition_text').notNull(),
    normalized: text('normalized').notNull(),
    cancerId: ciId('cancer_id'),
    matchType: text('match_type').notNull().default('UNRESOLVED'),
    confidence: real('confidence'),
  },
  (t) => [uniqueIndex('trial_conditions_uq').on(t.trialId, t.normalized), index('trial_conditions_cancer_idx').on(t.cancerId), index('trial_conditions_norm_idx').on(t.normalized)],
);

export const trialInterventions = pgTable(
  'trial_interventions',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    trialId: ciId('trial_id').notNull(),
    name: text('name').notNull(),
    normalized: text('normalized').notNull(),
    interventionType: text('intervention_type'), // DRUG | BIOLOGICAL | DEVICE | PROCEDURE | RADIATION | BEHAVIORAL | OTHER
    drugId: ciId('drug_id'),
    matchType: text('match_type').notNull().default('UNRESOLVED'),
  },
  (t) => [uniqueIndex('trial_interventions_uq').on(t.trialId, t.normalized), index('trial_interventions_drug_idx').on(t.drugId)],
);

export const trialLocations = pgTable(
  'trial_locations',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    trialId: ciId('trial_id').notNull(),
    facility: text('facility'),
    city: text('city'),
    state: text('state'),
    zip: text('zip'),
    country: text('country'),
    status: text('status'),
    lat: real('lat'),
    lng: real('lng'),
  },
  (t) => [index('trial_locations_trial_idx').on(t.trialId), index('trial_locations_country_idx').on(t.country)],
);

/** Daily trial-creation pulse (derived; CLAUDE.md §332). */
export const trialPulse = pgTable(
  'trial_pulse',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    day: date('day').notNull(),
    cancerId: ciId('cancer_id'),
    phase: text('phase'),
    newTrials: integer('new_trials').notNull(),
    computedAt: updatedAt(),
  },
  (t) => [uniqueIndex('trial_pulse_uq').on(t.day, t.cancerId, t.phase)],
);