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)`;
}

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);
    });
}