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

use Std\Lib\Db\Convert;
use Std\Lib\Db\DbException;
use Std\Lib\Db\Sqlite\Blob;

/**
 * SQLのダンプ（mysqldump、pg_dump（平文、COPY・INSERT）、sqlite3の.dump）から読む
 *
 * ファイルを2回読む（1回目はテーブルの定義、2回目は行）。CREATE TABLE、ALTER TABLE … ADD CONSTRAINT（主キー・一意）・
 * ADD PRIMARY KEY・ADD KEY・MODIFY、CREATE INDEX、シーケンス（nextval、OWNED BY、IDENTITY）、INSERT、COPYを使い、
 * 他の文（SET、ビュー、関数、権限等）は無視する。
 */
final class DumpSource implements Source {
  public string $dialect;
  private array $warnings = [];
  private ?array $tables = null;

  /**
   * @param string $path  ダンプのファイル（gzipで圧縮したファイルも可）
   * @param string|null $dialect  mysql、pgsql、sqlite（nullなら最初の部分から推測）
   */
  public function __construct(private string $path, ?string $dialect = null) {
    if (!is_file($path) || !is_readable($path)) throw new DbException("ダンプのファイルを読めません：{$path}", 0, 'HY000');
    $head = $this->head(65536);
    if (str_starts_with($head, 'PGDMP')) throw new DbException("pg_dumpのカスタム・ディレクトリの形式には未対応です（プレーンの形式、-Fpで作って下さい）：{$path}", 0, 'HY000');
    if (str_starts_with($head, "SQLite format 3\0")) throw new DbException("ダンプではなく、SQLiteのデータベースのファイルです：{$path}", 0, 'HY000');
    $this->dialect = $dialect ?? self::detect($head);
  }

  private function open() {
    // zlibが有れば、gzopenは圧縮していないファイルもそのまま読める
    $gz = function_exists('gzopen');
    if (!$gz && str_ends_with(strtolower($this->path), '.gz')) throw new DbException("圧縮したダンプにはzlibが必要です：{$this->path}", 0, 'HY000');
    $fh = $gz ? @gzopen($this->path, 'rb') : @fopen($this->path, 'rb');
    if ($fh === false) throw new DbException("ダンプのファイルを開けません：{$this->path}", 0, 'HY000');
    return [$fh, $gz];
  }

  private function head(int $n): string {
    [$fh, $gz] = $this->open();
    $s = $gz ? gzread($fh, $n) : fread($fh, $n);
    $gz ? gzclose($fh) : fclose($fh);
    return (string)$s;
  }

  /** ダンプの方言を推測する */
  public static function detect(string $head): string {
    if (preg_match('/^-- LittleBeast.*（(mysql|pgsql|sqlite)）/m', $head, $m)) return $m[1];
    if (preg_match('/PostgreSQL database dump|^COPY .* FROM stdin;|SET search_path|pg_catalog\./mi', $head)) return 'pgsql';
    if (preg_match('/MySQL dump|MariaDB dump|^\/\*!\d{5}|ENGINE=|`[^`]+`/mi', $head)) return 'mysql';
    return 'sqlite';
  }

  public function warnings(): array {
    return array_values(array_unique($this->warnings));
  }

  /**
   * 文を順番に（COPYのデータはcopy）
   *
   * @return \Generator  ['sql' => 文] 又は ['copy' => COPYの文, 'lines' => データの行の配列]
   */
  public function statements(): \Generator {
    [$fh, $gz] = $this->open();
    $read = $gz ? fn() => gzgets($fh) : fn() => fgets($fh);
    $delim = ';';
    $buf = '';
    $state = '';
    try {
      $first = true;
      while (($line = $read()) !== false) {
        if ($first) {
          // UTF-8のBOM
          if (str_starts_with($line, "\xEF\xBB\xBF")) $line = substr($line, 3);
          $first = false;
        }
        // COPY … FROM stdin;の後のデータ
        if ($state === 'copy') {
          if (rtrim($line, "\r\n") === '\\.') {
            if ($copyLines) yield ['copy' => $copySql, 'lines' => $copyLines];
            $copyLines = [];
            $state = '';
            continue;
          }
          $copyLines[] = rtrim($line, "\r\n");
          if (count($copyLines) >= 500) {
            yield ['copy' => $copySql, 'lines' => $copyLines];
            $copyLines = [];
          }
          continue;
        }
        if ($state === '' && trim($buf) === '' && preg_match('/^\s*DELIMITER\s+(\S+)\s*$/i', $line, $m)) {
          $delim = $m[1];
          continue;
        }
        $i = 0;
        $len = strlen($line);
        while ($i < $len) {
          if ($state === "'" || $state === '"' || $state === '`') {
            $bs = $this->dialect === 'mysql' && $state !== '`' || ($state === "'" && !empty($estring));
            $j = $i;
            while (true) {
              $k = strcspn($line, $state.($bs ? '\\' : ''), $j);
              $j += $k;
              if ($j >= $len) break;
              if ($line[$j] === '\\') {
                $j += 2;
                continue;
              }
              // 「''」
              if (($line[$j + 1] ?? '') === $state) {
                $j += 2;
                continue;
              }
              $state = '';
              $j++;
              break;
            }
            $buf .= substr($line, $i, min($j, $len) - $i);
            $i = min($j, $len);
            continue;
          }
          if ($state === '*/') {
            $j = strpos($line, '*/', $i);
            if ($j === false) {
              $buf .= substr($line, $i);
              break;
            }
            $buf .= substr($line, $i, $j + 2 - $i);
            $i = $j + 2;
            $state = '';
            continue;
          }
          if (str_starts_with($state, '$')) {
            $j = strpos($line, $state, $i);
            if ($j === false) {
              $buf .= substr($line, $i);
              break;
            }
            $buf .= substr($line, $i, $j + strlen($state) - $i);
            $i = $j + strlen($state);
            $state = '';
            continue;
          }
          $k = strcspn($line, "'\"`-#/\$".$delim[0], $i);
          $buf .= substr($line, $i, $k);
          $i += $k;
          if ($i >= $len) break;
          $c = $line[$i];
          if ($c === $delim[0] && substr($line, $i, strlen($delim)) === $delim) {
            $sql = trim($buf);
            $buf = '';
            $i += strlen($delim);
            if ($sql !== '') {
              if (preg_match('/^COPY\s.*\sFROM\s+stdin/is', $sql)) {
                $state = 'copy';
                $copySql = $sql;
                $copyLines = [];
                break;
              }
              yield ['sql' => $sql];
            }
            continue;
          }
          if ($c === "'" || $c === '"' || $c === '`') {
            if ($c === '`' && $this->dialect !== 'mysql' && $this->dialect !== 'sqlite') {
              $buf .= $c;
              $i++;
              continue;
            }
            $estring = $c === "'" && $i > 0 && ($line[$i - 1] === 'E' || $line[$i - 1] === 'e') && !ctype_alnum($line[$i - 2] ?? ' ');
            $state = $c;
            $buf .= $c;
            $i++;
            continue;
          }
          if ($c === '-' && ($line[$i + 1] ?? '') === '-' || ($c === '#' && $this->dialect === 'mysql')) {
            // 行の最後までのコメント
            break;
          }
          if ($c === '/' && ($line[$i + 1] ?? '') === '*') {
            $state = '*/';
            $buf .= '/*';
            $i += 2;
            continue;
          }
          if ($c === '$' && $this->dialect === 'pgsql' && preg_match('/\G\$([A-Za-z_][A-Za-z0-9_]*)?\$/', $line, $m, 0, $i)) {
            $state = $m[0];
            $buf .= $m[0];
            $i += strlen($m[0]);
            continue;
          }
          $buf .= $c;
          $i++;
        }
        if ($state === '' && $buf !== '' && !str_ends_with($buf, "\n")) $buf .= "\n";
      }
      if ($state !== '') throw new DbException("ダンプのファイルが途中で終わっています（{$state}）：{$this->path}", 0, 'HY000');
      if (trim($buf) !== '') yield ['sql' => trim($buf)];
    } finally {
      $gz ? gzclose($fh) : fclose($fh);
    }
  }

  public function tables(): array {
    if ($this->tables !== null) return array_values($this->tables);
    $tables = [];
    $seqOwner = [];
    foreach ($this->statements() as $st) {
      if (!isset($st['sql'])) continue;
      $sql = $st['sql'];
      // MySQLの「/*!40101 … */」の中の文
      if (preg_match('/^\/\*!\d*\s*(.*)\*\/$/s', $sql, $m)) $sql = trim($m[1]);
      $head = strtoupper(substr(ltrim($sql), 0, 40));
      if (str_starts_with($head, 'CREATE') && preg_match('/^CREATE\s+(?:(?:TEMP|TEMPORARY|UNLOGGED|GLOBAL|LOCAL)\s+)*TABLE\b/i', $sql)) {
        if (preg_match('/\bAS\s+SELECT\b/i', $sql) && !str_contains($sql, '(')) continue;
        $t = Sql::createTable($sql, $this->dialect, $this->warnings);
        if ($t === null) continue;
        if (in_array(strtolower($t['name']), ['sqlite_sequence', 'sqlite_stat1', 'sqlite_stat4'], true)) continue;
        $tables[strtolower($t['name'])] = $t;
      } else if (str_starts_with($head, 'CREATE') && preg_match('/^CREATE\s+(UNIQUE\s+)?INDEX\b/i', $sql)) {
        $this->createIndex($sql, $tables);
      } else if (str_starts_with($head, 'ALTER TABLE')) {
        $this->alterTable($sql, $tables);
      } else if (preg_match('/^ALTER\s+SEQUENCE\s+(\S+)\s+OWNED\s+BY\s+(\S+)\s*$/i', $sql, $m)) {
        $parts = explode('.', str_replace('"', '', $m[2]));
        $col = array_pop($parts);
        $tbl = array_pop($parts);
        $seqOwner[strtolower(self::lastPart($m[1]))] = [strtolower((string)$tbl), $col];
      } else if (preg_match('/^CREATE\s+(OR\s+REPLACE\s+)?(VIEW|TRIGGER|FUNCTION|PROCEDURE|MATERIALIZED)/i', $sql, $m)) {
        $this->warnings[] = strtoupper($m[2]).'は変換しません';
      }
    }
    // nextval('seq')のDEFAULTはalterTable()でautoincにする。OWNED BYだけの場合
    foreach ($seqOwner as [$tbl, $col]) {
      if (!isset($tables[$tbl])) continue;
      foreach ($tables[$tbl]['columns'] as $i => $c) {
        if (strcasecmp($c['name'], $col) === 0 && $c['type'] === 'int' && !empty($this->nextval[$tbl][strtolower($col)])) $tables[$tbl]['columns'][$i]['autoinc'] = true;
      }
    }
    $this->tables = $tables;
    return array_values($tables);
  }

  private array $nextval = [];

  private static function lastPart(string $name): string {
    $p = explode('.', str_replace(['"', '`'], '', $name));
    return end($p);
  }

  private function createIndex(string $sql, array &$tables): void {
    $tok = Sql::tokens($sql, $this->dialect);
    $p = 1;
    $unique = false;
    if (($tok[$p]['u'] ?? '') === 'UNIQUE') {
      $unique = true;
      $p++;
    }
    $p++;
    if (($tok[$p]['u'] ?? '') === 'CONCURRENTLY') $p++;
    if (($tok[$p]['u'] ?? '') === 'IF') $p += 3;
    $name = '';
    if (($tok[$p]['u'] ?? '') !== 'ON') $name = Sql::name($tok, $p);
    $p++;
    if (($tok[$p]['u'] ?? '') === 'ONLY') $p++;
    $table = strtolower(Sql::name($tok, $p));
    if (($tok[$p]['u'] ?? '') === 'USING') $p += 2;
    if (!isset($tables[$table])) return;
    $cols = Sql::columnList($tok, $p);
    if (in_array(null, $cols, true) || ($tok[$p]['u'] ?? '') === 'WHERE') {
      $this->warnings[] = "式・部分のインデックスは変換しません：{$name}";
      return;
    }
    $tables[$table]['indexes'][] = ['name' => $name, 'columns' => $cols, 'unique' => $unique];
  }

  private function alterTable(string $sql, array &$tables): void {
    $tok = Sql::tokens($sql, $this->dialect);
    $p = 2;
    if (($tok[$p]['u'] ?? '') === 'IF') $p += 2;
    if (($tok[$p]['u'] ?? '') === 'ONLY') $p++;
    $table = strtolower(Sql::name($tok, $p));
    if (!isset($tables[$table])) return;
    $n = count($tok);
    // 「,」で区切った複数の操作
    $ops = [];
    $cur = [];
    $depth = 0;
    for (; $p < $n; $p++) {
      if ($tok[$p]['t'] === 'op' && $tok[$p]['v'] === '(') $depth++;
      if ($tok[$p]['t'] === 'op' && $tok[$p]['v'] === ')') $depth--;
      if ($depth === 0 && $tok[$p]['t'] === 'op' && $tok[$p]['v'] === ',') {
        $ops[] = $cur;
        $cur = [];
        continue;
      }
      $cur[] = $tok[$p];
    }
    if ($cur) $ops[] = $cur;
    foreach ($ops as $op) {
      $u = array_map(fn($t) => $t['u'] ?? '', $op);
      if (($u[0] ?? '') === 'ADD') {
        $k = 1;
        $cname = '';
        if (($u[$k] ?? '') === 'CONSTRAINT') {
          $cname = (string)($op[$k + 1]['v'] ?? '');
          $k += 2;
        }
        if (($u[$k] ?? '') === 'PRIMARY') {
          while ($k < count($op) && !($op[$k]['t'] === 'op' && $op[$k]['v'] === '(')) $k++;
          $tables[$table]['pk'] = array_values(array_filter(Sql::columnList($op, $k)));
        } else if (in_array($u[$k] ?? '', ['UNIQUE', 'KEY', 'INDEX'], true)) {
          $unique = $u[$k] === 'UNIQUE';
          $k++;
          if (in_array($u[$k] ?? '', ['KEY', 'INDEX'], true)) $k++;
          if (!($op[$k]['t'] === 'op' && $op[$k]['v'] === '(')) $cname = (string)$op[$k++]['v'];
          while ($k < count($op) && !($op[$k]['t'] === 'op' && $op[$k]['v'] === '(')) $k++;
          $cols = Sql::columnList($op, $k);
          if (!in_array(null, $cols, true)) $tables[$table]['indexes'][] = ['name' => $cname, 'columns' => $cols, 'unique' => $unique];
        } else if (($u[$k] ?? '') === 'FOREIGN') {
          $this->warnings[] = "外部キーは変換しません：{$tables[$table]['name']}";
        }
      } else if (($u[0] ?? '') === 'ALTER') {
        // ALTER COLUMN x SET DEFAULT nextval(…)、ADD GENERATED … AS IDENTITY
        $k = ($u[1] ?? '') === 'COLUMN' ? 2 : 1;
        $col = (string)($op[$k]['v'] ?? '');
        $rest = strtoupper(Sql::text(array_slice($op, $k + 1)));
        if (str_contains($rest, 'NEXTVAL') || str_contains($rest, 'IDENTITY')) {
          foreach ($tables[$table]['columns'] as $i => $c) {
            if (strcasecmp($c['name'], $col) === 0) {
              $tables[$table]['columns'][$i]['autoinc'] = true;
              $this->nextval[$table][strtolower($col)] = true;
            }
          }
        }
      } else if (($u[0] ?? '') === 'MODIFY' || ($u[0] ?? '') === 'CHANGE') {
        // MODIFY [COLUMN] col 型 …（phpMyAdminのAUTO_INCREMENT）
        $k = ($u[1] ?? '') === 'COLUMN' ? 2 : 1;
        if ($u[0] === 'CHANGE') $k++;
        $def = Sql::columnDef(array_slice($op, $k), $this->dialect, $table, $this->warnings);
        if ($def === null) continue;
        foreach ($tables[$table]['columns'] as $i => $c) {
          if (strcasecmp($c['name'], $def['name']) === 0) {
            unset($def['pk'], $def['unique']);
            $tables[$table]['columns'][$i] = $def + $c;
          }
        }
      }
    }
  }

  public function rows(array $tables, callable $sink): void {
    $defs = [];
    foreach ($this->tables() as $t) $defs[strtolower($t['name'])] = $t;
    $want = array_flip($tables);
    foreach ($this->statements() as $st) {
      if (isset($st['copy'])) {
        if (!preg_match('/^COPY\s+(?:ONLY\s+)?(\S+)\s*(?:\(([^)]*)\))?\s+FROM/is', $st['copy'], $m)) continue;
        $name = strtolower(self::lastPart($m[1]));
        if (!isset($want[$name], $defs[$name])) continue;
        $cols = isset($m[2]) && trim($m[2]) !== '' ? array_map(fn($c) => trim($c, " \t\n\"`"), explode(',', $m[2])) : null;
        $rows = [];
        foreach ($st['lines'] as $line) $rows[] = Sql::copyLine($line);
        $sink($defs[$name]['name'], $this->arrange($defs[$name], $cols, $rows, true));
        continue;
      }
      $sql = $st['sql'];
      if (!preg_match('/^(INSERT|REPLACE)\b/i', $sql)) continue;
      $ins = Sql::insert($sql, $this->dialect);
      if ($ins === null) continue;
      $name = strtolower($ins['table']);
      if (!isset($want[$name], $defs[$name])) continue;
      if (!empty($ins['select'])) {
        $this->warnings[] = "INSERT … SELECTは変換しません：{$ins['table']}";
        continue;
      }
      $sink($defs[$name]['name'], $this->arrange($defs[$name], $ins['columns'], $ins['rows'], false));
    }
  }

  /** 列の順番にする（COPYの文字列は型に合わせる） */
  private function arrange(array $t, ?array $cols, array $rows, bool $copy): array {
    $map = null;
    if ($cols !== null) {
      $idx = [];
      foreach ($t['columns'] as $i => $c) $idx[strtolower($c['name'])] = $i;
      $map = [];
      foreach ($cols as $k => $n) {
        if (!isset($idx[strtolower($n)])) throw new DbException("ダンプの列がありません：{$t['name']}.{$n}", 0, 'HY000');
        $map[$k] = $idx[strtolower($n)];
      }
    }
    $nc = count($t['columns']);
    $out = [];
    foreach ($rows as $r) {
      $row = array_fill(0, $nc, null);
      foreach ($r as $k => $v) {
        $i = $map === null ? $k : ($map[$k] ?? null);
        if ($i === null || $i >= $nc) continue;
        $c = $t['columns'][$i];
        // PostgreSQLの配列（{a,"b c"}）はJSONの配列
        if ($v !== null && !empty($c['array']) && $this->dialect === 'pgsql' && is_string($v) && str_starts_with(ltrim($v), '{') || (is_string($v) && str_starts_with($v, '[') && str_contains($v, ']={'))) {
          $arr = \Std\Lib\Db\Pgsql::parseArray($v);
          if ($arr !== null) $v = $arr;
        }
        // PostgreSQLのbytea（\x…）、COPYのbool（t、f）
        if ($v !== null && !$v instanceof Blob && $c['type'] === 'blob') {
          $s = (string)$v;
          if ($this->dialect === 'pgsql' && str_starts_with($s, '\\x')) $v = new Blob((string)hex2bin(substr($s, 2)));
          else if ($this->dialect === 'pgsql' && $copy && str_contains($s, '\\')) $v = new Blob(stripcslashes($s));
        }
        $row[$i] = $v;
      }
      $out[] = $row;
    }
    return $out;
  }
}