<?php

namespace xmvc\core\cmd;

use xmvc\DB;
use xmvc\MVC;
use xmvc\Sqler;
use PhpMyAdmin\SqlParser\Parser;
use PhpMyAdmin\SqlParser\Exceptions\ParserException;
use stdClass;

/**
 * composer require phpmyadmin/sql-parser
 */
class MetaDataClass
{
    public DB $db;
    public function __construct()
    {
        if (!isset(MVC::$cfg['database'])) {
            MVC::$cfg['database'] = loadConfig('database');
        }
        if (!isset(MVC::$cfg['database']['xmeta'])) {
            MVC::warningExit('没有配置 xmeta 数据库');
        }
        $this->db = new DB('xmeta');
    }

    /**
     * 备份字段信息
     */
    public function backupColumn(DB $db, string $dbname, string $tableName, string $columnName)
    {
        $backupList = [
            [
                'table' => 'xmeta_column', // 字段定义
                'fields' => [
                    'dbname' => 'xmeta_column_db_name',
                    'tableName' => 'xmeta_column_table_name',
                    'columnName' => 'xmeta_column_name',
                ]
            ],
            [
                'table' => 'xmeta_index', //索引字段映射
                'fields' => [
                    'dbname' => 'xmeta_index_db_name',
                    'tableName' => 'xmeta_index_table_name',
                    'columnName' => 'xmeta_index_column_name',
                ]
            ],
            [
                'table' => 'xmeta_foreign', // 外键关系表 映射
                'fields' => [
                    'dbname' => 'xmeta_foreign_db_name',
                    'tableName' => 'xmeta_foreign_table_name',
                    'columnName' => 'xmeta_foreign_column_name',
                ]
            ],
            [
                'table' => 'xmeta_foreign', // 外键关系表 被 映射
                'fields' => [
                    'dbname' => 'xmeta_foreign_db_name2',
                    'tableName' => 'xmeta_foreign_table_name2',
                    'columnName' => 'xmeta_foreign_column_name2',
                ]
            ],
            [
                'table' => 'xmeta_power_item', // 权限配置子项
                'fields' => [
                    'dbname' => 'xmeta_power_item_db_name',
                    'tableName' => 'xmeta_power_item_table_name',
                    'columnName' => 'xmeta_power_item_column_name',
                ]
            ],
            [
                'table' => 'xmeta_form_field', // 表单字段配置
                'fields' => [
                    'dbname' => 'xmeta_form_field_db_name',
                    'tableName' => 'xmeta_form_field_table_name',
                    'columnName' => 'xmeta_form_field_column_name',
                ]
            ],
            [
                'table' => 'xmeta_lang_map', // 语言翻译映射
                'fields' => [
                    'dbname' => 'xmeta_lang_map_db_name',
                    'tableName' => 'xmeta_lang_map_table_name',
                    'columnName' => 'xmeta_lang_map_column_name',
                ]
            ],
        ];
        $q = $db->newSqler();
        $data = [];
        foreach ($backupList as $item) {
            $rows = $q->table($item['table'])->where([
                $item['fields']['dbname'] => $dbname,
                $item['fields']['tableName'] => $tableName,
                $item['fields']['columnName'] => $columnName,
            ])->all();
            $data[] = [
                'item' => $item,
                'data' => $rows,
            ];
        }
        $dir = DIR_BACKUP . "/meta/" . date('Ym');
        mkdirs($dir);
        $time = date('d.His');
        $backupFile = $dir . "/delete.field.{$dbname}.{$tableName}.{$columnName}.{$time}.php";
        if (!arrayToFile($backupFile, $data)) {
            error("备份字段信息时出错, {$backupFile}");
        }
    }

    public function execute(string $op): string
    {
        switch ($op) {
            case '':
            case 'databases':
                return $this->opDatabase(I('databases', []));
            case 'tables':
                return $this->opTables(I('dbKey'), I('tables', []));
            default:
                error('无效的网址');
        }
    }

    public function opDatabase(array $selectDbKeys = []): string
    {
        if ($selectDbKeys) {
            foreach ($selectDbKeys as $sort => $selectDbKey) {
                $db = new DB($selectDbKey);
                $selectDbname = MVC::$cfg['database'][$selectDbKey]['dbname'];
                $this->createMetadata('database', $db, $sort, $selectDbname, $selectDbKey);
            }
            header('Location: ' . url('SELF'));
            exit();
        }
        $databases = [];
        if ($this->db->hasTable('xmeta_db')) {
            $q = $this->db->newSqler();
            $rows = $q->table('xmeta_db')->map('xmeta_db_name')->all();
            $databases = array_keys($rows);
        }
        $formUrl = url('SELF');
        $csrfToken = getCsrfToken();
        $html = [];
        $html[] = "<h1>Database</h1>";
        $html[] = "<form action='{$formUrl}' method='post'>";
        $html[] = "<input type='hidden' name='csrfToken' value='{$csrfToken}'>";
        $html[] = '<div style="padding-left:1em"><input type="submit" value="生成元数据"></div>';
        $html[] = "<ul class='ul-tables'>";
        $dbKeys = array_keys(MVC::$cfg['database']);
        foreach ($dbKeys as $dbKey) {
            if ($dbKey == 'xmeta' || $dbKey == 'main') {
                continue;
            }
            $url = url('SELF', ['op' => 'tables', 'dbKey' => $dbKey]);
            $dbname = MVC::$cfg['database'][$dbKey]['dbname'];
            if (in_array($dbname, $databases)) {
                $html[] = "<li><input type='checkbox' value='{$dbKey}' name='databases[]' checked='true'><a href='{$url}'>{$dbKey}</a>  <span class='dbname'>( {$dbname} )<span></li>";
            } else {
                $html[] = "<li><input type='checkbox' value='{$dbKey}' name='databases[]'>{$dbKey} <span class='dbname'>( {$dbname} )<span></li>";
            }
        }
        $html[] = "</ul>";
        $html[] = "</form>";
        return implode("\n", $html);
    }

    public function opTables(string $dbKey, array $selectTables = []): string
    {
        $db = new DB($dbKey);
        $dbname = cfg("database>{$dbKey}>dbname");
        if ($selectTables) {
            // 删除旧记录
            //$sql = "DELETE FROM `{$dbname}`.`xmeta_table`";
            //$db->exec($sql);
            // 检查外键, 并按外键约束规则对创建表的顺序重新排列
            $selectTables = $this->sortTablesByForeign($db, $dbname, $selectTables);
            //创建Meta记录
            foreach ($selectTables as $sort => $selectTableName) {
                $this->createMetadata('tableOrView', $db, $sort, $dbname, $selectTableName);
            }
            header('Location: ' . url('SELF'));
            exit();
        }
        $sql = "SHOW TABLES FROM `{$dbname}`";
        $tables = $db->query($sql)->all();
        $url = url('SELF');
        $csrfToken = getCsrfToken();
        $hasTables = [];
        if ($this->db->hasTable('xmeta_table')) {
            $q = $this->db->newSqler();
            $rows = $q->table('xmeta_table')->where('xmeta_table_db_name', $dbname)->map('xmeta_table_name')->all();
            $hasTables = array_keys($rows);
        }
        $html = [];
        $html[] = "<h1>{$dbKey} - {$dbname}</h1>";
        $html[] = "<form action='{$url}' method='post'>";
        $html[] = "<input type='hidden' name='csrfToken' value='{$csrfToken}'>";
        $html[] = '<div style="padding-left:1em"><input type="submit" value="生成元数据"></div>';
        $html[] = '<div style="padding-left:1em"><input type="checkbox" id="checkall"> 全选</div>';
        $html[] = "<ul class='ul-tables'>";
        foreach ($tables as $i => $item) {
            $tableName = array_shift($item);
            $checked = in_array($tableName, $hasTables) ? " checked='true'" : '';
            $html[] = "<li><input type='checkbox' value='{$tableName}' name='tables[]' class='checkbox-table' id='ck{$i}'{$checked}><label for='ck{$i}'>{$tableName}</label></li>";
        }
        $html[] = "</ul>";
        $html[] = "</form>";
        return implode("\n", $html);
    }

    public function createMetadata(string $type, DB $db, int $sort, string $dbname, string $name = '', array $ext = [])
    {
        if ($type == 'database') {
            $ddl = $this->getDatabaseDDL($db, $dbname, $name);
            $ddlArr = [];
            $pattern = '/DEFAULT CHARACTER SET ([a-zA-Z0-9_]+) COLLATE ([a-zA-Z0-9_]+)/i';
            if (preg_match($pattern, $ddl, $m)) {
                $ddlArr = [
                    'default_charset' => $m[1],
                    'collate' => $m[2],
                ];
            }
            $this->createMetadataForDatabase($dbname, $ddlArr, $ddl, $name);
        } elseif ($type == 'tableOrView') {
            list($ddl, $jsonObj) = $this->getTableDDL($db, $dbname, $name);
            $extTableDDL = $this->extTableDDL($ddl, $jsonObj);
            $ddlObj = $this->parseDDL($extTableDDL);
            if ($ddlObj) {
                if ($this->hasOptionValue($ddlObj->options->options, 'TABLE')) {
                    $this->createMetadataForTable($sort, $dbname, $ddlObj, $ddl);
                } elseif ($this->hasOptionValue($ddlObj->options->options, 'VIEW')) {
                    $this->createMetadataForView($sort, $dbname, $ddlObj, $ddl);
                }
            }
        }
    }

    /**
     * 检查外键, 并按外键约束规则对创建表的顺序重新排列
     */
    public function sortTablesByForeign($db, $dbname, array $tables): array
    {
        $newTables = [];
        $tableForeigns = [];
        foreach ($tables as $pos => $tableName) {
            list($ddl, $jsonObj) = $this->getTableDDL($db, $dbname, $tableName);
            $extTableDDL = $this->extTableDDL($ddl, $jsonObj);
            $table = $this->parseDDL($extTableDDL);
            $tableName = $table->name->table;
            // 外键 信息
            $foreigns = [];
            if ($this->hasOptionValue($table->options->options, 'TABLE')) {
                foreach ($table->fields as $field) {
                    $type = $field->type;
                    $isConstraint = $field->isConstraint ?? false;
                    if (is_null($type) && $isConstraint == true) {
                        $foreigns[] = $field;
                    }
                }
            }
            $tableForeigns[$tableName] = $foreigns;
        }
        // 没有外键的表移到最前
        foreach ($tableForeigns as $tableName => $foreigns) {
            if (empty($foreigns)) {
                $newTables[] = $tableName;
            }
        }
        // 有外键的处理, 将 外键表移到自己之前
        $lreferArrnkArr = [];
        foreach ($tableForeigns as $tableName => $foreigns) {
            if (!empty($foreigns)) {
                foreach ($foreigns as $foreign) {
                    $references = $foreign->references;
                    $referDbname = $references->table->database ?: $dbname;
                    $referTableName = $references->table->table ?: $tableName;
                    $tableIdx = array_search($tableName, $tables);
                    $referTableIdx = array_search($referTableName, $tables);
                    if ($tableIdx < $referTableIdx) {
                        // 将 外键表 移到 自己 之前
                        unset($tables[$referTableIdx]);
                        array_splice($tables, $tableIdx, 0, $referTableName);
                        if (isset($referArr[$referTableName])) {
                            // 将 外键表 的 外键表 再移到 外键表 之前
                            foreach ($referArr[$referTableName] as $referArrTableName) {
                                $tableIdx = $tableIdx - 1;
                                unset($tables[array_search($referArrTableName, $tables)]);
                                array_splice($tables, $tableIdx, 0, $referArrTableName);
                            }
                        }
                    }
                    $referArr[$tableName][] = $referTableName;
                }
            }
        }
        foreach ($tables as $tableName) {
            if (!in_array($tableName, $newTables)) {
                $newTables[] = $tableName;
            }
        }
        return $newTables;
    }

    public function createMetadataForDatabase(string $dbname, array $ddlArr, string $ddl, string $dbKey)
    {
        $db = $this->db;
        $db->transaction(function (Sqler $q, DB $db) use ($dbname, $ddlArr, $ddl, $dbKey) {
            //  数据库 信息 插入/更新
            $q->table('xmeta_db');
            $q->set('xmeta_db_name', $dbname);
            $q->set('xmeta_db_key', $dbKey);
            $q->set('xmeta_db_namespace', $dbKey);
            $q->set('xmeta_db_default_charset', $ddlArr['default_charset']);
            $q->set('xmeta_db_collate', $ddlArr['collate']);
            $q->set('xmeta_db_ddl', $ddl);
            $q->where('xmeta_db_name', $dbname);
            $q->onUpdate = function (string $key, mixed $newValue, mixed $oldValue, array $oldRow) {
                if ($key == 'xmeta_db_namespace' && trim($oldValue) != '') {
                    return false; // 更新时, 如果xmeta_db_namespace 有值, 则不更新 xmeta_db_namespace 字段
                }
            };
            $db->replaceCheck();
        });
    }

    public function createMetadataForTable(int $sort, string $dbname, object $table, string $ddl)
    {
        //dd($table);
        $db = $this->db;
        $db->transaction(function (Sqler $q, DB $db) use ($sort, $dbname, $table, $ddl) {
            $tableName = $table->name->table;
            // 删除不存在的字段
            $rows = $q->table('xmeta_column')->where([
                'xmeta_column_db_name' => $dbname,
                'xmeta_column_table_name' => $tableName,
            ])->all();
            $newColumnNameArr = [];
            foreach ($table->fields as $field) {
                $type = $field->type;
                if (!is_null($type)) {
                    $newColumnNameArr[] = $field->name;
                }
            }
            foreach ($rows as $row) {
                $oldColumnName = $row['xmeta_column_name'];
                if (!in_array($oldColumnName, $newColumnNameArr)) {
                    // 备份字段信息
                    $this->backupColumn($db, $dbname, $tableName, $oldColumnName);
                    // 删除字段信息
                    $db->delete($q->table('xmeta_column')->where([
                        'xmeta_column_db_name' => $dbname,
                        'xmeta_column_table_name' => $tableName,
                        'xmeta_column_name' => $oldColumnName,
                    ]));
                }
            }
            // 主键 信息
            $tablePrimarys = [];
            foreach ($table->fields as $field) {
                $type = $field->type;
                $isConstraint = $field->isConstraint ?? false;
                if (is_null($type) && $isConstraint == false) {
                    $item = $field->key;
                    if ($item->type == 'PRIMARY KEY') {
                        foreach ($item->columns as $column) {
                            $tablePrimarys[] = $column['name'];
                        }
                    }
                }
            }
            if (empty($tablePrimarys)) {
                MVC::warningExit($tableName . ' 表缺失主键定义');
            }
            // 索引 信息
            $indexs = [];
            foreach ($table->fields as $field) {
                $type = $field->type;
                $isConstraint = $field->isConstraint ?? false;
                if (is_null($type) && $isConstraint == false) {
                    $item = $field->key;
                    if ($item->type != 'PRIMARY KEY') {
                        $indexs[] = $field;
                    }
                }
            }
            // 外键 信息
            $foreigns = [];
            foreach ($table->fields as $field) {
                $type = $field->type;
                $isConstraint = $field->isConstraint ?? false;
                if (is_null($type) && $isConstraint == true) {
                    $foreigns[] = $field;
                }
            }
            // 表 信息 更新
            $tableEngine = $this->getOptionByName($table->entityOptions->options, 'ENGINE');
            $tableDefaultCharset = $this->getOptionByName($table->entityOptions->options, 'DEFAULT CHARSET');
            $tableCollate = $this->getOptionByName($table->entityOptions->options, 'COLLATE');
            $tableComment = $this->getOptionByName($table->entityOptions->options, 'COMMENT');
            if (trim($tableComment) == '') {
                MVC::warningExit($dbname . '.' . $tableName . ' 缺失表描述元数据信息');
            }
            $tableCommentMeta = $this->parseTableComment($dbname, $tableName, $tableComment);
            $q->table('xmeta_table');
            $q->set('xmeta_table_db_name', $dbname);
            $q->set('xmeta_table_name', $tableName);
            $q->set('xmeta_table_classname', $tableCommentMeta->classname);
            $q->set('xmeta_table_classtpl', $tableCommentMeta->classtpl);
            $q->set('xmeta_table_sort', $sort);
            $q->set('xmeta_table_caption', $tableCommentMeta->caption);
            $q->set('xmeta_table_primary', implode(',', $tablePrimarys));
            $q->set('xmeta_table_field_prefix', $tableCommentMeta->prefix);
            $q->set('xmeta_table_type', $tableCommentMeta->type);
            $q->set('xmeta_table_type_info', $tableCommentMeta->typeInfo ? json_encode($tableCommentMeta->typeInfo, JSON_UNESCAPED_UNICODE) : null);
            $q->set('xmeta_table_parent_name', $tableCommentMeta->parent);
            $q->set('xmeta_table_ddl', $ddl);
            $q->set('xmeta_table_ddl_struct', json_encode($table, JSON_UNESCAPED_UNICODE));
            $q->set('xmeta_table_engine', $tableEngine);
            $q->set('xmeta_table_default_charset', $tableDefaultCharset);
            $q->set('xmeta_table_collate', $tableCollate);
            $q->set('xmeta_table_comment', $tableCommentMeta->comment);
            $q->where([
                'xmeta_table_db_name' => $dbname,
                'xmeta_table_name' => $tableName,
            ]);
            $db->replaceCheck();
            //dd($db->last());
            // 字段 信息 更新
            $regI = 0;
            foreach ($table->fields as $field) {
                $type = $field->type;
                $name = $field->name;
                $typeOptions = $field->type->options->options ?? [];
                $options = $field->options->options ?? [];
                if (!is_null($type)) {
                    $default = $this->getOptionByName($options, 'DEFAULT');
                    $comment = $this->getOptionByName($options, 'COMMENT');
                    $commentMeta = $this->parseColumnComment($dbname, $tableName, $tableCommentMeta->prefix, $name, $comment);
                    if ((!isset($commentMeta->attr) || $commentMeta->attr == '') && $tableCommentMeta->prefix != '' && substr($name, 0, strlen($tableCommentMeta->prefix)) != $tableCommentMeta->prefix) {
                        MVC::warningExit($dbname . '.' . $tableName . ' 表的中字段 ' . $name . ' 的前缀与表定义的前缀 ' . $tableCommentMeta->prefix . ' 不一至');
                    }
                    if ($comment === null || trim($comment) == '') {
                        MVC::warningExit($dbname . '.' . $tableName . '.' . $name . ' 字段缺失描述');
                    }
                    $isAutoIncrement = $this->hasOptionValue($options, 'AUTO_INCREMENT');
                    $isNotNull = $this->hasOptionValue($options, 'NOT NULL');
                    $typeName = $type->name;
                    $typeParam = isset($type->parameters) ? json_encode($field->type->parameters) : '';
                    $onUpdate = false; // 更新时是否更新当前时间
                    $this->getOptionByName($options, 'ON UPDATE');
                    if ($typeName == 'DATETIME' && $default == 'CURRENT_TIMESTAMP' && $this->getOptionByName($options, 'ON UPDATE')) {
                        if ($this->getOptionByName($options, 'ON UPDATE') == 'CURRENT_TIMESTAMP') {
                            $onUpdate = true;
                        }
                    }
                    $defaultCharset = '';
                    $collate = '';
                    if (in_array($typeName, ['CHAR', 'VARCHAR', 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'ENUM', 'SET'])) {
                        $defaultCharset = $this->getOptionByName($typeOptions, 'CHARACTER SET', $tableDefaultCharset);
                        $collate = $this->getOptionByName($typeOptions, 'COLLATE', $tableCollate);
                    }
                    $q->table('xmeta_column');
                    $q->set('xmeta_column_db_name', $dbname);
                    $q->set('xmeta_column_table_name', $tableName);
                    $q->set('xmeta_column_name', $name);
                    $q->set('xmeta_column_attr', $commentMeta->attr);
                    $q->set('xmeta_column_sort', $regI);
                    $q->set('xmeta_column_caption', $commentMeta->caption);
                    $q->set('xmeta_column_is_auto_increment', $isAutoIncrement);
                    $q->set('xmeta_column_is_notnull', $isNotNull);
                    $q->set('xmeta_column_is_primary', in_array($name, $tablePrimarys) ? 1 : 0);
                    $q->set('xmeta_column_type', $typeName);
                    $q->set('xmeta_column_type_param', $typeParam != '[]' ? $typeParam : '');
                    $q->set('xmeta_column_default_value', $default);
                    $q->set('xmeta_column_on_update', $onUpdate);
                    $q->set('xmeta_column_default_charset', $defaultCharset);
                    $q->set('xmeta_column_collate', $collate);
                    $q->set('xmeta_column_comment', $comment);
                    $q->set('xmeta_column_enum_info', $commentMeta->enum ? json_encode($commentMeta->enum, JSON_UNESCAPED_UNICODE) : null);
                    $q->set('xmeta_column_json', $commentMeta ? json_encode($commentMeta, JSON_UNESCAPED_UNICODE) : null);
                    $q->where([
                        'xmeta_column_db_name' => $dbname,
                        'xmeta_column_table_name' => $tableName,
                        'xmeta_column_name' => $name,
                    ]);
                    $db->replaceCheck();
                    $regI++;
                    // dd($db->last());
                }
            }
            // 索引 信息 更新
            $db->delete($q->table('xmeta_index')->where([
                'xmeta_index_db_name' => $dbname,
                'xmeta_index_table_name' => $tableName,
            ]));
            foreach ($indexs as $index) {
                $key = $index->key;
                foreach ($key->columns as $i => $column) {
                    $q->table('xmeta_index');
                    $q->set('xmeta_index_db_name', $dbname);
                    $q->set('xmeta_index_table_name', $tableName);
                    $q->set('xmeta_index_column_name', $column['name']);
                    $q->set('xmeta_index_name', $key->name);
                    $q->set('xmeta_index_type', $key->type);
                    $q->set('xmeta_index_sort', $i);
                    $db->insert();
                    // dd($db->last());
                }
            }
            // 外键 信息 更新
            $db->delete($q->table('xmeta_foreign')->where([
                'xmeta_foreign_db_name' => $dbname,
                'xmeta_foreign_table_name' => $tableName,
            ]));
            foreach ($foreigns as $foreign) {
                $foreignName = $foreign->name;
                $key = $foreign->key;
                $references = $foreign->references;
                $foreignMeta = $this->parseForeignName($dbname, $tableName, $foreignName);
                $db_name2 = $references->table->database ?: $dbname;
                $table_name2 = $references->table->table ?: $tableName;
                $on_delete = $this->getOptionByName($references->options->options, 'ON DELETE', 'RESTRICT');
                $on_update = $this->getOptionByName($references->options->options, 'ON UPDATE', 'RESTRICT');
                foreach ($key->columns as $i => $column) {
                    $referColumnName = $references->columns[$i];
                    // 外键是否已在 meta 记录中
                    $hasReferColumn = $q->table('xmeta_column')->where([
                        'xmeta_column_db_name' => $db_name2,
                        'xmeta_column_table_name' => $table_name2,
                        'xmeta_column_name' => $referColumnName,
                    ])->has();
                    if (!$hasReferColumn) {
                        MVC::warningExit('xmeta_column 表中,  表名: `' . $dbname . '`.`' . $tableName . '` 列名: ' . $column['name'] . ' 的外键引用的 表名: `' . $db_name2 . '`.`' . $table_name2 . '` 列名: ' . $referColumnName . ' 不存在或没有在它之前生成元数据记录');
                    }
                    $noindbTagStr = 'notindb_';
                    $noindb = 0;
                    if (substr($foreignName, 0, strlen($noindbTagStr)) == $noindbTagStr) {
                        $noindb = 1;
                        $foreignName = substr($foreignName, strlen($noindbTagStr));
                    }
                    $q->table('xmeta_foreign');
                    $q->set('xmeta_foreign_name', $foreignName);
                    $q->set('xmeta_foreign_db_name', $dbname);
                    $q->set('xmeta_foreign_table_name', $tableName);
                    $q->set('xmeta_foreign_column_name', $column['name']);
                    $q->set('xmeta_foreign_db_name2', $db_name2);
                    $q->set('xmeta_foreign_table_name2', $table_name2);
                    $q->set('xmeta_foreign_column_name2', $referColumnName);
                    $q->set('xmeta_foreign_type', $foreignMeta->type);
                    $q->set('xmeta_foreign_is_filter', $foreignMeta->filter ?? 0);
                    $q->set('xmeta_foreign_notindb', $noindb);
                    $q->set('xmeta_foreign_count', count($key->columns));
                    $q->set('xmeta_foreign_sort', $i);
                    $q->set('xmeta_foreign_on_delete', $on_delete);
                    $q->set('xmeta_foreign_on_update', $on_update);
                    $db->insert();
                }
            }
        });
    }

    public function createMetadataForView(int $sort, string $dbname, object $view, string $ddl)
    {
        $db = $this->db;
        $db->transaction(
            function (Sqler $q, DB $db) use ($dbname, $view, $ddl) {
                $viewName = $view->name->table;
                // 删除旧记录
                $q->table('xmeta_table');
                $q->where('xmeta_table_db_name', $dbname)->and('xmeta_table_name', $viewName);
                $q->table('xmeta_view');
                $q->set('xmeta_view_db_name', $dbname);
                $q->set('xmeta_view_name', $viewName);
                $q->set('xmeta_view_ddl', $ddl);
                $q->set('xmeta_view_ddl_struct', json_encode($view, JSON_UNESCAPED_UNICODE));
                $q->where(['xmeta_view_db_name' => $dbname, 'xmeta_view_name' => $viewName]);
                $db->replaceCheck();
            }
        );
    }

    public function getOptionByName(array $options, string $name, $default = null)
    {
        $idx = null;
        foreach ($options as $key => $value) {
            if (is_array($value) && $name == $value['name']) {
                $idx = $key;
                break;
            }
        }
        if (!is_null($idx)) {
            return  $options[$idx]['value'];
        }
        return $default;
    }

    public function hasOptionValue(array $options, string $value): bool
    {
        foreach ($options as $key => $v) {
            if ($v == $value) {
                return true;
                break;
            }
        }
        return false;
    }

    public function getTableDDL(DB $db, string $dbname, string $tableName): array
    {
        $sql = "SHOW CREATE TABLE `{$dbname}`.`{$tableName}`";
        $row =  $db->query($sql)->row();
        $ddl = $row['Create Table'] ?? $row['Create View'];
        $jsonObj = null;
        $parttern = '/(\{.*\})/';
        if (preg_match_all($parttern, $ddl, $m)) {
            $jsonObj = $this->parseCommentJson(end($m[1]));
            if ($jsonObj) {
                $jsonObj->foreigns = $jsonObj->foreigns ?? [];
            }
        }
        return [$ddl, $jsonObj];
    }

    public function extTableDDL(string $ddl, mixed $jsonObj): string
    {
        $result = $ddl;
        if ($jsonObj && $jsonObj->foreigns) {
            $parttern = '/CREATE TABLE([^\(]+)\((.*)\)(\s*ENGINE=.*)/is';
            if (preg_match($parttern, $ddl, $m)) {
                $extDDL = rtrim($m[2]);
                foreach ($jsonObj->foreigns as $str) {
                    if (preg_match('/CONSTRAINT\s*(.*)\s+FOREIGN\s+KEY\s*\((.*)/', $str, $vm)) {
                        $foreignName = sprintf('`notindb_%s`', trim($vm[1], '`'));
                        $str = sprintf('CONSTRAINT %s FOREIGN KEY (%s', $foreignName, $vm[2]);
                    } else {
                        MVC::warningExit(' 自定义外键格式有误 ' . $str . "\n<br><br>\n" . $ddl);
                    }
                    $extDDL .= ",\n  " . $str;
                }
                $result = sprintf("CREATE TABLE%s(%s\n)%s", $m[1], $extDDL, $m[3]);
            }
        }
        return $result;
    }

    public function getDatabaseDDL(DB $db, string $dbname)
    {
        $sql = "SHOW CREATE DATABASE `{$dbname}`";
        $row =  $db->query($sql)->row();
        return $row['Create Database'] ?? '';
    }

    public function parseDDL(string $ddl): object
    {
        try {
            $parser = new Parser($ddl);
            $statements = (array)$parser->statements;
            $result = $statements[0];
            return $result ?? null;
        } catch (ParserException $e) {
            error($e->getMessage());
        }
    }

    public function parseCommentJson(string $comment): object|bool|null
    {
        $parttern = '/(.*?)(\{.*\})/s';
        if (preg_match($parttern, $comment, $m)) {
            $jsonStr = $m[2];
            $jsonStr = strtr($jsonStr, ['\\r' => "\r", '\\n' => "\n", '\\t' => "\t", '\r' => "\r", '\n' => "\n", '\t' => "\t",  '\\\\"' => '\"']);
            if (!isJson($jsonStr)) {
                return false;
            } else {
                $object = json_decode($jsonStr);
                $object->comment = $object->comment ?? trim($m[1]); // 描述
                $object->caption = $object->caption ?? $object->comment; // 标题名称
                return $object;
            }
        }
        return null;
    }

    /**
     * 分析表描述中的元数据信息
     *
     * @param string $dbName
     * @param string $tableName
     * @param string $comment
     * @return object
     */
    public function parseTableComment(string $dbName, string $tableName, string $comment): object
    {
        $parttern = '/(.*?)(\{.*\})/s';
        if (!preg_match($parttern, $comment)) {
            // 没有提供 {} 时
            $comment = $comment . '{"caption":"' . addslashes($comment) . '"}';
        }
        $commentMeta = $this->parseCommentJson($comment);
        if ($commentMeta !== null) {
            if ($commentMeta !== false) {
                $commentMeta->prefix = $commentMeta->prefix ?? '';
                // 前缀 ~ 表示当前表名
                if (substr($commentMeta->prefix, 0, 1) == '~') {
                    $commentMeta->prefix = $tableName . substr($commentMeta->prefix, 1);
                }
                $commentMeta->type = $commentMeta->type ?? 'table'; // 表的类型, 例 table, tree, lrtree
                $commentMeta->typeInfo = $commentMeta->typeInfo ?? null; // 是否是左右值树型结构
                $commentMeta->parent = $commentMeta->parent ?? ''; // 父表名
                $commentMeta->classtpl = $commentMeta->classtpl ?? ''; // 表的类模板
                // 表的类名处理
                if (!isset($commentMeta->classname)) {
                    $classname = '';
                    $arr = explode('_', $tableName);
                    if (count($arr) > 1) {
                        foreach ($arr as $i => $v) {
                            $classname .= ucfirst($v);
                        }
                    } else {
                        $classname = ucfirst($tableName);
                    }
                    $commentMeta->classname = $classname;
                }
            } else {
                MVC::warningExit($dbName . '.' . $tableName . ' 表描述元数据部份不是一个有效的 JSON 格式<br>' . $comment);
            }
            return $commentMeta;
        }
    }

    /**
     * 分析字段描述中的元数据信息
     *
     * @param string $dbName
     * @param string $tableName
     * @param string $comment
     * @return object
     */
    public function parseColumnComment(string $dbName, string $tableName, string $prefix, string $columnName, string $comment): object
    {
        if (trim($comment) == '') {
            MVC::warningExit($dbName . '.' . $tableName . '.' . $columnName . ' 缺失字段描述元数据信息');
        }
        $parttern = '/(.*?)(\{.*\})/s';
        if (!preg_match($parttern, $comment)) {
            // 没有提供 {} 时
            $comment = $comment . ' {"caption":"' . addslashes($comment) . '"}';
        }
        $commentMeta = $this->parseCommentJson($comment);
        if ($commentMeta !== null) {
            if ($commentMeta !== false) {
                $commentMeta->enum = $commentMeta->enum ?? ''; // 值的枚举描述
                //字段在类中的属性名称 处理
                if (!isset($commentMeta->attr) || trim($commentMeta->attr) == '') {
                    if (substr($columnName, 0, strlen($prefix)) == $prefix) {
                        $attr = substr($columnName, strlen($prefix));
                    } else {
                        $attr = $columnName;
                    }
                    $arr = explode('_', $attr);
                    $newAttr = '';
                    foreach ($arr as $i => $v) {
                        $newAttr .= $i > 0 ? ucfirst($v) : $v;
                    }
                    $commentMeta->attr = $newAttr;
                }
            } else {
                MVC::warningExit($dbName . '.' . $tableName . ' 表的字段 ' . $columnName . ' 描述元数据部份不是一个有效的 JSON 格式, ' . $comment);
            }
            return $commentMeta;
        }
    }

    /**
     * 分析外键名称中的元数据信息
     *
     * @param string $dbName
     * @param string $tableName
     * @param string $comment
     * @return object
     */
    public function parseForeignName(string $dbName, string $tableName, string $foreignName): object
    {
        $commentMeta = $this->parseCommentJson($foreignName);
        if ($commentMeta != null) {
            if ($commentMeta != false) {
                if (!isset($commentMeta->type)) {
                    MVC::warningExit($dbName . '.' . $tableName  . ' 外键名称[ ' . $foreignName . ' ] 未定义关系类型 type');
                }
                if (!in_array($commentMeta->type, ['1:1', '1:N', 'N:1', 'M:N'])) {
                    MVC::warningExit($dbName . '.' . $tableName  . ' 外键名称 ' . $foreignName . '  的关系类型 type, 必须是 1:1, 1:N, N:1, M:N 之中的一个值, 1对1, 1对多, 多对1, 多对多');
                }
            } else {
                MVC::warningExit($dbName . '.' . $tableName . ' 外键名称元数据部份不是一个有效的 JSON 格式');
            }
            return $commentMeta;
        } else {
            MVC::warningExit($dbName . '.' . $tableName  . ' 外键名称 ' . $foreignName . ' 缺失外键元数据信息, 格式:  `外键名称 {"type":"1-N"}` 
                <br>外键名称: 一般为 表名_ibfk_1 (ibfk 为 index btree foreign key 缩写), 
                <br>关系类型 type: 1:1, 1:N, N:1, M:N (1对1, 1对多, 多对1, 多对多)
                <br>1:N 与 N:1 逻辑相同, 取决于观察的对象不同, 例 在学生表上创建对应班级ID的外键为 1:N 或 N:1 都一样, 在创建元数据类时, 学生类对于班级 为 N:1, 班级类对于学生为 1:N
            ');
        }
    }
}