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

use CodeIgniter\Database\BaseConnection;
use CodeIgniter\Database\Exceptions\DatabaseException;
use CodeIgniter\Database\TableName;
use CodeIgniter\Exceptions\LogicException;
use mysqli;
use mysqli_result;
use mysqli_sql_exception;
use stdClass;
use Throwable;

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

    /**
     * DELETE hack flag
     *
     * Whether to use the MySQL "delete hack" which allows the number
     * of affected rows to be shown. Uses a preg_replace when enabled,
     * adding a bit more processing to all queries.
     *
     * @var bool
     */
    public $deleteHack = true;

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

    /**
     * MySQLi object
     *
     * Has to be preserved without being assigned to $connId.
     *
     * @var false|mysqli
     */
    public $mysqli;

    /**
     * MySQLi constant
     *
     * For unbuffered queries use `MYSQLI_USE_RESULT`.
     *
     * Default mode for buffered queries uses `MYSQLI_STORE_RESULT`.
     *
     * @var int
     */
    public $resultMode = MYSQLI_STORE_RESULT;

    /**
     * Use MYSQLI_OPT_INT_AND_FLOAT_NATIVE
     *
     * @var bool
     */
    public $numberNative = false;

    /**
     * Use MYSQLI_CLIENT_FOUND_ROWS
     *
     * Whether affectedRows() should return number of rows found,
     * or number of rows changed, after an UPDATE query.
     *
     * @var bool
     */
    public $foundRows = false;

    /**
     * Connect to the database.
     *
     * @return false|mysqli
     *
     * @throws DatabaseException
     */
    public function connect(bool $persistent = false)
    {
        // Do we have a socket path?
        if ($this->hostname[0] === '/') {
            $hostname = null;
            $port     = null;
            $socket   = $this->hostname;
        } else {
            $hostname = $persistent ? 'p:' . $this->hostname : $this->hostname;
            $port     = empty($this->port) ? null : $this->port;
            $socket   = '';
        }

        $clientFlags  = ($this->compress === true) ? MYSQLI_CLIENT_COMPRESS : 0;
        $this->mysqli = mysqli_init();

        mysqli_report(MYSQLI_REPORT_ALL & ~MYSQLI_REPORT_INDEX);

        $this->mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10);

        if ($this->numberNative === true) {
            $this->mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
        }

        if (isset($this->strictOn)) {
            if ($this->strictOn) {
                $this->mysqli->options(
                    MYSQLI_INIT_COMMAND,
                    "SET SESSION sql_mode = CONCAT(@@sql_mode, ',', 'STRICT_ALL_TABLES')"
                );
            } else {
                $this->mysqli->options(
                    MYSQLI_INIT_COMMAND,
                    "SET SESSION sql_mode = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                        @@sql_mode,
                                        'STRICT_ALL_TABLES,', ''),
                                    ',STRICT_ALL_TABLES', ''),
                                'STRICT_ALL_TABLES', ''),
                            'STRICT_TRANS_TABLES,', ''),
                        ',STRICT_TRANS_TABLES', ''),
                    'STRICT_TRANS_TABLES', '')"
                );
            }
        }

        if (is_array($this->encrypt)) {
            $ssl = [];

            if (! empty($this->encrypt['ssl_key'])) {
                $ssl['key'] = $this->encrypt['ssl_key'];
            }
            if (! empty($this->encrypt['ssl_cert'])) {
                $ssl['cert'] = $this->encrypt['ssl_cert'];
            }
            if (! empty($this->encrypt['ssl_ca'])) {
                $ssl['ca'] = $this->encrypt['ssl_ca'];
            }
            if (! empty($this->encrypt['ssl_capath'])) {
                $ssl['capath'] = $this->encrypt['ssl_capath'];
            }
            if (! empty($this->encrypt['ssl_cipher'])) {
                $ssl['cipher'] = $this->encrypt['ssl_cipher'];
            }

            if ($ssl !== []) {
                if (isset($this->encrypt['ssl_verify'])) {
                    if ($this->encrypt['ssl_verify']) {
                        if (defined('MYSQLI_OPT_SSL_VERIFY_SERVER_CERT')) {
                            $this->mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, 1);
                        }
                    }
                    // Apparently (when it exists), setting MYSQLI_OPT_SSL_VERIFY_SERVER_CERT
                    // to FALSE didn't do anything, so PHP 5.6.16 introduced yet another
                    // constant ...
                    //
                    // https://secure.php.net/ChangeLog-5.php#5.6.16
                    // https://bugs.php.net/bug.php?id=68344
                    elseif (defined('MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT') && version_compare($this->mysqli->client_info, 'mysqlnd 5.6', '>=')) {
                        $clientFlags += MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT;
                    }
                }

                $this->mysqli->ssl_set(
                    $ssl['key'] ?? null,
                    $ssl['cert'] ?? null,
                    $ssl['ca'] ?? null,
                    $ssl['capath'] ?? null,
                    $ssl['cipher'] ?? null
                );
            }

            $clientFlags += MYSQLI_CLIENT_SSL;
        }

        if ($this->foundRows) {
            $clientFlags += MYSQLI_CLIENT_FOUND_ROWS;
        }

        try {
            if ($this->mysqli->real_connect(
                $hostname,
                $this->username,
                $this->password,
                $this->database,
                $port,
                $socket,
                $clientFlags
            )) {
                // Prior to version 5.7.3, MySQL silently downgrades to an unencrypted connection if SSL setup fails
                if (($clientFlags & MYSQLI_CLIENT_SSL) !== 0 && version_compare($this->mysqli->client_info, 'mysqlnd 5.7.3', '<=')
                    && empty($this->mysqli->query("SHOW STATUS LIKE 'ssl_cipher'")->fetch_object()->Value)
                ) {
                    $this->mysqli->close();
                    $message = 'MySQLi was configured for an SSL connection, but got an unencrypted connection instead!';
                    log_message('error', $message);

                    if ($this->DBDebug) {
                        throw new DatabaseException($message);
                    }

                    return false;
                }

                if (! $this->mysqli->set_charset($this->charset)) {
                    log_message('error', "Database: Unable to set the configured connection charset ('{$this->charset}').");

                    $this->mysqli->close();

                    if ($this->DBDebug) {
                        throw new DatabaseException('Unable to set client connection character set: ' . $this->charset);
                    }

                    return false;
                }

                return $this->mysqli;
            }
        } catch (Throwable $e) {
            // Clean sensitive information from errors.
            $msg = $e->getMessage();

            $msg = str_replace($this->username, '****', $msg);
            $msg = str_replace($this->password, '****', $msg);

            throw new DatabaseException($msg, $e->getCode(), $e);
        }

        return false;
    }

    /**
     * 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
    {
        if ($databaseName === '') {
            $databaseName = $this->database;
        }

        if (empty($this->connID)) {
            $this->initialize();
        }

        if ($this->connID->select_db($databaseName)) {
            $this->database = $databaseName;

            return true;
        }

        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'];
        }

        if (empty($this->mysqli)) {
            $this->initialize();
        }

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

    /**
     * Executes the query against the database.
     *
     * @return false|mysqli_result
     */
    protected function execute(string $sql)
    {
        while ($this->connID->more_results()) {
            $this->connID->next_result();
            if ($res = $this->connID->store_result()) {
                $res->free();
            }
        }

        try {
            return $this->connID->query($this->prepQuery($sql), $this->resultMode);
        } catch (mysqli_sql_exception $e) {
            log_message('error', (string) $e);

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

        return false;
    }

    /**
     * Prep the query. If needed, each database adapter can prep the query string
     */
    protected function prepQuery(string $sql): string
    {
        // mysqli_affected_rows() returns 0 for "DELETE FROM TABLE" queries. This hack
        // modifies the query so that it a proper number of affected rows is returned.
        if ($this->deleteHack === true && preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $sql)) {
            return trim($sql) . ' WHERE 1=1';
        }

        return $sql;
    }

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

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

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

    /**
     * Escape Like String Direct
     * There are a few instances where MySQLi queries cannot take the
     * additional "ESCAPE x" parameter for specifying the escape character
     * in "LIKE" strings, and this handles those directly with a backslash.
     *
     * @param list<string>|string $str Input string
     *
     * @return list<string>|string
     */
    public function escapeLikeStringDirect($str)
    {
        if (is_array($str)) {
            foreach ($str as $key => $val) {
                $str[$key] = $this->escapeLikeStringDirect($val);
            }

            return $str;
        }

        $str = $this->_escapeString($str);

        // Escape LIKE condition wildcards
        return str_replace(
            [$this->likeEscapeChar, '%', '_'],
            ['\\' . $this->likeEscapeChar, '\\%', '\\_'],
            $str
        );
    }

    /**
     * Generates the SQL for listing tables in a platform-dependent manner.
     * Uses escapeLikeStringDirect().
     *
     * @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
    {
        $sql = 'SHOW TABLES FROM ' . $this->escapeIdentifier($this->database);

        if ((string) $tableName !== '') {
            return $sql . ' LIKE ' . $this->escape($tableName);
        }

        if ($prefixLimit && $this->DBPrefix !== '') {
            return $sql . " LIKE '" . $this->escapeLikeStringDirect($this->DBPrefix) . "%'";
        }

        return $sql;
    }

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

        return 'SHOW COLUMNS FROM ' . $tableName;
    }

    /**
     * Returns an array of objects with field data
     *
     * @return list<stdClass>
     *
     * @throws DatabaseException
     */
    protected function _fieldData(string $table): array
    {
        $table = $this->protectIdentifiers($table, true, null, false);

        if (($query = $this->query('SHOW COLUMNS FROM ' . $table)) === false) {
            throw new DatabaseException(lang('Database.failGetFieldData'));
        }
        $query = $query->getResultObject();

        $retVal = [];

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

            sscanf($query[$i]->Type, '%[a-z](%d)', $retVal[$i]->type, $retVal[$i]->max_length);

            $retVal[$i]->nullable    = $query[$i]->Null === 'YES';
            $retVal[$i]->default     = $query[$i]->Default;
            $retVal[$i]->primary_key = (int) ($query[$i]->Key === 'PRI');
        }

        return $retVal;
    }

    /**
     * Returns an array of objects with index data
     *
     * @return array<string, stdClass>
     *
     * @throws DatabaseException
     * @throws LogicException
     */
    protected function _indexData(string $table): array
    {
        $table = $this->protectIdentifiers($table, true, null, false);

        if (($query = $this->query('SHOW INDEX FROM ' . $table)) === false) {
            throw new DatabaseException(lang('Database.failGetIndexData'));
        }

        $indexes = $query->getResultArray();

        if ($indexes === []) {
            return [];
        }

        $keys = [];

        foreach ($indexes as $index) {
            if (empty($keys[$index['Key_name']])) {
                $keys[$index['Key_name']]       = new stdClass();
                $keys[$index['Key_name']]->name = $index['Key_name'];

                if ($index['Key_name'] === 'PRIMARY') {
                    $type = 'PRIMARY';
                } elseif ($index['Index_type'] === 'FULLTEXT') {
                    $type = 'FULLTEXT';
                } elseif ($index['Non_unique']) {
                    $type = $index['Index_type'] === 'SPATIAL' ? 'SPATIAL' : 'INDEX';
                } else {
                    $type = 'UNIQUE';
                }

                $keys[$index['Key_name']]->type = $type;
            }

            $keys[$index['Key_name']]->fields[] = $index['Column_name'];
        }

        return $keys;
    }

    /**
     * Returns an array of objects with Foreign key data
     *
     * @return array<string, stdClass>
     *
     * @throws DatabaseException
     */
    protected function _foreignKeyData(string $table): array
    {
        $sql = '
                SELECT
                    tc.CONSTRAINT_NAME,
                    tc.TABLE_NAME,
                    kcu.COLUMN_NAME,
                    rc.REFERENCED_TABLE_NAME,
                    kcu.REFERENCED_COLUMN_NAME,
                    rc.DELETE_RULE,
                    rc.UPDATE_RULE,
                    rc.MATCH_OPTION
                FROM information_schema.table_constraints AS tc
                INNER JOIN information_schema.referential_constraints AS rc
                    ON tc.constraint_name = rc.constraint_name
                    AND tc.constraint_schema = rc.constraint_schema
                INNER JOIN information_schema.key_column_usage AS kcu
                    ON tc.constraint_name = kcu.constraint_name
                    AND tc.constraint_schema = kcu.constraint_schema
                WHERE
                    tc.constraint_type = ' . $this->escape('FOREIGN KEY') . ' AND
                    tc.table_schema = ' . $this->escape($this->database) . ' AND
                    tc.table_name = ' . $this->escape($table);

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

        $query   = $query->getResultObject();
        $indexes = [];

        foreach ($query as $row) {
            $indexes[$row->CONSTRAINT_NAME]['constraint_name']       = $row->CONSTRAINT_NAME;
            $indexes[$row->CONSTRAINT_NAME]['table_name']            = $row->TABLE_NAME;
            $indexes[$row->CONSTRAINT_NAME]['column_name'][]         = $row->COLUMN_NAME;
            $indexes[$row->CONSTRAINT_NAME]['foreign_table_name']    = $row->REFERENCED_TABLE_NAME;
            $indexes[$row->CONSTRAINT_NAME]['foreign_column_name'][] = $row->REFERENCED_COLUMN_NAME;
            $indexes[$row->CONSTRAINT_NAME]['on_delete']             = $row->DELETE_RULE;
            $indexes[$row->CONSTRAINT_NAME]['on_update']             = $row->UPDATE_RULE;
            $indexes[$row->CONSTRAINT_NAME]['match']                 = $row->MATCH_OPTION;
        }

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

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

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

    /**
     * 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
    {
        if (! empty($this->mysqli->connect_errno)) {
            return [
                'code'    => $this->mysqli->connect_errno,
                'message' => $this->mysqli->connect_error,
            ];
        }

        return [
            'code'    => $this->connID->errno,
            'message' => $this->connID->error,
        ];
    }

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

    /**
     * Begin Transaction
     */
    protected function _transBegin(): bool
    {
        $this->connID->autocommit(false);

        return $this->connID->begin_transaction();
    }

    /**
     * Commit Transaction
     */
    protected function _transCommit(): bool
    {
        if ($this->connID->commit()) {
            $this->connID->autocommit(true);

            return true;
        }

        return false;
    }

    /**
     * Rollback Transaction
     */
    protected function _transRollback(): bool
    {
        if ($this->connID->rollback()) {
            $this->connID->autocommit(true);

            return true;
        }

        return false;
    }

    // --------------------------------------------------------------------
    // 迅睿 DDL API（供 Fcms/Model/Table 等调用，便于多库驱动替换）
    // --------------------------------------------------------------------

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

        // Install.sql 等以 MySQL 为源：兼容旧 CHARSET=utf8，并规整 ENGINE 多余空格
        $sql = str_replace('ENGINE=InnoDB  ', 'ENGINE=InnoDB ', $sql);
        $sql = str_replace(
            'CHARSET=utf8 ',
            'CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ',
            $sql
        );

        return trim($sql);
    }

    /**
     * 生成「添加字段」SQL（表名占位符 {tablename}，供字段系统批量替换）
     *
     * @param string $name 字段名
     * @param string $type 字段类型，如 VARCHAR(255)
     * @param string $info 附加属性，如 NULL DEFAULT NULL
     * @param string $note 字段注释（空则不输出 COMMENT）
     *
     * @return string
     */
    public function sqlAddField($name, $type, $info, $note = '')
    {
        $sql = 'ALTER TABLE `{tablename}` ADD ' . $this->escapeIdentifiers($name)
            . ' ' . $type . ' ' . $info;
        $note = (string) $note;
        if ($note !== '') {
            $sql .= " COMMENT '" . str_replace("'", "''", $note) . "'";
        }

        return $sql;
    }

    /**
     * 生成「修改字段」SQL（表名占位符 {tablename}）
     *
     * @param string $name
     * @param string $type
     * @param string $info
     * @param string $note
     *
     * @return string
     */
    public function sqlEditField($name, $type, $info, $note = '')
    {
        $id = $this->escapeIdentifiers($name);
        $sql = 'ALTER TABLE `{tablename}` CHANGE ' . $id . ' ' . $id
            . ' ' . $type . ' ' . $info;
        $note = (string) $note;
        if ($note !== '') {
            $sql .= " COMMENT '" . str_replace("'", "''", $note) . "'";
        }

        return $sql;
    }

    /**
     * 生成「删除字段」SQL（表名占位符 {tablename}）
     *
     * @param string $name
     *
     * @return string
     */
    public function sqlDropField($name)
    {
        return 'ALTER TABLE `{tablename}` DROP ' . $this->escapeIdentifiers($name);
    }

    /**
     * 生成「批量添加字段」SQL（一条 ALTER 多列 ADD）
     *
     * @param array $columns [['name'=>'', 'type'=>'', 'info'=>'', 'note'=>''], ...]
     *
     * @return string
     */
    public function sqlAddFields(array $columns)
    {
        $parts = [];
        foreach ($columns as $col) {
            if (empty($col['name'])) {
                continue;
            }
            $part = 'ADD ' . $this->escapeIdentifiers($col['name'])
                . ' ' . ($col['type'] ?? '')
                . ' ' . ($col['info'] ?? '');
            $note = isset($col['note']) ? (string) $col['note'] : '';
            if ($note !== '') {
                $part .= " COMMENT '" . str_replace("'", "''", $note) . "'";
            }
            $parts[] = trim($part);
        }

        if ($parts === []) {
            return '';
        }

        return 'ALTER TABLE `{tablename}` ' . implode(', ', $parts);
    }

    /**
     * 生成「批量删除字段」SQL（一条 ALTER 多列 DROP）
     *
     * @param array $names 字段名列表
     *
     * @return string
     */
    public function sqlDropFields(array $names)
    {
        $parts = [];
        foreach ($names as $name) {
            if ($name === '' || $name === null) {
                continue;
            }
            $parts[] = 'DROP ' . $this->escapeIdentifiers($name);
        }

        if ($parts === []) {
            return '';
        }

        return 'ALTER TABLE `{tablename}` ' . implode(', ', $parts);
    }

    /**
     * 删除表
     *
     * @param string $table    表名（可含前缀）
     * @param bool   $ifExists 是否使用 IF EXISTS
     *
     * @return false|ResultInterface|bool
     */
    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);
    }

    /**
     * 修改字段
     *
     * @param string $table 表名（可含前缀）
     * @param string $name  字段名
     * @param string $type  字段类型，如 VARCHAR(50)
     * @param string $info  附加属性，如 NOT NULL / DEFAULT NULL
     * @param string $note  字段注释
     *
     * @return false|ResultInterface|bool
     */
    public function editField($table, $name, $type, $info, $note)
    {
        $table = trim((string) $table, '`');

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

    /**
     * 添加字段
     *
     * @param string $table 表名（可含前缀）
     * @param string $name  字段名
     * @param string $type  字段类型
     * @param string $info  附加属性
     * @param string $note  字段注释
     *
     * @return false|ResultInterface|bool
     */
    public function addField($table, $name, $type, $info, $note)
    {
        $table = trim((string) $table, '`');

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

    /**
     * 删除字段
     *
     * @param string $table 表名（可含前缀）
     * @param string $name  字段名
     *
     * @return false|ResultInterface|bool
     */
    public function dropField($table, $name)
    {
        $table = trim((string) $table, '`');

        return $this->query(str_replace(
            '{tablename}',
            $table,
            $this->sqlDropField($name)
        ));
    }

    /**
     * 创建表
     *
     * @param string $table  表名（可含前缀）
     * @param array  $fields 字段定义：['字段名' => '类型及属性SQL'] 或 [0 => '完整列定义SQL']
     * @param array  $indexs 索引定义：['PRIMARY KEY (`id`)', 'KEY `name` (`name`)', ...]
     * @param string $note   表注释
     *
     * @return false|ResultInterface|bool
     */
    public function createTable($table, $fields, $indexs, $note)
    {
        $lines = [];

        if (is_array($fields)) {
            foreach ($fields as $name => $def) {
                if (is_int($name)) {
                    $lines[] = $def;
                } else {
                    $lines[] = $this->escapeIdentifiers($name) . ' ' . $def;
                }
            }
        }

        if (is_array($indexs)) {
            foreach ($indexs as $idx) {
                if ($idx) {
                    $lines[] = $idx;
                }
            }
        }

        $sql = 'CREATE TABLE IF NOT EXISTS ' . $this->escapeIdentifiers($table) . " (
" . implode(",\n", $lines) . "
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='"
            . str_replace("'", "''", (string) $note) . "'";

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

    /**
     * 获取建表 SQL（CREATE TABLE IF NOT EXISTS ...）
     *
     * @param string $table 表名（可含前缀）
     *
     * @return string
     */
    public function createTableSql($table)
    {
        $row = $this->query('SHOW CREATE TABLE ' . $this->escapeIdentifiers($table))->getRowArray();
        if (! $row || empty($row['Create Table'])) {
            return '';
        }

        return str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row['Create Table']);
    }

    /**
     * 获取表完整字段信息（兼容 SHOW FULL COLUMNS 结果结构）
     *
     * @param string $table 表名（可含前缀）
     *
     * @return array
     */
    public function showFullColunms($table)
    {
        return $this->query('SHOW FULL COLUMNS FROM ' . $this->escapeIdentifiers($table))->getResultArray();
    }

    /**
     * 获取全部表状态（兼容 SHOW TABLE STATUS 结果结构：Name/Engine/Rows/...）
     *
     * @return array
     */
    public function showTableStatus()
    {
        return $this->query('SHOW TABLE STATUS')->getResultArray();
    }

    /**
     * 修复表
     */
    public function repairTable($table)
    {
        return $this->query('REPAIR TABLE ' . $this->escapeIdentifiers($table));
    }

    /**
     * 优化表
     */
    public function optimizeTable($table)
    {
        return $this->query('OPTIMIZE TABLE ' . $this->escapeIdentifiers($table));
    }

    /**
     * 刷新表
     */
    public function flushTable($table)
    {
        return $this->query('FLUSH TABLE ' . $this->escapeIdentifiers($table));
    }

    /**
     * 检查表，返回一行状态（如 Msg_text）
     *
     * @return array|null
     */
    public function checkTable($table)
    {
        return $this->query('CHECK TABLE ' . $this->escapeIdentifiers($table))->getRowArray();
    }

    /**
     * 修改表默认字符集/排序规则
     *
     * @param string $table
     * @param string $charset  默认 utf8mb4
     * @param string $collate  默认 utf8mb4_unicode_ci
     */
    public function setTableCharset($table, $charset = 'utf8mb4', $collate = 'utf8mb4_unicode_ci')
    {
        $table = $this->escapeIdentifiers(trim((string) $table, '`'));
        $charset = preg_replace('/[^a-z0-9_]/i', '', (string) $charset);
        $collate = preg_replace('/[^a-z0-9_]/i', '', (string) $collate);

        return $this->query(
            'ALTER TABLE ' . $table . ' DEFAULT CHARSET=' . $charset . ' COLLATE ' . $collate
        );
    }

    // --------------------------------------------------------------------
    // 字段搜索 WHERE 表达式（供 Model::where_* 中转；未实现则走 Model 默认 MySQL 写法）
    // --------------------------------------------------------------------

    /**
     * FIND_IN_SET 语义
     */
    public function whereFindInSet($column, $value)
    {
        $column = (string) $column;
        if (dr_is_numeric($value)) {
            return 'FIND_IN_SET(' . intval($value) . ',' . $column . ')';
        }

        return 'FIND_IN_SET("' . dr_safe_replace($value) . '",' . $column . ')';
    }

    /**
     * JSON 包含（多选/关联等）
     */
    public function whereJson($table, $name, $value)
    {
        if (strpos($name, '`') === false) {
            $name = $table ? '`' . $table . '`.`' . $name . '`' : '`' . $name . '`';
        }

        if (version_compare($this->getVersion(), '5.7.0') < 0) {
            return $name . ' LIKE \'%"' . $value . '"%\'';
        }

        return "(CASE WHEN JSON_VALID({$name}) THEN JSON_CONTAINS ({$name}->'$[*]', '\"" . $value . "\"', '$') ELSE null END)";
    }

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

    /**
     * 混合 SELECT：'form_id, COUNT(*) AS cnt, SUM(price) AS total'
     */
    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;
    }
}