import { spawn } from 'node:child_process';
import { mkdtemp, rm } from 'node:fs/promises';
import { tmpdir } from 'node:os';
import { dirname, join } from 'node:path';
import { createRequire } from 'node:module';
import { initdbCached } from '@tooling/pg-template';
import { sql } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import type { Querier, TransactionalDatabase } from '@platform-modules/db';
import pg from 'pg';
import { settingsSchema, type SettingsSchema } from './settings.js';
import { mediaSchema, type MediaSchema } from './media.js';

// Unix-domain socket ONLY. TCP is disabled (`listen_addresses=''`), so there is no
// ephemeral-port allocation and therefore no allocate-then-close-then-bind TOCTOU
// port race. The socket file is `<dataDir>/.s.PGSQL.<PG_PORT>`; dataDir is unique
// per mkdtemp, so a fixed port number never collides across concurrent harness
// instances. The connection semantics (concurrent connections, transactions,
// FOR UPDATE row locks) are identical to TCP — the 8-connection MVCC race test is
// unaffected.
const PG_PORT = 5432;

const CREATE_SETTINGS_TABLE = sql`
  CREATE TABLE site_settings (
    key text PRIMARY KEY,
    value jsonb NOT NULL,
    updated_at timestamptz(3) NOT NULL DEFAULT NOW()
  )
`;

const CREATE_MEDIA_TABLE = sql`
  CREATE TABLE media_assets (
    key text PRIMARY KEY,
    url text NOT NULL,
    content_type text NOT NULL,
    size bigint NOT NULL,
    width integer,
    height integer,
    uploader_id text NOT NULL,
    created_at timestamptz(3) NOT NULL DEFAULT NOW()
  )
`;

function embeddedPlatformPackage(): string {
  const { platform, arch } = process;
  if (platform === 'linux' && arch === 'x64') return 'linux-x64';
  if (platform === 'linux' && arch === 'arm64') return 'linux-arm64';
  if (platform === 'linux' && arch === 'arm') return 'linux-arm';
  if (platform === 'linux' && arch === 'ia32') return 'linux-ia32';
  if (platform === 'linux' && arch === 'ppc64') return 'linux-ppc64';
  if (platform === 'darwin' && arch === 'arm64') return 'darwin-arm64';
  if (platform === 'darwin' && arch === 'x64') return 'darwin-x64';
  if (platform === 'win32' && arch === 'x64') return 'windows-x64';
  throw new Error(`unsupported embedded-postgres platform: ${platform}-${arch}`);
}

function resolveBinDir(): string {
  const require = createRequire(import.meta.url);
  const pkg = `@embedded-postgres/${embeddedPlatformPackage()}`;
  const entry = require.resolve(pkg);
  return join(dirname(entry), '..', 'native', 'bin');
}

const PG_ENV = { ...process.env, LC_ALL: 'C', LANG: 'C' };

async function waitForPostgres(pool: pg.Pool, attempts = 60, delayMs = 250): Promise<void> {
  let lastErr: unknown;
  for (let i = 0; i < attempts; i++) {
    try {
      await pool.query('SELECT 1');
      return;
    } catch (e) {
      lastErr = e;
      await new Promise((r) => setTimeout(r, delayMs));
    }
  }
  throw lastErr instanceof Error ? lastErr : new Error(String(lastErr));
}

export async function startPg(): Promise<{
  db: Querier<SettingsSchema>;
  createIsolatedDb: () => Querier<SettingsSchema>;
  stop: () => Promise<void>;
}> {
  const binDir = resolveBinDir();
  const ext = process.platform === 'win32' ? '.exe' : '';
  const initdbBin = join(binDir, `initdb${ext}`);
  const postgresBin = join(binDir, `postgres${ext}`);
  const dataDir = await mkdtemp(join(tmpdir(), 'mod-cms-pg-'));
  const user = 'test';

  await initdbCached({ initdbBin, dataDir, user, args: ['-A', 'trust', '--no-sync'], env: PG_ENV });

  // No shell: each arg is literal, so `listen_addresses=` must carry an EMPTY value
  // (quoting it as "''" would pass two literal quote chars and postgres would reject
  // it). Empty = TCP off; clients reach it only via the `-k dataDir` Unix socket.
  const proc = spawn(
    postgresBin,
    ['-D', dataDir, '-p', String(PG_PORT), '-k', dataDir, '-c', 'listen_addresses='],
    { stdio: ['ignore', 'ignore', 'pipe'], env: PG_ENV },
  );

  // Keep the tail of postgres' own stderr so an early death reports WHY, not just a
  // bare exit code.
  let stderrTail = '';
  proc.stderr?.on('data', (chunk: Buffer) => {
    stderrTail = (stderrTail + chunk.toString()).slice(-2000);
  });

  // An early death (bad config, socket dir unwritable, initdb mismatch) must surface
  // immediately as its own error, not as a 15s `waitForPostgres` timeout that hides
  // the cause. After `ready`, `stop()`'s SIGKILL is expected — guard so teardown
  // never rejects a settled harness. A post-`ready` `error`/`exit` leaves this
  // promise pending forever, which is GC'd with no unhandled rejection.
  let ready = false;
  const earlyExit = new Promise<never>((_, reject) => {
    proc.on('error', (err) => {
      if (!ready) reject(err);
    });
    proc.on('exit', (code, signal) => {
      if (!ready) {
        const detail = stderrTail.trim();
        reject(
          new Error(
            `postgres exited before ready (code=${code}, signal=${signal})${detail ? `\n${detail}` : ''}`,
          ),
        );
      }
    });
  });

  const pools: pg.Pool[] = [];
  const makePool = () => {
    const pool = new pg.Pool({ host: dataDir, port: PG_PORT, user, database: 'postgres', max: 1 });
    pools.push(pool);
    return pool;
  };

  const primaryPool = makePool();
  try {
    await Promise.race([waitForPostgres(primaryPool), earlyExit]);
  } catch (err) {
    await Promise.all(pools.map((p) => p.end().catch(() => undefined)));
    if (!proc.killed) proc.kill('SIGKILL');
    await rm(dataDir, { recursive: true, force: true });
    throw err;
  }
  ready = true;

  const makeDb = (pool: pg.Pool) =>
    drizzle(pool, { schema: settingsSchema }) as unknown as Querier<SettingsSchema>;

  const db = makeDb(primaryPool);
  await db.execute(CREATE_SETTINGS_TABLE);

  const stop = async () => {
    await Promise.all(pools.map((p) => p.end().catch(() => undefined)));
    if (!proc.killed) {
      proc.kill('SIGKILL');
    }
    await rm(dataDir, { recursive: true, force: true });
  };

  return {
    db,
    createIsolatedDb: () => makeDb(makePool()),
    stop,
  };
}

export async function startMediaPg(): Promise<{
  db: TransactionalDatabase<MediaSchema>;
  createIsolatedDb: () => TransactionalDatabase<MediaSchema>;
  stop: () => Promise<void>;
}> {
  const binDir = resolveBinDir();
  const ext = process.platform === 'win32' ? '.exe' : '';
  const initdbBin = join(binDir, `initdb${ext}`);
  const postgresBin = join(binDir, `postgres${ext}`);
  const dataDir = await mkdtemp(join(tmpdir(), 'mod-cms-media-pg-'));
  const user = 'test';

  await initdbCached({ initdbBin, dataDir, user, args: ['-A', 'trust', '--no-sync'], env: PG_ENV });

  const proc = spawn(
    postgresBin,
    ['-D', dataDir, '-p', String(PG_PORT), '-k', dataDir, '-c', 'listen_addresses='],
    { stdio: ['ignore', 'ignore', 'pipe'], env: PG_ENV },
  );

  let stderrTail = '';
  proc.stderr?.on('data', (chunk: Buffer) => {
    stderrTail = (stderrTail + chunk.toString()).slice(-2000);
  });

  let ready = false;
  const earlyExit = new Promise<never>((_, reject) => {
    proc.on('error', (err) => {
      if (!ready) reject(err);
    });
    proc.on('exit', (code, signal) => {
      if (!ready) {
        const detail = stderrTail.trim();
        reject(
          new Error(
            `postgres exited before ready (code=${code}, signal=${signal})${detail ? `\n${detail}` : ''}`,
          ),
        );
      }
    });
  });

  const pools: pg.Pool[] = [];
  const makePool = () => {
    const pool = new pg.Pool({ host: dataDir, port: PG_PORT, user, database: 'postgres', max: 1 });
    pools.push(pool);
    return pool;
  };

  const primaryPool = makePool();
  try {
    await Promise.race([waitForPostgres(primaryPool), earlyExit]);
  } catch (err) {
    await Promise.all(pools.map((p) => p.end().catch(() => undefined)));
    if (!proc.killed) proc.kill('SIGKILL');
    await rm(dataDir, { recursive: true, force: true });
    throw err;
  }
  ready = true;

  const makeDb = (pool: pg.Pool) =>
    drizzle(pool, { schema: mediaSchema }) as unknown as TransactionalDatabase<MediaSchema>;

  const db = makeDb(primaryPool);
  await db.execute(CREATE_MEDIA_TABLE);

  const stop = async () => {
    await Promise.all(pools.map((p) => p.end().catch(() => undefined)));
    if (!proc.killed) {
      proc.kill('SIGKILL');
    }
    await rm(dataDir, { recursive: true, force: true });
  };

  return {
    db,
    createIsolatedDb: () => makeDb(makePool()),
    stop,
  };
}