import { Command } from 'commander';
import chalk from 'chalk';
import ora from 'ora';
import * as net from 'node:net';
import { readFileSync } from 'node:fs';
import { extname } from 'node:path';
import { createInterface } from 'node:readline';
import { WebSocket } from 'ws';
import { api, getCurrentToken, getApiBaseUrl, ApiError } from '../api.js';
import { resolveProject, handleJson, withErrorHandler } from '../util.js';

/**
 * Read a single line from the terminal. When `hidden` is set, the input is
 * masked with `*` and never echoed — used for the migration source DB
 * password so it does not appear on screen or in shell history. Mirrors the
 * hidden-prompt implementation in commands/auth.ts.
 */
function promptLine(question: string, hidden = false): Promise<string> {
  return new Promise((resolve) => {
    const rl = createInterface({ input: process.stdin, output: process.stdout });

    if (!hidden) {
      rl.question(question, (answer) => {
        rl.close();
        resolve(answer);
      });
      return;
    }

    process.stdout.write(question);
    rl.close();
    const stdin = process.stdin;
    stdin.resume();
    const wasRaw = stdin.isRaw;
    if (stdin.isTTY) stdin.setRawMode(true);
    let input = '';
    const onData = (ch: Buffer) => {
      const c = ch.toString();
      if (c === '\n' || c === '\r') {
        stdin.removeListener('data', onData);
        if (stdin.isTTY && wasRaw !== undefined) stdin.setRawMode(wasRaw);
        stdin.pause();
        process.stdout.write('\n');
        resolve(input);
      } else if (c === '') {
        process.exit(0);
      } else if (c === '' || c === '\b') {
        if (input.length > 0) {
          input = input.slice(0, -1);
          process.stdout.write('\b \b');
        }
      } else {
        input += c;
        process.stdout.write('*');
      }
    };
    stdin.on('data', onData);
  });
}

interface ParsedSource {
  host: string;
  port?: number;
  database: string;
  user: string;
  password?: string;
}

/**
 * Parse a DB connection URL (e.g. postgres://user:pass@host:5432/dbname) into
 * its discrete parts. The password, if embedded, is extracted but never
 * logged back out. Throws on a URL that is missing host/db/user.
 */
function parseSourceUrl(raw: string): ParsedSource {
  let u: URL;
  try {
    u = new URL(raw);
  } catch {
    throw new Error('--source-url is not a valid connection URL.');
  }
  const database = u.pathname.replace(/^\//, '');
  if (!u.hostname) throw new Error('--source-url is missing a host.');
  if (!database) throw new Error('--source-url is missing a database name.');
  if (!u.username) throw new Error('--source-url is missing a user.');
  return {
    host: u.hostname,
    port: u.port ? Number(u.port) : undefined,
    database,
    user: decodeURIComponent(u.username),
    password: u.password ? decodeURIComponent(u.password) : undefined,
  };
}

const MIGRATION_TERMINAL = new Set(['completed', 'failed', 'canceled']);

function migrationProgressLabel(m: any): string {
  const parts: string[] = [];
  if (m.phase) parts.push(String(m.phase));
  if (typeof m.rows_copied === 'number' && typeof m.rows_total === 'number') {
    parts.push(`${m.rows_copied}/${m.rows_total} rows`);
  }
  if (m.message) parts.push(String(m.message));
  return parts.join(' — ') || String(m.status);
}

const sleep = (ms: number) => new Promise<void>((r) => setTimeout(r, ms));

function printQueryRows(data: any): void {
  console.log(chalk.gray(`${data.row_count} row(s) in ${data.duration_ms}ms`));
  if (data.read_only) {
    const note = data.auto_limited ? 'read-only mode, limit applied automatically' : 'read-only mode';
    console.log(chalk.gray(`${note}\n`));
  } else {
    console.log();
  }

  if (!data.rows || data.rows.length === 0) {
    return;
  }

  const keys = Object.keys(data.rows[0]);
  const widths = keys.map((k) => Math.max(k.length, ...data.rows.map((r: any) => String(r[k] ?? 'NULL').length)));

  console.log(keys.map((k, i) => chalk.bold(k.padEnd(widths[i]))).join('  '));
  console.log(keys.map((_k, i) => '─'.repeat(widths[i])).join('  '));

  for (const row of data.rows.slice(0, 50)) {
    console.log(keys.map((k, i) => String(row[k] ?? chalk.gray('NULL')).padEnd(widths[i])).join('  '));
  }

  if (data.rows.length > 50) {
    console.log(chalk.gray(`\n... and ${data.rows.length - 50} more rows`));
  }
}

export function registerDbCommands(program: Command): void {
  const db = program.command('db').description('Database management');

  db.command('info <idOrName>')
    .description('Show database connection info')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, opts: { json?: boolean }) => {
        const project = await resolveProject(String(idOrName));
        const spinner = ora('Fetching database info...').start();
        const data = await api<Record<string, unknown>>(`/projects/${project.id}/database`);
        spinner.stop();

        handleJson(opts.json, data);

        console.log(chalk.bold(`Database for ${project.name}:`));
        // Pack 1 server resolution returns { status: "not-configured" } when
        // the project has no managed DB. Handle that case before iterating
        // field-by-field so we don't print a wall of `null`s.
        if (data.status === 'not-configured') {
          console.log(chalk.gray('  No managed database on this project.'));
          if (typeof data.note === 'string') {
            console.log(chalk.gray(`  ${data.note}`));
          }
          return;
        }
        const fields: Array<[string, string]> = [
          ['type', 'Type'],
          ['status', 'Status'],
          ['host', 'Host'],
          ['port', 'Port'],
          ['database', 'Database'],
          ['username', 'Username'],
          ['source', 'Source'],
          ['size_mb', 'Size (MB)'],
          ['tables', 'Tables'],
        ];
        for (const [key, label] of fields) {
          const v = data[key];
          if (v !== undefined && v !== null && v !== '') {
            console.log(`  ${chalk.blue(label + ':')}  ${v}`);
          }
        }
      }),
    );

  db.command('schema <idOrName>')
    .description('Show database schema — tables, columns, indexes')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, opts: { json?: boolean }) => {
        const project = await resolveProject(String(idOrName));
        const spinner = ora('Fetching schema...').start();
        const data = await api<any>(`/projects/${project.id}/database/schema`);
        spinner.stop();

        handleJson(opts.json, data);

        console.log(chalk.bold(`Schema: ${data.database}`));
        console.log(chalk.gray(`${data.tables?.length || 0} tables, ${data.total_rows || 0} rows, ${data.total_size_kb || 0} KB\n`));

        for (const table of data.tables || []) {
          console.log(chalk.bold.blue(`  ${table.name}`) + chalk.gray(` (${table.rows} rows, ${table.size_kb} KB)`));
          for (const col of table.columns || []) {
            const keyBadge = col.key === 'PRI' ? chalk.yellow(' PK') : col.key === 'UNI' ? chalk.cyan(' UQ') : col.key === 'MUL' ? chalk.gray(' IDX') : '';
            const nullable = col.nullable ? chalk.gray(' NULL') : '';
            const extra = col.extra ? chalk.gray(` ${col.extra}`) : '';
            console.log(`    ${chalk.white(col.name.padEnd(25))} ${chalk.gray(col.type)}${keyBadge}${nullable}${extra}`);
          }
          console.log();
        }
      }),
    );

  db.command('query <idOrName> <sql>')
    .description('Run a read-only SQL query against the project database')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, sql: unknown, opts: { json?: boolean }) => {
        const project = await resolveProject(String(idOrName));
        const spinner = ora('Executing query...').start();
        const data = await api<any>(`/projects/${project.id}/database/query`, {
          method: 'POST',
          body: { sql: String(sql) },
        });
        spinner.stop();

        if (data.error) {
          console.error(chalk.red(`Error: ${data.error}`));
          return;
        }

        handleJson(opts.json, data);
        printQueryRows(data);
      }),
    );

  db.command('recall <idOrName> <sql>')
    .description('Recall records with an explicitly read-only SQL query')
    .option('--limit <n>', 'Auto-limit SELECT/WITH queries without LIMIT (default: 100)')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, sql: unknown, opts: { json?: boolean; limit?: string }) => {
        const project = await resolveProject(String(idOrName));
        const spinner = ora('Recalling records...').start();
        const body: Record<string, unknown> = { sql: String(sql) };
        if (opts.limit) body.limit = Number(opts.limit);
        const data = await api<any>(`/projects/${project.id}/database/recall`, {
          method: 'POST',
          body,
        });
        spinner.stop();

        if (data.error) {
          console.error(chalk.red(`Error: ${data.error}`));
          return;
        }

        handleJson(opts.json, data);
        printQueryRows(data);
      }),
    );

  db
    .command('import <idOrName> <file>')
    .description('Bulk insert or upsert a JSON array or CSV file into a project table')
    .requiredOption('--table <name>', 'Target table')
    .option('--mode <mode>', 'insert or upsert (default: insert)', 'insert')
    .option(
      '--key <col>',
      'Conflict key (repeatable) — required for upsert',
      (v: string, acc: string[]) => [...acc, v],
      [] as string[],
    )
    .option('--dry-run', 'Run without committing; returns a confirmation token')
    .option('--confirm <token>', 'Commit using the token from a prior --dry-run')
    .option('--batch-size <n>', 'Rows per batch (default 500)')
    .option('--allow-extra-columns', 'Allow payload columns not in the target table')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(
        async (
          idOrName: unknown,
          file: unknown,
          opts: {
            table: string;
            mode: 'insert' | 'upsert';
            key: string[];
            dryRun?: boolean;
            confirm?: string;
            batchSize?: string;
            allowExtraColumns?: boolean;
            json?: boolean;
          },
        ) => {
          if (!opts.dryRun && !opts.confirm) {
            console.error(
              chalk.red(
                'Error: must pass either --dry-run or --confirm <token>. Two-phase commit is required.',
              ),
            );
            process.exit(1);
          }
          if (opts.mode === 'upsert' && opts.key.length === 0) {
            console.error(chalk.red('Error: --mode upsert requires at least one --key <col>'));
            process.exit(1);
          }

          const project = await resolveProject(String(idOrName));
          const filePath = String(file);
          const ext = extname(filePath).toLowerCase();
          const format = ext === '.csv' ? 'csv' : 'json';
          const payload = readFileSync(filePath, 'utf8');

          const spinner = ora(opts.dryRun ? 'Running dry-run import...' : 'Committing import...').start();
          const data = await api<any>(`/projects/${project.id}/database/import`, {
            method: 'POST',
            body: {
              table: opts.table,
              mode: opts.mode,
              conflict_keys: opts.key.length ? opts.key : undefined,
              dry_run: !!opts.dryRun,
              confirm: opts.confirm,
              payload,
              format,
              allow_extra_columns: !!opts.allowExtraColumns,
              batch_size: opts.batchSize ? Number(opts.batchSize) : undefined,
            },
          });
          spinner.stop();

          handleJson(opts.json, data);

          if (data.phase === 'dry_run') {
            console.log(chalk.bold('Dry-run OK'));
            console.log(`Rows seen:     ${data.rows_seen}`);
            console.log(`Would insert:  ${data.rows_inserted}`);
            console.log(`Would update:  ${data.rows_updated}`);
            console.log(chalk.gray(`Token:         ${data.confirm_token}`));
            console.log(
              chalk.gray(
                `Token expires: ${new Date(data.token_expires_at * 1000).toISOString()}`,
              ),
            );
            console.log('');
            console.log('Commit with:');
            const keyFlags = opts.key.length
              ? ' ' + opts.key.map((k: string) => `--key ${k}`).join(' ')
              : '';
            console.log(
              `  dailey db import ${String(idOrName)} ${filePath} --table ${opts.table} --mode ${opts.mode}${keyFlags} --confirm ${data.confirm_token}`,
            );
          } else if (data.phase === 'committed') {
            console.log(chalk.green.bold('Imported'));
            console.log(`Rows inserted: ${data.rows_inserted}`);
            console.log(`Rows updated:  ${data.rows_updated}`);
            console.log(`Checksum:      ${data.checksum_sha256}`);
            console.log(`Audit id:      ${data.audit_id}`);
            console.log(`Duration:      ${data.duration_ms}ms`);
          } else if (data.phase === 'failed') {
            console.log(chalk.red.bold('Import failed validation'));
            console.log(`Rows seen:    ${data.rows_seen}`);
            console.log(`Errors:       ${data.errors?.length ?? 0}`);
            for (const e of (data.errors ?? []).slice(0, 10)) {
              console.log(
                `  row ${e.row_index}${e.column ? ` col ${e.column}` : ''}: ${e.message}`,
              );
            }
            if ((data.errors?.length ?? 0) > 10) {
              console.log(chalk.gray(`  (+${data.errors.length - 10} more)`));
            }
            process.exit(1);
          }
        },
      ),
    );

  db.command('migrations <idOrName>')
    .description('Show migration status for a project')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, opts: { json?: boolean }) => {
        const project = await resolveProject(String(idOrName));
        const spinner = ora('Checking migrations...').start();
        const data = await api<any>(`/projects/${project.id}/database/migrations`);
        spinner.stop();

        handleJson(opts.json, data);

        if (!data.has_migration_table) {
          console.log(chalk.yellow('No _migrations table found.'));
          console.log(chalk.gray('This project may not use the auto-migration pattern.'));
          console.log(chalk.gray('Add a migrations/ directory with SQL files to enable auto-migrations.'));
          return;
        }

        console.log(chalk.bold(`Migrations for ${project.name}:`));
        console.log(chalk.gray(`${data.total_applied} applied\n`));

        for (const m of data.applied || []) {
          const date = m.applied_at ? new Date(m.applied_at).toLocaleString() : 'unknown';
          console.log(`  ${chalk.green('✓')} ${m.name}  ${chalk.gray(date)}`);
        }
      }),
    );

  db.command('validate <idOrName> <sqlFile>')
    .description('Validate a migration SQL file against the current schema')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, sqlFile: unknown, opts: { json?: boolean }) => {
        const fs = await import('fs');
        const filePath = String(sqlFile);

        if (!fs.existsSync(filePath)) {
          console.error(chalk.red(`File not found: ${filePath}`));
          process.exit(1);
        }

        const sql = fs.readFileSync(filePath, 'utf8');
        const project = await resolveProject(String(idOrName));
        const spinner = ora('Validating migration...').start();
        const data = await api<any>(`/projects/${project.id}/database/validate`, {
          method: 'POST',
          body: { sql },
        });
        spinner.stop();

        handleJson(opts.json, data);

        console.log(chalk.bold(data.summary));
        console.log();

        for (const stmt of data.statements || []) {
          const icon = !stmt.valid ? chalk.red('✗') : stmt.warning ? chalk.yellow('⚠') : chalk.green('✓');
          console.log(`  ${icon} ${chalk.gray(stmt.sql)}`);
          if (stmt.error) console.log(`    ${chalk.red(stmt.error)}`);
          if (stmt.warning) console.log(`    ${chalk.yellow(stmt.warning)}`);
          if (stmt.info) console.log(`    ${chalk.gray(stmt.info)}`);
        }
      }),
    );

  db.command('connect <idOrName>')
    .alias('tunnel')
    .description('Open a local DB tunnel (MySQL or PostgreSQL) for TablePlus, DBeaver, psql, mysql, etc.')
    .option('--local-port <port>', 'Local port to bind (default 13306 for mysql, 15432 for postgres)')
    .option('--print-only', 'Print connection details without opening a tunnel (legacy behavior)')
    .action(
      withErrorHandler(async (idOrName: unknown, opts: { localPort?: string; printOnly?: boolean }) => {
        const project = await resolveProject(String(idOrName));

        // Legacy "show me the env values" behavior, kept behind a flag for
        // anyone who just wants to see what their app's $DB_HOST etc. are.
        if (opts.printOnly) {
          const data = await api<any>(`/projects/${project.id}/database`);
          if (!data.database) {
            console.log(chalk.yellow('This project does not have a database.'));
            return;
          }
          console.log(chalk.bold('Database (in-pod) connection details:'));
          console.log(`  ${chalk.blue('Engine:')}    ${data.type || data.engine || 'mysql'}`);
          console.log(`  ${chalk.blue('Host:')}      ${data.host || 'auto-injected'}`);
          console.log(`  ${chalk.blue('Port:')}      ${data.port || (data.type === 'postgres' ? 5432 : 3306)}`);
          console.log(`  ${chalk.blue('Database:')}  ${data.database}`);
          console.log(`  ${chalk.blue('User:')}      ${data.username || data.user || 'see env vars'}`);
          console.log(chalk.gray('\nNote: these creds are only reachable from inside your app pod.'));
          console.log(chalk.gray('Run `dailey db connect <project>` (without --print-only) to open a'));
          console.log(chalk.gray('local tunnel you can point TablePlus / DBeaver / psql at.'));
          return;
        }

        // ── Open a real tunnel ──
        const spinner = ora('Issuing tunnel session...').start();
        let session: any;
        try {
          session = await api<any>(`/projects/${project.id}/database/tunnel`, { method: 'POST' });
        } catch (err: any) {
          spinner.fail(chalk.red(`Could not issue tunnel: ${err.message || err}`));
          process.exit(1);
        }
        spinner.stop();

        const engine: 'mysql' | 'postgres' = session.engine === 'postgres' ? 'postgres' : 'mysql';
        const defaultPort = engine === 'postgres' ? 15432 : 13306;
        const localPort = opts.localPort ? parseInt(opts.localPort, 10) : defaultPort;
        if (!Number.isFinite(localPort) || localPort < 1 || localPort > 65535) {
          console.error(chalk.red(`Invalid --local-port: ${opts.localPort}`));
          process.exit(1);
        }

        // Build the WS URL from the origin + ws_path the API returned.
        //
        // getApiBaseUrl() is e.g. "https://os.dailey.cloud/api" — we want
        // the ORIGIN only, because `session.ws_path` already starts with
        // `/api/projects/...`. Naively concatenating would produce
        // `wss://host/api/api/projects/.../connect` and hit 502.
        const apiUrl = new URL(getApiBaseUrl());
        const wsProto = apiUrl.protocol === 'https:' ? 'wss:' : 'ws:';
        // Token travels in the X-Dailey-Tunnel-Token header (not URL) so it
        // doesn't appear in any proxy access log.
        const wsUrl = `${wsProto}//${apiUrl.host}${session.ws_path}`;
        const jwt = getCurrentToken();
        if (!jwt) {
          console.error(chalk.red('No auth token in memory — re-run `dailey login`.'));
          process.exit(1);
        }

        // Per-connection WebSocket: each GUI connection from the local TCP
        // server gets its own WS to customer-api, which opens its own TCP
        // socket to the database. One local connection ≈ one DB connection
        // from the per-session user's pool.
        let activeConnections = 0;
        const localServer = net.createServer((tcpSocket) => {
          activeConnections++;
          const ws = new WebSocket(wsUrl, {
            headers: {
              Authorization: `Bearer ${jwt}`,
              'X-Dailey-Tunnel-Token': session.token,
              'X-Dailey-Source': 'cli',
            },
          });
          let closed = false;

          const cleanup = () => {
            if (closed) return;
            closed = true;
            try { tcpSocket.destroy(); } catch {}
            try { ws.close(); } catch {}
            activeConnections--;
          };

          ws.on('open', () => {
            // From now on, pipe bytes both ways. The server's MySQL handshake
            // greeting flows db → ws.message → tcpSocket.write. The GUI's
            // login response flows tcpSocket.data → ws.send → db.
            tcpSocket.on('data', (chunk) => {
              if (ws.readyState === ws.OPEN) ws.send(chunk);
            });
            ws.on('message', (data) => {
              const buf = Buffer.isBuffer(data) ? data : Buffer.from(data as ArrayBuffer);
              try { tcpSocket.write(buf); } catch { cleanup(); }
            });
          });
          ws.on('close', cleanup);
          // Non-101 HTTP response to the WS upgrade — this is what the user
          // saw as "Unexpected server response: 502" in the earlier run. The
          // bare error doesn't include the body, which is useless for
          // debugging. Capture both so the user can tell e.g. a Cloudflare
          // 502 page from an HAProxy 502 from a customer-api 401.
          ws.on('unexpected-response', (_req: any, res: any) => {
            const chunks: Buffer[] = [];
            res.on('data', (c: Buffer) => chunks.push(c));
            res.on('end', () => {
              const body = Buffer.concat(chunks).toString('utf8').slice(0, 200).replace(/\s+/g, ' ').trim();
              const cfRay = res.headers?.['cf-ray'] ? ` cf-ray=${res.headers['cf-ray']}` : '';
              const server = res.headers?.server ? ` server=${res.headers.server}` : '';
              console.error(chalk.yellow(
                `(tunnel ws upgrade failed: ${res.statusCode} ${res.statusMessage || ''}${server}${cfRay}${body ? ` — ${body}` : ''})`
              ));
              cleanup();
            });
          });
          ws.on('error', (err: any) => {
            // Skip noisy "unexpected server response" errors here — the
            // `unexpected-response` handler above already prints a richer
            // message for those. Only log genuine transport errors.
            const msg = String(err?.message || err);
            if (!msg.includes('Unexpected server response')) {
              console.error(chalk.yellow(`(tunnel ws error: ${msg})`));
            }
            cleanup();
          });
          tcpSocket.on('close', cleanup);
          tcpSocket.on('error', cleanup);
        });

        await new Promise<void>((resolve, reject) => {
          localServer.once('error', reject);
          localServer.listen(localPort, '127.0.0.1', () => resolve());
        });

        const expiresAt = new Date(session.expires_at);
        const ttlMin = Math.max(1, Math.round((expiresAt.getTime() - Date.now()) / 60000));
        const engineLabel = engine === 'postgres' ? 'PostgreSQL' : 'MySQL';
        const connScheme = engine === 'postgres' ? 'postgresql' : 'mysql';

        console.log(chalk.bold.green(`✓ ${engineLabel} tunnel ready for ${project.name}\n`));
        console.log(`  ${chalk.blue('Host:')}      127.0.0.1`);
        console.log(`  ${chalk.blue('Port:')}      ${localPort}`);
        console.log(`  ${chalk.blue('Database:')}  ${session.database}`);
        console.log(`  ${chalk.blue('User:')}      ${session.username}`);
        console.log(`  ${chalk.blue('Password:')}  ${session.password}`);
        console.log();
        console.log(chalk.gray(`  Connection string:`));
        console.log(`  ${chalk.cyan(`${connScheme}://${session.username}:${session.password}@127.0.0.1:${localPort}/${session.database}`)}`);
        console.log();
        if (engine === 'postgres') {
          console.log(chalk.gray(`  psql: PGPASSWORD='${session.password}' psql -h 127.0.0.1 -p ${localPort} -U ${session.username} -d ${session.database}`));
          console.log();
          console.log(chalk.gray(`  To import a dump (in a second terminal while this tunnel is open):`));
          console.log(chalk.gray(`    SQL file:   PGPASSWORD='${session.password}' psql -h 127.0.0.1 -p ${localPort} -U ${session.username} -d ${session.database} < your_dump.sql`));
          console.log(chalk.gray(`    Custom fmt: pg_restore -h 127.0.0.1 -p ${localPort} -U ${session.username} -d ${session.database} your_dump.pgdump`));
          console.log();
        } else {
          console.log(chalk.gray(`  To import a dump (in a second terminal while this tunnel is open):`));
          console.log(chalk.gray(`    mysql -h 127.0.0.1 -P ${localPort} -u ${session.username} -p'${session.password}' ${session.database} < your_dump.sql`));
          console.log();
        }
        console.log(chalk.gray(`  Session pinned to your source IP (${session.source_ip}).`));
        console.log(chalk.gray(`  Expires in ~${ttlMin} minutes (at ${expiresAt.toLocaleTimeString()}).`));
        console.log(chalk.gray(`  Press Ctrl+C to disconnect and drop the per-session DB user.`));
        console.log();

        // Cleanup on Ctrl+C: tell customer-api to drop the MySQL user
        // immediately rather than waiting for the reaper to find it.
        const shutdown = async () => {
          console.log(chalk.gray('\nClosing tunnel...'));
          try {
            await api(`/projects/${project.id}/database/tunnel/${session.session_id}`, { method: 'DELETE' });
          } catch {}
          try { localServer.close(); } catch {}
          process.exit(0);
        };
        process.on('SIGINT', shutdown);
        process.on('SIGTERM', shutdown);
      }),
    );

  // ─── Write path (EOS #48) ────────────────────────────────────────────────

  function readSqlOrFile(arg: string): string {
    if (arg.startsWith('@')) return readFileSync(arg.slice(1), 'utf8');
    return arg;
  }

  function printExecSummary(label: string, data: any): void {
    console.log(chalk.bold.green(`✓ ${label}`));
    console.log(`  ${chalk.blue('Engine:')}    ${data.engine}`);
    console.log(`  ${chalk.blue('Database:')}  ${data.database}`);
    if (typeof data.statement_count === 'number') {
      console.log(`  ${chalk.blue('Statements:')}  ${data.statement_count}`);
    }
    if (Array.isArray(data.destructive) && data.destructive.length > 0) {
      console.log(`  ${chalk.yellow('Destructive:')} ${data.destructive.join(', ')}`);
    }
    if (Array.isArray(data.results)) {
      for (let i = 0; i < data.results.length; i++) {
        const r = data.results[i];
        const detail = r.rows !== undefined ? `${r.rows} rows` : `${r.affected ?? 0} affected`;
        console.log(`  ${chalk.gray(`[${i + 1}]`)} ${detail}`);
      }
    }
  }

  db.command('exec <idOrName> <sqlOrAtFile>')
    .description('Execute SQL against the project database (one-shot). Use @path/to/file.sql to load from disk.')
    .option('--confirm', 'Required when SQL contains destructive statements (DROP, TRUNCATE, DELETE without WHERE, etc.)')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, sqlOrAtFile: unknown, opts: { confirm?: boolean; json?: boolean }) => {
        const project = await resolveProject(String(idOrName));
        const sql = readSqlOrFile(String(sqlOrAtFile));
        const spinner = ora('Executing SQL...').start();
        let data: any;
        try {
          data = await api<any>(`/projects/${project.id}/database/exec`, {
            method: 'POST',
            body: { sql, confirm: Boolean(opts.confirm) },
          });
        } catch (err: any) {
          spinner.fail(chalk.red(err.message || 'exec failed'));
          const body = (err as any).body;
          if (body?.forbidden?.length) {
            console.error(chalk.red('Forbidden statements detected:'));
            for (const f of body.forbidden) console.error(`  • ${f}`);
          }
          if (body?.destructive?.length) {
            console.error(chalk.yellow('Destructive statements detected:'));
            for (const d of body.destructive) console.error(`  • ${d}`);
            console.error(chalk.gray('Re-run with --confirm to apply.'));
          }
          process.exit(1);
        }
        spinner.stop();
        handleJson(opts.json, data);
        printExecSummary(`Committed (${project.name})`, data);
      }),
    );

  db.command('plan <idOrName> <sqlFile>')
    .description('Dry-run SQL and return a plan ID. Use `dailey db apply` to execute.')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, sqlFile: unknown, opts: { json?: boolean }) => {
        const project = await resolveProject(String(idOrName));
        const path = String(sqlFile);
        const sql = readFileSync(path, 'utf8');
        const spinner = ora('Planning SQL...').start();
        let data: any;
        try {
          data = await api<any>(`/projects/${project.id}/database/exec/plan`, {
            method: 'POST',
            body: { sql },
          });
        } catch (err: any) {
          spinner.fail(chalk.red(err.message || 'plan failed'));
          const body = (err as any).body;
          if (body?.forbidden?.length) {
            console.error(chalk.red('Forbidden statements detected:'));
            for (const f of body.forbidden) console.error(`  • ${f}`);
          }
          process.exit(1);
        }
        spinner.stop();
        handleJson(opts.json, data);
        console.log(chalk.bold.green(`✓ Plan ready for ${project.name}`));
        console.log(`  ${chalk.blue('Engine:')}    ${data.engine}`);
        console.log(`  ${chalk.blue('Database:')}  ${data.database}`);
        console.log(`  ${chalk.blue('Statements:')}  ${data.statement_count}`);
        if (data.destructive?.length) {
          console.log(`  ${chalk.yellow('Destructive:')} ${data.destructive.join(', ')}`);
        }
        const ttlMin = Math.max(1, Math.round((data.expires_at - Math.floor(Date.now() / 1000)) / 60));
        console.log(`  ${chalk.blue('Expires in:')}  ~${ttlMin} min`);
        console.log();
        console.log(chalk.gray('Apply with:'));
        console.log(`  dailey db apply ${project.slug} ${chalk.cyan(data.plan_id)} ${path}`);
      }),
    );

  db.command('apply <idOrName> <planId> <sqlFile>')
    .description('Apply a plan created with `dailey db plan` against the project database.')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(async (idOrName: unknown, planId: unknown, sqlFile: unknown, opts: { json?: boolean }) => {
        const project = await resolveProject(String(idOrName));
        const sql = readFileSync(String(sqlFile), 'utf8');
        const spinner = ora('Applying plan...').start();
        let data: any;
        try {
          data = await api<any>(`/projects/${project.id}/database/exec/apply`, {
            method: 'POST',
            body: { plan_id: String(planId), sql },
          });
        } catch (err: any) {
          spinner.fail(chalk.red(err.message || 'apply failed'));
          process.exit(1);
        }
        spinner.stop();
        handleJson(opts.json, data);
        printExecSummary(`Committed (${project.name})`, data);
      }),
    );

  // ─── Managed migration (source DB → managed DB) ──────────────────────────
  db.command('migrate <idOrName>')
    .description('Migrate an external database into the project managed DB (same engine). Two-phase: estimate → confirm.')
    .option('--source-url <url>', 'Full source connection URL (engine://user@host:port/db). Password may be embedded but a hidden prompt / env is preferred.')
    .option('--source-host <host>', 'Source DB host (when not using --source-url)')
    .option('--source-port <port>', 'Source DB port')
    .option('--source-db <name>', 'Source database name')
    .option('--source-user <user>', 'Source DB user')
    .option('--allow-non-empty', 'Allow migrating into a target DB that already has data')
    .option('--row-ceiling <n>', 'Refuse the migration if the source exceeds this row count')
    .option('--yes', 'Skip the interactive confirm prompt and proceed once estimated')
    .option('--confirm <id>', 'Confirm a prior estimate by migration id (skips submit)')
    .option('--watch', 'Poll the migration to a terminal state, showing progress')
    .option('--json', 'Output as JSON')
    .action(
      withErrorHandler(
        async (
          idOrName: unknown,
          opts: {
            sourceUrl?: string;
            sourceHost?: string;
            sourcePort?: string;
            sourceDb?: string;
            sourceUser?: string;
            allowNonEmpty?: boolean;
            rowCeiling?: string;
            yes?: boolean;
            confirm?: string;
            watch?: boolean;
            json?: boolean;
          },
        ) => {
          const project = await resolveProject(String(idOrName));

          const pollToTerminal = async (migId: string): Promise<any> => {
            const spinner = ora('Migrating...').start();
            let last: any;
            // eslint-disable-next-line no-constant-condition
            while (true) {
              const { migration } = await api<{ migration: any }>(
                `/projects/${project.id}/migrations/${migId}`,
              );
              last = migration;
              spinner.text = migrationProgressLabel(migration);
              if (MIGRATION_TERMINAL.has(migration.status)) break;
              await sleep(2000);
            }
            spinner.stop();
            return last;
          };

          // Exit code for a terminal migration status. `completed` → 0;
          // `failed`/`canceled` (and any unexpected terminal status) → 1.
          const terminalExitCode = (m: any): number => (m.status === 'completed' ? 0 : 1);

          // Render a terminal migration and exit with the correct code. This
          // must own the exit in BOTH modes: in --json mode we print the JSON
          // here (rather than via handleJson, which always exits 0) so that a
          // `failed`/`canceled` migration still exits non-zero.
          const finishTerminal = (m: any, json: boolean | undefined): never => {
            if (json) {
              console.log(JSON.stringify(m, null, 2));
              process.exit(terminalExitCode(m));
            }
            if (m.status === 'completed') {
              console.log(chalk.green.bold('✓ Migration completed'));
              if (typeof m.rows_copied === 'number') {
                console.log(`  ${chalk.blue('Rows copied:')}  ${m.rows_copied}`);
              }
              if (m.target_db) console.log(`  ${chalk.blue('Target DB:')}    ${m.target_db}`);
              if (m.message) console.log(`  ${chalk.gray(m.message)}`);
            } else if (m.status === 'canceled') {
              console.log(chalk.yellow('Migration canceled.'));
            } else {
              console.error(chalk.red.bold('✗ Migration failed'));
              if (m.error) console.error(`  ${chalk.red(m.error)}`);
              else if (m.message) console.error(`  ${chalk.red(m.message)}`);
            }
            process.exit(terminalExitCode(m));
          };

          // Confirm a migration, translating a 409 (not in a confirmable
          // state) into a friendly message instead of a raw generic error.
          const confirmMigration = async (
            migId: string,
          ): Promise<{ id: string; status: string }> => {
            try {
              return await api<{ id: string; status: string }>(
                `/projects/${project.id}/migrations/${migId}/confirm`,
                { method: 'POST' },
              );
            } catch (err) {
              if (err instanceof ApiError && err.status === 409) {
                // Surface the server-reported status when present.
                const m = /status:\s*([a-z_]+)/i.exec(err.message);
                const status = m ? m[1] : 'unknown';
                throw new ApiError(
                  `migration ${migId} is not in a confirmable state (status: ${status})`,
                  409,
                );
              }
              throw err;
            }
          };

          // ── Resume path: confirm a prior estimate by id ──
          if (opts.confirm) {
            const migId = opts.confirm;
            const spinner = ora('Confirming migration...').start();
            let confirmed: { id: string; status: string };
            try {
              confirmed = await confirmMigration(migId);
            } finally {
              spinner.stop();
            }
            if (opts.json && !opts.watch) {
              handleJson(true, confirmed);
            }
            console.log(chalk.green(`Migration ${confirmed.id} confirmed (status: ${confirmed.status}).`));
            if (opts.watch) {
              const terminal = await pollToTerminal(migId);
              finishTerminal(terminal, opts.json);
            } else {
              console.log(chalk.gray('Run with --watch to follow progress, or `dailey db migrations` to check status.'));
            }
            return;
          }

          // ── Submit path: build source creds (never the password via flag) ──
          let source: ParsedSource;
          if (opts.sourceUrl) {
            source = parseSourceUrl(opts.sourceUrl);
          } else {
            if (!opts.sourceHost || !opts.sourceDb || !opts.sourceUser) {
              console.error(
                chalk.red(
                  'Error: provide --source-url, or all of --source-host, --source-db, and --source-user.',
                ),
              );
              process.exit(1);
            }
            source = {
              host: opts.sourceHost,
              port: opts.sourcePort ? Number(opts.sourcePort) : undefined,
              database: opts.sourceDb,
              user: opts.sourceUser,
            };
          }

          // Password resolution order: env var, embedded URL password, hidden
          // prompt. NEVER a --password flag (shell-history leak).
          let password = process.env.DAILEY_MIGRATE_SOURCE_PASSWORD || source.password;
          if (!password) {
            if (!process.stdin.isTTY) {
              console.error(
                chalk.red(
                  'Error: no source DB password. Set DAILEY_MIGRATE_SOURCE_PASSWORD or run interactively for a hidden prompt.',
                ),
              );
              process.exit(1);
            }
            password = await promptLine('Source DB password: ', true);
          }
          if (!password) {
            console.error(chalk.red('Error: source DB password is required.'));
            process.exit(1);
          }

          const submitSpinner = ora('Submitting migration & estimating...').start();
          const created = await api<{ id: string; status: string }>(
            `/projects/${project.id}/migrations`,
            {
              method: 'POST',
              body: {
                host: source.host,
                port: source.port,
                database: source.database,
                user: source.user,
                password,
                allow_non_empty: opts.allowNonEmpty || undefined,
                row_ceiling: opts.rowCeiling ? Number(opts.rowCeiling) : undefined,
              },
            },
          );
          // Drop the password from memory reference; do not log it anywhere.
          password = '';
          const migId = created.id;

          // Poll until the estimate is ready (awaiting_confirm) or it errors.
          let estimate: any;
          // eslint-disable-next-line no-constant-condition
          while (true) {
            const { migration } = await api<{ migration: any }>(
              `/projects/${project.id}/migrations/${migId}`,
            );
            estimate = migration;
            if (migration.status === 'awaiting_confirm') break;
            if (MIGRATION_TERMINAL.has(migration.status)) break;
            submitSpinner.text = migrationProgressLabel(migration);
            await sleep(2000);
          }
          submitSpinner.stop();

          if (estimate.status !== 'awaiting_confirm') {
            // Estimation failed/canceled before we could confirm.
            finishTerminal(estimate, opts.json);
          }

          handleJson(opts.json, estimate);

          console.log(chalk.bold(`Estimate for ${project.name}:`));
          console.log(`  ${chalk.blue('Engine:')}       ${estimate.engine}`);
          if (estimate.target_db) console.log(`  ${chalk.blue('Target DB:')}    ${estimate.target_db}`);
          console.log(`  ${chalk.blue('Rows total:')}   ${estimate.rows_total ?? 'unknown'}`);
          if (estimate.message) console.log(`  ${chalk.gray(estimate.message)}`);
          console.log();

          // Decide whether to confirm now.
          let proceed = !!opts.yes;
          if (!proceed) {
            if (process.stdin.isTTY) {
              const answer = (await promptLine('Confirm migration? [y/N] ')).trim().toLowerCase();
              proceed = answer === 'y' || answer === 'yes';
            } else {
              proceed = false;
            }
          }

          if (!proceed) {
            console.log(chalk.yellow('Not confirmed.'));
            console.log(`Migration id: ${chalk.cyan(migId)}`);
            console.log('Resume later with:');
            console.log(`  dailey db migrate ${String(idOrName)} --confirm ${migId}${opts.watch ? ' --watch' : ''}`);
            return;
          }

          const confirmSpinner = ora('Confirming migration...').start();
          let confirmed: { id: string; status: string };
          try {
            confirmed = await confirmMigration(migId);
          } finally {
            confirmSpinner.stop();
          }
          console.log(chalk.green(`Migration ${confirmed.id} confirmed (status: ${confirmed.status}).`));

          if (opts.watch) {
            const terminal = await pollToTerminal(migId);
            finishTerminal(terminal, opts.json);
          } else {
            console.log(chalk.gray('Run with --watch to follow progress, or `dailey db migrations` to check status.'));
          }
        },
      ),
    );
}