cli.test.ts

import { describe, it, before, after } from 'node:test';
import assert from 'node:assert';
import { execSync } from 'node:child_process';
import { DatabaseSync } from 'node:sqlite';
import { existsSync, unlinkSync, readFileSync, mkdirSync } from 'node:fs';
import { resolve } from 'node:path';

describe('sqlite2pg CLI End-to-End Tests', () => {
  const fixtureDir = resolve(process.cwd(), 'tests', 'fixtures');
  const testDbPath = resolve(fixtureDir, 'sample_app.db');
  const outputSqlPath = resolve(fixtureDir, 'output_migration.sql');

  before(() => {
    if (!existsSync(fixtureDir)) {
      mkdirSync(fixtureDir, { recursive: true });
    }
    if (existsSync(testDbPath)) {
      unlinkSync(testDbPath);
    }
    if (existsSync(outputSqlPath)) {
      unlinkSync(outputSqlPath);
    }

    // Create realistic sample database
    const db = new DatabaseSync(testDbPath);
    db.exec(`
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        is_pro BOOLEAN DEFAULT 0,
        settings JSON,
        avatar BLOB,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      );

      CREATE TABLE posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        author_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        slug TEXT UNIQUE,
        content TEXT,
        published_at TIMESTAMP,
        FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
      );

      CREATE INDEX idx_posts_slug ON posts(slug);
    `);

    const insertUser = db.prepare(`
      INSERT INTO users (name, email, is_pro, settings, avatar, created_at)
      VALUES (?, ?, ?, ?, ?, ?);
    `);
    insertUser.run(
      'Satoshi Nakamoto',
      'satoshi@gmx.com',
      1,
      JSON.stringify({ dark_mode: true, language: 'en' }),
      Buffer.from([0x01, 0x02, 0x03]),
      '2024-01-01 00:00:00'
    );
    insertUser.run(
      'Hal Finney',
      'hal@finney.org',
      1,
      JSON.stringify({ dark_mode: false }),
      null,
      '2024-01-02 12:00:00'
    );

    const insertPost = db.prepare(`
      INSERT INTO posts (author_id, title, slug, content, published_at)
      VALUES (?, ?, ?, ?, ?);
    `);
    insertPost.run(
      1,
      'Running bitcoin',
      'running-bitcoin',
      'First transaction ever.',
      '2024-01-11 20:30:00'
    );

    db.close();
  });

  after(() => {
    try {
      if (existsSync(testDbPath)) unlinkSync(testDbPath);
      if (existsSync(outputSqlPath)) unlinkSync(outputSqlPath);
    } catch {
      // ignore
    }
  });

  it('should run CLI --dry-run cleanly', () => {
    const nodeFlag = process.versions.node.startsWith('22.') ? '--experimental-sqlite ' : '';
    const output = execSync(`node ${nodeFlag}bin/sqlite2pg.js "${testDbPath}" --dry-run`, {
      encoding: 'utf-8',
    });
    assert.ok(output.includes('sqlite2pg Schema Inspection'));
    assert.ok(output.includes('users'));
    assert.ok(output.includes('posts'));
    assert.ok(output.includes('Dry run completed'));
  });

  it('should run CLI and generate complete migration.sql for Supabase', () => {
    const nodeFlag = process.versions.node.startsWith('22.') ? '--experimental-sqlite ' : '';
    const output = execSync(
      `node ${nodeFlag}bin/sqlite2pg.js "${testDbPath}" -t supabase -o "${outputSqlPath}"`,
      { encoding: 'utf-8' }
    );
    assert.ok(output.includes('Migration SQL written to:'));
    assert.ok(existsSync(outputSqlPath));

    const sqlContent = readFileSync(outputSqlPath, 'utf-8');
    assert.ok(sqlContent.includes('Generated by sqlite2pg'));
    assert.ok(sqlContent.includes('CREATE TABLE IF NOT EXISTS "public"."users"'));
    assert.ok(sqlContent.includes('ENABLE ROW LEVEL SECURITY'));
    assert.ok(sqlContent.includes('Satoshi Nakamoto'));
    assert.ok(sqlContent.includes('Running bitcoin'));
    assert.ok(sqlContent.includes('PERFORM setval'));
  });
});