/**
 * Drizzle Migrations Integration for postgres.do
 *
 * This module provides seamless integration between Drizzle Kit's migration
 * system and postgres.do's multi-database Durable Object model.
 *
 * Key Features:
 * - Convert Drizzle migrations to postgres.do format
 * - Auto-run migrations on DO initialization
 * - Support drizzle-kit generate workflow
 * - Sync Drizzle journal with DO version tracking
 *
 * @example
 * ```typescript
 * import { createDrizzleMigrator } from 'postgres.do/drizzle'
 * import bundledMigrations from './drizzle-bundle'
 *
 * const migrator = createDrizzleMigrator({
 *   migrations: bundledMigrations,
 *   autoRun: true,
 * })
 *
 * // In your DO
 * class PostgresDO {
 *   async fetch(request: Request) {
 *     await migrator.ensureMigrated(this.pglite)
 *     // Handle request...
 *   }
 * }
 * ```
 */

import type { MigrationConfig as _MigrationConfig } from 'drizzle-orm/migrator'

/**
 * Drizzle journal entry format
 */
export interface DrizzleJournalEntry {
  idx: number
  version: string
  when: number
  tag: string
  breakpoints?: boolean
}

/**
 * Drizzle migrations journal format
 */
export interface DrizzleJournal {
  version: string
  dialect: 'postgresql' | 'mysql' | 'sqlite'
  entries: DrizzleJournalEntry[]
}

/**
 * Migration record for tracking applied migrations
 */
export interface DrizzleMigrationRecord {
  /** Migration hash/ID */
  hash: string
  /** Timestamp when applied */
  created_at: number
}

/**
 * Bundled Drizzle migrations format for Cloudflare Workers
 *
 * Since file system access is not available in Workers, migrations
 * need to be bundled at build time.
 */
export interface BundledDrizzleMigrations {
  /** The migrations journal */
  journal: DrizzleJournal
  /** SQL content keyed by folder name (e.g., '0000_init') */
  migrations: Record<string, string>
}

/**
 * Migration result
 */
export interface DrizzleMigrationResult {
  /** Whether all migrations succeeded */
  success: boolean
  /** Number of migrations applied */
  appliedCount: number
  /** Number of migrations skipped (already applied) */
  skippedCount: number
  /** Total duration in milliseconds */
  durationMs: number
  /** Applied migration hashes */
  applied: string[]
  /** Error if failed */
  error?: string
}

/**
 * Progress event for migrations
 */
export interface DrizzleMigrationProgressEvent {
  /** Current migration being processed */
  migration: {
    hash: string
    tag: string
    sql: string
  }
  /** Index of current migration */
  index: number
  /** Total migrations to process */
  total: number
  /** Phase of migration */
  phase: 'starting' | 'executing' | 'completed' | 'failed' | 'skipped'
  /** Duration in ms (on completion) */
  durationMs?: number
  /** Error message if failed */
  error?: string
}

/**
 * Configuration for Drizzle migrator
 */
export interface DrizzleMigratorConfig {
  /**
   * Bundled migrations from drizzle-kit generate
   */
  migrations: BundledDrizzleMigrations

  /**
   * Name of the migrations tracking table
   * Default: '__drizzle_migrations'
   */
  tableName?: string

  /**
   * Whether to run migrations automatically on ensureMigrated()
   * Default: true
   */
  autoRun?: boolean

  /**
   * Validate migration checksums
   * Default: true
   */
  checksumValidation?: boolean

  /**
   * Callback for migration progress
   */
  onProgress?: (event: DrizzleMigrationProgressEvent) => void

  /**
   * Callback when all migrations complete
   */
  onComplete?: (result: DrizzleMigrationResult) => void

  /**
   * Callback on migration error
   */
  onError?: (error: Error) => void

  /**
   * Enable debug logging
   * Default: false
   */
  debug?: boolean
}

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

/**
 * Migrator state
 */
export type DrizzleMigratorState = 'idle' | 'running' | 'completed' | 'failed'

/**
 * Generate a migration hash from SQL content
 */
function generateMigrationHash(sql: string): string {
  // Simple hash function for migration content
  let hash = 0
  for (let i = 0; i < sql.length; i++) {
    const char = sql.charCodeAt(i)
    hash = ((hash << 5) - hash) + char
    hash = hash & hash // Convert to 32-bit integer
  }
  return Math.abs(hash).toString(16).padStart(8, '0')
}

/**
 * Create the migrations tracking table SQL
 */
function getCreateTableSQL(tableName: string): string {
  return `
    CREATE TABLE IF NOT EXISTS "${tableName}" (
      id SERIAL PRIMARY KEY,
      hash TEXT NOT NULL UNIQUE,
      created_at BIGINT NOT NULL DEFAULT (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT
    );
  `
}

/**
 * Drizzle Migrator for postgres.do
 *
 * Provides automatic migration management for Drizzle ORM schemas
 * in postgres.do's Durable Object environment.
 */
export class DrizzleMigrator {
  private config: Required<Omit<DrizzleMigratorConfig, 'migrations' | 'onProgress' | 'onComplete' | 'onError'>>
  private migrations: BundledDrizzleMigrations
  private onProgress?: ((event: DrizzleMigrationProgressEvent) => void) | undefined
  private onComplete?: ((result: DrizzleMigrationResult) => void) | undefined
  private onError?: ((error: Error) => void) | undefined

  private state: DrizzleMigratorState = 'idle'
  private migrationPromise: Promise<DrizzleMigrationResult> | null = null
  private lastResult: DrizzleMigrationResult | null = null
  private lastError: Error | null = null

  constructor(config: DrizzleMigratorConfig) {
    this.migrations = config.migrations
    this.config = {
      tableName: config.tableName ?? '__drizzle_migrations',
      autoRun: config.autoRun ?? true,
      checksumValidation: config.checksumValidation ?? true,
      debug: config.debug ?? false,
    }
    this.onProgress = config.onProgress
    this.onComplete = config.onComplete
    this.onError = config.onError
  }

  /**
   * Log debug message
   */
  private log(message: string, ...args: unknown[]): void {
    if (this.config.debug) {
      console.log(`[DrizzleMigrator] ${message}`, ...args)
    }
  }

  /**
   * Get current state
   */
  getState(): DrizzleMigratorState {
    return this.state
  }

  /**
   * Get last migration result
   */
  getLastResult(): DrizzleMigrationResult | null {
    return this.lastResult
  }

  /**
   * Get last error
   */
  getLastError(): Error | null {
    return this.lastError
  }

  /**
   * Get the ordered list of migrations to apply
   */
  getMigrations(): Array<{ hash: string; tag: string; sql: string; idx: number }> {
    const { journal, migrations } = this.migrations

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

        // Generate hash from folder name + SQL for uniqueness
        const hash = generateMigrationHash(`${folderName}:${sql}`)

        return {
          hash,
          tag: entry.tag,
          sql,
          idx: entry.idx,
        }
      })
  }

  /**
   * Initialize the migrations table
   */
  private async initializeTable(executor: QueryExecutor): Promise<void> {
    this.log('Initializing migrations table')
    await executor.query(getCreateTableSQL(this.config.tableName))
  }

  /**
   * Get list of already applied migrations
   */
  private async getAppliedMigrations(executor: QueryExecutor): Promise<Set<string>> {
    const result = await executor.query<DrizzleMigrationRecord>(
      `SELECT hash FROM "${this.config.tableName}" ORDER BY created_at ASC`
    )
    return new Set(result.rows.map((r) => r.hash))
  }

  /**
   * Record a migration as applied
   */
  private async recordMigration(executor: QueryExecutor, hash: string): Promise<void> {
    await executor.query(
      `INSERT INTO "${this.config.tableName}" (hash) VALUES ($1) ON CONFLICT (hash) DO NOTHING`,
      [hash]
    )
  }

  /**
   * Ensure migrations are applied
   *
   * Safe to call multiple times - will only run pending migrations.
   */
  async ensureMigrated(executor: QueryExecutor): Promise<DrizzleMigrationResult> {
    // If already running, wait for completion
    if (this.migrationPromise) {
      return this.migrationPromise
    }

    // If already completed, return cached result
    if (this.state === 'completed' && this.lastResult) {
      return this.lastResult
    }

    // If failed, throw the last error
    if (this.state === 'failed' && this.lastError) {
      throw this.lastError
    }

    // If autoRun is disabled, return empty result
    if (!this.config.autoRun) {
      return {
        success: true,
        appliedCount: 0,
        skippedCount: 0,
        durationMs: 0,
        applied: [],
      }
    }

    // Start migration
    this.migrationPromise = this.runMigrations(executor)

    try {
      const result = await this.migrationPromise
      return result
    } finally {
      this.migrationPromise = null
    }
  }

  /**
   * Run all pending migrations
   */
  private async runMigrations(executor: QueryExecutor): Promise<DrizzleMigrationResult> {
    this.state = 'running'
    this.log('Starting Drizzle migrations')

    const startTime = performance.now()
    const applied: string[] = []
    let skippedCount = 0

    try {
      // Initialize migrations table
      await this.initializeTable(executor)

      // Get already applied migrations
      const appliedHashes = await this.getAppliedMigrations(executor)

      // Get all migrations to process
      const migrations = this.getMigrations()
      this.log(`Found ${migrations.length} migrations, ${appliedHashes.size} already applied`)

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

        // Check if already applied
        if (appliedHashes.has(migration.hash)) {
          this.log(`Skipping already applied: ${migration.tag}`)
          skippedCount++

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

          continue
        }

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

        const migrationStart = performance.now()

        try {
          // Execute migration SQL
          this.onProgress?.({
            migration,
            index: i,
            total: migrations.length,
            phase: 'executing',
          })

          // Clean up Drizzle breakpoint comments and execute
          const cleanSql = migration.sql.replace(/-->\s*statement-breakpoint\s*/g, '\n').trim()

          // Execute each statement separately for better error handling
          const statements = cleanSql
            .split(/;\s*(?=(?:[^']*'[^']*')*[^']*$)/) // Split on ; not in quotes
            .map((s) => s.trim())
            .filter((s) => s.length > 0)

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

          // Record migration as applied
          await this.recordMigration(executor, migration.hash)

          const durationMs = Math.round(performance.now() - migrationStart)
          applied.push(migration.hash)

          this.log(`Applied ${migration.tag} in ${durationMs}ms`)

          this.onProgress?.({
            migration,
            index: i,
            total: migrations.length,
            phase: 'completed',
            durationMs,
          })
        } catch (error) {
          const errorMessage = error instanceof Error ? error.message : String(error)
          const durationMs = Math.round(performance.now() - migrationStart)

          this.log(`Failed ${migration.tag}: ${errorMessage}`)

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

          // Stop on first error
          throw error
        }
      }

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

      const result: DrizzleMigrationResult = {
        success: true,
        appliedCount: applied.length,
        skippedCount,
        durationMs: totalDuration,
        applied,
      }

      this.state = 'completed'
      this.lastResult = result

      this.log(`Migration complete: ${applied.length} applied, ${skippedCount} skipped in ${totalDuration}ms`)
      this.onComplete?.(result)

      return result
    } catch (error) {
      const totalDuration = Math.round(performance.now() - startTime)
      const errorMessage = error instanceof Error ? error.message : String(error)

      const result: DrizzleMigrationResult = {
        success: false,
        appliedCount: applied.length,
        skippedCount,
        durationMs: totalDuration,
        applied,
        error: errorMessage,
      }

      this.state = 'failed'
      this.lastResult = result
      this.lastError = error instanceof Error ? error : new Error(errorMessage)

      this.onError?.(this.lastError)

      return result
    }
  }

  /**
   * Force re-run of migrations
   *
   * Resets state and runs ensureMigrated again.
   * Already applied migrations will be skipped.
   */
  async forceMigrate(executor: QueryExecutor): Promise<DrizzleMigrationResult> {
    this.state = 'idle'
    this.migrationPromise = null
    this.lastResult = null
    this.lastError = null

    return this.ensureMigrated(executor)
  }

  /**
   * Get the current migration status
   */
  async getStatus(executor: QueryExecutor): Promise<{
    initialized: boolean
    appliedCount: number
    pendingCount: number
    appliedMigrations: string[]
    pendingMigrations: string[]
  }> {
    try {
      // Try to get applied migrations
      const appliedHashes = await this.getAppliedMigrations(executor)
      const allMigrations = this.getMigrations()

      const appliedMigrations = allMigrations
        .filter((m) => appliedHashes.has(m.hash))
        .map((m) => m.tag)

      const pendingMigrations = allMigrations
        .filter((m) => !appliedHashes.has(m.hash))
        .map((m) => m.tag)

      return {
        initialized: true,
        appliedCount: appliedMigrations.length,
        pendingCount: pendingMigrations.length,
        appliedMigrations,
        pendingMigrations,
      }
    } catch {
      // Table doesn't exist yet
      const allMigrations = this.getMigrations()

      return {
        initialized: false,
        appliedCount: 0,
        pendingCount: allMigrations.length,
        appliedMigrations: [],
        pendingMigrations: allMigrations.map((m) => m.tag),
      }
    }
  }

  /**
   * Check if there are pending migrations
   */
  async needsMigration(executor: QueryExecutor): Promise<boolean> {
    const status = await this.getStatus(executor)
    return status.pendingCount > 0
  }
}

/**
 * Create a Drizzle migrator instance
 *
 * @example
 * ```typescript
 * import { createDrizzleMigrator } from 'postgres.do/drizzle'
 * import bundledMigrations from './drizzle-bundle'
 *
 * const migrator = createDrizzleMigrator({
 *   migrations: bundledMigrations,
 *   autoRun: true,
 *   onProgress: (event) => console.log(`${event.phase}: ${event.migration.tag}`),
 * })
 * ```
 */
export function createDrizzleMigrator(config: DrizzleMigratorConfig): DrizzleMigrator {
  return new DrizzleMigrator(config)
}

/**
 * Parse a Drizzle migrations journal file
 */
export function parseDrizzleJournal(content: string): DrizzleJournal {
  try {
    return JSON.parse(content) as DrizzleJournal
  } catch (error) {
    throw new Error(`Failed to parse Drizzle journal: ${error}`)
  }
}

/**
 * Convert Drizzle migrations to bundled format
 *
 * This is useful for build-time bundling in a Node.js environment.
 *
 * @example
 * ```typescript
 * // In your build script
 * import { bundleDrizzleMigrations } from 'postgres.do/drizzle'
 * import fs from 'fs'
 * import path from 'path'
 *
 * const migrationsDir = './drizzle'
 * const bundled = bundleDrizzleMigrations(migrationsDir)
 *
 * fs.writeFileSync(
 *   './src/drizzle-bundle.json',
 *   JSON.stringify(bundled, null, 2)
 * )
 * ```
 */
export function bundleDrizzleMigrations(
  journal: DrizzleJournal,
  sqlFiles: Record<string, string>
): BundledDrizzleMigrations {
  // Validate all migrations exist
  for (const entry of journal.entries) {
    const folderName = `${entry.idx.toString().padStart(4, '0')}_${entry.tag}`
    if (!sqlFiles[folderName] && !sqlFiles[entry.tag]) {
      throw new Error(`Missing SQL file for migration: ${folderName}`)
    }
  }

  return {
    journal,
    migrations: sqlFiles,
  }
}