import type { RuntimeLogger } from "../../../../core/types.ts";
import type { MigrationDb } from "../runtime-migration-runner.ts";
import type { DialectAdapter, IdempotencyInsertValues, RuntimeDialect } from "./dialect-adapter.ts";
import type { BetterSQLite3Database } from "drizzle-orm/better-sqlite3";

import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import { runRuntimeMigrations } from "../runtime-migration-runner.ts";
import * as schema from "../schema/sqlite.ts";
import { adoptLegacySqliteMigrations } from "../sqlite-legacy-adoption.ts";

export interface SqliteDialectOptions {
  logger?: RuntimeLogger;
}

export interface SqliteDialect extends RuntimeDialect {
  readonly kind: "sqlite";
  readonly db: BetterSQLite3Database<typeof schema>;
  readonly schema: typeof schema;
  readonly rawDatabase: Database.Database;
}

const migrationDirectory = new URL("../../../../../drizzle/sqlite/", import.meta.url);

export async function createSqliteDialect(
  filename: string,
  options: SqliteDialectOptions = {},
): Promise<SqliteDialect> {
  const rawDatabase = new Database(filename);
  rawDatabase.exec("pragma journal_mode = wal;");

  const migrationDb = wrapBetterSqliteMigrationDb(rawDatabase);
  await adoptLegacySqliteMigrations(migrationDb);
  await runRuntimeMigrations({
    db: migrationDb,
    migrationDirectory,
    logger: options.logger,
    dialect: "sqlite",
  });

  const rootDb = drizzle(rawDatabase, { schema });
  const dialect: SqliteDialect = {
    kind: "sqlite",
    db: rootDb,
    schema,
    adapter: null as unknown as DialectAdapter,
    rawDatabase,
    async close(): Promise<void> {
      rawDatabase.close();
    },
  };
  dialect.adapter = createSqliteAdapter(dialect);

  return dialect;
}

function createSqliteAdapter(dialect: SqliteDialect): DialectAdapter {
  // better-sqlite3 is single-connection: serialize async transactions so concurrent
  // claims cannot interleave BEGIN/COMMIT on the same handle.
  let transactionQueue: Promise<unknown> = Promise.resolve();

  const runExclusive = <T>(work: () => Promise<T>): Promise<T> => {
    const result = transactionQueue.then(work, work);
    transactionQueue = result.then(
      () => undefined,
      () => undefined,
    );
    return result;
  };

  return {
    kind: "sqlite",

    async transaction<T>(work: () => Promise<T>): Promise<T> {
      return await runExclusive(async () => {
        dialect.rawDatabase.exec("begin immediate");
        try {
          const result = await work();
          dialect.rawDatabase.exec("commit");
          return result;
        } catch (error) {
          try {
            dialect.rawDatabase.exec("rollback");
          } catch {
            // Prefer the original error if rollback also fails.
          }
          throw error;
        }
      });
    },

    async deleteRetentionTail(keepCount: number): Promise<void> {
      dialect.rawDatabase
        .prepare(
          `
          delete from runs
          where id in (
            select id from runs
            order by started_at desc, id desc
            limit -1 offset ?
          )
        `,
        )
        .run(keepCount);
    },

    async insertIgnoreIdempotency(values: IdempotencyInsertValues): Promise<boolean> {
      const result = dialect.db
        .insert(schema.idempotencyRecords)
        .values({
          keyHash: values.keyHash,
          claimId: values.claimId,
          requestHash: values.requestHash,
          state: "in_progress",
          responseValue: null,
          createdAt: values.createdAt,
          expiresAt: values.expiresAt,
        })
        .onConflictDoNothing()
        .run();
      return result.changes > 0;
    },
  };
}

function wrapBetterSqliteMigrationDb(raw: Database.Database): MigrationDb {
  return {
    async exec(sql: string): Promise<void> {
      raw.exec(sql);
    },
    prepare(sql: string) {
      const statement = raw.prepare(sql);
      return {
        async run(...params: unknown[]): Promise<{ changes: number }> {
          const result = statement.run(...params);
          return { changes: result.changes };
        },
        async all(...params: unknown[]): Promise<unknown[]> {
          return statement.all(...params);
        },
        async get(...params: unknown[]): Promise<unknown> {
          return statement.get(...params);
        },
      };
    },
  };
}