#!/usr/bin/env node
/**
 * postgres.do CLI
 *
 * Comprehensive CLI for postgres.do database management including:
 * - Database creation and management
 * - Schema introspection, generation, diff, and sync
 * - Migration management (run, status, rollback, create)
 * - Backup and restore
 * - Logs viewing
 * - Interactive SQL shell
 *
 * @example
 * ```bash
 * # Database management
 * postgres.do create mydb
 * postgres.do list
 * postgres.do delete mydb --force
 *
 * # Backup and restore
 * postgres.do backup mydb -o backup.sql
 * postgres.do restore backup.sql --database mydb
 *
 * # View logs
 * postgres.do logs mydb --tail
 *
 * # Interactive shell
 * postgres.do shell mydb
 *
 * # Schema commands
 * postgres.do introspect --url postgres://... --output ./schema.ts
 * postgres.do schema:diff --from $LOCAL_URL --to $PROD_URL
 *
 * # Migration commands
 * postgres.do migrate --url $DATABASE_URL
 * postgres.do migrate:status --url $DATABASE_URL
 * ```
 *
 * @module cli
 */

import { Command } from 'commander'
import { writeFileSync, readFileSync, existsSync } from 'node:fs'
import { resolve, dirname } from 'node:path'
import { mkdirSync } from 'node:fs'

import {
  generateSchemaFromDatabase,
  generateSchemaFromTypeScript,
  generateSchemaWithTypes,
  type SchemaGeneratorOptions,
} from '../drizzle/schema-generator.js'
import postgres from '../index.js'
import type { Sql } from '../types.js'

// Import command implementations
import { runMigrate } from './commands/migrate.js'
import { runMigrateStatus } from './commands/migrate-status.js'
import { runMigrateRollback } from './commands/migrate-rollback.js'
import { runMigrateCreate } from './commands/migrate-create.js'
import { runMigrateValidate } from './commands/migrate-validate.js'
import { runMigrateDryRun } from './commands/migrate-dry-run.js'
import { runMigrateDashboard } from './commands/migrate-dashboard.js'
import {
  runMigrateFromNeon,
  runMigrateFromSupabase,
  runMigrateValidateSchema,
  runImportPgDump,
  runQueryCompatibilityTest,
} from './commands/migrate-from-external.js'
import { runSchemaDiff } from './commands/schema-diff.js'
import { runSchemaPull, runSchemaPush } from './commands/schema-sync.js'
import { runDev } from './commands/dev.js'
import { runDevReset } from './commands/dev-reset.js'
import { runCreate, runList, runInfo, runDelete } from './commands/create.js'
import { runBackup, runBackupList, runBackupDownload } from './commands/backup.js'
import { runRestore, runRestoreFromUrl, runRestoreFromDatabase } from './commands/restore.js'
import { runLogs, runLogStats } from './commands/logs.js'
import { runShell } from './commands/shell.js'
import { loginWithApiKey, logout, getCurrentUser, isLoggedIn as _isLoggedIn } from './cli-auth.js'
import { loadConfig, updateConfig, resetConfig } from './config.js'
import { printResult, printInfo, type OutputOptions, type OutputFormat } from './output.js'

// Helper to build output options from global CLI options
function buildOutputOptions(globalOpts: { apiUrl?: string; json?: boolean; verbose?: boolean; color?: boolean }): OutputOptions & { apiUrl?: string } {
  return {
    ...(globalOpts.json ? { format: 'json' as OutputFormat } : {}),
    ...(globalOpts.verbose !== undefined ? { verbose: globalOpts.verbose } : {}),
    ...(globalOpts.color === false ? { noColor: true } : {}),
    ...(globalOpts.apiUrl ? { apiUrl: globalOpts.apiUrl } : {}),
  }
}

// ============================================================================
// CLI Setup with Commander
// ============================================================================

const program = new Command()

program
  .name('postgres.do')
  .description('PostgreSQL for Cloudflare Workers - Database management CLI')
  .version('0.0.1')

// ============================================================================
// Global Options
// ============================================================================

program
  .option('--api-url <url>', 'Override API URL')
  .option('--json', 'Output in JSON format')
  .option('--verbose', 'Enable verbose output')
  .option('--no-color', 'Disable colored output')

// ============================================================================
// Database Commands
// ============================================================================

program
  .command('create <name>')
  .description('Create a new database')
  .option('-r, --region <region>', 'Database region', 'auto')
  .option('-p, --plan <plan>', 'Database plan (free, pro, enterprise)', 'free')
  .option('--no-wait', 'Do not wait for database to be ready')
  .option('-t, --timeout <ms>', 'Wait timeout in milliseconds', '60000')
  .action(async (name, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runCreate({
      name,
      region: options.region,
      plan: options.plan,
      wait: options.wait,
      timeout: parseInt(options.timeout, 10),
      ...buildOutputOptions(globalOpts),
    })
  })

program
  .command('list')
  .alias('ls')
  .description('List all databases')
  .action(async (_options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runList(buildOutputOptions(globalOpts))
  })

program
  .command('info <name>')
  .description('Get database information')
  .action(async (name, _options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runInfo(name, buildOutputOptions(globalOpts))
  })

program
  .command('delete <name>')
  .alias('rm')
  .description('Delete a database')
  .option('-f, --force', 'Force deletion without confirmation')
  .action(async (name, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runDelete(name, {
      ...buildOutputOptions(globalOpts),
      ...(options.force ? { force: options.force } : {}),
    })
  })

// ============================================================================
// Backup Commands
// ============================================================================

program
  .command('backup <name>')
  .description('Create a backup of a database')
  .option('-o, --output <file>', 'Output file path')
  .option('-b, --backup-format <format>', 'Backup format (sql, custom, tar)', 'sql')
  .option('--schema-only', 'Backup schema only (no data)')
  .option('--data-only', 'Backup data only (no schema)')
  .option('--tables <tables>', 'Comma-separated list of tables to backup')
  .option('--exclude-tables <tables>', 'Comma-separated list of tables to exclude')
  .option('-c, --compress', 'Compress output')
  .action(async (name, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runBackup({
      name,
      output: options.output,
      backupFormat: options.backupFormat,
      schemaOnly: options.schemaOnly,
      dataOnly: options.dataOnly,
      tables: options.tables,
      excludeTables: options.excludeTables,
      compress: options.compress,
      apiUrl: globalOpts.apiUrl,
      format: globalOpts.json ? 'json' : undefined,
      verbose: globalOpts.verbose,
      noColor: globalOpts.color === false,
    })
  })

program
  .command('backup:list <name>')
  .description('List available backups for a database')
  .action(async (name, _options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runBackupList(name, buildOutputOptions(globalOpts))
  })

program
  .command('backup:download <name> <backupId>')
  .description('Download a specific backup')
  .option('-o, --output <file>', 'Output file path')
  .action(async (name, backupId, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runBackupDownload(name, backupId, {
      output: options.output,
      apiUrl: globalOpts.apiUrl,
      format: globalOpts.json ? 'json' : undefined,
      verbose: globalOpts.verbose,
      noColor: globalOpts.color === false,
    })
  })

// ============================================================================
// Restore Commands
// ============================================================================

program
  .command('restore <file>')
  .description('Restore a database from a backup file')
  .option('-d, --database <name>', 'Target database name')
  .option('--clean', 'Drop existing objects before restore')
  .option('--create', 'Create database before restore')
  .option('--data-only', 'Restore data only (no schema)')
  .option('--schema-only', 'Restore schema only (no data)')
  .option('--tables <tables>', 'Comma-separated list of tables to restore')
  .option('--ignore-errors', 'Continue on error')
  .option('-j, --jobs <n>', 'Number of parallel jobs')
  .action(async (file, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runRestore({
      file,
      database: options.database,
      clean: options.clean,
      create: options.create,
      dataOnly: options.dataOnly,
      schemaOnly: options.schemaOnly,
      tables: options.tables,
      ignoreErrors: options.ignoreErrors,
      jobs: options.jobs ? parseInt(options.jobs, 10) : undefined,
      apiUrl: globalOpts.apiUrl,
      format: globalOpts.json ? 'json' : undefined,
      verbose: globalOpts.verbose,
      noColor: globalOpts.color === false,
    })
  })

program
  .command('restore:url <url>')
  .description('Restore a database from a URL')
  .requiredOption('-d, --database <name>', 'Target database name')
  .option('--clean', 'Drop existing objects before restore')
  .option('--create', 'Create database before restore')
  .action(async (url, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runRestoreFromUrl(url, {
      file: '',
      database: options.database,
      clean: options.clean,
      create: options.create,
      apiUrl: globalOpts.apiUrl,
      format: globalOpts.json ? 'json' : undefined,
      verbose: globalOpts.verbose,
      noColor: globalOpts.color === false,
    })
  })

program
  .command('clone <source> <target>')
  .description('Clone a database to a new database')
  .option('--clean', 'Drop existing objects in target before cloning')
  .action(async (source, target, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runRestoreFromDatabase(source, target, {
      clean: options.clean,
      apiUrl: globalOpts.apiUrl,
      format: globalOpts.json ? 'json' : undefined,
      verbose: globalOpts.verbose,
      noColor: globalOpts.color === false,
    })
  })

// ============================================================================
// Logs Commands
// ============================================================================

program
  .command('logs <name>')
  .description('View database logs')
  .option('-f, --tail', 'Follow logs in real-time')
  .option('-n, --lines <n>', 'Number of lines to show', '100')
  .option('--since <duration>', 'Show logs since (e.g., "1h", "30m", "2024-01-15T10:00:00Z")')
  .option('--until <timestamp>', 'Show logs until timestamp')
  .option('-l, --level <level>', 'Filter by log level (debug, info, warning, error)')
  .option('-q, --query <query>', 'Filter by search query')
  .option('--no-timestamps', 'Hide timestamps')
  .action(async (name, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runLogs({
      name,
      tail: options.tail,
      lines: parseInt(options.lines, 10),
      since: options.since,
      until: options.until,
      level: options.level,
      query: options.query,
      timestamps: options.timestamps,
      apiUrl: globalOpts.apiUrl,
      format: globalOpts.json ? 'json' : undefined,
      verbose: globalOpts.verbose,
      noColor: globalOpts.color === false,
    })
  })

program
  .command('logs:stats <name>')
  .description('View log statistics')
  .option('--since <duration>', 'Stats since (e.g., "1h", "24h", "7d")')
  .option('--until <timestamp>', 'Stats until timestamp')
  .action(async (name, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runLogStats(name, {
      since: options.since,
      until: options.until,
      apiUrl: globalOpts.apiUrl,
      format: globalOpts.json ? 'json' : undefined,
      verbose: globalOpts.verbose,
      noColor: globalOpts.color === false,
    })
  })

// ============================================================================
// Shell Command
// ============================================================================

program
  .command('shell <name>')
  .alias('sql')
  .description('Start interactive SQL REPL')
  .option('-c, --command <sql>', 'Execute a single command and exit')
  .option('-f, --file <file>', 'Execute commands from file')
  .option('-t, --timing', 'Show query timing')
  .option('-o, --output-format <format>', 'Output format (table, csv, json, aligned)', 'table')
  .action(async (name, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runShell({
      name,
      command: options.command,
      file: options.file,
      timing: options.timing,
      outputFormat: options.outputFormat,
      apiUrl: globalOpts.apiUrl,
      format: globalOpts.json ? 'json' : undefined,
      verbose: globalOpts.verbose,
      noColor: globalOpts.color === false,
    })
  })

// ============================================================================
// Dev Commands
// ============================================================================

program
  .command('dev')
  .description('Start local development server with PGLite')
  .option('--migrations-dir <path>', 'Path to migrations directory', './migrations')
  .option('--seeds-dir <path>', 'Path to seeds directory', './seeds')
  .option('--data-dir <path>', 'Path to data directory', './.postgres.do')
  .option('--persist', 'Enable persistent storage')
  .option('--port <port>', 'Port number', '5432')
  .option('--no-watch', 'Disable file watching')
  .option('--wrangler', 'Enable wrangler integration')
  .option('--seed', 'Run seeds after migrations')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runDev({
      command: 'dev',
      url: undefined,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      migrationsDir: options.migrationsDir,
      seedsDir: options.seedsDir,
      dataDir: options.dataDir,
      persist: options.persist,
      port: parseInt(options.port, 10),
      watch: options.watch,
      wrangler: options.wrangler,
      seed: options.seed,
    })
  })

program
  .command('dev:reset')
  .description('Reset local development database')
  .option('--migrations-dir <path>', 'Path to migrations directory', './migrations')
  .option('--seeds-dir <path>', 'Path to seeds directory', './seeds')
  .option('--data-dir <path>', 'Path to data directory', './.postgres.do')
  .option('--persist', 'Enable persistent storage')
  .option('--seed', 'Run seeds after reset')
  .option('--do <id>', 'Reset specific Durable Object')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runDevReset({
      command: 'dev:reset',
      url: undefined,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      migrationsDir: options.migrationsDir,
      seedsDir: options.seedsDir,
      dataDir: options.dataDir,
      persist: options.persist,
      seed: options.seed,
      doId: options.do,
    })
  })

// ============================================================================
// Schema Commands
// ============================================================================

program
  .command('introspect')
  .description('Generate Drizzle schema from existing database')
  .requiredOption('-u, --url <url>', 'Database connection URL')
  .option('-o, --output <path>', 'Output file path', './schema.ts')
  .option('-s, --schema <name>', 'Database schema to introspect', 'public')
  .option('--include-relations', 'Include relation definitions')
  .option('--include-indexes', 'Include index definitions')
  .option('--include <tables>', 'Comma-separated list of tables to include')
  .option('--exclude <tables>', 'Comma-separated list of tables to exclude')
  .option('--types', 'Also generate TypeScript type definitions')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runIntrospect({
      url: options.url,
      output: options.output,
      schema: options.schema,
      includeRelations: options.includeRelations,
      includeIndexes: options.includeIndexes,
      includeTables: options.include?.split(','),
      excludeTables: options.exclude?.split(','),
      generateTypes: options.types,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
    })
  })

program
  .command('generate')
  .description('Generate Drizzle schema from TypeScript interfaces')
  .requiredOption('-i, --input <path>', 'Input TypeScript file with interfaces')
  .option('-o, --output <path>', 'Output file path', './schema.ts')
  .option('-s, --schema <name>', 'Schema name to use', 'public')
  .option('--include-relations', 'Include relation definitions')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runGenerate({
      input: options.input,
      output: options.output,
      schema: options.schema,
      includeRelations: options.includeRelations,
      verbose: globalOpts.verbose,
    })
  })

program
  .command('schema:diff')
  .description('Compare two database schemas')
  .option('--from <url>', 'Source database URL')
  .option('--to <url>', 'Target database URL')
  .option('-s, --schema <name>', 'Database schema to compare', 'public')
  .option('--include <tables>', 'Comma-separated list of tables to include')
  .option('--exclude <tables>', 'Comma-separated list of tables to exclude')
  .option('--include-indexes', 'Include index comparison')
  .option('--ignore-case', 'Ignore case differences in names')
  .option('--generate-migration', 'Generate migration from diff')
  .option('--migration-name <name>', 'Name for generated migration')
  .option('--migrations-dir <path>', 'Migrations directory', './migrations')
  .option('--format <type>', 'Migration format (ts, sql)', 'ts')
  .option('--safe', 'Use IF EXISTS/IF NOT EXISTS in SQL')
  .option('--exit-code', 'Exit with code 1 if schemas differ')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runSchemaDiff({
      command: 'schema:diff',
      url: undefined,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      from: options.from,
      to: options.to,
      schema: options.schema,
      includeTables: options.include?.split(','),
      excludeTables: options.exclude?.split(','),
      includeIndexes: options.includeIndexes,
      ignoreCase: options.ignoreCase,
      generateMigration: options.generateMigration,
      migrationName: options.migrationName,
      migrationsDir: options.migrationsDir,
      migrationFormat: options.format,
      safeMode: options.safe,
      exitCode: options.exitCode,
    })
  })

program
  .command('schema:pull')
  .description('Pull schema from database to local file')
  .option('-u, --url <url>', 'Database URL')
  .option('-o, --output <path>', 'Output file path', './schema.ts')
  .option('-s, --schema <name>', 'Database schema', 'public')
  .option('--include <tables>', 'Comma-separated list of tables to include')
  .option('--exclude <tables>', 'Comma-separated list of tables to exclude')
  .option('--include-relations', 'Include relation definitions')
  .option('--include-indexes', 'Include index definitions')
  .option('--types', 'Also generate TypeScript type definitions')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runSchemaPull({
      command: 'schema:pull',
      url: options.url,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      output: options.output,
      schema: options.schema,
      includeTables: options.include?.split(','),
      excludeTables: options.exclude?.split(','),
      includeRelations: options.includeRelations,
      includeIndexes: options.includeIndexes,
      generateTypes: options.types,
    })
  })

program
  .command('schema:push')
  .description('Push local schema to database')
  .option('-u, --url <url>', 'Database URL')
  .requiredOption('-i, --input <path>', 'Input Drizzle schema file')
  .option('-s, --schema <name>', 'Database schema', 'public')
  .option('--include <tables>', 'Comma-separated list of tables to include')
  .option('--exclude <tables>', 'Comma-separated list of tables to exclude')
  .option('--dry-run', 'Preview changes without applying')
  .option('-f, --force', 'Force push even with destructive changes')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runSchemaPush({
      command: 'schema:push',
      url: options.url,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      input: options.input,
      schema: options.schema,
      includeTables: options.include?.split(','),
      excludeTables: options.exclude?.split(','),
      dryRun: options.dryRun,
      force: options.force,
    })
  })

// ============================================================================
// Migration Commands
// ============================================================================

program
  .command('migrate')
  .description('Run pending migrations')
  .option('-u, --url <url>', 'Database connection URL')
  .option('--migrations-dir <path>', 'Path to migrations directory', './migrations')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runMigrate({
      command: 'migrate',
      url: options.url,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      migrationsDir: options.migrationsDir,
    })
  })

program
  .command('migrate:status')
  .description('Show current migration status')
  .option('-u, --url <url>', 'Database connection URL')
  .option('--migrations-dir <path>', 'Path to migrations directory', './migrations')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runMigrateStatus({
      command: 'migrate:status',
      url: options.url,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      migrationsDir: options.migrationsDir,
    })
  })

program
  .command('migrate:rollback')
  .description('Rollback migrations')
  .option('-u, --url <url>', 'Database connection URL')
  .option('--migrations-dir <path>', 'Path to migrations directory', './migrations')
  .option('--to <version>', 'Target version to rollback to')
  .option('-n, --steps <n>', 'Number of migrations to rollback', '1')
  .option('--dry-run', 'Preview rollback without making changes')
  .option('-f, --force', 'Force rollback of non-reversible migrations')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runMigrateRollback({
      command: 'migrate:rollback',
      url: options.url,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      migrationsDir: options.migrationsDir,
      toVersion: options.to ? parseInt(options.to, 10) : undefined,
      steps: parseInt(options.steps, 10),
      dryRun: options.dryRun,
      force: options.force,
    })
  })

program
  .command('migrate:create <name>')
  .description('Create a new migration file')
  .option('--migrations-dir <path>', 'Path to migrations directory', './migrations')
  .option('--format <type>', 'Migration format (ts, sql)', 'ts')
  .action(async (name, options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runMigrateCreate({
      command: 'migrate:create',
      url: undefined,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      migrationsDir: options.migrationsDir,
      name,
      migrationFormat: options.format,
    })
  })

program
  .command('migrate:validate')
  .description('Validate migrations before deployment')
  .option('-u, --url <url>', 'Database connection URL for syntax checking')
  .option('--migrations-dir <path>', 'Path to migrations directory', './migrations')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runMigrateValidate({
      command: 'migrate:validate',
      url: options.url,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      migrationsDir: options.migrationsDir,
    })
  })

program
  .command('migrate:dry-run')
  .description('Preview migrations without running them')
  .option('-u, --url <url>', 'Database connection URL')
  .option('--migrations-dir <path>', 'Path to migrations directory', './migrations')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runMigrateDryRun({
      command: 'migrate:dry-run',
      url: options.url,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      migrationsDir: options.migrationsDir,
    })
  })

program
  .command('migrate:dashboard')
  .description('View migration progress dashboard across all DOs')
  .option('--api-url <url>', 'API URL for postgres.do')
  .option('-k, --api-key <key>', 'API key for authentication')
  .option('-w, --watch', 'Watch mode - continuously refresh dashboard')
  .option('--refresh <ms>', 'Refresh interval in milliseconds for watch mode', '5000')
  .option('--version <n>', 'Show DOs at specific schema version')
  .option('--failed', 'Show failed migrations')
  .option('--operations', 'List all migration operations')
  .option('--operation <id>', 'Show specific operation details')
  .option('--retry <id>', 'Retry failed migrations for an operation')
  .option('-l, --limit <n>', 'Limit number of results', '100')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runMigrateDashboard({
      command: 'migrate:dashboard',
      url: undefined,
      help: undefined,
      verbose: globalOpts.verbose,
      json: globalOpts.json,
      apiUrl: options.apiUrl || globalOpts.apiUrl,
      apiKey: options.apiKey,
      watch: options.watch,
      refreshInterval: options.refresh ? parseInt(options.refresh, 10) : undefined,
      showVersion: options.version ? parseInt(options.version, 10) : undefined,
      showFailed: options.failed,
      listOperations: options.operations,
      operationId: options.operation,
      retry: options.retry,
      limit: options.limit ? parseInt(options.limit, 10) : undefined,
    })
  })

// ============================================================================
// External Migration Commands (Neon/Supabase)
// ============================================================================

program
  .command('migrate:from-neon')
  .description('Migrate from Neon to postgres.do (PGLite)')
  .requiredOption('-c, --connection-string <url>', 'Neon connection string')
  .option('-o, --output-dir <path>', 'Output directory for migration files', './migration-output')
  .option('--include-data', 'Include data migration')
  .option('--tables <tables>', 'Comma-separated list of tables to migrate')
  .option('--exclude-tables <tables>', 'Comma-separated list of tables to exclude')
  .option('--batch-size <n>', 'Batch size for data migration', '1000')
  .option('--validate', 'Validate schema compatibility before migration')
  .option('--dry-run', 'Preview migration without writing files')
  .option('--validate-integrity', 'Validate data integrity after migration')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    const opts: Parameters<typeof runMigrateFromNeon>[0] = {
      connectionString: options.connectionString,
      outputDir: options.outputDir,
      includeData: options.includeData,
      validate: options.validate,
      dryRun: options.dryRun,
      validateIntegrity: options.validateIntegrity,
      json: globalOpts.json,
      verbose: globalOpts.verbose,
    }
    if (options.tables) {
      opts.tables = options.tables.split(',')
    }
    if (options.excludeTables) {
      opts.excludeTables = options.excludeTables.split(',')
    }
    if (options.batchSize) {
      opts.batchSize = parseInt(options.batchSize, 10)
    }
    await runMigrateFromNeon(opts)
  })

program
  .command('migrate:from-supabase')
  .description('Migrate from Supabase to postgres.do (PGLite)')
  .requiredOption('-c, --connection-string <url>', 'Supabase connection string')
  .option('-o, --output-dir <path>', 'Output directory for migration files', './migration-output')
  .option('--include-data', 'Include data migration')
  .option('--tables <tables>', 'Comma-separated list of tables to migrate')
  .option('--exclude-tables <tables>', 'Comma-separated list of tables to exclude (default: Supabase internal tables)')
  .option('--batch-size <n>', 'Batch size for data migration', '1000')
  .option('--validate', 'Validate schema compatibility before migration')
  .option('--dry-run', 'Preview migration without writing files')
  .option('--validate-integrity', 'Validate data integrity after migration')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    const opts: Parameters<typeof runMigrateFromSupabase>[0] = {
      connectionString: options.connectionString,
      outputDir: options.outputDir,
      includeData: options.includeData,
      validate: options.validate,
      dryRun: options.dryRun,
      validateIntegrity: options.validateIntegrity,
      json: globalOpts.json,
      verbose: globalOpts.verbose,
    }
    if (options.tables) {
      opts.tables = options.tables.split(',')
    }
    if (options.excludeTables) {
      opts.excludeTables = options.excludeTables.split(',')
    }
    if (options.batchSize) {
      opts.batchSize = parseInt(options.batchSize, 10)
    }
    await runMigrateFromSupabase(opts)
  })

program
  .command('migrate:validate-schema')
  .description('Validate schema compatibility with PGLite')
  .requiredOption('-c, --connection-string <url>', 'Database connection string')
  .option('--strict', 'Treat warnings as errors')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runMigrateValidateSchema({
      connectionString: options.connectionString,
      strict: options.strict,
      json: globalOpts.json,
      verbose: globalOpts.verbose,
    })
  })

program
  .command('migrate:import-dump')
  .description('Import a pg_dump file and transform for PGLite')
  .requiredOption('-f, --file <path>', 'Path to the pg_dump SQL file')
  .option('-o, --output-dir <path>', 'Output directory for transformed files', './migration-output')
  .option('--validate', 'Validate schema compatibility')
  .option('--dry-run', 'Preview without writing files')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runImportPgDump({
      file: options.file,
      outputDir: options.outputDir,
      validate: options.validate,
      dryRun: options.dryRun,
      json: globalOpts.json,
      verbose: globalOpts.verbose,
    })
  })

program
  .command('migrate:test-queries')
  .description('Test query compatibility with PGLite')
  .option('-f, --queries-file <path>', 'Path to file containing queries')
  .option('-q, --query <queries...>', 'Individual queries to test')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    await runQueryCompatibilityTest({
      queriesFile: options.queriesFile,
      queries: options.query,
      json: globalOpts.json,
      verbose: globalOpts.verbose,
    })
  })

// ============================================================================
// Auth Commands
// ============================================================================

program
  .command('login')
  .description('Login to postgres.do')
  .option('-k, --api-key <key>', 'API key to use for authentication')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()

    if (options.apiKey) {
      const result = await loginWithApiKey(options.apiKey, globalOpts.apiUrl)
      if (result.success) {
        printInfo(`Logged in successfully as ${result.user?.email || 'user'}`, {
          format: globalOpts.json ? 'json' : undefined,
        })
      } else {
        printResult({ success: false, error: result.error }, {
          format: globalOpts.json ? 'json' : undefined,
        })
        process.exit(1)
      }
    } else {
      console.log('Interactive login not yet implemented.')
      console.log('Please use: postgres.do login --api-key <your-api-key>')
      console.log('Or set POSTGRES_DO_API_KEY environment variable')
      process.exit(1)
    }
  })

program
  .command('logout')
  .description('Logout from postgres.do')
  .action(async (_options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()
    logout(globalOpts.apiUrl)
    printInfo('Logged out successfully', buildOutputOptions(globalOpts))
  })

program
  .command('whoami')
  .description('Show current logged in user')
  .action(async (_options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()

    const user = await getCurrentUser(globalOpts.apiUrl)
    if (user) {
      printResult({
        success: true,
        data: user,
      }, buildOutputOptions(globalOpts))
    } else {
      printResult({
        success: false,
        error: 'Not logged in',
      }, buildOutputOptions(globalOpts))
      process.exit(1)
    }
  })

// ============================================================================
// Config Commands
// ============================================================================

program
  .command('config')
  .description('View or update CLI configuration')
  .option('--get <key>', 'Get a configuration value')
  .option('--set <key=value>', 'Set a configuration value')
  .option('--reset', 'Reset configuration to defaults')
  .action(async (options, cmd) => {
    const globalOpts = cmd.optsWithGlobals()

    if (options.reset) {
      resetConfig()
      printInfo('Configuration reset to defaults', {
        format: globalOpts.json ? 'json' : undefined,
      })
    } else if (options.get) {
      const config = loadConfig()
      const value = (config as Record<string, unknown>)[options.get]
      if (globalOpts.json) {
        console.log(JSON.stringify({ [options.get]: value }))
      } else {
        console.log(value !== undefined ? value : '(not set)')
      }
    } else if (options.set) {
      const [key, ...valueParts] = options.set.split('=')
      const value = valueParts.join('=')
      updateConfig({ [key]: value } as Record<string, unknown>)
      printInfo(`Set ${key} = ${value}`, {
        format: globalOpts.json ? 'json' : undefined,
      })
    } else {
      const config = loadConfig()
      printResult({
        success: true,
        data: config,
      }, {
        format: globalOpts.json ? 'json' : undefined,
      })
    }
  })

// ============================================================================
// Helper Functions
// ============================================================================

function ensureDirectoryExists(filePath: string): void {
  const dir = dirname(filePath)
  if (!existsSync(dir)) {
    mkdirSync(dir, { recursive: true })
  }
}

interface IntrospectOptions {
  url: string
  output: string
  schema: string
  includeRelations?: boolean
  includeIndexes?: boolean
  includeTables?: string[]
  excludeTables?: string[]
  generateTypes?: boolean
  verbose?: boolean
  json?: boolean
}

async function runIntrospect(options: IntrospectOptions): Promise<void> {
  const url = options.url || process.env['DATABASE_URL']

  if (!url) {
    console.error('Error: Database URL is required')
    console.error('Provide --url or set DATABASE_URL environment variable')
    process.exit(1)
  }

  const outputPath = resolve(process.cwd(), options.output || './schema.ts')

  console.log('Connecting to database...')

  const sql: Sql = postgres(url)

  try {
    const generatorOptions: SchemaGeneratorOptions = {}
    if (options.schema !== undefined) generatorOptions.schema = options.schema
    if (options.includeRelations !== undefined) generatorOptions.includeRelations = options.includeRelations
    if (options.includeIndexes !== undefined) generatorOptions.includeIndexes = options.includeIndexes
    if (options.includeTables !== undefined) generatorOptions.includeTables = options.includeTables
    if (options.excludeTables !== undefined) generatorOptions.excludeTables = options.excludeTables

    console.log(`Introspecting schema '${options.schema || 'public'}'...`)

    if (options.generateTypes) {
      const { schema, types } = await generateSchemaWithTypes(sql, generatorOptions)

      ensureDirectoryExists(outputPath)
      writeFileSync(outputPath, schema)
      console.log(`Schema written to: ${outputPath}`)

      const typesPath = outputPath.replace(/\.ts$/, '.types.ts')
      writeFileSync(typesPath, types)
      console.log(`Types written to: ${typesPath}`)
    } else {
      const schema = await generateSchemaFromDatabase(sql, generatorOptions)

      ensureDirectoryExists(outputPath)
      writeFileSync(outputPath, schema)
      console.log(`Schema written to: ${outputPath}`)
    }

    console.log('Done!')
  } finally {
    await sql.end()
  }
}

interface GenerateOptions {
  input: string
  output: string
  schema: string
  includeRelations?: boolean
  verbose?: boolean
}

async function runGenerate(options: GenerateOptions): Promise<void> {
  if (!options.input) {
    console.error('Error: Input file is required')
    console.error('Provide --input with path to TypeScript file')
    process.exit(1)
  }

  const inputPath = resolve(process.cwd(), options.input)
  const outputPath = resolve(process.cwd(), options.output || './schema.ts')

  if (!existsSync(inputPath)) {
    console.error(`Error: Input file not found: ${inputPath}`)
    process.exit(1)
  }

  console.log(`Reading types from: ${inputPath}`)

  const source = readFileSync(inputPath, 'utf-8')

  const generatorOptions: SchemaGeneratorOptions = {}
  if (options.schema !== undefined) generatorOptions.schema = options.schema
  if (options.includeRelations !== undefined) generatorOptions.includeRelations = options.includeRelations

  console.log('Generating Drizzle schema...')

  const schema = generateSchemaFromTypeScript(source, generatorOptions)

  ensureDirectoryExists(outputPath)
  writeFileSync(outputPath, schema)

  console.log(`Schema written to: ${outputPath}`)
  console.log('Done!')
}

// ============================================================================
// Main
// ============================================================================

program.parse()

// Export for programmatic use
export { program }
export { runMigrate, runMigrateStatus, runMigrateRollback, runMigrateCreate, runMigrateValidate, runMigrateDryRun, runMigrateDashboard }
export { runMigrateFromNeon, runMigrateFromSupabase, runMigrateValidateSchema, runImportPgDump, runQueryCompatibilityTest }
export { runSchemaDiff, runSchemaPull, runSchemaPush }
export { runDev, runDevReset }
export { runCreate, runList, runInfo, runDelete }
export { runBackup, runBackupList, runBackupDownload }
export { runRestore, runRestoreFromUrl, runRestoreFromDatabase }
export { runLogs, runLogStats }
export { runShell }