<?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\Sqlite\Blob;
use Std\Lib\Db\Sqlite\Engine;
use Std\Lib\Db\Sqlite\Pager;

/**
 * PHP純正のSQLiteドライバ（sqlite3・pdo_sqliteのモジュールが不要）
 *
 * SQLite 3のファイル形式をそのまま読み書きするので、sqlite3のコマンド等でも開けます（ロールバックジャーナル、UTF-8）。
 * 対応：CREATE/DROP TABLE・INDEX・VIEW、ALTER TABLE（RENAME、ADD・DROP・RENAME COLUMN）、INSERT（OR REPLACE、
 * ON CONFLICT、RETURNING）、UPDATE（FROM）、DELETE、SELECT（JOIN、GROUP BY、集約、サブクエリ、UNION等、WITH）、
 * トランザクション、SAVEPOINT、PRAGMA（table_info、index_list、integrity_check、user_version等）
 * 未対応：WALモード、トリガー、WITHOUT ROWID、仮想テーブル（FTS）、ウィンドウ関数、再帰のWITH、外部キーの確認、
 * 部分・式のインデックス、ATTACH
 *
 * ロックはflock()なので、同時に書き込むのはPHPのプロセス同士だけにして下さい。
 * プレースホルダは「?」、「?1」、「:name」。パラメータの文字列は、UTF-8でなければBLOBになる（Sqlite\Blobで明示も可）。
 *
 *   $db = new Sqlite(['path' => ROOT.'/data/site.sqlite']);
 *   $db->connect();
 *   $id = $db->query("INSERT INTO users (name) VALUES ('諏訪子')")['insertId'];
 */
class Sqlite implements DbInterface {
  public string $path;
  private bool $readOnly;
  private bool $create;
  private int $busyTimeout;
  private bool $debug;
  private ?Pager $pager = null;
  private ?Engine $engine = null;
  private array $prepared = [];
  private int $nextStmt = 1;

  /**
   * コンストラクタ
   *
   * @param array|null $info  path（又はdbname。相対パスはROOTから。「:memory:」はメモリー）、readonly、create（無ければ作る、既定true）、
   *                          busy_timeout（ロックを待つミリ秒、5000）、debug
   * @throws \Exception  $infoを省略し、DB_ENABLEDが無効な場合
   */
  public function __construct(?array $info = null) {
    if ($info === null && !(defined('DB_ENABLED') && DB_ENABLED)) {
      throw new \Exception('データベースは無効です。');
    }
    $info ??= DBINFO;
    $path = (string)($info['path'] ?? $info['dbname'] ?? ':memory:');
    if ($path === '') $path = ':memory:';
    if ($path !== ':memory:' && !preg_match('#^([A-Za-z]:)?[/\\\\]#', $path) && defined('ROOT')) $path = ROOT.'/'.$path;
    $this->path = $path;
    $this->readOnly = (bool)($info['readonly'] ?? false);
    $this->create = (bool)($info['create'] ?? true);
    $this->busyTimeout = (int)($info['busy_timeout'] ?? $info['timeout'] ?? 5000);
    if (isset($info['timeout']) && !isset($info['busy_timeout'])) $this->busyTimeout = (int)$info['timeout'] * 1000;
    $this->debug = (bool)($info['debug'] ?? false);
  }

  public function __destruct() {
    $this->close();
  }

  public function setDebug(bool $debug): Sqlite {
    $this->debug = $debug;
    return $this;
  }

  /**
   * ファイルを開く
   *
   * @return bool
   * @throws DbException
   */
  public function connect(): bool {
    if ($this->engine !== null) return true;
    try {
      $this->pager = new Pager($this->path, $this->readOnly, $this->create, $this->busyTimeout);
      $this->engine = new Engine($this->pager);
      // ファイルの確認（壊れたファイル・SQLiteでないファイル）
      $this->pager->beginRead();
      $this->engine->schema();
      $this->pager->endRead();
    } catch (DbException $e) {
      $this->pager?->close();
      $this->pager = null;
      $this->engine = null;
      $this->log($e->getMessage());
      throw $e;
    }
    return true;
  }

  public function close(): void {
    $this->pager?->close();
    $this->pager = null;
    $this->engine = null;
    $this->prepared = [];
  }

  public function isConnected(): bool {
    return $this->engine !== null;
  }

  public function ping(): bool {
    return $this->engine !== null && ($this->path === ':memory:' || is_file($this->path));
  }

  /**
   * SQLを実行する（セミコロンで区切った複数の文も可）
   *
   * @return array  fields、rows、affectedRows、insertId、warningCount（、more）
   * @throws DbException
   */
  public function query(string $query): array {
    $this->requireConnection();
    try {
      $stmts = $this->engine->prepare($query);
    } catch (DbException $e) {
      $this->fail('クエリに失敗', $e);
    }
    if (empty($stmts)) return self::emptyResult();
    $results = [];
    // パラメータはNULL（SQLiteと同じ）
    foreach ($stmts as $st) $results[] = $this->runOne($st, [], 'クエリに失敗');
    $first = array_shift($results);
    if (!empty($results)) $first['more'] = $results;
    return $first;
  }

  /**
   * プリペアドステートメント
   *
   * @return int  ステートメントID
   * @throws DbException
   */
  public function prepare(string $query): int {
    $this->requireConnection();
    try {
      $stmts = $this->engine->prepare($query);
    } catch (DbException $e) {
      $this->fail('準備に失敗', $e);
    }
    if (empty($stmts)) $this->fail('準備に失敗', new DbException('空のSQL', 1, 'HY000'));
    $id = $this->nextStmt++;
    $this->prepared[$id] = $stmts;
    return $id;
  }

  /**
   * プリペアドステートメントの実行
   *
   * @param int $statementId
   * @param array $params  順番の配列、又は名前（「:name」の「name」）の連想配列
   * @return array
   * @throws DbException
   */
  public function execute(int $statementId, array $params = []): array {
    $this->requireConnection();
    if (!isset($this->prepared[$statementId])) $this->fail('実行に失敗', new DbException("不正なステートメントID: {$statementId}", 1, 'HY000'));
    $results = [];
    foreach ($this->prepared[$statementId] as $st) {
      $results[] = $this->runOne($st, $this->bind($st, $params), '実行に失敗');
    }
    $first = array_shift($results);
    if (!empty($results)) $first['more'] = $results;
    return $first;
  }

  public function demolish(int $statementId): bool {
    if (!isset($this->prepared[$statementId])) return false;
    unset($this->prepared[$statementId]);
    return true;
  }

  public function beginTransaction(): bool {
    $this->query('BEGIN');
    return true;
  }

  public function commit(): bool {
    $this->query('COMMIT');
    return true;
  }

  public function rollback(): bool {
    $this->query('ROLLBACK');
    return true;
  }

  public function inTransaction(): bool {
    return $this->engine !== null && $this->engine->inTransaction();
  }

  public function quoteIdentifier(string $name): string {
    return '"'.str_replace('"', '""', $name).'"';
  }

  /** 最後に入れた行のrowid */
  public function lastInsertId(): int {
    return $this->engine?->lastInsertRowid ?? 0;
  }

  /** 情報：version、path、pageSize、pages、journal */
  public function getServerInfo(): array {
    return ['version' => '3.45.1', 'path' => $this->path, 'pageSize' => $this->pager?->pageSize, 'pages' => $this->pager?->nPages,
            'readOnly' => $this->pager?->readOnly ?? $this->readOnly];
  }

  ///////////////

  private static function emptyResult(): array {
    return ['fields' => [], 'rows' => [], 'affectedRows' => 0, 'insertId' => 0, 'warningCount' => 0];
  }

  /** パラメータを番号（1から）の配列にする */
  private function bind(array $st, array $params): array {
    $out = [];
    $isList = array_is_list($params);
    if ($isList) {
      if (count($params) !== $st['nParam']) {
        $this->fail('実行に失敗', new DbException("パラメータ数が一致しません: 期待 {$st['nParam']}, 取得 ".count($params), 1, 'HY000'));
      }
      foreach ($params as $i => $v) $out[$i + 1] = $this->value($v, $i);
      return $out;
    }
    foreach ($params as $k => $v) {
      if (is_int($k)) {
        $out[$k + 1] = $this->value($v, $k);
        continue;
      }
      $name = in_array($k[0] ?? '', [':', '@', '$'], true) ? $k : null;
      $i = null;
      foreach ($name !== null ? [$name] : [':'.$k, '@'.$k, '$'.$k] as $n) {
        if (isset($st['names'][$n])) $i = $st['names'][$n];
      }
      if ($i === null) $this->fail('実行に失敗', new DbException("パラメータがありません：{$k}", 1, 'HY000'));
      $out[$i] = $this->value($v, $k);
    }
    return $out;
  }

  private function value(mixed $v, int|string $i): mixed {
    if ($v instanceof \BackedEnum) $v = $v->value;
    if ($v instanceof \DateTimeInterface) $v = $v->format('u') === '000000' ? $v->format('Y-m-d H:i:s') : $v->format('Y-m-d H:i:s.v');
    if ($v === null || is_int($v) || is_float($v) || $v instanceof Blob) return $v;
    if (is_bool($v)) return (int)$v;
    if (is_string($v)) return mb_check_encoding($v, 'UTF-8') ? $v : new Blob($v);
    if ($v instanceof \Stringable) return (string)$v;
    $this->fail('実行に失敗', new DbException("パラメータ{$i}の型は未対応です: ".get_debug_type($v), 1, 'HY000'));
  }

  private function runOne(array $st, array $params, string $what): array {
    if ($this->debug) echo "=== SQLite === {$st['sql']}\n";
    $before = $this->engine->lastInsertRowid;
    try {
      $res = $this->engine->run($st, $params);
    } catch (DbException $e) {
      $this->fail($what, $e);
    }
    $fields = [];
    foreach ($res['cols'] as $i => $n) $fields[] = ['name' => $n, 'table' => $res['tables'][$i] ?? '', 'type' => $res['types'][$i] ?? ''];
    $rows = [];
    foreach ($res['rows'] as $vals) {
      $row = [];
      foreach ($res['cols'] as $i => $n) {
        $v = $vals[$i] ?? null;
        $row[$n] = $v instanceof Blob ? $v->data : $v;
      }
      $rows[] = $row;
    }
    $isInsert = $st['t'] === 'insert';
    return ['fields' => $fields, 'rows' => $rows, 'affectedRows' => in_array($st['t'], ['insert', 'update', 'delete'], true) ? $res['changes'] : count($rows),
            'insertId' => $isInsert && $this->engine->lastInsertRowid !== $before ? $this->engine->lastInsertRowid : ($isInsert ? $this->engine->lastInsertRowid : 0),
            'warningCount' => 0];
  }

  private function requireConnection(): void {
    if ($this->engine === null) $this->fail('', new DbException('接続していません（先にconnect()を呼んで下さい）', 0, 'HY000'));
  }

  /** @return never */
  private function fail(string $what, DbException $e): never {
    $msg = $what === '' ? $e->getMessage() : "{$what}: ".$e->getMessage();
    $this->log($msg);
    throw new DbException($msg, $e->getCode(), $e->sqlState, $e);
  }

  private function log(string $msg): void {
    if (function_exists('logger')) logger(\LogType::Db, $msg);
  }
}