package com.ruoyi.quartz.util;

import com.ruoyi.system.service.ISysConfigService;
import com.smartor.mapper.PatMedOuthospMapper;
import com.smartor.service.IPatMedOuthospService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.*;

/**
 * 创建数据库表
 */
@Slf4j
@Component
public class CreateDataTable {
    @Autowired
    private ISysConfigService configService;

    @Autowired
    IPatMedOuthospService iPatMedOuthospService;

    @Autowired
    PatMedOuthospMapper patMedOuthospMapper;

    @Autowired
    private DataSource dataSource;


    public void createOuthospQuarterTable() {
        log.info("【createOuthospQuarterTable】开始执行门诊分表任务");
        try {
            // 从 sys_config 查询分表模式， key = outhosp.split.mode
            String splitMode = configService.selectConfigByKey("outhosp.split.mode");
            if (org.apache.commons.lang3.StringUtils.isBlank(splitMode)) {
                log.info("【createOuthospQuarterTable】未配置 outhosp.split.mode，不执行分表");
                return;
            }
            splitMode = splitMode.trim();
            if (!"季度".equals(splitMode) && !"年".equals(splitMode) && !"月".equals(splitMode)) {
                log.warn("【createOuthospQuarterTable】分表模式配置非法：{}，应为 quarter/year/month，不执行分表", splitMode);
                return;
            }
            String newTable = createPartitionTableAndRefreshView(splitMode);
            if (newTable == null) {
                log.info("【createOuthospQuarterTable】当前周期表已存在，无需操作");
            } else {
                log.info("【createOuthospQuarterTable】新建分表成功：{}，已刷新视图 v_pat_med_outhosp", newTable);
            }
        } catch (Exception e) {
            log.error("【createOuthospQuarterTable】建表异常", e);
        }
    }


    /**
     * 按指定模式建门诊分表并更新动态视图
     * splitMode: 季度 / 年 / 月 / 不分表
     * 表名：
     * quarter -> pat_med_outhosp_20260103
     * year    -> pat_med_outhosp_2026
     * month   -> pat_med_outhosp_202601
     */
    public String createPartitionTableAndRefreshView(String splitMode) {
        LocalDate today = LocalDate.now();
        int year = today.getYear();
        int month = today.getMonthValue();

        // 1. 根据模式计算新表名和模板表名
        String newTableName;
        String templateName;

        if ("年".equals(splitMode)) {
            // 按年：pat_med_outhosp_2026
            newTableName = "pat_med_outhosp_" + year;
            // 模板表：优先上一年分表
            String prevYearTable = "pat_med_outhosp_" + (year - 1);
            templateName = patMedOuthospMapper.tableExists(prevYearTable) > 0 ? prevYearTable : "pat_med_outhosp";

        } else if ("月".equals(splitMode)) {
            // 按月：pat_med_outhosp_202601
            newTableName = String.format("pat_med_outhosp_%d%02d", year, month);
            // 模板表：优先上一月分表
            LocalDate prevMonth = today.minusMonths(1);
            String prevMonthTable = String.format("pat_med_outhosp_%d%02d", prevMonth.getYear(), prevMonth.getMonthValue());
            templateName = patMedOuthospMapper.tableExists(prevMonthTable) > 0 ? prevMonthTable : "pat_med_outhosp";

        } else {
            // 默认按季度：pat_med_outhosp_20260103
            String quarterSuffix;
            if (month <= 3) {
                quarterSuffix = "0103";
            } else if (month <= 6) {
                quarterSuffix = "0406";
            } else if (month <= 9) {
                quarterSuffix = "0709";
            } else {
                quarterSuffix = "1012";
            }
            newTableName = "pat_med_outhosp_" + year + quarterSuffix;
            templateName = resolveQuarterTemplateName(year, quarterSuffix);
        }

        // 2. 检查表是否已存在
        if (patMedOuthospMapper.tableExists(newTableName) > 0) {
            log.info("[建表] 表 {} 已存在，无需重复建表，但仍然尝试刷新存储过程（确保分表已纳入查询）", newTableName);
            refreshOuthospView();
            return null;
        }

        log.info("[建表] 模式={}，使用模板表 {} 创建 {}", splitMode, templateName, newTableName);

        // 3. 创建新表（LIKE 模板表，只复制结构+索引，不含数据）
        patMedOuthospMapper.createPatMedOuthosp(templateName, newTableName);
        // 4. 设置自增主键
        patMedOuthospMapper.createPatMedOuthospAutoAdd(newTableName);

        // 5. 重建动态视图
        refreshOuthospView();

        log.info("[建表] 建表并刷新视图完成，新表：{}", newTableName);
        return newTableName;
    }

    /**
     * 季度分表模板确定：优先上一季度分表，没有则用主表
     */
    private String resolveQuarterTemplateName(int year, String currentQuarterSuffix) {
        String[][] quarters = {{"0103", "1012"}, {"0406", "0103"}, {"0709", "0406"}, {"1012", "0709"}};
        String prevSuffix = null;
        int prevYear = year;
        for (String[] q : quarters) {
            if (q[0].equals(currentQuarterSuffix)) {
                prevSuffix = q[1];
                if (currentQuarterSuffix.equals("0103")) {
                    prevYear = year - 1;
                }
                break;
            }
        }
        if (prevSuffix != null) {
            String prevTableName = "pat_med_outhosp_" + prevYear + prevSuffix;
            if (patMedOuthospMapper.tableExists(prevTableName) > 0) {
                return prevTableName;
            }
        }
        return "pat_med_outhosp";
    }

    /**
     * 刷新存储过程 sp_query_outhosp
     * 逻辑：
     * 1. 从 information_schema 动态获取 pat_med_outhosp 字段列表
     * 2. 查出所有分表（后缀为4-8位纯数字）拼 UNION ALL
     * 3. 根据字段是否存在动态决定存储过程参数和 WHERE 条件
     * 4. 用原生 JDBC 执行（CREATE PROCEDURE 内含分号，MyBatis 无法处理）
     */
    private void refreshOuthospView() {
        // 1. 获取所有分表
        List<String> allTables = patMedOuthospMapper.getAllOuthospTableNames();
        List<String> partTables = allTables.stream().filter(t -> t.matches("pat_med_outhosp_[0-9]{4,8}")).sorted().collect(java.util.stream.Collectors.toList());

        // 2. 动态获取主表字段列表
        List<String> columns = patMedOuthospMapper.getTableColumns("pat_med_outhosp");
        Set<String> cs = new HashSet<>(columns);
        String colList = String.join(",", columns);

        // 3. 拼 UNION ALL（del_flag 内嵌入 SET @sql='...' 中，用 ''0'' 转义）
        StringBuilder union = new StringBuilder();
        for (String tbl : partTables) {
            union.append("SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0'' UNION ALL ");
        }
        union.append("SELECT ").append(colList).append(" FROM pat_med_outhosp WHERE del_flag=''0''");

        // 4. 从 DB 元数据动态构建参数列表
        //    排除无业务意义的管理字段
        Set<String> excludeCols = new HashSet<>(java.util.Arrays.asList("id", "del_flag", "create_by", "create_time", "update_by", "update_time", "remark"));
        // 强制精确匹配的字段（字段名不符合 eqPattern 规则但业务上需精确查询）
        Set<String> forceEqCols = new HashSet<>(java.util.Arrays.asList("orgid", "guid", "campusid"));
        // 精确匹配字段的关键词（含 no/code/flag/status/state/num/type/level/grade/sex/gender/phone/tel/id_）
        java.util.regex.Pattern eqPattern = java.util.regex.Pattern.compile("(no|code|flag|status|state|num|type|level|grade|sex|gender|phone|tel|id_.*|.*_id)$", java.util.regex.Pattern.CASE_INSENSITIVE);

        List<Map<String, String>> colMetaList = patMedOuthospMapper.getTableColumnMeta("pat_med_outhosp");
        // [paramName, colName, kind, sqlType]
        List<Object[]> activeParams = new ArrayList<>();
        Set<String> addedDateCols = new HashSet<>();
        for (Map<String, String> meta : colMetaList) {
            String colName = meta.get("column_name");
            String dataType = meta.get("data_type").toLowerCase();
            String colType = meta.get("column_type").toUpperCase();
            if (excludeCols.contains(colName)) continue;
            switch (dataType) {
                case "tinyint":
                case "smallint":
                case "int":
                case "bigint":
                case "integer":
                case "decimal":
                case "float":
                case "double":
                    activeParams.add(new Object[]{"p_" + colName, colName, "num", colType});
                    break;
                case "date":
                case "datetime":
                case "timestamp":
                    // 日期列生成区间参数，防止重复添加
                    if (addedDateCols.add(colName)) {
                        if (colName.equals("admitdate")) {
                            activeParams.add(new Object[]{"p_begin_" + colName, colName, "date_ge", "DATE"});
                            activeParams.add(new Object[]{"p_end_" + colName, colName, "date_le", "DATE"});
                        }
                    }
                    break;
                default:
                    // varchar / char / text 等字符串类型
                    if (dataType.contains("char") || dataType.contains("text") || dataType.contains("enum")) {
                        if (colName.equals("deptcode")) {
                            activeParams.add(new Object[]{"p_" + colName, colName, "in", "TEXT"});
                            break;
                        }
                        if (colName.equals("hospitaldistrictcode")) {
                            activeParams.add(new Object[]{"p_" + colName, colName, "in", "TEXT"});
                            break;
                        }
                        String kind = forceEqCols.contains(colName) ? "eq" : (eqPattern.matcher(colName).find() ? "eq" : "like");
                        activeParams.add(new Object[]{"p_" + colName, colName, kind, colType});
                    }
                    break;
            }
        }

        // 6. 拼参数定义（去除重复参数名，如 p_begin_xxx/p_end_xxx 同列）
        StringBuilder paramDef = new StringBuilder("CREATE PROCEDURE sp_query_outhosp(");
        Set<String> addedParams = new LinkedHashSet<>();
        for (Object[] m : activeParams) {
            String pName = (String) m[0];
            String dtype = (String) m[3];
            if (addedParams.add(pName)) {
                paramDef.append("IN ").append(pName).append(" ").append(dtype).append(",");
            }
        }
        paramDef.append("IN p_page_size INT,IN p_page_num INT)");

        // 7. 拼存储过程主体
        //    优化策略：用 @cond 变量收集所有过滤条件，然后注入到每个子查询的 WHERE 中
        //    这样 MySQL 可以对每张分表直接走索引，避免全表扫描后外层再过滤
        List<String> body = new ArrayList<>();
        body.add(paramDef.toString());
        body.add("BEGIN");
        body.add("  SET @cond='';");

        // 7a. 动态拼接 @cond 条件片段
        for (Object[] m : activeParams) {
            String pName = (String) m[0];
            String col = (String) m[1];
            String kind = (String) m[2];
            String line;
            switch (kind) {
                case "num":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "='," + pName + "); END IF;";
                    break;
                case "eq":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "=',QUOTE(" + pName + ")); END IF;";
                    break;
                case "like":
                    // MySQL 存储过程内 '' 表示一个单引号
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + " LIKE CONCAT(''%''," + pName + ",''%'')'); END IF;";
                    break;
                case "date_ge":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + ">=',QUOTE(" + pName + ")); END IF;";
                    break;
                case "date_le":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "<=',QUOTE(" + pName + ")); END IF;";
                    break;
                case "in":
                    // 参数为逗号分隔字符串，用 FIND_IN_SET 匹配字段在集合中
                    // 生成 SQL： FIND_IN_SET(col, 'val1,val2,...')
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND FIND_IN_SET(" + col + ",',QUOTE(" + pName + "),')'); END IF;";
                    break;
                default:
                    line = "";
            }
            if (!line.isEmpty()) body.add(line);
        }

        // 7b. 构建 UNION ALL SQL：每个子查询的 WHERE 内直接注入 @cond
        //     最终存储过程里执行的 SQL 类似：
        //     SELECT cols FROM tbl1 WHERE del_flag='0' AND orgid=? AND ...
        //     UNION ALL SELECT cols FROM tbl2 WHERE del_flag='0' AND orgid=? AND ...
        //     ORDER BY update_time DESC LIMIT ? OFFSET ?
        //
        //     实现方式：用 CONCAT(单引号字符串, @cond, ...) 拼接
        //     del_flag=''0'' 在存储过程内单引号字符串中表示一个 '0'
        List<String> allQueryTables = new ArrayList<>(partTables);
        allQueryTables.add("pat_med_outhosp");

        StringBuilder concatArgs = new StringBuilder();
        for (int i = 0; i < allQueryTables.size(); i++) {
            String tbl = allQueryTables.get(i);
            if (i > 0) {
                concatArgs.append(",' UNION ALL SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
            } else {
                concatArgs.append("'SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
            }
        }

        String orderCol = cs.contains("update_time") ? "update_time" : "id";
        body.add("  SET @sql=CONCAT(" + concatArgs + ",' ORDER BY " + orderCol + " DESC');");
        body.add("  IF p_page_size IS NOT NULL AND p_page_num IS NOT NULL THEN");
        body.add("    SET @sql=CONCAT(@sql,' LIMIT ',p_page_size,' OFFSET ',p_page_num);");
        body.add("  END IF;");
        body.add("  PREPARE stmt FROM @sql;");
        body.add("  EXECUTE stmt;");
        body.add("  DEALLOCATE PREPARE stmt;");
        body.add("END");

        String procSql = String.join("\n", body);
        log.debug("[刷新存储过程] DDL=\n{}", procSql);

        // 8. 通过原生 JDBC 执行（CREATE PROCEDURE 含分号，MyBatis 不支持）
        // 策略：
        //   - 存储过程不存在 → 全量创建
        //   - 存储过程已存在 → 只把 DB 中还没有的分表以 UNION ALL 形式追加进 SET @sql=CONCAT(...) 里，
        //     其余所有条件逻辑（人工改过的模糊/区间等）原样保留，绝不覆盖
        try (Connection conn = dataSource.getConnection(); Statement jdbcStmt = conn.createStatement()) {

            // 先查 DB 中是否存在该存储过程及其完整 DDL
            String showCreateDdl = null;
            try (java.sql.ResultSet rs = jdbcStmt.executeQuery("SHOW CREATE PROCEDURE sp_query_outhosp")) {
                if (rs.next()) {
                    showCreateDdl = rs.getString("Create Procedure");
                }
            } catch (Exception ignored) {
                // 存储过程不存在时 SHOW CREATE 会抛异常，忽略
            }

            if (showCreateDdl == null) {
                // 存储过程不存在，全量创建
                jdbcStmt.execute(procSql);
                log.info("[刷新存储过程] sp_query_outhosp 首次创建，分表数量：{}", partTables.size());
            } else {
                // 存储过程已存在：找出 DB 里已有哪些分表，把新增的追加进去
                // 找出当前 DDL 里已出现的所有 pat_med_outhosp_xxx 表名
                java.util.regex.Matcher tblMatcher = java.util.regex.Pattern.compile("pat_med_outhosp_([0-9]{4,8})").matcher(showCreateDdl);
                Set<String> existingTables = new java.util.LinkedHashSet<>();
                while (tblMatcher.find()) {
                    existingTables.add(tblMatcher.group(0));
                }

                // 找出本次新建后还没进入存储过程的分表
                List<String> newTables = partTables.stream().filter(t -> !existingTables.contains(t)).collect(java.util.stream.Collectors.toList());

                if (newTables.isEmpty()) {
                    log.info("[刷新存储过程] 无新增分表，存储过程无需更新");
                } else {
                    // 直接从 DDL 里提取字段列表（避免与 Java 动态查表字段不一致）
                    // DDL 里字段列表就在： SELECT 后面到 FROM 前面
                    java.util.regex.Matcher colMatcher = java.util.regex.Pattern.compile("SELECT\\s+(.+?)\\s+FROM\\s+pat_med_outhosp", java.util.regex.Pattern.CASE_INSENSITIVE).matcher(showCreateDdl);
                    String existingColList = colMatcher.find() ? colMatcher.group(1).trim() : colList;

                    // 精确锚点：',@cond,' UNION ALL SELECT cols FROM pat_med_outhosp WHERE
                    String mainTableAnchor = "',@cond,' UNION ALL SELECT " + existingColList + " FROM pat_med_outhosp WHERE ";
                    int mainAnchorIdx = showCreateDdl.indexOf(mainTableAnchor);

                    // 字段列表匹配不到时，简化定位
                    if (mainAnchorIdx < 0) {
                        // 找 ',@cond,' UNION ALL SELECT ... FROM pat_med_outhosp WHERE
                        // 先找 FROM pat_med_outhosp WHERE，再往前找 ',@cond,'
                        int fromIdx = showCreateDdl.indexOf(" FROM pat_med_outhosp WHERE ");
                        if (fromIdx >= 0) {
                            String before = showCreateDdl.substring(0, fromIdx);
                            int condIdx = before.lastIndexOf("',@cond,'");
                            if (condIdx >= 0) mainAnchorIdx = condIdx;
                        }
                    }

                    if (mainAnchorIdx < 0) {
                        log.warn("[刷新存储过程] 未找到主表锚点，跳过追加");
                    } else {
                        // 新表片段：每张新表追加一段 ',@cond,' UNION ALL SELECT cols FROM 新表 WHERE del_flag=''0''
                        // 插在主表锚点前面，主表锚点本身（',@cond,' UNION ALL ...主表...）保持不动
                        StringBuilder newTablesFragment = new StringBuilder();
                        for (String nt : newTables) {
                            newTablesFragment.append("',@cond,' UNION ALL SELECT ").append(existingColList).append(" FROM ").append(nt).append(" WHERE del_flag=''0''");
                        }

                        // 插入到主表 ',@cond,' 片段前面
                        String updatedDdl = showCreateDdl.substring(0, mainAnchorIdx) + newTablesFragment + showCreateDdl.substring(mainAnchorIdx);

                        log.debug("[刷新存储过程] 更新后 DDL=\n{}", updatedDdl);

                        // 安全保护：DROP 前先保存原 DDL，CREATE 失败则立刻用原 DDL 恢复
                        jdbcStmt.execute("DROP PROCEDURE IF EXISTS sp_query_outhosp");
                        try {
                            jdbcStmt.execute(updatedDdl);
                            log.info("[刷新存储过程] sp_query_outhosp 追加新分表 {}，原有条件保持不变", newTables);
                        } catch (Exception createEx) {
                            log.error("[刷新存储过程] 新 DDL 执行失败，正在用原 DDL 恢复, updatedDdl=\n{}", updatedDdl, createEx);
                            try {
                                jdbcStmt.execute(showCreateDdl);
                                log.info("[刷新存储过程] sp_query_outhosp 已用原 DDL 恢复成功");
                            } catch (Exception rollbackEx) {
                                log.error("[刷新存储过程] 原 DDL 恢复也失败！存储过程丢失！请手动修复, 原 DDL=\n{}", showCreateDdl, rollbackEx);
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.error("[刷新存储过程] sp_query_outhosp 更新失败, DDL=\n{}", procSql, e);
        }

        // 9. 同步刷新 count 存储过程
        refreshOuthospCountProc(partTables, activeParams, colList);
    }

    /**
     * 刷新计数存储过程 sp_count_outhosp
     * 与 sp_query_outhosp 参数完全一致，只返回总条数，不分页
     */
    private void refreshOuthospCountProc(List<String> partTables, List<Object[]> activeParams, String colList) {
        // 拼参数定义（去掉 p_page_size/p_page_num）
        StringBuilder paramDef = new StringBuilder("CREATE PROCEDURE sp_count_outhosp(");
        Set<String> addedParams = new LinkedHashSet<>();
        for (Object[] m : activeParams) {
            String pName = (String) m[0];
            String dtype = (String) m[3];
            if (addedParams.add(pName)) {
                paramDef.append("IN ").append(pName).append(" ").append(dtype).append(",");
            }
        }
        // 去掉最后的多余逗号
        String paramDefStr = paramDef.toString();
        if (paramDefStr.endsWith(",")) {
            paramDefStr = paramDefStr.substring(0, paramDefStr.length() - 1);
        }
        paramDefStr += ")";

        List<String> body = new ArrayList<>();
        body.add(paramDefStr);
        body.add("BEGIN");
        body.add("  SET @cond='';");

        // 条件片段（与 sp_query_outhosp 完全一致）
        for (Object[] m : activeParams) {
            String pName = (String) m[0];
            String col = (String) m[1];
            String kind = (String) m[2];
            String line;
            switch (kind) {
                case "num":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "='," + pName + "); END IF;";
                    break;
                case "eq":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "=',QUOTE(" + pName + ")); END IF;";
                    break;
                case "like":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + " LIKE CONCAT(''%''," + pName + ",''%'')'); END IF;";
                    break;
                case "date_ge":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + ">=',QUOTE(" + pName + ")); END IF;";
                    break;
                case "date_le":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "<=',QUOTE(" + pName + ")); END IF;";
                    break;
                case "in":
                    line = "  IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND FIND_IN_SET(" + col + ",',QUOTE(" + pName + "),')'); END IF;";
                    break;
                default:
                    line = "";
            }
            if (!line.isEmpty()) body.add(line);
        }

        // 拼 COUNT UNION ALL SQL：各分表各计一次再汇总
        // SELECT SUM(cnt) FROM (SELECT COUNT(1) cnt FROM tbl1 WHERE del_flag=''0'' ...
        //                        UNION ALL SELECT COUNT(1) cnt FROM tbl2 WHERE del_flag=''0'' ...) t
        List<String> allQueryTables = new ArrayList<>(partTables);
        allQueryTables.add("pat_med_outhosp");

        StringBuilder concatArgs = new StringBuilder();
        concatArgs.append("'SELECT SUM(cnt) FROM (");
        for (int i = 0; i < allQueryTables.size(); i++) {
            String tbl = allQueryTables.get(i);
            if (i > 0) {
                concatArgs.append(" UNION ALL SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''''0''''").append("',@cond,'");
            } else {
                concatArgs.append("SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''''0''''").append("',@cond,'");
            }
        }
        concatArgs.append(") t';");

        // 重新拼：每个子表 WHERE del_flag=''0'' 后拼 @cond
        // 存储过程内 '' 表示一个单引号，所以 del_flag=''0'' 正确
        StringBuilder concatArgs2 = new StringBuilder();
        for (int i = 0; i < allQueryTables.size(); i++) {
            String tbl = allQueryTables.get(i);
            if (i == 0) {
                concatArgs2.append("'SELECT SUM(cnt) FROM (SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
            } else {
                concatArgs2.append(",' UNION ALL SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
            }
        }
        concatArgs2.append(",' ) t'");

        body.add("  SET @sql=CONCAT(" + concatArgs2 + ");");
        body.add("  PREPARE stmt FROM @sql;");
        body.add("  EXECUTE stmt;");
        body.add("  DEALLOCATE PREPARE stmt;");
        body.add("END");

        String procSql = String.join("\n", body);
        log.debug("[刷新count存储过程] DDL=\n{}", procSql);

        try (Connection conn = dataSource.getConnection(); Statement jdbcStmt = conn.createStatement()) {
            String showCreateDdl = null;
            try (java.sql.ResultSet rs = jdbcStmt.executeQuery("SHOW CREATE PROCEDURE sp_count_outhosp")) {
                if (rs.next()) showCreateDdl = rs.getString("Create Procedure");
            } catch (Exception ignored) {
            }

            if (showCreateDdl == null) {
                jdbcStmt.execute(procSql);
                log.info("[刷新count存储过程] sp_count_outhosp 首次创建，分表数量：{}", partTables.size());
            } else {
                // 只追加新分表，不覆盖已有条件
                java.util.regex.Matcher tblMatcher = java.util.regex.Pattern.compile("pat_med_outhosp_([0-9]{4,8})").matcher(showCreateDdl);
                Set<String> existingTables = new java.util.LinkedHashSet<>();
                while (tblMatcher.find()) existingTables.add(tblMatcher.group(0));

                List<String> newTables = partTables.stream().filter(t -> !existingTables.contains(t)).collect(java.util.stream.Collectors.toList());
                if (newTables.isEmpty()) {
                    log.info("[刷新count存储过程] 无新增分表，无需更新");
                    return;
                }

                // 从 DDL 里提取 COUNT(1) cnt FROM 后面的字段模式无案，COUNT存储过程没有字段列表
                // 定位主表锚点：',@cond,' UNION ALL SELECT COUNT(1) cnt FROM pat_med_outhosp WHERE
                String mainAnchor = "',@cond,' UNION ALL SELECT COUNT(1) cnt FROM pat_med_outhosp WHERE ";
                int mainAnchorIdx = showCreateDdl.indexOf(mainAnchor);
                if (mainAnchorIdx < 0) {
                    int fromIdx = showCreateDdl.indexOf(" FROM pat_med_outhosp WHERE ");
                    if (fromIdx >= 0) {
                        String before = showCreateDdl.substring(0, fromIdx);
                        int condIdx = before.lastIndexOf("',@cond,'");
                        if (condIdx >= 0) mainAnchorIdx = condIdx;
                    }
                }

                if (mainAnchorIdx < 0) {
                    log.warn("[刷新count存储过程] 未找到主表锚点，跳过追加");
                    return;
                }

                StringBuilder newFrag = new StringBuilder();
                for (String nt : newTables) {
                    newFrag.append("',@cond,' UNION ALL SELECT COUNT(1) cnt FROM ").append(nt).append(" WHERE del_flag=''0''");
                }
                String updatedDdl = showCreateDdl.substring(0, mainAnchorIdx) + newFrag + showCreateDdl.substring(mainAnchorIdx);
                log.debug("[刷新count存储过程] 更新后 DDL=\n{}", updatedDdl);

                jdbcStmt.execute("DROP PROCEDURE IF EXISTS sp_count_outhosp");
                try {
                    jdbcStmt.execute(updatedDdl);
                    log.info("[刷新count存储过程] sp_count_outhosp 追加新分表 {}", newTables);
                } catch (Exception createEx) {
                    log.error("[刷新count存储过程] 新 DDL 失败，恢复原 DDL, updatedDdl=\n{}", updatedDdl, createEx);
                    try {
                        jdbcStmt.execute(showCreateDdl);
                        log.info("[刷新count存储过程] 已恢复原 DDL");
                    } catch (Exception rollbackEx) {
                        log.error("[刷新count存储过程] 恢复也失败！请手动修复, 原 DDL=\n{}", showCreateDdl, rollbackEx);
                    }
                }
            }
        } catch (Exception e) {
            log.error("[刷新count存储过程] sp_count_outhosp 更新失败", e);
        }
    }


}