cfc/cfc_backend/src/main/java/com/zxyj/config/SchemaSyncHandler.java

package com.zxyj.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 运行时数据库结构自动同步处理器
 * 当SQL报错"字段不存在"时，自动检测并添加字段
 */
@Slf4j
@Component
public class SchemaSyncHandler {

    @Resource
    private JdbcTemplate jdbcTemplate;

    // 已处理过的表缓存，避免重复检查
    private final Set<String> processedTables = ConcurrentHashMap.newKeySet();

    // 字段定义映射表：表名 -> (字段名 -> MySQL类型定义)
    private static final Map<String, Map<String, String>> SCHEMA_DEFINITIONS;
    
    static {
        // rewards 表字段
        Map<String, String> rewardsFields = new HashMap<>();
        rewardsFields.put("title", "VARCHAR(100) NOT NULL DEFAULT ''");
        rewardsFields.put("points_required", "INT NOT NULL DEFAULT 0");
        rewardsFields.put("category", "VARCHAR(32)");
        rewardsFields.put("status", "ENUM('available','pending','exchanged','rejected') DEFAULT 'available'");
        rewardsFields.put("exchange_approved", "TINYINT");
        rewardsFields.put("exchanged_at", "DATETIME");
        rewardsFields.put("is_template", "TINYINT DEFAULT 0");
        rewardsFields.put("child_id", "BIGINT NULL");
        rewardsFields.put("family_id", "BIGINT NOT NULL");
        
        // tasks 表字段
        Map<String, String> tasksFields = new HashMap<>();
        tasksFields.put("title", "VARCHAR(200) NOT NULL");
        tasksFields.put("description", "VARCHAR(500)");
        tasksFields.put("category", "VARCHAR(32)");
        tasksFields.put("points", "TINYINT DEFAULT 2");
        tasksFields.put("deadline", "DATETIME");
        tasksFields.put("child_id", "BIGINT NULL");
        tasksFields.put("family_id", "BIGINT NOT NULL");
        tasksFields.put("creator_id", "BIGINT NOT NULL");
        tasksFields.put("status", "ENUM('pending','completed','overdue','cancelled') DEFAULT 'pending'");
        tasksFields.put("executor_type", "ENUM('child','parent') DEFAULT 'child'");
        tasksFields.put("executor_id", "BIGINT");
        tasksFields.put("repeat_type", "ENUM('none','daily','weekly') DEFAULT 'none'");
        tasksFields.put("need_review", "TINYINT DEFAULT 0");
        tasksFields.put("bonus_rule", "VARCHAR(500)");
        tasksFields.put("points_expire_days", "INT DEFAULT 0");
        tasksFields.put("template_id", "BIGINT");
        tasksFields.put("is_template", "TINYINT DEFAULT 0");
        
        // users 表字段
        Map<String, String> usersFields = new HashMap<>();
        usersFields.put("openid", "VARCHAR(64) NOT NULL");
        usersFields.put("unionid", "VARCHAR(64)");
        usersFields.put("family_id", "BIGINT NOT NULL");
        usersFields.put("role", "ENUM('parent','child','teacher') NOT NULL");
        usersFields.put("nickname", "VARCHAR(50)");
        usersFields.put("avatar", "VARCHAR(255)");
        usersFields.put("password", "VARCHAR(128)");
        usersFields.put("roles", "VARCHAR(100)");
        usersFields.put("total_points", "INT DEFAULT 0");
        
        // children 表字段
        Map<String, String> childrenFields = new HashMap<>();
        childrenFields.put("user_id", "BIGINT NOT NULL");
        childrenFields.put("family_id", "BIGINT NOT NULL");
        childrenFields.put("nickname", "VARCHAR(50) NOT NULL");
        childrenFields.put("age", "TINYINT NOT NULL");
        childrenFields.put("dan_level", "VARCHAR(2)");
        childrenFields.put("penalty_enabled", "TINYINT DEFAULT 1");
        childrenFields.put("theme", "VARCHAR(32) DEFAULT 'default'");
        childrenFields.put("total_points", "INT DEFAULT 0");
        childrenFields.put("streak_days", "INT DEFAULT 0");
        childrenFields.put("birthday", "DATE");
        childrenFields.put("gender", "ENUM('male','female')");
        
        // points_log 表字段
        Map<String, String> pointsLogFields = new HashMap<>();
        pointsLogFields.put("child_id", "BIGINT NOT NULL");
        pointsLogFields.put("task_id", "BIGINT");
        pointsLogFields.put("amount", "INT NOT NULL");
        pointsLogFields.put("type", "ENUM('earn','spend','bonus','penalty','adjust','reset','refund') NOT NULL");
        pointsLogFields.put("description", "VARCHAR(255)");
        pointsLogFields.put("expire_at", "DATE");
        pointsLogFields.put("expired", "TINYINT DEFAULT 0");
        
        SCHEMA_DEFINITIONS = new HashMap<>();
        SCHEMA_DEFINITIONS.put("rewards", rewardsFields);
        SCHEMA_DEFINITIONS.put("tasks", tasksFields);
        SCHEMA_DEFINITIONS.put("users", usersFields);
        SCHEMA_DEFINITIONS.put("children", childrenFields);
        SCHEMA_DEFINITIONS.put("points_log", pointsLogFields);
    }

    /**
     * 处理字段不存在的异常，自动添加字段后重试
     * @param exception 原始SQL异常消息
     * @param operation 要重试的操作
     * @return 重试结果，如果成功返回null，失败返回异常消息
     */
    @Transactional
    public String handleMissingColumn(String exception, Runnable operation) {
        // 匹配 "Unknown column 'xxx' in 'field list'" 或 "Field 'xxx' doesn't have a default value"
        Pattern pattern = Pattern.compile("Unknown column '(\\w+)' in '(?:field list|\\w+)'", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(exception);
        
        String fieldName = null;
        if (!matcher.find()) {
            // 也可能是 "Field 'xxx' doesn't have a default value" 情况
            pattern = Pattern.compile("Field '(\\w+)' doesn't have a default value", Pattern.CASE_INSENSITIVE);
            matcher = pattern.matcher(exception);
            if (!matcher.find()) {
                log.warn("无法解析字段缺失错误: {}", exception);
                return "无法解析字段缺失错误";
            }
        }
        fieldName = matcher.group(1);
        
        // 尝试提取表名
        String tableName = extractTableName(exception);
        if (tableName == null) {
            log.warn("无法从错误消息中提取表名: {}", exception);
            return "无法提取表名";
        }
        
        log.info("检测到字段缺失: {}.{}", tableName, fieldName);
        
        // 检查并添加字段
        boolean success = addMissingColumn(tableName, fieldName);
        
        if (!success) {
            return "无法自动添加字段: " + tableName + "." + fieldName;
        }
        
        // 重试操作
        try {
            operation.run();
            log.info("字段自动同步成功，重试操作完成: {}.{}", tableName, fieldName);
            return null;
        } catch (Exception e) {
            log.error("重试操作仍然失败: {}", e.getMessage());
            return "重试失败: " + e.getMessage();
        }
    }
    
    /**
     * 从异常消息中提取表名
     */
    private String extractTableName(String message) {
        // 优先尝试从表名中提取 (tableName.field)
        Pattern p = Pattern.compile("\\b(\\w+)\\.(\\w+)\\b", Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(message);
        if (m.find()) {
            return m.group(1);
        }
        
        // 从 INSERT/UPDATE/INTO tableName 中提取
        p = Pattern.compile("(?:INSERT\\s+INTO|UPDATE|INTO)\\s+(\\w+)", Pattern.CASE_INSENSITIVE);
        m = p.matcher(message);
        if (m.find()) {
            return m.group(1);
        }
        
        return null;
    }
    
    /**
     * 添加缺失的列
     */
    private boolean addMissingColumn(String tableName, String fieldName) {
        tableName = tableName.toLowerCase();
        
        // 检查是否已经处理过
        String cacheKey = tableName + "." + fieldName;
        if (processedTables.contains(cacheKey)) {
            log.info("字段已在缓存中，跳过: {}", cacheKey);
            return true;
        }
        
        // 检查表定义中是否存在该字段
        Map<String, String> fields = SCHEMA_DEFINITIONS.get(tableName);
        if (fields == null) {
            log.warn("未找到表 {} 的字段定义", tableName);
            return false;
        }
        
        String columnDef = fields.get(fieldName.toLowerCase());
        if (columnDef == null) {
            log.warn("表 {} 中未定义字段 {}", tableName, fieldName);
            return false;
        }
        
        try {
            // 检查字段是否已存在
            boolean exists = checkColumnExists(tableName, fieldName);
            if (!exists) {
                String sql = String.format("ALTER TABLE %s ADD COLUMN %s %s", tableName, fieldName, columnDef);
                jdbcTemplate.execute(sql);
                log.info("已自动添加字段: {}.{}", tableName, fieldName);
            }
            
            // 加入缓存
            processedTables.add(cacheKey);
            return true;
        } catch (Exception e) {
            log.error("自动添加字段失败: {}.{} - {}", tableName, fieldName, e.getMessage());
            return false;
        }
    }
    
    /**
     * 检查字段是否已存在
     */
    private boolean checkColumnExists(String tableName, String fieldName) {
        try {
            String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS " +
                       "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName, fieldName);
            return count != null && count > 0;
        } catch (Exception e) {
            return false;
        }
    }
    
    /**
     * 运行时同步整个表的结构
     */
    @Transactional
    public void syncTableSchema(String tableName) {
        tableName = tableName.toLowerCase();
        Map<String, String> fields = SCHEMA_DEFINITIONS.get(tableName);
        if (fields == null) {
            log.warn("未找到表 {} 的定义，跳过同步", tableName);
            return;
        }
        
        log.info("开始同步表 {} 的结构...", tableName);
        
        for (Map.Entry<String, String> entry : fields.entrySet()) {
            String fieldName = entry.getKey();
            String columnDef = entry.getValue();
            
            try {
                boolean exists = checkColumnExists(tableName, fieldName);
                if (!exists) {
                    String sql = String.format("ALTER TABLE %s ADD COLUMN %s %s", tableName, fieldName, columnDef);
                    jdbcTemplate.execute(sql);
                    log.info("已添加字段: {}.{}", tableName, fieldName);
                }
            } catch (Exception e) {
                log.warn("同步字段失败: {}.{} - {}", tableName, fieldName, e.getMessage());
            }
        }
        
        log.info("表 {} 结构同步完成", tableName);
    }
    
    /**
     * 同步所有已定义的表
     */
    public void syncAllTables() {
        log.info("开始同步所有表结构...");
        
        for (String tableName : SCHEMA_DEFINITIONS.keySet()) {
            try {
                syncTableSchema(tableName);
            } catch (Exception e) {
                log.error("同步表 {} 失败: {}", tableName, e.getMessage());
            }
        }
        
        log.info("所有表结构同步完成");
    }
}