/**
 * Custom migration script - why?
 * Well migration scripts aren't so complex, and we haven't found a simple enough library
 * to deserve a new dependency. We like this setup because we can customize it easily
 * and it's pretty easy to understand.
 *
 * This migration system handles both custom TypeScript migrations and
 * SQL migrations generated by Drizzle.
 */

import { readdirSync, readFileSync } from 'node:fs'
import { basename, resolve } from 'node:path'

import { getDBClient } from './getDBClient.js'
import { ensureZeroDatabases } from './zero.js'

import type { PoolClient } from 'pg'

export type Migration = {
  name: string
  up?: (client: PoolClient) => Promise<void>
  /** per-migration timeout in ms, overrides defaultTimeout */
  timeout?: number
}

/** default timeout for migrations (5 seconds) */
const DEFAULT_MIGRATION_TIMEOUT = 5_000

export type MigrateOptions = {
  connectionString: string
  migrations: Record<string, () => Promise<unknown>>
  createDatabases?: string[]
  onMigrationComplete?: () => Promise<void>
  gitSha?: string
  cvrDb?: string
  changeDb?: string
  /** call process.exit(0) after migrations complete (default: false) */
  forceExit?: boolean
  /** default timeout for all migrations in ms (default: 5000) */
  defaultTimeout?: number
}

async function withTimeout<T>(promise: Promise<T>, ms: number, name: string): Promise<T> {
  let timeoutId: ReturnType<typeof setTimeout>
  const timeoutPromise = new Promise<never>((_, reject) => {
    timeoutId = setTimeout(() => {
      reject(new Error(`migration "${name}" timed out after ${ms}ms`))
    }, ms)
  })
  try {
    return await Promise.race([promise, timeoutPromise])
  } finally {
    clearTimeout(timeoutId!)
  }
}

export async function migrate(options: MigrateOptions) {
  const {
    connectionString,
    migrations,
    createDatabases = [],
    onMigrationComplete,
    forceExit = false,
    gitSha,
    cvrDb,
    changeDb,
    defaultTimeout = DEFAULT_MIGRATION_TIMEOUT,
  } = options

  // create zero + additional databases before running migrations
  await ensureZeroDatabases({ connectionString, cvrDb, changeDb })
  for (const dbUrl of createDatabases) {
    await ensureZeroDatabases({ connectionString, cvrDb: dbUrl })
  }

  const client = await getDBClient({ connectionString })

  try {
    await client.query('BEGIN')
    // force public schema - if a schema matching the db user exists (e.g. startchat),
    // postgres resolves unqualified names there first, causing tables to land in the
    // wrong schema. setting search_path ensures all CREATE TABLE etc go to public.
    await client.query(`SET search_path = public`)
    await client.query(`
      CREATE TABLE IF NOT EXISTS public.migrations (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        run_on TIMESTAMP NOT NULL DEFAULT NOW()
      )
    `)

    const appliedMigrations = await client.query('SELECT name FROM public.migrations')
    const appliedMigrationNames = new Set(appliedMigrations.rows.map((row) => row.name))

    // get TypeScript migrations
    const tsMigrationsSorted = Object.entries(migrations)
      .sort(([a], [b]) => a.localeCompare(b))
      .map(([file, run]) => ({
        name: basename(file).replace(/\.(ts|sql)$/, ''),
        run,
      }))
      .filter(({ name }) => /^[\d]+/.test(name))

    // process TypeScript migrations
    const tsMigrations: Migration[] = await Promise.all(
      tsMigrationsSorted.map(async ({ name, run }) => {
        if (appliedMigrationNames.has(name)) {
          return null
        }
        try {
          const migration = (await run()) as object
          return { ...migration, name }
        } catch (error) {
          console.error(`Failed to load TypeScript migration ${name}:`, error)
          throw error
        }
      })
    ).then((migrations) => migrations.filter(Boolean) as Migration[])

    // combine TypeScript and SQL migrations, sorted by name to ensure correct order
    const pendingMigrations = [...tsMigrations].sort((a, b) =>
      a.name.localeCompare(b.name)
    )

    if (!pendingMigrations.length) {
      await client.query('COMMIT')
    } else {
      for (const migration of pendingMigrations) {
        if (migration.up) {
          // use savepoints so "already exists" errors don't abort the transaction.
          // this handles cases where tables/columns exist but migration tracking
          // was lost (e.g. db restore, pglite data loss)
          const sp = `sp_${migration.name.replace(/\W/g, '_')}`
          await client.query(`SAVEPOINT ${sp}`)
          try {
            const timeout = migration.timeout ?? defaultTimeout
            await withTimeout(migration.up(client), timeout, migration.name)
            await client.query(`RELEASE SAVEPOINT ${sp}`)
          } catch (e: any) {
            // ROLLBACK TO SAVEPOINT can fail in its own right (e.g. when running
            // against pglite via orez's wire-protocol proxy, a parallel client
            // sharing the same backend session can corrupt the transaction
            // state, leaving us with no active transaction). if we don't catch
            // the rollback failure separately, the original migration error `e`
            // is replaced by the rollback's error, hiding what actually went
            // wrong. log the rollback failure and re-throw the original.
            try {
              await client.query(`ROLLBACK TO SAVEPOINT ${sp}`)
            } catch (rollbackErr) {
              console.error(
                `[migrate] ${migration.name}: ROLLBACK TO SAVEPOINT failed:`,
                rollbackErr
              )
              console.error(
                `[migrate] ${migration.name}: original migration error was:`,
                e
              )
              throw e
            }
            // only ignore idempotency errors - NOT all 42xxx errors!
            // 42xxx is the entire "Syntax Error or Access Rule Violation" class
            // which includes undefined_table (42P01), syntax_error (42601), etc.
            // we only want to skip migrations where objects already/don't exist:
            const idempotentErrorCodes = [
              '42710', // duplicate_object
              '42P07', // duplicate_table
              '42701', // duplicate_column
              '42P16', // invalid_table_definition (e.g. constraint already exists)
              '42704', // undefined_object (e.g. DROP CONSTRAINT on non-existent constraint)
            ]
            if (idempotentErrorCodes.includes(e?.code)) {
              console.info(
                `[migrate] ${migration.name}: ${e.message} (${e.code}), recording as applied`
              )
            } else {
              // any other error should fail the migration
              throw e
            }
          }
        }

        await client.query('INSERT INTO public.migrations (name) VALUES ($1)', [
          migration.name,
        ])
      }

      await client.query('COMMIT')
    }
  } catch (e) {
    console.error(`Migration failed, rolling back:`, e)
    await client.query('ROLLBACK')
    try {
      client.release(false)
    } catch (releaseErr) {
      console.error(`Error releasing connection after rollback:`, releaseErr)
    }
    throw e
  }

  if (onMigrationComplete) {
    await onMigrationComplete()
  }

  try {
    // don't destroy the connection forcefully - let it return to pool gracefully
    client.release(false)
  } catch (err) {
    console.error(`Error releasing connection gracefully, trying to destroy:`, err)
    try {
      client.release(true)
    } catch (destroyErr) {
      console.error(`Error destroying connection:`, destroyErr)
    }
  }

  if (forceExit) {
    // flush stdio before exiting — process.exit() doesn't wait for streams to drain
    await new Promise<void>((resolve) => {
      process.stderr.write('', () => {
        process.stdout.write('', () => resolve())
      })
    })
    process.exit(0)
  }
}

/** load migration files from a directory (for non-vite contexts like bun scripts) */
export function loadMigrationsFromDir(
  dir: string
): Record<string, () => Promise<unknown>> {
  const files = readdirSync(dir)
    .filter((f) => /^\d+/.test(f) && (f.endsWith('.ts') || f.endsWith('.sql')))
    .sort()

  // deduplicate: prefer .ts over .sql for same base name
  const seen = new Set<string>()
  const deduped: string[] = []
  for (const f of files) {
    if (f.endsWith('.ts')) {
      seen.add(f.replace(/\.ts$/, ''))
      deduped.push(f)
    }
  }
  for (const f of files) {
    if (f.endsWith('.sql') && !seen.has(f.replace(/\.sql$/, ''))) {
      deduped.push(f)
    }
  }
  deduped.sort()

  return Object.fromEntries(
    deduped.map((f) => {
      if (f.endsWith('.sql')) {
        return [
          `./${f}`,
          async () => {
            const sql = readFileSync(resolve(dir, f), 'utf-8')
            return {
              up: async (client: PoolClient) => {
                for (const stmt of sql.split('--> statement-breakpoint')) {
                  const trimmed = stmt.trim()
                  if (trimmed) await client.query(trimmed)
                }
              },
            }
          },
        ]
      }
      return [`./${f}`, () => import(/* @vite-ignore */ resolve(dir, f))]
    })
  )
}