import type { RuntimeLogger } from "../../../core/types.ts";
import type { DialectKind } from "./dialect/dialect-adapter.ts";

import { readFileSync, readdirSync } from "node:fs";

/**
 * Minimal DB surface used by the runtime migration runner.
 * Methods are async so PostgreSQL / MySQL pool adapters and the SQLite wrapper share one path.
 */
export interface MigrationDb {
  exec(sql: string): Promise<void>;
  prepare(sql: string): MigrationStatement;
}

export interface MigrationStatement {
  run(...params: unknown[]): Promise<{ changes: number }>;
  all(...params: unknown[]): Promise<unknown[]>;
  get(...params: unknown[]): Promise<unknown>;
}

export interface MigrationRunnerOptions {
  db: MigrationDb;
  migrationDirectory: URL;
  logger?: RuntimeLogger;
  /** Controls transaction begin syntax. Defaults to sqlite (BEGIN IMMEDIATE). */
  dialect?: DialectKind;
}

const BEGIN_SQL: Record<DialectKind, string> = {
  sqlite: "begin immediate",
  postgres: "begin",
  mysql: "start transaction",
};

/** MySQL cannot use bare TEXT as a primary key; keep PG/SQLite on text for parity with value columns. */
const RUNTIME_MIGRATIONS_DDL: Record<DialectKind, string> = {
  sqlite: `
    create table if not exists runtime_migrations (
      name text primary key,
      applied_at text not null
    );
  `,
  postgres: `
    create table if not exists runtime_migrations (
      name text primary key,
      applied_at text not null
    );
  `,
  mysql: `
    create table if not exists runtime_migrations (
      name varchar(255) primary key,
      applied_at varchar(64) not null
    );
  `,
};

/**
 * Apply ordered `NNNN_*.sql` files under `migrationDirectory`, recording each in
 * `runtime_migrations`. Generated Drizzle files may contain `--> statement-breakpoint`
 * markers; statements are split and executed one by one.
 */
export async function runRuntimeMigrations({
  db,
  migrationDirectory,
  logger,
  dialect = "sqlite",
}: MigrationRunnerOptions): Promise<void> {
  await db.exec(RUNTIME_MIGRATIONS_DDL[dialect]);

  const applied = new Set(
    (await db.prepare("select name from runtime_migrations").all()).map((row) => readString(row, "name")),
  );

  const migrationFiles = readdirSync(migrationDirectory)
    .filter((name) => /^\d+_.*\.sql$/.test(name))
    .sort();

  let newlyAppliedCount = 0;
  const startedAt = Date.now();

  for (const file of migrationFiles) {
    if (applied.has(file)) {
      continue;
    }

    const migrationStartedAt = Date.now();
    logger?.info({ migration: file }, "runtime migration started");
    try {
      const sql = readFileSync(new URL(file, migrationDirectory), "utf8");
      await runInMigrationTransaction(db, dialect, async () => {
        for (const statement of splitMigrationStatements(sql)) {
          await db.exec(statement);
        }
        await db
          .prepare("insert into runtime_migrations (name, applied_at) values (?, ?)")
          .run(file, new Date().toISOString());
      });
      logger?.info({ migration: file, durationMs: Date.now() - migrationStartedAt }, "runtime migration completed");
      newlyAppliedCount += 1;
    } catch (error) {
      logger?.error(
        { migration: file, durationMs: Date.now() - migrationStartedAt, err: error },
        "runtime migration failed",
      );
      throw error;
    }
  }

  logger?.info(
    {
      migrationCount: migrationFiles.length,
      appliedCount: migrationFiles.filter((file) => applied.has(file)).length + newlyAppliedCount,
      newlyAppliedCount,
      durationMs: Date.now() - startedAt,
    },
    "runtime migrations ready",
  );
}

export function splitMigrationStatements(sql: string): string[] {
  return sql
    .split(/-->\s*statement-breakpoint\s*/g)
    .map((statement) => statement.trim())
    .filter((statement) => statement.length > 0);
}

async function runInMigrationTransaction<T>(db: MigrationDb, dialect: DialectKind, work: () => Promise<T>): Promise<T> {
  await db.exec(BEGIN_SQL[dialect]);
  try {
    const result = await work();
    await db.exec("commit");
    return result;
  } catch (error) {
    try {
      await db.exec("rollback");
    } catch {
      // Keep the original error if rollback also fails.
    }
    throw error;
  }
}

function readString(row: unknown, key: string): string {
  if (typeof row !== "object" || row == null) {
    throw new Error(`Expected migration row for ${key}.`);
  }

  const value = (row as Record<string, unknown>)[key];
  if (typeof value !== "string") {
    throw new Error(`Expected migration column ${key} to be a string.`);
  }

  return value;
}