<?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;
use Std\Lib\Db\Convert;
use Std\Lib\Db\DbException;

/**
 * データベース（MySQL・PostgreSQL・SQLite）から読む
 *
 * MySQLはSHOW CREATE TABLE、PostgreSQLはinformation_schemaとpg_catalog、SQLiteはsqlite_masterでテーブルの定義を読む。
 */
final class DbSource implements Source {
  public string $dialect;
  private array $warnings = [];
  private ?array $tables = null;

  /**
   * @param Db $db  接続（まだ接続していなくても可）
   * @param int $batch  1回に読む行の数
   */
  public function __construct(private Db $db, private int $batch = 1000) {
    $this->dialect = self::dialectOf($db);
  }

  public static function dialectOf(Db $db): string {
    return match ($db->driverName) {
      'mysql', 'mariadb' => 'mysql',
      'pgsql', 'postgres', 'postgresql' => 'pgsql',
      default => 'sqlite',
    };
  }

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

  /**
   * テーブルの名前
   *
   * @return array
   */
  public static function tableNames(Db $db): array {
    switch (self::dialectOf($db)) {
      case 'mysql':
        $out = [];
        foreach ($db->query('SHOW FULL TABLES')['rows'] as $r) {
          $v = array_values($r);
          if (($v[1] ?? 'BASE TABLE') === 'BASE TABLE') $out[] = (string)$v[0];
        }
        return $out;
      case 'pgsql':
        return array_column($db->query("SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() AND table_type = 'BASE TABLE' ORDER BY table_name")['rows'], 'table_name');
      default:
        return array_column($db->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite\\_%' ESCAPE '\\' ORDER BY name")['rows'], 'name');
    }
  }

  public function tables(): array {
    if ($this->tables !== null) return $this->tables;
    $out = [];
    foreach (self::tableNames($this->db) as $name) {
      $t = match ($this->dialect) {
        'mysql' => $this->mysqlTable($name),
        'pgsql' => $this->pgsqlTable($name),
        default => $this->sqliteTable($name),
      };
      if ($t !== null) $out[] = $t;
    }
    return $this->tables = $out;
  }

  private function mysqlTable(string $name): ?array {
    $r = $this->db->query('SHOW CREATE TABLE '.Convert::quote('mysql', $name))['rows'][0] ?? null;
    $sql = $r === null ? '' : (string)(array_values($r)[1] ?? '');
    return Sql::createTable($sql, 'mysql', $this->warnings);
  }

  private function sqliteTable(string $name): ?array {
    $sql = (string)($this->db->query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?", [$name])['rows'][0]['sql'] ?? '');
    $t = Sql::createTable($sql, 'sqlite', $this->warnings);
    if ($t === null) return null;
    // INTEGER PRIMARY KEYはrowid（自動の番号）
    if (count($t['pk']) === 1) {
      foreach ($t['columns'] as $i => $c) {
        if (strcasecmp($c['name'], $t['pk'][0]) === 0 && $c['type'] === 'int') {
          $info = $this->db->query('PRAGMA table_info('.Convert::quote('sqlite', $name).')')['rows'];
          foreach ($info as $col) {
            if (strcasecmp($col['name'], $c['name']) === 0 && strtoupper($col['type']) === 'INTEGER') $t['columns'][$i]['autoinc'] = true;
          }
        }
      }
    }
    foreach ($this->db->query("SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = ? AND sql IS NOT NULL ORDER BY name", [$name])['rows'] as $r) {
      $idx = self::parseIndex((string)$r['sql'], 'sqlite');
      if ($idx === null) {
        $this->warnings[] = "式・部分のインデックスは変換しません：{$r['name']}";
        continue;
      }
      $t['indexes'][] = $idx;
    }
    return $t;
  }

  /** CREATE [UNIQUE] INDEX 名前 ON テーブル (列) */
  public static function parseIndex(string $sql, string $dialect): ?array {
    $tok = Sql::tokens($sql, $dialect);
    $p = 1;
    $unique = ($tok[$p]['u'] ?? '') === 'UNIQUE';
    if ($unique) $p++;
    $p++;
    if (($tok[$p]['u'] ?? '') === 'IF') $p += 3;
    $name = Sql::name($tok, $p);
    $p++;
    Sql::name($tok, $p);
    if (($tok[$p]['u'] ?? '') === 'USING') $p += 2;
    $cols = Sql::columnList($tok, $p);
    if (in_array(null, $cols, true) || ($tok[$p]['u'] ?? '') === 'WHERE') return null;
    return ['name' => $name, 'columns' => $cols, 'unique' => $unique];
  }

  private function pgsqlTable(string $name): array {
    $t = ['name' => $name, 'columns' => [], 'pk' => [], 'indexes' => []];
    $cols = $this->db->query('SELECT column_name, data_type, udt_name, character_maximum_length, numeric_precision, numeric_scale, is_nullable, column_default, is_identity
      FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = ? ORDER BY ordinal_position', [$name])['rows'];
    foreach ($cols as $c) {
      $dt = strtolower((string)$c['data_type']);
      $type = match (true) {
        $dt === 'array' => 'json[]',
        $dt === 'user-defined' => (string)$c['udt_name'],
        $dt === 'character varying' && $c['character_maximum_length'] !== null => "varchar({$c['character_maximum_length']})",
        $dt === 'character' => 'char('.($c['character_maximum_length'] ?? 1).')',
        $dt === 'numeric' && $c['numeric_precision'] !== null => "numeric({$c['numeric_precision']},".(int)$c['numeric_scale'].')',
        default => $dt,
      };
      $col = Convert::parseType($type, 'pgsql') + ['name' => $c['column_name'], 'notNull' => $c['is_nullable'] === 'NO', 'default' => null, 'autoinc' => $c['is_identity'] === 'YES'];
      if (in_array($dt, ['user-defined'], true) && !in_array($c['udt_name'], ['citext', 'uuid', 'json', 'jsonb'], true)) $col['type'] = 'text';
      if ($c['column_default'] !== null) {
        $d = Convert::parseDefault((string)$c['column_default']);
        if ($d !== null && isset($d['seq'])) $col['autoinc'] = true;
        else if ($d === null) $this->warnings[] = "DEFAULTを変換できません：{$name}.{$c['column_name']}（{$c['column_default']}）";
        else $col['default'] = $d;
      }
      $t['columns'][] = $col;
    }
    $idx = $this->db->query("SELECT i.relname AS name, ix.indisunique AS uniq, ix.indisprimary AS prim, ix.indexprs IS NOT NULL OR ix.indpred IS NOT NULL AS expr,
        array_to_string(ARRAY(SELECT a.attname FROM unnest(ix.indkey) WITH ORDINALITY AS k(attnum, n) JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum ORDER BY k.n), ',') AS cols
      FROM pg_index ix JOIN pg_class t ON t.oid = ix.indrelid JOIN pg_class i ON i.oid = ix.indexrelid JOIN pg_namespace ns ON ns.oid = t.relnamespace
      WHERE t.relname = ? AND ns.nspname = current_schema() ORDER BY i.relname", [$name])['rows'];
    foreach ($idx as $r) {
      if ($r['expr']) {
        $this->warnings[] = "式・部分のインデックスは変換しません：{$r['name']}";
        continue;
      }
      $list = explode(',', (string)$r['cols']);
      if ($r['prim']) $t['pk'] = $list;
      else $t['indexes'][] = ['name' => $r['name'], 'columns' => $list, 'unique' => (bool)$r['uniq']];
    }
    return $t;
  }

  /** 列毎の一番長い値（インデックスの有る文字列の列、MySQLのVARCHARの長さ用） */
  public function maxLengths(array $t): array {
    $out = [];
    $q = fn(string $n) => Convert::quote($this->dialect, $n);
    $fn = $this->dialect === 'mysql' ? 'CHAR_LENGTH' : 'LENGTH';
    foreach ($t['columns'] as $c) {
      if (!in_array($c['type'], ['text', 'varchar', 'blob'], true)) continue;
      $out[$c['name']] = (int)(array_values($this->db->query("SELECT MAX({$fn}({$q($c['name'])})) FROM {$q($t['name'])}")['rows'][0] ?? [])[0] ?? 0);
    }
    return $out;
  }

  public function rows(array $tables, callable $sink): void {
    $q = fn(string $n) => Convert::quote($this->dialect, $n);
    foreach ($this->tables() as $t) {
      if (!in_array(strtolower($t['name']), $tables, true)) continue;
      $cols = implode(', ', array_map(fn($c) => $q($c['name']), $t['columns']));
      $key = count($t['pk']) === 1 ? $t['pk'][0] : null;
      $from = $q($t['name']);
      if ($key !== null) {
        // 主キーの順番で、少しずつ
        $last = null;
        while (true) {
          $sql = "SELECT {$cols} FROM {$from}".($last !== null ? " WHERE {$q($key)} > ?" : '')." ORDER BY {$q($key)} LIMIT {$this->batch}";
          $rows = $this->db->query($sql, $last !== null ? [$last] : [])['rows'];
          if (empty($rows)) break;
          $sink($t['name'], array_map('array_values', $rows));
          $lastRow = end($rows);
          $last = $lastRow[$this->keyName($rows, $key)];
          if (count($rows) < $this->batch) break;
        }
      } else {
        $order = $t['pk'] ? ' ORDER BY '.implode(', ', array_map($q, $t['pk'])) : '';
        for ($off = 0; ; $off += $this->batch) {
          $rows = $this->db->query("SELECT {$cols} FROM {$from}{$order} LIMIT {$this->batch} OFFSET {$off}")['rows'];
          if (empty($rows)) break;
          $sink($t['name'], array_map('array_values', $rows));
          if (count($rows) < $this->batch) break;
        }
      }
    }
  }

  /** 結果の行の主キーの名前（大文字と小文字） */
  private function keyName(array $rows, string $key): string {
    foreach (array_keys($rows[0]) as $k) if (strcasecmp((string)$k, $key) === 0) return (string)$k;
    return $key;
  }
}