/**
 * External Migration Commands
 *
 * CLI commands for migrating from external PostgreSQL providers (Neon, Supabase)
 * to postgres.do (PGLite).
 *
 * @module cli/commands/migrate-from-external
 */

import { resolve } from 'node:path'
import { writeFileSync, mkdirSync, existsSync } from 'node:fs'
import {
  formatSuccess,
  formatError,
  formatInfo,
  formatWarning,
  formatDim,
  formatProgress,
  formatDuration,
} from '../formatting.js'

// Import migration tooling from @dotdo/postgres
import {
  exportSchema,
  transformSchema,
  validateSchemaCompatibility,
  detectProvider,
  DataMigrator,
  createProgressReporter,
  type SchemaExportOptions,
  type ExportedSchema,
  type TransformResult,
  type ProgressEvent,
  type DatabaseProvider,
  type SchemaValidationOptions,
} from '@dotdo/postgres/migration-tooling'

/**
 * Options for migrate from-neon command
 */
export interface MigrateFromNeonOptions {
  /** Neon connection string */
  connectionString: string
  /** Output directory for migration files */
  outputDir?: string
  /** Include data migration */
  includeData?: boolean
  /** Specific tables to migrate */
  tables?: string[]
  /** Tables to exclude */
  excludeTables?: string[]
  /** Batch size for data migration */
  batchSize?: number
  /** Validate schema before migration */
  validate?: boolean
  /** Output as JSON */
  json?: boolean
  /** Verbose output */
  verbose?: boolean
  /** Dry run - don't actually write files */
  dryRun?: boolean
  /** Validate data integrity after migration */
  validateIntegrity?: boolean
}

/**
 * Options for migrate from-supabase command
 */
export interface MigrateFromSupabaseOptions {
  /** Supabase connection string */
  connectionString: string
  /** Output directory for migration files */
  outputDir?: string
  /** Include data migration */
  includeData?: boolean
  /** Specific tables to migrate */
  tables?: string[]
  /** Tables to exclude (default: Supabase internal tables) */
  excludeTables?: string[]
  /** Batch size for data migration */
  batchSize?: number
  /** Validate schema before migration */
  validate?: boolean
  /** Output as JSON */
  json?: boolean
  /** Verbose output */
  verbose?: boolean
  /** Dry run - don't actually write files */
  dryRun?: boolean
  /** Validate data integrity after migration */
  validateIntegrity?: boolean
}

/**
 * Result of a migration operation
 */
export interface MigrationResult {
  success: boolean
  provider: DatabaseProvider
  schema?: {
    tables: number
    views: number
    functions: number
    triggers: number
    extensions: string[]
  }
  validation?: {
    isCompatible: boolean
    errorCount: number
    warningCount: number
    complexityScore: number
  }
  transform?: {
    warnings: number
    unsupportedExtensions: string[]
  }
  data?: {
    tablesProcessed: number
    totalRows: number
  }
  outputFiles?: string[]
  errors?: string[]
  warnings?: string[]
  duration: number
}

/**
 * Default Supabase internal tables to exclude
 */
const SUPABASE_INTERNAL_TABLES = [
  'auth.users',
  'auth.sessions',
  'auth.refresh_tokens',
  'auth.mfa_factors',
  'auth.mfa_challenges',
  'auth.mfa_amr_claims',
  'auth.flow_state',
  'auth.saml_providers',
  'auth.saml_relay_states',
  'auth.sso_providers',
  'auth.sso_domains',
  'auth.identities',
  'auth.one_time_tokens',
  'storage.objects',
  'storage.buckets',
  'storage.migrations',
  'realtime.messages',
  'realtime.subscription',
  'supabase_functions.hooks',
  'supabase_functions.migrations',
  'extensions.pg_stat_statements',
]

/**
 * Run migrate from-neon command
 */
export async function runMigrateFromNeon(options: MigrateFromNeonOptions): Promise<void> {
  const startTime = Date.now()
  const result: MigrationResult = {
    success: false,
    provider: 'neon',
    duration: 0,
  }

  // Validate provider
  const provider = detectProvider(options.connectionString)
  if (provider !== 'neon') {
    console.error(formatError(`Connection string does not appear to be a Neon database`))
    console.error(formatInfo(`Detected provider: ${provider}`))
    console.error(formatInfo(`For Neon, connection string should contain '.neon.tech'`))
    if (options.json) {
      console.log(JSON.stringify({ success: false, error: 'Invalid provider', detected: provider }))
    }
    process.exit(1)
  }

  const outputDir = resolve(process.cwd(), options.outputDir || './migration-output')

  if (!options.json) {
    console.log('')
    console.log(formatInfo('postgres.do Migration Tooling'))
    console.log(formatDim('Migrating from Neon to postgres.do (PGLite)'))
    console.log('')
  }

  const reporter = createProgressReporter()

  if (!options.json && options.verbose) {
    reporter.onProgress((event) => {
      console.log(formatProgress(`[${event.phase}] ${event.progress}% - ${event.message || ''}`))
    })
  }

  try {
    // Step 1: Export schema
    reporter.startPhase('schema_export')
    if (!options.json) {
      console.log(formatInfo('Exporting schema from Neon...'))
    }

    const exportOptions: SchemaExportOptions = {
      connectionString: options.connectionString,
      includeData: false,
      includeViews: true,
      includeFunctions: true,
      includeTriggers: true,
      includeExtensions: true,
      ...(options.tables && { tables: options.tables }),
      ...(options.excludeTables && { excludeTables: options.excludeTables }),
    }

    const schema = await exportSchema(exportOptions)
    reporter.endPhase('schema_export')

    result.schema = {
      tables: schema.tables.length,
      views: schema.views.length,
      functions: schema.functions.length,
      triggers: schema.triggers.length,
      extensions: schema.extensions,
    }

    if (!options.json) {
      console.log(formatSuccess(`Exported ${schema.tables.length} tables, ${schema.views.length} views`))
    }

    // Step 2: Validate schema (if requested)
    if (options.validate) {
      reporter.startPhase('validation')
      if (!options.json) {
        console.log(formatInfo('Validating schema compatibility...'))
      }

      const validation = validateSchemaCompatibility(schema)
      reporter.endPhase('validation')

      result.validation = {
        isCompatible: validation.isCompatible,
        errorCount: validation.errorCount,
        warningCount: validation.warningCount,
        complexityScore: validation.complexityScore,
      }

      if (!options.json) {
        if (validation.isCompatible) {
          console.log(formatSuccess('Schema is compatible with PGLite'))
        } else {
          console.log(formatWarning(`Schema has ${validation.errorCount} compatibility issues`))
        }

        if (validation.warningCount > 0 && options.verbose) {
          console.log(formatWarning(`${validation.warningCount} warnings detected`))
          validation.issues
            .filter((i) => i.severity === 'warning')
            .slice(0, 5)
            .forEach((issue) => {
              console.log(formatDim(`  - ${issue.message}`))
            })
        }
      }
    }

    // Step 3: Transform schema
    reporter.startPhase('schema_transform')
    if (!options.json) {
      console.log(formatInfo('Transforming schema for PGLite...'))
    }

    const transformed = await transformSchema(schema, {
      viewStrategy: 'preserve',
    })
    reporter.endPhase('schema_transform')

    result.transform = {
      warnings: transformed.warnings.length,
      unsupportedExtensions: transformed.unsupportedExtensions || [],
    }

    if (!options.json && transformed.warnings.length > 0 && options.verbose) {
      console.log(formatWarning(`${transformed.warnings.length} transformation warnings`))
    }

    // Step 4: Write output files
    if (!options.dryRun) {
      result.outputFiles = []

      // Create output directory
      if (!existsSync(outputDir)) {
        mkdirSync(outputDir, { recursive: true })
      }

      // Write schema SQL
      const schemaPath = resolve(outputDir, 'schema.sql')
      writeFileSync(schemaPath, transformed.sql)
      result.outputFiles.push(schemaPath)

      // Write schema JSON (for programmatic use)
      const schemaJsonPath = resolve(outputDir, 'schema.json')
      writeFileSync(schemaJsonPath, JSON.stringify(transformed.schema, null, 2))
      result.outputFiles.push(schemaJsonPath)

      // Write validation report (if validated)
      if (result.validation) {
        const validationPath = resolve(outputDir, 'validation-report.json')
        writeFileSync(validationPath, JSON.stringify(result.validation, null, 2))
        result.outputFiles.push(validationPath)
      }

      if (!options.json) {
        console.log(formatSuccess(`Output written to: ${outputDir}`))
        result.outputFiles.forEach((f) => {
          console.log(formatDim(`  - ${f}`))
        })
      }
    } else {
      if (!options.json) {
        console.log(formatInfo('Dry run - no files written'))
      }
    }

    // Step 5: Data migration (if requested)
    if (options.includeData && !options.dryRun) {
      reporter.startPhase('data_migration')
      if (!options.json) {
        console.log(formatInfo('Starting data migration...'))
      }

      const migrator = new DataMigrator({
        sourceConnection: options.connectionString,
        targetConnection: 'pglite://local',
      })

      const dataResult = await migrator.migrateAll({
        schema: transformed.schema,
        onProgress: (event: ProgressEvent) => {
          if (!options.json && options.verbose) {
            console.log(
              formatProgress(
                `[${event.phase}] ${event.progress}% - ${event.details?.table || ''} (${event.details?.rowsProcessed || 0} rows)`
              )
            )
          }
        },
        ...(options.validateIntegrity !== undefined && { validateIntegrity: options.validateIntegrity }),
      })

      reporter.endPhase('data_migration')

      result.data = {
        tablesProcessed: dataResult.tablesProcessed.length,
        totalRows: dataResult.tablesProcessed.reduce((sum, t) => sum + t.rowsProcessed, 0),
      }

      if (!options.json) {
        console.log(formatSuccess(`Migrated ${result.data.totalRows} rows from ${result.data.tablesProcessed} tables`))
      }
    }

    result.success = true
    result.duration = Date.now() - startTime

    if (options.json) {
      console.log(JSON.stringify(result, null, 2))
    } else {
      console.log('')
      console.log(formatSuccess(`Migration completed in ${formatDuration(result.duration)}`))
    }
  } catch (error) {
    result.success = false
    result.duration = Date.now() - startTime
    result.errors = [error instanceof Error ? error.message : String(error)]

    if (options.json) {
      console.log(JSON.stringify(result, null, 2))
    } else {
      console.error(formatError(`Migration failed: ${error instanceof Error ? error.message : error}`))
      if (options.verbose && error instanceof Error && error.stack) {
        console.error(formatDim(error.stack))
      }
    }
    process.exit(1)
  }
}

/**
 * Run migrate from-supabase command
 */
export async function runMigrateFromSupabase(options: MigrateFromSupabaseOptions): Promise<void> {
  const startTime = Date.now()
  const result: MigrationResult = {
    success: false,
    provider: 'supabase',
    duration: 0,
  }

  // Validate provider
  const provider = detectProvider(options.connectionString)
  if (provider !== 'supabase') {
    console.error(formatError(`Connection string does not appear to be a Supabase database`))
    console.error(formatInfo(`Detected provider: ${provider}`))
    console.error(formatInfo(`For Supabase, connection string should contain '.supabase.co'`))
    if (options.json) {
      console.log(JSON.stringify({ success: false, error: 'Invalid provider', detected: provider }))
    }
    process.exit(1)
  }

  const outputDir = resolve(process.cwd(), options.outputDir || './migration-output')

  // Default to excluding Supabase internal tables
  const excludeTables = options.excludeTables || SUPABASE_INTERNAL_TABLES

  if (!options.json) {
    console.log('')
    console.log(formatInfo('postgres.do Migration Tooling'))
    console.log(formatDim('Migrating from Supabase to postgres.do (PGLite)'))
    console.log('')
    if (excludeTables.length > 0 && options.verbose) {
      console.log(formatDim(`Excluding ${excludeTables.length} Supabase internal tables`))
    }
  }

  const reporter = createProgressReporter()

  if (!options.json && options.verbose) {
    reporter.onProgress((event) => {
      console.log(formatProgress(`[${event.phase}] ${event.progress}% - ${event.message || ''}`))
    })
  }

  try {
    // Step 1: Export schema
    reporter.startPhase('schema_export')
    if (!options.json) {
      console.log(formatInfo('Exporting schema from Supabase...'))
    }

    const exportOptions: SchemaExportOptions = {
      connectionString: options.connectionString,
      includeData: false,
      includeViews: true,
      includeFunctions: true,
      includeTriggers: true,
      includeExtensions: true,
      ...(options.tables && { tables: options.tables }),
      excludeTables: excludeTables,
      schemas: ['public'], // Default to public schema for Supabase
    }

    const schema = await exportSchema(exportOptions)
    reporter.endPhase('schema_export')

    result.schema = {
      tables: schema.tables.length,
      views: schema.views.length,
      functions: schema.functions.length,
      triggers: schema.triggers.length,
      extensions: schema.extensions,
    }

    if (!options.json) {
      console.log(formatSuccess(`Exported ${schema.tables.length} tables, ${schema.views.length} views`))
    }

    // Step 2: Validate schema (if requested)
    if (options.validate) {
      reporter.startPhase('validation')
      if (!options.json) {
        console.log(formatInfo('Validating schema compatibility...'))
      }

      const validation = validateSchemaCompatibility(schema)
      reporter.endPhase('validation')

      result.validation = {
        isCompatible: validation.isCompatible,
        errorCount: validation.errorCount,
        warningCount: validation.warningCount,
        complexityScore: validation.complexityScore,
      }

      if (!options.json) {
        if (validation.isCompatible) {
          console.log(formatSuccess('Schema is compatible with PGLite'))
        } else {
          console.log(formatWarning(`Schema has ${validation.errorCount} compatibility issues`))
        }

        if (validation.warningCount > 0 && options.verbose) {
          console.log(formatWarning(`${validation.warningCount} warnings detected`))
          validation.issues
            .filter((i) => i.severity === 'warning')
            .slice(0, 5)
            .forEach((issue) => {
              console.log(formatDim(`  - ${issue.message}`))
            })
        }
      }
    }

    // Step 3: Transform schema
    reporter.startPhase('schema_transform')
    if (!options.json) {
      console.log(formatInfo('Transforming schema for PGLite...'))
    }

    const transformed = await transformSchema(schema, {
      viewStrategy: 'preserve',
    })
    reporter.endPhase('schema_transform')

    result.transform = {
      warnings: transformed.warnings.length,
      unsupportedExtensions: transformed.unsupportedExtensions || [],
    }

    if (!options.json && transformed.warnings.length > 0 && options.verbose) {
      console.log(formatWarning(`${transformed.warnings.length} transformation warnings`))
    }

    // Step 4: Write output files
    if (!options.dryRun) {
      result.outputFiles = []

      // Create output directory
      if (!existsSync(outputDir)) {
        mkdirSync(outputDir, { recursive: true })
      }

      // Write schema SQL
      const schemaPath = resolve(outputDir, 'schema.sql')
      writeFileSync(schemaPath, transformed.sql)
      result.outputFiles.push(schemaPath)

      // Write schema JSON (for programmatic use)
      const schemaJsonPath = resolve(outputDir, 'schema.json')
      writeFileSync(schemaJsonPath, JSON.stringify(transformed.schema, null, 2))
      result.outputFiles.push(schemaJsonPath)

      // Write validation report (if validated)
      if (result.validation) {
        const validationPath = resolve(outputDir, 'validation-report.json')
        writeFileSync(validationPath, JSON.stringify(result.validation, null, 2))
        result.outputFiles.push(validationPath)
      }

      // Write migration guide for Supabase-specific features
      const migrationGuidePath = resolve(outputDir, 'MIGRATION_GUIDE.md')
      writeFileSync(migrationGuidePath, generateSupabaseMigrationGuide(schema, transformed))
      result.outputFiles.push(migrationGuidePath)

      if (!options.json) {
        console.log(formatSuccess(`Output written to: ${outputDir}`))
        result.outputFiles.forEach((f) => {
          console.log(formatDim(`  - ${f}`))
        })
      }
    } else {
      if (!options.json) {
        console.log(formatInfo('Dry run - no files written'))
      }
    }

    // Step 5: Data migration (if requested)
    if (options.includeData && !options.dryRun) {
      reporter.startPhase('data_migration')
      if (!options.json) {
        console.log(formatInfo('Starting data migration...'))
      }

      const migrator = new DataMigrator({
        sourceConnection: options.connectionString,
        targetConnection: 'pglite://local',
      })

      const dataResult = await migrator.migrateAll({
        schema: transformed.schema,
        onProgress: (event: ProgressEvent) => {
          if (!options.json && options.verbose) {
            console.log(
              formatProgress(
                `[${event.phase}] ${event.progress}% - ${event.details?.table || ''} (${event.details?.rowsProcessed || 0} rows)`
              )
            )
          }
        },
        ...(options.validateIntegrity !== undefined && { validateIntegrity: options.validateIntegrity }),
      })

      reporter.endPhase('data_migration')

      result.data = {
        tablesProcessed: dataResult.tablesProcessed.length,
        totalRows: dataResult.tablesProcessed.reduce((sum, t) => sum + t.rowsProcessed, 0),
      }

      if (!options.json) {
        console.log(formatSuccess(`Migrated ${result.data.totalRows} rows from ${result.data.tablesProcessed} tables`))
      }
    }

    result.success = true
    result.duration = Date.now() - startTime

    if (options.json) {
      console.log(JSON.stringify(result, null, 2))
    } else {
      console.log('')
      console.log(formatSuccess(`Migration completed in ${formatDuration(result.duration)}`))
    }
  } catch (error) {
    result.success = false
    result.duration = Date.now() - startTime
    result.errors = [error instanceof Error ? error.message : String(error)]

    if (options.json) {
      console.log(JSON.stringify(result, null, 2))
    } else {
      console.error(formatError(`Migration failed: ${error instanceof Error ? error.message : error}`))
      if (options.verbose && error instanceof Error && error.stack) {
        console.error(formatDim(error.stack))
      }
    }
    process.exit(1)
  }
}

/**
 * Generate a migration guide for Supabase-specific features
 */
function generateSupabaseMigrationGuide(schema: ExportedSchema, transformed: TransformResult): string {
  const lines: string[] = [
    '# Supabase to postgres.do Migration Guide',
    '',
    'This guide covers migrating from Supabase to postgres.do (PGLite).',
    '',
    '## What Was Migrated',
    '',
    `- **Tables:** ${schema.tables.length}`,
    `- **Views:** ${schema.views.length}`,
    `- **Functions:** ${schema.functions.length}`,
    `- **Triggers:** ${schema.triggers.length}`,
    '',
    '## Supabase-Specific Features',
    '',
    '### Authentication (auth schema)',
    '',
    'Supabase auth tables were excluded from migration. You will need to:',
    '',
    '1. Implement your own authentication using the postgres.do SDK',
    '2. Or integrate with a third-party auth provider',
    '',
    '### Storage (storage schema)',
    '',
    'Supabase storage objects were excluded. You will need to:',
    '',
    '1. Use Cloudflare R2 for object storage',
    '2. Update file references in your application',
    '',
    '### Realtime',
    '',
    'Supabase realtime is not available in postgres.do. Consider:',
    '',
    '1. Using Cloudflare Durable Objects for real-time state',
    '2. Implementing WebSocket connections in your Workers',
    '',
    '### Row Level Security (RLS)',
    '',
    'RLS policies from Supabase are preserved in the schema. However:',
    '',
    '1. postgres.do runs in a trusted environment (your Worker)',
    '2. Consider implementing access control in your application layer',
    '',
    '## Extension Compatibility',
    '',
  ]

  if (transformed.unsupportedExtensions && transformed.unsupportedExtensions.length > 0) {
    lines.push('The following extensions are not supported in PGLite:')
    lines.push('')
    transformed.unsupportedExtensions.forEach((ext) => {
      lines.push(`- \`${ext}\``)
    })
    lines.push('')
    lines.push('Consider alternative approaches for functionality provided by these extensions.')
  } else {
    lines.push('All used extensions are compatible with PGLite.')
  }

  lines.push('')
  lines.push('## Transformation Warnings')
  lines.push('')

  if (transformed.warnings.length > 0) {
    transformed.warnings.forEach((warning) => {
      lines.push(`- **${warning.type}:** ${warning.message}`)
    })
  } else {
    lines.push('No transformation warnings.')
  }

  lines.push('')
  lines.push('## Next Steps')
  lines.push('')
  lines.push('1. Review `schema.sql` and make any necessary adjustments')
  lines.push('2. Apply the schema to your postgres.do database')
  lines.push('3. Update your application code to use the postgres.do client SDK')
  lines.push('4. Test thoroughly before migrating production data')
  lines.push('')
  lines.push('## Resources')
  lines.push('')
  lines.push('- [postgres.do Documentation](https://postgres.do/docs)')
  lines.push('- [PGLite Compatibility](https://postgres.do/docs/pglite)')
  lines.push('- [Migration API Reference](https://postgres.do/docs/migration)')
  lines.push('')

  return lines.join('\n')
}

/**
 * Run schema validation command
 */
export async function runMigrateValidateSchema(options: {
  connectionString: string
  json?: boolean
  verbose?: boolean
  strict?: boolean
}): Promise<void> {
  const provider = detectProvider(options.connectionString)

  if (!options.json) {
    console.log('')
    console.log(formatInfo(`Validating schema from ${provider}...`))
    console.log('')
  }

  try {
    const schema = await exportSchema({
      connectionString: options.connectionString,
      includeViews: true,
      includeFunctions: true,
      includeTriggers: true,
      includeExtensions: true,
    })

    const validationOpts: SchemaValidationOptions = {}
    if (options.strict !== undefined) {
      validationOpts.strict = options.strict
    }
    const validation = validateSchemaCompatibility(schema, validationOpts)

    if (options.json) {
      console.log(JSON.stringify(validation, null, 2))
    } else {
      console.log(formatInfo(`Schema Analysis:`))
      console.log(formatDim(`  Tables: ${schema.tables.length}`))
      console.log(formatDim(`  Views: ${schema.views.length}`))
      console.log(formatDim(`  Functions: ${schema.functions.length}`))
      console.log(formatDim(`  Extensions: ${schema.extensions.join(', ') || 'none'}`))
      console.log('')

      if (validation.isCompatible) {
        console.log(formatSuccess('Schema is compatible with PGLite'))
      } else {
        console.log(formatError(`Schema has ${validation.errorCount} blocking issues`))
      }

      if (validation.warningCount > 0) {
        console.log(formatWarning(`${validation.warningCount} warnings`))
      }

      console.log(formatDim(`Complexity score: ${validation.complexityScore}/10`))
      console.log('')

      if (validation.issues.length > 0 && (options.verbose || !validation.isCompatible)) {
        console.log(formatInfo('Issues:'))
        const issueLimit = options.verbose ? validation.issues.length : 10
        validation.issues.slice(0, issueLimit).forEach((issue) => {
          const icon = issue.severity === 'error' ? '!' : issue.severity === 'warning' ? '?' : '-'
          const color = issue.severity === 'error' ? formatError : issue.severity === 'warning' ? formatWarning : formatDim
          console.log(color(`  ${icon} [${issue.code}] ${issue.message}`))
          if (issue.suggestion && options.verbose) {
            console.log(formatDim(`    Suggestion: ${issue.suggestion}`))
          }
        })

        if (validation.issues.length > issueLimit) {
          console.log(formatDim(`  ... and ${validation.issues.length - issueLimit} more issues`))
        }
      }
    }

    // Exit with error code if not compatible
    if (!validation.isCompatible) {
      process.exit(1)
    }
  } catch (error) {
    if (options.json) {
      console.log(JSON.stringify({ success: false, error: error instanceof Error ? error.message : String(error) }))
    } else {
      console.error(formatError(`Validation failed: ${error instanceof Error ? error.message : error}`))
    }
    process.exit(1)
  }
}

/**
 * Options for pg_dump import
 */
export interface ImportPgDumpOptions {
  /** Path to the pg_dump file */
  file: string
  /** Output directory for transformed files */
  outputDir?: string
  /** Validate schema compatibility */
  validate?: boolean
  /** Output as JSON */
  json?: boolean
  /** Verbose output */
  verbose?: boolean
  /** Dry run - don't write files */
  dryRun?: boolean
}

/**
 * Parse a pg_dump SQL file and extract schema information
 */
function parsePgDumpFile(content: string): ExportedSchema {
  const tables: ExportedSchema['tables'] = []
  const views: ExportedSchema['views'] = []
  const functions: ExportedSchema['functions'] = []
  const triggers: ExportedSchema['triggers'] = []
  const extensions: string[] = []

  // Simple regex-based parsing for CREATE TABLE statements
  const createTableRegex = /CREATE TABLE\s+(?:IF NOT EXISTS\s+)?(?:"?(\w+)"?\.)?"?(\w+)"?\s*\(([\s\S]*?)\);/gi
  const createViewRegex = /CREATE(?:\s+OR\s+REPLACE)?\s+VIEW\s+(?:"?(\w+)"?\.)?"?(\w+)"?\s+AS\s+([\s\S]*?);/gi
  const createFunctionRegex = /CREATE(?:\s+OR\s+REPLACE)?\s+FUNCTION\s+(?:"?(\w+)"?\.)?"?(\w+)"?\s*\([\s\S]*?\)\s+RETURNS[\s\S]*?LANGUAGE\s+(\w+)[\s\S]*?AS[\s\S]*?\$\$[\s\S]*?\$\$/gi
  const createExtensionRegex = /CREATE EXTENSION(?:\s+IF NOT EXISTS)?\s+"?(\w+)"?/gi

  let match: RegExpExecArray | null

  // Parse extensions
  while ((match = createExtensionRegex.exec(content)) !== null) {
    if (match[1]) {
      extensions.push(match[1])
    }
  }

  // Parse tables
  while ((match = createTableRegex.exec(content)) !== null) {
    const schemaName = match[1] || 'public'
    const tableName = match[2]
    const columnsBlock = match[3]

    if (!tableName || !columnsBlock) continue

    // Parse columns
    const columns: ExportedSchema['tables'][0]['columns'] = []
    const columnLines = columnsBlock.split(',').map((l) => l.trim()).filter((l) => l && !l.startsWith('PRIMARY KEY') && !l.startsWith('FOREIGN KEY') && !l.startsWith('CONSTRAINT'))

    for (const line of columnLines) {
      const colMatch = line.match(/"?(\w+)"?\s+(\w+(?:\([^)]+\))?)\s*(.*)/i)
      if (colMatch) {
        const colName = colMatch[1]
        const colType = colMatch[2]
        const constraints = colMatch[3] || ''

        if (!colName || !colType) continue

        const column: ExportedSchema['tables'][0]['columns'][0] = {
          name: colName,
          type: colType,
          nullable: !constraints.toUpperCase().includes('NOT NULL'),
          unique: constraints.toUpperCase().includes('UNIQUE'),
        }
        const defaultMatch = constraints.match(/DEFAULT\s+([^,\s]+)/i)
        if (defaultMatch?.[1]) {
          column.default = defaultMatch[1]
        }
        columns.push(column)
      }
    }

    // Parse primary key
    const pkMatch = columnsBlock.match(/PRIMARY\s+KEY\s*\(([^)]+)\)/i)
    const primaryKey = pkMatch?.[1]
      ? { columns: pkMatch[1].split(',').map((c) => c.trim().replace(/"/g, '')) }
      : null

    // Parse foreign keys
    const foreignKeys: ExportedSchema['tables'][0]['foreignKeys'] = []
    const fkRegex = /FOREIGN\s+KEY\s*\(([^)]+)\)\s+REFERENCES\s+"?(\w+)"?\s*\(([^)]+)\)/gi
    let fkMatch: RegExpExecArray | null
    while ((fkMatch = fkRegex.exec(columnsBlock)) !== null) {
      if (fkMatch[1] && fkMatch[2] && fkMatch[3]) {
        foreignKeys.push({
          columns: fkMatch[1].split(',').map((c) => c.trim().replace(/"/g, '')),
          references: {
            table: fkMatch[2],
            columns: fkMatch[3].split(',').map((c) => c.trim().replace(/"/g, '')),
          },
        })
      }
    }

    tables.push({
      name: tableName,
      schema: schemaName,
      columns,
      primaryKey,
      foreignKeys,
      indexes: [],
    })
  }

  // Parse views
  while ((match = createViewRegex.exec(content)) !== null) {
    if (match[2] && match[3]) {
      views.push({
        name: match[2],
        schema: match[1] || 'public',
        definition: match[3].trim(),
      })
    }
  }

  // Parse functions
  while ((match = createFunctionRegex.exec(content)) !== null) {
    if (match[2] && match[3]) {
      functions.push({
        name: match[2],
        schema: match[1] || 'public',
        definition: match[0],
        language: match[3],
      })
    }
  }

  return { tables, views, functions, triggers, extensions }
}

/**
 * Run pg_dump import command
 */
export async function runImportPgDump(options: ImportPgDumpOptions): Promise<void> {
  const { readFileSync, writeFileSync, mkdirSync, existsSync } = await import('node:fs')
  const { resolve } = await import('node:path')

  const startTime = Date.now()

  if (!existsSync(options.file)) {
    if (options.json) {
      console.log(JSON.stringify({ success: false, error: `File not found: ${options.file}` }))
    } else {
      console.error(formatError(`File not found: ${options.file}`))
    }
    process.exit(1)
  }

  const outputDir = resolve(process.cwd(), options.outputDir || './migration-output')

  if (!options.json) {
    console.log('')
    console.log(formatInfo('postgres.do Migration Tooling'))
    console.log(formatDim('Importing pg_dump file'))
    console.log('')
  }

  try {
    // Read and parse the dump file
    if (!options.json) {
      console.log(formatInfo(`Reading ${options.file}...`))
    }

    const content = readFileSync(options.file, 'utf-8')
    const schema = parsePgDumpFile(content)

    if (!options.json) {
      console.log(formatSuccess(`Parsed ${schema.tables.length} tables, ${schema.views.length} views, ${schema.functions.length} functions`))
    }

    // Validate if requested
    if (options.validate) {
      if (!options.json) {
        console.log(formatInfo('Validating schema compatibility...'))
      }

      const validation = validateSchemaCompatibility(schema)

      if (!options.json) {
        if (validation.isCompatible) {
          console.log(formatSuccess('Schema is compatible with PGLite'))
        } else {
          console.log(formatWarning(`Schema has ${validation.errorCount} compatibility issues`))
        }
      }
    }

    // Transform schema
    if (!options.json) {
      console.log(formatInfo('Transforming schema for PGLite...'))
    }

    const transformed = await transformSchema(schema, {
      viewStrategy: 'preserve',
    })

    // Write output files
    if (!options.dryRun) {
      if (!existsSync(outputDir)) {
        mkdirSync(outputDir, { recursive: true })
      }

      // Write transformed SQL
      const schemaPath = resolve(outputDir, 'schema.sql')
      writeFileSync(schemaPath, transformed.sql)

      // Write schema JSON
      const schemaJsonPath = resolve(outputDir, 'schema.json')
      writeFileSync(schemaJsonPath, JSON.stringify(transformed.schema, null, 2))

      if (!options.json) {
        console.log(formatSuccess(`Output written to: ${outputDir}`))
        console.log(formatDim(`  - ${schemaPath}`))
        console.log(formatDim(`  - ${schemaJsonPath}`))
      }
    } else {
      if (!options.json) {
        console.log(formatInfo('Dry run - no files written'))
      }
    }

    const duration = Date.now() - startTime

    if (options.json) {
      console.log(JSON.stringify({
        success: true,
        schema: {
          tables: schema.tables.length,
          views: schema.views.length,
          functions: schema.functions.length,
          extensions: schema.extensions,
        },
        transform: {
          warnings: transformed.warnings.length,
        },
        duration,
      }, null, 2))
    } else {
      console.log('')
      console.log(formatSuccess(`Import completed in ${formatDuration(duration)}`))
    }
  } catch (error) {
    if (options.json) {
      console.log(JSON.stringify({ success: false, error: error instanceof Error ? error.message : String(error) }))
    } else {
      console.error(formatError(`Import failed: ${error instanceof Error ? error.message : error}`))
    }
    process.exit(1)
  }
}

/**
 * Options for query compatibility test
 */
export interface QueryCompatibilityTestOptions {
  /** Path to file containing test queries (one per line) */
  queriesFile?: string
  /** Individual queries to test */
  queries?: string[]
  /** Output as JSON */
  json?: boolean
  /** Verbose output */
  verbose?: boolean
}

/**
 * Query test result
 */
export interface QueryTestResult {
  query: string
  compatible: boolean
  issues?: string[]
  suggestion?: string
}

/**
 * Check if a query uses PGLite-compatible features
 */
function checkQueryCompatibility(query: string): QueryTestResult {
  const issues: string[] = []

  // Check for unsupported features
  const unsupportedPatterns: Array<{ pattern: RegExp; feature: string; suggestion: string }> = [
    {
      pattern: /\bLISTEN\b/i,
      feature: 'LISTEN/NOTIFY',
      suggestion: 'Use polling or application-level pub/sub instead',
    },
    {
      pattern: /\bNOTIFY\b/i,
      feature: 'LISTEN/NOTIFY',
      suggestion: 'Use polling or application-level pub/sub instead',
    },
    {
      pattern: /\bCREATE\s+SUBSCRIPTION\b/i,
      feature: 'Logical replication',
      suggestion: 'Use application-level data sync instead',
    },
    {
      pattern: /\bCREATE\s+PUBLICATION\b/i,
      feature: 'Logical replication',
      suggestion: 'Use application-level data sync instead',
    },
    {
      pattern: /\bCREATE\s+FOREIGN\s+TABLE\b/i,
      feature: 'Foreign data wrappers',
      suggestion: 'Fetch data from external sources in application code',
    },
    {
      pattern: /\bST_\w+\s*\(/i,
      feature: 'PostGIS functions',
      suggestion: 'Store coordinates as separate columns or use GeoJSON in text/jsonb',
    },
    {
      pattern: /\bgeography\s*\(/i,
      feature: 'PostGIS geography type',
      suggestion: 'Use text or jsonb to store GeoJSON',
    },
    {
      pattern: /\bgeometry\s*\(/i,
      feature: 'PostGIS geometry type',
      suggestion: 'Use text or jsonb to store GeoJSON',
    },
    {
      pattern: /\bts_rank\s*\(/i,
      feature: 'Full-text search ranking',
      suggestion: 'Implement ranking in application code or use LIKE with scoring',
    },
    {
      pattern: /\bplainto_tsquery\s*\(/i,
      feature: 'Full-text search',
      suggestion: 'Use LIKE/ILIKE for simple searches or implement in application',
    },
    {
      pattern: /\bto_tsvector\s*\(/i,
      feature: 'Full-text search',
      suggestion: 'Use LIKE/ILIKE for simple searches or implement in application',
    },
  ]

  for (const { pattern, feature, suggestion } of unsupportedPatterns) {
    if (pattern.test(query)) {
      issues.push(`Uses ${feature}`)
      return {
        query,
        compatible: false,
        issues,
        suggestion,
      }
    }
  }

  // Check for PostgreSQL-specific syntax that might need attention
  const warningPatterns: Array<{ pattern: RegExp; warning: string }> = [
    { pattern: /\bRETURNING\s+\*/i, warning: 'RETURNING * is supported but may be slower than selecting specific columns' },
    { pattern: /\bFOR\s+UPDATE\s+SKIP\s+LOCKED\b/i, warning: 'SKIP LOCKED works but may behave differently in single-connection mode' },
    { pattern: /\bFOR\s+UPDATE\s+NOWAIT\b/i, warning: 'NOWAIT works but may behave differently in single-connection mode' },
  ]

  for (const { pattern, warning } of warningPatterns) {
    if (pattern.test(query)) {
      issues.push(warning)
    }
  }

  const result: QueryTestResult = {
    query,
    compatible: issues.length === 0 || issues.every((i) => i.includes('may')),
  }
  if (issues.length > 0) {
    result.issues = issues
  }
  return result
}

/**
 * Run query compatibility test command
 */
export async function runQueryCompatibilityTest(options: QueryCompatibilityTestOptions): Promise<void> {
  const queries: string[] = []

  // Collect queries from file
  if (options.queriesFile) {
    const { readFileSync, existsSync } = await import('node:fs')

    if (!existsSync(options.queriesFile)) {
      if (options.json) {
        console.log(JSON.stringify({ success: false, error: `File not found: ${options.queriesFile}` }))
      } else {
        console.error(formatError(`File not found: ${options.queriesFile}`))
      }
      process.exit(1)
    }

    const content = readFileSync(options.queriesFile, 'utf-8')
    // Split by semicolons, trim, and filter empty lines
    const fileQueries = content
      .split(';')
      .map((q) => q.trim())
      .filter((q) => q && !q.startsWith('--'))
    queries.push(...fileQueries)
  }

  // Add individual queries
  if (options.queries) {
    queries.push(...options.queries)
  }

  if (queries.length === 0) {
    if (options.json) {
      console.log(JSON.stringify({ success: false, error: 'No queries provided' }))
    } else {
      console.error(formatError('No queries provided. Use --queries-file or --queries'))
    }
    process.exit(1)
  }

  if (!options.json) {
    console.log('')
    console.log(formatInfo('Query Compatibility Test'))
    console.log(formatDim(`Testing ${queries.length} queries for PGLite compatibility`))
    console.log('')
  }

  const results: QueryTestResult[] = []
  let compatibleCount = 0
  let incompatibleCount = 0

  for (const query of queries) {
    const result = checkQueryCompatibility(query)
    results.push(result)

    if (result.compatible) {
      compatibleCount++
    } else {
      incompatibleCount++
    }

    if (!options.json && options.verbose) {
      const status = result.compatible ? formatSuccess('OK') : formatError('INCOMPATIBLE')
      console.log(`${status} ${query.substring(0, 60)}${query.length > 60 ? '...' : ''}`)
      if (result.issues && result.issues.length > 0) {
        result.issues.forEach((issue) => {
          console.log(formatDim(`  - ${issue}`))
        })
      }
      if (result.suggestion) {
        console.log(formatWarning(`  Suggestion: ${result.suggestion}`))
      }
    }
  }

  if (options.json) {
    console.log(JSON.stringify({
      success: true,
      summary: {
        total: queries.length,
        compatible: compatibleCount,
        incompatible: incompatibleCount,
      },
      results: options.verbose ? results : undefined,
    }, null, 2))
  } else {
    console.log('')
    console.log(formatInfo('Summary:'))
    console.log(formatDim(`  Total queries: ${queries.length}`))
    console.log(formatSuccess(`  Compatible: ${compatibleCount}`))
    if (incompatibleCount > 0) {
      console.log(formatError(`  Incompatible: ${incompatibleCount}`))
    }
    console.log('')

    if (incompatibleCount > 0 && !options.verbose) {
      console.log(formatWarning('Run with --verbose to see details of incompatible queries'))
    }
  }

  // Exit with error if there are incompatible queries
  if (incompatibleCount > 0) {
    process.exit(1)
  }
}