import { PGlite } from "@electric-sql/pglite";import { drizzle as drizzlePGlite } from "drizzle-orm/pglite";import { migrate as migratePGlite } from "drizzle-orm/pglite/migrator";import { drizzle as drizzlePostgres } from "drizzle-orm/postgres-js";import { migrate as migratePostgres } from "drizzle-orm/postgres-js/migrator";import { existsSync, readdirSync, readFileSync } from "node:fs";import { mkdir, unlink } from "node:fs/promises";import * as path from "node:path";import { fileURLToPath } from "node:url";import postgres from "postgres"; import { drizzleSchema } from "./schema"; export type SqlBackend = "pglite" | "postgres"; export type CreateSqlRuntimeOptions = {  databaseUrl?: string;  localDataDir?: string;  postgresApplicationName?: string;  migrationsFolder?: string;}; const createPGliteDb = (client: PGlite) =>  drizzlePGlite({ client, schema: drizzleSchema }); const createPostgresDb = (client: postgres.Sql) =>  drizzlePostgres({ client, schema: drizzleSchema }); export type PGliteDb = ReturnType<typeof createPGliteDb>;export type PostgresDb = ReturnType<typeof createPostgresDb>;export type DrizzleDb = PGliteDb | PostgresDb; export type SqlRuntime = {  backend: SqlBackend;  db: DrizzleDb;  close: () => Promise<void>;}; const trim = (value: string | undefined): string | undefined => {  const candidate = value?.trim();  return candidate && candidate.length > 0 ? candidate : undefined;}; const isPostgresUrl = (value: string): boolean =>  value.startsWith("postgres://") || value.startsWith("postgresql://"); /** * Remove a stale PGlite `postmaster.pid` lock file if present. * * PGlite writes this file on open and removes it on close. When a process * exits without calling `PGlite.close()` (e.g. the Vite dev server is * killed with SIGKILL, or the terminal is closed), the lock file is left * behind and prevents subsequent PGlite instances from opening the * database. PGlite uses a synthetic PID (e.g. `-42`) that is never a real * OS process, so we cannot use it to detect a live owner. Instead we rely * on the fact that in the local single-user context only one process * should ever own this database at a time. */const cleanupStalePGliteLock = async (dataDir: string): Promise<void> => {  const lockPath = path.join(dataDir, "postmaster.pid");  if (!existsSync(lockPath)) {    return;  }   // PGlite's synthetic PID is always negative (e.g. -42). A real Postgres  // postmaster would write a positive PID. If we ever see a positive PID  // we leave the file alone.  try {    const content = readFileSync(lockPath, "utf-8");    const firstLine = content.split("\n")[0]?.trim();    const pid = Number(firstLine);    if (!Number.isNaN(pid) && pid > 0) {      // Looks like a real Postgres PID — don't touch it.      return;    }  } catch {    // If we can't read the file, try to remove it anyway.  }   try {    await unlink(lockPath);  } catch {    // Best-effort cleanup; PGlite will report the real error if this fails.  }}; const createPGliteRuntime = async (localDataDir: string): Promise<SqlRuntime> => {  const normalized = trim(localDataDir) ?? ".executor/control-plane-pgdata";   let client: PGlite;  if (normalized === ":memory:") {    client = new PGlite();  } else {    const resolvedDataDir = path.resolve(normalized);    if (!existsSync(resolvedDataDir)) {      await mkdir(resolvedDataDir, { recursive: true });    }    await cleanupStalePGliteLock(resolvedDataDir);    client = new PGlite(resolvedDataDir);  }   const db = createPGliteDb(client);   return {    backend: "pglite",    db,    close: async () => {      await client.close();    },  };}; const createPostgresRuntime = async (  databaseUrl: string,  applicationName: string | undefined,): Promise<SqlRuntime> => {  const client = postgres(databaseUrl, {    prepare: false,    max: 10,    ...(applicationName      ? { connection: { application_name: applicationName } }      : {}),  });  const db = createPostgresDb(client);   return {    backend: "postgres",    db,    close: async () => {      await client.end({ timeout: 5 });    },  };}; const resolveMigrationsFolder = (explicit: string | undefined): string => {  const explicitTrimmed = trim(explicit);  if (explicitTrimmed) {    return path.resolve(explicitTrimmed);  }   const moduleDir = path.dirname(fileURLToPath(import.meta.url));  const packageRoot = path.resolve(moduleDir, "../..");  const cwd = process.cwd();  const candidates = [    path.resolve(packageRoot, "drizzle"),    path.resolve(cwd, "packages/control-plane/drizzle"),    path.resolve(cwd, "drizzle"),  ];   const hasModernMigrations = (candidate: string): boolean => {    if (!existsSync(candidate)) {      return false;    }     const entries = readdirSync(candidate, { withFileTypes: true });    return entries.some(      (entry) =>        entry.isDirectory()        && existsSync(path.join(candidate, entry.name, "migration.sql")),    );  };   for (const candidate of candidates) {    if (      existsSync(path.join(candidate, "meta", "_journal.json"))      || hasModernMigrations(candidate)    ) {      return candidate;    }  }   throw new Error(    "Unable to resolve Drizzle migrations folder for control-plane persistence",  );}; export const runMigrations = async (  runtime: SqlRuntime,  options?: { migrationsFolder?: string },): Promise<void> => {  const migrationsFolder = resolveMigrationsFolder(options?.migrationsFolder);   if (runtime.backend === "pglite") {    await migratePGlite(runtime.db as PGliteDb, { migrationsFolder });    return;  }   await migratePostgres(runtime.db as PostgresDb, { migrationsFolder });}; export const createSqlRuntime = async (  options: CreateSqlRuntimeOptions,): Promise<SqlRuntime> => {  const databaseUrl = trim(options.databaseUrl);  const runtime =    databaseUrl && isPostgresUrl(databaseUrl)      ? await createPostgresRuntime(          databaseUrl,          trim(options.postgresApplicationName),        )      : await createPGliteRuntime(          options.localDataDir ?? ".executor/control-plane-pgdata",        );   return runtime;};