<?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\Db\Convert\Source;
use Std\Lib\Db\Convert\Target;
use Std\Lib\Db\Sqlite\Blob;

/**
 * データベースの変換（MySQL・PostgreSQL・SQLite・SQLのダンプ・LittleBeastのJSONのデータ）
 *
 * Source（DbSource、DumpSource、JsonSource）から読んで、Target（DbTarget、DumpTarget、JsonTarget）に書く。
 * テーブルの定義は共通の形にしてから、書く先の方言のCREATE TABLEにする：
 *   name、columns（name、type、size、scale、unsigned、notNull、default、autoinc、tz、collate）、pk（列の名前）、
 *   indexes（name、columns、unique）
 * 共通の型：int（bits：16、32、64）、real、decimal、varchar、char、text、blob、bool、date、datetime（tz）、time、json、uuid
 * 値：null、int、float、string、bool、Sqlite\Blob（バイナリ）。日付は「Y-m-d H:i:s」（時差が有ればUTC）。
 * 外部キー、ビュー、トリガー、関数は変換しない。
 *
 *   $report = Convert::run(new DbSource(new Db(['driver' => 'pgsql', …])), new DbTarget(new Db(['driver' => 'sqlite', 'path' => 'data/x.sqlite'])));
 */
final class Convert {
  public const TYPES = ['int', 'real', 'decimal', 'varchar', 'char', 'text', 'blob', 'bool', 'date', 'datetime', 'time', 'json', 'uuid'];

  /**
   * 変換する
   *
   * @param Source $src
   * @param Target $dst
   * @param array $opt  tables（変換するテーブルの名前の配列、nullは全部）、drop（書く先の同じ名前のテーブルを消す）、
   *                    progress（fn(string $table, int $rows)）
   * @return array  tables（名前 => 行数）、warnings
   * @throws \Throwable  失敗した場合（書く先は途中までの状態）
   */
  public static function run(Source $src, Target $dst, array $opt = []): array {
    $only = isset($opt['tables']) ? array_map('strtolower', (array)$opt['tables']) : null;
    $progress = $opt['progress'] ?? null;
    $warnings = [];
    $tables = [];
    foreach ($src->tables() as $t) {
      if ($only !== null && !in_array(strtolower($t['name']), $only, true)) continue;
      $tables[strtolower($t['name'])] = self::normalizeTable($t);
    }
    // MySQLのインデックスの有るTEXT・BLOBは長さが必要（VARCHAR・VARBINARYにする）
    if (property_exists($dst, 'dialect') && $dst->dialect === 'mysql' && method_exists($src, 'maxLengths')) {
      foreach ($tables as $k => $t) {
        $indexed = array_map('strtolower', array_merge($t['pk'], ...array_map(fn($i) => $i['columns'], $t['indexes'] ?: [['columns' => []]])));
        $need = false;
        foreach ($t['columns'] as $c) if (in_array(strtolower($c['name']), $indexed, true) && in_array($c['type'], ['text', 'blob'], true)) $need = true;
        if (!$need) continue;
        $lens = $src->maxLengths($t);
        foreach ($t['columns'] as $i => $c) {
          if (in_array(strtolower($c['name']), $indexed, true) && in_array($c['type'], ['text', 'blob'], true)) {
            $len = (int)($lens[$c['name']] ?? 0);
            if ($len > ($c['type'] === 'text' ? 768 : 3072)) $warnings[] = "インデックスには長過ぎる値が有ります：{$t['name']}.{$c['name']}（{$len}）";
            $tables[$k]['columns'][$i]['size'] = max($c['size'] ?? 0, $len);
          }
        }
      }
    }
    $counts = array_fill_keys(array_map(fn($t) => $t['name'], $tables), 0);
    try {
      $dst->begin(!empty($opt['drop']));
      foreach ($tables as $t) $dst->createTable($t);
      $src->rows(array_keys($tables), function(string $name, array $rows) use ($tables, $dst, &$counts, $progress): void {
        $t = $tables[strtolower($name)] ?? null;
        if ($t === null || empty($rows)) return;
        $norm = [];
        foreach ($rows as $r) $norm[] = self::normalizeRow($t, $r);
        try {
          $dst->insertRows($t, $norm);
        } catch (DbException $e) {
          throw new DbException("{$t['name']}の行（".($counts[$t['name']] + 1).'行目から）：'.$e->getMessage(), $e->getCode(), $e->sqlState, $e);
        }
        $counts[$t['name']] += count($norm);
        if ($progress) $progress($t['name'], $counts[$t['name']]);
      });
      foreach ($tables as $t) $dst->finishTable($t);
      $dst->end();
    } catch (\Throwable $e) {
      $dst->abort();
      throw $e;
    }
    return ['tables' => $counts, 'warnings' => array_values(array_unique(array_merge($warnings, $src->warnings(), $dst->warnings())))];
  }

  /** テーブルの定義を確認して、足りない値を補う */
  public static function normalizeTable(array $t): array {
    $t += ['pk' => [], 'indexes' => []];
    $names = [];
    foreach ($t['columns'] as $i => $c) {
      $c += ['size' => null, 'scale' => null, 'unsigned' => false, 'notNull' => false, 'default' => null, 'autoinc' => false, 'tz' => false, 'collate' => null, 'array' => false, 'binary' => false];
      if ($c['type'] === 'int') $c['size'] ??= 64;
      if (!in_array($c['type'], self::TYPES, true)) throw new \InvalidArgumentException("不明な型：{$c['type']}（{$t['name']}.{$c['name']}）");
      $t['columns'][$i] = $c;
      $names[strtolower($c['name'])] = true;
    }
    foreach ($t['pk'] as $n) if (!isset($names[strtolower($n)])) throw new \InvalidArgumentException("主キーの列がありません：{$t['name']}.{$n}");
    foreach ($t['indexes'] as $k => $idx) {
      $t['indexes'][$k] += ['unique' => false];
      foreach ($idx['columns'] as $n) if (!isset($names[strtolower($n)])) throw new \InvalidArgumentException("インデックスの列がありません：{$t['name']}.{$n}");
    }
    return $t;
  }

  /**
   * 行の値を共通の形にする（型に合わせる）
   *
   * @param array $t  テーブル
   * @param array $row  列の順番の値
   * @return array
   */
  public static function normalizeRow(array $t, array $row): array {
    $out = [];
    foreach ($t['columns'] as $i => $c) {
      $v = $row[$i] ?? null;
      $out[] = $v === null ? null : self::normalizeValue($v, $c);
    }
    return $out;
  }

  public static function normalizeValue(mixed $v, array $c): mixed {
    if (is_array($v)) $v = json_encode($v, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    switch ($c['type']) {
      case 'bool':
        if (is_bool($v)) return $v;
        if (is_string($v)) {
          $l = strtolower(trim($v));
          if (in_array($l, ['t', 'true', 'y', 'yes', 'on', '1'], true)) return true;
          if (in_array($l, ['f', 'false', 'n', 'no', 'off', '0', ''], true)) return false;
        }
        return (bool)(is_object($v) ? (string)$v : $v);
      case 'int':
        if (is_int($v)) return $v;
        if (is_bool($v)) return (int)$v;
        if (is_float($v) && floor($v) === $v && abs($v) < 9.2e18) return (int)$v;
        if (is_string($v) && preg_match('/^\s*[+-]?\d+\s*$/', $v) && (string)(int)trim($v) === ltrim(trim($v), '+')) return (int)trim($v);
        return is_object($v) ? (string)$v : $v;
      case 'real':
        if (is_float($v) || is_int($v)) return (float)$v;
        if (is_string($v) && is_numeric(trim($v))) return (float)trim($v);
        if (is_string($v) && in_array(strtolower($v), ['nan', 'infinity', '-infinity', 'inf', '-inf'], true)) return match (strtolower($v)) { 'nan' => NAN, 'infinity', 'inf' => INF, default => -INF };
        return is_object($v) ? (string)$v : $v;
      case 'decimal':
        if (is_float($v)) return rtrim(rtrim(sprintf('%.'.max(0, (int)($c['scale'] ?? 10)).'F', $v), '0'), '.') ?: '0';
        return is_bool($v) ? (string)(int)$v : (string)$v;
      case 'blob':
        if ($v instanceof Blob) return $v;
        return new Blob(is_bool($v) ? (string)(int)$v : (string)$v);
      case 'datetime':
      case 'date':
      case 'time':
        return self::normalizeTime((string)$v, $c['type']);
      default:
        if ($v instanceof Blob) return $v->data;
        if (is_bool($v)) return $c['type'] === 'json' ? ($v ? 'true' : 'false') : (string)(int)$v;
        if (is_float($v)) return Sqlite\Value::realToText($v);
        return (string)$v;
    }
  }

  /** 日付・時間の文字列（時差が有ればUTCにする、0000-00-00はnull） */
  public static function normalizeTime(string $v, string $type): ?string {
    $v = trim($v);
    if ($v === '' || str_starts_with($v, '0000-00-00')) return $type === 'time' ? $v : null;
    if ($type === 'time') return $v;
    if (preg_match('/^(\d{4}-\d{2}-\d{2})(?:[ T](\d{2}:\d{2}(?::\d{2})?)(\.\d+)?)?\s*(Z|[+-]\d{2}(?::?\d{2})?)?$/i', $v, $m)) {
      if ($type === 'date') return $m[1];
      $time = ($m[2] ?? '') === '' ? '00:00:00' : (strlen($m[2]) === 5 ? $m[2].':00' : $m[2]);
      // 「.000000」（MySQLのDATETIME(6)）は無し
      $frac = rtrim($m[3] ?? '', '0');
      if ($frac === '.') $frac = '';
      if (!empty($m[4])) {
        $d = new \DateTimeImmutable($m[1].' '.$time.' '.(strtoupper($m[4]) === 'Z' ? '+00:00' : (strlen($m[4]) === 3 ? $m[4].':00' : $m[4])));
        $d = $d->setTimezone(new \DateTimeZone('UTC'));
        return $d->format('Y-m-d H:i:s').$frac;
      }
      return $m[1].' '.$time.$frac;
    }
    // 数値（UNIXの秒）
    if (is_numeric($v)) return gmdate($type === 'date' ? 'Y-m-d' : 'Y-m-d H:i:s', (int)$v);
    return $v;
  }

  ///////////////
  // 方言

  /** 識別子を囲む */
  public static function quote(string $dialect, string $name): string {
    if ($dialect === 'mysql') return '`'.str_replace('`', '``', $name).'`';
    return '"'.str_replace('"', '""', $name).'"';
  }

  /**
   * 列の型（方言のSQL）
   */
  public static function columnType(string $dialect, array $c, bool $indexed = false): string {
    $size = $c['size'];
    switch ($dialect) {
      case 'mysql':
        return match ($c['type']) {
          'int' => ($c['size'] <= 16 ? 'SMALLINT' : ($c['size'] <= 32 ? 'INT' : 'BIGINT')).($c['unsigned'] ? ' UNSIGNED' : ''),
          'real' => 'DOUBLE',
          'decimal' => $size ? 'DECIMAL('.min(65, $size).','.min(30, (int)$c['scale']).')' : 'DECIMAL(65,30)',
          'varchar' => $size && $size <= 16383 ? "VARCHAR({$size})" : ($indexed ? 'VARCHAR(768)' : 'LONGTEXT'),
          'char' => $size && $size <= 255 ? "CHAR({$size})" : 'VARCHAR(255)',
          'text' => $indexed ? 'VARCHAR('.max(255, min(768, (int)$size)).')' : 'LONGTEXT',
          'blob' => $indexed ? 'VARBINARY('.max(255, min(3072, (int)$size)).')' : 'LONGBLOB',
          'bool' => 'TINYINT(1)',
          'date' => 'DATE',
          'datetime' => 'DATETIME(6)',
          'time' => 'TIME(6)',
          'json' => 'JSON',
          'uuid' => 'CHAR(36)',
        };
      case 'pgsql':
        return match ($c['type']) {
          'int' => $c['size'] <= 16 ? ($c['unsigned'] ? 'INTEGER' : 'SMALLINT') : ($c['size'] <= 32 ? ($c['unsigned'] ? 'BIGINT' : 'INTEGER') : ($c['unsigned'] ? 'NUMERIC(20)' : 'BIGINT')),
          'real' => 'DOUBLE PRECISION',
          'decimal' => $size ? "NUMERIC({$size},".(int)$c['scale'].')' : 'NUMERIC',
          'varchar' => $size ? "VARCHAR({$size})" : 'TEXT',
          'char' => $size ? "CHAR({$size})" : 'TEXT',
          'text' => 'TEXT',
          'blob' => 'BYTEA',
          'bool' => 'BOOLEAN',
          'date' => 'DATE',
          'datetime' => $c['tz'] ? 'TIMESTAMPTZ' : 'TIMESTAMP',
          'time' => 'TIME',
          'json' => !empty($c['binary']) ? 'JSONB' : 'JSON',
          'uuid' => 'UUID',
        };
      default:
        return match ($c['type']) {
          'int' => 'INTEGER',
          'real' => 'REAL',
          'decimal' => $size ? "NUMERIC({$size},".(int)$c['scale'].')' : 'NUMERIC',
          'varchar' => $size ? "VARCHAR({$size})" : 'TEXT',
          'char' => $size ? "CHAR({$size})" : 'TEXT',
          'text' => 'TEXT',
          'blob' => 'BLOB',
          'bool' => 'BOOLEAN',
          'date' => 'DATE',
          'datetime' => $c['tz'] ? 'TIMESTAMPTZ' : 'DATETIME',
          'time' => 'TIME',
          'json' => 'JSON',
          'uuid' => 'UUID',
        };
    }
  }

  /**
   * CREATE TABLE（インデックスは別）
   *
   * @param string $dialect  mysql、pgsql、sqlite
   * @param array $t  テーブル
   * @param array $warnings  警告を足す
   * @return string
   */
  public static function createTable(string $dialect, array $t, array &$warnings = []): string {
    $q = fn(string $n) => self::quote($dialect, $n);
    $indexed = [];
    foreach ($t['pk'] as $n) $indexed[strtolower($n)] = true;
    foreach ($t['indexes'] as $idx) foreach ($idx['columns'] as $n) $indexed[strtolower($n)] = true;
    $single = count($t['pk']) === 1 ? strtolower($t['pk'][0]) : null;
    $lines = [];
    $pkInline = false;
    foreach ($t['columns'] as $c) {
      $isPk = $single !== null && strtolower($c['name']) === $single;
      $auto = $c['autoinc'] && $isPk && $c['type'] === 'int';
      if ($c['autoinc'] && !$auto) $warnings[] = "自動の番号は主キーの整数の列だけです：{$t['name']}.{$c['name']}";
      if ($dialect === 'sqlite' && $auto) {
        $lines[] = $q($c['name']).' INTEGER PRIMARY KEY';
        $pkInline = true;
        continue;
      }
      if ($dialect === 'pgsql' && $auto) {
        $lines[] = $q($c['name']).' '.($c['size'] <= 32 && !$c['unsigned'] ? 'SERIAL' : 'BIGSERIAL').' PRIMARY KEY';
        $pkInline = true;
        continue;
      }
      $line = $q($c['name']).' '.self::columnType($dialect, $c, isset($indexed[strtolower($c['name'])]));
      if ($dialect === 'mysql' && $c['collate'] && in_array($c['type'], ['varchar', 'char', 'text'], true)) $line .= ' COLLATE '.$c['collate'];
      if ($c['notNull'] || ($isPk && $dialect !== 'sqlite')) $line .= ' NOT NULL';
      if ($dialect === 'mysql' && $auto) $line .= ' AUTO_INCREMENT';
      if ($c['default'] !== null) {
        $d = self::defaultSql($dialect, $c);
        if ($d === null) $warnings[] = "DEFAULTを変換できません：{$t['name']}.{$c['name']}";
        else $line .= ' DEFAULT '.$d;
      }
      $lines[] = $line;
    }
    if (!$pkInline && $t['pk']) $lines[] = 'PRIMARY KEY ('.implode(', ', array_map($q, $t['pk'])).')';
    $sql = 'CREATE TABLE '.$q($t['name'])." (\n  ".implode(",\n  ", $lines)."\n)";
    if ($dialect === 'mysql') $sql .= ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE='.($t['collate'] ?? 'utf8mb4_bin');
    return $sql;
  }

  /** DEFAULTの値（変換できなければnull） */
  private static function defaultSql(string $dialect, array $c): ?string {
    $d = $c['default'];
    if (array_key_exists('now', $d)) {
      if (!in_array($c['type'], ['datetime', 'date', 'time'], true)) return null;
      if ($dialect === 'mysql' && $c['type'] !== 'datetime') return null;
      return $dialect === 'mysql' ? 'CURRENT_TIMESTAMP(6)' : ($c['type'] === 'date' ? 'CURRENT_DATE' : ($c['type'] === 'time' ? 'CURRENT_TIME' : 'CURRENT_TIMESTAMP'));
    }
    if (!array_key_exists('value', $d)) return null;
    $v = $d['value'];
    if ($v === null) return 'NULL';
    // MySQLのTEXT・BLOBにはDEFAULTを付けられない
    if ($dialect === 'mysql' && in_array($c['type'], ['text', 'blob', 'json'], true)) return null;
    try {
      $v = self::normalizeValue($v, $c);
    } catch (\Throwable $e) {
      return null;
    }
    if ($v === null) return 'NULL';
    return self::literal($dialect, $v, $c);
  }

  /**
   * 値のSQLのリテラル
   */
  public static function literal(string $dialect, mixed $v, ?array $c = null): string {
    if ($v === null) return 'NULL';
    if (is_bool($v)) return $dialect === 'pgsql' ? ($v ? 'TRUE' : 'FALSE') : ($v ? '1' : '0');
    if (is_int($v)) return (string)$v;
    if (is_float($v)) {
      if (is_nan($v) || is_infinite($v)) {
        if ($dialect === 'pgsql') return "'".(is_nan($v) ? 'NaN' : ($v > 0 ? 'Infinity' : '-Infinity'))."'";
        return $dialect === 'sqlite' && is_infinite($v) ? ($v > 0 ? '9e999' : '-9e999') : 'NULL';
      }
      $s = var_export($v, true);
      return str_contains($s, '.') || str_contains($s, 'E') ? $s : $s.'.0';
    }
    if ($v instanceof Blob) {
      $hex = bin2hex($v->data);
      if ($dialect === 'pgsql') return "'\\x{$hex}'::bytea";
      return $dialect === 'mysql' ? ($hex === '' ? "''" : "0x{$hex}") : "X'{$hex}'";
    }
    $s = (string)$v;
    if ($dialect === 'mysql') return "'".strtr($s, ["\\" => "\\\\", "'" => "\\'", "\0" => "\\0", "\n" => "\\n", "\r" => "\\r", "\x1A" => "\\Z"])."'";
    return "'".str_replace("'", "''", $s)."'";
  }

  /** インデックスの名前（PostgreSQL・SQLiteはデータベース全体で一意なので、テーブルの名前を付ける） */
  public static function indexName(string $dialect, array $t, array $idx): string {
    $n = $idx['name'] !== '' && $idx['name'] !== null ? $idx['name'] : $t['name'].'_'.implode('_', $idx['columns']).'_idx';
    if ($dialect !== 'mysql' && stripos($n, $t['name']) !== 0) $n = $t['name'].'_'.$n;
    return $n;
  }

  /** CREATE INDEX */
  public static function createIndex(string $dialect, array $t, array $idx): string {
    $q = fn(string $n) => self::quote($dialect, $n);
    return 'CREATE '.($idx['unique'] ? 'UNIQUE ' : '').'INDEX '.$q(self::indexName($dialect, $t, $idx)).' ON '.$q($t['name'])
      .' ('.implode(', ', array_map($q, $idx['columns'])).')';
  }

  ///////////////
  // 型の名前から共通の型

  /**
   * 型の名前（どの方言でも）を共通の型にする
   *
   * @param string $type  例：varchar(255)、int unsigned、timestamp with time zone、TINYINT(1)
   * @return array  type、size、scale、unsigned、tz
   */
  public static function parseType(string $type, string $dialect = ''): array {
    $t = strtolower(trim($type));
    $t = preg_replace('/\s+/', ' ', $t);
    $unsigned = (bool)preg_match('/\bunsigned\b/', $t);
    $t = trim(preg_replace('/\b(unsigned|zerofill|signed)\b/', '', $t));
    $array = str_ends_with($t, '[]');
    if ($array) return ['type' => 'json', 'size' => null, 'scale' => null, 'unsigned' => false, 'tz' => false, 'array' => true];
    $args = [];
    if (preg_match('/\(([^)]*)\)/', $t, $m)) $args = array_map('trim', explode(',', $m[1]));
    $base = trim(preg_replace('/\([^)]*\)/', '', $t));
    $size = isset($args[0]) && is_numeric($args[0]) ? (int)$args[0] : null;
    $scale = isset($args[1]) && is_numeric($args[1]) ? (int)$args[1] : null;
    $r = fn(string $type, ?int $size = null, ?int $scale = null, bool $tz = false) => ['type' => $type, 'size' => $size, 'scale' => $scale, 'unsigned' => $unsigned, 'tz' => $tz];
    switch (true) {
      case $base === 'tinyint' && $size === 1:
      case in_array($base, ['bool', 'boolean', 'bit'], true) && ($base !== 'bit' || ($size ?? 1) === 1):
        return $r('bool');
      case in_array($base, ['tinyint', 'smallint', 'int2', 'smallserial', 'serial2'], true):
        return $r('int', $base === 'tinyint' && $unsigned ? 16 : ($unsigned ? 32 : 16));
      case in_array($base, ['mediumint', 'int', 'integer', 'int4', 'serial', 'serial4'], true):
        return $r('int', $unsigned || $base === 'integer' && $dialect === 'sqlite' ? 64 : 32);
      case in_array($base, ['bigint', 'int8', 'bigserial', 'serial8'], true) || $base === 'year':
        return $r('int', $base === 'year' ? 16 : 64);
      case in_array($base, ['float', 'double', 'double precision', 'real', 'float4', 'float8'], true):
        return $r('real');
      case in_array($base, ['decimal', 'numeric', 'dec', 'fixed', 'money'], true):
        return $r('decimal', $size, $scale);
      case in_array($base, ['varchar', 'character varying', 'nvarchar', 'varchar2', 'varying character', 'native character', 'nchar varying'], true):
        return $r('varchar', $size);
      case in_array($base, ['char', 'character', 'nchar', 'bpchar'], true):
        return $r('char', $size ?? 1);
      case in_array($base, ['text', 'tinytext', 'mediumtext', 'longtext', 'clob', 'citext', 'name', 'string', 'enum', 'set', 'inet', 'cidr', 'macaddr', 'xml', 'interval', 'tsvector'], true):
        return $r('text');
      case in_array($base, ['blob', 'tinyblob', 'mediumblob', 'longblob', 'bytea', 'binary', 'varbinary', 'bit varying', 'image'], true) || $base === 'bit':
        return $r('blob', in_array($base, ['binary', 'varbinary'], true) ? $size : null);
      case $base === 'date':
        return $r('date');
      case in_array($base, ['datetime', 'timestamp', 'timestamp without time zone', 'smalldatetime', 'datetime2'], true):
        return $r('datetime');
      case in_array($base, ['timestamptz', 'timestamp with time zone', 'datetimeoffset'], true):
        return $r('datetime', null, null, true);
      case in_array($base, ['time', 'time without time zone', 'timetz', 'time with time zone'], true):
        return $r('time');
      case in_array($base, ['json', 'jsonb'], true):
        return $r('json') + ['binary' => $base === 'jsonb'];
      case $base === 'uuid' || $base === 'uniqueidentifier':
        return $r('uuid');
    }
    // SQLiteの親和性の規則
    if ($base === '') return $r($dialect === 'sqlite' ? 'text' : 'text');
    if (str_contains($base, 'int')) return $r('int', 64);
    if (str_contains($base, 'char') || str_contains($base, 'clob') || str_contains($base, 'text')) return $r('text');
    if (str_contains($base, 'blob')) return $r('blob');
    if (str_contains($base, 'real') || str_contains($base, 'floa') || str_contains($base, 'doub')) return $r('real');
    if (str_contains($base, 'date') || str_contains($base, 'time')) return $r('datetime');
    return $r($dialect === 'sqlite' ? 'decimal' : 'text');
  }

  /**
   * DEFAULTの式を共通の形にする
   *
   * @param string|null $expr  SQLの式（例：'abc'、0、CURRENT_TIMESTAMP、now()、nextval('seq'::regclass)）
   * @return array|null  ['value' => 値]、['now' => true]、['seq' => true]（自動の番号）、変換できなければnull
   */
  public static function parseDefault(?string $expr): ?array {
    if ($expr === null) return null;
    $e = trim($expr);
    while (preg_match('/^\((.*)\)$/s', $e, $m)) $e = trim($m[1]);
    // キャスト（'abc'::character varying）
    $e = preg_replace("/::[a-z_ ]+(\\([^)]*\\))?(\\[\\])?$/i", '', $e);
    if (preg_match('/^nextval\(/i', $e)) return ['seq' => true];
    if (preg_match('/^(current_timestamp|now|localtimestamp|current_date|current_time|getdate|sysdate)(\s*\(\s*\d*\s*\))?$/i', $e)) return ['now' => true];
    if (preg_match("/^datetime\\s*\\(\\s*'now'/i", $e)) return ['now' => true];
    if (strcasecmp($e, 'null') === 0) return ['value' => null];
    if (strcasecmp($e, 'true') === 0) return ['value' => true];
    if (strcasecmp($e, 'false') === 0) return ['value' => false];
    if (preg_match('/^[+-]?(\d+(\.\d*)?|\.\d+)([eE][+-]?\d+)?$/', $e)) return ['value' => preg_match('/^[+-]?\d+$/', $e) ? (int)$e : (float)$e];
    if (preg_match("/^[Ee]?'((?:[^']|'')*)'$/s", $e, $m)) {
      $s = str_replace("''", "'", $m[1]);
      if ($e[0] === 'E' || $e[0] === 'e') $s = stripcslashes($s);
      return ['value' => $s];
    }
    if (preg_match('/^b\'([01]*)\'$/i', $e, $m)) return ['value' => bindec($m[1]) !== 0];
    return null;
  }
}