<?php
/*************************************************************
# 076 License

Copyright (c) テクニカル諏訪子

Permission is hereby granted to any person obtaining a copy of the software
Little Beast (the "Software") to use, modify, merge, copy, publish, distribute,
sublicense, and/or sell copies of the Software, subject to the following conditions:

    1. **Origin Attribution**:
       - You must not misrepresent the origin of the Software; you must not claim
         you created the original Software.
       - If the Software is used in a product, you must either:
         a. Provide clear attribution in the product's documentation, user interface,
            or other visible areas, **OR**
         b. Pay the original developers a fee they specify in writing.
    2. **Usage Restriction**:
       - The Software, or any derivative works, dependencies, or libraries
         incorporating it, must not be used for censorship or to suppress freedom of
         speech, expression, or creativity. Prohibited uses include, but are not
         limited to:
         - Censorship of so-called "hate speech", visuals, non-mainstream opinions,
           ideas, or objective reality.
         - Tools or systems designed to restrict access to information or
           artistic works.
    3. **Notice Preservation**:
       - This license and the above copyright notice must remain intact in all copies
         of the source code.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */
// テスト用の偽物のPostgreSQLサーバー（MockServerが起動する）。
// 使い方：php pg-server.php ポート [証明書のPEM] [秘密鍵のPEM] [オプション（カンマ区切り）]
// SQLはメモリーのSQLiteで実行し、結果をPostgreSQLのプロトコル3.0で返す（pdo_sqliteが必要）。
// 証明書と秘密鍵を渡すと、TLS（SSLRequest）とSCRAM-SHA-256-PLUS（tls-server-end-point）に対応する。
// オプション：nocb（TLSでもSCRAM-SHA-256-PLUSを出さない）、badsig（SCRAMのサーバーの署名を間違える）、
//            badnonce（SCRAMのnonceを変える）、nofinal（SCRAMのSASLFinalを送らずにAuthenticationOk）
//
// ユーザー（パスワード、認証）：
//   lb（ひみつ、SCRAM-SHA-256）、md5user（md5ひみつ、MD5）、clear（clearひみつ、パスワードをそのまま）、
//   trust（なし）、tlsonly（tlsひみつ、SCRAM-SHA-256、TLSのみ）、gss（GSSAPI＝未対応）
// データベース：「nodb」以外は何でも存在する事にする
// 偽物なので、Describe（ステートメント）は列を返さない（NoData）。列はポータルのDescribeで返す。
// 「$1::型」・INSERTの列・「列 = $1」からパラメータの型を決める（無ければtext）。
// 特別なSQL：
//   SELECT lb_disconnect()  返事をせずに接続を切る
//   SELECT lb_fatal()       FATALのエラーを送って切る
//   SELECT lb_sleep(秒)     返事を遅らせる
//   SELECT lb_notice()      NOTICEとWARNINGを送る
//   SELECT lb_async()       結果の途中にNotificationResponseとParameterStatusを送る
//   SELECT lb_types()       色々な型の列（本物のPostgreSQL 16の出力と同じ）
//   lb_tls()・lb_auth()・lb_cb()（チャネルバインディングの種類）・current_user・current_database()・
//   pg_backend_pid()・version()
//   COPY … FROM STDIN、COPY … TO STDOUT
//   information_schema.tablesのtable_name（テーブルの一覧）、setval()（何もしない）

require_once __DIR__.'/../../../../autoload.php';

use Std\Lib\Crypto\X509;
use Std\Lib\Tls;

$port = (int)($argv[1] ?? 0);
$certFile = $argv[2] ?? '';
$keyFile = $argv[3] ?? '';
$options = array_filter(explode(',', $argv[4] ?? ''));

if (!class_exists('PDO') || !in_array('sqlite', PDO::getAvailableDrivers(), true)) {
  fwrite(STDOUT, "error pdo_sqlite\n");
  exit(1);
}

$server = @stream_socket_server("tcp://127.0.0.1:{$port}", $errno, $errstr);
if (!$server) {
  fwrite(STDOUT, "error {$errstr}\n");
  exit(1);
}

$pg = new MockPgsql($certFile, $keyFile, $options);
fwrite(STDOUT, "ready\n");
fflush(STDOUT);
$pg->run($server);

/** エラーを返す為の例外 */
class PgError extends \Exception {
  public function __construct(public string $sqlState, string $message, public string $detail = '', public string $severity = 'ERROR') {
    parent::__construct($message);
  }
}

class MockPgsql {
  private const VERSION = '16.0 (lbmock)';

  private const USERS = [
    'lb' => ['ひみつ', 'scram'],
    'md5user' => ['md5ひみつ', 'md5'],
    'clear' => ['clearひみつ', 'password'],
    'trust' => ['', 'trust'],
    'tlsonly' => ['tlsひみつ', 'scram'],
    'gss' => ['', 'gss'],
  ];

  // SQLiteの宣言された型 => OID
  private const DECL = [
    'BOOL' => 16, 'BYTEA' => 17, 'BIGINT' => 20, 'INT8' => 20, 'BIGSERIAL' => 20, 'SMALLINT' => 21, 'INT2' => 21,
    'INTEGER' => 23, 'INT' => 23, 'INT4' => 23, 'SERIAL' => 23, 'TEXT' => 25, 'JSON' => 114, 'REAL' => 700, 'FLOAT4' => 700,
    'DOUBLE' => 701, 'FLOAT8' => 701, 'FLOAT' => 701, 'VARCHAR' => 1043, 'DATE' => 1082, 'TIMESTAMP' => 1114,
    'TIMESTAMPTZ' => 1184, 'NUMERIC' => 1700, 'DECIMAL' => 1700, 'UUID' => 2950, 'JSONB' => 3802,
  ];

  // 本物のPostgreSQL 16（TimeZone = Asia/Tokyo）の出力（escはbytea_output = escapeの場合、big8はint8に入らない値）
  private const TYPES = [
    'i2' => [21, '-5'], 'i4' => [23, '42'], 'i8' => [20, '9223372036854775807'], 'num' => [1700, '12345678901234567890.5'],
    'f4' => [700, '2.5'], 'nan' => [701, 'NaN'], 'ninf' => [701, '-Infinity'], 'big' => [701, '1e+100'],
    't' => [16, 't'], 'f' => [16, 'f'], 'bin' => [17, '\\x00ff5c'], 'txts' => [1009, '{"a,b","c\\"d",NULL,"NULL",""," x "}'],
    'ints' => [1007, '{{1,2},{3,NULL}}'], 'ranged' => [1007, '[0:1]={1,2}'], 'bools' => [1000, '{t,f}'],
    'bins' => [1001, '{"\\\\x01"}'], 'floats' => [1022, '{1.5,NaN}'], 'j' => [114, '{"a": [1, "x"]}'], 'jb' => [3802, '{"b": 2}'],
    'd' => [1082, '2026-01-02'], 'ts' => [1184, '2026-01-02 03:04:05.123456+09'], 'u' => [2950, 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'],
    'o' => [26, '1259'], 'n' => [25, null], 'v' => [1043, '日本語'], 'i8s' => [20, '2147483648'], 'empty' => [1009, '{}'],
    'esc' => [17, '\\000\\377\\\\A'], 'big8' => [20, '99999999999999999999'],
  ];

  private \PDO $pdo;
  private array $conns = [];
  private int $nextId = 1000;
  private string $certDer = '';
  private string $certSigAlg = '';

  public function __construct(private string $certFile, private string $keyFile, private array $options) {
    $this->pdo = new \PDO('sqlite::memory:');
    $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    if ($certFile !== '') {
      $cert = X509::fromPem((string)file_get_contents($certFile));
      $this->certDer = $cert->der;
      $this->certSigAlg = $cert->sigAlg;
    }
  }

  public function run($server): void {
    while (true) {
      // TLSで既に復号されたデータが残っている接続を先に
      $pending = [];
      foreach ($this->conns as $id => $cn) {
        if ($cn['tls'] && Tls::pending($cn['sock'])) $pending[] = $id;
      }

      if (empty($pending)) {
        $r = [$server];
        foreach ($this->conns as $cn) $r[] = $cn['sock'];
        $w = $e = null;
        $n = @stream_select($r, $w, $e, 120);
        if ($n === false || $n === 0) return;

        foreach ($r as $s) {
          if ($s === $server) {
            $c = @stream_socket_accept($server, 5);
            if ($c) $this->accept($c);
            continue;
          }
          foreach ($this->conns as $id => $cn) {
            if ($cn['sock'] === $s) $pending[] = $id;
          }
        }
      }

      foreach ($pending as $id) {
        if (!isset($this->conns[$id])) continue;
        try {
          $res = $this->handle($id);
        } catch (PgError $e) {
          // 認証中のエラー等（FATAL）
          $this->error($id, $e);
          $res = false;
        }
        if ($res === 'quit') return;
        if ($res === false) $this->drop($id);
      }
    }
  }

  ///////////////
  // 接続・認証

  private function accept($c): void {
    stream_set_blocking($c, true);
    stream_set_timeout($c, 30);
    // 読み込みのバッファを無くす（SSLRequestと一緒にTLSのClientHelloまで読んでしまうと、TLSを開始出来ない）
    stream_set_read_buffer($c, 0);
    $id = $this->nextId++;
    $this->conns[$id] = [
      'sock' => $c, 'id' => $id, 'state' => 'new', 'tls' => false, 'user' => '', 'db' => '', 'params' => [],
      'auth' => '', 'cb' => '', 'scram' => null, 'salt' => '', 'txn' => 'I', 'skip' => false, 'stmts' => [], 'portals' => [],
    ];
  }

  private function drop(int $id): void {
    // 切れた接続のトランザクションはロールバック（SQLiteは1つなので）
    if ($this->conns[$id]['txn'] !== 'I' && $this->pdo->inTransaction()) $this->pdo->rollBack();
    @fclose($this->conns[$id]['sock']);
    unset($this->conns[$id]);
  }

  /**
   * 1つのメッセージを処理する
   *
   * @return bool|string  false＝接続を切る、'quit'＝サーバーを止める
   */
  private function handle(int $id): bool|string {
    $cn = &$this->conns[$id];

    if ($cn['state'] === 'new') {
      $head = $this->readRaw($id, 4);
      if ($head === null) return false;
      // MockServer::stop()の終了のリクエスト
      if ($head === 'QUIT') return 'quit';
      $len = unpack('N', $head)[1];
      if ($len < 8 || $len > 10000) return false;
      $body = $this->readRaw($id, $len - 4);
      if ($body === null) return false;
      return $this->startup($id, $body);
    }

    $msg = $this->recv($id);
    if ($msg === null) return false;
    [$type, $body] = $msg;
    if ($type === 'X') return false;

    if ($cn['state'] === 'auth') return $this->authMessage($id, $type, $body);
    return $this->message($id, $type, $body);
  }

  private function startup(int $id, string $body): bool {
    $cn = &$this->conns[$id];
    $code = unpack('N', $body)[1];

    if ($code === 80877103) { // SSLRequest
      if ($cn['tls'] || $this->certFile === '') {
        $this->raw($id, 'N');
        return true;
      }
      $this->raw($id, 'S');
      try {
        $cn['sock'] = Tls::accept($cn['sock'], ['cert' => $this->certFile, 'key' => $this->keyFile]);
      } catch (\Throwable $e) {
        return false;
      }
      $cn['tls'] = true;
      return true;
    }
    if ($code === 80877104) { // GSSENCRequest
      $this->raw($id, 'N');
      return true;
    }
    if ($code === 80877102) return false; // CancelRequest
    if ($code !== 196608) throw new PgError('0A000', 'unsupported frontend protocol '.($code >> 16).'.'.($code & 0xFFFF), '', 'FATAL');

    $kv = explode("\0", substr($body, 4));
    for ($i = 0; $i + 1 < count($kv) && $kv[$i] !== ''; $i += 2) $cn['params'][$kv[$i]] = $kv[$i + 1];
    $user = $cn['params']['user'] ?? '';
    if ($user === '') throw new PgError('28000', 'no PostgreSQL user name specified in startup packet', '', 'FATAL');
    $cn['user'] = $user;
    $cn['db'] = $cn['params']['database'] ?? $user;

    [$pw, $method] = self::USERS[$user] ?? ['', 'scram'];
    if ($user === 'tlsonly' && !$cn['tls']) {
      throw new PgError('28000', "no pg_hba.conf entry for host \"127.0.0.1\", user \"{$user}\", database \"{$cn['db']}\", no encryption", '', 'FATAL');
    }
    $cn['auth'] = $method;
    $cn['state'] = 'auth';

    switch ($method) {
      case 'trust':
        $this->ready($id);
        return true;
      case 'password':
        $this->send($id, 'R', pack('N', 3));
        return true;
      case 'md5':
        $cn['salt'] = random_bytes(4);
        $this->send($id, 'R', pack('N', 5).$cn['salt']);
        return true;
      case 'gss':
        $this->send($id, 'R', pack('N', 7));
        return true;
    }
    $plus = $cn['tls'] && !in_array('nocb', $this->options, true);
    $this->send($id, 'R', pack('N', 10).($plus ? "SCRAM-SHA-256-PLUS\0" : '')."SCRAM-SHA-256\0\0");
    return true;
  }

  /** 認証中のメッセージ（PasswordMessage、SASLInitialResponse、SASLResponse） */
  private function authMessage(int $id, string $type, string $body): bool {
    $cn = &$this->conns[$id];
    if ($type !== 'p') throw new PgError('08P01', "invalid frontend message type {$type}", '', 'FATAL');
    $user = $cn['user'];
    $pw = self::USERS[$user][0] ?? null;
    $failed = new PgError('28P01', "password authentication failed for user \"{$user}\"", '', 'FATAL');

    if ($cn['auth'] === 'password') {
      if ($pw === null || rtrim($body, "\0") !== $pw) throw $failed;
      $this->ready($id);
      return true;
    }
    if ($cn['auth'] === 'md5') {
      if ($pw === null || rtrim($body, "\0") !== 'md5'.md5(md5($pw.$user).$cn['salt'])) throw $failed;
      $this->ready($id);
      return true;
    }

    if ($cn['scram'] === null) {
      // SASLInitialResponse
      $end = strpos($body, "\0");
      $mech = substr($body, 0, $end);
      $len = unpack('N', $body, $end + 1)[1];
      $data = substr($body, $end + 5, $len);
      $plusOffered = $cn['tls'] && !in_array('nocb', $this->options, true);
      if (!preg_match('/^([pny])(?:=([\w-]+))?,,(n=[^,]*,r=([^,]+))$/', $data, $m)) {
        throw new PgError('08P01', 'malformed SCRAM message', '', 'FATAL');
      }
      [, $flag, $cbType, $first, $nonce] = $m + ['', '', '', '', ''];
      if ($mech === 'SCRAM-SHA-256-PLUS') {
        if (!$plusOffered || $flag !== 'p' || $cbType !== 'tls-server-end-point') {
          throw new PgError('08P01', 'unsupported SCRAM channel-binding type', '', 'FATAL');
        }
      } else if ($mech === 'SCRAM-SHA-256') {
        if ($flag === 'p') throw new PgError('08P01', 'channel binding not supported with SCRAM-SHA-256', '', 'FATAL');
        if ($flag === 'y' && $plusOffered) {
          throw new PgError('08P01', 'SCRAM channel binding negotiation error', 'The client supports SCRAM channel binding but thinks the server does not.  However, this server does support channel binding.', 'FATAL');
        }
      } else {
        throw new PgError('08P01', 'client selected an invalid SASL authentication mechanism', '', 'FATAL');
      }
      $cn['cb'] = $flag === 'p' ? $cbType : ($flag === 'y' ? 'y' : 'none');
      $salt = random_bytes(16);
      $snonce = $nonce.rtrim(base64_encode(random_bytes(18)), '=');
      if (in_array('badnonce', $this->options, true)) $snonce = 'x'.substr($snonce, 1);
      $serverFirst = 'r='.$snonce.',s='.base64_encode($salt).',i=4096';
      $cn['scram'] = ['gs2' => substr($data, 0, strlen($data) - strlen($first)), 'first' => $first, 'nonce' => $snonce,
                      'server' => $serverFirst, 'salt' => $salt];
      $this->send($id, 'R', pack('N', 11).$serverFirst);
      return true;
    }

    // SASLResponse（client-final-message）
    $s = $cn['scram'];
    if (!preg_match('/^c=([^,]*),r=([^,]*),p=([^,]*)$/', $body, $m)) throw new PgError('08P01', 'malformed SCRAM message', '', 'FATAL');
    $cbData = $cn['cb'] === 'tls-server-end-point' ? $this->channelBinding() : '';
    if (base64_decode($m[1], true) !== $s['gs2'].$cbData) {
      throw new PgError('08P01', 'SCRAM channel binding check failed', '', 'FATAL');
    }
    if ($m[2] !== $s['nonce']) throw new PgError('08P01', 'SCRAM nonce mismatch', '', 'FATAL');
    if ($pw === null) throw $failed;

    $salted = hash_pbkdf2('sha256', $pw, $s['salt'], 4096, 32, true);
    $clientKey = hash_hmac('sha256', 'Client Key', $salted, true);
    $authMessage = $s['first'].','.$s['server'].',c='.$m[1].',r='.$m[2];
    $proof = (string)base64_decode($m[3], true);
    $recovered = $proof ^ hash_hmac('sha256', $authMessage, hash('sha256', $clientKey, true), true);
    if (strlen($proof) !== 32 || !hash_equals(hash('sha256', $clientKey, true), hash('sha256', $recovered, true))) throw $failed;

    $sig = hash_hmac('sha256', $authMessage, hash_hmac('sha256', 'Server Key', $salted, true), true);
    if (in_array('badsig', $this->options, true)) $sig = ~$sig;
    if (!in_array('nofinal', $this->options, true)) $this->send($id, 'R', pack('N', 12).'v='.base64_encode($sig));
    $this->ready($id);
    return true;
  }

  /** サーバーの証明書のハッシュ（RFC 5929） */
  private function channelBinding(): string {
    $hash = match ($this->certSigAlg) {
      '1.2.840.113549.1.1.12', '1.2.840.10045.4.3.3' => 'sha384',
      '1.2.840.113549.1.1.13', '1.2.840.10045.4.3.4' => 'sha512',
      default => 'sha256',
    };
    return hash($hash, $this->certDer, true);
  }

  /** 認証の完了：AuthenticationOk、ParameterStatus、BackendKeyData、ReadyForQuery */
  private function ready(int $id): void {
    $cn = &$this->conns[$id];
    if ($cn['db'] === 'nodb') throw new PgError('3D000', "database \"{$cn['db']}\" does not exist", '', 'FATAL');
    $this->send($id, 'R', pack('N', 0));
    $status = [
      'application_name' => $cn['params']['application_name'] ?? '', 'client_encoding' => $cn['params']['client_encoding'] ?? 'UTF8',
      'DateStyle' => $cn['params']['DateStyle'] ?? 'ISO, MDY', 'integer_datetimes' => 'on', 'IntervalStyle' => 'postgres',
      'is_superuser' => 'off', 'server_encoding' => 'UTF8', 'server_version' => self::VERSION,
      'session_authorization' => $cn['user'], 'standard_conforming_strings' => 'on', 'TimeZone' => 'Asia/Tokyo',
    ];
    foreach ($status as $k => $v) $this->send($id, 'S', "{$k}\0{$v}\0");
    $this->send($id, 'K', pack('NN', $id, random_int(0, 0x7FFFFFFF)));
    $cn['state'] = 'ready';
    $this->send($id, 'Z', 'I');
  }

  ///////////////
  // クエリ

  private function message(int $id, string $type, string $body): bool {
    $cn = &$this->conns[$id];
    // 拡張クエリのエラーの後はSyncまで無視する
    if ($cn['skip'] && $type !== 'S') return true;

    try {
      switch ($type) {
        case 'Q':
          return $this->simple($id, rtrim($body, "\0"));
        case 'P':
          $this->parse($id, $body);
          return true;
        case 'B':
          $this->bind($id, $body);
          return true;
        case 'D':
          $this->describe($id, $body[0], substr($body, 1, -1));
          return true;
        case 'E':
          $this->executePortal($id, rtrim(substr($body, 0, -4), "\0"));
          return true;
        case 'C':
          if ($body[0] === 'S') {
            unset($cn['stmts'][substr($body, 1, -1)]);
          } else {
            unset($cn['portals'][substr($body, 1, -1)]);
          }
          $this->send($id, '3', '');
          return true;
        case 'S':
          $cn['skip'] = false;
          $cn['portals'] = [];
          $this->send($id, 'Z', $cn['txn']);
          return true;
        case 'H': // Flush
          return true;
      }
      throw new PgError('08P01', "invalid frontend message type {$type}", '', 'FATAL');
    } catch (PgError $e) {
      if ($e->severity === 'FATAL') throw $e;
      $this->error($id, $e);
      $cn['skip'] = true;
      return true;
    }
  }

  /** 単純クエリ */
  private function simple(int $id, string $sql): bool {
    $cn = &$this->conns[$id];
    $stmts = array_values(array_filter(self::split($sql), fn($s) => trim($s) !== ''));
    if (empty($stmts)) {
      $this->send($id, 'I', '');
      $this->send($id, 'Z', $cn['txn']);
      return true;
    }

    foreach ($stmts as $one) {
      try {
        $res = $this->statement($id, trim($one), []);
      } catch (PgError $e) {
        if ($e->severity === 'FATAL') throw $e;
        $this->error($id, $e);
        break;
      }
      if ($res === false) return false;
      if ($res['cols'] !== null) $this->send($id, 'T', $this->rowDescription($res['cols']));
      foreach ($res['rows'] as $row) $this->send($id, 'D', $this->dataRow($row));
      $this->send($id, 'C', $res['tag']."\0");
    }
    $this->send($id, 'Z', $cn['txn']);
    return true;
  }

  private function parse(int $id, string $body): void {
    $cn = &$this->conns[$id];
    $p = 0;
    $name = self::cstr($body, $p);
    $sql = self::cstr($body, $p);
    $n = unpack('n', $body, $p)[1];
    $given = $n > 0 ? array_values(unpack("N{$n}", $body, $p + 2)) : [];

    if ($name !== '' && isset($cn['stmts'][$name])) throw new PgError('42P05', "prepared statement \"{$name}\" already exists");
    if (count(array_filter(self::split($sql), fn($s) => trim($s) !== '')) > 1) {
      throw new PgError('42601', 'cannot insert multiple commands into a prepared statement');
    }
    $sql = trim(rtrim(trim($sql), ';'));
    $types = $this->paramTypes($sql);
    foreach ($given as $i => $oid) if ($oid !== 0) $types[$i] = $oid;
    // 構文・テーブルの確認（特別なSQLは実行の時に）
    if (!preg_match('/^(BEGIN|START|COMMIT|END|ROLLBACK|SET|SHOW|COPY)\b|lb_\w+\(|setval\(|information_schema\./i', $sql)) {
      try {
        $this->pdo->prepare($this->rewrite($id, $sql));
      } catch (\PDOException $e) {
        throw $this->sqlError($e);
      }
    }
    $cn['stmts'][$name] = ['sql' => $sql, 'types' => $types];
    $this->send($id, '1', '');
  }

  private function bind(int $id, string $body): void {
    $cn = &$this->conns[$id];
    $p = 0;
    $portal = self::cstr($body, $p);
    $name = self::cstr($body, $p);
    if (!isset($cn['stmts'][$name])) throw new PgError('26000', "prepared statement \"{$name}\" does not exist");
    $stmt = $cn['stmts'][$name];

    $nf = unpack('n', $body, $p)[1];
    $formats = $nf > 0 ? array_values(unpack("n{$nf}", $body, $p + 2)) : [];
    $p += 2 + 2 * $nf;
    $np = unpack('n', $body, $p)[1];
    $p += 2;
    if ($np !== count($stmt['types'])) {
      throw new PgError('08P01', "bind message supplies {$np} parameters, but prepared statement \"{$name}\" requires ".count($stmt['types']));
    }
    $params = [];
    for ($i = 0; $i < $np; $i++) {
      $len = unpack('N', $body, $p)[1];
      $p += 4;
      $format = $formats[$nf === 1 ? 0 : $i] ?? 0;
      if ($len === 0xFFFFFFFF) {
        $params[] = null;
        continue;
      }
      $params[] = $this->decodeParam(substr($body, $p, $len), $format, $stmt['types'][$i]);
      $p += $len;
    }
    $nr = unpack('n', $body, $p)[1];
    if ($nr > 0 && in_array(1, array_values(unpack("n{$nr}", $body, $p + 2)), true)) {
      throw new PgError('0A000', 'binary result format is not supported by lbmock');
    }

    $res = $this->statement($id, $stmt['sql'], $params);
    if ($res === false) throw new PgError('57P01', 'terminating connection', '', 'FATAL');
    $cn['portals'][$portal] = $res;
    $this->send($id, '2', '');
  }

  private function describe(int $id, string $kind, string $name): void {
    $cn = &$this->conns[$id];
    if ($kind === 'S') {
      if (!isset($cn['stmts'][$name])) throw new PgError('26000', "prepared statement \"{$name}\" does not exist");
      $types = $cn['stmts'][$name]['types'];
      $this->send($id, 't', pack('n', count($types)).(empty($types) ? '' : pack('N*', ...$types)));
      $this->send($id, 'n', '');
      return;
    }
    if (!isset($cn['portals'][$name])) throw new PgError('34000', "portal \"{$name}\" does not exist");
    $cols = $cn['portals'][$name]['cols'];
    $this->send($id, $cols === null ? 'n' : 'T', $cols === null ? '' : $this->rowDescription($cols));
  }

  private function executePortal(int $id, string $name): void {
    $cn = &$this->conns[$id];
    if (!isset($cn['portals'][$name])) throw new PgError('34000', "portal \"{$name}\" does not exist");
    $res = $cn['portals'][$name];
    foreach ($res['rows'] as $row) $this->send($id, 'D', $this->dataRow($row));
    $this->send($id, 'C', $res['tag']."\0");
  }

  /**
   * 1つの文を実行する
   *
   * @return array|false  cols（nullなら行なし）、rows（テキストの値）、tag、又は接続を切る場合false
   * @throws PgError
   */
  private function statement(int $id, string $sql, array $params): array|false {
    $cn = &$this->conns[$id];
    $sql = rtrim(trim($sql), ';');
    $none = fn(string $tag): array => ['cols' => null, 'rows' => [], 'tag' => $tag];
    $one = fn(string $name, int $oid, ?string $v): array => ['cols' => [[$name, $oid]], 'rows' => [[$v]], 'tag' => 'SELECT 1'];

    if (preg_match('/^(BEGIN|START\s+TRANSACTION)$/i', $sql)) {
      if ($cn['txn'] !== 'I') {
        $this->notice($id, 'WARNING', '25001', 'there is already a transaction in progress');
      } else {
        $this->pdo->beginTransaction();
        $cn['txn'] = 'T';
      }
      return $none('BEGIN');
    }
    if (preg_match('/^(COMMIT|END|ROLLBACK)$/i', $sql, $m)) {
      if ($cn['txn'] === 'I') {
        $this->notice($id, 'WARNING', '25P01', 'there is no transaction in progress');
        return $none(strtoupper($m[1]) === 'ROLLBACK' ? 'ROLLBACK' : 'COMMIT');
      }
      $commit = strtoupper($m[1]) !== 'ROLLBACK' && $cn['txn'] === 'T';
      $commit ? $this->pdo->commit() : $this->pdo->rollBack();
      $cn['txn'] = 'I';
      return $none($commit ? 'COMMIT' : 'ROLLBACK');
    }
    if ($cn['txn'] === 'E') throw new PgError('25P02', 'current transaction is aborted, commands ignored until end of transaction block');

    try {
      if (preg_match('/^SET\s+(?:SESSION\s+)?(\w+)\s*(?:=|TO)\s*\'?([^\']*)\'?$/i', $sql, $m)) {
        $key = ['application_name' => 'application_name', 'client_encoding' => 'client_encoding', 'datestyle' => 'DateStyle', 'timezone' => 'TimeZone'][strtolower($m[1])] ?? null;
        if ($key !== null) {
          $cn['params'][$key] = $m[2];
          $this->send($id, 'S', "{$key}\0{$m[2]}\0");
        }
        return $none('SET');
      }
      if (preg_match('/^SHOW\s+(\w+)$/i', $sql, $m)) {
        $v = ['server_version' => self::VERSION, 'client_encoding' => $cn['params']['client_encoding'] ?? 'UTF8'][strtolower($m[1])] ?? '';
        return $one(strtolower($m[1]), 25, $v);
      }
      if (preg_match('/^COPY\s.*\sFROM\s+STDIN/is', $sql)) return $this->copyIn($id);
      if (preg_match('/^COPY\s.*\sTO\s+STDOUT/is', $sql)) {
        $this->send($id, 'H', "\0".pack('nn', 1, 0));
        $this->send($id, 'd', "1\t一\n");
        $this->send($id, 'c', '');
        return $none('COPY 1');
      }
      if (preg_match('/^SELECT\s+table_name\s+FROM\s+information_schema\.tables\b/i', $sql)) {
        $names = $this->pdo->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name")->fetchAll(\PDO::FETCH_COLUMN);
        return ['cols' => [['table_name', 25]], 'rows' => array_map(fn($n) => [$n], $names), 'tag' => 'SELECT '.count($names)];
      }
      if (preg_match('/^SELECT\s+setval\(/i', $sql)) return $one('setval', 20, '1');
      if (preg_match('/^SELECT\s+lb_disconnect\(\)$/i', $sql)) return false;
      if (preg_match('/^SELECT\s+lb_fatal\(\)$/i', $sql)) throw new PgError('57P01', 'terminating connection due to administrator command', '', 'FATAL');
      if (preg_match('/^SELECT\s+lb_notice\(\)$/i', $sql)) {
        $this->notice($id, 'NOTICE', '00000', 'やあ');
        $this->notice($id, 'WARNING', '01000', 'w');
        return $one('lb_notice', 2278, '');
      }
      if (preg_match('/^SELECT\s+lb_async\(\)$/i', $sql)) {
        $this->send($id, 'A', pack('N', $id)."lbchan\0hi\0");
        $this->send($id, 'S', "TimeZone\0UTC\0");
        return $one('lb_async', 25, 'ok');
      }
      if (preg_match('/^SELECT\s+lb_types\(\)$/i', $sql)) {
        $cols = [];
        foreach (self::TYPES as $k => [$oid]) $cols[] = [$k, $oid];
        return ['cols' => $cols, 'rows' => [array_column(array_values(self::TYPES), 1)], 'tag' => 'SELECT 1'];
      }
      if (preg_match('/lb_sleep\(([\d.]+)\)/i', $sql, $m)) {
        usleep((int)((float)$m[1] * 1000000));
        $sql = preg_replace('/lb_sleep\([\d.]+\)/i', "''", $sql);
      }
      if (preg_match('/^DROP\s+TABLE\s+IF\s+EXISTS\s+(\w+)$/i', $sql, $m)) {
        if (!$this->columns($m[1])) {
          $this->notice($id, 'NOTICE', '00000', "table \"{$m[1]}\" does not exist, skipping");
          return $none('DROP TABLE');
        }
      }

      $st = $this->pdo->prepare($this->rewrite($id, $sql));
      foreach ($params as $i => $v) {
        $type = match (true) {
          $v === null => \PDO::PARAM_NULL,
          is_int($v) => \PDO::PARAM_INT,
          $v instanceof Blob => \PDO::PARAM_LOB,
          default => \PDO::PARAM_STR,
        };
        $st->bindValue(':p'.($i + 1), $v instanceof Blob ? $v->data : $v, $type);
      }
      $st->execute();
      $rows = $st->columnCount() > 0 ? $st->fetchAll(\PDO::FETCH_NUM) : [];
    } catch (\PDOException $e) {
      if ($cn['txn'] === 'T') $cn['txn'] = 'E';
      throw $this->sqlError($e);
    } catch (PgError $e) {
      if ($cn['txn'] === 'T') $cn['txn'] = 'E';
      throw $e;
    }

    $word = strtoupper((string)strtok($sql, " \t\r\n("));
    $count = $st->columnCount() > 0 ? count($rows) : $st->rowCount();
    $tag = match ($word) {
      'SELECT', 'VALUES', 'WITH' => "SELECT {$count}",
      'INSERT' => "INSERT 0 {$count}",
      'UPDATE', 'DELETE' => "{$word} {$count}",
      'CREATE', 'DROP', 'ALTER' => $word.' '.strtoupper(preg_split('/\s+/', $sql)[1] ?? ''),
      default => $word,
    };
    if ($st->columnCount() === 0) return $none($tag);

    // 列の型
    $cols = [];
    for ($i = 0; $i < $st->columnCount(); $i++) {
      $meta = $st->getColumnMeta($i) ?: [];
      $decl = strtoupper(preg_replace('/\(.*$/', '', (string)($meta['sqlite:decl_type'] ?? '')));
      $oid = self::DECL[$decl] ?? (str_starts_with($decl, 'BOOL') ? 16 : null);
      if ($oid === null && $decl !== '') $oid = 25;
      if ($oid === null) {
        // 式の列：値から
        $oid = 25;
        foreach ($rows as $r) {
          if ($r[$i] === null) continue;
          $oid = is_int($r[$i]) ? ($r[$i] >= -2147483648 && $r[$i] <= 2147483647 ? 23 : 20) : (is_float($r[$i]) ? 701 : 25);
          break;
        }
      }
      $cols[] = [(string)($meta['name'] ?? "?column?"), $oid];
    }
    $out = [];
    foreach ($rows as $r) {
      $line = [];
      foreach ($r as $i => $v) $line[] = $this->textValue($v, $cols[$i][1]);
      $out[] = $line;
    }
    return ['cols' => $cols, 'rows' => $out, 'tag' => $tag];
  }

  /** COPY FROM STDIN：CopyData・CopyDone・CopyFailを待つ */
  private function copyIn(int $id): array {
    $this->send($id, 'G', "\0".pack('nn', 1, 0));
    $n = 0;
    while (true) {
      $msg = $this->recv($id);
      if ($msg === null) throw new PgError('08006', 'unexpected EOF on client connection with an open transaction', '', 'FATAL');
      [$type, $body] = $msg;
      if ($type === 'd') {
        $n += substr_count($body, "\n");
      } else if ($type === 'c') {
        return ['cols' => null, 'rows' => [], 'tag' => "COPY {$n}"];
      } else if ($type === 'f') {
        throw new PgError('57014', 'COPY from stdin failed: '.rtrim($body, "\0"));
      } else if ($type !== 'H' && $type !== 'S') {
        throw new PgError('08P01', "unexpected message type 0x".bin2hex($type)." during COPY from stdin");
      }
    }
  }

  ///////////////
  // SQLite ⇔ PostgreSQL

  /** PostgreSQLの書き方をSQLiteにする、特別な関数を値にする */
  private function rewrite(int $id, string $sql): string {
    $cn = $this->conns[$id];
    $q = fn(string $s): string => $this->pdo->quote($s);
    $sql = preg_replace('/\b(?:BIG)?SERIAL\s+PRIMARY\s+KEY\b/i', 'INTEGER PRIMARY KEY AUTOINCREMENT', $sql);

    $sql = strtr($sql, [
      'current_database()' => $q($cn['db']),
      'current_user' => $q($cn['user']),
      'pg_backend_pid()' => (string)$cn['id'],
      'version()' => $q('PostgreSQL '.self::VERSION),
      'lb_tls()' => $cn['tls'] ? '1' : '0',
      'lb_auth()' => $q($cn['auth']),
      'lb_cb()' => $q($cn['cb']),
    ]);

    // 「$1」→「:p1」、「値::型」→「CAST(値 AS 型)」（文字列・識別子の中は変えない）
    return preg_replace_callback('/((?:\$\d+|\'(?:[^\']|\'\')*\'|\w+)(?:::\w+(?:\[\])?)+)|\'(?:[^\']|\'\')*\'|"(?:[^"]|"")*"|\$(\d+)/', function(array $m): string {
      if (isset($m[2]) && $m[2] !== '') return ':p'.$m[2];
      if (!isset($m[1]) || $m[1] === '') return $m[0];
      $parts = explode('::', $m[1]);
      $v = array_shift($parts);
      if (preg_match('/^\$(\d+)$/', $v, $p)) $v = ':p'.$p[1];
      foreach ($parts as $t) {
        $t = strtoupper($t);
        $aff = match (true) {
          str_contains($t, 'INT') || str_starts_with($t, 'BOOL') => 'INTEGER',
          str_starts_with($t, 'FLOAT') || in_array($t, ['REAL', 'DOUBLE', 'NUMERIC', 'DECIMAL'], true) => 'REAL',
          $t === 'BYTEA' => 'BLOB',
          default => 'TEXT',
        };
        $v = "CAST({$v} AS {$aff})";
      }
      return $v;
    }, $sql);
  }

  /** パラメータの型（「$1::型」、INSERTの列、「列 = $1」から、無ければtext） */
  private function paramTypes(string $sql): array {
    preg_match_all('/\$(\d+)/', preg_replace('/\'(?:[^\']|\'\')*\'/', "''", $sql), $all);
    $n = empty($all[1]) ? 0 : max(array_map('intval', $all[1]));
    $types = array_fill(0, $n, 25);
    $toOid = fn(string $t): int => self::DECL[strtoupper($t)] ?? (str_starts_with(strtoupper($t), 'BOOL') ? 16 : 25);

    // テーブルの列
    $cols = [];
    if (preg_match_all('/\b(?:FROM|INTO|UPDATE|JOIN)\s+"?(\w+)"?/i', $sql, $t)) {
      foreach ($t[1] as $table) $cols += $this->columns($table);
    }
    // INSERT INTO t (列, …) VALUES (…), (…)：「"」の名前、複数の行も
    if (preg_match('/INSERT\s+INTO\s+"?\w+"?\s*\(([^)]*)\)\s*VALUES\s*(.*)$/is', $sql, $m)) {
      $names = array_map(fn($n) => strtolower(trim($n, " \t\n\"")), explode(',', $m[1]));
      preg_match_all('/\(([^()]*)\)/', $m[2], $tuples);
      foreach ($tuples[1] as $tuple) {
        foreach (array_map('trim', explode(',', $tuple)) as $i => $v) {
          if (preg_match('/^\$(\d+)$/', $v, $p) && isset($cols[$names[$i] ?? ''])) {
            $types[(int)$p[1] - 1] = $toOid($cols[$names[$i]]);
          }
        }
      }
    }
    if (preg_match_all('/(\w+)\s*(?:=|<>|!=|<=|>=|<|>)\s*\$(\d+)(?![\d:])/', $sql, $mm, PREG_SET_ORDER)) {
      foreach ($mm as $m) {
        if (isset($cols[strtolower($m[1])])) $types[(int)$m[2] - 1] = $toOid($cols[strtolower($m[1])]);
      }
    }
    if (preg_match_all('/\$(\d+)::(\w+)(\[\])?/', $sql, $mm, PREG_SET_ORDER)) {
      foreach ($mm as $m) $types[(int)$m[1] - 1] = empty($m[3]) ? $toOid($m[2]) : 1009;
    }
    return $types;
  }

  /** テーブルの列 => 宣言された型（小文字の名前） */
  private function columns(string $table): array {
    $out = [];
    try {
      foreach ($this->pdo->query('PRAGMA table_info('.$this->pdo->quote($table).')')->fetchAll(\PDO::FETCH_ASSOC) as $c) {
        $out[strtolower($c['name'])] = preg_replace('/\(.*$/', '', $c['type']);
      }
    } catch (\PDOException $e) {
      // 無い
    }
    return $out;
  }

  /** パラメータ（テキスト又はバイナリ）をSQLiteの値にする */
  private function decodeParam(string $v, int $format, int $oid): mixed {
    if ($format === 1) {
      if ($oid !== 17) throw new PgError('0A000', "binary format for type {$oid} is not supported by lbmock");
      return new Blob($v);
    }
    switch ($oid) {
      case 16:
        $b = strtolower(trim($v));
        if (in_array($b, ['t', 'true', 'y', 'yes', 'on', '1'], true)) return 1;
        if (in_array($b, ['f', 'false', 'n', 'no', 'off', '0'], true)) return 0;
        throw new PgError('22P02', "invalid input syntax for type boolean: \"{$v}\"");
      case 20:
      case 21:
      case 23:
        if (!preg_match('/^\s*[+-]?\d+\s*$/', $v)) throw new PgError('22P02', "invalid input syntax for type integer: \"{$v}\"");
        return (int)$v;
      case 700:
      case 701:
        if (!is_numeric($v) && !in_array($v, ['NaN', 'Infinity', '-Infinity'], true)) {
          throw new PgError('22P02', "invalid input syntax for type double precision: \"{$v}\"");
        }
        return is_numeric($v) ? (float)$v : $v;
    }
    // テキストはUTF-8のみ（0x00も不可）
    if (!mb_check_encoding($v, 'UTF-8') || str_contains($v, "\0")) {
      throw new PgError('22021', 'invalid byte sequence for encoding "UTF8"');
    }
    if ($oid === 17) {
      // テキスト形式のbytea（\x…、又はescape形式）
      if (str_starts_with($v, '\\x')) return new Blob((string)hex2bin(substr($v, 2)));
      $bad = false;
      $out = preg_replace_callback('/\\\\(\\\\|[0-3][0-7]{2}|.?)/s', function(array $m) use (&$bad): string {
        if ($m[1] === '\\') return '\\';
        if (strlen($m[1]) === 3) return chr(octdec($m[1]));
        $bad = true;
        return '';
      }, $v);
      if ($bad) throw new PgError('22P02', 'invalid input syntax for type bytea');
      return new Blob($out);
    }
    return $v;
  }

  /** SQLiteの値をPostgreSQLのテキストにする */
  private function textValue(mixed $v, int $oid): ?string {
    if ($v === null) return null;
    return match ($oid) {
      16 => in_array($v, [1, '1', 't', 'true'], true) ? 't' : 'f',
      17 => '\\x'.bin2hex((string)$v),
      700, 701 => is_string($v) && !is_numeric($v) ? $v : self::floatText((float)$v),
      default => (string)$v,
    };
  }

  private static function floatText(float $f): string {
    if (is_nan($f)) return 'NaN';
    if (is_infinite($f)) return $f > 0 ? 'Infinity' : '-Infinity';
    return str_replace(['.0E', 'E'], ['e', 'e'], (string)$f);
  }

  private function sqlError(\PDOException $e): PgError {
    $msg = $e->getMessage();
    if (preg_match('/no such table: (\S+)/', $msg, $m)) return new PgError('42P01', "relation \"{$m[1]}\" does not exist");
    if (preg_match('/UNIQUE constraint failed: (\w+)\.(\w+)/', $msg, $m)) {
      return new PgError('23505', "duplicate key value violates unique constraint \"{$m[1]}_{$m[2]}_key\"", "Key ({$m[2]}) already exists.");
    }
    if (preg_match('/NOT NULL constraint failed: (\w+)\.(\w+)/', $msg, $m)) {
      return new PgError('23502', "null value in column \"{$m[2]}\" of relation \"{$m[1]}\" violates not-null constraint");
    }
    if (preg_match('/no such column: (\S+)/', $msg, $m)) return new PgError('42703', "column \"{$m[1]}\" does not exist");
    if (preg_match('/near "([^"]*)": syntax error/', $msg, $m)) return new PgError('42601', "syntax error at or near \"{$m[1]}\"");
    if (preg_match('/already exists/', $msg)) return new PgError('42P07', preg_replace('/^SQLSTATE\[\w+\]: [^:]*: \d+ /', '', $msg));
    return new PgError('XX000', preg_replace('/^SQLSTATE\[\w+\]: [^:]*: \d+ /', '', $msg));
  }

  /** セミコロンで文を分ける（文字列・識別子・$$・コメントの中は分けない） */
  private static function split(string $sql): array {
    $out = [];
    $cur = '';
    $len = strlen($sql);
    for ($i = 0; $i < $len; $i++) {
      $c = $sql[$i];
      if ($c === "'" || $c === '"') {
        $j = $i + 1;
        while ($j < $len && !($sql[$j] === $c && ($sql[$j + 1] ?? '') !== $c)) $j += $sql[$j] === $c ? 2 : 1;
        $cur .= substr($sql, $i, $j - $i + 1);
        $i = $j;
      } else if ($c === '$' && preg_match('/\G\$(\w*)\$/', $sql, $m, 0, $i) && !ctype_digit($m[1])) {
        $end = strpos($sql, $m[0], $i + strlen($m[0]));
        $j = $end === false ? $len : $end + strlen($m[0]);
        $cur .= substr($sql, $i, $j - $i);
        $i = $j - 1;
      } else if ($c === '-' && ($sql[$i + 1] ?? '') === '-') {
        $j = strpos($sql, "\n", $i);
        $i = $j === false ? $len : $j;
      } else if ($c === ';') {
        $out[] = $cur;
        $cur = '';
      } else {
        $cur .= $c;
      }
    }
    $out[] = $cur;
    return $out;
  }

  ///////////////
  // メッセージ

  private function rowDescription(array $cols): string {
    $b = pack('n', count($cols));
    foreach ($cols as [$name, $oid]) {
      $size = [16 => 1, 20 => 8, 21 => 2, 23 => 4, 700 => 4, 701 => 8, 1082 => 4, 1114 => 8, 1184 => 8][$oid] ?? -1;
      $b .= $name."\0".pack('NnNnNn', 0, 0, $oid, $size & 0xFFFF, 0xFFFFFFFF, 0);
    }
    return $b;
  }

  private function dataRow(array $row): string {
    $b = pack('n', count($row));
    foreach ($row as $v) $b .= $v === null ? pack('N', 0xFFFFFFFF) : pack('N', strlen($v)).$v;
    return $b;
  }

  private function notice(int $id, string $severity, string $code, string $msg): void {
    $this->send($id, 'N', "S{$severity}\0V{$severity}\0C{$code}\0M{$msg}\0\0");
  }

  private function error(int $id, PgError $e): void {
    $b = "S{$e->severity}\0V{$e->severity}\0C{$e->sqlState}\0M{$e->getMessage()}\0";
    if ($e->detail !== '') $b .= "D{$e->detail}\0";
    $this->send($id, 'E', $b."\0");
  }

  private static function cstr(string $b, int &$pos): string {
    $end = strpos($b, "\0", $pos);
    $s = substr($b, $pos, $end - $pos);
    $pos = $end + 1;
    return $s;
  }

  private function send(int $id, string $type, string $body): void {
    $this->raw($id, $type.pack('N', strlen($body) + 4).$body);
  }

  private function raw(int $id, string $data): void {
    for ($w = 0; $w < strlen($data);) {
      $n = @fwrite($this->conns[$id]['sock'], substr($data, $w, 1048576));
      if ($n === false || $n === 0) return;
      $w += $n;
    }
  }

  private function readRaw(int $id, int $n): ?string {
    $buf = '';
    while (strlen($buf) < $n) {
      $x = @fread($this->conns[$id]['sock'], $n - strlen($buf));
      if ($x === false || $x === '') return null;
      $buf .= $x;
    }
    return $buf;
  }

  private function recv(int $id): ?array {
    $head = $this->readRaw($id, 5);
    if ($head === null) return null;
    $len = unpack('N', $head, 1)[1];
    if ($len < 4) return null;
    $body = $len > 4 ? $this->readRaw($id, $len - 4) : '';
    return $body === null ? null : [$head[0], $body];
  }
}

/** byteaの値（SQLiteにBLOBとして渡す） */
class Blob {
  public function __construct(public string $data) {}
}