import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { fileURLToPath } from 'node:url';
import path from 'node:path';
import { getDb, getSql, closeDb } from './client.js';

/**
 * Runs SQL migrations from ./migrations (CLAUDE.md §284). Extensions are created first because
 * drizzle-kit does not manage them; pgvector is optional and only enabled when available.
 */
export async function runMigrations(): Promise<void> {
  const sql = getSql({ max: 1 });
  await sql`CREATE EXTENSION IF NOT EXISTS pg_trgm`;
  await sql`CREATE EXTENSION IF NOT EXISTS unaccent`;
  const [vec] = await sql`SELECT 1 AS ok FROM pg_available_extensions WHERE name = 'vector'`;
  if (vec) await sql`CREATE EXTENSION IF NOT EXISTS vector`;
  else console.warn('[migrate] pgvector not available — semantic search disabled until installed');
  const here = path.dirname(fileURLToPath(import.meta.url));
  await migrate(getDb({ max: 1 }), { migrationsFolder: path.resolve(here, '../migrations') });
  // Trigram indexes for fuzzy search (not expressible in drizzle schema).
  await sql`CREATE INDEX IF NOT EXISTS cancer_aliases_trgm_idx ON cancer_aliases USING gin (normalized gin_trgm_ops)`;
  await sql`CREATE INDEX IF NOT EXISTS cancers_name_trgm_idx ON cancers USING gin (lower(canonical_name) gin_trgm_ops)`;
  await sql`CREATE INDEX IF NOT EXISTS genes_symbol_trgm_idx ON genes USING gin (lower(symbol) gin_trgm_ops)`;
  await sql`CREATE INDEX IF NOT EXISTS drugs_name_trgm_idx ON drugs USING gin (lower(name) gin_trgm_ops)`;
  await sql`CREATE INDEX IF NOT EXISTS trials_title_trgm_idx ON clinical_trials USING gin (lower(brief_title) gin_trgm_ops)`;
  await sql`CREATE INDEX IF NOT EXISTS publications_title_trgm_idx ON publications USING gin (lower(title) gin_trgm_ops)`;
  await createPerformanceIndexes(sql);
}

/**
 * Performance indexes reviewed 2026-09-08 (docs/schema-changes-ops.md) — idempotent, applied after
 * every migration. GIN array indexes serve the containment form (`gene_ids @> ARRAY[$1]::text[]`,
 * used by the ranking counters); `x = ANY(array_column)` cannot use them. text_pattern_ops makes
 * `normalized LIKE 'prefix%'` indexable under the en_US collation the databases are created with.
 */
export async function createPerformanceIndexes(sql: ReturnType<typeof getSql>): Promise<void> {
  // CIViC evidence: per-gene / per-variant / per-therapy roll-ups (counters, gene & drug pages).
  await sql`CREATE INDEX IF NOT EXISTS civic_evidence_gene_ids_gin ON civic_evidence_items USING gin (gene_ids)`;
  await sql`CREATE INDEX IF NOT EXISTS civic_evidence_variant_ids_gin ON civic_evidence_items USING gin (variant_ids)`;
  await sql`CREATE INDEX IF NOT EXISTS civic_evidence_therapy_ids_gin ON civic_evidence_items USING gin (therapy_ids)`;
  // Trials: descendant scope → trials semi-join (index-only) and status/type facets.
  // Deliberately NO (overall_status, last_update_posted_date) index: with `ORDER BY … LIMIT 20` the
  // planner walked that ordered index for small scopes (2 ms → 116 ms measured) instead of the
  // trial_conditions semi-join.
  await sql`CREATE INDEX IF NOT EXISTS trial_conditions_cancer_trial_idx ON trial_conditions (cancer_id, trial_id)`;
  await sql`CREATE INDEX IF NOT EXISTS clinical_trials_status_type_idx ON clinical_trials (overall_status, study_type)`;
  await sql`DROP INDEX IF EXISTS clinical_trials_status_updated_idx`;
  // Aliases: prefix lookups (`normalized LIKE 'glioblastoma%'`) in search / cancers?q=.
  await sql`CREATE INDEX IF NOT EXISTS cancer_aliases_norm_pattern_idx ON cancer_aliases (normalized text_pattern_ops)`;
  // Ops: latest run per connector (doctor, anomaly guard baseline).
  await sql`CREATE INDEX IF NOT EXISTS ingest_runs_connector_status_idx ON ingest_runs (connector_id, status, started_at DESC)`;
}

const isMain = process.argv[1] && path.resolve(process.argv[1]) === fileURLToPath(import.meta.url);
if (isMain) {
  runMigrations()
    .then(async () => {
      console.log('[migrate] done');
      await closeDb();
    })
    .catch(async (err) => {
      console.error('[migrate] failed', err);
      await closeDb();
      process.exit(1);
    });
}