import path from 'node:path'
import { fileURLToPath } from 'node:url'
import { sql } from 'drizzle-orm'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import { baseEnv, createLogger, databaseEnv, loadEnv } from '@znet/shared'
import { createDb } from './db.js'
import { applyGrants } from './grants.js'

export const MIGRATIONS_DIR = path.resolve(path.dirname(fileURLToPath(import.meta.url)), '../migrations')

/** Apply pending migrations, then (re)apply role grants. Run as the schema owner. */
export async function runMigrations(url: string): Promise<string[]> {
  const { db, close } = createDb(url, { max: 1, applicationName: 'mcp-znet-migrate' })
  try {
    // pg_trgm is a trusted extension (PG13+), so the database owner can create it.
    await db.execute(sql`CREATE EXTENSION IF NOT EXISTS pg_trgm`)
    await migrate(db, { migrationsFolder: MIGRATIONS_DIR })
    return await applyGrants(db)
  } finally {
    await close()
  }
}

if (import.meta.url === `file://${process.argv[1]}`) {
  const env = loadEnv(baseEnv.extend(databaseEnv.shape))
  const log = createLogger('migrate', env.LOG_LEVEL)
  runMigrations(env.DATABASE_URL)
    .then(roles => log.info({ grantedRoles: roles }, 'migrations applied'))
    .catch(err => {
      log.error(err)
      process.exit(1)
    })
}