<?php

namespace App\Service;

use App\BaseApp\BaseService;
use App\Service\common\UtilsService;
use Exception;
use Illuminate\Support\Facades\DB;

class DatabaseService extends BaseService
{
    public function __construct()
    {
        parent::__construct();
        // 检查权限：只有role_id=1才能操作
        if ($this->roleId !== 1) {
            $this->utils->errorThrow('权限不足，只有超级管理员可以操作数据库管理功能');
        }
    }

    /**
     * 获取数据库表列表
     * @return array
     */
    public function listTables(): array
    {
        $database = config('database.connections.mysql.database');

        $tables = DB::select("
            SELECT
                TABLE_NAME as `name`,
                TABLE_COMMENT as `comment`,
                TABLE_ROWS as `rows`,
                DATA_LENGTH as data_length,
                INDEX_LENGTH as index_length,
                CREATE_TIME as create_time,
                UPDATE_TIME as update_time,
                TABLE_COLLATION as collation,
                ENGINE as engine
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = ?
            ORDER BY TABLE_NAME
        ", [$database]);

        $result = [];
        foreach ($tables as $table) {
            $result[] = [
                'name' => $table->name,
                'comment' => $table->comment ?: '',
                'rows' => (int)$table->rows,
                'data_length' => (int)$table->data_length,
                'index_length' => (int)$table->index_length,
                'create_time' => $table->create_time,
                'update_time' => $table->update_time,
                'collation' => $table->collation,
                'engine' => $table->engine,
            ];
        }

        return $result;
    }

    /**
     * 获取表详细信息
     * @param string $tableName
     * @return array
     * @throws Exception
     */
    public function getTableInfo(string $tableName): array
    {
        $this->validateTableName($tableName);
        $database = config('database.connections.mysql.database');

        // 获取表基本信息
        $tableInfo = DB::selectOne("
            SELECT
                TABLE_NAME as `name`,
                TABLE_COMMENT as `comment`,
                TABLE_ROWS as `rows`,
                DATA_LENGTH as data_length,
                INDEX_LENGTH as index_length,
                CREATE_TIME as create_time,
                UPDATE_TIME as update_time,
                TABLE_COLLATION as collation,
                ENGINE as engine
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ", [$database, $tableName]);

        if (!$tableInfo) {
            $this->utils->errorThrow('表不存在');
        }

        // 获取字段信息
        $columns = $this->getTableColumns($tableName);

        // 获取索引信息
        $indexes = $this->getTableIndexes($tableName);

        return [
            'name' => $tableInfo->name,
            'comment' => $tableInfo->comment ?: '',
            'rows' => (int)$tableInfo->rows,
            'data_length' => (int)$tableInfo->data_length,
            'index_length' => (int)$tableInfo->index_length,
            'create_time' => $tableInfo->create_time,
            'update_time' => $tableInfo->update_time,
            'collation' => $tableInfo->collation,
            'engine' => $tableInfo->engine,
            'columns' => $columns,
            'indexes' => $indexes,
        ];
    }

    /**
     * 获取表字段信息
     * @param string $tableName
     * @return array
     */
    public function getTableColumns(string $tableName): array
    {
        $this->validateTableName($tableName);
        $database = config('database.connections.mysql.database');

        $columns = DB::select("
            SELECT
                COLUMN_NAME as `name`,
                DATA_TYPE as `type`,
                CHARACTER_MAXIMUM_LENGTH as `length`,
                NUMERIC_PRECISION as `precision`,
                NUMERIC_SCALE as `scale`,
                COLUMN_DEFAULT as default_value,
                COLUMN_COMMENT as `comment`,
                IS_NULLABLE as nullable,
                COLUMN_KEY as key_type,
                EXTRA as extra,
                COLUMN_TYPE as column_type,
                ORDINAL_POSITION as `position`
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
            ORDER BY ORDINAL_POSITION
        ", [$database, $tableName]);

        $result = [];
        foreach ($columns as $column) {
            $result[] = [
                'name' => $column->name,
                'type' => $column->type,
                'length' => $column->length,
                'precision' => $column->precision,
                'scale' => $column->scale,
                'default_value' => $column->default_value,
                'comment' => $column->comment ?: '',
                'nullable' => $column->nullable === 'YES',
                'key_type' => $column->key_type,
                'extra' => $column->extra,
                'column_type' => $column->column_type,
                'position' => (int)$column->position,
            ];
        }

        return $result;
    }

    /**
     * 获取表索引信息
     * @param string $tableName
     * @return array
     */
    public function getTableIndexes(string $tableName): array
    {
        $this->validateTableName($tableName);
        $database = config('database.connections.mysql.database');

        $indexes = DB::select("
            SELECT
                INDEX_NAME as `name`,
                COLUMN_NAME as column_name,
                NON_UNIQUE as non_unique,
                SEQ_IN_INDEX as seq_in_index,
                INDEX_TYPE as `type`
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
            ORDER BY INDEX_NAME, SEQ_IN_INDEX
        ", [$database, $tableName]);

        $result = [];
        $indexMap = [];

        foreach ($indexes as $index) {
            $indexName = $index->name;
            if (!isset($indexMap[$indexName])) {
                $indexMap[$indexName] = [
                    'name' => $indexName,
                    'columns' => [],
                    'unique' => $index->non_unique == 0,
                    'type' => $index->type,
                ];
            }
            $indexMap[$indexName]['columns'][] = $index->column_name;
        }

        return array_values($indexMap);
    }

    /**
     * 获取表数据（分页）
     * @param string $tableName
     * @param int $page
     * @param int $pageSize
     * @param array $where
     * @return array
     * @throws Exception
     */
    public function getTableData(string $tableName, int $page = 1, int $pageSize = 20, array $where = []): array
    {
        $this->validateTableName($tableName);

        // 移除表名前缀，因为 DB::table() 会自动添加
        $tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
        $query = DB::table($tableNameWithoutPrefix);

        // 应用查询条件
        $this->applyWhereConditions($query, $where);

        $total = $query->count();
        $data = $query->skip(($page - 1) * $pageSize)->take($pageSize)->get()->toArray();

        return [
            'page' => $page,
            'size' => $pageSize,
            'total' => $total,
            'page_count' => ceil($total / $pageSize),
            'items' => $data,
        ];
    }

    /**
     * 应用WHERE条件到查询构建器
     * @param \Illuminate\Database\Query\Builder $query
     * @param array $where
     * @return void
     */
    private function applyWhereConditions($query, array $where): void
    {
        $logic = 'AND'; // 默认逻辑连接符

        foreach ($where as $index => $condition) {
            // 处理逻辑连接符（AND/OR）
            if (isset($condition['logic'])) {
                $logic = strtoupper($condition['logic']);
            } elseif ($index > 0) {
                // 如果没有指定，默认使用AND
                $logic = 'AND';
            }

            if (!isset($condition['field']) || !isset($condition['operator'])) {
                continue;
            }

            $field = $condition['field'];
            $operator = strtolower($condition['operator']);
            $value = $condition['value'] ?? null;

            // 根据逻辑连接符选择方法
            $whereMethod = $logic === 'OR' ? 'orWhere' : 'where';

            switch ($operator) {
                case '=':
                case '!=':
                case '>':
                case '>=':
                case '<':
                case '<=':
                    if ($value !== null) {
                        $query->{$whereMethod}($field, $operator, $value);
                    }
                    break;

                case 'like':
                    if ($value !== null) {
                        $query->{$whereMethod}($field, 'like', "%{$value}%");
                    }
                    break;

                case 'not like':
                    if ($value !== null) {
                        $query->{$whereMethod}($field, 'not like', "%{$value}%");
                    }
                    break;

                case 'in':
                    if (is_array($value) && !empty($value)) {
                        $query->{$whereMethod . 'In'}($field, $value);
                    }
                    break;

                case 'not in':
                    if (is_array($value) && !empty($value)) {
                        $query->{$whereMethod . 'NotIn'}($field, $value);
                    }
                    break;

                case 'between':
                    if (is_array($value) && count($value) === 2) {
                        $query->{$whereMethod . 'Between'}($field, $value);
                    }
                    break;

                case 'is null':
                    $query->{$whereMethod . 'Null'}($field);
                    break;

                case 'is not null':
                    $query->{$whereMethod . 'NotNull'}($field);
                    break;
            }
        }
    }

    /**
     * 更新表数据
     * @param string $tableName
     * @param array $data
     * @param array $where
     * @return bool
     * @throws Exception
     */
    public function updateTableData(string $tableName, array $data, array $where): bool
    {
        $this->validateTableName($tableName);

        // 移除表名前缀，因为 DB::table() 会自动添加
        $tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
        $query = DB::table($tableNameWithoutPrefix);

        // 获取变更前的数据
        $beforeQuery = clone $query;
        $this->applyWhereConditions($beforeQuery, $where);
        $beforeData = $beforeQuery->get()->toArray();

        // 应用WHERE条件
        $this->applyWhereConditions($query, $where);

        $affected = $query->update($data);

        if ($affected > 0) {
            // 获取变更后的数据
            $afterQuery = DB::table($tableNameWithoutPrefix);
            $this->applyWhereConditions($afterQuery, $where);
            $afterData = $afterQuery->get()->toArray();

            // 记录变更日志
            $this->logChange('data_change', $tableName, [
                'action' => 'update',
                'affected_rows' => $affected,
                'where' => $where,
            ], null, ['data' => $beforeData], ['data' => $afterData]);
        }

        return $affected > 0;
    }

    /**
     * 删除表数据
     * @param string $tableName
     * @param array $where
     * @return bool
     * @throws Exception
     */
    public function deleteTableData(string $tableName, array $where): bool
    {
        $this->validateTableName($tableName);

        // 移除表名前缀，因为 DB::table() 会自动添加
        $tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
        $query = DB::table($tableNameWithoutPrefix);

        // 获取变更前的数据
        $beforeQuery = clone $query;
        $this->applyWhereConditions($beforeQuery, $where);
        $beforeData = $beforeQuery->get()->toArray();

        // 应用WHERE条件
        $this->applyWhereConditions($query, $where);

        $affected = $query->delete();

        if ($affected > 0) {
            // 记录变更日志
            $this->logChange('data_change', $tableName, [
                'action' => 'delete',
                'affected_rows' => $affected,
                'where' => $where,
            ], null, ['data' => $beforeData], null);
        }

        return $affected > 0;
    }

    /**
     * 批量删除表数据
     * @param string $tableName
     * @param array $where 多个WHERE条件，使用OR连接
     * @return int 删除的记录数
     * @throws Exception
     */
    public function batchDeleteTableData(string $tableName, array $where): int
    {
        $this->validateTableName($tableName);

        if (empty($where)) {
            $this->utils->errorThrow('删除条件不能为空');
        }

        // 移除表名前缀，因为 DB::table() 会自动添加
        $tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
        $query = DB::table($tableNameWithoutPrefix);

        // 获取变更前的数据
        $beforeQuery = clone $query;
        $beforeQuery->where(function ($q) use ($where) {
            foreach ($where as $index => $condition) {
                if (isset($condition['field']) && isset($condition['operator']) && isset($condition['value'])) {
                    if ($index === 0) {
                        $q->where($condition['field'], $condition['operator'], $condition['value']);
                    } else {
                        $q->orWhere($condition['field'], $condition['operator'], $condition['value']);
                    }
                }
            }
        });
        $beforeData = $beforeQuery->get()->toArray();

        // 批量删除：使用OR连接多个条件
        $query->where(function ($q) use ($where) {
            foreach ($where as $index => $condition) {
                if (isset($condition['field']) && isset($condition['operator']) && isset($condition['value'])) {
                    if ($index === 0) {
                        $q->where($condition['field'], $condition['operator'], $condition['value']);
                    } else {
                        $q->orWhere($condition['field'], $condition['operator'], $condition['value']);
                    }
                }
            }
        });

        $affected = $query->delete();

        if ($affected > 0) {
            // 记录变更日志
            $this->logChange('data_change', $tableName, [
                'action' => 'batch_delete',
                'affected_rows' => $affected,
                'where' => $where,
            ], null, ['data' => $beforeData], null);
        }

        return $affected;
    }

    /**
     * 插入表数据
     * @param string $tableName
     * @param array $data
     * @return int
     * @throws Exception
     */
    public function insertTableData(string $tableName, array $data): int
    {
        $this->validateTableName($tableName);
        // 移除表名前缀，因为 DB::table() 会自动添加
        $tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
        $id = DB::table($tableNameWithoutPrefix)->insertGetId($data);

        // 记录变更日志
        $this->logChange('data_change', $tableName, [
            'action' => 'insert',
            'inserted_id' => $id,
        ], null, null, ['data' => $data]);

        return $id;
    }

    /**
     * 更新表注释
     * @param string $tableName
     * @param string $comment
     * @return bool
     * @throws Exception
     */
    public function updateTableComment(string $tableName, string $comment): bool
    {
        $this->validateTableName($tableName);

        // 获取变更前的注释
        $beforeInfo = DB::selectOne("
            SELECT TABLE_COMMENT as comment
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ", [config('database.connections.mysql.database'), $tableName]);

        $database = config('database.connections.mysql.database');
        $sql = "ALTER TABLE `{$tableName}` COMMENT = " . DB::getPdo()->quote($comment);

        DB::statement($sql);

        // 记录变更日志
        $this->logChange('structure_change', $tableName, [
            'action' => 'update_table_comment',
            'before_comment' => $beforeInfo->comment ?? '',
            'after_comment' => $comment,
        ], $sql);

        return true;
    }

    /**
     * 更新字段注释
     * @param string $tableName
     * @param string $columnName
     * @param string $comment
     * @return bool
     * @throws Exception
     */
    public function updateColumnComment(string $tableName, string $columnName, string $comment): bool
    {
        $this->validateTableName($tableName);

        // 获取字段的完整定义
        $column = DB::selectOne("
            SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
        ", [config('database.connections.mysql.database'), $tableName, $columnName]);

        if (!$column) {
            $this->utils->errorThrow('字段不存在');
        }

        $nullable = $column->IS_NULLABLE === 'YES' ? 'NULL' : 'NOT NULL';
        $default = $column->COLUMN_DEFAULT !== null ? "DEFAULT " . DB::getPdo()->quote($column->COLUMN_DEFAULT) : '';
        $extra = $column->EXTRA ?: '';

        // 获取变更前的注释
        $beforeColumn = DB::selectOne("
            SELECT COLUMN_COMMENT
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
        ", [config('database.connections.mysql.database'), $tableName, $columnName]);
        $beforeComment = $beforeColumn->COLUMN_COMMENT ?? '';

        $sql = "ALTER TABLE `{$tableName}` MODIFY COLUMN `{$columnName}` {$column->COLUMN_TYPE} {$nullable} {$default} {$extra} COMMENT " . DB::getPdo()->quote($comment);

        DB::statement($sql);

        // 记录变更日志
        $this->logChange('structure_change', $tableName, [
            'action' => 'update_column_comment',
            'column_name' => $columnName,
            'before_comment' => $beforeComment,
            'after_comment' => $comment,
        ], $sql);

        return true;
    }

    /**
     * 添加索引
     * @param string $tableName
     * @param string $indexName
     * @param array $columns
     * @param bool $unique
     * @param string $type 索引类型：BTREE、HASH、FULLTEXT、SPATIAL
     * @return bool
     * @throws Exception
     */
    public function addIndex(string $tableName, string $indexName, array $columns, bool $unique = false, string $type = 'BTREE'): bool
    {
        $this->validateTableName($tableName);

        if (empty($columns)) {
            $this->utils->errorThrow('索引列不能为空');
        }

        // 验证索引类型
        $allowedTypes = ['BTREE', 'HASH', 'FULLTEXT', 'SPATIAL'];
        $type = strtoupper($type);
        if (!in_array($type, $allowedTypes)) {
            $this->utils->errorThrow('不支持的索引类型');
        }

        $uniqueStr = $unique ? 'UNIQUE' : '';
        $columnsStr = '`' . implode('`, `', $columns) . '`';
        $usingType = "USING {$type}";

        $sql = "ALTER TABLE `{$tableName}` ADD {$uniqueStr} INDEX `{$indexName}` ({$columnsStr}) {$usingType}";

        DB::statement($sql);

        // 记录变更日志
        $this->logChange('structure_change', $tableName, [
            'action' => 'add_index',
            'index_name' => $indexName,
            'columns' => $columns,
            'unique' => $unique,
            'type' => $type,
        ], $sql);

        return true;
    }

    /**
     * 删除索引
     * @param string $tableName
     * @param string $indexName
     * @return bool
     * @throws Exception
     */
    public function dropIndex(string $tableName, string $indexName): bool
    {
        $this->validateTableName($tableName);

        $sql = "ALTER TABLE `{$tableName}` DROP INDEX `{$indexName}`";

        DB::statement($sql);

        // 记录变更日志
        $this->logChange('structure_change', $tableName, [
            'action' => 'drop_index',
            'index_name' => $indexName,
        ], $sql);

        return true;
    }

    /**
     * 修改表结构（添加/修改/删除字段）
     * @param string $tableName
     * @param string $action add|modify|drop
     * @param array $columnInfo
     * @return bool
     * @throws Exception
     */
    public function updateTableStructure(string $tableName, string $action, array $columnInfo): bool
    {
        $this->validateTableName($tableName);

        switch ($action) {
            case 'add':
                return $this->addColumn($tableName, $columnInfo);
            case 'modify':
                return $this->modifyColumn($tableName, $columnInfo);
            case 'drop':
                if (!isset($columnInfo['name'])) {
                    $this->utils->errorThrow('字段名不能为空');
                }

                // 获取变更前的字段信息
                $beforeColumn = $this->getTableColumns($tableName);
                $droppedColumn = null;
                foreach ($beforeColumn as $col) {
                    if ($col['name'] === $columnInfo['name']) {
                        $droppedColumn = $col;
                        break;
                    }
                }

                $sql = "ALTER TABLE `{$tableName}` DROP COLUMN `{$columnInfo['name']}`";
                DB::statement($sql);

                // 记录变更日志
                $this->logChange('structure_change', $tableName, [
                    'action' => 'drop_column',
                    'column_name' => $columnInfo['name'],
                    'column_info' => $droppedColumn,
                ], $sql, $droppedColumn ? ['column' => $droppedColumn] : null);

                return true;
            default:
                $this->utils->errorThrow('不支持的操作类型');
        }
    }

    /**
     * 添加字段
     * @param string $tableName
     * @param array $columnInfo
     * @return bool
     * @throws Exception
     */
    private function addColumn(string $tableName, array $columnInfo): bool
    {
        if (!isset($columnInfo['name']) || !isset($columnInfo['type'])) {
            $this->utils->errorThrow('字段名和类型不能为空');
        }

        $name = $columnInfo['name'];
        $type = $columnInfo['type'];
        $length = isset($columnInfo['length']) ? "({$columnInfo['length']})" : '';
        $nullable = isset($columnInfo['nullable']) && $columnInfo['nullable'] ? 'NULL' : 'NOT NULL';
        $default = isset($columnInfo['default_value']) && $columnInfo['default_value'] !== null
            ? "DEFAULT " . DB::getPdo()->quote($columnInfo['default_value'])
            : '';
        $comment = isset($columnInfo['comment']) ? "COMMENT " . DB::getPdo()->quote($columnInfo['comment']) : '';
        $after = isset($columnInfo['after']) ? "AFTER `{$columnInfo['after']}`" : '';

        $sql = "ALTER TABLE `{$tableName}` ADD COLUMN `{$name}` {$type}{$length} {$nullable} {$default} {$comment} {$after}";

        DB::statement($sql);

        // 记录变更日志
        $this->logChange('structure_change', $tableName, [
            'action' => 'add_column',
            'column_info' => $columnInfo,
        ], $sql, null, ['column' => $columnInfo]);

        return true;
    }

    /**
     * 解析字段类型（从 column_type 如 varchar(255) 中提取类型和长度）
     * @param string $columnType
     * @return array ['type' => string, 'length' => string]
     */
    private function parseColumnType(string $columnType): array
    {
        // 匹配类型和长度，如 varchar(255) 或 decimal(10,2)
        if (preg_match('/^(\w+)(?:\(([^)]+)\))?/i', $columnType, $matches)) {
            return [
                'type' => strtoupper($matches[1]),
                'length' => isset($matches[2]) ? $matches[2] : '',
            ];
        }
        return ['type' => strtoupper($columnType), 'length' => ''];
    }

    /**
     * 构建字段类型字符串
     * @param string $type
     * @param string $length
     * @return string
     */
    private function buildColumnType(string $type, string $length = ''): string
    {
        $type = strtoupper($type);
        // 需要长度的类型
        $typesNeedLength = ['VARCHAR', 'CHAR', 'INT', 'BIGINT', 'TINYINT', 'SMALLINT', 'DECIMAL', 'FLOAT', 'DOUBLE'];

        if (in_array($type, $typesNeedLength) && !empty($length)) {
            return "{$type}({$length})";
        }
        return $type;
    }

    /**
     * 修改字段
     * @param string $tableName
     * @param array $columnInfo
     * @return bool
     * @throws Exception
     */
    private function modifyColumn(string $tableName, array $columnInfo): bool
    {
        if (!isset($columnInfo['name']) || !isset($columnInfo['type'])) {
            $this->utils->errorThrow('字段名和类型不能为空');
        }

        $name = $columnInfo['name'];
        $type = $columnInfo['type'];

        // 构建类型和长度
        $length = '';
        if (isset($columnInfo['length']) && !empty($columnInfo['length'])) {
            $length = "({$columnInfo['length']})";
        } else {
            // 如果没有提供长度，尝试从现有字段获取
            $existingColumn = DB::selectOne("
                SELECT COLUMN_TYPE
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
            ", [config('database.connections.mysql.database'), $tableName, $name]);

            if ($existingColumn) {
                $parsed = $this->parseColumnType($existingColumn->COLUMN_TYPE);
                // 如果类型需要长度但新类型也需要，尝试保留长度
                $typesNeedLength = ['VARCHAR', 'CHAR', 'INT', 'BIGINT', 'TINYINT', 'SMALLINT', 'DECIMAL', 'FLOAT', 'DOUBLE'];
                if (in_array(strtoupper($type), $typesNeedLength) && !empty($parsed['length'])) {
                    $length = "({$parsed['length']})";
                }
            }
        }

        $nullable = isset($columnInfo['nullable']) && $columnInfo['nullable'] ? 'NULL' : 'NOT NULL';
        $default = '';
        if (isset($columnInfo['default_value']) && $columnInfo['default_value'] !== null && $columnInfo['default_value'] !== '') {
            $default = "DEFAULT " . DB::getPdo()->quote($columnInfo['default_value']);
        }
        $comment = isset($columnInfo['comment']) && !empty($columnInfo['comment'])
            ? "COMMENT " . DB::getPdo()->quote($columnInfo['comment'])
            : '';

        // 位置调整
        $after = '';
        if (isset($columnInfo['after']) && !empty($columnInfo['after'])) {
            $after = "AFTER `{$columnInfo['after']}`";
        }

        // 获取变更前的字段信息
        $beforeColumn = $this->getTableColumns($tableName);
        $beforeColumnInfo = null;
        foreach ($beforeColumn as $col) {
            if ($col['name'] === $name) {
                $beforeColumnInfo = $col;
                break;
            }
        }

        $sql = "ALTER TABLE `{$tableName}` MODIFY COLUMN `{$name}` {$type}{$length} {$nullable} {$default} {$comment} {$after}";

        DB::statement($sql);

        // 记录变更日志
        $this->logChange('structure_change', $tableName, [
            'action' => 'modify_column',
            'column_name' => $name,
            'column_info' => $columnInfo,
        ], $sql, $beforeColumnInfo ? ['column' => $beforeColumnInfo] : null, ['column' => $columnInfo]);

        return true;
    }

    /**
     * 执行SELECT查询（仅支持SELECT语句）
     * @param string $sql
     * @return array
     * @throws Exception
     */
    public function executeSelectQuery(string $sql): array
    {
        // 验证SQL安全性
        $trimmedSql = trim($sql);
        $upperSql = strtoupper($trimmedSql);

        // 必须以SELECT开头
        if (!preg_match('/^\s*SELECT/i', $trimmedSql)) {
            $this->utils->errorThrow('仅支持SELECT查询语句');
        }

        // 禁止的危险关键字
        $dangerousKeywords = [
            'DROP', 'DELETE', 'UPDATE', 'INSERT', 'ALTER', 'CREATE', 'TRUNCATE',
            'REPLACE', 'GRANT', 'REVOKE', 'EXEC', 'EXECUTE', 'CALL', 'PROCEDURE',
            'INTO OUTFILE', 'INTO DUMPFILE', 'LOAD_FILE', 'LOAD DATA',
        ];

        foreach ($dangerousKeywords as $keyword) {
            if (strpos($upperSql, $keyword) !== false) {
                $this->utils->errorThrow("SQL语句包含危险关键字: {$keyword}");
            }
        }

        try {
            // 执行查询并限制结果数量
            $results = DB::select($trimmedSql);

            // 限制返回结果数量（最多1000条）
            $maxResults = 1000;
            if (count($results) > $maxResults) {
                $results = array_slice($results, 0, $maxResults);
            }

            // 转换为数组格式
            $data = array_map(function ($row) {
                return (array) $row;
            }, $results);

            // 记录查询日志
            $this->logChange('sql_query', '', [
                'action' => 'select_query',
                'result_count' => count($data),
            ], $trimmedSql);

            return $data;
        } catch (Exception $e) {
            $this->utils->errorThrow('SQL执行失败: ' . $e->getMessage());
        }
    }

    /**
     * 移除表名前缀（如果存在）
     * @param string $tableName
     * @return string
     */
    private function removeTablePrefix(string $tableName): string
    {
        $prefix = config('database.connections.mysql.prefix', '');
        if (!empty($prefix) && strpos($tableName, $prefix) === 0) {
            return substr($tableName, strlen($prefix));
        }
        return $tableName;
    }

    /**
     * 记录数据库变更日志
     * @param string $operationType 操作类型：structure_change, data_change, sql_query
     * @param string $tableName 表名
     * @param array $detail 操作详情
     * @param string|null $sql SQL语句
     * @param array|null $beforeData 变更前数据
     * @param array|null $afterData 变更后数据
     * @return void
     */
    private function logChange(string $operationType, string $tableName, array $detail, ?string $sql = null, ?array $beforeData = null, ?array $afterData = null): void
    {
        try {
            DB::table('nl_database_change_log')->insert([
                'user_id' => $this->userId ?? 0,
                'table_name' => $tableName,
                'operation_type' => $operationType,
                'operation_detail' => json_encode($detail, JSON_UNESCAPED_UNICODE),
                'sql_statement' => $sql,
                'before_data' => $beforeData ? json_encode($beforeData, JSON_UNESCAPED_UNICODE) : null,
                'after_data' => $afterData ? json_encode($afterData, JSON_UNESCAPED_UNICODE) : null,
                'created_at' => time(),
            ]);
        } catch (Exception $e) {
            // 日志记录失败不影响主操作，只记录错误
            \Log::error('数据库变更日志记录失败: ' . $e->getMessage());
        }
    }

    /**
     * 获取变更记录列表
     * @param int $page
     * @param int $pageSize
     * @param array $filters 筛选条件：table_name, operation_type, start_time, end_time
     * @return array
     */
    public function getChangeLog(int $page = 1, int $pageSize = 20, array $filters = []): array
    {
        $query = DB::table('database_change_log');

        // 应用筛选条件
        if (!empty($filters['table_name'])) {
            $query->where('table_name', $filters['table_name']);
        }
        if (!empty($filters['operation_type'])) {
            $query->where('operation_type', $filters['operation_type']);
        }
        if (!empty($filters['start_time'])) {
            $query->where('created_at', '>=', $filters['start_time']);
        }
        if (!empty($filters['end_time'])) {
            $query->where('created_at', '<=', $filters['end_time']);
        }

        $total = $query->count();
        $items = $query->orderBy('created_at', 'desc')
            ->skip(($page - 1) * $pageSize)
            ->take($pageSize)
            ->get()
            ->toArray();

        // 解析JSON字段
        foreach ($items as &$item) {
            $item = (array) $item;
            if (!empty($item['operation_detail'])) {
                $item['operation_detail'] = json_decode($item['operation_detail'], true);
            }
            if (!empty($item['before_data'])) {
                $item['before_data'] = json_decode($item['before_data'], true);
            }
            if (!empty($item['after_data'])) {
                $item['after_data'] = json_decode($item['after_data'], true);
            }
        }

        return [
            'page' => $page,
            'size' => $pageSize,
            'total' => $total,
            'page_count' => ceil($total / $pageSize),
            'items' => $items,
        ];
    }

    /**
     * 验证表名（防止SQL注入）
     * @param string $tableName
     * @return void
     * @throws Exception
     */
    private function validateTableName(string $tableName): void
    {
        if (empty($tableName)) {
            $this->utils->errorThrow('表名不能为空');
        }

        // 只允许字母、数字、下划线
        if (!preg_match('/^[a-zA-Z0-9_]+$/', $tableName)) {
            $this->utils->errorThrow('表名格式不正确');
        }

        // 检查表是否存在
        $database = config('database.connections.mysql.database');
        $exists = DB::selectOne("
            SELECT COUNT(*) as count
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ", [$database, $tableName]);

        if ($exists->count == 0) {
            $this->utils->errorThrow('表不存在');
        }
    }
}