<?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.
 */
namespace Std\Lib\Db;

use Std\Lib\Tls;
use Std\Lib\Tls\TlsException;

/**
 * PHP純正のPostgreSQLドライバ（プロトコル3.0）。pgsql・pdo_pgsql・opensslのモジュールが不要。
 *
 * 対応：TCP・UNIXソケット、TLS（Std\Lib\Tls）、認証（SCRAM-SHA-256、TLSならチャネルバインディング付きの
 * SCRAM-SHA-256-PLUS、MD5、パスワード、trust）、単純・拡張クエリ、複数の結果、トランザクション、通知（NOTICE）、
 * 型の変換（整数・浮動小数点数・真偽値・bytea・配列）
 * 未対応：COPY（データは無視する）、LISTEN・NOTIFYの待ち受け、GSSAPI・SSPI・Kerberos、取り消し要求
 *
 * プレースホルダは「?」（$1、$2…に変換する、「??」は「?」そのもの）又は「$1」。
 * PostgreSQLにはAUTO_INCREMENTのIDが無いので、insertIdは常に0（RETURNINGを使って下さい）。
 *
 * 普通はStd\Lib\Db経由で使う。直接使う場合：
 *   $pg = new Pgsql(['host' => '127.0.0.1', 'username' => 'lb', 'password' => 'ひみつ', 'dbname' => 'lb']);
 *   $pg->connect();
 *   $id = $pg->prepare('SELECT * FROM users WHERE id = ?');
 *   $res = $pg->execute($id, [1]); // $res['rows']
 *   $pg->demolish($id);
 */
class Pgsql implements DbInterface {
  // 型のOID
  public const OID_BOOL = 16;
  public const OID_BYTEA = 17;
  public const OID_INT8 = 20;
  public const OID_INT2 = 21;
  public const OID_INT4 = 23;
  public const OID_TEXT = 25;
  public const OID_OID = 26;
  public const OID_JSON = 114;
  public const OID_FLOAT4 = 700;
  public const OID_FLOAT8 = 701;
  public const OID_VARCHAR = 1043;
  public const OID_DATE = 1082;
  public const OID_TIMESTAMP = 1114;
  public const OID_TIMESTAMPTZ = 1184;
  public const OID_NUMERIC = 1700;
  public const OID_UUID = 2950;
  public const OID_JSONB = 3802;

  // 配列の型 => 要素の型
  private const ARRAYS = [
    1000 => 16, 1001 => 17, 1005 => 21, 1007 => 23, 1016 => 20, 1009 => 25, 1015 => 1043, 1014 => 1042,
    1021 => 700, 1022 => 701, 1231 => 1700, 1182 => 1082, 1115 => 1114, 1185 => 1184, 2951 => 2950, 199 => 114, 3807 => 3802,
  ];

  private const PROTOCOL = 196608; // 3.0
  private const SSL_REQUEST = 80877103;

  /** @var resource|null */
  private $socket = null;
  /** @var resource|null TLSの下のソケット（タイムアウトの確認用） */
  private $raw = null;
  private bool $connected = false;
  private bool $secure = false;
  private bool $debug = false;
  private array $packetLog = [];
  private string $out = '';
  public  string $username;
  private string $password;
  public  string $dbname;
  public  string $host;
  private int $port;
  private string $socketPath;
  private string $charset;
  private bool|array $tls;
  private string $channelBinding;
  private bool $allowCleartext;
  private int $timeout;

  private string $status = '';
  private array $params = [];
  private int $pid = 0;
  private string $authMethod = '';
  private array $notices = [];
  private array $prepared = [];
  private int $nextStmt = 1;

  /**
   * コンストラクタ
   *
   * 接続に必要な設定を初期化します。$infoを省略するとDBINFOを使います。
   *
   * @param array|null $info  host、port（5432）、socket（UNIXソケットのファイル又はディレクトリ）、username、password、dbname、
   *                          charset（UTF8）、tls（true＝証明書を確認するTLS、又はStd\Lib\Tlsのオプションの配列）、
   *                          channel_binding（prefer＝TLSなら使う、require、disable）、
   *                          allow_cleartext（TLSでないTCPでもパスワードをそのまま送る、既定false）、timeout（秒、10）、debug
   * @throws \Exception  $infoを省略し、DB_ENABLEDが無効な場合
   */
  public function __construct(?array $info = null) {
    if ($info === null && !(defined('DB_ENABLED') && DB_ENABLED)) {
      throw new \Exception('データベースは無効です。');
    }

    $info ??= DBINFO;
    $this->host = (string)($info['host'] ?? '127.0.0.1');
    $this->port = (int)($info['port'] ?? 5432);
    $this->socketPath = (string)($info['socket'] ?? '');
    $this->username = (string)($info['username'] ?? '');
    $this->password = (string)($info['password'] ?? '');
    $this->dbname = (string)($info['dbname'] ?? '');
    $this->charset = (string)($info['charset'] ?? 'UTF8');
    $this->tls = $info['tls'] ?? false;
    $this->channelBinding = strtolower((string)($info['channel_binding'] ?? 'prefer'));
    $this->allowCleartext = (bool)($info['allow_cleartext'] ?? false);
    $this->timeout = max(1, (int)($info['timeout'] ?? 10));
    $this->debug = (bool)($info['debug'] ?? false);

    if (!in_array($this->channelBinding, ['prefer', 'require', 'disable'], true)) {
      throw new \InvalidArgumentException("channel_bindingはprefer、require、disable： {$this->channelBinding}");
    }
    // PostgreSQLの「utf8mb4」は無いので、MySQLと同じ設定でも動く様に
    if (in_array(strtolower($this->charset), ['utf8mb4', 'utf8', 'utf-8'], true)) $this->charset = 'UTF8';
  }

  public function __destruct() {
    $this->close();
  }

  /**
   * デバッギングの有無（有効にすると送受信したメッセージを出力し、ログに残す）
   *
   * @param bool $debug
   * @return Pgsql
   */
  public function setDebug(bool $debug): Pgsql {
    $this->debug = $debug;
    return $this;
  }

  /**
   * メッセージのログ
   *
   * @return array  direction、type、length、data、timestamp
   */
  public function getPacketLog(): array {
    return $this->packetLog;
  }

  /**
   * サーバーの情報
   *
   * @return array  サーバーのパラメータ（server_version、client_encoding等）、pid、tls、authMethod
   */
  public function getServerInfo(): array {
    return $this->params + ['pid' => $this->pid, 'tls' => $this->secure && $this->socketPath === '', 'authMethod' => $this->authMethod];
  }

  /**
   * 最後のクエリの通知（NOTICE、WARNING等）
   *
   * @return array  ['severity' => …, 'code' => SQLSTATE, 'message' => …]の配列
   */
  public function getNotices(): array {
    return $this->notices;
  }

  /**
   * PostgreSQLサーバーに接続
   *
   * @return bool
   * @throws DbException  接続または認証に失敗した場合
   */
  public function connect(): bool {
    if ($this->connected) return true;

    $unix = $this->socketPath !== '';
    if ($unix) {
      $path = @is_dir($this->socketPath) ? rtrim($this->socketPath, '/')."/.s.PGSQL.{$this->port}" : $this->socketPath;
      $target = "unix://{$path}";
    } else {
      $target = 'tcp://'.(str_contains($this->host, ':') ? "[{$this->host}]" : $this->host).":{$this->port}";
    }
    $errno = 0;
    $errstr = '';
    $socket = @stream_socket_client($target, $errno, $errstr, $this->timeout, STREAM_CLIENT_CONNECT, stream_context_create());
    if ($socket === false) $this->fail("ソケットに接続に失敗: {$errstr} ({$errno})");
    stream_set_timeout($socket, $this->timeout);
    $this->socket = $this->raw = $socket;
    $this->secure = $unix;

    try {
      if ($this->tls !== false) $this->startTls();
      $this->startup();
      $this->connected = true;
    } catch (\Throwable $e) {
      $this->drop();
      throw $e;
    }
    return true;
  }

  public function close(): void {
    if ($this->socket && $this->connected) {
      try {
        $this->send('X', ''); // Terminate
        $this->flush();
      } catch (\Throwable $e) {
        // 既に切れている
      }
    }
    $this->drop();
  }

  public function isConnected(): bool {
    return $this->connected;
  }

  /**
   * 接続がまだ生きているか確認する（空のクエリ）
   *
   * @return bool
   */
  public function ping(): bool {
    if (!$this->connected) return false;
    try {
      $this->query('');
      return true;
    } catch (\Throwable $e) {
      return false;
    }
  }

  /**
   * SQLをそのまま実行する（単純クエリ、セミコロンで区切った複数の文も可）
   *
   * @param string $query  SQL
   * @return array  fields、rows、affectedRows、insertId（常に0）、warningCount、command（例：「INSERT 0 1」）（、more）
   * @throws DbException
   */
  public function query(string $query): array {
    $this->requireConnection();
    $this->notices = [];
    $this->send('Q', $query."\0");
    return $this->collect('クエリに失敗');
  }

  /**
   * プリペアドステートメントの準備
   *
   * @param string $query  プレースホルダ（「?」又は「$1」）付きのSQL
   * @return int  ステートメントID
   * @throws DbException
   */
  public function prepare(string $query): int {
    $this->requireConnection();
    $id = $this->nextStmt++;
    $name = "lb{$id}";

    $this->send('P', $name."\0".self::placeholders($query)."\0\0\0");
    $this->send('D', 'S'.$name."\0");
    $this->send('S', '');

    $params = [];
    $error = null;
    while (true) {
      [$type, $body] = $this->readMessage();
      switch ($type) {
        case '1': // ParseComplete
        case 'n': // NoData
        case 'T': // RowDescription（列は実行の時にポータルのDescribeで受け取る）
          break;
        case 't': // ParameterDescription
          $n = unpack('n', $body)[1];
          $params = $n > 0 ? array_values(unpack("N{$n}", $body, 2)) : [];
          break;
        case 'E':
          $error = $this->parseError($body);
          if ($this->fatal($error)) $this->failError('準備に失敗', $error);
          break;
        case 'Z':
          $this->status = $body;
          if ($error) $this->failError('準備に失敗', $error);
          $this->prepared[$id] = ['name' => $name, 'params' => $params];
          return $id;
        default:
          $this->other($type, $body);
      }
    }
  }

  /**
   * プリペアドステートメントの実行
   *
   * int・float・bool・null・文字列（bytea型には生のバイト）・DateTimeInterface・BackedEnumを送れます。
   *
   * @param int $statementId  ステートメントID
   * @param array $params  パラメータ（順番通り）
   * @return array  結果（query()と同じ形）
   * @throws DbException
   */
  public function execute(int $statementId, array $params = []): array {
    $this->requireConnection();
    if (!isset($this->prepared[$statementId])) $this->fail("不正なステートメントID: {$statementId}");

    $stmt = $this->prepared[$statementId];
    $params = array_values($params);
    $n = count($stmt['params']);
    if (count($params) !== $n) {
      $this->fail("パラメータ数が一致しません: 期待 {$n}, 取得 ".count($params));
    }

    $formats = '';
    $values = '';
    foreach ($params as $i => $v) {
      [$format, $data] = $this->encodeParam($v, $stmt['params'][$i], $i);
      $formats .= pack('n', $format);
      $values .= $data === null ? pack('N', 0xFFFFFFFF) : pack('N', strlen($data)).$data;
    }

    $this->notices = [];
    $this->send('B', "\0".$stmt['name']."\0".pack('n', $n).$formats.pack('n', $n).$values."\0\0");
    $this->send('D', "P\0");
    $this->send('E', "\0".pack('N', 0));
    $this->send('S', '');
    return $this->collect('実行に失敗');
  }

  /**
   * プリペアドステートメントの解放
   *
   * @param int $statementId
   * @return bool
   */
  public function demolish(int $statementId): bool {
    if (!isset($this->prepared[$statementId])) return false;
    $name = $this->prepared[$statementId]['name'];
    unset($this->prepared[$statementId]);
    if (!$this->connected) return false;

    $this->send('C', 'S'.$name."\0");
    $this->send('S', '');
    while (true) {
      [$type, $body] = $this->readMessage();
      if ($type === 'Z') {
        $this->status = $body;
        return true;
      }
      if ($type !== '3' && $type !== 'E') $this->other($type, $body);
    }
  }

  public function beginTransaction(): bool {
    $this->query('BEGIN');
    return true;
  }

  /**
   * トランザクションをコミットする
   *
   * 失敗したトランザクション（エラーの後）は、PostgreSQLがCOMMITをROLLBACKにするので、例外にする。
   *
   * @return bool
   * @throws DbException  失敗したトランザクションの場合（SQLSTATEは25P02、ロールバック済み）
   */
  public function commit(): bool {
    $res = $this->query('COMMIT');
    if ($res['command'] === 'ROLLBACK') {
      $this->fail('コミットに失敗: トランザクションの中でエラーが起きたので、ロールバックしました', 0, '25P02');
    }
    return true;
  }

  public function rollback(): bool {
    $this->query('ROLLBACK');
    return true;
  }

  /** トランザクション中（失敗したトランザクションも）かどうか */
  public function inTransaction(): bool {
    return $this->connected && ($this->status === 'T' || $this->status === 'E');
  }

  public function quoteIdentifier(string $name): string {
    return '"'.str_replace('"', '""', $name).'"';
  }

  /**
   * 「?」のプレースホルダを「$1」「$2」…にする
   *
   * 文字列（'…'、E'…'）、識別子（"…"）、$tag$…$tag$、コメントの中は変えない。「??」は「?」そのもの（jsonbの演算子等）。
   *
   * @param string $sql
   * @return string
   */
  public static function placeholders(string $sql): string {
    if (!str_contains($sql, '?')) return $sql;
    $out = '';
    $n = 0;
    $len = strlen($sql);
    for ($i = 0; $i < $len; $i++) {
      $c = $sql[$i];
      $prev = $i > 0 ? $sql[$i - 1] : '';
      $word = fn(string $ch): bool => $ch !== '' && (ctype_alnum($ch) || $ch === '_' || ord($ch) >= 0x80);

      if ($c === "'") {
        $escape = ($prev === 'E' || $prev === 'e') && !$word($i > 1 ? $sql[$i - 2] : '');
        $j = $i + 1;
        while ($j < $len) {
          if ($escape && $sql[$j] === '\\') {
            $j += 2;
            continue;
          }
          if ($sql[$j] === "'") {
            if (($sql[$j + 1] ?? '') === "'") {
              $j += 2;
              continue;
            }
            break;
          }
          $j++;
        }
        $out .= substr($sql, $i, $j - $i + 1);
        $i = $j;
      } else if ($c === '"') {
        $j = $i + 1;
        while ($j < $len && !($sql[$j] === '"' && ($sql[$j + 1] ?? '') !== '"')) $j += $sql[$j] === '"' ? 2 : 1;
        $out .= substr($sql, $i, $j - $i + 1);
        $i = $j;
      } else if ($c === '$' && !$word($prev) && preg_match('/\G\$([A-Za-z_][A-Za-z0-9_]*)?\$/', $sql, $m, 0, $i)) {
        $end = strpos($sql, $m[0], $i + strlen($m[0]));
        $j = $end === false ? $len : $end + strlen($m[0]);
        $out .= substr($sql, $i, $j - $i);
        $i = $j - 1;
      } else if ($c === '-' && ($sql[$i + 1] ?? '') === '-') {
        $j = strpos($sql, "\n", $i);
        $j = $j === false ? $len : $j;
        $out .= substr($sql, $i, $j - $i);
        $i = $j - 1;
      } else if ($c === '/' && ($sql[$i + 1] ?? '') === '*') {
        // 入れ子のコメント
        $depth = 0;
        $j = $i;
        while ($j < $len) {
          if (substr($sql, $j, 2) === '/*') {
            $depth++;
            $j += 2;
          } else if (substr($sql, $j, 2) === '*/') {
            $depth--;
            $j += 2;
            if ($depth === 0) break;
          } else {
            $j++;
          }
        }
        $out .= substr($sql, $i, $j - $i);
        $i = $j - 1;
      } else if ($c === '?') {
        if (($sql[$i + 1] ?? '') === '?') {
          $out .= '?';
          $i++;
        } else {
          $out .= '$'.(++$n);
        }
      } else {
        $out .= $c;
      }
    }
    return $out;
  }

  /**
   * 配列のテキスト（例：{1,2,NULL}、{{a,b},{"c,d",e}}）をPHPの配列にする
   *
   * @param string $text  PostgreSQLの配列の書き方
   * @param int $elementOid  要素の型（値の変換に使う）
   * @return array|null
   */
  public static function parseArray(string $text, int $elementOid = self::OID_TEXT): ?array {
    // 範囲の指定（例：[0:1]={1,2}）は無視する
    if ($text !== '' && $text[0] === '[') $text = substr($text, strpos($text, '=') + 1);
    $pos = 0;
    $res = self::parseArrayLevel($text, $pos, $elementOid);
    return $pos === strlen($text) ? $res : null;
  }

  private static function parseArrayLevel(string $t, int &$pos, int $oid): ?array {
    if (($t[$pos] ?? '') !== '{') return null;
    $pos++;
    $out = [];
    $len = strlen($t);
    if (($t[$pos] ?? '') === '}') {
      $pos++;
      return [];
    }
    while ($pos < $len) {
      if ($t[$pos] === '{') {
        $out[] = self::parseArrayLevel($t, $pos, $oid);
      } else if ($t[$pos] === '"') {
        $v = '';
        for ($pos++; $pos < $len && $t[$pos] !== '"'; $pos++) {
          if ($t[$pos] === '\\') $pos++;
          $v .= $t[$pos] ?? '';
        }
        $pos++;
        $out[] = self::castText($v, $oid);
      } else {
        $start = $pos;
        while ($pos < $len && $t[$pos] !== ',' && $t[$pos] !== '}') $pos++;
        $v = substr($t, $start, $pos - $start);
        $out[] = strtoupper($v) === 'NULL' ? null : self::castText($v, $oid);
      }
      $c = $t[$pos] ?? '';
      $pos++;
      if ($c === '}') return $out;
      if ($c !== ',') return null;
    }
    return null;
  }

  /**
   * テキストの値をPHPの型にする
   *
   * @param string $v  値
   * @param int $oid  型
   * @return mixed  整数はint、浮動小数点数はfloat、真偽値はbool、byteaはバイト、配列は配列、その他は文字列
   */
  public static function castText(string $v, int $oid): mixed {
    switch ($oid) {
      case self::OID_INT2:
      case self::OID_INT4:
      case self::OID_INT8:
      case self::OID_OID:
        $i = (int)$v;
        return (string)$i === $v ? $i : $v;
      case self::OID_FLOAT4:
      case self::OID_FLOAT8:
        return match ($v) {
          'NaN' => NAN,
          'Infinity' => INF,
          '-Infinity' => -INF,
          default => (float)$v,
        };
      case self::OID_BOOL:
        return $v === 't';
      case self::OID_BYTEA:
        if (str_starts_with($v, '\\x')) return (string)hex2bin(substr($v, 2));
        // escape形式（bytea_output = escape）
        return preg_replace_callback('/\\\\(\\\\|[0-7]{3})/', fn($m) => $m[1] === '\\' ? '\\' : chr(octdec($m[1])), $v);
    }
    if (isset(self::ARRAYS[$oid])) return self::parseArray($v, self::ARRAYS[$oid]) ?? $v;
    return $v;
  }

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

  /** SSLRequestを送って、TLSを開始する */
  private function startTls(): void {
    $this->send(null, pack('NN', 8, self::SSL_REQUEST));
    $answer = $this->readBytes(1);
    if ($answer !== 'S') $this->fail('サーバーがTLSに対応していません');

    $opts = is_array($this->tls) ? $this->tls : [];
    $opts += ['peer_name' => $this->host];
    try {
      $this->socket = Tls::enable($this->raw, $opts);
    } catch (TlsException $e) {
      $this->fail('TLSの開始に失敗: '.$e->getMessage());
    }
    $this->secure = true;
  }

  /** StartupMessageと認証、ReadyForQueryまで */
  private function startup(): void {
    $params = ['user' => $this->username, 'database' => $this->dbname, 'client_encoding' => $this->charset,
               'application_name' => 'LittleBeast', 'DateStyle' => 'ISO, YMD'];
    $body = pack('N', self::PROTOCOL);
    foreach ($params as $k => $v) {
      if ($v !== '') $body .= $k."\0".$v."\0";
    }
    $this->send(null, pack('N', strlen($body) + 5).$body."\0");

    $scram = null;
    while (true) {
      [$type, $body] = $this->readMessage();
      if ($type === 'E') $this->failError('接続に失敗', $this->parseError($body));
      if ($type === 'Z') {
        $this->status = $body;
        return;
      }
      if ($type !== 'R') {
        $this->other($type, $body);
        continue;
      }

      $code = unpack('N', $body)[1];
      switch ($code) {
        case 0: // AuthenticationOk
          if ($scram !== null && empty($scram['verified'])) $this->fail('認証に失敗: サーバーがSCRAMを完了しませんでした');
          if ($this->authMethod === '') $this->authMethod = 'trust';
          break;
        case 3: // パスワードをそのまま
          if (!$this->secure && !$this->allowCleartext) {
            $this->fail('認証に失敗: サーバーがパスワードをそのまま送る事を求めました（TLS・UNIXソケットでない場合は、allow_cleartextが必要）');
          }
          $this->authMethod = 'password';
          $this->send('p', $this->password."\0");
          break;
        case 5: // MD5
          $this->authMethod = 'md5';
          $this->send('p', 'md5'.md5(md5($this->password.$this->username).substr($body, 4, 4))."\0");
          break;
        case 10: // SASL
          $scram = $this->scramStart(array_filter(explode("\0", substr($body, 4))));
          break;
        case 11: // SASLContinue
          if ($scram === null) $this->fail('認証に失敗: 想定外のSASLContinue');
          $scram = $this->scramContinue($scram, substr($body, 4));
          break;
        case 12: // SASLFinal
          if ($scram === null) $this->fail('認証に失敗: 想定外のSASLFinal');
          $this->scramFinal($scram, substr($body, 4));
          $scram['verified'] = true;
          break;
        default:
          $this->fail("未対応の認証方式: {$code}（GSSAPI・SSPI・Kerberosは未対応）");
      }
    }
  }

  /** SCRAM-SHA-256（-PLUS）を始める（RFC 5802、RFC 7677） */
  private function scramStart(array $mechanisms): array {
    $cbData = $this->secure && $this->socketPath === '' && $this->channelBinding !== 'disable' ? $this->channelBindingData() : null;
    if (in_array('SCRAM-SHA-256-PLUS', $mechanisms, true) && $cbData !== null) {
      $mech = 'SCRAM-SHA-256-PLUS';
      $gs2 = 'p=tls-server-end-point,,';
    } else if (in_array('SCRAM-SHA-256', $mechanisms, true)) {
      if ($this->channelBinding === 'require') $this->fail('認証に失敗: チャネルバインディングが使えません（channel_binding = require）');
      $mech = 'SCRAM-SHA-256';
      // TLSなのにサーバーが-PLUSを出さない＝クライアントは対応している（ダウングレードを見つける為の「y」）
      $gs2 = $cbData !== null ? 'y,,' : 'n,,';
      $cbData = null;
    } else {
      $this->fail('未対応のSASLの方式: '.implode(', ', $mechanisms));
    }

    $nonce = rtrim(strtr(base64_encode(random_bytes(18)), '+/', '-_'), '=');
    $first = 'n=,r='.$nonce; // ユーザー名はStartupMessageの物を使う
    $this->authMethod = $mech;
    $this->send('p', $mech."\0".pack('N', strlen($gs2.$first)).$gs2.$first);
    return ['nonce' => $nonce, 'first' => $first, 'gs2' => $gs2, 'cb' => $cbData];
  }

  private function scramContinue(array $scram, string $serverFirst): array {
    $a = [];
    foreach (explode(',', $serverFirst) as $kv) {
      if (strlen($kv) > 2 && $kv[1] === '=') $a[$kv[0]] = substr($kv, 2);
    }
    $salt = base64_decode($a['s'] ?? '', true);
    $iter = (int)($a['i'] ?? 0);
    if (!isset($a['r']) || !str_starts_with($a['r'], $scram['nonce']) || strlen($a['r']) <= strlen($scram['nonce']) || $salt === false || $iter < 1) {
      $this->fail('認証に失敗: 不正なSCRAMのメッセージ');
    }

    $salted = hash_pbkdf2('sha256', self::saslPrep($this->password), $salt, $iter, 32, true);
    $clientKey = hash_hmac('sha256', 'Client Key', $salted, true);
    $finalBare = 'c='.base64_encode($scram['gs2'].($scram['cb'] ?? '')).',r='.$a['r'];
    $authMessage = $scram['first'].','.$serverFirst.','.$finalBare;
    $proof = $clientKey ^ hash_hmac('sha256', $authMessage, hash('sha256', $clientKey, true), true);
    $this->send('p', $finalBare.',p='.base64_encode($proof));

    return $scram + ['salted' => $salted, 'auth' => $authMessage];
  }

  /** サーバーの署名を確認する（偽物のサーバーではない事） */
  private function scramFinal(array $scram, string $serverFinal): void {
    if (str_starts_with($serverFinal, 'e=')) $this->fail('認証に失敗: '.substr($serverFinal, 2));
    $expected = hash_hmac('sha256', $scram['auth'], hash_hmac('sha256', 'Server Key', $scram['salted'], true), true);
    if (!str_starts_with($serverFinal, 'v=') || !hash_equals($expected, (string)base64_decode(substr($serverFinal, 2), true))) {
      $this->fail('認証に失敗: サーバーの署名が不正です');
    }
  }

  /** tls-server-end-point（RFC 5929）：サーバーの証明書のハッシュ（MD5・SHA-1ならSHA-256） */
  private function channelBindingData(): ?string {
    $cert = Tls::info($this->socket)['peer_certificates'][0] ?? null;
    if ($cert === null) return null;
    $hash = match ($cert->sigAlg) {
      '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, $cert->der, true);
  }

  /**
   * パスワードの正規化（SASLprep、RFC 4013の簡略版）
   *
   * intlのNormalizerが有ればNFKC、無ければそのまま（ASCIIと普通の日本語は同じ）。
   */
  private static function saslPrep(string $pw): string {
    if (preg_match('/^[\x20-\x7E]*$/', $pw) || !mb_check_encoding($pw, 'UTF-8')) return $pw;
    // 色々なスペースはスペース、ソフトハイフン等は消す
    $pw = preg_replace(['/[\x{00A0}\x{1680}\x{2000}-\x{200B}\x{202F}\x{205F}\x{3000}]/u', '/[\x{00AD}\x{034F}\x{1806}\x{180B}-\x{180D}\x{200C}\x{200D}\x{2060}\x{FE00}-\x{FE0F}\x{FEFF}]/u'], [' ', ''], $pw);
    return class_exists('Normalizer') ? (\Normalizer::normalize($pw, \Normalizer::FORM_KC) ?: $pw) : $pw;
  }

  ///////////////
  // 結果

  /**
   * ReadyForQueryまでの結果を集める
   *
   * @param string $what  失敗した時のメッセージ
   * @return array
   * @throws DbException
   */
  private function collect(string $what): array {
    $results = [];
    $cur = ['fields' => [], 'rows' => []];
    $error = null;

    while (true) {
      [$type, $body] = $this->readMessage();
      switch ($type) {
        case 'T': // RowDescription
          $cur = ['fields' => $this->parseRowDescription($body), 'rows' => []];
          break;
        case 'D': // DataRow
          $cur['rows'][] = $this->parseDataRow($body, $cur['fields']);
          break;
        case 'C': // CommandComplete
          $tag = rtrim($body, "\0");
          $results[] = $cur + ['command' => $tag, 'affectedRows' => $this->affected($tag)];
          $cur = ['fields' => [], 'rows' => []];
          break;
        case 'I': // EmptyQueryResponse
          $results[] = ['fields' => [], 'rows' => [], 'command' => '', 'affectedRows' => 0];
          break;
        case 's': // PortalSuspended
          $results[] = $cur + ['command' => '', 'affectedRows' => 0];
          break;
        case '2': // BindComplete
        case 'n': // NoData（行を返さない文）
          break;
        case 'E':
          $error = $this->parseError($body);
          if ($this->fatal($error)) $this->failError($what, $error);
          break;
        case 'G': // CopyInResponse：断る
          $this->send('f', "COPYは未対応です\0");
          break;
        case 'H': // CopyOutResponse：データは無視する
        case 'd':
        case 'c':
          break;
        case 'Z':
          $this->status = $body;
          if ($error) $this->failError($what, $error);
          if (empty($results)) $results[] = ['fields' => [], 'rows' => [], 'command' => '', 'affectedRows' => 0];
          $warnings = count($this->notices);
          foreach ($results as &$r) $r += ['insertId' => 0, 'warningCount' => $warnings];
          unset($r);
          $first = array_shift($results);
          if (!empty($results)) $first['more'] = $results;
          return $first;
        default:
          $this->other($type, $body);
      }
    }
  }

  /** 「INSERT 0 5」「UPDATE 3」等から行数 */
  private function affected(string $tag): int {
    return preg_match('/^(?:INSERT \d+|UPDATE|DELETE|SELECT|MOVE|FETCH|COPY|MERGE) (\d+)$/', $tag, $m) ? (int)$m[1] : 0;
  }

  private function parseRowDescription(string $b): array {
    $n = unpack('n', $b)[1];
    $pos = 2;
    $fields = [];
    for ($i = 0; $i < $n; $i++) {
      $end = strpos($b, "\0", $pos);
      $name = substr($b, $pos, $end - $pos);
      $f = unpack('Ntable/ncolumn/Ntype/nsize/Nmodifier/nformat', $b, $end + 1);
      $pos = $end + 19;
      $fields[] = ['name' => $name, 'table' => $f['table'], 'column' => $f['column'], 'type' => $f['type'],
                   'size' => $f['size'] >= 0x8000 ? $f['size'] - 0x10000 : $f['size'],
                   'modifier' => $f['modifier'] >= 0x80000000 ? $f['modifier'] - 0x100000000 : $f['modifier'], 'format' => $f['format']];
    }
    return $fields;
  }

  private function parseDataRow(string $b, array $fields): array {
    $n = unpack('n', $b)[1];
    $pos = 2;
    $row = [];
    for ($i = 0; $i < $n; $i++) {
      $len = unpack('N', $b, $pos)[1];
      $pos += 4;
      $f = $fields[$i] ?? ['name' => (string)$i, 'type' => self::OID_TEXT];
      if ($len === 0xFFFFFFFF) {
        $row[$f['name']] = null;
        continue;
      }
      $row[$f['name']] = self::castText(substr($b, $pos, $len), $f['type']);
      $pos += $len;
    }
    return $row;
  }

  /**
   * パラメータを送る形にする
   *
   * @return array  [形式（0＝テキスト、1＝バイナリ）, データ（nullならNULL）]
   */
  private function encodeParam(mixed $v, int $oid, int $i): array {
    if ($v instanceof \BackedEnum) $v = $v->value;
    if ($v instanceof \DateTimeInterface) $v = $v->format('Y-m-d H:i:s.uP');
    if ($v === null) return [0, null];
    if (is_bool($v)) {
      $int = in_array($oid, [self::OID_INT2, self::OID_INT4, self::OID_INT8], true);
      return [0, $int ? ($v ? '1' : '0') : ($v ? 't' : 'f')];
    }
    if (is_int($v)) return [0, (string)$v];
    if (is_float($v)) {
      if (is_nan($v)) return [0, 'NaN'];
      if (is_infinite($v)) return [0, $v > 0 ? 'Infinity' : '-Infinity'];
      return [0, var_export($v, true)];
    }
    if (is_scalar($v) || $v instanceof \Stringable) {
      // byteaには生のバイト（バイナリ形式）を送る（テキストだと「\」が特別な意味になる）
      return $oid === self::OID_BYTEA ? [1, (string)$v] : [0, (string)$v];
    }
    $this->fail("パラメータ{$i}の型は未対応です: ".get_debug_type($v));
  }

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

  /** 接続の処理中に来る、結果と関係の無いメッセージ */
  private function other(string $type, string $body): void {
    switch ($type) {
      case 'S': // ParameterStatus
        [$k, $v] = explode("\0", $body) + ['', ''];
        $this->params[$k] = $v;
        return;
      case 'K': // BackendKeyData
        $this->pid = unpack('N', $body)[1];
        return;
      case 'N': // NoticeResponse
        $e = $this->parseError($body);
        $this->notices[] = ['severity' => $e['V'] ?? $e['S'] ?? '', 'code' => $e['C'] ?? '', 'message' => $e['M'] ?? ''];
        return;
      case 'A': // NotificationResponse（LISTEN）：無視する
        return;
    }
    $this->fail("想定外のメッセージ: {$type}");
  }

  /** ErrorResponse・NoticeResponseのフィールド（S、V、C、M、D、H等） */
  private function parseError(string $b): array {
    $out = [];
    foreach (explode("\0", rtrim($b, "\0")) as $f) {
      if ($f !== '') $out[$f[0]] = substr($f, 1);
    }
    return $out;
  }

  /** FATAL・PANICのエラーの後はサーバーが接続を切る */
  private function fatal(array $e): bool {
    return in_array($e['V'] ?? $e['S'] ?? '', ['FATAL', 'PANIC'], true);
  }

  /**
   * サーバーのエラーで例外を投げる
   *
   * @return never
   */
  private function failError(string $what, array $e): never {
    if ($this->fatal($e)) $this->drop();
    $code = $e['C'] ?? '';
    $msg = "{$what}: {$code} - ".($e['M'] ?? '不明なエラー');
    if (!empty($e['D'])) $msg .= "（{$e['D']}）";
    $this->fail($msg, 0, $code);
  }

  private function readMessage(): array {
    $head = $this->readBytes(5);
    $type = $head[0];
    $len = unpack('N', $head, 1)[1];
    if ($len < 4 || $len > 0x40000000) {
      $this->drop();
      $this->fail("不正なメッセージの長さ: {$len}");
    }
    $body = $len > 4 ? $this->readBytes($len - 4) : '';
    if ($this->debug) $this->log('RECV', $type, $body);
    return [$type, $body];
  }

  private function readBytes(int $len): string {
    if (!$this->socket) $this->fail('接続していません');
    if ($this->out !== '') $this->flush();
    $buf = '';
    while (strlen($buf) < $len) {
      $chunk = @fread($this->socket, min($len - strlen($buf), 1048576));
      if ($chunk === false || $chunk === '') {
        $timedOut = Tls::timedOut($this->socket) || (bool)(stream_get_meta_data($this->raw ?? $this->socket)['timed_out'] ?? false);
        if (!$timedOut && $chunk === '' && !feof($this->socket)) continue;
        $this->drop();
        $this->fail($timedOut ? "メッセージの読み込みがタイムアウト（{$this->timeout}秒）" : 'サーバーとの接続が切れました');
      }
      $buf .= $chunk;
    }
    return $buf;
  }

  /**
   * メッセージを送る（バッファに溜めて、次に読む時にまとめて送る）
   *
   * Parse・Bind・Execute・Syncを別々に送ると、Nagleと遅延ACKで1回40ms程待たされる為。
   *
   * @param string|null $type  種類（StartupMessage・SSLRequestはnull）
   * @param string $body  中身
   */
  private function send(?string $type, string $body): void {
    if (!$this->socket) $this->fail('接続していません');
    $this->out .= $type === null ? $body : $type.pack('N', strlen($body) + 4).$body;
    if ($this->debug) $this->log('SEND', $type ?? '-', $body);
  }

  /** 溜めたメッセージを送る */
  private function flush(): void {
    $data = $this->out;
    $this->out = '';
    for ($w = 0, $len = strlen($data); $w < $len; ) {
      $n = @fwrite($this->socket, substr($data, $w, 1048576));
      if ($n === false || $n === 0) {
        $this->drop();
        $this->fail('メッセージの送信に失敗');
      }
      $w += $n;
    }
  }

  private function log(string $dir, string $type, string $body): void {
    $this->packetLog[] = ['direction' => $dir, 'type' => $type, 'length' => strlen($body), 'data' => $body, 'timestamp' => microtime(true)];
    $show = preg_replace('/[^\x20-\x7E]/', '.', substr($body, 0, 200));
    echo "=== {$dir} {$type} (長さ: ".strlen($body).") === {$show}\n";
  }

  private function requireConnection(): void {
    if (!$this->connected) $this->fail('接続していません（先にconnect()を呼んで下さい）');
  }

  private function drop(): void {
    if ($this->socket) @fclose($this->socket);
    if ($this->raw && is_resource($this->raw)) @fclose($this->raw);
    $this->socket = $this->raw = null;
    $this->out = '';
    $this->connected = false;
    $this->secure = false;
    $this->status = '';
    $this->prepared = [];
  }

  /**
   * @return never
   * @throws DbException
   */
  private function fail(string $msg, int $code = 0, string $sqlState = ''): never {
    if (function_exists('logger')) logger(\LogType::Db, $msg);
    throw new DbException($msg, $code, $sqlState);
  }
}