kknongchang_2506/app/Module/Cleanup/Logics/TableScannerLogic.php

<?php

namespace App\Module\Cleanup\Logics;

use App\Module\Cleanup\Models\CleanupConfig;
use App\Module\Cleanup\Models\CleanupTableStats;
use App\Module\Cleanup\Enums\DATA_CATEGORY;
use App\Module\Cleanup\Enums\CLEANUP_TYPE;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Log;

/**
 * 表扫描逻辑类
 * 
 * 负责扫描系统中的数据表并生成清理配置
 */
class TableScannerLogic
{
    /**
     * 扫描所有数据表
     *
     * @param bool $forceRefresh 是否强制刷新
     * @return array 扫描结果
     */
    public static function scanAllTables(bool $forceRefresh = false): array
    {
        $startTime = microtime(true);
        
        // 获取所有以 kku_ 开头的表
        $tables = static::getAllTables();
        
        $result = [
            'total_tables' => count($tables),
            'scanned_tables' => 0,
            'new_tables' => 0,
            'updated_tables' => 0,
            'tables' => [],
            'scan_time' => 0,
        ];

        foreach ($tables as $tableName) {
            try {
                $tableInfo = static::scanTable($tableName, $forceRefresh);
                $result['tables'][] = $tableInfo;
                $result['scanned_tables']++;
                
                if ($tableInfo['is_new']) {
                    $result['new_tables']++;
                } elseif ($tableInfo['is_updated']) {
                    $result['updated_tables']++;
                }
            } catch (\Exception $e) {
                Log::error("扫描表 {$tableName} 失败: " . $e->getMessage());
            }
        }

        $result['scan_time'] = round(microtime(true) - $startTime, 3);
        
        return $result;
    }

    /**
     * 扫描单个表
     *
     * @param string $tableName 表名
     * @param bool $forceRefresh 是否强制刷新
     * @return array 表信息
     */
    public static function scanTable(string $tableName, bool $forceRefresh = false): array
    {
        // 检查是否已存在配置
        $existingConfig = CleanupConfig::where('table_name', $tableName)->first();
        $isNew = !$existingConfig;
        $isUpdated = false;

        // 获取表的基本信息
        $tableInfo = static::getTableInfo($tableName);
        
        // 分析表结构
        $structure = static::analyzeTableStructure($tableName);
        
        // 自动识别数据分类
        $dataCategory = DATA_CATEGORY::detectFromTableName($tableName);
        
        // 识别模块名称
        $moduleName = static::detectModuleName($tableName);
        
        // 生成默认清理配置
        $defaultConfig = static::generateDefaultConfig($dataCategory, $structure);

        if ($isNew || $forceRefresh) {
            // 创建或更新配置
            $configData = [
                'table_name' => $tableName,
                'module_name' => $moduleName,
                'data_category' => $dataCategory->value,
                'default_cleanup_type' => $defaultConfig['cleanup_type'],
                'default_conditions' => $defaultConfig['conditions'],
                'is_enabled' => $defaultConfig['is_enabled'],
                'priority' => $defaultConfig['priority'],
                'batch_size' => $defaultConfig['batch_size'],
                'description' => $defaultConfig['description'],
            ];

            if ($isNew) {
                CleanupConfig::create($configData);
            } else {
                $existingConfig->update($configData);
                $isUpdated = true;
            }
        }

        // 更新表统计信息
        static::updateTableStats($tableName, $tableInfo, $structure);

        return [
            'table_name' => $tableName,
            'module_name' => $moduleName,
            'data_category' => $dataCategory->value,
            'data_category_name' => $dataCategory->getDescription(),
            'record_count' => $tableInfo['record_count'],
            'table_size_mb' => $tableInfo['table_size_mb'],
            'has_time_field' => !empty($structure['time_fields']),
            'time_fields' => $structure['time_fields'],
            'has_user_field' => !empty($structure['user_fields']),
            'user_fields' => $structure['user_fields'],
            'has_status_field' => !empty($structure['status_fields']),
            'status_fields' => $structure['status_fields'],
            'default_cleanup_type' => $defaultConfig['cleanup_type'],
            'default_priority' => $defaultConfig['priority'],
            'is_new' => $isNew,
            'is_updated' => $isUpdated,
        ];
    }

    /**
     * 获取所有数据表列表
     *
     * @return array 表名列表
     */
    private static function getAllTables(): array
    {
        $tables = DB::select("SHOW TABLES LIKE 'kku_%'");

        if (empty($tables)) {
            return [];
        }

        // 获取第一个表对象的属性名
        $firstTable = $tables[0];
        $properties = get_object_vars($firstTable);
        $tableColumn = array_keys($properties)[0]; // 获取第一个属性名

        return array_map(function($table) use ($tableColumn) {
            return $table->$tableColumn;
        }, $tables);
    }

    /**
     * 获取表的基本信息
     *
     * @param string $tableName 表名
     * @return array 表信息
     */
    private static function getTableInfo(string $tableName): array
    {
        // 获取表状态信息
        $tableStatus = DB::select("SHOW TABLE STATUS LIKE '{$tableName}'");

        if (empty($tableStatus)) {
            throw new \Exception("表 {$tableName} 不存在");
        }

        $status = $tableStatus[0];

        // 获取记录数量（移除前缀，因为Laravel会自动添加）
        $tableNameWithoutPrefix = preg_replace('/^kku_/', '', $tableName);
        $recordCount = DB::table($tableNameWithoutPrefix)->count();

        return [
            'record_count' => $recordCount,
            'table_size_mb' => round(($status->Data_length + $status->Index_length) / 1024 / 1024, 2),
            'index_size_mb' => round($status->Index_length / 1024 / 1024, 2),
            'data_free_mb' => round($status->Data_free / 1024 / 1024, 2),
            'avg_row_length' => $status->Avg_row_length,
            'auto_increment' => $status->Auto_increment,
            'engine' => $status->Engine,
            'collation' => $status->Collation,
        ];
    }

    /**
     * 分析表结构
     *
     * @param string $tableName 表名
     * @return array 结构信息
     */
    private static function analyzeTableStructure(string $tableName): array
    {
        $columns = Schema::getColumnListing($tableName);
        
        $timeFields = [];
        $userFields = [];
        $statusFields = [];
        $primaryKey = null;

        foreach ($columns as $column) {
            $columnInfo = DB::select("SHOW COLUMNS FROM {$tableName} LIKE '{$column}'");

            if (!empty($columnInfo)) {
                $info = $columnInfo[0];
                
                // 检查主键
                if ($info->Key === 'PRI') {
                    $primaryKey = $column;
                }
                
                // 检查时间字段
                if (static::isTimeField($column, $info->Type)) {
                    $timeFields[] = $column;
                }
                
                // 检查用户字段
                if (static::isUserField($column)) {
                    $userFields[] = $column;
                }
                
                // 检查状态字段
                if (static::isStatusField($column)) {
                    $statusFields[] = $column;
                }
            }
        }

        // 获取时间范围
        $timeRange = static::getTimeRange($tableName, $timeFields);

        return [
            'columns' => $columns,
            'primary_key' => $primaryKey,
            'time_fields' => $timeFields,
            'user_fields' => $userFields,
            'status_fields' => $statusFields,
            'time_range' => $timeRange,
        ];
    }

    /**
     * 判断是否为时间字段
     *
     * @param string $columnName 字段名
     * @param string $columnType 字段类型
     * @return bool
     */
    private static function isTimeField(string $columnName, string $columnType): bool
    {
        // 按字段名判断
        $timeFieldNames = [
            'created_at', 'updated_at', 'deleted_at',
            'start_time', 'end_time', 'expire_time',
            'login_time', 'logout_time', 'last_login',
            'published_at', 'completed_at', 'processed_at'
        ];
        
        if (in_array($columnName, $timeFieldNames)) {
            return true;
        }
        
        // 按字段名模式判断
        if (preg_match('/(time|date|at)$/i', $columnName)) {
            return true;
        }
        
        // 按字段类型判断
        if (preg_match('/^(datetime|timestamp|date)/i', $columnType)) {
            return true;
        }
        
        return false;
    }

    /**
     * 判断是否为用户字段
     *
     * @param string $columnName 字段名
     * @return bool
     */
    private static function isUserField(string $columnName): bool
    {
        $userFieldNames = [
            'user_id', 'urs_user_id', 'created_by', 'updated_by',
            'owner_id', 'author_id', 'operator_id'
        ];
        
        return in_array($columnName, $userFieldNames);
    }

    /**
     * 判断是否为状态字段
     *
     * @param string $columnName 字段名
     * @return bool
     */
    private static function isStatusField(string $columnName): bool
    {
        $statusFieldNames = [
            'status', 'state', 'is_active', 'is_enabled',
            'is_deleted', 'is_published', 'is_completed'
        ];
        
        return in_array($columnName, $statusFieldNames);
    }

    /**
     * 获取时间范围
     *
     * @param string $tableName 表名
     * @param array $timeFields 时间字段
     * @return array 时间范围
     */
    private static function getTimeRange(string $tableName, array $timeFields): array
    {
        if (empty($timeFields)) {
            return [];
        }

        $timeField = $timeFields[0]; // 使用第一个时间字段
        
        try {
            // 移除前缀，因为Laravel会自动添加
            $tableNameWithoutPrefix = preg_replace('/^kku_/', '', $tableName);
            $result = DB::table($tableNameWithoutPrefix)
                ->selectRaw("MIN({$timeField}) as min_time, MAX({$timeField}) as max_time")
                ->whereNotNull($timeField)
                ->first();
            
            return [
                'field' => $timeField,
                'min_time' => $result->min_time,
                'max_time' => $result->max_time,
            ];
        } catch (\Exception $e) {
            return [];
        }
    }

    /**
     * 识别模块名称
     *
     * @param string $tableName 表名
     * @return string 模块名称
     */
    private static function detectModuleName(string $tableName): string
    {
        // 移除表前缀
        $name = preg_replace('/^kku_/', '', $tableName);
        
        // 常见模块映射
        $moduleMap = [
            'farm' => 'Farm',
            'item' => 'GameItems',
            'pet' => 'Pet',
            'user' => 'User',
            'fund' => 'Fund',
            'mex' => 'Mex',
            'promotion' => 'Promotion',
            'config' => 'Config',
            'log' => 'Log',
            'cache' => 'Cache',
            'session' => 'Session',
        ];

        foreach ($moduleMap as $prefix => $module) {
            if (str_starts_with($name, $prefix . '_')) {
                return $module;
            }
        }

        // 尝试从表名中提取模块名
        $parts = explode('_', $name);
        if (!empty($parts)) {
            return ucfirst($parts[0]);
        }

        return 'Unknown';
    }

    /**
     * 生成默认清理配置
     *
     * @param DATA_CATEGORY $dataCategory 数据分类
     * @param array $structure 表结构
     * @return array 默认配置
     */
    private static function generateDefaultConfig(DATA_CATEGORY $dataCategory, array $structure): array
    {
        $cleanupType = $dataCategory->getDefaultCleanupType();
        $priority = $dataCategory->getDefaultPriority();
        $isEnabled = $dataCategory->isDefaultEnabled();
        
        $conditions = null;
        $batchSize = 1000;
        
        // 根据清理类型生成默认条件
        if ($cleanupType === CLEANUP_TYPE::DELETE_BY_TIME && !empty($structure['time_fields'])) {
            $conditions = [
                'time_field' => $structure['time_fields'][0],
                'time_condition' => 'older_than',
                'time_value' => 30,
                'time_unit' => 'days'
            ];
        }
        
        // 根据数据分类调整批处理大小
        if ($dataCategory === DATA_CATEGORY::LOG_DATA) {
            $batchSize = 5000; // 日志数据可以使用更大的批处理
        } elseif ($dataCategory === DATA_CATEGORY::CACHE_DATA) {
            $batchSize = 10000; // 缓存数据可以使用更大的批处理
        }

        $description = static::generateDescription($dataCategory, $cleanupType, $conditions);

        return [
            'cleanup_type' => $cleanupType->value,
            'conditions' => $conditions,
            'is_enabled' => $isEnabled,
            'priority' => $priority,
            'batch_size' => $batchSize,
            'description' => $description,
        ];
    }

    /**
     * 生成配置描述
     *
     * @param DATA_CATEGORY $dataCategory 数据分类
     * @param CLEANUP_TYPE $cleanupType 清理类型
     * @param array|null $conditions 清理条件
     * @return string 描述
     */
    private static function generateDescription(DATA_CATEGORY $dataCategory, CLEANUP_TYPE $cleanupType, ?array $conditions): string
    {
        $description = $dataCategory->getDescription() . ' - ' . $cleanupType->getDescription();
        
        if ($conditions && $cleanupType === CLEANUP_TYPE::DELETE_BY_TIME) {
            $description .= "，保留{$conditions['time_value']}{$conditions['time_unit']}内的数据";
        }
        
        return $description;
    }

    /**
     * 更新表统计信息
     *
     * @param string $tableName 表名
     * @param array $tableInfo 表信息
     * @param array $structure 表结构
     * @return void
     */
    private static function updateTableStats(string $tableName, array $tableInfo, array $structure): void
    {
        $statsData = [
            'table_name' => $tableName,
            'record_count' => $tableInfo['record_count'],
            'table_size_mb' => $tableInfo['table_size_mb'],
            'index_size_mb' => $tableInfo['index_size_mb'],
            'data_free_mb' => $tableInfo['data_free_mb'],
            'avg_row_length' => $tableInfo['avg_row_length'],
            'auto_increment' => $tableInfo['auto_increment'],
            'oldest_record_time' => $structure['time_range']['min_time'] ?? null,
            'newest_record_time' => $structure['time_range']['max_time'] ?? null,
            'scan_time' => now(),
        ];

        // 这里应该创建 CleanupTableStats 模型，暂时跳过
        // CleanupTableStats::create($statsData);
    }
}