migrate.ts

import { sql, type TransactionSQL } from "bun";
const [, , ...flags] = Bun.argv;
import { readdir } from "node:fs/promises";

const MIGRATIONS_DIR = "./migrations";

const makeMigration = (name: string) => {
  const timestamp = new Date().toISOString().split(".")[0].replace(/\D/g, "");
  const migrationName = `${timestamp}_${name}`;
  const migrationPath = `${MIGRATIONS_DIR}/${migrationName}.ts`;
  const migrationContent = `import { type TransactionSQL } from "bun";

export async function up(sql: TransactionSQL): Promise<void> {

}

export async function down(sql: TransactionSQL): Promise<void> {

}
`;

  Bun.write(migrationPath, migrationContent);
};

const initMigrationsTable = async (): Promise<void> => {
  await sql`CREATE TABLE IF NOT EXISTS migrations (
    name VARCHAR(255) PRIMARY KEY NOT NULL,
    batch SERIAL NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )`;
};

const getMigrationFiles = async (): Promise<string[]> => {
  const migrationDirContents = await readdir(MIGRATIONS_DIR);
  const files = [];
  for (const file of migrationDirContents) {
    if (/^^\d{14}_.*\.ts$/.test(file)) {
      files.push(file);
    }
  }
  files.sort();
  return files;
};

export async function runLatestMigrations(): Promise<void> {
  // 1. Get all migration files from the filesystem
  // This function needs to be available in the scope.
  // It should return a sorted list of migration filenames.
  const allFiles = await getMigrationFiles();

  // 2. Get names of already run migrations from the DB
  // The 'sql' template literal tag is assumed to be imported from 'bun:sqlite'.
  const alreadyRun: { name: string; batch: number }[] =
    await sql`SELECT name, batch FROM migrations ORDER BY name ASC`;

  // 3. Determine pending migrations
  const pendingFiles = allFiles.filter(
    (fileName) => !alreadyRun.some((run) => run.name === fileName)
  );

  // 4. If no pending migrations, log and return
  if (pendingFiles.length === 0) {
    console.log("No new migrations to run. Database is up to date.");
    return;
  }

  console.log(`Found ${pendingFiles.length} new migration(s) to run:`);
  pendingFiles.forEach((file) => console.log(`  - ${file}`));

  // 5. Determine the next batch number
  // Query for the maximum existing batch number.
  const maxBatchResult: { max_batch: number | null }[] =
    await sql`SELECT MAX(batch) as max_batch FROM migrations`;

  let nextBatchNumber = 1;
  if (maxBatchResult.length > 0 && maxBatchResult[0].max_batch !== null) {
    nextBatchNumber = maxBatchResult[0].max_batch + 1;
  }
  console.log(`Assigning batch number ${nextBatchNumber} to these migrations.`);

  // 6. Run migrations within a single transaction
  try {
    await sql.begin(async (tx: TransactionSQL) => {
      for (const fileName of pendingFiles) {
        console.log(`Running migration ${fileName}...`);
        try {
          // Dynamically import the migration file
          const migrationModule: {
            up: (sqlTx: TransactionSQL) => Promise<void>;
          } = await import(`${MIGRATIONS_DIR}/${fileName}`);

          if (typeof migrationModule.up !== "function") {
            throw new Error(
              `Migration file ${fileName} does not export an 'up' function.`
            );
          }

          // Execute the 'up' migration function, passing the transaction object
          await migrationModule.up(tx);

          // Insert record into migrations table with the new batch number
          await tx`INSERT INTO migrations (name, batch) VALUES (${fileName}, ${nextBatchNumber})`;
          console.log(`Successfully ran and recorded migration ${fileName}.`);
        } catch (e) {
          console.error(
            `Error processing migration ${fileName}:`,
            e instanceof Error ? e.message : String(e)
          );
          // To ensure the transaction rolls back, re-throw the error.
          // The sql.begin block will catch this and initiate a rollback.
          throw new Error(
            `Failed to run migration ${fileName}. Rolling back transaction. Original error: ${
              e instanceof Error ? e.message : String(e)
            }`
          );
        }
      }
    });
    console.log(
      `All ${pendingFiles.length} new migrations have been run and recorded successfully with batch number ${nextBatchNumber}.`
    );
  } catch (transactionError) {
    console.error(
      "Transaction failed. No migrations were applied from this batch:",
      transactionError instanceof Error
        ? transactionError.message
        : String(transactionError)
    );
    // The transaction has already been rolled back by sql.begin
  }
}

export async function rollbackMigrations(): Promise<void> {
  // 1. Find the latest batch number
  const latestBatchResult: { latest_batch: number | null }[] =
    await sql`SELECT MAX(batch) as latest_batch FROM migrations`;

  if (
    latestBatchResult.length === 0 ||
    latestBatchResult[0].latest_batch === null
  ) {
    console.log("No migrations found in the database to roll back.");
    return;
  }

  const latestBatchNumber = latestBatchResult[0].latest_batch;
  console.log(`Found latest batch number: ${latestBatchNumber}.`);

  // 2. Get all migration names from the latest batch, ordered for rollback
  // (reverse of their application filename, which is descending for timestamped names)
  const migrationsInBatch: { name: string }[] =
    await sql`SELECT name FROM migrations WHERE batch = ${latestBatchNumber} ORDER BY name DESC`;

  if (migrationsInBatch.length === 0) {
    console.log(
      `No migrations found in batch ${latestBatchNumber} to roll back (this might indicate they were already rolled back or an issue).`
    );
    return;
  }

  console.log(
    `Found ${migrationsInBatch.length} migration(s) in batch ${latestBatchNumber} to roll back:`
  );
  migrationsInBatch.forEach((m) => console.log(`  - ${m.name}`));

  // 3. Run 'down' migrations within a single transaction
  try {
    await sql.begin(async (tx: TransactionSQL) => {
      for (const migrationRecord of migrationsInBatch) {
        const fileName = migrationRecord.name; // e.g., "20231027123456_create_users.ts"
        console.log(`Rolling back migration ${fileName}...`);

        try {
          // Dynamically import the migration file.
          // The path should be relative to the project root or correctly resolvable by Bun.
          // If MIGRATIONS_DIR is "./migrations" and fileName is "timestamp_name.ts",
          // this will attempt to import "./migrations/timestamp_name.ts".
          const migrationModule: {
            down: (sqlTx: TransactionSQL) => Promise<void>;
          } = await import(`${MIGRATIONS_DIR}/${fileName}`);

          if (typeof migrationModule.down !== "function") {
            throw new Error(
              `Migration file ${fileName} does not export a 'down' function.`
            );
          }

          // Execute the 'down' migration function
          await migrationModule.down(tx);

          // Delete the record from the migrations table
          await tx`DELETE FROM migrations WHERE name = ${fileName} AND batch = ${latestBatchNumber}`;
          console.log(
            `Successfully rolled back and removed record for migration ${fileName}.`
          );
        } catch (e) {
          console.error(
            `Error rolling back migration ${fileName}:`,
            e instanceof Error ? e.message : String(e)
          );
          // Re-throw the error to ensure the transaction is rolled back
          throw new Error(
            `Failed to roll back migration ${fileName}. Rolling back transaction. Original error: ${
              e instanceof Error ? e.message : String(e)
            }`
          );
        }
      }
    });
    console.log(
      `Successfully rolled back all ${migrationsInBatch.length} migration(s) from batch ${latestBatchNumber}.`
    );
  } catch (transactionError) {
    console.error(
      "Transaction failed during rollback. The database state for this batch might be inconsistent if some 'down' migrations partially completed before the error that caused the rollback:",
      transactionError instanceof Error
        ? transactionError.message
        : String(transactionError)
    );
    // The transaction has already been rolled back by sql.begin
  }
}

const runNextMigration = async () => {
  const files = await getMigrationFiles();
  console.log(`Found ${files.length} migrations.`);

  const alreadyRun: { name: string }[] = await sql`SELECT name FROM migrations`;

  const remainingFiles = files.filter((fileName) => {
    return !alreadyRun.some((run) => run.name === fileName);
  });

  const fileName = remainingFiles[0];
  if (!fileName) {
    console.log("No migrations to run.");
    return;
  }

  const migration: {
    up: (sql: TransactionSQL) => Promise<void>;
  } = await import(`${MIGRATIONS_DIR}/${fileName}`);

  await sql.begin(async (tx) => {
    try {
      console.log(`Running migration ${fileName}`);
      await migration.up(tx);
    } catch (e) {
      console.error(`Error running migration ${fileName}:`);
      throw e;
    }
    await tx`INSERT INTO migrations (name) VALUES (${fileName})`;
  });

  for (const fileName of remainingFiles) {
  }
};

const runPreviousMigration = async () => {
  const [lastMigration]: { name: string }[] =
    await sql`SELECT name FROM migrations ORDER BY name DESC LIMIT 1`;

  if (!lastMigration) {
    console.log("No migrations to run.");
    return;
  }

  const migrations = await getMigrationFiles();
  const lastMigrationIndex = migrations.findIndex(
    (migration) => migration === lastMigration.name
  );
  const previousMigration = migrations[lastMigrationIndex];
  if (!previousMigration) {
    console.log("No migrations to run.");
    return;
  }

  const migration: {
    down: (sql: TransactionSQL) => Promise<void>;
  } = await import(`${MIGRATIONS_DIR}/${previousMigration}`);

  await sql.begin(async (tx) => {
    try {
      console.log(`Running migration ${previousMigration}`);
      await migration.down(tx);
    } catch (e) {
      console.error(`Error running migration ${previousMigration}:`);
      throw e;
    }

    await tx`DELETE FROM migrations WHERE name = ${previousMigration}`;
  });
};

if (flags[0] === "make") {
  const migrationName = flags[1];
  if (!migrationName) {
    console.error("Must provide a migration name");
    process.exit(1);
  }
  makeMigration(migrationName);
}

if (flags[0] === "up") {
  await initMigrationsTable();
  await runNextMigration();
}

if (flags[0] === "down") {
  await initMigrationsTable();
  await runPreviousMigration();
}

if (flags[0] === "latest") {
  await initMigrationsTable();
  await runLatestMigrations();
}

if (flags[0] === "rollback") {
  await initMigrationsTable();
  await rollbackMigrations();
}