<?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.
 */
// テスト用の偽物のMySQLサーバー（MockServerが起動する）。
// 使い方：php mysql-server.php ポート [既定の認証プラグイン] [証明書のPEM] [秘密鍵のPEM]
// SQLはメモリーのSQLiteで実行し、結果をMySQLのプロトコル（テキスト・バイナリ）で返す（pdo_sqliteが必要）。
// 証明書と秘密鍵を渡すと、TLSとRSA（caching_sha2_password・sha256_passwordの完全な認証）に対応する。
//
// ユーザー（パスワード、認証プラグイン）：
//   lb（ひみつ、mysql_native_password）、nopw（なし、mysql_native_password）、
//   sha2（sha2ひみつ、caching_sha2_password）、sha256（sha256ひみつ、sha256_password）、
//   clear（clearひみつ、mysql_clear_password、TLSのみ）
// データベース：「nodb」以外は何でも存在する事にする
// 特別なSQL：
//   SELECT LB_DISCONNECT()  返事をせずに接続を切る
//   SELECT LB_SLEEP(秒)     返事を遅らせる
//   CALL lb_multi()         結果を2つ返す
//   SHOW FULL TABLES、SHOW CREATE TABLE（このサーバーで作ったテーブルの元のSQL）
//   @@lb_tls・@@lb_plugin・@@lb_full_auth・@@lb_caps・@@character_set_client・@@version、
//   DATABASE()・CONNECTION_ID()・VERSION()

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

use Std\Lib\Crypto\Rsa;
use Std\Lib\Tls;

$port = (int)($argv[1] ?? 0);
$defaultPlugin = $argv[2] ?? 'mysql_native_password';
$certFile = $argv[3] ?? '';
$keyFile = $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);
}

$my = new MockMysql($defaultPlugin, $certFile, $keyFile);
fwrite(STDOUT, "ready\n");
fflush(STDOUT);
$my->run($server);

class MockMysql {
  private const VERSION = '8.4.0-lbmock';

  // 機能フラグ
  private const CLIENT_CONNECT_WITH_DB = 0x00000008;
  private const CLIENT_PROTOCOL_41 = 0x00000200;
  private const CLIENT_SSL = 0x00000800;
  private const CLIENT_SECURE_CONNECTION = 0x00008000;
  private const CLIENT_PLUGIN_AUTH = 0x00080000;
  private const CLIENT_CONNECT_ATTRS = 0x00100000;
  private const CLIENT_PLUGIN_AUTH_LENENC = 0x00200000;
  private const SERVER_CAPS = 0x00000001 | 0x00000002 | 0x00000004 | 0x00000008 | 0x00000200 | 0x00002000
    | 0x00008000 | 0x00020000 | 0x00040000 | 0x00080000 | 0x00100000 | 0x00200000;

  // 状況
  private const STATUS_IN_TRANS = 0x0001;
  private const STATUS_AUTOCOMMIT = 0x0002;
  private const STATUS_MORE_RESULTS = 0x0008;

  // 種類
  private const T_DECIMAL = 0xF6, T_TINY = 0x01, T_SHORT = 0x02, T_LONG = 0x03, T_FLOAT = 0x04, T_DOUBLE = 0x05,
    T_NULL = 0x06, T_TIMESTAMP = 0x07, T_LONGLONG = 0x08, T_INT24 = 0x09, T_DATE = 0x0A, T_TIME = 0x0B,
    T_DATETIME = 0x0C, T_YEAR = 0x0D, T_JSON = 0xF5, T_BLOB = 0xFC, T_VAR_STRING = 0xFD, T_STRING = 0xFE;

  private const USERS = [
    'lb' => ['ひみつ', 'mysql_native_password'],
    'nopw' => ['', 'mysql_native_password'],
    'sha2' => ['sha2ひみつ', 'caching_sha2_password'],
    'sha256' => ['sha256ひみつ', 'sha256_password'],
    'clear' => ['clearひみつ', 'mysql_clear_password'],
  ];

  private const CHARSETS = [45 => 'utf8mb4', 33 => 'utf8mb3', 8 => 'latin1', 11 => 'ascii', 63 => 'binary', 12 => 'ujis', 13 => 'sjis', 95 => 'cp932', 97 => 'eucjpms', 255 => 'utf8mb4'];

  private \PDO $pdo;
  private array $conns = [];
  /** テーブル => 元のCREATE TABLE（SHOW CREATE TABLE用） */
  private array $ddl = [];
  private int $nextId = 1;
  private int $nextStmt = 1;
  private array $sha2Cache = [];
  private $privKey = null;
  private string $pubPem = '';

  public function __construct(private string $defaultPlugin, private string $certFile, private string $keyFile) {
    $this->pdo = new \PDO('sqlite::memory:');
    $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    if ($keyFile !== '') {
      $this->privKey = Rsa::fromPem((string)file_get_contents($keyFile));
      $this->pubPem = $this->privKey->publicPem();
    }
  }

  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;
        $res = $this->handle($id);
        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);
    // 読み込みのバッファを無くす（SSLリクエストと一緒にTLSのClientHelloまで読んでしまうと、TLSを開始出来ない）
    stream_set_read_buffer($c, 0);
    $id = $this->nextId++;
    $scramble = '';
    for ($i = 0; $i < 20; $i++) $scramble .= chr(random_int(33, 126));

    $caps = self::SERVER_CAPS | ($this->certFile !== '' ? self::CLIENT_SSL : 0);
    $this->conns[$id] = [
      'sock' => $c, 'id' => $id, 'state' => 'new', 'seq' => 0, 'tls' => false, 'caps' => 0,
      'user' => '', 'plugin' => '', 'scramble' => $scramble, 'db' => '', 'charset' => 45,
      'fullAuth' => false, 'stmts' => [], 'inTrans' => false,
    ];

    $g = chr(10).self::VERSION."\0".pack('V', $id).substr($scramble, 0, 8)."\0"
      .pack('v', $caps & 0xFFFF).chr(255).pack('v', self::STATUS_AUTOCOMMIT).pack('v', $caps >> 16)
      .chr(21).str_repeat("\0", 10).substr($scramble, 8)."\0".$this->defaultPlugin."\0";
    $this->send($id, $g);
  }

  private function drop(int $id): void {
    @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') {
      // MockServer::stop()の終了のリクエスト
      $head = $this->readRaw($id, 4);
      if ($head === null) return false;
      if ($head === 'QUIT') return 'quit';
      $pkt = $this->readRest($id, $head);
      if ($pkt === null) return false;
      return $this->handshake($id, $pkt);
    }

    $pkt = $this->recv($id);
    if ($pkt === null || $pkt === '') return false;
    return $this->command($id, $pkt);
  }

  private function handshake(int $id, string $pkt): bool {
    $cn = &$this->conns[$id];
    $caps = unpack('V', $pkt)[1];

    // SSLリクエスト（32バイト）
    if (($caps & self::CLIENT_SSL) && strlen($pkt) === 32) {
      if ($this->certFile === '') return false;
      try {
        $cn['sock'] = Tls::accept($cn['sock'], ['cert' => $this->certFile, 'key' => $this->keyFile]);
      } catch (\Throwable $e) {
        return false;
      }
      $cn['tls'] = true;
      $pkt = $this->recv($id);
      if ($pkt === null) return false;
      $caps = unpack('V', $pkt)[1];
    }

    if (!($caps & self::CLIENT_PROTOCOL_41)) {
      $this->err($id, 1251, '08004', 'Client does not support authentication protocol requested by server');
      return false;
    }

    $cn['caps'] = $caps;
    $cn['charset'] = ord($pkt[8]);
    $pos = 32;
    $end = strpos($pkt, "\0", $pos);
    $user = substr($pkt, $pos, $end - $pos);
    $pos = $end + 1;
    if ($caps & self::CLIENT_PLUGIN_AUTH_LENENC) {
      $auth = $this->lenencStr($pkt, $pos);
    } else if ($caps & self::CLIENT_SECURE_CONNECTION) {
      $len = ord($pkt[$pos]);
      $auth = substr($pkt, $pos + 1, $len);
      $pos += 1 + $len;
    } else {
      $end = strpos($pkt, "\0", $pos);
      $auth = substr($pkt, $pos, $end - $pos);
      $pos = $end + 1;
    }
    $db = '';
    if ($caps & self::CLIENT_CONNECT_WITH_DB) {
      $end = strpos($pkt, "\0", $pos);
      $db = substr($pkt, $pos, ($end === false ? strlen($pkt) : $end) - $pos);
      $pos = $end === false ? strlen($pkt) : $end + 1;
    }
    $clientPlugin = '';
    if ($caps & self::CLIENT_PLUGIN_AUTH) {
      $end = strpos($pkt, "\0", $pos);
      $clientPlugin = substr($pkt, $pos, ($end === false ? strlen($pkt) : $end) - $pos);
    }

    $cn['user'] = $user;
    $known = self::USERS[$user] ?? null;
    $using = $auth === '' || $auth === "\0" ? 'NO' : 'YES';
    if (!$known) {
      $this->err($id, 1045, '28000', "Access denied for user '{$user}'@'localhost' (using password: {$using})");
      return false;
    }
    [$pw, $plugin] = $known;
    $cn['plugin'] = $plugin;

    // クライアントのプラグインが違えば、切り替えを求める
    if ($clientPlugin !== $plugin) {
      for ($i = 0, $cn['scramble'] = ''; $i < 20; $i++) $cn['scramble'] .= chr(random_int(33, 126));
      $this->send($id, "\xFE{$plugin}\0{$cn['scramble']}\0");
      $auth = $this->recv($id);
      if ($auth === null) return false;
    }

    if (!$this->verify($id, $plugin, $pw, $auth)) {
      $this->err($id, 1045, '28000', "Access denied for user '{$user}'@'localhost' (using password: {$using})");
      return false;
    }

    if ($db === 'nodb') {
      $this->err($id, 1049, '42000', "Unknown database '{$db}'");
      return false;
    }
    $cn['db'] = $db;
    $cn['state'] = 'cmd';
    $this->ok($id);
    return true;
  }

  private function verify(int $id, string $plugin, string $pw, ?string $auth): bool {
    $cn = &$this->conns[$id];
    $scramble = $cn['scramble'];
    if ($auth === null) return false;

    switch ($plugin) {
      case 'mysql_native_password':
        if ($pw === '') return $auth === '';
        $s1 = sha1($pw, true);
        return $auth === ($s1 ^ sha1($scramble.sha1($s1, true), true));

      case 'caching_sha2_password':
        if ($pw === '') return $auth === '';
        $s1 = hash('sha256', $pw, true);
        $fast = $s1 ^ hash('sha256', hash('sha256', $s1, true).$scramble, true);
        if (isset($this->sha2Cache[$cn['user']])) {
          if ($auth !== $fast) return false;
          $this->send($id, "\x01\x03");
          return true;
        }
        // キャッシュに無い：完全な認証
        $this->send($id, "\x01\x04");
        $res = $this->recv($id);
        if ($res === null) return false;
        if ($cn['tls']) {
          $ok = $res === $pw."\0";
        } else {
          if ($res === "\x02") {
            if ($this->pubPem === '') return false;
            $this->send($id, "\x01".$this->pubPem);
            $res = $this->recv($id);
          }
          $ok = $this->decrypt((string)$res, $scramble) === $pw."\0";
        }
        if ($ok) {
          $this->sha2Cache[$cn['user']] = true;
          $cn['fullAuth'] = true;
        }
        return $ok;

      case 'sha256_password':
        if ($pw === '') return $auth === "\0" || $auth === '';
        if ($cn['tls']) return $auth === $pw."\0";
        if ($auth === "\x01") {
          if ($this->pubPem === '') return false;
          $this->send($id, "\x01".$this->pubPem);
          $auth = $this->recv($id);
        }
        $cn['fullAuth'] = true;
        return $this->decrypt((string)$auth, $scramble) === $pw."\0";

      case 'mysql_clear_password':
        return $cn['tls'] && $auth === $pw."\0";
    }

    return false;
  }

  private function decrypt(string $data, string $scramble): ?string {
    $out = $this->privKey?->decrypt($data, 'sha1');
    if ($out === null) return null;
    $res = '';
    for ($i = 0; $i < strlen($out); $i++) $res .= $out[$i] ^ $scramble[$i % strlen($scramble)];
    return $res;
  }

  ///////////////
  // コマンド

  private function command(int $id, string $pkt): bool {
    $cn = &$this->conns[$id];
    $cmd = ord($pkt[0]);
    $data = (string)substr($pkt, 1);

    switch ($cmd) {
      case 0x01: // COM_QUIT
        return false;
      case 0x02: // COM_INIT_DB
        if ($data === 'nodb') {
          $this->err($id, 1049, '42000', "Unknown database '{$data}'");
        } else {
          $cn['db'] = $data;
          $this->ok($id);
        }
        return true;
      case 0x03: // COM_QUERY
        return $this->query($id, $data);
      case 0x0E: // COM_PING
        $this->ok($id);
        return true;
      case 0x16: // COM_STMT_PREPARE
        $this->prepare($id, $data);
        return true;
      case 0x17: // COM_STMT_EXECUTE
        $this->execute($id, $data);
        return true;
      case 0x19: // COM_STMT_CLOSE（返事なし）
        unset($cn['stmts'][unpack('V', $data)[1]]);
        return true;
      case 0x1A: // COM_STMT_RESET
        $this->ok($id);
        return true;
    }

    $this->err($id, 1047, '08S01', 'Unknown command');
    return true;
  }

  private function query(int $id, string $sql): bool {
    $cn = &$this->conns[$id];
    $sql = rtrim(trim($sql), ';');

    if (preg_match('/^SELECT\s+LB_DISCONNECT\(\)/i', $sql)) return false;
    if (preg_match('/LB_SLEEP\(([\d.]+)\)/i', $sql, $m)) {
      usleep((int)((float)$m[1] * 1000000));
      $sql = preg_replace('/LB_SLEEP\([\d.]+\)/i', '0', $sql);
    }

    if (preg_match('/^CALL\s+lb_multi\(\)$/i', $sql)) {
      $this->resultSet($id, $this->pdo->query("SELECT 1 AS a"), false, self::STATUS_MORE_RESULTS);
      $this->resultSet($id, $this->pdo->query("SELECT '二' AS b"), false, self::STATUS_MORE_RESULTS);
      $this->ok($id);
      return true;
    }

    if (preg_match('/^(START\s+TRANSACTION|BEGIN)$/i', $sql)) {
      if ($this->pdo->inTransaction()) $this->pdo->commit();
      $this->pdo->beginTransaction();
      $cn['inTrans'] = true;
      $this->ok($id);
      return true;
    }
    if (preg_match('/^(COMMIT|ROLLBACK)$/i', $sql, $m)) {
      if ($this->pdo->inTransaction()) strtoupper($m[1]) === 'COMMIT' ? $this->pdo->commit() : $this->pdo->rollBack();
      $cn['inTrans'] = false;
      $this->ok($id);
      return true;
    }
    if (preg_match('/^SET\s+NAMES\s+\'?(\w+)/i', $sql, $m)) {
      $cs = array_search(strtolower($m[1]), self::CHARSETS, true);
      if ($cs !== false) $cn['charset'] = $cs;
      $this->ok($id);
      return true;
    }
    if (preg_match('/^SET\s/i', $sql)) {
      $this->ok($id);
      return true;
    }
    if (preg_match('/^USE\s+`?(\w+)`?$/i', $sql, $m)) {
      return $this->command($id, "\x02".$m[1]);
    }
    // 変換（Std\Lib\Db\Convert）用：テーブルの一覧と元のCREATE TABLE
    if (preg_match('/^SHOW\s+FULL\s+TABLES$/i', $sql)) {
      $db = $cn['db'] === '' ? 'lb' : $cn['db'];
      $st = $this->pdo->query("SELECT name AS ".$this->pdo->quote('Tables_in_'.$db).", 'BASE TABLE' AS Table_type FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name");
      $this->resultSet($id, $st, false);
      return true;
    }
    if (preg_match('/^SHOW\s+CREATE\s+TABLE\s+`?([^`]+)`?$/i', $sql, $m)) {
      $ddl = $this->ddl[strtolower($m[1])] ?? null;
      if ($ddl === null) {
        $this->err($id, 1146, '42S02', "Table '{$cn['db']}.{$m[1]}' doesn't exist");
        return true;
      }
      $st = $this->pdo->prepare('SELECT ? AS "Table", ? AS "Create Table"');
      $st->execute([$m[1], $ddl]);
      $this->resultSet($id, $st, false);
      return true;
    }

    try {
      $st = $this->pdo->prepare($this->rewrite($id, $sql));
      $st->execute();
    } catch (\PDOException $e) {
      $this->sqlError($id, $e);
      return true;
    }
    if (preg_match('/^CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([^`\s(]+)`?/i', $sql, $m)) $this->ddl[strtolower($m[1])] = $sql;
    if (preg_match('/^DROP\s+TABLE\s+(?:IF\s+EXISTS\s+)?`?([^`\s]+)`?/i', $sql, $m)) unset($this->ddl[strtolower($m[1])]);

    $this->finish($id, $st, $sql, false);
    return true;
  }

  private function prepare(int $id, string $sql): void {
    $cn = &$this->conns[$id];
    $sql = rtrim(trim($sql), ';');
    [$n, $marks] = $this->placeholders($sql);

    try {
      $st = $this->pdo->prepare($this->rewrite($id, $sql));
      $cols = [];
      if (preg_match('/^\s*(SELECT|WITH|VALUES|PRAGMA)/i', $sql)) {
        $st->execute(array_fill(0, $n, null));
        $cols = $this->columns($st, []);
        $st->closeCursor();
      }
    } catch (\PDOException $e) {
      $this->sqlError($id, $e);
      return;
    }

    $sid = $this->nextStmt++;
    $cn['stmts'][$sid] = ['sql' => $sql, 'n' => $n, 'marks' => $marks, 'types' => array_fill(0, $n, [self::T_VAR_STRING, 0])];

    $this->send($id, "\x00".pack('V', $sid).pack('v', count($cols)).pack('v', $n)."\x00".pack('v', 0));
    if ($n > 0) {
      for ($i = 0; $i < $n; $i++) $this->send($id, $this->colDef(['name' => '?', 'table' => '', 'type' => self::T_VAR_STRING, 'flags' => 0, 'decimals' => 0, 'charset' => 63]));
      $this->eof($id);
    }
    if ($cols) {
      foreach ($cols as $c) $this->send($id, $this->colDef($c));
      $this->eof($id);
    }
  }

  private function execute(int $id, string $data): void {
    $cn = &$this->conns[$id];
    $sid = unpack('V', $data)[1];
    if (!isset($cn['stmts'][$sid])) {
      $this->err($id, 1243, 'HY000', "Unknown prepared statement handler ({$sid}) given to mysqld_stmt_execute");
      return;
    }
    $stmt = &$cn['stmts'][$sid];
    $n = $stmt['n'];
    $pos = 9;
    $values = [];

    if ($n > 0) {
      $bitmap = substr($data, $pos, intdiv($n + 7, 8));
      $pos += strlen($bitmap);
      $bound = ord($data[$pos]);
      $pos++;
      if ($bound) {
        for ($i = 0; $i < $n; $i++) {
          $stmt['types'][$i] = [ord($data[$pos]), ord($data[$pos + 1])];
          $pos += 2;
        }
      }
      for ($i = 0; $i < $n; $i++) {
        if (ord($bitmap[$i >> 3]) & (1 << ($i & 7))) {
          $values[] = null;
          continue;
        }
        $values[] = $this->readParam($data, $pos, $stmt['types'][$i][0], ($stmt['types'][$i][1] & 0x80) !== 0);
      }
    }

    // 浮動小数点数はSQLiteにREALとして渡す
    $sql = $stmt['sql'];
    foreach (array_reverse(array_keys($values)) as $i) {
      if (is_float($values[$i])) $sql = substr_replace($sql, 'CAST(? AS REAL)', $stmt['marks'][$i], 1);
    }

    try {
      $st = $this->pdo->prepare($this->rewrite($id, $sql));
      foreach ($values as $i => $v) {
        $type = match (true) {
          $v === null => \PDO::PARAM_NULL,
          is_int($v) => \PDO::PARAM_INT,
          is_string($v) && !mb_check_encoding($v, 'UTF-8') => \PDO::PARAM_LOB,
          default => \PDO::PARAM_STR,
        };
        $st->bindValue($i + 1, is_float($v) ? var_export($v, true) : $v, $type);
      }
      $st->execute();
    } catch (\PDOException $e) {
      $this->sqlError($id, $e);
      return;
    }

    $this->finish($id, $st, $stmt['sql'], true);
  }

  private function readParam(string $d, int &$pos, int $type, bool $unsigned): mixed {
    switch ($type) {
      case self::T_TINY:
        $v = ord($d[$pos]);
        $pos += 1;
        return $unsigned || $v < 0x80 ? $v : $v - 0x100;
      case self::T_SHORT:
      case self::T_YEAR:
        $v = unpack('v', $d, $pos)[1];
        $pos += 2;
        return $unsigned || $v < 0x8000 ? $v : $v - 0x10000;
      case self::T_LONG:
      case self::T_INT24:
        $v = unpack('V', $d, $pos)[1];
        $pos += 4;
        return $unsigned || $v < 0x80000000 ? $v : $v - 0x100000000;
      case self::T_LONGLONG:
        $v = unpack('P', $d, $pos)[1];
        $pos += 8;
        return $unsigned && $v < 0 ? sprintf('%u', $v) : $v;
      case self::T_FLOAT:
        $v = unpack('g', $d, $pos)[1];
        $pos += 4;
        return $v;
      case self::T_DOUBLE:
        $v = unpack('e', $d, $pos)[1];
        $pos += 8;
        return $v;
      case self::T_NULL:
        return null;
      case self::T_DATE:
      case self::T_DATETIME:
      case self::T_TIMESTAMP:
        $len = ord($d[$pos]);
        $b = substr($d, $pos + 1, $len).str_repeat("\0", 11);
        $pos += 1 + $len;
        $s = sprintf('%04d-%02d-%02d', unpack('v', $b)[1], ord($b[2]), ord($b[3]));
        if ($type === self::T_DATE) return $s;
        $us = unpack('V', $b, 7)[1];
        return $s.sprintf(' %02d:%02d:%02d', ord($b[4]), ord($b[5]), ord($b[6])).($us ? sprintf('.%06d', $us) : '');
      case self::T_TIME:
        $len = ord($d[$pos]);
        $b = substr($d, $pos + 1, $len).str_repeat("\0", 12);
        $pos += 1 + $len;
        $us = unpack('V', $b, 8)[1];
        return (ord($b[0]) ? '-' : '').sprintf('%02d:%02d:%02d', unpack('V', $b, 1)[1] * 24 + ord($b[5]), ord($b[6]), ord($b[7])).($us ? sprintf('.%06d', $us) : '');
    }

    return $this->lenencStr($d, $pos);
  }

  /** 結果セット又はOK */
  private function finish(int $id, \PDOStatement $st, string $sql, bool $binary): void {
    if ($st->columnCount() > 0) {
      $this->resultSet($id, $st, $binary);
      return;
    }
    $insertId = preg_match('/^\s*(INSERT|REPLACE)\b/i', $sql) ? (int)$this->pdo->lastInsertId() : 0;
    $this->ok($id, $st->rowCount(), $insertId);
  }

  ///////////////
  // SQLite → MySQL

  /** MySQLの書き方をSQLiteにする、特別な変数を値にする */
  private function rewrite(int $id, string $sql): string {
    $cn = $this->conns[$id];
    $q = fn(string $s): string => $this->pdo->quote($s);
    $auto = 0;
    $sql = preg_replace('/\b(?:TINY|SMALL|MEDIUM|BIG)?INT(?:EGER)?(?:\(\d+\))?(?:\s+UNSIGNED)?(?:\s+NOT\s+NULL)?\s+AUTO_INCREMENT(?:\s+PRIMARY\s+KEY)?/i', 'INTEGER PRIMARY KEY AUTOINCREMENT', $sql, -1, $auto);
    $sql = preg_replace('/\)\s*(ENGINE|DEFAULT\s+CHARSET|CHARSET|COLLATE)\s*=.*$/is', ')', $sql);
    if (preg_match('/^\s*CREATE\s+TABLE/i', $sql)) {
      // SQLiteに無い物（照合順序、文字セット、DATETIME(6)のDEFAULT）、AUTO_INCREMENTの別の主キー
      $sql = preg_replace(['/\s+COLLATE\s+\w+/i', '/\s+CHARACTER\s+SET\s+\w+/i', '/CURRENT_TIMESTAMP\(\d*\)/i'], ['', '', 'CURRENT_TIMESTAMP'], $sql);
      if ($auto) $sql = preg_replace('/,\s*PRIMARY\s+KEY\s*\([^)]*\)/i', '', $sql);
    }

    return strtr($sql, [
      'DATABASE()' => $cn['db'] === '' ? 'NULL' : $q($cn['db']),
      'CONNECTION_ID()' => (string)$cn['id'],
      'VERSION()' => $q(self::VERSION),
      '@@version' => $q(self::VERSION),
      '@@lb_tls' => $cn['tls'] ? '1' : '0',
      '@@lb_plugin' => $q($cn['plugin']),
      '@@lb_full_auth' => $cn['fullAuth'] ? '1' : '0',
      '@@lb_caps' => (string)$cn['caps'],
      '@@character_set_client' => $q(self::CHARSETS[$cn['charset']] ?? (string)$cn['charset']),
    ]);
  }

  /** 「?」の数と位置（文字列・識別子・コメントの中は数えない） */
  private function placeholders(string $sql): array {
    $marks = [];
    $len = strlen($sql);
    for ($i = 0; $i < $len; $i++) {
      $c = $sql[$i];
      if ($c === "'" || $c === '"' || $c === '`') {
        for ($i++; $i < $len && $sql[$i] !== $c; $i++) if ($sql[$i] === '\\') $i++;
      } else if ($c === '-' && ($sql[$i + 1] ?? '') === '-') {
        while ($i < $len && $sql[$i] !== "\n") $i++;
      } else if ($c === '/' && ($sql[$i + 1] ?? '') === '*') {
        $end = strpos($sql, '*/', $i + 2);
        $i = $end === false ? $len : $end + 1;
      } else if ($c === '?') {
        $marks[] = $i;
      }
    }
    return [count($marks), $marks];
  }

  /** 列の説明（宣言された型から、無ければ最初の値から） */
  private function columns(\PDOStatement $st, array $rows): array {
    $cols = [];
    for ($i = 0; $i < $st->columnCount(); $i++) {
      $meta = $st->getColumnMeta($i) ?: [];
      $decl = strtoupper((string)($meta['sqlite:decl_type'] ?? ''));
      $col = ['name' => (string)($meta['name'] ?? ''), 'table' => (string)($meta['table'] ?? ''), 'flags' => 0, 'decimals' => 0, 'charset' => 45];

      if (str_contains($decl, 'UNSIGNED')) $col['flags'] |= 0x20;
      $col['type'] = match (true) {
        $decl === '' => null,
        str_starts_with($decl, 'TINYINT') || str_starts_with($decl, 'BOOL') => self::T_TINY,
        str_starts_with($decl, 'SMALLINT') => self::T_SHORT,
        str_starts_with($decl, 'MEDIUMINT') => self::T_INT24,
        str_starts_with($decl, 'BIGINT') => self::T_LONGLONG,
        str_starts_with($decl, 'INT') => self::T_LONG,
        str_starts_with($decl, 'FLOAT') => self::T_FLOAT,
        str_starts_with($decl, 'DOUBLE') || str_starts_with($decl, 'REAL') => self::T_DOUBLE,
        str_starts_with($decl, 'DECIMAL') || str_starts_with($decl, 'NUMERIC') => self::T_DECIMAL,
        str_starts_with($decl, 'DATETIME') => self::T_DATETIME,
        str_starts_with($decl, 'TIMESTAMP') => self::T_TIMESTAMP,
        str_starts_with($decl, 'DATE') => self::T_DATE,
        str_starts_with($decl, 'TIME') => self::T_TIME,
        str_starts_with($decl, 'YEAR') => self::T_YEAR,
        str_starts_with($decl, 'JSON') => self::T_JSON,
        str_contains($decl, 'BLOB') || str_contains($decl, 'BINARY') => self::T_BLOB,
        str_contains($decl, 'TEXT') => self::T_BLOB,
        str_starts_with($decl, 'CHAR') => self::T_STRING,
        default => self::T_VAR_STRING,
      };
      if (str_contains($decl, 'BLOB') || str_contains($decl, 'BINARY')) {
        $col['charset'] = 63;
        $col['flags'] |= 0x80;
      }
      if (in_array($col['type'], [self::T_FLOAT, self::T_DOUBLE], true)) $col['decimals'] = 31;
      if (preg_match('/\((\d+)(?:,\s*(\d+))?\)/', $decl, $m)) {
        if ($col['type'] === self::T_DECIMAL) $col['decimals'] = (int)($m[2] ?? 0);
        if (in_array($col['type'], [self::T_FLOAT, self::T_DOUBLE], true) && isset($m[2])) $col['decimals'] = (int)$m[2];
        if (in_array($col['type'], [self::T_DATETIME, self::T_TIMESTAMP, self::T_TIME], true)) $col['decimals'] = (int)$m[1];
      }

      if ($col['type'] === null) {
        $col['type'] = self::T_NULL;
        foreach ($rows as $r) {
          if ($r[$i] === null) continue;
          $col['type'] = is_int($r[$i]) ? self::T_LONGLONG : (is_float($r[$i]) ? self::T_DOUBLE : self::T_VAR_STRING);
          if ($col['type'] === self::T_DOUBLE) $col['decimals'] = 31;
          break;
        }
        if ($col['type'] === self::T_NULL && empty($rows)) $col['type'] = self::T_VAR_STRING;
      }
      $cols[] = $col;
    }
    return $cols;
  }

  private function resultSet(int $id, \PDOStatement $st, bool $binary, int $extra = 0): void {
    $rows = $st->fetchAll(\PDO::FETCH_NUM);
    $cols = $this->columns($st, $rows);

    $this->send($id, $this->lenenc(count($cols)));
    foreach ($cols as $c) $this->send($id, $this->colDef($c));
    $this->eof($id);

    foreach ($rows as $r) {
      if ($binary) {
        $bitmap = str_repeat("\0", intdiv(count($cols) + 9, 8));
        $vals = '';
        foreach ($cols as $i => $c) {
          if ($r[$i] === null) {
            $b = $i + 2;
            $bitmap[$b >> 3] = chr(ord($bitmap[$b >> 3]) | (1 << ($b & 7)));
            continue;
          }
          $vals .= $this->binValue($r[$i], $c);
        }
        $this->send($id, "\x00".$bitmap.$vals);
      } else {
        $out = '';
        foreach ($cols as $i => $c) {
          $out .= $r[$i] === null ? "\xFB" : $this->lenencStrOut($this->textValue($r[$i], $c));
        }
        $this->send($id, $out);
      }
    }
    $this->eof($id, $extra);
  }

  private function textValue(mixed $v, array $c): string {
    switch ($c['type']) {
      case self::T_DECIMAL:
        return sprintf('%.'.$c['decimals'].'F', (float)$v);
      case self::T_FLOAT:
        return (string)(float)sprintf('%.6g', (float)$v);
      case self::T_DOUBLE:
        return (string)(float)$v;
      case self::T_DATETIME:
      case self::T_TIMESTAMP:
        $t = $this->splitDateTime((string)$v);
        return sprintf('%04d-%02d-%02d %02d:%02d:%02d', ...array_slice($t, 0, 6)).$this->frac($t[6], $c['decimals']);
      case self::T_TIME:
        $t = $this->splitTime((string)$v);
        return ($t[0] ? '-' : '').sprintf('%02d:%02d:%02d', $t[1], $t[2], $t[3]).$this->frac($t[4], $c['decimals']);
    }
    return (string)$v;
  }

  private function binValue(mixed $v, array $c): string {
    switch ($c['type']) {
      case self::T_TINY:
        return chr((int)$v & 0xFF);
      case self::T_SHORT:
      case self::T_YEAR:
        return pack('v', (int)$v & 0xFFFF);
      case self::T_LONG:
      case self::T_INT24:
        return pack('V', (int)$v & 0xFFFFFFFF);
      case self::T_LONGLONG:
        return pack('P', (int)$v);
      case self::T_FLOAT:
        return pack('g', (float)$v);
      case self::T_DOUBLE:
        return pack('e', (float)$v);
      case self::T_DATE:
      case self::T_DATETIME:
      case self::T_TIMESTAMP:
        $t = $this->splitDateTime((string)$v);
        if ($c['type'] === self::T_DATE || ($t[3] === 0 && $t[4] === 0 && $t[5] === 0 && $t[6] === 0)) {
          if ($t[0] === 0 && $t[1] === 0 && $t[2] === 0) return "\x00";
          return "\x04".pack('v', $t[0]).chr($t[1]).chr($t[2]);
        }
        if ($t[6] === 0) return "\x07".pack('v', $t[0]).chr($t[1]).chr($t[2]).chr($t[3]).chr($t[4]).chr($t[5]);
        return "\x0B".pack('v', $t[0]).chr($t[1]).chr($t[2]).chr($t[3]).chr($t[4]).chr($t[5]).pack('V', $t[6]);
      case self::T_TIME:
        $t = $this->splitTime((string)$v);
        $days = intdiv($t[1], 24);
        $body = chr($t[0]).pack('V', $days).chr($t[1] % 24).chr($t[2]).chr($t[3]);
        if ($t[4] === 0) return ($t[1] === 0 && $t[2] === 0 && $t[3] === 0) ? "\x00" : "\x08".$body;
        return "\x0C".$body.pack('V', $t[4]);
    }
    return $this->lenencStrOut($this->textValue($v, $c));
  }

  /** [年, 月, 日, 時, 分, 秒, マイクロ秒] */
  private function splitDateTime(string $v): array {
    preg_match('/^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2}):(\d{2})(?:\.(\d{1,6}))?)?/', $v, $m);
    return [(int)($m[1] ?? 0), (int)($m[2] ?? 0), (int)($m[3] ?? 0), (int)($m[4] ?? 0), (int)($m[5] ?? 0), (int)($m[6] ?? 0), (int)str_pad($m[7] ?? '0', 6, '0')];
  }

  /** [負数, 時, 分, 秒, マイクロ秒] */
  private function splitTime(string $v): array {
    preg_match('/^(-)?(\d+):(\d{2}):(\d{2})(?:\.(\d{1,6}))?/', $v, $m);
    return [!empty($m[1]) ? 1 : 0, (int)($m[2] ?? 0), (int)($m[3] ?? 0), (int)($m[4] ?? 0), (int)str_pad($m[5] ?? '0', 6, '0')];
  }

  private function frac(int $us, int $dec): string {
    return $dec > 0 && $dec <= 6 ? '.'.substr(sprintf('%06d', $us), 0, $dec) : '';
  }

  private function sqlError(int $id, \PDOException $e): void {
    $msg = $e->getMessage();
    $db = $this->conns[$id]['db'];
    if (preg_match('/no such table: (\S+)/', $msg, $m)) {
      $this->err($id, 1146, '42S02', "Table '{$db}.{$m[1]}' doesn't exist");
    } else if (preg_match('/UNIQUE constraint failed: (\S+)/', $msg, $m)) {
      $this->err($id, 1062, '23000', "Duplicate entry for key '{$m[1]}'");
    } else if (preg_match('/NOT NULL constraint failed: \w+\.(\w+)/', $msg, $m)) {
      $this->err($id, 1048, '23000', "Column '{$m[1]}' cannot be null");
    } else if (preg_match('/no such column: (\S+)/', $msg, $m)) {
      $this->err($id, 1054, '42S22', "Unknown column '{$m[1]}' in 'field list'");
    } else if (str_contains($msg, 'syntax error')) {
      $this->err($id, 1064, '42000', 'You have an error in your SQL syntax; '.preg_replace('/^.*?: \d+ /', '', $msg));
    } else {
      $this->err($id, 1105, 'HY000', preg_replace('/^SQLSTATE\[\w+\]: [^:]*: \d+ /', '', $msg));
    }
  }

  ///////////////
  // パケット

  private function status(int $id, int $extra = 0): int {
    return self::STATUS_AUTOCOMMIT | ($this->conns[$id]['inTrans'] ? self::STATUS_IN_TRANS : 0) | $extra;
  }

  private function ok(int $id, int $affected = 0, int $insertId = 0): void {
    $this->send($id, "\x00".$this->lenenc($affected).$this->lenenc($insertId).pack('v', $this->status($id)).pack('v', 0));
  }

  private function eof(int $id, int $extra = 0): void {
    $this->send($id, "\xFE".pack('v', 0).pack('v', $this->status($id, $extra)));
  }

  private function err(int $id, int $code, string $state, string $msg): void {
    $this->send($id, "\xFF".pack('v', $code).'#'.$state.$msg);
  }

  private function colDef(array $c): string {
    return $this->lenencStrOut('def').$this->lenencStrOut($c['table'] === '' ? '' : 'lb').$this->lenencStrOut($c['table'])
      .$this->lenencStrOut($c['table']).$this->lenencStrOut($c['name']).$this->lenencStrOut($c['name'])
      ."\x0C".pack('v', $c['charset']).pack('V', 255).chr($c['type']).pack('v', $c['flags']).chr($c['decimals'])."\0\0";
  }

  private function lenenc(int $v): string {
    if ($v < 251) return chr($v);
    if ($v < 65536) return "\xFC".pack('v', $v);
    if ($v < 16777216) return "\xFD".substr(pack('V', $v), 0, 3);
    return "\xFE".pack('P', $v);
  }

  private function lenencStrOut(string $s): string {
    return $this->lenenc(strlen($s)).$s;
  }

  private function lenencStr(string $d, int &$pos): string {
    $f = ord($d[$pos] ?? "\0");
    $pos++;
    if ($f < 251) {
      $len = $f;
    } else {
      $size = [0xFC => 2, 0xFD => 3, 0xFE => 8][$f] ?? 0;
      $len = unpack('P', str_pad(substr($d, $pos, $size), 8, "\0"))[1];
      $pos += $size;
    }
    $s = (string)substr($d, $pos, $len);
    $pos += $len;
    return $s;
  }

  private function send(int $id, string $data): void {
    $cn = &$this->conns[$id];
    $pos = 0;
    $total = strlen($data);
    do {
      $len = min($total - $pos, 0xFFFFFF);
      $out = substr(pack('V', $len), 0, 3).chr($cn['seq']).substr($data, $pos, $len);
      $pos += $len;
      $cn['seq'] = ($cn['seq'] + 1) & 0xFF;
      for ($w = 0; $w < strlen($out);) {
        $n = @fwrite($cn['sock'], substr($out, $w, 1048576));
        if ($n === false || $n === 0) return;
        $w += $n;
      }
    } while ($len === 0xFFFFFF);
  }

  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 readRest(int $id, string $head): ?string {
    $data = '';
    while (true) {
      $len = unpack('V', substr($head, 0, 3)."\0")[1];
      $this->conns[$id]['seq'] = (ord($head[3]) + 1) & 0xFF;
      if ($len > 0) {
        $chunk = $this->readRaw($id, $len);
        if ($chunk === null) return null;
        $data .= $chunk;
      }
      if ($len !== 0xFFFFFF) return $data;
      $head = $this->readRaw($id, 4);
      if ($head === null) return null;
    }
  }

  private function recv(int $id): ?string {
    $head = $this->readRaw($id, 4);
    return $head === null ? null : $this->readRest($id, $head);
  }
}