import { Command } from 'commander';

interface Options {
  direction: 'up' | 'down';
  version?: string;
  dbType: 'postgresql' | 'mysql' | 'sqlite';
  connectionString: string;
  verbose?: boolean;
  dryRun?: boolean;
}

export function createCLI(): Command {
  const program = new Command();
  
  program
    .name('migrate-cli')
    .description('Database migration tool for PostgreSQL, MySQL, and SQLite')
    .version('1.0.0', '-v, --version', 'Display version number')
    .helpOption('-h, --help', 'Display help information');
  
  program
    .command('migrate')
    .description('Run database migrations')
    .option(
      '-d, --direction <direction>',
      'Migration direction: up (apply) or down (rollback)',
      'up'
    )
    .option(
      '--version <version>',
      'Target migration version (timestamp or name)'
    )
    .requiredOption(
      '--db-type <type>',
      'Database type: postgresql, mysql, or sqlite'
    )
    .requiredOption(
      '--connection-string <string>',
      'Database connection string (e.g., postgresql://user:pass@host:5432/db)'
    )
    .option(
      '--verbose',
      'Enable verbose logging (show SQL statements)',
      false
    )
    .option(
      '--dry-run',
      'Simulate migrations without applying changes',
      false
    )
    .action(async (options: Options) => {
      try {
        // Validate options
        validateOptions(options);
        
        // Execute command
        const result = await runMigration(options);
        
        // Success output
        console.log(`✅ Migration ${options.direction} completed successfully`);
        if (options.verbose) {
          console.log(JSON.stringify(result, null, 2));
        }
        
        process.exit(0);
      } catch (error) {
        handleCLIError(error);
      }
    });
  
  return program;
}

// Usage
const program = createCLI();
program.parse(process.argv);

const program = new Command();

program
  .name('myapp')
  .description('Multi-purpose development tool')
  .version('2.0.0');

// Sub-command: init
program
  .command('init')
  .description('Initialize a new project')
  .option('-t, --template <name>', 'Project template', 'default')
  .action(async (options) => {
    await initProject(options);
  });

// Sub-command: deploy
program
  .command('deploy')
  .description('Deploy application to production')
  .option('-e, --environment <env>', 'Target environment', 'production')
  .option('--skip-tests', 'Skip test suite', false)
  .action(async (options) => {
    await deployApp(options);
  });

// Sub-command: status
program
  .command('status')
  .description('Check application status')
  .action(async () => {
    await showStatus();
  });

program.parse(process.argv);

import { z } from 'zod';

const OptionsSchema = z.object({
  direction: z.enum(['up', 'down'], {
    errorMap: () => ({ message: 'Direction must be "up" or "down"' }),
  }),
  version: z.string().regex(/^\d{14}$/).optional(),
  dbType: z.enum(['postgresql', 'mysql', 'sqlite'], {
    errorMap: () => ({ message: 'Database type must be postgresql, mysql, or sqlite' }),
  }),
  connectionString: z.string().min(1, 'Connection string is required'),
  verbose: z.boolean().optional(),
  dryRun: z.boolean().optional(),
});

function validateOptions(options: unknown): asserts options is Options {
  const result = OptionsSchema.safeParse(options);
  
  if (!result.success) {
    const errors = result.error.errors.map((err) => {
      return `  • ${err.path.join('.')}: ${err.message}`;
    }).join('\n');
    
    console.error('❌ Invalid options:\n' + errors);
    console.error('\nRun with --help for usage information');
    process.exit(1);
  }
}

function validateOptions(options: Options): void {
  // 1. Validate direction
  if (!['up', 'down'].includes(options.direction)) {
    console.error(`❌ Error: direction must be "up" or "down", got "${options.direction}"`);
    process.exit(1);
  }
  
  // 2. Validate version format (if provided)
  if (options.version && !/^\d{14}$/.test(options.version)) {
    console.error(`❌ Error: version must be a 14-digit timestamp, got "${options.version}"`);
    console.error('   Example: 20260131120000');
    process.exit(1);
  }
  
  // 3. Validate database type
  const validDbTypes = ['postgresql', 'mysql', 'sqlite'];
  if (!validDbTypes.includes(options.dbType)) {
    console.error(`❌ Error: db-type must be one of: ${validDbTypes.join(', ')}`);
    console.error(`   Got: "${options.dbType}"`);
    process.exit(1);
  }
  
  // 4. Validate connection string format
  if (options.dbType === 'postgresql') {
    if (!options.connectionString.startsWith('postgresql://')) {
      console.error('❌ Error: PostgreSQL connection string must start with "postgresql://"');
      console.error('   Example: postgresql://user:pass@localhost:5432/mydb');
      process.exit(1);
    }
  }
  
  // 5. Validate dry-run + version combination
  if (options.dryRun && options.version) {
    console.warn('⚠️  Warning: --dry-run with specific version may not show accurate results');
  }
}

program
  .command('migrate')
  .description('Run database migrations')
  .addHelpText('before', `
Migration Tool - Apply or rollback database schema changes

Usage:
  $ migrate-cli migrate --db-type postgresql --connection-string "postgresql://..."
  
Examples:
  # Apply all pending migrations
  $ migrate-cli migrate --db-type postgresql --connection-string "postgresql://user:pass@localhost:5432/mydb"
  
  # Rollback one migration
  $ migrate-cli migrate --direction down --db-type mysql --connection-string "mysql://..."
  
  # Apply specific migration
  $ migrate-cli migrate --version 20260131120000 --db-type sqlite --connection-string "file:./db.sqlite"
  
  # Dry run (preview changes)
  $ migrate-cli migrate --dry-run --verbose --db-type postgresql --connection-string "..."
`)
  .addHelpText('after', `
Configuration:
  Connection strings can also be provided via environment variables:
    DATABASE_URL - Database connection string
    DB_TYPE      - Database type (postgresql, mysql, sqlite)
    
Exit Codes:
  0 - Success
  1 - General error (invalid options, validation failure)
  2 - Database connection error
  3 - Migration execution error
  
For more information, visit: https://github.com/user/migrate-cli
`);

program
  .option(
    '-d, --direction <direction>',
    [
      'Migration direction:',
      '  up   - Apply pending migrations (default)',
      '  down - Rollback the last applied migration',
    ].join('\n'),
    'up'
  )
  .option(
    '--version <version>',
    [
      'Target migration version (14-digit timestamp):',
      '  Example: 20260131120000',
      '  Applies all migrations up to (or down to) this version',
    ].join('\n')
  );

export enum ExitCode {
  SUCCESS = 0,              // Successful execution
  GENERAL_ERROR = 1,        // General error (invalid options, validation)
  MISUSE = 2,              // Incorrect usage (missing required options)
  CONNECTION_ERROR = 3,     // Database/network connection error
  EXECUTION_ERROR = 4,      // Migration/operation execution error
  PERMISSION_ERROR = 5,     // Permission denied
  NOT_FOUND = 6,           // Resource not found (migration file)
  TIMEOUT = 7,             // Operation timed out
}

function handleCLIError(error: unknown): never {
  if (error instanceof ValidationError) {
    console.error(`❌ Validation Error: ${error.message}`);
    for (const [field, message] of Object.entries(error.fields)) {
      console.error(`  • ${field}: ${message}`);
    }
    process.exit(ExitCode.GENERAL_ERROR);
  }
  
  if (error instanceof DatabaseConnectionError) {
    console.error(`❌ Database Connection Error: ${error.message}`);
    console.error('   Please check your connection string and network connectivity');
    process.exit(ExitCode.CONNECTION_ERROR);
  }
  
  if (error instanceof MigrationExecutionError) {
    console.error(`❌ Migration Execution Error: ${error.message}`);
    if (error.cause) {
      console.error('   Caused by:', error.cause);
    }
    process.exit(ExitCode.EXECUTION_ERROR);
  }
  
  if (error instanceof ConfigurationError) {
    console.error(`❌ Configuration Error: ${error.message}`);
    console.error('   Run with --help for configuration options');
    process.exit(ExitCode.GENERAL_ERROR);
  }
  
  // Unknown error
  console.error('❌ Unexpected Error:', error);
  if (error instanceof Error && error.stack) {
    console.error('\nStack trace:');
    console.error(error.stack);
  }
  process.exit(ExitCode.GENERAL_ERROR);
}

// ✅ DO: User-friendly error messages with context
❌ Database Connection Error: Failed to connect to PostgreSQL
   Please check your connection string and network connectivity
   Connection string: postgresql://user:***@localhost:5432/mydb
   Error: ECONNREFUSED

// ❌ DON'T: Cryptic error dumps
Error: connect ECONNREFUSED 127.0.0.1:5432
    at TCPConnectWrap.afterConnect [as oncomplete]
    ...

import ora from 'ora';

async function runMigration(options: Options): Promise<void> {
  const spinner = ora('Connecting to database...').start();
  
  try {
    // Connect
    await database.connect();
    spinner.succeed('Connected to database');
    
    // Load migrations
    spinner.start('Loading migration files...');
    const migrations = await loadMigrations(options.directory);
    spinner.succeed(`Loaded ${migrations.length} migrations`);
    
    // Execute
    spinner.start(`Applying migrations...`);
    for (const migration of migrations) {
      spinner.text = `Applying migration: ${migration.name}`;
      await database.executeMigration(migration);
    }
    spinner.succeed(`Applied ${migrations.length} migrations`);
    
  } catch (error) {
    spinner.fail('Migration failed');
    throw error;
  } finally {
    await database.disconnect();
  }
}

import cliProgress from 'cli-progress';

async function runMigrations(migrations: Migration[]): Promise<void> {
  const bar = new cliProgress.SingleBar({
    format: 'Progress |{bar}| {percentage}% | {value}/{total} migrations | Current: {migration}',
  }, cliProgress.Presets.shades_classic);
  
  bar.start(migrations.length, 0, { migration: '' });
  
  for (let i = 0; i < migrations.length; i++) {
    const migration = migrations[i];
    bar.update(i, { migration: migration.name });
    await executeMigration(migration);
    bar.update(i + 1);
  }
  
  bar.stop();
}

program
  .option('--json', 'Output results as JSON')
  .action(async (options) => {
    const result = await runMigration(options);
    
    if (options.json) {
      // Machine-readable output
      console.log(JSON.stringify({
        status: 'success',
        migrationsApplied: result.migrations.map(m => m.name),
        duration: result.durationMs,
        timestamp: new Date().toISOString(),
      }));
    } else {
      // Human-readable output
      console.log('✅ Migration completed successfully');
      console.log(`   Applied ${result.migrations.length} migrations in ${result.durationMs}ms`);
      for (const migration of result.migrations) {
        console.log(`   • ${migration.name}`);
      }
    }
  });

interface Config {
  dbType: string;
  connectionString: string;
  migrationsDir: string;
  verbose: boolean;
}

function loadConfiguration(options: Partial<Options>): Config {
  // 1. Start with defaults
  const config: Config = {
    dbType: 'postgresql',
    connectionString: '',
    migrationsDir: './migrations',
    verbose: false,
  };
  
  // 2. Load from config file (if exists)
  const configFile = process.env.MIGRATE_CONFIG || './migrate.config.json';
  if (fs.existsSync(configFile)) {
    const fileConfig = JSON.parse(fs.readFileSync(configFile, 'utf-8'));
    Object.assign(config, fileConfig);
  }
  
  // 3. Override with environment variables
  if (process.env.DB_TYPE) {
    config.dbType = process.env.DB_TYPE;
  }
  if (process.env.DATABASE_URL) {
    config.connectionString = process.env.DATABASE_URL;
  }
  if (process.env.MIGRATIONS_DIR) {
    config.migrationsDir = process.env.MIGRATIONS_DIR;
  }
  
  // 4. Override with CLI flags (highest priority)
  if (options.dbType) {
    config.dbType = options.dbType;
  }
  if (options.connectionString) {
    config.connectionString = options.connectionString;
  }
  if (options.verbose) {
    config.verbose = true;
  }
  
  return config;
}

import inquirer from 'inquirer';

async function promptForMissingOptions(options: Partial<Options>): Promise<Options> {
  const questions = [];
  
  if (!options.dbType) {
    questions.push({
      type: 'list',
      name: 'dbType',
      message: 'Select database type:',
      choices: ['postgresql', 'mysql', 'sqlite'],
    });
  }
  
  if (!options.connectionString) {
    questions.push({
      type: 'input',
      name: 'connectionString',
      message: 'Enter database connection string:',
      validate: (input: string) => {
        if (!input) return 'Connection string is required';
        return true;
      },
    });
  }
  
  if (!options.direction) {
    questions.push({
      type: 'list',
      name: 'direction',
      message: 'Migration direction:',
      choices: ['up', 'down'],
      default: 'up',
    });
  }
  
  const answers = await inquirer.prompt(questions);
  return { ...options, ...answers } as Options;
}

// Usage
.action(async (options: Partial<Options>) => {
  const completeOptions = await promptForMissingOptions(options);
  await runMigration(completeOptions);
});

// build.ts
import * as esbuild from 'esbuild';
import * as fs from 'fs';

async function build() {
  try {
    // Build CLI bundle
    await esbuild.build({
      entryPoints: ['src/cli.ts'],
      bundle: true,
      platform: 'node',
      target: 'node20',
      outfile: 'dist/cli.js',
      format: 'cjs',
      minify: true,
      sourcemap: true,
      external: [
        // Don't bundle native modules
        'pg-native',
        'better-sqlite3',
      ],
      banner: {
        js: '#!/usr/bin/env node\n', // Shebang for executability
      },
    });
    
    // Make executable
    fs.chmodSync('dist/cli.js', 0o755);
    
    console.log('✅ Build successful: dist/cli.js');
    console.log(`   Size: ${(fs.statSync('dist/cli.js').size / 1024).toFixed(2)} KB`);
  } catch (error) {
    console.error('❌ Build failed:', error);
    process.exit(1);
  }
}

build();

{
  "name": "migrate-cli",
  "version": "1.0.0",
  "description": "Database migration CLI tool",
  "main": "dist/cli.js",
  "bin": {
    "migrate-cli": "./dist/cli.js"
  },
  "files": [
    "dist"
  ],
  "scripts": {
    "build": "tsx build.ts",
    "dev": "tsx watch src/cli.ts",
    "prepublishOnly": "npm run build"
  },
  "keywords": [
    "cli",
    "migration",
    "database",
    "postgresql",
    "mysql",
    "sqlite"
  ],
  "engines": {
    "node": ">=20.0.0"
  }
}

# 1. Test locally
npm link
migrate-cli --help

# 2. Test in another directory
cd /tmp && migrate-cli migrate --help

# 3. Validate package
npm pack --dry-run

# 4. Check what will be published
npm publish --dry-run

# 5. Publish to npm
npm publish --access public

import { execSync } from 'child_process';

describe('CLI - migrate command', () => {
  it('should show help text', () => {
    const output = execSync('node dist/cli.js migrate --help', {
      encoding: 'utf-8',
    });
    
    expect(output).toContain('Run database migrations');
    expect(output).toContain('--db-type');
    expect(output).toContain('--connection-string');
  });
  
  it('should exit with code 1 for invalid direction', () => {
    try {
      execSync('node dist/cli.js migrate --direction invalid', {
        encoding: 'utf-8',
      });
      fail('Should have thrown');
    } catch (error: any) {
      expect(error.status).toBe(1);
      expect(error.stderr.toString()).toContain('direction must be "up" or "down"');
    }
  });
  
  it('should require connection string', () => {
    try {
      execSync('node dist/cli.js migrate --db-type postgresql', {
        encoding: 'utf-8',
      });
      fail('Should have thrown');
    } catch (error: any) {
      expect(error.status).toBe(1);
      expect(error.stderr.toString()).toContain('required option');
    }
  });
});

describe('CLI error handling', () => {
  let exitSpy: jest.SpyInstance;
  let consoleErrorSpy: jest.SpyInstance;
  
  beforeEach(() => {
    exitSpy = jest.spyOn(process, 'exit').mockImplementation();
    consoleErrorSpy = jest.spyOn(console, 'error').mockImplementation();
  });
  
  afterEach(() => {
    exitSpy.mockRestore();
    consoleErrorSpy.mockRestore();
  });
  
  it('should exit with code 1 for validation error', () => {
    handleCLIError(new ValidationError('email', 'Invalid format'));
    
    expect(exitSpy).toHaveBeenCalledWith(ExitCode.GENERAL_ERROR);
    expect(consoleErrorSpy).toHaveBeenCalledWith(
      expect.stringContaining('Validation Error')
    );
  });
  
  it('should exit with code 3 for connection error', () => {
    handleCLIError(new DatabaseConnectionError('Connection refused'));
    
    expect(exitSpy).toHaveBeenCalledWith(ExitCode.CONNECTION_ERROR);
    expect(consoleErrorSpy).toHaveBeenCalledWith(
      expect.stringContaining('Database Connection Error')
    );
  });
});

## CLI Implementation Evidence

**Tool**: migrate-cli
**Framework**: Commander.js 12.x
**Build**: ESBuild with Node.js target

### Commands Implemented
- `migrate` - Run database migrations (up/down)

### Options Validated
- `--direction` - Enum validation (up|down)
- `--db-type` - Enum validation (postgresql|mysql|sqlite)
- `--connection-string` - Required, format validation
- `--version` - Optional, regex validation (14-digit timestamp)
- `--verbose` - Boolean flag
- `--dry-run` - Boolean flag

### Help Text
- ✅ Command descriptions
- ✅ Usage examples (3 examples provided)
- ✅ Configuration documentation
- ✅ Exit codes documented

### Error Handling
- ✅ Custom exit codes (0-7)
- ✅ User-friendly error messages
- ✅ Validation errors show context
- ✅ Database errors include suggestions

### Testing
- 12 CLI tests (exit codes, validation, help text)
- 100% coverage of error paths