import { createPostgresJsClient } from "@platform-modules/db/postgres/postgres-js";
import { readMigrationFiles } from "drizzle-orm/migrator";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import { sql } from "drizzle-orm";
import { schema } from "./schema.js";
import { PostgresUnitOfWork } from "./unit-of-work.js";

export interface MigrationStatus {
  readonly applied: number;
  readonly expected: number;
  readonly pending: number;
  readonly drift: readonly string[];
}

export interface PostgresRuntime {
  readonly uow: PostgresUnitOfWork;
  migrate(): Promise<void>;
  health(): Promise<{ readonly status: "ok"; readonly database: "postgresql" }>;
  migrationStatus(): Promise<MigrationStatus>;
  close(): Promise<void>;
}

function isUndefinedTableError(error: unknown): boolean {
  let current: unknown = error;
  for (let depth = 0; depth < 6 && current !== undefined; depth += 1) {
    if (current instanceof Error && /does not exist|undefined_table|42P01/i.test(current.message)) {
      return true;
    }
    if (typeof current !== "object" || current === null) break;
    const record = current as { readonly code?: unknown; readonly cause?: unknown };
    if (record.code === "42P01") return true;
    current = record.cause;
  }
  return false;
}

export function createPostgresRuntime(
  connectionString: string,
  migrationsFolder: string,
): PostgresRuntime {
  const db = createPostgresJsClient({ connectionString, schema });
  const uow = new PostgresUnitOfWork(db);

  return {
    uow,
    async migrate(): Promise<void> {
      await migrate(db, { migrationsFolder });
    },
    async health() {
      const result = await db.execute(sql<{ alive: number }>`select 1::int as alive`);
      const row = Array.from(result)[0];
      if (row?.alive !== 1)
        throw new Error("PostgreSQL health probe returned an unexpected result");
      return { status: "ok" as const, database: "postgresql" as const };
    },
    async migrationStatus(): Promise<MigrationStatus> {
      const expected = readMigrationFiles({ migrationsFolder });
      let appliedRows: readonly { hash: string; created_at: string | number }[] = [];
      try {
        const result = await db.execute(
          sql<{ hash: string; created_at: string | number }>`
            select hash, created_at
            from drizzle.__drizzle_migrations
            order by created_at asc, id asc
          `,
        );
        appliedRows = Array.from(result).map((row) => ({
          hash: String(row.hash),
          created_at:
            typeof row.created_at === "number" || typeof row.created_at === "string"
              ? row.created_at
              : String(row.created_at),
        }));
      } catch (error) {
        if (!isUndefinedTableError(error)) throw error;
      }

      const drift: string[] = [];
      const byCreatedAt = new Map(
        appliedRows.map((row) => [Number(row.created_at), row.hash] as const),
      );
      for (const migration of expected) {
        const appliedHash = byCreatedAt.get(migration.folderMillis);
        if (appliedHash !== undefined && appliedHash !== migration.hash) {
          drift.push(`migration ${migration.folderMillis} hash differs from repository`);
        }
      }
      for (const row of appliedRows) {
        const createdAt = Number(row.created_at);
        if (!expected.some((migration) => migration.folderMillis === createdAt)) {
          drift.push(`database contains unknown migration ${createdAt}`);
        }
      }
      const appliedExpected = expected.filter(
        (migration) => byCreatedAt.get(migration.folderMillis) === migration.hash,
      ).length;
      return {
        applied: appliedRows.length,
        expected: expected.length,
        pending: Math.max(0, expected.length - appliedExpected),
        drift,
      };
    },
    async close(): Promise<void> {
      const client = (
        db as unknown as {
          readonly $client?: { end(options?: { timeout?: number }): Promise<void> };
        }
      ).$client;
      if (client) await client.end({ timeout: 5 });
    },
  };
}

export function createPostgresPersistence(connectionString: string): PostgresUnitOfWork {
  const db = createPostgresJsClient({ connectionString, schema });
  return new PostgresUnitOfWork(db);
}