<?php

namespace base\common\service\scheme;

use think\facade\Db;
use think\facade\Config;
use app\common\scheme\attribute\Table;
use app\common\scheme\attribute\Field;
use app\common\scheme\attribute\Component;
use app\common\scheme\attribute\Index;
use ReflectionClass;
use ReflectionProperty;

class SchemeToDbService
{
    protected $connection;

    public function __construct()
    {
        $this->connection = Config::get('database.default', 'mysql');
    }

    public function sync(string $className, bool $skipData = false)
    {
        if (!class_exists($className)) {
            throw new \Exception("Class $className not found");
        }

        $ref = new ReflectionClass($className);
        
        // 获取 Table 注解
        $tableAttrs = $ref->getAttributes(Table::class);
        if (empty($tableAttrs)) {
            throw new \Exception("Class $className missing #[Table] attribute");
        }
        /** @var Table $tableAttr */
        $tableAttr = $tableAttrs[0]->newInstance();

        $connection = $this->resolveConnection($tableAttr);
        $tableName = $tableAttr->name;
        $prefix = (string)Config::get('database.connections.' . $connection . '.prefix', '');
        
        // 确保表名带前缀
        $fullTableName = $tableName;
        if ($prefix && !str_starts_with($tableName, $prefix)) {
            $fullTableName = $prefix . $tableName;
        }

        $sql = $this->buildCreateTableSql($fullTableName, $tableAttr, $ref);

        // 检查表是否存在
        $tableExists = $this->checkTableExists($connection, $fullTableName);
        $backupTableName = null;

        // 1. 备份
        if ($tableExists) {
            $backupPrefix = Config::get('scheme.backup_prefix', 'backup');
            $backupTableName = $prefix . $backupPrefix . '_' . date('YmdHis') . '_' . $fullTableName;
            
            try {
                // 确保没有残留的同名备份表（极端情况）
                Db::connect($connection)->execute("DROP TABLE IF EXISTS `$backupTableName`");
                Db::connect($connection)->execute("RENAME TABLE `$fullTableName` TO `$backupTableName`");
            } catch (\Exception $e) {
                // 如果备份失败，可能是权限问题或其他，抛出异常
                throw new \Exception("Backup failed: " . $e->getMessage());
            }
        }

        // 2. 建表
        Db::connect($connection)->execute($sql);

        // 3. 恢复数据
        if ($tableExists && !$skipData && $backupTableName) {
            try {
                $this->restoreData($connection, $fullTableName, $backupTableName, $ref->getProperties());
            } catch (\Exception $e) {
                // 如果数据恢复失败，尝试回滚（这里只是简单的删除新表，重命名回旧表）
                // 实际生产环境可能需要更复杂的恢复机制
                // Db::connect($this->connection)->execute("DROP TABLE IF EXISTS `$fullTableName`");
                // Db::connect($this->connection)->execute("RENAME TABLE `$backupTableName` TO `$fullTableName`");
                throw $e;
            }
        }
        
        return $backupTableName;
    }

    public function diff(string $className): array
    {
        if (!class_exists($className)) {
            throw new \Exception("Class $className not found");
        }

        $ref = new ReflectionClass($className);
        $tableAttr = $this->getTableAttribute($ref, $className);
        $connection = $this->resolveConnection($tableAttr);
        $prefix = (string)Config::get('database.connections.' . $connection . '.prefix', '');

        $fullTableName = $tableAttr->name;
        if ($prefix && !str_starts_with($fullTableName, $prefix)) {
            $fullTableName = $prefix . $fullTableName;
        }

        if (!$this->checkTableExists($connection, $fullTableName)) {
            return ["表不存在：{$fullTableName}"];
        }

        try {
            $dbColumnsRows = Db::connect($connection)->query("SHOW FULL COLUMNS FROM `{$fullTableName}`");
            $dbKeysRows = Db::connect($connection)->query("SHOW KEYS FROM `{$fullTableName}`");
        } catch (\Throwable $e) {
            return ["无法读取数据库表结构：{$fullTableName}，{$e->getMessage()}"];
        }

        $dbColumns = [];
        foreach ($dbColumnsRows as $row) {
            $dbColumns[$row['Field']] = $row;
        }

        $schemeColumns = $this->buildSchemeColumnSignature($ref);
        $schemeIndexes = $this->buildSchemeIndexSignature($ref);
        $dbIndexes = $this->buildDbIndexSignature($dbKeysRows);

        $missingFields = [];
        $extraFields = [];
        $changedFields = [];

        foreach ($schemeColumns as $field => $sig) {
            if (!isset($dbColumns[$field])) {
                $missingFields[] = $field;
                continue;
            }

            $row = $dbColumns[$field];
            $fieldChanges = [];

            $dbType = $this->normalizeDbType((string)$row['Type']);
            $schemeType = $this->normalizeDbType($sig['type']);
            if ($dbType !== $schemeType) {
                $fieldChanges['type'] = ['db' => $dbType, 'scheme' => $schemeType];
            }

            $dbNull = (string)$row['Null'];
            $schemeNull = $sig['null'];
            if ($dbNull !== $schemeNull) {
                $fieldChanges['null'] = ['db' => $dbNull, 'scheme' => $schemeNull];
            }

            $dbDefault = $row['Default'];
            $schemeDefault = $sig['default'];
            if (!$this->defaultEquals($dbDefault, $schemeDefault)) {
                $fieldChanges['default'] = [
                    'db' => $this->stringifyDefault($dbDefault),
                    'scheme' => $this->stringifyDefault($schemeDefault),
                ];
            }

            $dbExtra = (string)$row['Extra'];
            $schemeExtra = $sig['extra'];
            if ($dbExtra !== $schemeExtra) {
                $fieldChanges['extra'] = ['db' => $dbExtra, 'scheme' => $schemeExtra];
            }

            $dbPrimary = (string)$row['Key'] === 'PRI';
            if ($dbPrimary !== $sig['primary']) {
                $fieldChanges['primary'] = [
                    'db' => $dbPrimary ? 'PRI' : '',
                    'scheme' => $sig['primary'] ? 'PRI' : '',
                ];
            }

            $dbComment = (string)$row['Comment'];
            $schemeComment = $sig['comment'];
            if ($this->parseComment($dbComment) !== $this->parseComment($schemeComment)) {
                $fieldChanges['comment'] = ['db' => $dbComment, 'scheme' => $schemeComment];
            }

            if (!empty($fieldChanges)) {
                $changedFields[$field] = $fieldChanges;
            }
        }

        foreach ($dbColumns as $field => $row) {
            if (!isset($schemeColumns[$field])) {
                $extraFields[] = $field;
            }
        }

        $missingIndexes = [];
        $extraIndexes = [];
        $changedIndexes = [];

        foreach ($schemeIndexes as $name => $idx) {
            if (!isset($dbIndexes[$name])) {
                $missingIndexes[] = $name;
                continue;
            }

            $dbIdx = $dbIndexes[$name];
            $idxChanges = [];

            if ($dbIdx['type'] !== $idx['type']) {
                $idxChanges['type'] = ['db' => $dbIdx['type'], 'scheme' => $idx['type']];
            }

            if ($dbIdx['columns'] !== $idx['columns']) {
                $idxChanges['columns'] = [
                    'db' => implode(',', $dbIdx['columns']),
                    'scheme' => implode(',', $idx['columns']),
                ];
            }

            if (!empty($idxChanges)) {
                $changedIndexes[$name] = $idxChanges;
            }
        }

        foreach ($dbIndexes as $name => $idx) {
            if (!isset($schemeIndexes[$name])) {
                $extraIndexes[] = $name;
            }
        }

        $tableCommentDiff = $this->diffTableComment($connection, $fullTableName, $tableAttr->comment);
        $hasDiff = !empty($missingFields) || !empty($extraFields) || !empty($changedFields) || !empty($missingIndexes) || !empty($extraIndexes) || !empty($changedIndexes) || $tableCommentDiff !== null;
        if (!$hasDiff) {
            return [];
        }

        $lines = [];
        $lines[] = '差异汇总：'
            . '字段缺失=' . count($missingFields)
            . '，字段多余=' . count($extraFields)
            . '，字段修改=' . count($changedFields)
            . '；索引缺失=' . count($missingIndexes)
            . '，索引多余=' . count($extraIndexes)
            . '，索引修改=' . count($changedIndexes)
            . '；表注释=' . ($tableCommentDiff !== null ? '不一致' : '一致');

        if (!empty($missingFields)) {
            $lines[] = '字段缺失（' . count($missingFields) . '）：' . implode(',', $missingFields);
        }
        if (!empty($extraFields)) {
            $lines[] = '字段多余（' . count($extraFields) . '）：' . implode(',', $extraFields);
        }
        if (!empty($changedFields)) {
            $lines[] = '字段修改（' . count($changedFields) . '）：' . implode(',', array_keys($changedFields));
            foreach ($changedFields as $field => $changes) {
                $lines[] = '字段 ' . $field . '：';
                foreach ($changes as $k => $v) {
                    $lines[] = '  - ' . $k . '：DB=' . $v['db'] . ' Scheme=' . $v['scheme'];
                }
            }
        }

        if (!empty($missingIndexes)) {
            $lines[] = '索引缺失（' . count($missingIndexes) . '）：' . implode(',', $missingIndexes);
        }
        if (!empty($extraIndexes)) {
            $lines[] = '索引多余（' . count($extraIndexes) . '）：' . implode(',', $extraIndexes);
        }
        if (!empty($changedIndexes)) {
            $lines[] = '索引修改（' . count($changedIndexes) . '）：' . implode(',', array_keys($changedIndexes));
            foreach ($changedIndexes as $name => $changes) {
                $lines[] = '索引 ' . $name . '：';
                foreach ($changes as $k => $v) {
                    $lines[] = '  - ' . $k . '：DB=' . $v['db'] . ' Scheme=' . $v['scheme'];
                }
            }
        }

        if ($tableCommentDiff !== null) {
            $lines[] = $tableCommentDiff;
        }

        return $lines;
    }

    public function getColumnsForCurd(string $className, array $onlyFields = []): array
    {
        if (!class_exists($className)) {
            throw new \Exception("Class $className not found");
        }

        $ref = new ReflectionClass($className);
        $columns = [];

        foreach ($ref->getProperties() as $prop) {
            $fieldAttrs = $prop->getAttributes(Field::class);
            if (empty($fieldAttrs)) {
                continue;
            }

            $fieldName = $prop->getName();
            if (!empty($onlyFields) && !in_array($fieldName, $onlyFields, true)) {
                continue;
            }

            /** @var Field $field */
            $field = $fieldAttrs[0]->newInstance();
            $this->validateSchemeField($field, $ref->getName(), $fieldName);

            $columns[$fieldName] = [
                'type' => $this->buildMysqlTypeFromSchemeField($field),
                'comment' => $this->buildColumnComment($field, $prop),
                'required' => !$field->nullable,
                'default' => $field->default,
                'field' => $fieldName,
            ];
        }

        return $columns;
    }

    public function getPrimaryKey(string $className): ?string
    {
        if (!class_exists($className)) {
            throw new \Exception("Class $className not found");
        }

        $ref = new ReflectionClass($className);
        foreach ($ref->getProperties() as $prop) {
            $fieldAttrs = $prop->getAttributes(Field::class);
            if (empty($fieldAttrs)) {
                continue;
            }

            /** @var Field $field */
            $field = $fieldAttrs[0]->newInstance();
            if ($field->primary) {
                return $prop->getName();
            }
        }

        return null;
    }

    protected function checkTableExists(string $connection, string $tableName): bool
    {
        $tables = Db::connect($connection)->query("SHOW TABLES LIKE '$tableName'");
        return !empty($tables);
    }

    protected function buildCreateTableSql($tableName, Table $tableAttr, ReflectionClass $ref)
    {
        $properties = $ref->getProperties();
        $lines = [];
        $primaryKeys = [];
        
        foreach ($properties as $prop) {
            $fieldAttrs = $prop->getAttributes(Field::class);
            if (empty($fieldAttrs)) {
                continue;
            }
            /** @var Field $field */
            $field = $fieldAttrs[0]->newInstance();
            $fieldName = $prop->getName();
            $this->validateSchemeField($field, $ref->getName(), $fieldName);
            
            $line = "`$fieldName` {$field->type}";
            
            // Length/Precision
            if ($field->length !== null) {
                $line .= "({$field->length})";
            } elseif ($field->precision > 0) {
                $line .= "({$field->precision},{$field->scale})";
            }
            
            // Unsigned
            if ($field->unsigned) {
                $line .= " UNSIGNED";
            }
            
            // Nullable
            if (!$field->nullable) {
                $line .= " NOT NULL";
            } else {
                $line .= " DEFAULT NULL";
            }
            
            // AutoIncrement
            if ($field->autoIncrement) {
                $line .= " AUTO_INCREMENT";
            }
            
            // Default
            if (!is_null($field->default)) {
                $def = $field->default;
                if (is_string($def)) {
                    $line .= " DEFAULT '$def'";
                } elseif (is_bool($def)) {
                    $line .= " DEFAULT " . ($def ? 1 : 0);
                } else {
                    $line .= " DEFAULT $def";
                }
            }

            // Comment + Component Restore
            $comment = $this->buildColumnComment($field, $prop);
            
            if ($comment) {
                $line .= " COMMENT '$comment'";
            }
            
            $lines[] = $line;
            
            if ($field->primary) {
                $primaryKeys[] = "`$fieldName`";
            }
        }
        
        if (!empty($primaryKeys)) {
            $lines[] = "PRIMARY KEY (" . implode(',', $primaryKeys) . ")";
        }

        // 处理 Index 注解
        $indexAttrs = $ref->getAttributes(Index::class);
        foreach ($indexAttrs as $attr) {
            /** @var Index $idx */
            $idx = $attr->newInstance();
            $cols = is_array($idx->columns) ? $idx->columns : [$idx->columns];
            $colStr = "`" . implode("`,`", $cols) . "`";
            $keyName = $idx->name ?: $cols[0]; // 默认使用第一列名
            
            if ($idx->type === 'UNIQUE') {
                $lines[] = "UNIQUE KEY `$keyName` ($colStr)";
            } elseif ($idx->type === 'FULLTEXT') {
                $lines[] = "FULLTEXT KEY `$keyName` ($colStr)";
            } else {
                $lines[] = "KEY `$keyName` ($colStr)";
            }
        }
        
        $body = implode(",\n  ", $lines);
        $comment = $tableAttr->comment ? " COMMENT='{$tableAttr->comment}'" : "";
        
        return "CREATE TABLE `$tableName` (\n  $body\n) ENGINE={$tableAttr->engine} DEFAULT CHARSET={$tableAttr->charset}$comment";
    }

    protected function validateSchemeField(Field $field, string $className, string $fieldName): void
    {
        $type = strtolower(trim($field->type));
        $length = $field->length;

        if ($length !== null && $length < 1) {
            throw new \InvalidArgumentException("Scheme 字段定义非法：{$className}::\${$fieldName} length 必须 >= 1，当前={$length}");
        }

        if ($type === 'char' && $length !== null && $length > 255) {
            throw new \InvalidArgumentException("Scheme 字段定义非法：{$className}::\${$fieldName} type=char 的 length 超出范围，允许 1-255，当前={$length}");
        }
    }

    protected function restoreData(string $connection, $newTable, $oldTable, array $properties)
    {
        $fields = [];
        foreach ($properties as $prop) {
            $fieldAttrs = $prop->getAttributes(Field::class);
            if (!empty($fieldAttrs)) {
                $fields[] = "`" . $prop->getName() . "`";
            }
        }
        
        if (empty($fields)) return;
        
        try {
            $oldFieldsRaw = Db::connect($connection)->getFields($oldTable);
            $oldFields = array_keys($oldFieldsRaw);
            
            $commonFields = [];
            foreach ($properties as $prop) {
                $name = $prop->getName();
                if (in_array($name, $oldFields)) {
                    $commonFields[] = "`$name`";
                }
            }
            
            if (empty($commonFields)) return;
            
            $commonStr = implode(',', $commonFields);
            
            $sql = "INSERT INTO `$newTable` ($commonStr) SELECT $commonStr FROM `$oldTable`";
            Db::connect($connection)->execute($sql);
            
        } catch (\Exception $e) {
            throw new \Exception("Data migration failed: " . $e->getMessage());
        }
    }

    protected function resolveConnection(Table $tableAttr): string
    {
        return !empty($tableAttr->connection) ? (string)$tableAttr->connection : (string)$this->connection;
    }

    protected function getTableAttribute(ReflectionClass $ref, string $className): Table
    {
        $tableAttrs = $ref->getAttributes(Table::class);
        if (empty($tableAttrs)) {
            throw new \Exception("Class $className missing #[Table] attribute");
        }

        /** @var Table $tableAttr */
        $tableAttr = $tableAttrs[0]->newInstance();

        return $tableAttr;
    }

    protected function buildSchemeColumnSignature(ReflectionClass $ref): array
    {
        $sig = [];

        foreach ($ref->getProperties() as $prop) {
            $fieldAttrs = $prop->getAttributes(Field::class);
            if (empty($fieldAttrs)) {
                continue;
            }

            $fieldName = $prop->getName();
            /** @var Field $field */
            $field = $fieldAttrs[0]->newInstance();
            $this->validateSchemeField($field, $ref->getName(), $fieldName);

            $sig[$fieldName] = [
                'type' => $this->buildMysqlTypeFromSchemeField($field),
                'null' => $field->nullable ? 'YES' : 'NO',
                'default' => $field->default,
                'extra' => $field->autoIncrement ? 'auto_increment' : '',
                'primary' => (bool)$field->primary,
                'comment' => $this->buildColumnComment($field, $prop),
            ];
        }

        return $sig;
    }

    protected function buildSchemeIndexSignature(ReflectionClass $ref): array
    {
        $sig = [];

        $indexAttrs = $ref->getAttributes(Index::class);
        foreach ($indexAttrs as $attr) {
            /** @var Index $idx */
            $idx = $attr->newInstance();
            $cols = is_array($idx->columns) ? $idx->columns : [$idx->columns];
            $keyName = $idx->name ?: $cols[0];

            $sig[$keyName] = [
                'type' => $idx->type,
                'columns' => array_values($cols),
            ];
        }

        return $sig;
    }

    protected function buildDbIndexSignature(array $dbKeysRows): array
    {
        $idx = [];

        foreach ($dbKeysRows as $row) {
            $name = $row['Key_name'];
            if ($name === 'PRIMARY') {
                continue;
            }

            if (!isset($idx[$name])) {
                $type = 'NORMAL';
                if ((int)$row['Non_unique'] === 0) {
                    $type = 'UNIQUE';
                } elseif (strtoupper((string)$row['Index_type']) === 'FULLTEXT') {
                    $type = 'FULLTEXT';
                }

                $idx[$name] = [
                    'type' => $type,
                    'columns' => [],
                ];
            }

            $seq = (int)$row['Seq_in_index'];
            $idx[$name]['columns'][$seq] = $row['Column_name'];
        }

        foreach ($idx as $name => $val) {
            ksort($idx[$name]['columns']);
            $idx[$name]['columns'] = array_values($idx[$name]['columns']);
        }

        return $idx;
    }

    protected function buildMysqlTypeFromSchemeField(Field $field): string
    {
        $type = strtolower($field->type);

        if ($field->length !== null) {
            $type .= "({$field->length})";
        } elseif ($field->precision > 0) {
            $type .= "({$field->precision},{$field->scale})";
        }

        if ($field->unsigned) {
            $type .= ' unsigned';
        }

        return $type;
    }

    protected function buildColumnComment(Field $field, ReflectionProperty $prop): string
    {
        $comment = (string)$field->comment;
        $compAttrs = $prop->getAttributes(Component::class);
        if (empty($compAttrs)) {
            return trim($comment);
        }

        /** @var Component $comp */
        $comp = $compAttrs[0]->newInstance();
        $typeStr = "{{$comp->type}}";
        $optionsStr = '';

        if (!empty($comp->options)) {
            $parts = [];
            foreach ($comp->options as $k => $v) {
                $parts[] = "$k:$v";
            }
            $optionsStr = ' (' . implode(',', $parts) . ')';
        }

        return trim(trim($comment) . " $typeStr$optionsStr");
    }

    protected function normalizeDbType(string $type): string
    {
        $type = strtolower(trim($type));
        $type = preg_replace('/\\s+/', ' ', $type);
        // 移除整数类型的显示宽度，例如 int(11) -> int, bigint(20) -> bigint
        // 但不移除 decimal(10,2) 或 char(32) 这种有实际意义的长度
        $type = preg_replace('/(tinyint|smallint|mediumint|int|integer|bigint)\s*\(\d+\)/', '$1', $type);
        $type = preg_replace('/^decimal\\((\\d+)\\)$/', 'decimal($1,0)', $type);
        return $type;
    }

    protected function parseComment(string $comment): array
    {
        $comment = trim($comment);
        $result = [
            'content' => '',
            'type' => '',
            'options' => [],
        ];

        // Extract options (...)
        if (preg_match('/^(.*)\s*\((.*)\)$/', $comment, $matches)) {
            $comment = trim($matches[1]);
            $optsStr = $matches[2];
            $pairs = explode(',', $optsStr);
            foreach ($pairs as $p) {
                $kv = explode(':', $p, 2);
                $k = trim($kv[0]);
                $v = isset($kv[1]) ? trim($kv[1]) : '';
                if ($k !== '') {
                    $result['options'][$k] = $v;
                }
            }
        }

        // Extract type {...}
        if (preg_match('/^(.*)\s*\{(\w+)\}$/', $comment, $matches)) {
            $comment = trim($matches[1]);
            $result['type'] = strtolower($matches[2]);
        }

        $result['content'] = $comment;
        
        // Sort options for comparison
        ksort($result['options']);

        return $result;
    }

    protected function defaultEquals($dbDefault, $schemeDefault): bool
    {
        if (is_null($dbDefault) && is_null($schemeDefault)) {
            return true;
        }

        if (is_null($dbDefault) xor is_null($schemeDefault)) {
            return false;
        }

        if (is_bool($schemeDefault)) {
            $schemeDefault = $schemeDefault ? '1' : '0';
        } elseif (is_int($schemeDefault) || is_float($schemeDefault)) {
            $schemeDefault = (string)$schemeDefault;
        } elseif (is_string($schemeDefault)) {
            $schemeDefault = (string)$schemeDefault;
        } else {
            $schemeDefault = (string)$schemeDefault;
        }

        return (string)$dbDefault === (string)$schemeDefault;
    }

    protected function stringifyDefault($value): string
    {
        if (is_null($value)) {
            return 'NULL';
        }

        if (is_bool($value)) {
            return $value ? '1' : '0';
        }

        if (is_int($value) || is_float($value)) {
            return (string)$value;
        }

        return (string)$value;
    }

    protected function diffTableComment(string $connection, string $tableName, string $schemeComment): ?string
    {
        $type = strtolower((string)Config::get('database.connections.' . $connection . '.type', ''));
        if ($type !== 'mysql') {
            return null;
        }

        $dbComment = '';
        try {
            $escaped = addcslashes($tableName, "\\_%");
            $rows = Db::connect($connection)->query("SHOW TABLE STATUS LIKE '$escaped'");
            if (!empty($rows)) {
                $dbComment = isset($rows[0]['Comment']) ? (string)$rows[0]['Comment'] : '';
            }
        } catch (\Throwable $e) {
        }

        if ($dbComment === '') {
            try {
                $rows = Db::connect($connection)->query("SHOW CREATE TABLE `$tableName`");
                if (!empty($rows)) {
                    $create = $rows[0]['Create Table'] ?? '';
                    if ($create !== '' && preg_match("/COMMENT='(.*?)'/s", $create, $matches)) {
                        $dbComment = stripcslashes($matches[1]);
                    }
                }
            } catch (\Throwable $e) {
            }
        }

        if ($dbComment !== (string)$schemeComment) {
            return "表注释不一致：DB={$dbComment} Scheme={$schemeComment}";
        }

        return null;
    }
}