<?php

namespace App\Services;

use App\Helpers\DatabaseBackupHelper;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;

class DatabaseService
{
    protected array $systemTablePrefixes = ['admin_', 'migrations', 'cache', 'jobs', 'personal_access_tokens', 'password_reset_tokens', 'failed_jobs'];

    protected array $fieldTypes = [
        'int', 'tinyint', 'smallint', 'mediumint', 'bigint',
        'varchar', 'char', 'text', 'mediumtext', 'longtext',
        'date', 'datetime', 'timestamp', 'time', 'year',
        'decimal', 'float', 'double',
        'json', 'enum', 'set', 'blob', 'binary',
    ];

    public function getTables(): array
    {
        $database = config('database.connections.mysql.database');
        $tables = DB::select("SHOW TABLE STATUS FROM `{$database}`");

        $result = [];
        foreach ($tables as $table) {
            $result[] = (array) $table;
        }

        return $result;
    }

    public function getTableInfo(string $table): ?array
    {
        $this->validateTableName($table);
        $database = config('database.connections.mysql.database');
        $tables = DB::select("SHOW TABLE STATUS FROM `{$database}` WHERE Name = ?", [$table]);

        if (empty($tables)) {
            return null;
        }

        return (array) $tables[0];
    }

    public function getTableColumns(string $table): array
    {
        $this->validateTableName($table);
        $columns = DB::select("SHOW FULL COLUMNS FROM `{$table}`");

        return array_map(fn ($col) => (array) $col, $columns);
    }

    public function createTable(string $table, string $comment): void
    {
        $this->validateTableName($table);

        if ($this->tableExists($table)) {
            throw new \Exception("数据表 {$table} 已存在");
        }

        $charset = config('database.connections.mysql.charset', 'utf8mb4');
        $collation = config('database.connections.mysql.collation', 'utf8mb4_unicode_ci');

        DB::statement("CREATE TABLE IF NOT EXISTS `{$table}` (
            `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET={$charset} COLLATE={$collation} AUTO_INCREMENT=1 COMMENT=?", [$comment]);
    }

    public function editTable(string $oldTable, string $newTable, string $comment): void
    {
        $this->validateTableName($oldTable);
        $this->validateTableName($newTable);
        $this->ensureNotSystemTable($oldTable, '编辑');

        $database = config('database.connections.mysql.database');

        if ($oldTable !== $newTable) {
            DB::statement("RENAME TABLE `{$database}`.`{$oldTable}` TO `{$database}`.`{$newTable}`");
        }

        DB::statement("ALTER TABLE `{$newTable}` COMMENT = ?", [$comment]);
    }

    public function copyTable(string $sourceTable, string $newTable): void
    {
        $this->validateTableName($sourceTable);
        $this->validateTableName($newTable);

        if (! $this->tableExists($sourceTable)) {
            throw new \Exception("源数据表 {$sourceTable} 不存在");
        }

        if ($this->tableExists($newTable)) {
            throw new \Exception("目标数据表 {$newTable} 已存在");
        }

        DB::statement("CREATE TABLE `{$newTable}` LIKE `{$sourceTable}`");
    }

    public function deleteTable(string $table): void
    {
        $this->validateTableName($table);
        $this->ensureNotSystemTable($table, '删除');

        DB::statement("DROP TABLE IF EXISTS `{$table}`");

        $backupDir = $this->getBackupPath($table);
        if (Storage::disk('local')->exists($backupDir)) {
            Storage::disk('local')->deleteDirectory($backupDir);
        }
    }

    public function optimizeTable(string $table): array
    {
        $this->validateTableName($table);
        $results = DB::select("OPTIMIZE TABLE `{$table}`");

        foreach ($results as $row) {
            if (strtolower($row->Msg_type) === 'status' && $row->Msg_text === 'OK') {
                return ['success' => true, 'message' => '优化完成'];
            }
        }

        foreach ($results as $row) {
            if (strtolower($row->Msg_type) === 'status' && str_contains($row->Msg_text, 'already up to date')) {
                return ['success' => false, 'message' => '表已是最新状态，无需优化'];
            }
        }

        $lastMsg = end($results);

        return ['success' => false, 'message' => '优化失败：'.($lastMsg ? $lastMsg->Msg_text : '未知错误')];
    }

    public function repairTable(string $table): array
    {
        $this->validateTableName($table);
        $results = DB::select("REPAIR TABLE `{$table}`");

        foreach ($results as $row) {
            if (strtolower($row->Msg_type) === 'status' && $row->Msg_text === 'OK') {
                return ['success' => true, 'message' => '修复完成'];
            }
        }

        $lastMsg = end($results);

        return ['success' => false, 'message' => '修复失败：'.($lastMsg ? $lastMsg->Msg_text : '未知错误')];
    }

    public function addFields(string $table, array $fields, string $afterField = ''): void
    {
        $this->validateTableName($table);
        $this->ensureNotSystemTable($table, '修改字段');

        $existingColumns = $this->getTableColumns($table);
        $existingNames = array_column($existingColumns, 'Field');

        $alterParts = [];
        $defaultValIndex = 0;

        foreach ($fields as $i => $field) {
            $name = $field['name'] ?? '';
            $type = $field['type'] ?? '';
            $length = $field['length'] ?? '';
            $default = $field['default'] ?? 'NONE';
            $defaultVal = $field['default_val'] ?? '';
            $remark = $field['remark'] ?? '';

            if (empty($name) || empty($type)) {
                throw new \Exception('第'.($i + 1).'行字段名和类型不能为空');
            }

            if (in_array($name, $existingNames)) {
                throw new \Exception("字段 {$name} 已存在");
            }

            $typeStr = $this->buildFieldType($type, $length);
            $defaultStr = $this->buildFieldDefault($default, $defaultVal);
            $position = $this->buildFieldPosition($afterField, $i, $fields, $name);

            $alterParts[] = "ADD `{$name}` {$typeStr} {$defaultStr} COMMENT '{$remark}' {$position}";
        }

        $sql = "ALTER TABLE `{$table}` ".implode(', ', $alterParts);
        DB::statement($sql);
    }

    public function editField(string $table, string $oldName, string $name, string $type, ?string $length, string $default, string $defaultVal, string $remark, string $afterField = ''): void
    {
        $this->validateTableName($table);
        $this->validateFieldName($oldName);
        $this->validateFieldName($name);
        $this->ensureNotSystemTable($table, '修改字段');

        $typeStr = $this->buildFieldType($type, $length);
        $defaultStr = $this->buildFieldDefault($default, $defaultVal);

        $sql = "ALTER TABLE `{$table}` CHANGE `{$oldName}` `{$name}` {$typeStr} {$defaultStr} COMMENT ?";

        if (! empty($afterField)) {
            $this->validateFieldName($afterField);
            $sql .= " AFTER `{$afterField}`";
        }

        DB::statement($sql, [$remark]);
    }

    public function deleteField(string $table, string $field): void
    {
        $this->validateTableName($table);
        $this->validateFieldName($field);
        $this->ensureNotSystemTable($table, '删除字段');

        if (strtolower($field) === 'id') {
            throw new \Exception('ID字段禁止删除');
        }

        DB::statement("ALTER TABLE `{$table}` DROP `{$field}`");
    }

    public function deleteFields(string $table, array $fields): void
    {
        $this->validateTableName($table);
        $this->ensureNotSystemTable($table, '删除字段');

        $drops = [];
        foreach ($fields as $field) {
            $this->validateFieldName($field);
            if (strtolower($field) === 'id') {
                throw new \Exception('ID字段禁止删除');
            }
            $drops[] = "DROP `{$field}`";
        }

        DB::statement("ALTER TABLE `{$table}` ".implode(', ', $drops));
    }

    public function addFieldIndex(string $table, string $field): void
    {
        $this->validateTableName($table);
        $this->validateFieldName($field);

        $indexes = DB::select("SHOW INDEX FROM `{$table}`");
        foreach ($indexes as $index) {
            if ($index->Column_name === $field) {
                throw new \Exception("字段 {$field} 已存在索引");
            }
        }

        DB::statement("ALTER TABLE `{$table}` ADD INDEX(`{$field}`)");
    }

    public function getTableData(string $table, int $page = 1, int $perPage = 20, ?string $searchField = null, ?string $searchValue = null): array
    {
        $this->validateTableName($table);

        $query = DB::table($table);

        if ($searchField && $searchValue) {
            $this->validateFieldName($searchField);
            $query->where($searchField, 'like', "%{$searchValue}%");
        }

        $total = $query->count();
        $items = $query->orderByDesc('id')
            ->offset(($page - 1) * $perPage)
            ->limit($perPage)
            ->get()
            ->toArray();

        foreach ($items as &$item) {
            $item = (array) $item;
        }

        return [
            'items' => $items,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'last_page' => max(1, ceil($total / $perPage)),
            ],
        ];
    }

    public function addTableRow(string $table, array $data): void
    {
        $this->validateTableName($table);
        $this->ensureNotSystemTable($table, '添加数据');
        DB::table($table)->insert($data);
    }

    public function editTableRow(string $table, int $id, array $data): void
    {
        $this->validateTableName($table);
        $this->ensureNotSystemTable($table, '编辑数据');
        DB::table($table)->where('id', $id)->update($data);
    }

    public function deleteTableRow(string $table, int $id): void
    {
        $this->validateTableName($table);
        $this->ensureNotSystemTable($table, '删除数据');
        DB::table($table)->where('id', $id)->delete();
    }

    public function deleteTableRows(string $table, array $ids): void
    {
        $this->validateTableName($table);
        $this->ensureNotSystemTable($table, '删除数据');
        DB::table($table)->whereIn('id', $ids)->delete();
    }

    public function backupTable(string $table, int $volumeSizeMB = 2): string
    {
        set_time_limit(0);
        $this->validateTableName($table);

        $backupDir = $this->getBackupPath($table);
        $filename = $table . '_' . date('YmdHis');
        $tableDir = storage_path('app/' . $backupDir . '/' . $filename);

        if (! is_dir($tableDir)) {
            mkdir($tableDir, 0755, true);
        }

        $files = $this->streamBackupTable($table, $tableDir, $volumeSizeMB);
        DatabaseBackupHelper::writeVolumeManifest($tableDir, $volumeSizeMB, [$table], $files);

        return $filename;
    }

    public function backupAll(int $volumeSizeMB = 2): string
    {
        set_time_limit(0);

        $tables = $this->getTables();
        $timestamp = date('YmdHis');
        $baseDir = storage_path('app/database-backups/full_' . $timestamp);

        if (! is_dir($baseDir)) {
            mkdir($baseDir, 0755, true);
        }

        $allFiles = [];
        foreach ($tables as $table) {
            $files = $this->streamBackupTable($table['Name'], $baseDir, $volumeSizeMB);
            $allFiles = array_merge($allFiles, $files);
        }

        $allFiles = array_values(array_unique($allFiles));
        DatabaseBackupHelper::writeVolumeManifest($baseDir, $volumeSizeMB, array_column($tables, 'Name'), $allFiles);

        return $timestamp;
    }

    public function getBackupList(string $table): array
    {
        $backupDir = $this->getBackupPath($table);

        if (! Storage::disk('local')->exists($backupDir)) {
            return [];
        }

        $files = Storage::disk('local')->files($backupDir);
        $result = [];

        foreach ($files as $file) {
            $name = basename($file);
            $size = Storage::disk('local')->size($file);
            $time = Storage::disk('local')->lastModified($file);

            $result[] = [
                'name' => $name,
                'size' => $size,
                'time' => $time,
            ];
        }

        usort($result, fn ($a, $b) => $b['time'] - $a['time']);

        return $result;
    }

    public function recoverBackup(string $table, string $filename): array
    {
        set_time_limit(0);
        $this->validateTableName($table);
        $this->validateFilename($filename);

        $backupDir = $this->getBackupPath($table) . '/' . $filename;
        $tableDir = storage_path('app/' . $backupDir);

        if (! is_dir($tableDir)) {
            return ['code' => 50010, 'message' => '备份目录不存在'];
        }

        try {
            $sqlFiles = DatabaseBackupHelper::resolveSqlFiles($tableDir);
            DatabaseBackupHelper::streamRestore($sqlFiles, [$table]);

            return ['code' => 0, 'message' => '恢复成功'];
        } catch (\Throwable $e) {
            Log::error('数据库恢复失败', ['table' => $table, 'error' => $e->getMessage()]);
            return ['code' => 50010, 'message' => '恢复失败：' . $e->getMessage()];
        }
    }

    public function deleteBackup(string $table, string $filename): void
    {
        $this->validateTableName($table);
        $this->validateFilename($filename);

        $backupDir = $this->getBackupPath($table);
        $path = "{$backupDir}/{$filename}";

        if (Storage::disk('local')->exists($path)) {
            Storage::disk('local')->delete($path);
        }
    }

    public function deleteBackups(string $table, array $filenames): void
    {
        foreach ($filenames as $filename) {
            $this->deleteBackup($table, $filename);
        }
    }

    public function isSystemTable(string $table): bool
    {
        foreach ($this->systemTablePrefixes as $prefix) {
            if (Str::startsWith($table, $prefix)) {
                return true;
            }
        }

        return false;
    }

    public function getFieldTypes(): array
    {
        return $this->fieldTypes;
    }

    protected function tableExists(string $table): bool
    {
        $database = config('database.connections.mysql.database');
        $result = DB::selectOne(
            'SELECT COUNT(*) as cnt FROM information_schema.tables WHERE table_schema = ? AND table_name = ?',
            [$database, $table]
        );

        return $result->cnt > 0;
    }

    protected function validateTableName(string $table): void
    {
        if (! preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $table)) {
            throw new \Exception('数据表名格式不合法');
        }
    }

    protected function validateFieldName(string $field): void
    {
        if (! preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $field)) {
            throw new \Exception('字段名格式不合法');
        }
    }

    protected function validateFilename(string $filename): void
    {
        if (! preg_match('/^[a-zA-Z0-9_\-\.]+$/', $filename)) {
            throw new \Exception('文件名格式不合法');
        }

        if (! Str::endsWith($filename, '.sql')) {
            throw new \Exception('仅支持SQL备份文件');
        }
    }

    protected function ensureNotSystemTable(string $table, string $operation): void
    {
        if ($this->isSystemTable($table)) {
            throw new \Exception("系统表禁止{$operation}");
        }
    }

    protected function getBackupPath(string $table): string
    {
        return "database-backups/{$table}";
    }

    protected function buildFieldType(string $type, ?string $length): string
    {
        $type = strtolower($type);

        if (in_array($type, ['int', 'tinyint', 'smallint', 'mediumint', 'bigint', 'varchar', 'char'])) {
            if (empty($length)) {
                throw new \Exception("类型 {$type} 必须指定长度");
            }

            return "{$type}({$length})";
        }

        if ($type === 'decimal' || $type === 'float' || $type === 'double') {
            if (! empty($length)) {
                return "{$type}({$length})";
            }
        }

        return $type;
    }

    protected function buildFieldDefault(string $default, string $defaultVal = ''): string
    {
        return match ($default) {
            'NONE' => 'NULL DEFAULT NULL',
            'NULL' => 'NULL DEFAULT NULL',
            'USER_DEFINED' => "NOT NULL DEFAULT '{$defaultVal}'",
            default => 'NULL DEFAULT NULL',
        };
    }

    protected function buildFieldPosition(string $afterField, int $index, array $fields, string $currentName): string
    {
        if (! empty($afterField)) {
            if ($index === 0) {
                $this->validateFieldName($afterField);

                return "AFTER `{$afterField}`";
            }
            $prevField = $fields[$index - 1]['name'] ?? '';
            if (! empty($prevField)) {
                return "AFTER `{$prevField}`";
            }
        }

        if ($index === 0) {
            return 'FIRST';
        }

        $prevField = $fields[$index - 1]['name'] ?? '';
        if (! empty($prevField)) {
            return "AFTER `{$prevField}`";
        }

        return '';
    }

    /**
     * 流式生成表备份文件（支持分卷）
     *
     * @param string $table 表名
     * @param string $dirPath 写入目录（绝对路径）
     * @param int $volumeSizeMB 分卷大小（MB）
     * @return string[] 生成的文件路径列表
     */
    protected function streamBackupTable(string $table, string $dirPath, int $volumeSizeMB = 2): array
    {
        $this->validateTableName($table);

        $files = DatabaseBackupHelper::streamTableData($table, $dirPath, $volumeSizeMB);

        // 在 db_backup.sql 头部追加注释头
        $headerFile = $dirPath . '/db_backup.sql';
        if (file_exists($headerFile)) {
            $header = "-- CmsPro Database Backup\n";
            $header .= '-- Date: ' . date('Y-m-d H:i:s') . "\n";
            $header .= "-- Table: {$table}\n\n";

            $existing = file_get_contents($headerFile);
            file_put_contents($headerFile, $header . $existing);
        }

        return $files;
    }
}
