<?php

declare(strict_types=1);

/**
 * This file is part of CodeIgniter 4 framework.
 *
 * (c) CodeIgniter Foundation <admin@codeigniter.com>
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace CodeIgniter\Database\SQLite3;

use CodeIgniter\Database\BaseConnection;
use CodeIgniter\Database\Exceptions\DatabaseException;
use CodeIgniter\Database\TableName;
use CodeIgniter\Exceptions\InvalidArgumentException;
use Exception;
use SQLite3;
use SQLite3Result;
use stdClass;

/**
 * Connection for SQLite3
 *
 * @extends BaseConnection<SQLite3, SQLite3Result>
 */
class Connection extends BaseConnection
{
    /**
     * Database driver
     *
     * @var string
     */
    public $DBDriver = 'SQLite3';

    /**
     * Identifier escape character
     *
     * @var string
     */
    public $escapeChar = '"';

    /**
     * @var bool Enable Foreign Key constraint or not
     */
    protected $foreignKeys = false;

    /**
     * The milliseconds to sleep when database is locked.
     * 迅睿默认 10s，避免并发写时立刻抛 database is locked。
     *
     * @var int|null milliseconds
     *
     * @see https://www.php.net/manual/en/sqlite3.busytimeout
     */
    protected $busyTimeout = 10000;

    /**
     * The setting of the "synchronous" flag
     *
     * @var int<0, 3>|null flag
     *
     * @see https://www.sqlite.org/pragma.html#pragma_synchronous
     */
    protected ?int $synchronous = null;

    /**
     * @return void
     */
    public function initialize()
    {
        parent::initialize();

        if ($this->foreignKeys) {
            $this->enableForeignKeyChecks();
        }

        $timeout = is_int($this->busyTimeout) ? $this->busyTimeout : 10000;
        if ($timeout > 0 && $this->connID instanceof SQLite3) {
            $this->connID->busyTimeout($timeout);
            // 双保险：部分环境 busyTimeout() 对 PRAGMA 路径不生效
            @$this->connID->exec('PRAGMA busy_timeout = ' . $timeout);
        }

        // WAL 提升并发读；内存库跳过
        if ($this->connID instanceof SQLite3 && $this->database !== ':memory:') {
            @$this->connID->exec('PRAGMA journal_mode = WAL');
            @$this->connID->exec('PRAGMA synchronous = NORMAL');
        }

        if (is_int($this->synchronous)) {
            if (! in_array($this->synchronous, [0, 1, 2, 3], true)) {
                throw new InvalidArgumentException('Invalid synchronous value.');
            }
            $this->connID->exec('PRAGMA synchronous = ' . $this->synchronous);
        }
    }

    /**
     * Connect to the database.
     *
     * @return SQLite3
     *
     * @throws DatabaseException
     */
    public function connect(bool $persistent = false)
    {
        if ($persistent && $this->DBDebug) {
            throw new DatabaseException('SQLite3 doesn\'t support persistent connections.');
        }

        try {
            if ($this->database !== ':memory:' && ! str_contains($this->database, DIRECTORY_SEPARATOR)) {
                $this->database = WRITEPATH . $this->database;
            }

            $sqlite = (! isset($this->password) || $this->password !== '')
                ? new SQLite3($this->database)
                : new SQLite3($this->database, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, $this->password);

            $sqlite->enableExceptions(true);

            return $sqlite;
        } catch (Exception $e) {
            throw new DatabaseException('SQLite3 error: ' . $e->getMessage());
        }
    }

    /**
     * Keep or establish the connection if no queries have been sent for
     * a length of time exceeding the server's idle timeout.
     *
     * @return void
     */
    public function reconnect()
    {
        $this->close();
        $this->initialize();
    }

    /**
     * Close the database connection.
     *
     * @return void
     */
    protected function _close()
    {
        $this->connID->close();
    }

    /**
     * Select a specific database table to use.
     */
    public function setDatabase(string $databaseName): bool
    {
        return false;
    }

    /**
     * Returns a string containing the version of the database being used.
     */
    public function getVersion(): string
    {
        if (isset($this->dataCache['version'])) {
            return $this->dataCache['version'];
        }

        $version = SQLite3::version();

        return $this->dataCache['version'] = $version['versionString'];
    }

    /**
     * Execute the query
     *
     * @return false|SQLite3Result
     */
    protected function execute(string $sql)
    {
        try {
            return $this->isWriteType($sql)
                ? $this->connID->exec($sql)
                : $this->connID->query($sql);
        } catch (Exception $e) {
            log_message('error', (string) $e);

            if ($this->DBDebug) {
                throw new DatabaseException($e->getMessage(), $e->getCode(), $e);
            }
        }

        return false;
    }

    /**
     * Returns the total number of rows affected by this query.
     */
    public function affectedRows(): int
    {
        return $this->connID->changes();
    }

    /**
     * Platform-dependant string escape
     */
    protected function _escapeString(string $str): string
    {
        if (! $this->connID instanceof SQLite3) {
            $this->initialize();
        }

        return $this->connID->escapeString($str);
    }

    /**
     * Generates the SQL for listing tables in a platform-dependent manner.
     *
     * @param string|null $tableName If $tableName is provided will return only this table if exists.
     */
    protected function _listTables(bool $prefixLimit = false, ?string $tableName = null): string
    {
        if ((string) $tableName !== '') {
            return 'SELECT "NAME" FROM "SQLITE'.'_'.'MASTER" WHERE "TYPE" = \'table\''
                   . ' AND "NAME" NOT LIKE \'sqlite!_%\' ESCAPE \'!\''
                   . ' AND "NAME" LIKE ' . $this->escape($tableName);
        }

        return 'SELECT "NAME" FROM "SQLITE'.'_'.'MASTER" WHERE "TYPE" = \'table\''
               . ' AND "NAME" NOT LIKE \'sqlite!_%\' ESCAPE \'!\''
               . (($prefixLimit && $this->DBPrefix !== '')
                    ? ' AND "NAME" LIKE \'' . $this->escapeLikeString($this->DBPrefix) . '%\' ' . sprintf($this->likeEscapeStr, $this->likeEscapeChar)
                    : '');
    }

    /**
     * Generates a platform-specific query string so that the column names can be fetched.
     *
     * @param string|TableName $table
     */
    protected function _listColumns($table = ''): string
    {
        if ($table instanceof TableName) {
            $tableName = $this->escapeIdentifier($table);
        } else {
            $tableName = $this->protectIdentifiers($table, true, null, false);
        }

        return 'PRAGMA TABLE_INFO(' . $tableName . ')';
    }

    /**
     * @param string|TableName $tableName
     *
     * @return false|list<string>
     *
     * @throws DatabaseException
     */
    public function getFieldNames($tableName)
    {
        $table = ($tableName instanceof TableName) ? $tableName->getTableName() : $tableName;

        // Is there a cached result?
        if (isset($this->dataCache['field_names'][$table])) {
            return $this->dataCache['field_names'][$table];
        }

        if (! $this->connID instanceof SQLite3) {
            $this->initialize();
        }

        $sql = $this->_listColumns($tableName);

        $query                                  = $this->query($sql);
        $this->dataCache['field_names'][$table] = [];

        foreach ($query->getResultArray() as $row) {
            // Do we know from where to get the column's name?
            if (! isset($key)) {
                if (isset($row['column_name'])) {
                    $key = 'column_name';
                } elseif (isset($row['COLUMN_NAME'])) {
                    $key = 'COLUMN_NAME';
                } elseif (isset($row['name'])) {
                    $key = 'name';
                } else {
                    // We have no other choice but to just get the first element's key.
                    $key = key($row);
                }
            }

            $this->dataCache['field_names'][$table][] = $row[$key];
        }

        return $this->dataCache['field_names'][$table];
    }

    /**
     * Returns an array of objects with field data
     *
     * @return list<stdClass>
     *
     * @throws DatabaseException
     */
    protected function _fieldData(string $table): array
    {
        if (false === $query = $this->query('PRAGMA TABLE_INFO(' . $this->protectIdentifiers($table, true, null, false) . ')')) {
            throw new DatabaseException(lang('Database.failGetFieldData'));
        }

        $query = $query->getResultObject();

        if (empty($query)) {
            return [];
        }

        $retVal = [];

        for ($i = 0, $c = count($query); $i < $c; $i++) {
            $retVal[$i] = new stdClass();

            $retVal[$i]->name       = $query[$i]->name;
            $retVal[$i]->type       = $query[$i]->type;
            $retVal[$i]->max_length = null;
            $retVal[$i]->nullable   = isset($query[$i]->notnull) && ! (bool) $query[$i]->notnull;
            $retVal[$i]->default    = $query[$i]->dflt_value;
            // "pk" (either zero for columns that are not part of the primary key,
            // or the 1-based index of the column within the primary key).
            // https://www.sqlite.org/pragma.html#pragma_table_info
            $retVal[$i]->primary_key = ($query[$i]->pk === 0) ? 0 : 1;
        }

        return $retVal;
    }

    /**
     * Returns an array of objects with index data
     *
     * @return array<string, stdClass>
     *
     * @throws DatabaseException
     */
    protected function _indexData(string $table): array
    {
        $sql = "SELECT 'PRIMARY' as indexname, l.name as fieldname, 'PRIMARY' as indextype
                FROM pragma_table_info(" . $this->escape(strtolower($table)) . ") as l
                WHERE l.pk <> 0
                UNION ALL
                SELECT sqlite_master.name as indexname, ii.name as fieldname,
                CASE
                WHEN ti.pk <> 0 AND sqlite_master.name LIKE 'sqlite_autoindex_%' THEN 'PRIMARY'
                WHEN sqlite_master.name LIKE 'sqlite_autoindex_%' THEN 'UNIQUE'
                WHEN sqlite_master.sql LIKE '% UNIQUE %' THEN 'UNIQUE'
                ELSE 'INDEX'
                END as indextype
                FROM sqlite_master
                INNER JOIN pragma_index_xinfo(sqlite_master.name) ii ON ii.name IS NOT NULL
                LEFT JOIN pragma_table_info(" . $this->escape(strtolower($table)) . ") ti ON ti.name = ii.name
                WHERE sqlite_master.type='index' AND sqlite_master.tbl_name = " . $this->escape(strtolower($table)) . ' COLLATE NOCASE';

        if (($query = $this->query($sql)) === false) {
            throw new DatabaseException(lang('Database.failGetIndexData'));
        }
        $query = $query->getResultObject();

        $tempVal = [];

        foreach ($query as $row) {
            if ($row->indextype === 'PRIMARY') {
                $tempVal['PRIMARY']['indextype']               = $row->indextype;
                $tempVal['PRIMARY']['indexname']               = $row->indexname;
                $tempVal['PRIMARY']['fields'][$row->fieldname] = $row->fieldname;
            } else {
                $tempVal[$row->indexname]['indextype']               = $row->indextype;
                $tempVal[$row->indexname]['indexname']               = $row->indexname;
                $tempVal[$row->indexname]['fields'][$row->fieldname] = $row->fieldname;
            }
        }

        $retVal = [];

        foreach ($tempVal as $val) {
            $obj                = new stdClass();
            $obj->name          = $val['indexname'];
            $obj->fields        = array_values($val['fields']);
            $obj->type          = $val['indextype'];
            $retVal[$obj->name] = $obj;
        }

        return $retVal;
    }

    /**
     * Returns an array of objects with Foreign key data
     *
     * @return array<string, stdClass>
     */
    protected function _foreignKeyData(string $table): array
    {
        if (! $this->supportsForeignKeys()) {
            return [];
        }

        $query   = $this->query("PRAGMA foreign_key_list({$table})")->getResult();
        $indexes = [];

        foreach ($query as $row) {
            $indexes[$row->id]['constraint_name']       = null;
            $indexes[$row->id]['table_name']            = $table;
            $indexes[$row->id]['foreign_table_name']    = $row->table;
            $indexes[$row->id]['column_name'][]         = $row->from;
            $indexes[$row->id]['foreign_column_name'][] = $row->to;
            $indexes[$row->id]['on_delete']             = $row->on_delete;
            $indexes[$row->id]['on_update']             = $row->on_update;
            $indexes[$row->id]['match']                 = $row->match;
        }

        return $this->foreignKeyDataToObjects($indexes);
    }

    /**
     * Returns platform-specific SQL to disable foreign key checks.
     *
     * @return string
     */
    protected function _disableForeignKeyChecks()
    {
        return 'PRAGMA foreign_keys = OFF';
    }

    /**
     * Returns platform-specific SQL to enable foreign key checks.
     *
     * @return string
     */
    protected function _enableForeignKeyChecks()
    {
        return 'PRAGMA foreign_keys = ON';
    }

    /**
     * Returns the last error code and message.
     * Must return this format: ['code' => string|int, 'message' => string]
     * intval(code) === 0 means "no error".
     *
     * @return array<string, int|string>
     */
    public function error(): array
    {
        return [
            'code'    => $this->connID->lastErrorCode(),
            'message' => $this->connID->lastErrorMsg(),
        ];
    }

    /**
     * Insert ID
     */
    public function insertID(): int
    {
        return $this->connID->lastInsertRowID();
    }

    /**
     * Begin Transaction
     */
    protected function _transBegin(): bool
    {
        return $this->connID->exec('BEGIN TRANSACTION');
    }

    /**
     * Commit Transaction
     */
    protected function _transCommit(): bool
    {
        return $this->connID->exec('END TRANSACTION');
    }

    /**
     * Rollback Transaction
     */
    protected function _transRollback(): bool
    {
        return $this->connID->exec('ROLLBACK');
    }

    /**
     * Checks to see if the current install supports Foreign Keys
     * and has them enabled.
     */
    public function supportsForeignKeys(): bool
    {
        $result = $this->simpleQuery('PRAGMA foreign_keys');

        return (bool) $result;
    }

    // --------------------------------------------------------------------
    // 迅睿 DDL API（与 MySQLi\Connection 同名，供 Fcms/Model/Table 等调用）
    // --------------------------------------------------------------------

    /**
     * 拦截 MySQL 风格 ALTER ... CHANGE，改为重建表修改列
     *
     * {@inheritDoc}
     */
    public function query(string $sql, $binds = null, bool $setEscapeFlags = true, string $queryClass = '')
    {
        if ($binds === null && is_string($sql)) {
            $trimmed = ltrim($sql);
            $id = '(?:[`"]?)([^\s`"]+)(?:[`"]?)';
            if (preg_match(
                '/^ALTER\s+TABLE\s+'.$id.'\s+CHANGE\s+'.$id.'\s+'.$id.'\s+(.+)$/is',
                $trimmed,
                $m
            )) {
                $def = trim($m[4]);
                $def = preg_replace("/\s+COMMENT\s+'((?:''|[^'])*)'/i", '', $def) ?? $def;
                $def = preg_replace('/\s+COMMENT\s+"((?:""|[^"])*)"/i', '', $def) ?? $def;
                if (preg_match('/^(\S+(?:\([^)]*\))?)\s*(.*)$/s', trim($def), $d)) {
                    $rt = $this->editField($m[1], $m[2], $d[1], trim($d[2]), '');

                    return $rt !== false;
                }
            }
            if (preg_match(
                '/^ALTER\s+TABLE\s+'.$id.'\s+DROP(?:\s+COLUMN)?\s+'.$id.'\s*$/is',
                $trimmed,
                $m
            )) {
                $rt = $this->dropField($m[1], $m[2]);

                return $rt !== false;
            }
        }

        return parent::query($sql, $binds, $setEscapeFlags, $queryClass);
    }

    /**
     * 规范化建表/DDL SQL（Install.sql 等以 MySQL 方言为源 → SQLite）
     */
    public function formatCreateSql($sql)
    {
        if ($sql === '' || $sql === null) {
            return '';
        }

        $sql = trim($sql);

        // DML 兼容
        $sql = preg_replace('/\bREPLACE\s+INTO\b/i', 'INSERT OR REPLACE INTO', $sql) ?? $sql;
        $sql = preg_replace('/\bINSERT\s+IGNORE\s+INTO\b/i', 'INSERT OR IGNORE INTO', $sql) ?? $sql;

        // DROP TABLE 可直接用
        if (preg_match('/^DROP\s+TABLE\b/i', $sql)) {
            return $sql;
        }

        // ALTER TABLE ADD / DROP（去掉 COMMENT）
        if (preg_match('/^ALTER\s+TABLE\b/i', $sql)) {
            $sql = preg_replace("/\s+COMMENT\s+'((?:''|[^'])*)'/i", '', $sql) ?? $sql;
            $sql = preg_replace('/\s+COMMENT\s+"((?:""|[^"])*)"/i', '', $sql) ?? $sql;
            // CHANGE 留给 query() 拦截重建
            if (preg_match('/^ALTER\s+TABLE\s+[`]?([^`\s]+)[`]?\s+CHANGE\b/i', $sql)) {
                return $sql;
            }
            // DROP COLUMN 留给 query() 走重建（兼容旧 SQLite）
            if (preg_match('/^ALTER\s+TABLE\s+[`]?([^`\s]+)[`]?\s+DROP(?:\s+COLUMN)?\s+/i', $sql)) {
                return $sql;
            }
            // ADD 列类型映射
            if (preg_match('/^(ALTER\s+TABLE\s+[`]?[^`\s]+[`]?\s+ADD(?:\s+COLUMN)?\s+[`]?[^`\s]+[`]?\s+)(.+)$/is', $sql, $m)) {
                return $m[1] . $this->_xrNormalizeMysqlColumnLine($m[2]);
            }

            return $sql;
        }

        // CREATE TABLE
        if (preg_match('/^CREATE\s+TABLE\b/i', $sql)) {
            return $this->_xrFormatCreateTableSql($sql);
        }

        return $sql;
    }

    /**
     * 生成「添加字段」SQL（表名占位符 {tablename}）
     */
    public function sqlAddField($name, $type, $info, $note = '')
    {
        $def = $this->_xrNormalizeMysqlColumnLine(trim($type . ' ' . $info));

        // 表名用占位符，避免反引号在 SQLite 下偶发语法问题；执行时再 escapeIdentifiers
        return 'ALTER TABLE {tablename} ADD COLUMN ' . $this->escapeIdentifiers($name) . ' ' . $def;
    }

    /**
     * 生成「修改字段」SQL（MySQL CHANGE 形态；执行时由 query() 拦截并重建表）
     */
    public function sqlEditField($name, $type, $info, $note = '')
    {
        $id  = $this->escapeIdentifiers($name);
        $def = $this->_xrNormalizeMysqlColumnLine(trim($type . ' ' . $info));

        return 'ALTER TABLE {tablename} CHANGE ' . $id . ' ' . $id . ' ' . $def;
    }

    /**
     * 生成「删除字段」SQL
     */
    public function sqlDropField($name)
    {
        return 'ALTER TABLE {tablename} DROP COLUMN ' . $this->escapeIdentifiers($name);
    }

    /**
     * 批量添加字段
     */
    public function sqlAddFields(array $columns)
    {
        // SQLite 一条 ALTER 只能 ADD 一列，拆成多条由调用方逐条执行不现实；
        // 返回第一条，其余由 sqlAddField 循环更稳妥。这里用分号拼接，simpleQuery/exec 可多语句。
        $parts = [];
        foreach ($columns as $col) {
            if (empty($col['name'])) {
                continue;
            }
            $parts[] = $this->sqlAddField(
                $col['name'],
                $col['type'] ?? '',
                $col['info'] ?? '',
                $col['note'] ?? ''
            );
        }

        return $parts === [] ? '' : implode(";\n", $parts);
    }

    /**
     * 批量删除字段
     */
    public function sqlDropFields(array $names)
    {
        $parts = [];
        foreach ($names as $name) {
            if ($name === '' || $name === null) {
                continue;
            }
            $parts[] = $this->sqlDropField($name);
        }

        return $parts === [] ? '' : implode(";\n", $parts);
    }

    /**
     * 删除表
     */
    public function dropTable($table, $ifExists = true)
    {
        $table = trim((string) $table, '`');
        $sql   = ($ifExists ? 'DROP TABLE IF EXISTS ' : 'DROP TABLE ')
            . $this->escapeIdentifiers($table);

        return $this->query($sql);
    }

    /**
     * 修改字段（重建表）
     */
    public function editField($table, $name, $type, $info, $note)
    {
        $table = trim((string) $table, '`"[]');
        $name  = trim((string) $name, '`"[]');
        $type  = $this->_xrMapMysqlTypeOnly((string) $type);
        $info  = (string) $info;
        $null  = stripos($info, 'NOT NULL') === false;

        $field = [
            'name' => $name,
            'type' => $type,
            'null' => $null,
        ];

        if (preg_match('/DEFAULT\s+(NULL|\'(?:\'\'|[^\'])*\'|"(?:""|[^"])*"|\S+)/i', $info, $m)) {
            $default = $m[1];
            if (strtoupper($default) === 'NULL') {
                $field['default'] = null;
            } else {
                $field['default'] = trim($default, "'\"");
            }
        }

        try {
            $forge = new Forge($this);

            return (new Table($this, $forge))
                ->fromTable($table)
                ->modifyColumn([$field])
                ->run();
        } catch (\Throwable $e) {
            log_message('error', 'SQLite3 editField: ' . $e->getMessage());

            return false;
        }
    }

    /**
     * 添加字段
     */
    public function addField($table, $name, $type, $info, $note)
    {
        $table = trim((string) $table, '`"[]');

        return $this->query(str_replace(
            '{tablename}',
            $this->escapeIdentifiers($table),
            $this->sqlAddField($name, $type, $info, $note)
        ));
    }

    /**
     * 删除字段（重建表，兼容旧版 SQLite）
     */
    public function dropField($table, $name)
    {
        $table = trim((string) $table, '`"[]');
        $name  = trim((string) $name, '`"[]');

        try {
            $forge = new Forge($this);

            return (new Table($this, $forge))
                ->fromTable($table)
                ->dropColumn($name)
                ->run();
        } catch (\Throwable $e) {
            log_message('error', 'SQLite3 dropField: ' . $e->getMessage());

            return false;
        }
    }

    /**
     * 创建表
     *
     * @param array  $fields ['字段名' => '类型及属性SQL'] 或 [0 => '完整列定义SQL']
     * @param array  $indexs ['PRIMARY KEY (`id`)', 'KEY `name` (`name`)', ...]
     * @param string $note   表注释（SQLite 忽略，仅日志无关）
     */
    public function createTable($table, $fields, $indexs, $note)
    {
        $table        = trim((string) $table, '`"[]');
        $lines        = [];
        $extraIndexes = [];
        $pkInline     = false;

        if (is_array($fields)) {
            foreach ($fields as $name => $def) {
                if (is_int($name)) {
                    $line = $this->_xrNormalizeMysqlColumnLine((string) $def);
                    if (preg_match('/PRIMARY\s+KEY/i', $line) && preg_match('/AUTOINCREMENT/i', $line)) {
                        $pkInline = true;
                    }
                    $lines[] = $line;
                } else {
                    $mapped = $this->_xrNormalizeMysqlColumnLine((string) $def);
                    if (preg_match('/PRIMARY\s+KEY/i', $mapped) && preg_match('/AUTOINCREMENT/i', $mapped)) {
                        $pkInline = true;
                    }
                    $lines[] = $this->escapeIdentifiers($name) . ' ' . $mapped;
                }
            }
        }

        if (is_array($indexs)) {
            foreach ($indexs as $idx) {
                $idx = trim((string) $idx);
                if ($idx === '') {
                    continue;
                }
                if (preg_match('/^PRIMARY\s+KEY/i', $idx)) {
                    if (! $pkInline) {
                        // 去掉 MySQL 反引号
                        $idx = preg_replace('/[`"]/', '', $idx) ?? $idx;
                        $lines[] = $idx;
                    }
                    continue;
                }
                // UNIQUE KEY `uid` (`uid`) → UNIQUE (`uid`)
                if (preg_match('/^UNIQUE(?:\s+KEY|\s+INDEX)?\s+(?:[`"]?\w+[`]?\s+)?\((.+)\)/i', $idx, $m)) {
                    $cols = preg_replace('/[`"]/', '', $this->_xrStripMysqlIndexPrefix($m[1])) ?? $m[1];
                    $lines[] = 'UNIQUE (' . $cols . ')';
                    continue;
                }
                // KEY `name` (`col`) / INDEX → 表外 CREATE INDEX（索引名加表前缀，SQLite 全局唯一）
                if (preg_match('/^(?:KEY|INDEX)\s+(?:[`"]?(\w+)[`]?\s+)?\((.+)\)/i', $idx, $m)) {
                    $extraIndexes[] = [
                        'name' => $m[1] ?? '',
                        'cols' => preg_replace('/[`"]/', '', $this->_xrStripMysqlIndexPrefix($m[2])) ?? $m[2],
                    ];
                    continue;
                }
                $lines[] = preg_replace('/[`"]/', '', $idx) ?? $idx;
            }
        }

        if ($lines === []) {
            log_message('error', 'SQLite3 createTable: empty fields for ' . $table);

            return false;
        }

        $sql = 'CREATE TABLE IF NOT EXISTS ' . $this->escapeIdentifiers($table) . " (
" . implode(",\n", $lines) . '
)';

        try {
            $rt = $this->query($sql);
        } catch (\Throwable $e) {
            log_message('error', 'SQLite3 createTable: ' . $e->getMessage() . ' SQL: ' . $sql);

            return false;
        }

        if ($rt === false) {
            $err = $this->error();
            log_message('error', 'SQLite3 createTable failed: ' . ($err['message'] ?? '') . ' SQL: ' . $sql);

            return false;
        }

        // 建表后刷新表名缓存，避免 tableExists 误判
        $this->resetDataCache();

        $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '_', $table) ?: 't';
        foreach ($extraIndexes as $i => $ix) {
            $base    = $ix['name'] !== '' ? $ix['name'] : ('idx_' . $i);
            $idxName = $safeTable . '_' . preg_replace('/[^a-zA-Z0-9_]/', '_', $base);
            try {
                $this->query(
                    'CREATE INDEX IF NOT EXISTS ' . $this->escapeIdentifiers($idxName)
                    . ' ON ' . $this->escapeIdentifiers($table) . ' (' . $ix['cols'] . ')'
                );
            } catch (\Throwable $e) {
                log_message('error', 'SQLite3 createIndex: ' . $e->getMessage());
            }
        }

        return $rt;
    }

    /**
     * 获取建表 SQL（CREATE TABLE IF NOT EXISTS ...）
     */
    public function createTableSql($table)
    {
        $table = trim((string) $table, '`');
        $row   = $this->query(
            'SELECT sql FROM sqlite_master WHERE type=' . $this->escape('table')
            . ' AND name=' . $this->escape($table) . ' COLLATE NOCASE'
        )->getRowArray();

        if (! $row || empty($row['sql'])) {
            return '';
        }

        $sql = trim($row['sql']);
        if (stripos($sql, 'IF NOT EXISTS') === false) {
            $sql = preg_replace('/^CREATE\s+TABLE\b/i', 'CREATE TABLE IF NOT EXISTS', $sql) ?? $sql;
        }

        return $sql;
    }

    /**
     * 获取表完整字段信息（兼容 SHOW FULL COLUMNS 结果键名）
     */
    public function showFullColunms($table)
    {
        $table = trim((string) $table, '`');
        $rows  = $this->query(
            'PRAGMA TABLE_INFO(' . $this->protectIdentifiers($table, true, null, false) . ')'
        )->getResultArray();

        $out = [];
        foreach ($rows as $r) {
            $out[] = [
                'Field'      => $r['name'] ?? '',
                'Type'       => $r['type'] ?? '',
                'Collation'  => null,
                'Null'       => empty($r['notnull']) ? 'YES' : 'NO',
                'Key'        => ! empty($r['pk']) ? 'PRI' : '',
                'Default'    => $r['dflt_value'] ?? null,
                'Extra'      => '',
                'Privileges' => '',
                'Comment'    => '',
            ];
        }

        return $out;
    }

    /**
     * 获取全部表状态（兼容 SHOW TABLE STATUS 常用键）
     */
    public function showTableStatus()
    {
        $tables = $this->query(
            "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
        )->getResultArray();

        $out = [];
        foreach ($tables as $t) {
            $name = $t['name'];
            $cnt  = $this->query('SELECT COUNT(*) AS c FROM ' . $this->escapeIdentifiers($name))->getRowArray();
            $out[] = [
                'Name'            => $name,
                'Engine'          => 'SQLite3',
                'Version'         => null,
                'Row_format'      => null,
                'Rows'            => isset($cnt['c']) ? (int) $cnt['c'] : 0,
                'Avg_row_length'  => null,
                'Data_length'     => 0,
                'Max_data_length' => null,
                'Index_length'    => 0,
                'Data_free'       => 0,
                'Auto_increment'  => null,
                'Create_time'     => null,
                'Update_time'     => null,
                'Check_time'      => null,
                'Collation'       => 'BINARY',
                'Checksum'        => null,
                'Create_options'  => null,
                'Comment'         => '',
            ];
        }

        return $out;
    }

    /**
     * 修复表（SQLite 无对应操作，返回 true）
     */
    public function repairTable($table)
    {
        return true;
    }

    /**
     * 优化表 → VACUUM（库级，忽略单表名）
     */
    public function optimizeTable($table)
    {
        return $this->query('VACUUM');
    }

    /**
     * 刷新表（无操作）
     */
    public function flushTable($table)
    {
        return true;
    }

    /**
     * 检查表 → integrity_check，返回兼容 Msg_text 的一行
     */
    public function checkTable($table)
    {
        $row = $this->query('PRAGMA integrity_check')->getRowArray();
        $msg = is_array($row) ? (string) reset($row) : 'unknown';

        return [
            'Table'    => trim((string) $table, '`'),
            'Op'       => 'check',
            'Msg_type' => strtolower($msg) === 'ok' ? 'status' : 'error',
            'Msg_text' => $msg,
        ];
    }

    /**
     * 修改表字符集（SQLite 固定 UTF-8，无操作）
     */
    public function setTableCharset($table, $charset = 'utf8mb4', $collate = 'utf8mb4_unicode_ci')
    {
        return true;
    }

    // --------------------------------------------------------------------
    // 迅睿 DDL 内部辅助
    // --------------------------------------------------------------------

    /**
     * 将 MySQL CREATE TABLE 转为 SQLite（KEY 拆成后续 CREATE INDEX）
     */
    protected function _xrFormatCreateTableSql(string $sql): string
    {
        // 去掉表尾 ENGINE/CHARSET/COLLATE/COMMENT
        $sql = preg_replace(
            '/\)\s*ENGINE\s*=\s*\w+[^;]*$/i',
            ')',
            $sql
        ) ?? $sql;
        $sql = preg_replace('/\)\s*DEFAULT\s+CHARSET\s*=\s*\w+[^;]*$/i', ')', $sql) ?? $sql;
        $sql = preg_replace('/\)\s*COMMENT\s*=\s*\'(?:\'\'|[^\'])*\'\s*$/i', ')', $sql) ?? $sql;
        $sql = preg_replace('/\s+CHARACTER\s+SET\s+\w+/i', '', $sql) ?? $sql;
        $sql = preg_replace('/\s+COLLATE\s+\w+/i', '', $sql) ?? $sql;

        if (! preg_match('/^(CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?\s+)([^\s(]+)(\s*\(.*\))\s*$/is', $sql, $m)) {
            // 宽松：仍做列级替换
            return $this->_xrNormalizeMysqlColumnLine($sql);
        }

        $prefix = $m[1];
        $table  = trim($m[2], '`"');
        $body   = trim($m[3]);
        $body   = substr($body, 1, -1); // 去括号

        $parts = $this->_xrSplitSqlColumns($body);
        $cols  = [];
        $idxs  = [];
        $pkInline = false;

        foreach ($parts as $part) {
            $part = trim($part);
            if ($part === '') {
                continue;
            }
            if (preg_match('/^PRIMARY\s+KEY/i', $part)) {
                if (! $pkInline) {
                    $cols[] = $part;
                }
                continue;
            }
            if (preg_match('/^UNIQUE(?:\s+KEY|\s+INDEX)?\s+(?:[`"]?\w+[`]?\s+)?\((.+)\)/i', $part, $um)) {
                $cols[] = 'UNIQUE (' . $this->_xrStripMysqlIndexPrefix($um[1]) . ')';
                continue;
            }
            if (preg_match('/^(?:KEY|INDEX)\s+(?:[`"]?(\w+)[`]?\s+)?\((.+)\)/i', $part, $km)) {
                $idxs[] = [
                    'name' => $km[1] ?? '',
                    'cols' => $this->_xrStripMysqlIndexPrefix($km[2]),
                ];
                continue;
            }
            if (preg_match('/^CONSTRAINT\b/i', $part)) {
                // 跳过复杂约束或保留 UNIQUE/PK 子句
                if (preg_match('/PRIMARY\s+KEY/i', $part) || preg_match('/UNIQUE/i', $part)) {
                    $cols[] = $part;
                }
                continue;
            }

            // 普通列：`name` def 或 name def
            if (preg_match('/^[`"]?(\w+)[`"]?\s+(.+)$/s', $part, $cm)) {
                $mapped = $this->_xrNormalizeMysqlColumnLine($cm[2]);
                if (preg_match('/PRIMARY\s+KEY/i', $mapped) && preg_match('/AUTOINCREMENT/i', $mapped)) {
                    $pkInline = true;
                }
                $cols[] = $this->escapeIdentifiers($cm[1]) . ' ' . $mapped;
            } else {
                $cols[] = $this->_xrNormalizeMysqlColumnLine($part);
            }
        }

        $create = $prefix . $this->escapeIdentifiers($table) . " (
" . implode(",\n", $cols) . '
)';

        foreach ($idxs as $i => $ix) {
            $idxName = $ix['name'] !== '' ? $ix['name'] : ($table . '_idx_' . $i);
            $idxName = preg_replace('/[^a-zA-Z0-9_]/', '_', $idxName);
            $create .= ";\nCREATE INDEX IF NOT EXISTS " . $this->escapeIdentifiers($idxName)
                . ' ON ' . $this->escapeIdentifiers($table) . ' (' . $ix['cols'] . ')';
        }

        return $create;
    }

    /**
     * 去掉 MySQL 索引列前缀长度：`col`(191) / col(20) → col
     * SQLite 会把 col(20) 解析成函数调用，导致 no such function: col
     */
    protected function _xrStripMysqlIndexPrefix(string $cols): string
    {
        return preg_replace('/([`"\']?\w+[`"\']?)\s*\(\s*\d+\s*\)/', '$1', $cols) ?? $cols;
    }

    /**
     * 按逗号拆分 CREATE 括号内定义（忽略括号内逗号）
     */
    protected function _xrSplitSqlColumns(string $body): array
    {
        $parts  = [];
        $buf    = '';
        $depth  = 0;
        $len    = strlen($body);
        $quote  = null;

        for ($i = 0; $i < $len; $i++) {
            $ch = $body[$i];
            if ($quote !== null) {
                $buf .= $ch;
                if ($ch === $quote) {
                    // 处理 '' 转义
                    if ($ch === "'" && $i + 1 < $len && $body[$i + 1] === "'") {
                        $buf .= $body[++$i];
                        continue;
                    }
                    $quote = null;
                }
                continue;
            }
            if ($ch === "'" || $ch === '"' || $ch === '`') {
                $quote = $ch;
                $buf .= $ch;
                continue;
            }
            if ($ch === '(') {
                $depth++;
                $buf .= $ch;
                continue;
            }
            if ($ch === ')') {
                $depth--;
                $buf .= $ch;
                continue;
            }
            if ($ch === ',' && $depth === 0) {
                $parts[] = trim($buf);
                $buf     = '';
                continue;
            }
            $buf .= $ch;
        }
        if (trim($buf) !== '') {
            $parts[] = trim($buf);
        }

        return $parts;
    }

    /**
     * 规范化单列 MySQL 定义片段为 SQLite
     */
    protected function _xrNormalizeMysqlColumnLine(string $def): string
    {
        $def = preg_replace("/\s+COMMENT\s+'((?:''|[^'])*)'/i", '', $def) ?? $def;
        $def = preg_replace('/\s+COMMENT\s+"((?:""|[^"])*)"/i', '', $def) ?? $def;
        $def = preg_replace('/\bUNSIGNED\b/i', '', $def) ?? $def;
        $def = preg_replace('/\bZEROFILL\b/i', '', $def) ?? $def;
        $def = preg_replace('/\bCHARACTER\s+SET\s+\w+/i', '', $def) ?? $def;
        $def = preg_replace('/\bCOLLATE\s+\w+/i', '', $def) ?? $def;

        $def = preg_replace('/\b(LONGTEXT|MEDIUMTEXT|TINYTEXT)\b/i', 'TEXT', $def) ?? $def;
        $def = preg_replace('/\bDOUBLE(?:\s*\([^)]*\))?/i', 'REAL', $def) ?? $def;
        $def = preg_replace('/\bFLOAT(?:\s*\([^)]*\))?/i', 'REAL', $def) ?? $def;
        $def = preg_replace('/\bDECIMAL(?:\s*\([^)]*\))?/i', 'NUMERIC', $def) ?? $def;
        $def = preg_replace('/\bDATETIME\b/i', 'TEXT', $def) ?? $def;
        $def = preg_replace('/\bTIMESTAMP\b/i', 'TEXT', $def) ?? $def;
        $def = preg_replace('/\bENUM\s*\([^)]*\)/i', 'TEXT', $def) ?? $def;
        $def = preg_replace('/\bSET\s*\([^)]*\)/i', 'TEXT', $def) ?? $def;

        $hasAi = (bool) preg_match('/\bAUTO_INCREMENT\b/i', $def);
        $def   = preg_replace('/\bAUTO_INCREMENT\b/i', '', $def) ?? $def;

        // int(N) / tinyint 等 → INTEGER
        $def = preg_replace('/\b(?:TINY|SMALL|MEDIUM|BIG)?INT(?:EGER)?\s*\(\d+\)/i', 'INTEGER', $def) ?? $def;
        $def = preg_replace('/\b(?:TINY|SMALL|MEDIUM|BIG)?INT\b/i', 'INTEGER', $def) ?? $def;

        if ($hasAi) {
            // SQLite：AUTOINCREMENT 仅用于 INTEGER PRIMARY KEY
            return 'INTEGER PRIMARY KEY AUTOINCREMENT';
        }

        return trim(preg_replace('/\s+/', ' ', $def) ?? $def);
    }

    /**
     * 仅映射类型名（不含属性）
     */
    protected function _xrMapMysqlTypeOnly(string $type): string
    {
        $type = trim($type);
        $tmp  = $this->_xrNormalizeMysqlColumnLine($type);
        if (preg_match('/^(\S+)/', $tmp, $m)) {
            return $m[1];
        }

        return $type;
    }

    // --------------------------------------------------------------------
    // 字段搜索 WHERE 表达式（覆盖 Model 默认 MySQL 写法）
    // --------------------------------------------------------------------

    /**
     * FIND_IN_SET → SQLite 逗号包围 LIKE
     */
    public function whereFindInSet($column, $value)
    {
        $column = (string) $column;
        if (dr_is_numeric($value)) {
            $safe = (string) (int) $value;
        } else {
            $safe = str_replace(["\\", "'"], ["\\\\", "''"], dr_safe_replace($value));
        }

        return "(',' || IFNULL(" . $column . ",'') || ',') LIKE '%," . $safe . ",%'";
    }

    /**
     * JSON 包含 → SQLite LIKE 兼容
     */
    public function whereJson($table, $name, $value)
    {
        if (strpos($name, '`') === false && strpos($name, '"') === false) {
            $name = $table
                ? $this->escapeIdentifiers($table) . '.' . $this->escapeIdentifiers($name)
                : $this->escapeIdentifiers($name);
        }

        return $name . ' LIKE \'%"' . $value . '"%\'';
    }

    // --------------------------------------------------------------------
    // 聚合 SELECT（供 Model::select_agg / select_count / select_sum 中转）
    // --------------------------------------------------------------------

    /**
     * 混合 SELECT：'form_id, COUNT(*) AS cnt, SUM(price) AS total'
     * SQLite 用双引号标识符（escapeChar="）
     */
    public function buildSelectAgg($select)
    {
        $parts = $this->_xrSplitSelectParts((string) $select);
        $out   = [];
        foreach ($parts as $part) {
            $part = trim($part);
            if ($part === '') {
                continue;
            }
            if (preg_match('/^(COUNT|SUM|AVG|MAX|MIN)\s*\(\s*(.+?)\s*\)\s*(?:AS\s+([`"]?[\w]+[`"]?))?\s*$/i', $part, $m)) {
                $out[] = $this->buildSelectFunc(strtoupper($m[1]), trim($m[2]), isset($m[3]) ? trim($m[3], '`"[]') : '');
                continue;
            }
            if (preg_match('/^(.+?)\s+AS\s+([`"]?[\w]+[`"]?)\s*$/i', $part, $m)) {
                $out[] = $this->_xrQuoteSelectIdent(trim($m[1])) . ' AS ' . $this->escapeIdentifiers(trim($m[2], '`"[]'));
                continue;
            }
            $out[] = $this->_xrQuoteSelectIdent($part);
        }

        return implode(', ', $out);
    }

    /**
     * 单个聚合：COUNT/SUM/AVG/MAX/MIN
     */
    public function buildSelectFunc($fn, $field, $alias = '')
    {
        $fn    = strtoupper(trim((string) $fn));
        $field = trim((string) $field);
        $alias = trim((string) $alias, '`"[]');
        if (! in_array($fn, ['COUNT', 'SUM', 'AVG', 'MAX', 'MIN'], true) || $field === '') {
            return '';
        }
        $inner = ($field === '*') ? '*' : $this->_xrQuoteSelectIdent($field);
        $sql   = $fn . '(' . $inner . ')';
        if ($alias !== '') {
            $sql .= ' AS ' . $this->escapeIdentifiers($alias);
        }

        return $sql;
    }

    /**
     * 列标识加引（table.field / *）
     */
    protected function _xrQuoteSelectIdent($expr)
    {
        $expr = trim((string) $expr);
        if ($expr === '' || $expr === '*') {
            return $expr === '*' ? '*' : '';
        }
        $expr = trim($expr, '`"[]');
        if (strpos($expr, '.') !== false) {
            $bits = explode('.', $expr);
            foreach ($bits as &$b) {
                $b = $this->escapeIdentifiers(trim($b, '`"[]'));
            }

            return implode('.', $bits);
        }

        return $this->escapeIdentifiers($expr);
    }

    /**
     * 按逗号拆 SELECT（忽略括号/引号内逗号）
     */
    protected function _xrSplitSelectParts($select)
    {
        $parts = [];
        $buf   = '';
        $depth = 0;
        $len   = strlen($select);
        $quote = null;
        for ($i = 0; $i < $len; $i++) {
            $ch = $select[$i];
            if ($quote !== null) {
                $buf .= $ch;
                if ($ch === $quote) {
                    if (($ch === "'" || $ch === '"') && $i + 1 < $len && $select[$i + 1] === $ch) {
                        $buf .= $select[++$i];
                        continue;
                    }
                    $quote = null;
                }
                continue;
            }
            if ($ch === "'" || $ch === '"' || $ch === '`') {
                $quote = $ch;
                $buf .= $ch;
                continue;
            }
            if ($ch === '(') {
                $depth++;
                $buf .= $ch;
                continue;
            }
            if ($ch === ')') {
                $depth = max(0, $depth - 1);
                $buf .= $ch;
                continue;
            }
            if ($ch === ',' && $depth === 0) {
                $parts[] = $buf;
                $buf     = '';
                continue;
            }
            $buf .= $ch;
        }
        if (trim($buf) !== '') {
            $parts[] = $buf;
        }

        return $parts;
    }
}