import { spawn } from 'node:child_process';
import fs from 'node:fs';
import fsp from 'node:fs/promises';
import path from 'node:path';

import mysql from 'mysql2/promise';
import pg from 'pg';
import { parse as parsePgConnectionString } from 'pg-connection-string';

import { loadMemindEnvFiles } from '../scripts/memind-runtime-profile.mjs';
import { assertNonProductionTarget } from './safety.mjs';

const { Client: PgClient } = pg;
const SAFE_DB_NAME = /^[a-z][a-z0-9_]{5,62}$/;

function assertLoopbackHost(host, label) {
  const normalized = String(host ?? '').toLowerCase();
  if (!['localhost', '127.0.0.1', '::1', '/tmp'].includes(normalized)) {
    throw new Error(`${label} must use a local loopback or Unix socket`);
  }
}

function quoteMysqlIdentifier(identifier) {
  if (!SAFE_DB_NAME.test(identifier)) throw new Error(`Unsafe MySQL database name: ${identifier}`);
  return `\`${identifier}\``;
}

function quotePgIdentifier(identifier) {
  if (!SAFE_DB_NAME.test(identifier)) throw new Error(`Unsafe PostgreSQL database name: ${identifier}`);
  return `"${identifier}"`;
}

export function makeIsolatedDatabaseName(runId, prefix) {
  const suffix = String(runId)
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '_')
    .replace(/^_+|_+$/g, '')
    .slice(0, 38);
  const name = `${prefix}_${suffix || 'local'}`;
  if (!SAFE_DB_NAME.test(name)) throw new Error(`Unsafe generated database name: ${name}`);
  return name;
}

export function buildPgConnectionString(baseConnectionString, database) {
  if (!SAFE_DB_NAME.test(database)) throw new Error(`Unsafe PostgreSQL database name: ${database}`);
  const config = parsePgConnectionString(baseConnectionString);
  assertLoopbackHost(config.host, 'PostgreSQL');
  const user = encodeURIComponent(config.user ?? '');
  const password = config.password ? `:${encodeURIComponent(config.password)}` : '';
  const authorityHost = config.host?.startsWith('/') ? 'localhost' : config.host;
  const port = config.port ? `:${config.port}` : '';
  const query = config.host?.startsWith('/')
    ? `?host=${encodeURIComponent(config.host)}${config.port ? `&port=${encodeURIComponent(config.port)}` : ''}`
    : '';
  return `postgresql://${user}${password}@${authorityHost}${port}/${database}${query}`;
}

export function buildContainerPgConnectionString(baseConnectionString, database) {
  const raw = String(baseConnectionString ?? '').trim();
  if (!raw) return '';
  const parsed = new URL(raw);
  if (!SAFE_DB_NAME.test(database)) throw new Error(`Unsafe PostgreSQL database name: ${database}`);
  parsed.pathname = `/${database}`;
  return parsed.toString();
}

/**
 * Copy only the selected local provider metadata into an isolated gate DB.
 * The encrypted key material stays inside the local database boundary and is
 * never logged; production/remote DSNs are rejected by assertNonProductionTarget.
 */
export async function seedSelectedProviderKeys({ sourceUrl, targetUrl }) {
  assertNonProductionTarget(sourceUrl, 'source provider database');
  assertNonProductionTarget(targetUrl, 'isolated provider database');
  const source = await mysql.createConnection(sourceUrl);
  const target = await mysql.createConnection(targetUrl);
  try {
    const [rows] = await source.query(
      `SELECT * FROM h5_llm_provider_keys
       ORDER BY is_selected DESC, is_vision_selected DESC, updated_at DESC`,
    );
    for (const row of rows) {
      await target.execute(
        `INSERT INTO h5_llm_provider_keys
         (id, provider_id, provider_kind, api_url, base_path, models_json, goosed_provider_id,
          engine, relay_provider, name, api_key_ciphertext, api_key_iv, api_key_tag,
          default_model, status, is_selected, is_vision_selected, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
           provider_id = VALUES(provider_id), provider_kind = VALUES(provider_kind),
           api_url = VALUES(api_url), base_path = VALUES(base_path), models_json = VALUES(models_json),
           goosed_provider_id = VALUES(goosed_provider_id), engine = VALUES(engine),
           relay_provider = VALUES(relay_provider), api_key_ciphertext = VALUES(api_key_ciphertext),
           api_key_iv = VALUES(api_key_iv), api_key_tag = VALUES(api_key_tag),
           default_model = VALUES(default_model), status = VALUES(status),
           is_selected = VALUES(is_selected), is_vision_selected = VALUES(is_vision_selected),
           updated_at = VALUES(updated_at)`,
        [
          row.id, row.provider_id, row.provider_kind, row.api_url, row.base_path,
          row.models_json, row.goosed_provider_id, row.engine, row.relay_provider,
          row.name, row.api_key_ciphertext, row.api_key_iv, row.api_key_tag,
          row.default_model, row.status, row.is_selected, row.is_vision_selected,
          row.created_at, row.updated_at,
        ],
      );
    }
    const [bindings] = await source.query('SELECT * FROM h5_llm_executor_bindings');
    for (const binding of bindings) {
      await target.execute(
        `INSERT INTO h5_llm_executor_bindings
         (id, executor, purpose, provider_key_id, model, enabled, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE provider_key_id = VALUES(provider_key_id),
           model = VALUES(model), enabled = VALUES(enabled), updated_at = VALUES(updated_at)`,
        [
          binding.id, binding.executor, binding.purpose, binding.provider_key_id,
          binding.model, binding.enabled, binding.created_at, binding.updated_at,
        ],
      );
    }
    return { copied: rows.length, bindings: bindings.length };
  } finally {
    await Promise.all([source.end(), target.end()]);
  }
}

/**
 * Select the backend direct-LLM provider inside an isolated gate database.
 * This deliberately never mutates the source database or any production DSN.
 */
export async function selectBackendLlmProvider({
  targetUrl,
  providerId = 'custom_deepseek',
  model = 'deepseek-v4-pro',
}) {
  assertNonProductionTarget(targetUrl, 'isolated backend LLM database');
  const target = await mysql.createConnection(targetUrl);
  try {
    const [rows] = await target.query(
      `SELECT id, provider_id, provider_kind, models_json, status
       FROM h5_llm_provider_keys
       WHERE provider_id = ? AND status = 'active'
       ORDER BY updated_at DESC
       LIMIT 1`,
      [providerId],
    );
    const row = rows[0];
    if (!row) throw new Error(`isolated backend LLM provider not found: ${providerId}`);
    const supportedModels = row.provider_kind === 'custom'
      ? (() => {
          try { return JSON.parse(row.models_json ?? '[]'); } catch { return []; }
        })()
      : ['deepseek-v4-pro', 'deepseek-v4-flash'];
    if (!supportedModels.includes(model)) {
      throw new Error(`backend LLM model is not supported by ${providerId}: ${model}`);
    }
    await target.query('UPDATE h5_llm_provider_keys SET is_selected = 0, updated_at = ?', [Date.now()]);
    await target.execute(
      `UPDATE h5_llm_provider_keys
       SET is_selected = 1, default_model = ?, updated_at = ?
       WHERE id = ?`,
      [model, Date.now(), row.id],
    );
    await target.execute(
      `UPDATE h5_llm_executor_bindings
       SET provider_key_id = ?, model = ?, updated_at = ?
       WHERE executor IN ('goose', 'aider', 'openhands') AND purpose = 'default' AND enabled = 1`,
      [row.id, model, Date.now()],
    );
    return { providerId: row.provider_id, providerKeyId: row.id, model };
  } finally {
    await target.end();
  }
}

async function waitForPortal(baseUrl, child, timeoutMs = 60_000) {
  const deadline = Date.now() + timeoutMs;
  while (Date.now() < deadline) {
    if (child.exitCode !== null) throw new Error(`isolated Portal exited with code ${child.exitCode}`);
    try {
      const response = await fetch(`${baseUrl}/auth/status`, { signal: AbortSignal.timeout(1_000) });
      if (response.ok) return;
    } catch {
      // Startup can take several seconds while the isolated schema is initialized.
    }
    await new Promise((resolve) => setTimeout(resolve, 250));
  }
  throw new Error(`isolated Portal did not become ready: ${baseUrl}`);
}

async function waitForHttpHealth(baseUrl, child, label, timeoutMs = 30_000) {
  const deadline = Date.now() + timeoutMs;
  while (Date.now() < deadline) {
    if (child.exitCode !== null) throw new Error(`${label} exited with code ${child.exitCode}`);
    try {
      const response = await fetch(`${baseUrl}/health`, { signal: AbortSignal.timeout(1_000) });
      if (response.ok) return;
    } catch {
      // The child can take a moment to bind its listener.
    }
    await new Promise((resolve) => setTimeout(resolve, 150));
  }
  throw new Error(`${label} did not become ready: ${baseUrl}`);
}

async function createMysqlDatabase(baseUrl, database) {
  const parsed = new URL(baseUrl);
  assertNonProductionTarget(baseUrl, 'MySQL URL');
  assertLoopbackHost(parsed.hostname, 'MySQL');
  const adminUrl = new URL(baseUrl);
  adminUrl.pathname = '/mysql';
  const connection = await mysql.createConnection(adminUrl.toString());
  try {
    await connection.query(`CREATE DATABASE ${quoteMysqlIdentifier(database)}
      CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
  } finally {
    await connection.end();
  }
  parsed.pathname = `/${database}`;
  return parsed.toString();
}

async function dropMysqlDatabase(baseUrl, database) {
  const adminUrl = new URL(baseUrl);
  adminUrl.pathname = '/mysql';
  const connection = await mysql.createConnection(adminUrl.toString());
  try {
    await connection.query(`DROP DATABASE IF EXISTS ${quoteMysqlIdentifier(database)}`);
  } finally {
    await connection.end();
  }
}

async function createPgDatabase(baseUrl, database) {
  const config = parsePgConnectionString(baseUrl);
  assertLoopbackHost(config.host, 'PostgreSQL');
  const admin = new PgClient({ connectionString: baseUrl, database: 'postgres' });
  await admin.connect();
  try {
    await admin.query(`CREATE DATABASE ${quotePgIdentifier(database)}`);
  } finally {
    await admin.end();
  }
  return buildPgConnectionString(baseUrl, database);
}

async function dropPgDatabase(baseUrl, database) {
  const admin = new PgClient({ connectionString: baseUrl, database: 'postgres' });
  await admin.connect();
  try {
    await admin.query(
      `SELECT pg_terminate_backend(pid)
       FROM pg_stat_activity
       WHERE datname = $1 AND pid <> pg_backend_pid()`,
      [database],
    );
    await admin.query(`DROP DATABASE IF EXISTS ${quotePgIdentifier(database)}`);
  } finally {
    await admin.end();
  }
}

export async function createLocalGateStack({
  root = path.resolve(new URL('..', import.meta.url).pathname),
  runId = `gate-${Date.now()}`,
  port = 19081,
  portalRoot = root,
  nodeEnv = 'test',
  runtimeProfile = 'local',
  beforePortalStart = null,
} = {}) {
  const resolvedPortalRoot = path.resolve(portalRoot);
  if (
    resolvedPortalRoot !== path.resolve(root)
    && resolvedPortalRoot !== path.resolve(root, '.runtime', 'portal')
  ) {
    throw new Error('isolated Portal root must be the repository or .runtime/portal');
  }
  const baseEnv = { ...process.env };
  loadMemindEnvFiles(root, baseEnv);
  if (!baseEnv.DATABASE_URL) throw new Error('Local release gate requires DATABASE_URL');
  if (!baseEnv.MINDSPACE_USERDATA_PG_URL) {
    throw new Error('Local release gate requires MINDSPACE_USERDATA_PG_URL');
  }
  assertNonProductionTarget(baseEnv.DATABASE_URL, 'database');
  assertNonProductionTarget(baseEnv.MINDSPACE_USERDATA_PG_URL, 'Page Data database');

  const mysqlDatabase = makeIsolatedDatabaseName(runId, 'memind_gate');
  const pgDatabase = makeIsolatedDatabaseName(runId, 'mindspace_gate');
  const runRoot = path.join(root, '.release-gate', 'local', runId);
  const sandboxRoot = path.join(runRoot, 'sandbox');
  const usersRoot = path.join(sandboxRoot, 'users');
  const storageRoot = path.join(sandboxRoot, 'data', 'mindspace');
  const publishRoot = path.join(sandboxRoot, 'MindSpace');
  const logPath = path.join(runRoot, 'portal.log');
  const deepseekProxyLogPath = path.join(runRoot, 'deepseek-no-think.log');
  // Each isolated stack gets its own compatibility-proxy port so parallel
  // suites never share mutable provider state. The proxy talks directly to
  // the configured backend API; it is not the H5 relay service.
  const deepseekNoThinkPort = Number(
    process.env.MEMIND_RELEASE_GATE_DEEPSEEK_PROXY_PORT
      ?? (18000 + (Number(port) % 1000)),
  );
  await Promise.all([
    fsp.mkdir(usersRoot, { recursive: true }),
    fsp.mkdir(storageRoot, { recursive: true }),
    fsp.mkdir(publishRoot, { recursive: true }),
  ]);
  // Keep the H5 root writable and isolated so packaged-runtime smoke tests
  // cannot materialize MindSpace pages inside the candidate artifact. The
  // platform skill catalog still comes from the packaged runtime via a
  // read-only symlink.
  await fsp.symlink(
    path.join(resolvedPortalRoot, 'skills'),
    path.join(sandboxRoot, 'skills'),
    'dir',
  );
  const wechatBundle = path.join(resolvedPortalRoot, 'wechat-mp.bundle.mjs');
  if (await fsp.stat(wechatBundle).catch(() => null)) {
    await fsp.copyFile(wechatBundle, path.join(sandboxRoot, 'wechat-mp.bundle.mjs'));
  }

  let mysqlUrl;
  let pgUrl;
  let child;
  let deepseekProxyChild;
  let logFd;
  let deepseekProxyLogFd;
  let childEnv;
  let baseUrl;

  async function stopPortal() {
    const proxyToStop = deepseekProxyChild;
    const waitForProxyExit = async (timeoutMs) => {
      if (!proxyToStop || proxyToStop.exitCode !== null) return true;
      return Promise.race([
        new Promise((resolve) => proxyToStop.once('exit', () => resolve(true))),
        new Promise((resolve) => setTimeout(() => resolve(false), timeoutMs)),
      ]);
    };
    if (proxyToStop && proxyToStop.exitCode === null) {
      proxyToStop.kill('SIGTERM');
      const exited = await waitForProxyExit(3_000);
      if (!exited && proxyToStop.exitCode === null) proxyToStop.kill('SIGKILL');
    }
    deepseekProxyChild = undefined;
    if (deepseekProxyLogFd !== undefined) {
      fs.closeSync(deepseekProxyLogFd);
      deepseekProxyLogFd = undefined;
    }
    const processToStop = child;
    const waitForExit = async (timeoutMs) => {
      if (!processToStop || processToStop.exitCode !== null) return true;
      return Promise.race([
        new Promise((resolve) => processToStop.once('exit', () => resolve(true))),
        new Promise((resolve) => setTimeout(() => resolve(false), timeoutMs)),
      ]);
    };
    if (processToStop && processToStop.exitCode === null) {
      processToStop.kill('SIGTERM');
      const exited = await waitForExit(5_000);
      if (!exited && processToStop.exitCode === null) {
        processToStop.kill('SIGKILL');
        const killed = await waitForExit(5_000);
        if (!killed && processToStop.exitCode === null) {
          throw new Error('isolated Portal did not stop after SIGKILL');
        }
      }
    }
    child = undefined;
    if (logFd !== undefined) {
      fs.closeSync(logFd);
      logFd = undefined;
    }
  }

  async function startPortal() {
    if (!childEnv || !baseUrl) throw new Error('isolated Portal environment is not initialized');
    if (child && child.exitCode === null) throw new Error('isolated Portal is already running');
    deepseekProxyLogFd = fs.openSync(deepseekProxyLogPath, 'a');
    deepseekProxyChild = spawn(
      process.execPath,
      [path.join(resolvedPortalRoot, 'deepseek-no-think-proxy.mjs')],
      {
        cwd: root,
        env: {
          ...childEnv,
          MEMIND_DEEPSEEK_PROXY_ENTRYPOINT: '1',
          MEMIND_DEEPSEEK_NO_THINK_PORT: String(deepseekNoThinkPort),
        },
        stdio: ['ignore', deepseekProxyLogFd, deepseekProxyLogFd],
      },
    );
    await waitForHttpHealth(
      `http://127.0.0.1:${deepseekNoThinkPort}`,
      deepseekProxyChild,
      'DeepSeek no-thinking proxy',
    );
    logFd = fs.openSync(logPath, 'a');
    child = spawn(process.execPath, ['server.mjs'], {
      cwd: resolvedPortalRoot,
      env: childEnv,
      stdio: ['ignore', logFd, logFd],
    });
    await waitForPortal(baseUrl, child);
  }

  try {
    mysqlUrl = await createMysqlDatabase(baseEnv.DATABASE_URL, mysqlDatabase);
    pgUrl = await createPgDatabase(baseEnv.MINDSPACE_USERDATA_PG_URL, pgDatabase);
    childEnv = {
      ...baseEnv,
      NODE_ENV: nodeEnv,
      ...(runtimeProfile ? { MEMIND_RUNTIME_PROFILE: runtimeProfile } : {}),
      H5_HOST: '127.0.0.1',
      H5_PORT: String(port),
      H5_PUBLIC_BASE_URL: `http://127.0.0.1:${port}`,
      DATABASE_URL: mysqlUrl,
      MINDSPACE_USERDATA_PG_URL: pgUrl,
      MINDSPACE_USERDATA_MCP_PG_URL: buildContainerPgConnectionString(
        baseEnv.MINDSPACE_USERDATA_MCP_PG_URL,
        pgDatabase,
      ),
      // Keep code and packaged skills available while isolating all writable
      // H5/MindSpace state from the candidate runtime artifact.
      MEMIND_PORTAL_H5_ROOT: sandboxRoot,
      H5_USERS_ROOT: usersRoot,
      MINDSPACE_STORAGE_ROOT: storageRoot,
      MEMIND_SHARED_PUBLISH_ROOT: publishRoot,
      MEMIND_WORKSPACE_MAINTENANCE: '0',
      H5_RELAY_BOOTSTRAP_DISABLED: '1',
      // Keep the selected backend provider direct while enabling the local
      // DeepSeek protocol compatibility shim. This is not the H5 relay: it
      // only prevents dropped reasoning_content from turning tool rounds into
      // provider HTTP 400s.
      MEMIND_DEEPSEEK_DISABLE_THINKING: '1',
      MEMIND_DEEPSEEK_NO_THINK_PORT: String(deepseekNoThinkPort),
      // The isolated gate must exercise the same shadow validation contract
      // used by production release checks; the database remains isolated.
      MEMIND_ORCHESTRATOR_MODE: 'shadow',
      MEMIND_ORCHESTRATOR_SHADOW_OBSERVATION_ENABLED: '1',
      MEMIND_ORCHESTRATOR_PAGE_DATA_VALIDATION_GATE_ENABLED: '1',
      MEMIND_RUNTIME_REDIS_NAMESPACE: `memind:release-gate:${runId}`,
      H5_ADMIN_USERNAME: 'gate_admin',
      H5_ADMIN_PASSWORD: `Gate-${runId}-Admin!`,
      H5_ACCESS_PASSWORD: `Gate-${runId}-Access!`,
      H5_WECHAT_MP_ENABLED: '0',
      H5_REMINDER_WORKER_ENABLED: '0',
      MEMIND_ANALYTICS_ENABLED: '0',
      MINDSPACE_ANALYTICS_ENABLED: '0',
      MEMIND_RYBBIT_ENABLED: 'false',
    };
    baseUrl = `http://127.0.0.1:${port}`;
    if (beforePortalStart) {
      await beforePortalStart({
        mysqlUrl,
        pgUrl,
        mysqlDatabase,
        pgDatabase,
        sandboxRoot,
      });
    }
    await startPortal();
    await fsp.writeFile(
      path.join(runRoot, 'stack.json'),
      `${JSON.stringify({
        run_id: runId,
        portal_base: baseUrl,
        portal_root: path.relative(root, resolvedPortalRoot) || '.',
        node_env: nodeEnv,
        mysql_database: mysqlDatabase,
        postgres_database: pgDatabase,
        sandbox_root: path.relative(root, sandboxRoot),
        production_connected: false,
      }, null, 2)}\n`,
    );
    return {
      runId,
      runRoot,
      sandboxRoot,
      baseUrl,
      mysqlUrl,
      pgUrl,
      mysqlDatabase,
      pgDatabase,
      sourceDatabaseUrl: baseEnv.DATABASE_URL,
      async restartPortal() {
        await stopPortal();
        await startPortal();
      },
      async cleanup() {
        await stopPortal();
        const keepSandbox = process.env.MEMIND_RELEASE_GATE_KEEP_SANDBOX === '1';
        const cleanupResults = await Promise.allSettled([
          dropPgDatabase(baseEnv.MINDSPACE_USERDATA_PG_URL, pgDatabase),
          dropMysqlDatabase(baseEnv.DATABASE_URL, mysqlDatabase),
          keepSandbox ? Promise.resolve() : fsp.rm(sandboxRoot, { recursive: true, force: true }),
        ]);
        const cleanupState = {
          mysql_dropped: cleanupResults[1].status === 'fulfilled',
          postgres_dropped: cleanupResults[0].status === 'fulfilled',
          sandbox_removed: !keepSandbox && cleanupResults[2].status === 'fulfilled',
          sandbox_preserved_for_debug: keepSandbox,
        };
        await fsp.writeFile(
          path.join(runRoot, 'cleanup.json'),
          `${JSON.stringify(cleanupState, null, 2)}\n`,
        );
        if (cleanupResults.some((result) => result.status === 'rejected')) {
          throw new Error('isolated release gate cleanup failed');
        }
      },
    };
  } catch (error) {
    if (child && child.exitCode === null) child.kill('SIGKILL');
    if (deepseekProxyChild && deepseekProxyChild.exitCode === null) {
      deepseekProxyChild.kill('SIGKILL');
    }
    if (logFd !== undefined) fs.closeSync(logFd);
    if (deepseekProxyLogFd !== undefined) fs.closeSync(deepseekProxyLogFd);
    if (pgUrl) await dropPgDatabase(baseEnv.MINDSPACE_USERDATA_PG_URL, pgDatabase).catch(() => {});
    if (mysqlUrl) await dropMysqlDatabase(baseEnv.DATABASE_URL, mysqlDatabase).catch(() => {});
    await fsp.rm(sandboxRoot, { recursive: true, force: true }).catch(() => {});
    throw error;
  }
}