/**
 * Migration Bridge between Drizzle and postgres.do
 *
 * This module provides integration between Drizzle Kit's migration system
 * and postgres.do's migration infrastructure. It enables:
 *
 * 1. Converting Drizzle migrations to postgres.do format
 * 2. Syncing Drizzle journal with DO version tracking
 * 3. Supporting both migration systems simultaneously
 *
 * @example
 * ```typescript
 * import { createMigrationBridge } from 'postgres.do/drizzle'
 * import bundledMigrations from './drizzle-bundle'
 *
 * // Create a bridge that converts Drizzle migrations to postgres.do format
 * const bridge = createMigrationBridge(bundledMigrations)
 *
 * // Get migrations in postgres.do format
 * const migrations = bridge.getMigrations()
 *
 * // Or use with AutoMigrator
 * const migrator = createAutoMigrator({
 *   migrations: bridge.getMigrations(),
 * })
 * ```
 */

import type {
  DrizzleJournal,
  DrizzleJournalEntry,
  BundledDrizzleMigrations,
} from './drizzle-migrator.js'

/**
 * postgres.do migration format
 */
export interface PostgresDoMigration {
  /** Unique migration identifier */
  id: string
  /** Human-readable name */
  name: string
  /** Version number for ordering */
  version: number
  /** Forward migration SQL */
  up: string
  /** Rollback SQL (optional) */
  down?: string
  /** Whether this migration is reversible */
  isReversible?: boolean
  /** Tags for categorization */
  tags?: string[]
  /** Whether to run in a transaction */
  transactional?: boolean
  /** Original Drizzle metadata */
  drizzle?: {
    idx: number
    hash: string
    tag: string
    when: number
  }
}

/**
 * Migration bridge configuration
 */
export interface MigrationBridgeConfig {
  /**
   * Bundled Drizzle migrations
   */
  migrations: BundledDrizzleMigrations

  /**
   * Optional down migrations keyed by migration tag
   * Drizzle doesn't generate down migrations by default,
   * but you can provide them manually.
   */
  downMigrations?: Record<string, string> | undefined

  /**
   * Prefix for migration IDs
   * Default: empty (uses Drizzle's NNNN_tag format)
   */
  idPrefix?: string | undefined

  /**
   * Version offset for postgres.do versioning
   * Default: 0
   */
  versionOffset?: number | undefined
}

/**
 * Generate migration hash from content
 */
function generateHash(content: string): string {
  let hash = 0
  for (let i = 0; i < content.length; i++) {
    const char = content.charCodeAt(i)
    hash = ((hash << 5) - hash) + char
    hash = hash & hash
  }
  return Math.abs(hash).toString(16).padStart(8, '0')
}

/**
 * Clean Drizzle SQL by removing breakpoint comments
 */
function cleanDrizzleSql(sql: string): string {
  return sql
    .replace(/-->\s*statement-breakpoint\s*/g, '\n')
    .trim()
}

/**
 * Convert tag to human-readable name
 */
function tagToName(tag: string): string {
  return tag
    .replace(/_/g, ' ')
    .replace(/(^|\s)\w/g, (c) => c.toUpperCase())
}

/**
 * Migration Bridge
 *
 * Converts Drizzle migrations to postgres.do format, enabling
 * interoperability between both migration systems.
 */
export class MigrationBridge {
  private config: Required<Omit<MigrationBridgeConfig, 'migrations' | 'downMigrations'>>
  private migrations: BundledDrizzleMigrations
  private downMigrations: Record<string, string>
  private cachedMigrations: PostgresDoMigration[] | null = null

  constructor(config: MigrationBridgeConfig) {
    this.migrations = config.migrations
    this.downMigrations = config.downMigrations ?? {}
    this.config = {
      idPrefix: config.idPrefix ?? '',
      versionOffset: config.versionOffset ?? 0,
    }
  }

  /**
   * Get all migrations in postgres.do format
   */
  getMigrations(): PostgresDoMigration[] {
    if (this.cachedMigrations) {
      return this.cachedMigrations
    }

    const { journal, migrations } = this.migrations
    const { idPrefix, versionOffset = 0 } = this.config

    this.cachedMigrations = journal.entries
      .sort((a, b) => a.idx - b.idx)
      .map((entry) => {
        const folderName = `${entry.idx.toString().padStart(4, '0')}_${entry.tag}`
        const sql = migrations[folderName] || migrations[entry.tag]

        if (!sql) {
          throw new Error(`Missing migration SQL for: ${folderName}`)
        }

        const cleanSql = cleanDrizzleSql(sql)
        const hash = generateHash(`${folderName}:${sql}`)
        const down = this.downMigrations[entry.tag] || this.downMigrations[folderName]
        const idPrefixStr = idPrefix ?? ''

        const migration: PostgresDoMigration = {
          id: `${idPrefixStr}${folderName}`,
          name: tagToName(entry.tag),
          version: entry.idx + 1 + versionOffset, // 1-indexed
          up: cleanSql,
          tags: ['drizzle'],
          transactional: entry.breakpoints !== false,
          drizzle: {
            idx: entry.idx,
            hash,
            tag: entry.tag,
            when: entry.when,
          },
        }

        if (down) {
          migration.down = cleanDrizzleSql(down)
          migration.isReversible = true
        } else {
          migration.isReversible = false
        }

        return migration
      })

    return this.cachedMigrations
  }

  /**
   * Get a specific migration by ID
   */
  getMigration(id: string): PostgresDoMigration | undefined {
    return this.getMigrations().find((m) => m.id === id)
  }

  /**
   * Get a migration by Drizzle tag
   */
  getMigrationByTag(tag: string): PostgresDoMigration | undefined {
    return this.getMigrations().find((m) => m.drizzle?.tag === tag)
  }

  /**
   * Get a migration by version
   */
  getMigrationByVersion(version: number): PostgresDoMigration | undefined {
    return this.getMigrations().find((m) => m.version === version)
  }

  /**
   * Get the latest version number
   */
  getLatestVersion(): number {
    const migrations = this.getMigrations()
    if (migrations.length === 0) return 0
    return Math.max(...migrations.map((m) => m.version))
  }

  /**
   * Get migrations after a specific version
   */
  getMigrationsAfter(version: number): PostgresDoMigration[] {
    return this.getMigrations().filter((m) => m.version > version)
  }

  /**
   * Get the Drizzle journal
   */
  getJournal(): DrizzleJournal {
    return this.migrations.journal
  }

  /**
   * Export back to Drizzle journal format
   *
   * Useful for keeping Drizzle's journal in sync after manual changes.
   */
  exportToJournal(): DrizzleJournal {
    const migrations = this.getMigrations()

    const versionOffset = this.config.versionOffset ?? 0
    const entries: DrizzleJournalEntry[] = migrations.map((m) => ({
      idx: m.drizzle?.idx ?? (m.version - 1 - versionOffset),
      version: m.drizzle?.when?.toString() ?? new Date().toISOString().replace(/[-:T.Z]/g, '').substring(0, 14),
      when: m.drizzle?.when ?? Date.now(),
      tag: m.drizzle?.tag ?? m.id.replace(/^\d+_/, ''),
      breakpoints: m.transactional !== false,
    }))

    return {
      version: this.migrations.journal.version || '7',
      dialect: this.migrations.journal.dialect || 'postgresql',
      entries,
    }
  }

  /**
   * Validate that all migrations have SQL files
   */
  validate(): { valid: boolean; errors: string[] } {
    const errors: string[] = []

    for (const entry of this.migrations.journal.entries) {
      const folderName = `${entry.idx.toString().padStart(4, '0')}_${entry.tag}`
      const sql = this.migrations.migrations[folderName] || this.migrations.migrations[entry.tag]

      if (!sql) {
        errors.push(`Missing SQL file for migration: ${folderName}`)
      }
    }

    return {
      valid: errors.length === 0,
      errors,
    }
  }

  /**
   * Add down migrations
   */
  addDownMigration(tagOrId: string, downSql: string): void {
    this.downMigrations[tagOrId] = downSql
    // Invalidate cache
    this.cachedMigrations = null
  }

  /**
   * Generate migration SQL statistics
   */
  getStats(): {
    totalMigrations: number
    reversibleCount: number
    nonReversibleCount: number
    totalSqlStatements: number
  } {
    const migrations = this.getMigrations()

    let totalSqlStatements = 0
    for (const m of migrations) {
      // Count statements by splitting on semicolons
      const statements = m.up
        .split(/;\s*(?=(?:[^']*'[^']*')*[^']*$)/)
        .filter((s) => s.trim().length > 0)
      totalSqlStatements += statements.length
    }

    return {
      totalMigrations: migrations.length,
      reversibleCount: migrations.filter((m) => m.isReversible).length,
      nonReversibleCount: migrations.filter((m) => !m.isReversible).length,
      totalSqlStatements,
    }
  }
}

/**
 * Create a migration bridge instance
 *
 * @example
 * ```typescript
 * import { createMigrationBridge } from 'postgres.do/drizzle'
 *
 * const bridge = createMigrationBridge({
 *   migrations: bundledMigrations,
 *   downMigrations: {
 *     'create_users': 'DROP TABLE users;',
 *     'add_posts': 'DROP TABLE posts;',
 *   },
 * })
 *
 * const postgresDoMigrations = bridge.getMigrations()
 * ```
 */
export function createMigrationBridge(config: MigrationBridgeConfig): MigrationBridge {
  return new MigrationBridge(config)
}

/**
 * Quick conversion from Drizzle migrations to postgres.do format
 */
export function convertDrizzleToPostgresDo(
  bundled: BundledDrizzleMigrations,
  downMigrations?: Record<string, string>
): PostgresDoMigration[] {
  const bridge = createMigrationBridge({
    migrations: bundled,
    downMigrations,
  })
  return bridge.getMigrations()
}

/**
 * Create a combined migrator that uses both Drizzle and postgres.do tracking
 *
 * This is useful when you want to leverage Drizzle's migration generation
 * while using postgres.do's multi-database version tracking.
 *
 * @example
 * ```typescript
 * import { createCombinedMigrator } from 'postgres.do/drizzle'
 * import bundledMigrations from './drizzle-bundle'
 *
 * const migrator = createCombinedMigrator({
 *   drizzle: bundledMigrations,
 *   tableName: '_combined_migrations',
 * })
 *
 * // In your DO
 * await migrator.ensureMigrated(pglite)
 * ```
 */
export interface CombinedMigratorConfig {
  /**
   * Bundled Drizzle migrations
   */
  drizzle: BundledDrizzleMigrations

  /**
   * Optional down migrations
   */
  downMigrations?: Record<string, string>

  /**
   * Table name for version tracking
   * Default: '_migrations'
   */
  tableName?: string

  /**
   * Enable debug logging
   */
  debug?: boolean

  /**
   * Callback on progress
   */
  onProgress?: (event: {
    migration: PostgresDoMigration
    index: number
    total: number
    phase: string
    durationMs?: number
    error?: string
  }) => void
}

/**
 * Query executor interface
 */
interface QueryExecutor {
  query<T = unknown>(sql: string, params?: unknown[]): Promise<{ rows: T[] }>
}

/**
 * Combined migrator that merges Drizzle and postgres.do tracking
 */
export async function runCombinedMigrations(
  executor: QueryExecutor,
  config: CombinedMigratorConfig
): Promise<{
  success: boolean
  appliedCount: number
  skippedCount: number
  durationMs: number
  error?: string
}> {
  const bridge = createMigrationBridge({
    migrations: config.drizzle,
    downMigrations: config.downMigrations,
  })

  const migrations = bridge.getMigrations()
  const tableName = config.tableName || '_migrations'
  const startTime = performance.now()
  let appliedCount = 0
  let skippedCount = 0

  try {
    // Create tracking table (execute separately for PGLite compatibility)
    await executor.query(`
      CREATE TABLE IF NOT EXISTS "${tableName}" (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        version INTEGER NOT NULL UNIQUE,
        checksum TEXT NOT NULL,
        applied_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
        execution_time_ms INTEGER NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT 'applied',
        drizzle_hash TEXT,
        drizzle_tag TEXT
      )
    `)
    await executor.query(
      `CREATE INDEX IF NOT EXISTS idx_${tableName.replace(/"/g, '')}_version ON "${tableName}" (version)`
    )

    // Get applied versions
    const applied = await executor.query<{ version: number }>(
      `SELECT version FROM "${tableName}" WHERE status = 'applied'`
    )
    const appliedVersions = new Set(applied.rows.map((r) => r.version))

    // Apply pending migrations
    for (let i = 0; i < migrations.length; i++) {
      const migration = migrations[i]!

      if (appliedVersions.has(migration.version)) {
        skippedCount++
        config.onProgress?.({
          migration,
          index: i,
          total: migrations.length,
          phase: 'skipped',
        })
        continue
      }

      config.onProgress?.({
        migration,
        index: i,
        total: migrations.length,
        phase: 'starting',
      })

      const migrationStart = performance.now()

      try {
        // Execute migration
        const statements = migration.up
          .split(/;\s*(?=(?:[^']*'[^']*')*[^']*$)/)
          .map((s) => s.trim())
          .filter((s) => s.length > 0)

        for (const stmt of statements) {
          await executor.query(stmt + ';')
        }

        // Record migration
        const execTime = Math.round(performance.now() - migrationStart)
        const checksum = generateHash(migration.up)

        await executor.query(
          `INSERT INTO "${tableName}" (id, name, version, checksum, execution_time_ms, drizzle_hash, drizzle_tag)
           VALUES ($1, $2, $3, $4, $5, $6, $7)`,
          [
            migration.id,
            migration.name,
            migration.version,
            checksum,
            execTime,
            migration.drizzle?.hash ?? null,
            migration.drizzle?.tag ?? null,
          ]
        )

        appliedCount++

        config.onProgress?.({
          migration,
          index: i,
          total: migrations.length,
          phase: 'completed',
          durationMs: execTime,
        })

        if (config.debug) {
          console.log(`[CombinedMigrator] Applied ${migration.id} in ${execTime}ms`)
        }
      } catch (error) {
        const execTime = Math.round(performance.now() - migrationStart)
        const errorMsg = error instanceof Error ? error.message : String(error)

        config.onProgress?.({
          migration,
          index: i,
          total: migrations.length,
          phase: 'failed',
          durationMs: execTime,
          error: errorMsg,
        })

        throw error
      }
    }

    const totalTime = Math.round(performance.now() - startTime)

    return {
      success: true,
      appliedCount,
      skippedCount,
      durationMs: totalTime,
    }
  } catch (error) {
    const totalTime = Math.round(performance.now() - startTime)
    const errorMsg = error instanceof Error ? error.message : String(error)

    return {
      success: false,
      appliedCount,
      skippedCount,
      durationMs: totalTime,
      error: errorMsg,
    }
  }
}