import type { Database } from 'bun:sqlite';
import { createHash } from 'node:crypto';
import migration0000Sql from '../../drizzle/0000_natural_the_hood.sql' with { type: 'text' };
import migration0001Sql from '../../drizzle/0001_tranquil_polaris.sql' with { type: 'text' };
import migration0002Sql from '../../drizzle/0002_lonely_shinko_yamashiro.sql' with { type: 'text' };
import migration0003Sql from '../../drizzle/0003_hard_randall.sql' with { type: 'text' };
import migration0004Sql from '../../drizzle/0004_elite_the_spike.sql' with { type: 'text' };
import migration0005Sql from '../../drizzle/0005_lean_magneto.sql' with { type: 'text' };
import journal from '../../drizzle/meta/_journal.json';

/**
 * The migration source bundled into every executable.
 *
 * SQL remains authoritative in `drizzle/` and Bun's text loader embeds those
 * exact bytes into compiled executables. When Drizzle generates a migration,
 * add its text import to `SQL_BY_TAG`; module initialization and the parity
 * test both fail if a journal entry has no embedded SQL.
 */
export interface MigrationAsset {
  readonly tag: string;
  readonly when: number;
  readonly breakpoints: boolean;
  readonly sql: string;
}

const SQL_BY_TAG: Readonly<Record<string, string>> = {
  '0000_natural_the_hood': migration0000Sql,
  '0001_tranquil_polaris': migration0001Sql,
  '0002_lonely_shinko_yamashiro': migration0002Sql,
  '0003_hard_randall': migration0003Sql,
  '0004_elite_the_spike': migration0004Sql,
  '0005_lean_magneto': migration0005Sql,
};

/** The first migration whose indexes depend on the legacy-row repair phase. */
export const ARTICLE_IDENTITY_CONSTRAINTS_MIGRATION_TAG = '0003_hard_randall';

export const MIGRATION_ASSETS: readonly MigrationAsset[] = journal.entries.map((entry) => {
  const sql = SQL_BY_TAG[entry.tag];
  if (sql === undefined) {
    throw new Error(`Migration ${entry.tag} is present in the journal but is not embedded`);
  }
  return {
    tag: entry.tag,
    when: entry.when,
    breakpoints: entry.breakpoints,
    sql,
  };
});

/** All migrations that must complete before article identity repair is safe. */
export function migrationsBeforeArticleIdentityConstraints(
  assets: readonly MigrationAsset[] = MIGRATION_ASSETS,
): readonly MigrationAsset[] {
  const constraintIndex = assets.findIndex((asset) => asset.tag === ARTICLE_IDENTITY_CONSTRAINTS_MIGRATION_TAG);
  if (constraintIndex === -1) {
    throw new Error(`Embedded migrations do not contain ${ARTICLE_IDENTITY_CONSTRAINTS_MIGRATION_TAG}`);
  }
  return assets.slice(0, constraintIndex);
}

/**
 * Apply embedded Drizzle Kit assets without constructing a Drizzle Bun session.
 *
 * This intentionally mirrors `SQLiteSyncDialect.migrate`: it records the same
 * hashes/timestamps and executes every migration newer than the latest journal
 * row. It joins a caller transaction when one exists, otherwise it owns one
 * deferred transaction. Raw Bun SQLite calls finalize deterministically;
 * Drizzle's one-time prepared statements are GC-owned and can keep the dedicated
 * startup descriptor alive after its session has returned.
 */
export function migrateEmbedded(sqliteDb: Database, assets: readonly MigrationAsset[] = MIGRATION_ASSETS): void {
  sqliteDb.run(`
    CREATE TABLE IF NOT EXISTS __drizzle_migrations (
      id SERIAL PRIMARY KEY,
      hash text NOT NULL,
      created_at numeric
    )
  `);

  const latestStatement = sqliteDb.prepare(
    'SELECT created_at FROM __drizzle_migrations ORDER BY created_at DESC LIMIT 1',
  );
  let latest: { created_at: number | string | null } | null;
  try {
    latest = latestStatement.get() as { created_at: number | string | null } | null;
  } finally {
    latestStatement.finalize();
  }

  const ownsTransaction = !sqliteDb.inTransaction;
  if (ownsTransaction) sqliteDb.run('BEGIN');
  try {
    for (const asset of assets) {
      if (latest && Number(latest.created_at) >= asset.when) continue;
      for (const statement of asset.sql.split('--> statement-breakpoint')) {
        sqliteDb.run(statement);
      }
      sqliteDb.run('INSERT INTO __drizzle_migrations (hash, created_at) VALUES (?, ?)', [
        createHash('sha256').update(asset.sql).digest('hex'),
        asset.when,
      ]);
    }
    if (ownsTransaction) sqliteDb.run('COMMIT');
  } catch (error) {
    if (!ownsTransaction) throw error;
    try {
      sqliteDb.run('ROLLBACK');
    } catch (rollbackError) {
      throw new AggregateError([error, rollbackError], 'Embedded migration failed and could not be rolled back');
    }
    throw error;
  }
}