<?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\Sqlite;

use Std\Lib\Db\DbException;

/**
 * SQLの関数（スカラー、日付、JSON）と集約関数
 */
final class Func {
  public const AGGREGATES = ['count', 'sum', 'total', 'avg', 'min', 'max', 'group_concat', 'string_agg'];

  private static array $likeCache = [];

  /** 集約関数か（minとmaxは引数が1つの場合だけ） */
  public static function isAggregate(string $name, int $argc, bool $star): bool {
    if ($name === 'count') return $star || $argc <= 1;
    if ($name === 'min' || $name === 'max') return $argc === 1;
    return in_array($name, self::AGGREGATES, true);
  }

  /**
   * スカラー関数
   *
   * @param string $name  小文字の名前
   * @param array $a  引数の値
   * @param Engine $eng  last_insert_rowid()等
   * @param array $colls  引数の照合順序（min・max用）
   * @return mixed
   */
  public static function call(string $name, array $a, Engine $eng, array $colls = []): mixed {
    $n = count($a);
    $need = function(int $min, ?int $max = null) use ($n, $name): void {
      $max ??= $min;
      if ($n < $min || ($max >= 0 && $n > $max)) throw new DbException("関数{$name}()の引数の数が違います", 1, 'HY000');
    };
    switch ($name) {
      case 'abs':
        $need(1);
        $v = $a[0];
        if ($v === null) return null;
        $v = Value::toNumber($v);
        if ($v === PHP_INT_MIN) throw new DbException('整数のオーバーフロー', 1, 'HY000');
        return is_int($v) ? abs($v) : abs((float)$v);
      case 'changes':
        return $eng->changes;
      case 'total_changes':
        return $eng->totalChanges;
      case 'last_insert_rowid':
        return $eng->lastInsertRowid;
      case 'char':
        return implode('', array_map(fn($c) => mb_chr((int)Value::toInt($c) ?: 0xFFFD, 'UTF-8') ?: '', $a));
      case 'coalesce':
        if ($n < 2) throw new DbException('関数coalesce()の引数の数が違います', 1, 'HY000');
        foreach ($a as $v) if ($v !== null) return $v;
        return null;
      case 'ifnull':
        $need(2);
        return $a[0] ?? $a[1];
      case 'iif':
        $need(3);
        return Value::truth($a[0]) ? $a[1] : $a[2];
      case 'nullif':
        $need(2);
        return Record::compare($a[0], $a[1], $colls[0] ?? 'BINARY') === 0 ? null : $a[0];
      case 'concat':
        return implode('', array_map(fn($v) => (string)Value::toText($v), $a));
      case 'concat_ws':
        if ($n < 1) throw new DbException('関数concat_ws()の引数の数が違います', 1, 'HY000');
        if ($a[0] === null) return null;
        $sep = Value::toText($a[0]);
        return implode($sep, array_map(fn($v) => Value::toText($v), array_filter(array_slice($a, 1), fn($v) => $v !== null)));
      case 'hex':
        $need(1);
        if ($a[0] === null) return '';
        return strtoupper(bin2hex((string)Value::toText($a[0])));
      case 'unhex':
        $need(1, 2);
        if ($a[0] === null) return null;
        $s = (string)Value::toText($a[0]);
        if ($n === 2 && $a[1] !== null) $s = str_replace(str_split((string)Value::toText($a[1])), '', $s);
        if (strlen($s) % 2 || ($s !== '' && !ctype_xdigit($s))) return null;
        return new Blob((string)hex2bin($s));
      case 'instr':
        $need(2);
        if ($a[0] === null || $a[1] === null) return null;
        if ($a[0] instanceof Blob || $a[1] instanceof Blob) {
          $p = strpos((string)$a[0], (string)$a[1]);
          return $p === false ? 0 : $p + 1;
        }
        $p = mb_strpos((string)Value::toText($a[0]), (string)Value::toText($a[1]), 0, 'UTF-8');
        return $p === false ? 0 : $p + 1;
      case 'length':
        $need(1);
        if ($a[0] === null) return null;
        if ($a[0] instanceof Blob) return strlen($a[0]->data);
        return mb_strlen((string)Value::toText($a[0]), 'UTF-8');
      case 'octet_length':
        $need(1);
        return $a[0] === null ? null : strlen((string)Value::toText($a[0]));
      case 'likely':
      case 'unlikely':
        $need(1);
        return $a[0];
      case 'likelihood':
        $need(2);
        return $a[0];
      case 'lower':
        $need(1);
        return $a[0] === null ? null : strtolower((string)Value::toText($a[0]));
      case 'upper':
        $need(1);
        return $a[0] === null ? null : strtoupper((string)Value::toText($a[0]));
      case 'trim':
      case 'ltrim':
      case 'rtrim':
        $need(1, 2);
        if ($a[0] === null || ($n === 2 && $a[1] === null)) return null;
        $chars = $n === 2 ? (string)Value::toText($a[1]) : ' ';
        $s = (string)Value::toText($a[0]);
        if ($chars === '') return $s;
        $set = preg_quote($chars, '/');
        $u = mb_check_encoding($chars, 'UTF-8') && mb_check_encoding($s, 'UTF-8') ? 'u' : '';
        if ($name !== 'rtrim') $s = preg_replace("/^[{$set}]+/{$u}", '', $s);
        if ($name !== 'ltrim') $s = preg_replace("/[{$set}]+$/{$u}", '', $s);
        return $s;
      case 'max':
      case 'min':
        if ($n < 2) throw new DbException("関数{$name}()の引数の数が違います", 1, 'HY000');
        $best = null;
        foreach ($a as $i => $v) {
          if ($v === null) return null;
          if ($best === null || ($name === 'max' ? Record::compare($v, $best, $colls[$i] ?? 'BINARY') > 0 : Record::compare($v, $best, $colls[$i] ?? 'BINARY') < 0)) $best = $v;
        }
        return $best;
      case 'printf':
      case 'format':
        if ($n < 1) throw new DbException("関数{$name}()の引数の数が違います", 1, 'HY000');
        return $a[0] === null ? null : self::printf((string)Value::toText($a[0]), array_slice($a, 1));
      case 'quote':
        $need(1);
        return Value::quote($a[0]);
      case 'random':
        return random_int(PHP_INT_MIN, PHP_INT_MAX);
      case 'randomblob':
        $need(1);
        return new Blob(random_bytes(max(1, (int)Value::toInt($a[0]))));
      case 'zeroblob':
        $need(1);
        return new Blob(str_repeat("\0", max(0, (int)Value::toInt($a[0]))));
      case 'replace':
        $need(3);
        if ($a[0] === null || $a[1] === null || $a[2] === null) return null;
        $from = (string)Value::toText($a[1]);
        return $from === '' ? $a[0] : str_replace($from, (string)Value::toText($a[2]), (string)Value::toText($a[0]));
      case 'round':
        $need(1, 2);
        if ($a[0] === null) return null;
        $d = $n === 2 ? (int)Value::toInt($a[1]) : 0;
        return round((float)Value::toNumber($a[0]), max(0, min(30, $d)));
      case 'sign':
        $need(1);
        $v = $a[0];
        if ($v === null || (is_string($v) && Value::parseNumber($v, true) === null) || $v instanceof Blob) return null;
        $v = Value::toNumber($v);
        return $v > 0 ? 1 : ($v < 0 ? -1 : 0);
      case 'sqlite_version':
        return '3.45.1';
      case 'substr':
      case 'substring':
        $need(2, 3);
        if ($a[0] === null || $a[1] === null || ($n === 3 && $a[2] === null)) return null;
        return self::substr($a[0], (int)Value::toInt($a[1]), $n === 3 ? (int)Value::toInt($a[2]) : null);
      case 'typeof':
        $need(1);
        return Value::type($a[0]);
      case 'unicode':
        $need(1);
        if ($a[0] === null) return null;
        $s = (string)Value::toText($a[0]);
        return $s === '' ? null : mb_ord(mb_substr($s, 0, 1, 'UTF-8'), 'UTF-8');
      case 'glob':
        $need(2);
        return $a[0] === null || $a[1] === null ? null : (int)self::glob((string)Value::toText($a[1]), (string)Value::toText($a[0]));
      case 'like':
        $need(2, 3);
        if ($a[0] === null || $a[1] === null) return null;
        return (int)self::like((string)Value::toText($a[1]), (string)Value::toText($a[0]), $n === 3 ? Value::toText($a[2]) : null);
      case 'current_timestamp':
        return gmdate('Y-m-d H:i:s');
      case 'current_date':
        return gmdate('Y-m-d');
      case 'current_time':
        return gmdate('H:i:s');
      case 'date':
      case 'time':
      case 'datetime':
      case 'julianday':
      case 'unixepoch':
      case 'strftime':
      case 'timediff':
        return self::dateFunc($name, $a);
      case 'json':
        $need(1);
        $d = self::jsonDecode($a[0]);
        return json_encode($d, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRESERVE_ZERO_FRACTION);
      case 'json_valid':
        $need(1);
        if ($a[0] === null) return null;
        json_decode((string)Value::toText($a[0]));
        return (int)(json_last_error() === JSON_ERROR_NONE);
      case 'json_quote':
        $need(1);
        return self::jsonText(self::toJsonValue($a[0]));
      case 'json_array':
        return self::jsonText(array_map([self::class, 'toJsonValue'], $a));
      case 'json_object':
        if ($n % 2) throw new DbException('json_object()の引数は偶数です', 1, 'HY000');
        $o = new \stdClass();
        for ($i = 0; $i < $n; $i += 2) $o->{(string)Value::toText($a[$i])} = self::toJsonValue($a[$i + 1]);
        return self::jsonText($o);
      case 'json_extract':
      case 'json_extract_text':
      case 'json_extract_json':
        if ($n < 2) throw new DbException('json_extract()の引数の数が違います', 1, 'HY000');
        if ($a[0] === null) return null;
        $doc = self::jsonDecode($a[0]);
        $out = [];
        foreach (array_slice($a, 1) as $path) {
          $found = true;
          $v = self::jsonPath($doc, $path, $name !== 'json_extract', $found);
          if ($name === 'json_extract_json') return $found ? self::jsonText($v) : null;
          $out[] = $v;
        }
        if ($n === 2) {
          $v = $out[0];
          if (is_array($v) || is_object($v)) return self::jsonText($v);
          if (is_bool($v)) return (int)$v;
          return $v;
        }
        return self::jsonText($out);
      case 'json_type':
        $need(1, 2);
        if ($a[0] === null) return null;
        $doc = self::jsonDecode($a[0]);
        $found = true;
        $v = $n === 2 ? self::jsonPath($doc, $a[1], false, $found) : $doc;
        if (!$found) return null;
        return match (true) {
          $v === null => 'null', $v === true => 'true', $v === false => 'false', is_int($v) => 'integer', is_float($v) => 'real',
          is_string($v) => 'text', is_array($v) => 'array', default => 'object',
        };
      case 'json_array_length':
        $need(1, 2);
        if ($a[0] === null) return null;
        $doc = self::jsonDecode($a[0]);
        $found = true;
        $v = $n === 2 ? self::jsonPath($doc, $a[1], false, $found) : $doc;
        return is_array($v) ? count($v) : 0;
    }
    throw new DbException("関数がありません：{$name}()", 1, 'HY000');
  }

  private static function substr(mixed $v, int $start, ?int $len): mixed {
    $blob = $v instanceof Blob;
    $s = $blob ? $v->data : (string)Value::toText($v);
    $total = $blob ? strlen($s) : mb_strlen($s, 'UTF-8');
    // SQLiteの規則：1から、0は最初の前、負は最後から
    if ($start < 0) {
      $start = $total + $start + 1;
      if ($start < 1) {
        if ($len !== null) $len += $start - 1;
        $start = 1;
      }
    } else if ($start === 0) {
      $start = 1;
      if ($len !== null) $len--;
    }
    if ($len !== null && $len < 0) {
      $start += $len;
      $len = -$len;
      if ($start < 1) {
        $len += $start - 1;
        $start = 1;
      }
    }
    $len = $len === null ? null : max(0, $len);
    $r = $blob ? (string)substr($s, $start - 1, $len ?? PHP_INT_MAX) : mb_substr($s, $start - 1, $len, 'UTF-8');
    return $blob ? new Blob($r) : $r;
  }

  /** LIKE（ASCIIは大文字と小文字を区別しない） */
  public static function like(string $pattern, string $s, ?string $escape = null): bool {
    $key = 'L'.$escape."\0".$pattern;
    if (!isset(self::$likeCache[$key])) {
      if (count(self::$likeCache) > 500) self::$likeCache = [];
      $re = '';
      $u = mb_check_encoding($pattern, 'UTF-8');
      $chars = $u ? mb_str_split($pattern, 1, 'UTF-8') : str_split($pattern);
      for ($i = 0; $i < count($chars); $i++) {
        $c = $chars[$i];
        if ($escape !== null && $c === $escape) {
          $re .= preg_quote(strtolower($chars[++$i] ?? ''), '/');
        } else if ($c === '%') {
          $re .= '.*';
        } else if ($c === '_') {
          $re .= '.';
        } else {
          $re .= preg_quote(strtolower($c), '/');
        }
      }
      self::$likeCache[$key] = '/^'.$re.'$/s'.($u ? 'u' : '');
    }
    return (bool)@preg_match(self::$likeCache[$key], strtolower($s));
  }

  /** GLOB（大文字と小文字を区別する） */
  public static function glob(string $pattern, string $s): bool {
    $key = 'G'.$pattern;
    if (!isset(self::$likeCache[$key])) {
      $re = '';
      $u = mb_check_encoding($pattern, 'UTF-8');
      $chars = $u ? mb_str_split($pattern, 1, 'UTF-8') : str_split($pattern);
      for ($i = 0; $i < count($chars); $i++) {
        $c = $chars[$i];
        if ($c === '*') {
          $re .= '.*';
        } else if ($c === '?') {
          $re .= '.';
        } else if ($c === '[') {
          $j = $i + 1;
          $set = '';
          if (($chars[$j] ?? '') === '^') {
            $set .= '^';
            $j++;
          }
          if (($chars[$j] ?? '') === ']') {
            $set .= '\]';
            $j++;
          }
          while ($j < count($chars) && $chars[$j] !== ']') {
            $set .= $chars[$j] === '-' ? '-' : preg_quote($chars[$j], '/');
            $j++;
          }
          if ($j >= count($chars)) {
            $re .= '\[';
            continue;
          }
          $re .= '['.$set.']';
          $i = $j;
        } else {
          $re .= preg_quote($c, '/');
        }
      }
      self::$likeCache[$key] = '/^'.$re.'$/s'.($u ? 'u' : '');
    }
    return (bool)@preg_match(self::$likeCache[$key], $s);
  }

  /** printf()（%d、%s、%f、%x、%q、%Q等） */
  private static function printf(string $fmt, array $args): string {
    $i = 0;
    return preg_replace_callback('/%([-+ 0#,!]*)(\d+|\*)?(?:\.(\d+|\*))?(ll|l)?([diouxXeEfgGcsqQwz%])/', function(array $m) use (&$i, $args): string {
      [, $flags, $width, $prec, , $conv] = $m + ['', '', '', '', '', ''];
      if ($conv === '%') return '%';
      if ($width === '*') $width = (string)Value::toInt($args[$i++] ?? 0);
      if ($prec === '*') $prec = (string)Value::toInt($args[$i++] ?? 0);
      $v = $args[$i++] ?? null;
      $flags = str_replace([',', '!'], '', $flags);
      $spec = '%'.$flags.$width.($prec !== '' ? '.'.$prec : '');
      switch ($conv) {
        case 'd':
        case 'i':
          return sprintf($spec.'d', (int)Value::toInt($v));
        case 'u':
          return sprintf($spec.'u', (int)Value::toInt($v));
        case 'o':
        case 'x':
        case 'X':
          return sprintf($spec.$conv, (int)Value::toInt($v));
        case 'e':
        case 'E':
        case 'f':
        case 'g':
        case 'G':
          return sprintf($spec.($conv === 'f' ? 'F' : $conv), (float)Value::toNumber($v));
        case 'c':
          return mb_substr((string)Value::toText($v), 0, 1, 'UTF-8');
        case 'q':
          return $v === null ? '(NULL)' : str_replace("'", "''", (string)Value::toText($v));
        case 'Q':
          return $v === null ? 'NULL' : "'".str_replace("'", "''", (string)Value::toText($v))."'";
        case 'w':
          return $v === null ? '(NULL)' : str_replace('"', '""', (string)Value::toText($v));
        default:
          return sprintf($spec.'s', $v === null ? '' : (string)Value::toText($v));
      }
    }, $fmt);
  }

  ///////////////
  // 日付

  /**
   * 時間の値をUNIXの秒（マイクロ秒付き）にする
   *
   * @return float|null
   */
  public static function timeValue(mixed $v): ?float {
    if ($v === null) return null;
    if (is_int($v) || is_float($v)) {
      // ユリウス日
      return ((float)$v - 2440587.5) * 86400.0;
    }
    $s = trim((string)Value::toText($v));
    if (strcasecmp($s, 'now') === 0) return microtime(true);
    if (preg_match('/^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2})(\.\d+)?)?)?\s*(Z|[+-]\d{2}:?\d{2})?$/i', $s, $m)) {
      $t = gmmktime((int)($m[4] ?? 0), (int)($m[5] ?? 0), (int)($m[6] ?? 0), (int)$m[2], (int)$m[3], (int)$m[1]);
      $t += (float)('0'.($m[7] ?? ''));
      if (!empty($m[8]) && strtoupper($m[8]) !== 'Z') {
        $sign = $m[8][0] === '-' ? -1 : 1;
        $hm = str_replace(':', '', substr($m[8], 1));
        $t -= $sign * ((int)substr($hm, 0, 2) * 3600 + (int)substr($hm, 2, 2) * 60);
      }
      return (float)$t;
    }
    if (preg_match('/^(\d{2}):(\d{2})(?::(\d{2})(\.\d+)?)?$/', $s, $m)) {
      return (float)gmmktime((int)$m[1], (int)$m[2], (int)($m[3] ?? 0), 1, 1, 2000) + (float)('0'.($m[4] ?? ''));
    }
    $n = Value::parseNumber($s, true);
    if ($n !== null) return ((float)$n - 2440587.5) * 86400.0;
    return null;
  }

  private static function dateFunc(string $name, array $a): mixed {
    $fmt = null;
    if ($name === 'strftime') {
      if (empty($a) || $a[0] === null) return null;
      $fmt = (string)Value::toText($a[0]);
      $a = array_slice($a, 1);
    }
    if ($name === 'timediff') {
      if (count($a) !== 2) throw new DbException('timediff()の引数の数が違います', 1, 'HY000');
      $x = self::timeValue($a[0]);
      $y = self::timeValue($a[1]);
      if ($x === null || $y === null) return null;
      $d = $x - $y;
      $sign = $d < 0 ? '-' : '+';
      $d = abs($d);
      $days = (int)floor($d / 86400);
      return sprintf('%s%04d-%02d-%02d %02d:%02d:%06.3f', $sign, 0, 0, $days, (int)($d / 3600) % 24, (int)($d / 60) % 60, fmod($d, 60));
    }
    $v = empty($a) ? 'now' : $a[0];
    $mods = array_slice($a, 1);
    // unixepochの修飾子
    $t = null;
    foreach ($mods as $m) {
      if (is_string($m) && strcasecmp(trim($m), 'unixepoch') === 0 && (is_int($v) || is_float($v) || (is_string($v) && is_numeric($v)))) {
        $t = (float)Value::toNumber($v);
      }
    }
    if ($t === null) $t = self::timeValue($v);
    if ($t === null) return null;
    foreach ($mods as $m) {
      if ($m === null) return null;
      $m = strtolower(trim((string)Value::toText($m)));
      if ($m === 'unixepoch' || $m === 'utc' || $m === 'auto' || $m === 'subsec' || $m === 'subsecond') continue;
      if ($m === 'localtime') {
        $t += (int)date('Z', (int)$t);
        continue;
      }
      if (preg_match('/^([+-]?\d+(?:\.\d+)?)\s*(second|minute|hour|day|month|year)s?$/', $m, $mm)) {
        $q = (float)$mm[1];
        switch ($mm[2]) {
          case 'second': $t += $q; break;
          case 'minute': $t += $q * 60; break;
          case 'hour': $t += $q * 3600; break;
          case 'day': $t += $q * 86400; break;
          case 'month':
          case 'year':
            $frac = $t - floor($t);
            $d = new \DateTimeImmutable('@'.(int)floor($t));
            $months = (int)$q * ($mm[2] === 'year' ? 12 : 1);
            $y = (int)$d->format('Y');
            $mo = (int)$d->format('n') + $months;
            $y += intdiv($mo - 1 - (($mo - 1) % 12 + 12) % 12, 12);
            $mo = (($mo - 1) % 12 + 12) % 12 + 1;
            $t = gmmktime((int)$d->format('G'), (int)$d->format('i'), (int)$d->format('s'), $mo, (int)$d->format('j'), $y) + $frac;
            break;
        }
        continue;
      }
      if (preg_match('/^start of (day|month|year)$/', $m, $mm)) {
        $d = gmdate('Y-m-d', (int)floor($t));
        [$y, $mo, $dd] = explode('-', $d);
        $t = (float)match ($mm[1]) { 'day' => gmmktime(0, 0, 0, (int)$mo, (int)$dd, (int)$y), 'month' => gmmktime(0, 0, 0, (int)$mo, 1, (int)$y), default => gmmktime(0, 0, 0, 1, 1, (int)$y) };
        continue;
      }
      if (preg_match('/^weekday (\d)$/', $m, $mm)) {
        $w = (int)gmdate('w', (int)floor($t));
        $t += ((((int)$mm[1] - $w) % 7 + 7) % 7) * 86400;
        continue;
      }
      return null;
    }
    $sec = (int)floor($t);
    $frac = $t - $sec;
    switch ($name) {
      case 'date':
        return gmdate('Y-m-d', $sec);
      case 'time':
        return gmdate('H:i:s', $sec);
      case 'datetime':
        return gmdate('Y-m-d H:i:s', $sec);
      case 'julianday':
        return $t / 86400.0 + 2440587.5;
      case 'unixepoch':
        return $sec;
    }
    // strftime
    return preg_replace_callback('/%(.)/', function(array $m) use ($sec, $frac, $t): string {
      return match ($m[1]) {
        'd' => gmdate('d', $sec), 'e' => str_pad(gmdate('j', $sec), 2, ' ', STR_PAD_LEFT), 'f' => sprintf('%06.3f', (float)gmdate('s', $sec) + $frac),
        'F' => gmdate('Y-m-d', $sec), 'H' => gmdate('H', $sec), 'I' => gmdate('h', $sec), 'j' => sprintf('%03d', (int)gmdate('z', $sec) + 1),
        'J' => (string)($t / 86400.0 + 2440587.5), 'k' => str_pad(gmdate('G', $sec), 2, ' ', STR_PAD_LEFT), 'l' => str_pad(gmdate('g', $sec), 2, ' ', STR_PAD_LEFT),
        'm' => gmdate('m', $sec), 'M' => gmdate('i', $sec), 'p' => gmdate('A', $sec), 'P' => gmdate('a', $sec), 'R' => gmdate('H:i', $sec),
        's' => (string)$sec, 'S' => gmdate('s', $sec), 'T' => gmdate('H:i:s', $sec), 'u' => gmdate('N', $sec), 'w' => gmdate('w', $sec),
        'W' => sprintf('%02d', intdiv((int)gmdate('z', $sec) + 7 - (((int)gmdate('w', $sec) + 6) % 7), 7)), 'Y' => gmdate('Y', $sec),
        'G' => gmdate('o', $sec), 'g' => gmdate('y', $sec), 'V' => gmdate('W', $sec), 'U' => sprintf('%02d', intdiv((int)gmdate('z', $sec) + 7 - (int)gmdate('w', $sec), 7)),
        '%' => '%',
        default => '',
      };
    }, $fmt ?? '');
  }

  ///////////////
  // JSON

  private static function jsonDecode(mixed $v): mixed {
    if ($v === null) return null;
    $d = json_decode((string)Value::toText($v), false, 512, JSON_BIGINT_AS_STRING);
    if (json_last_error() !== JSON_ERROR_NONE) throw new DbException('不正なJSON', 1, 'HY000');
    return $d;
  }

  private static function jsonText(mixed $v): string {
    return (string)json_encode($v, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRESERVE_ZERO_FRACTION);
  }

  public static function toJsonValue(mixed $v): mixed {
    if ($v instanceof Blob) throw new DbException('JSONにBLOBは使えません', 1, 'HY000');
    return $v;
  }

  /** $.a.b[0]、->の場合は「a」・数値も */
  private static function jsonPath(mixed $doc, mixed $path, bool $arrow, bool &$found): mixed {
    if ($path === null) {
      $found = false;
      return null;
    }
    if ($arrow && is_int($path)) $path = '$['.$path.']';
    $p = (string)Value::toText($path);
    if ($arrow && !str_starts_with($p, '$')) $p = '$.'.$p;
    if (!str_starts_with($p, '$')) throw new DbException("不正なJSONのパス：{$p}", 1, 'HY000');
    preg_match_all('/\.(?:"((?:[^"\\\\]|\\\\.)*)"|([^.\[]+))|\[(#-)?(\d+)\]/', substr($p, 1), $m, PREG_SET_ORDER);
    $cur = $doc;
    foreach ($m as $part) {
      if (isset($part[4]) && $part[4] !== '') {
        if (!is_array($cur)) {
          $found = false;
          return null;
        }
        $i = (int)$part[4];
        if (!empty($part[3])) $i = count($cur) - $i;
        if (!array_key_exists($i, $cur)) {
          $found = false;
          return null;
        }
        $cur = $cur[$i];
      } else {
        $k = $part[1] !== '' ? stripcslashes($part[1]) : $part[2];
        if (!is_object($cur) || !property_exists($cur, $k)) {
          $found = false;
          return null;
        }
        $cur = $cur->$k;
      }
    }
    return $cur;
  }

  ///////////////
  // 集約関数

  /** 集約の初期の状態 */
  public static function aggInit(string $name): array {
    return ['n' => 0, 'v' => null, 'sum' => 0, 'float' => false, 'parts' => [], 'seen' => []];
  }

  /** 1行分を足す */
  public static function aggStep(string $name, array &$st, array $args, bool $star, string $coll): void {
    if ($star) {
      $st['n']++;
      return;
    }
    $v = $args[0] ?? null;
    if ($v === null) return;
    switch ($name) {
      case 'count':
        $st['n']++;
        return;
      case 'sum':
      case 'total':
      case 'avg':
        $num = Value::toNumber($v);
        $st['n']++;
        // 整数以外（文字列も）が有ればREAL
        if (!is_int($v) || $st['float']) {
          $st['float'] = true;
          $st['sum'] = (float)$st['sum'] + (float)$num;
        } else {
          $s = $st['sum'] + $num;
          if (is_float($s)) {
            if ($name === 'sum') throw new DbException('整数のオーバーフロー', 1, 'HY000');
            $st['float'] = true;
          }
          $st['sum'] = $s;
        }
        return;
      case 'min':
      case 'max':
        if ($st['n'] === 0 || ($name === 'min' ? Record::compare($v, $st['v'], $coll) < 0 : Record::compare($v, $st['v'], $coll) > 0)) $st['v'] = $v;
        $st['n']++;
        return;
      case 'group_concat':
      case 'string_agg':
        $sep = array_key_exists(1, $args) ? $args[1] : ($name === 'string_agg' ? null : ',');
        if ($name === 'string_agg' && count($args) < 2) throw new DbException('string_agg()の引数の数が違います', 1, 'HY000');
        if ($st['n'] > 0) $st['parts'][] = (string)Value::toText($sep);
        $st['parts'][] = (string)Value::toText($v);
        $st['n']++;
        return;
    }
  }

  /** 結果 */
  public static function aggFinal(string $name, array $st): mixed {
    return match ($name) {
      'count' => $st['n'],
      'sum' => $st['n'] === 0 ? null : $st['sum'],
      'total' => (float)$st['sum'],
      'avg' => $st['n'] === 0 ? null : (float)$st['sum'] / $st['n'],
      'min', 'max' => $st['v'],
      'group_concat', 'string_agg' => $st['n'] === 0 ? null : implode('', $st['parts']),
      default => null,
    };
  }
}