package com.smqjh.agent.mcp;

import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

final class ReadOnlyDatabaseClient {
    private static final Pattern TABLE_PATTERN = Pattern.compile("\\b(?:from|join)\\s+([`.\\w]+)", Pattern.CASE_INSENSITIVE);
    private static final Set<String> ALLOWED_TABLES = Set.of(
        "smqjh_oms.oms_order",
        "smqjh_oms.oms_order_item",
        "smqjh_system.sm_member",
        "smqjh_system.ums_member_account",
        "smqjh_system.sm_channel",
        "smqjh_system.sm_points_recharge",
        "smqjh_system.sm_points_record",
        "smqjh_pms.tz_prod",
        "smqjh_pms.tz_sku",
        "smqjh_pms.tz_channel_prod",
        "smqjh_pms.tz_shop_sku",
        "smqjh_pms.pms_djk_goods",
        "smqjh_pms.pms_spu",
        "smqjh_pms.pms_sku",
        "smqjh_pms.pms_video_product",
        "smqjh_pms.pms_video_channel_price",
        "oms_order",
        "oms_order_item",
        "sm_member",
        "ums_member_account",
        "sm_channel",
        "sm_points_recharge",
        "sm_points_record",
        "tz_prod",
        "tz_sku",
        "tz_channel_prod",
        "tz_shop_sku",
        "pms_djk_goods",
        "pms_spu",
        "pms_sku",
        "pms_video_product",
        "pms_video_channel_price"
    );

    private final AppConfig config;

    ReadOnlyDatabaseClient(AppConfig config) {
        this.config = config;
    }

    ObjectNode runReadOnlyQuery(String sql) throws Exception {
        ensureDatabaseEnabled();
        String safeSql = normalizeSql(sql);
        assertReadOnlySql(safeSql);
        assertAllowedTables(safeSql);
        String executedSql = withDefaultLimit(safeSql);

        try (Connection connection = connect()) {
            prepareReadOnlySession(connection);
            try (Statement statement = connection.createStatement()) {
                statement.setQueryTimeout(8);
                try (ResultSet resultSet = statement.executeQuery(executedSql)) {
                    return resultSetPayload(resultSet, executedSql);
                }
            }
        }
    }

    ObjectNode productLookupSummary(String keyword) throws Exception {
        ensureDatabaseEnabled();
        String normalized = normalizeProductKeyword(keyword);
        List<String> candidates = productKeywordCandidates(normalized);
        String sql = """
            SELECT
              p.prod_id,
              p.prod_name,
              p.brand_name,
              p.brief,
              p.price AS prod_price,
              p.ori_price,
              p.status AS prod_status,
              s.sku_id,
              s.sku_name,
              s.sku_code,
              s.price AS sku_price,
              s.ori_price AS sku_ori_price,
              s.status AS sku_status
            FROM smqjh_pms.tz_prod p
            LEFT JOIN smqjh_pms.tz_sku s ON s.prod_id = p.prod_id AND COALESCE(s.is_delete, 0) = 0
            WHERE COALESCE(p.is_delete, 0) = 0
              AND (
                p.prod_name LIKE ?
                OR p.brand_name LIKE ?
                OR s.prod_name LIKE ?
                OR s.sku_name LIKE ?
                OR s.sku_code LIKE ?
              )
            ORDER BY
              CASE WHEN p.prod_name LIKE ? OR s.prod_name LIKE ? OR s.sku_name LIKE ? THEN 0 ELSE 1 END,
              p.status DESC,
              s.status DESC,
              p.update_time DESC,
              p.prod_id DESC,
              s.sku_id DESC
            LIMIT 20
            """;

        try (Connection connection = connect()) {
            prepareReadOnlySession(connection);
            for (String candidate : candidates) {
                String like = "%" + candidate + "%";
                try (PreparedStatement statement = connection.prepareStatement(sql)) {
                    for (int i = 1; i <= 8; i++) {
                        statement.setString(i, like);
                    }
                    statement.setQueryTimeout(8);
                    try (ResultSet rs = statement.executeQuery()) {
                        ObjectNode payload = resultSetPayload(rs, "商品资料查询: tz_prod/tz_sku LIKE \"" + candidate + "\"");
                        if (payload.path("rowCount").asInt() > 0) {
                            payload.put("keyword", keyword);
                            payload.put("matchedKeyword", candidate);
                            payload.put("priceUnit", "tz_prod/tz_sku price 字段当前按分转元展示；最终以业务系统实际字段单位为准");
                            enrichProductRows(payload);
                            return payload;
                        }
                    }
                }
            }
        }

        ObjectNode miss = Jsons.object();
        miss.put("ok", false);
        miss.put("message", "未匹配到未逻辑删除的商品");
        miss.put("keyword", keyword);
        miss.set("candidates", Jsons.MAPPER.valueToTree(candidates));
        return miss;
    }

    ObjectNode countOrders(String mobile, String dateFrom, String dateTo) throws Exception {
        ensureDatabaseEnabled();
        String normalizedMobile = normalizeMobile(mobile);
        String from = normalizeDate(dateFrom);
        String to = normalizeDate(dateTo);
        if (!from.isBlank() && !to.isBlank() && !LocalDate.parse(from).isBefore(LocalDate.parse(to))) {
            throw new IllegalArgumentException("dateTo 必须晚于 dateFrom");
        }

        List<String> where = new ArrayList<>();
        where.add("COALESCE(a.delete_status, 0) = 0");
        List<String> params = new ArrayList<>();
        if (!normalizedMobile.isBlank()) {
            where.add("(a.consignee_mobile LIKE ? OR b.mobile LIKE ?)");
            params.add("%" + normalizedMobile + "%");
            params.add("%" + normalizedMobile + "%");
        }
        if (!from.isBlank()) {
            where.add("a.create_time >= ?");
            params.add(from + " 00:00:00");
        }
        if (!to.isBlank()) {
            where.add("a.create_time < ?");
            params.add(to + " 00:00:00");
        }

        String sql = """
            SELECT
              COUNT(DISTINCT a.order_id) AS totalCount,
              COALESCE(SUM(COALESCE(a.actual_total, a.total, a.order_money, 0)), 0) AS totalAmount,
              COALESCE(SUM(CASE WHEN COALESCE(a.is_payed, 0) = 1 THEN COALESCE(a.actual_total, a.total, a.order_money, 0) ELSE 0 END), 0) AS paidAmount,
              COALESCE(SUM(CASE WHEN COALESCE(a.is_payed, 0) = 0 THEN COALESCE(a.actual_total, a.total, a.order_money, 0) ELSE 0 END), 0) AS unpaidAmount,
              SUM(CASE WHEN COALESCE(a.is_payed, 0) = 1 THEN 1 ELSE 0 END) AS paidCount,
              SUM(CASE WHEN COALESCE(a.is_payed, 0) = 0 THEN 1 ELSE 0 END) AS unpaidCount,
              SUM(CASE WHEN a.hb_order_status IN (80, 90, 100) THEN 1 ELSE 0 END) AS completedCount,
              SUM(CASE WHEN a.hb_order_status IN (-1, 50, 60) THEN 1 ELSE 0 END) AS canceledCount,
              MAX(a.create_time) AS latestOrderTime
            FROM smqjh_oms.oms_order a
            LEFT JOIN smqjh_system.sm_member b ON a.member_id = b.id
            WHERE %s
            """.formatted(String.join(" AND ", where));

        try (Connection connection = connect()) {
            prepareReadOnlySession(connection);
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                for (int i = 0; i < params.size(); i++) {
                    statement.setString(i + 1, params.get(i));
                }
                statement.setQueryTimeout(8);
                try (ResultSet rs = statement.executeQuery()) {
                    ObjectNode payload = resultSetPayload(rs, "订单统计查询: smqjh_oms.oms_order + smqjh_system.sm_member");
                    payload.put("mobile", normalizedMobile);
                    payload.put("dateFrom", from);
                    payload.put("dateTo", to);
                    payload.put("evidence", "只读 SELECT；过滤逻辑删除订单 delete_status=0；金额字段 COALESCE(actual_total,total,order_money)");
                    return payload;
                }
            }
        }
    }

    ObjectNode monthlySettlementPreview(String enterpriseInput, String monthInput) throws Exception {
        ensureDatabaseEnabled();
        Enterprise enterprise = findSettlementEnterprise(enterpriseInput);
        if (enterprise == null) {
            throw new IllegalArgumentException("未找到月结企业: " + enterpriseInput + "。当前支持：招商银行贵阳分行、中数未来、铜仁移动。");
        }
        LocalDate start = parseMonth(monthInput);
        LocalDate end = start.plusMonths(1);
        String startTime = start + " 00:00:00";
        String endTime = end + " 00:00:00";

        try (Connection connection = connect()) {
            prepareReadOnlySession(connection);
            ObjectNode orderSummary = queryMonthlyOrderSummary(connection, enterprise, startTime, endTime);
            ObjectNode pointsSummary = queryMonthlyPointsSummary(connection, enterprise, startTime, endTime);
            ObjectNode rechargeSummary = queryMonthlyRechargeSummary(connection, enterprise, startTime, endTime);
            ObjectNode productSummary = queryMonthlyProductSummary(connection, enterprise, startTime, endTime);
            return buildMonthlySettlementPayload(enterprise, monthInput, startTime, endTime, orderSummary, pointsSummary, rechargeSummary, productSummary);
        }
    }

    private ObjectNode queryMonthlyOrderSummary(Connection connection, Enterprise enterprise, String startTime, String endTime) throws SQLException {
        String sql = """
            SELECT
              COUNT(DISTINCT o.order_id) AS paidOrderCount,
              COALESCE(SUM(COALESCE(o.total, 0)), 0) AS productAmount,
              COALESCE(SUM(COALESCE(o.freight_amount, 0)), 0) AS freightAmount,
              COALESCE(SUM(COALESCE(o.offset_points, 0)), 0) AS offsetPoints,
              COALESCE(SUM(COALESCE(o.actual_total, 0)), 0) AS cashAmount,
              COALESCE(SUM(COALESCE(o.total, 0) + COALESCE(o.freight_amount, 0)), 0) AS payableAmount,
              MIN(COALESCE(o.pay_time, o.create_time)) AS firstPaidTime,
              MAX(COALESCE(o.pay_time, o.create_time)) AS lastPaidTime
            FROM smqjh_oms.oms_order o
            WHERE COALESCE(o.delete_status, 0) = 0
              AND COALESCE(o.is_payed, 0) = 1
              AND o.channel_id = ?
              AND COALESCE(o.pay_time, o.create_time) >= ?
              AND COALESCE(o.pay_time, o.create_time) < ?
            """;
        return firstRow(connection, sql, enterprise.channelId(), startTime, endTime);
    }

    private ObjectNode queryMonthlyPointsSummary(Connection connection, Enterprise enterprise, String startTime, String endTime) throws SQLException {
        String sql = """
            SELECT
              COALESCE(SUM(CASE WHEN COALESCE(variable_points, 0) > 0 THEN variable_points ELSE 0 END), 0) AS pointsIncrease,
              COALESCE(SUM(CASE WHEN COALESCE(variable_points, 0) < 0 THEN variable_points ELSE 0 END), 0) AS pointsDecrease,
              COALESCE(SUM(COALESCE(variable_points, 0)), 0) AS pointsNetChange,
              COALESCE(SUM(COALESCE(expiry_points, 0)), 0) AS expiryPoints,
              COUNT(1) AS recordCount
            FROM smqjh_system.sm_points_record
            WHERE channel_id = ?
              AND creation_date >= ?
              AND creation_date < ?
            """;
        return firstRow(connection, sql, enterprise.channelId(), startTime, endTime);
    }

    private ObjectNode queryMonthlyRechargeSummary(Connection connection, Enterprise enterprise, String startTime, String endTime) throws SQLException {
        String sql = """
            SELECT
              COALESCE(SUM(COALESCE(points, 0)), 0) AS rechargePoints,
              COALESCE(SUM(COALESCE(available_points, 0)), 0) AS availablePoints,
              COALESCE(SUM(COALESCE(consume_points, 0)), 0) AS consumePoints,
              COALESCE(SUM(COALESCE(expiry_points, 0)), 0) AS rechargeExpiryPoints,
              COUNT(1) AS rechargeRecordCount
            FROM smqjh_system.sm_points_recharge
            WHERE channel_id = ?
              AND create_time >= ?
              AND create_time < ?
            """;
        return firstRow(connection, sql, enterprise.channelId(), startTime, endTime);
    }

    private ObjectNode queryMonthlyProductSummary(Connection connection, Enterprise enterprise, String startTime, String endTime) throws SQLException {
        String sql = """
            SELECT
              COALESCE(NULLIF(oi.prod_name, ''), p.prod_name) AS productName,
              COALESCE(NULLIF(oi.sku_name, ''), s.sku_name) AS skuName,
              COALESCE(NULLIF(oi.spec, ''), s.spec) AS spec,
              COALESCE(oi.price, 0) AS unitPrice,
              COALESCE(SUM(COALESCE(oi.prod_count, 0)), 0) AS quantity,
              COALESCE(SUM(COALESCE(oi.product_total_amount, COALESCE(oi.price, 0) * COALESCE(oi.prod_count, 0))), 0) AS productTotalAmount,
              COALESCE(SUM(COALESCE(oi.use_score, 0)), 0) AS useScore,
              COALESCE(SUM(COALESCE(oi.gain_score, 0)), 0) AS gainScore,
              GROUP_CONCAT(DISTINCT oi.prod_id ORDER BY oi.prod_id SEPARATOR ',') AS prodIds,
              GROUP_CONCAT(DISTINCT oi.sku_id ORDER BY oi.sku_id SEPARATOR ',') AS skuIds,
              GROUP_CONCAT(DISTINCT s.sku_code ORDER BY s.sku_code SEPARATOR ',') AS skuCodes,
              CASE WHEN MAX(p.prod_id) IS NULL OR MAX(s.sku_id) IS NULL THEN '需人工确认' ELSE '已补齐' END AS confirmStatus
            FROM smqjh_oms.oms_order_item oi
            INNER JOIN smqjh_oms.oms_order o ON o.order_number = oi.order_number
            LEFT JOIN smqjh_pms.tz_prod p ON p.prod_id = oi.prod_id AND COALESCE(p.is_delete, 0) = 0
            LEFT JOIN smqjh_pms.tz_sku s ON s.sku_id = oi.sku_id AND COALESCE(s.is_delete, 0) = 0
            WHERE COALESCE(o.delete_status, 0) = 0
              AND COALESCE(o.is_payed, 0) = 1
              AND o.channel_id = ?
              AND COALESCE(o.pay_time, o.create_time) >= ?
              AND COALESCE(o.pay_time, o.create_time) < ?
            GROUP BY
              COALESCE(NULLIF(oi.prod_name, ''), p.prod_name),
              COALESCE(NULLIF(oi.sku_name, ''), s.sku_name),
              COALESCE(NULLIF(oi.spec, ''), s.spec),
              COALESCE(oi.price, 0)
            ORDER BY productTotalAmount DESC
            LIMIT 200
            """;
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setLong(1, enterprise.channelId());
            statement.setString(2, startTime);
            statement.setString(3, endTime);
            statement.setQueryTimeout(8);
            try (ResultSet rs = statement.executeQuery()) {
                ObjectNode payload = resultSetPayload(rs, "SELECT monthly product summary FROM smqjh_oms.oms_order_item + smqjh_oms.oms_order + smqjh_pms.tz_prod/tz_sku");
                payload.put("title", enterprise.name() + " 商品明细汇总");
                payload.put("summary", "按已付款、未逻辑删除订单聚合商品数量、商品总额、积分字段，并尝试补齐商品/SKU 编码。");
                return payload;
            }
        }
    }

    private ObjectNode firstRow(Connection connection, String sql, Object... params) throws SQLException {
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) {
                Object value = params[i];
                if (value instanceof Number number) {
                    statement.setLong(i + 1, number.longValue());
                } else {
                    statement.setString(i + 1, String.valueOf(value));
                }
            }
            statement.setQueryTimeout(8);
            try (ResultSet rs = statement.executeQuery()) {
                if (rs.next()) {
                    ObjectNode row = Jsons.object();
                    ResultSetMetaData meta = rs.getMetaData();
                    for (int i = 1; i <= meta.getColumnCount(); i++) {
                        Object value = rs.getObject(i);
                        if (value == null) {
                            row.putNull(meta.getColumnLabel(i));
                        } else {
                            row.put(meta.getColumnLabel(i), String.valueOf(value));
                        }
                    }
                    return row;
                }
            }
        }
        return Jsons.object();
    }

    private ObjectNode buildMonthlySettlementPayload(
        Enterprise enterprise,
        String month,
        String startTime,
        String endTime,
        ObjectNode orderSummary,
        ObjectNode pointsSummary,
        ObjectNode rechargeSummary,
        ObjectNode productSummary
    ) {
        BigDecimal productAmount = decimal(orderSummary, "productAmount");
        BigDecimal freightAmount = decimal(orderSummary, "freightAmount");
        BigDecimal offsetPoints = decimal(orderSummary, "offsetPoints");
        BigDecimal cashAmount = decimal(orderSummary, "cashAmount");
        BigDecimal payableAmount = decimal(orderSummary, "payableAmount");
        BigDecimal settlementAmount = cashAmount.add(offsetPoints);
        BigDecimal diffAmount = payableAmount.subtract(settlementAmount);
        boolean needConfirm = diffAmount.abs().compareTo(new BigDecimal("0.01")) > 0 || hasManualConfirmRows(productSummary);

        ObjectNode root = Jsons.object();
        root.put("ok", true);
        root.put("title", enterprise.name() + " " + month + " 月结预览");
        root.put("enterpriseName", enterprise.name());
        root.put("channelId", enterprise.channelId());
        root.put("channelNo", enterprise.channelNo());
        root.put("month", month);
        root.put("startTime", startTime);
        root.put("endTime", endTime);
        root.put("manualConfirmStatus", needConfirm ? "需人工确认" : "核对通过");
        root.put("summary", "已按企业渠道、付款时间、已付款订单和逻辑删除过滤生成只读预览；结果不会写入业务系统。");
        root.put("evidence", "MySQL只读 SELECT: smqjh_oms.oms_order / oms_order_item, smqjh_system.sm_points_record / sm_points_recharge, smqjh_pms.tz_prod / tz_sku");
        root.set("columns", Jsons.MAPPER.valueToTree(new String[] {"项目", "数值", "口径", "状态", "依据"}));

        ArrayNode rows = Jsons.MAPPER.createArrayNode();
        rows.add(summaryRow("企业", enterprise.name(), "sm_channel 月结企业配置", "已确认", "channelId=" + enterprise.channelId() + ", channelNo=" + enterprise.channelNo()));
        rows.add(summaryRow("结算月份", month, "管理员选择月份", "已确认", startTime + " 至 " + endTime));
        rows.add(summaryRow("已付款订单数", text(orderSummary, "paidOrderCount"), "is_payed=1 且 delete_status=0", "已统计", "smqjh_oms.oms_order"));
        rows.add(summaryRow("商品总额", money(productAmount), "SUM(oms_order.total)", "已统计", "订单表商品总额字段"));
        rows.add(summaryRow("总运费", money(freightAmount), "SUM(oms_order.freight_amount)", "已统计", "订单表运费字段"));
        rows.add(summaryRow("积分抵扣金额", money(offsetPoints.negate()), "SUM(oms_order.offset_points) 按结算表负数展示", "已统计", "负数表示充值转换为积分后的抵扣金额"));
        rows.add(summaryRow("现金支付金额", money(cashAmount), "SUM(oms_order.actual_total)", "已统计", "订单实付金额字段"));
        rows.add(summaryRow("商品总额+运费", money(payableAmount), "商品总额 + 总运费", "已计算", "用于对账汇总小蓝表"));
        rows.add(summaryRow("积分抵扣+现金", money(settlementAmount), "offset_points + actual_total", "已计算", "用于核对商品总额+运费"));
        rows.add(summaryRow("核对差异", money(diffAmount), "商品总额+运费 - (积分抵扣+现金)", needConfirm ? "需人工确认" : "核对通过", "差异不为 0 或商品/SKU 缺失时人工确认"));
        rows.add(summaryRow("本月充值积分", text(rechargeSummary, "rechargePoints"), "SUM(sm_points_recharge.points)", "已统计", "sm_points_recharge"));
        rows.add(summaryRow("本月可用积分", text(rechargeSummary, "availablePoints"), "SUM(sm_points_recharge.available_points)", "已统计", "sm_points_recharge"));
        rows.add(summaryRow("本月已消费积分", text(rechargeSummary, "consumePoints"), "SUM(sm_points_recharge.consume_points)", "已统计", "sm_points_recharge"));
        rows.add(summaryRow("积分流水净变化", text(pointsSummary, "pointsNetChange"), "SUM(sm_points_record.variable_points)", "已统计", "sm_points_record"));
        root.set("rows", rows);
        root.put("rowCount", rows.size());

        root.set("orderSummary", orderSummary);
        root.set("pointsSummary", pointsSummary);
        root.set("rechargeSummary", rechargeSummary);
        root.set("productSummary", productSummary);
        root.set("comparisonRows", productSummary.path("rows"));
        root.set("comparisonColumns", productSummary.path("columns"));
        root.put("comparisonTitle", "商品汇总表");
        root.put("generatedFileHint", "前端表格区域可直接下载 CSV；后续会接入 Excel 模板生成完整月结报告。");
        return root;
    }

    private ObjectNode summaryRow(String item, String value, String rule, String status, String evidence) {
        ObjectNode row = Jsons.object();
        row.put("项目", item);
        row.put("数值", value);
        row.put("口径", rule);
        row.put("状态", status);
        row.put("依据", evidence);
        return row;
    }

    private BigDecimal decimal(ObjectNode node, String key) {
        String value = text(node, key);
        if (value.isBlank()) {
            return BigDecimal.ZERO;
        }
        try {
            return new BigDecimal(value);
        } catch (NumberFormatException ignored) {
            return BigDecimal.ZERO;
        }
    }

    private String money(BigDecimal value) {
        return "￥" + value.setScale(2, RoundingMode.HALF_UP).toPlainString();
    }

    private String text(ObjectNode node, String key) {
        if (node == null || !node.hasNonNull(key)) {
            return "";
        }
        return node.path(key).asText("");
    }

    private boolean hasManualConfirmRows(ObjectNode productSummary) {
        if (productSummary == null || !productSummary.path("rows").isArray()) {
            return false;
        }
        for (var row : productSummary.path("rows")) {
            if (row.path("confirmStatus").asText("").contains("需人工确认")) {
                return true;
            }
        }
        return false;
    }

    private Enterprise findSettlementEnterprise(String input) {
        String normalized = input == null ? "" : input.trim().toLowerCase(Locale.ROOT);
        if (normalized.isBlank() || "all".equals(normalized) || "全部".equals(normalized)) {
            return null;
        }
        for (Enterprise enterprise : settlementEnterprises()) {
            if (enterprise.name().toLowerCase(Locale.ROOT).contains(normalized)
                || normalized.contains(enterprise.name().toLowerCase(Locale.ROOT))
                || enterprise.channelNo().equals(normalized)
                || String.valueOf(enterprise.channelId()).equals(normalized)) {
                return enterprise;
            }
        }
        return null;
    }

    private List<Enterprise> settlementEnterprises() {
        return List.of(
            new Enterprise(54, "1", "招商银行贵阳分行"),
            new Enterprise(55, "2", "中数未来"),
            new Enterprise(58, "5", "铜仁移动")
        );
    }

    private LocalDate parseMonth(String month) {
        if (month == null || !month.matches("\\d{4}-\\d{2}")) {
            throw new IllegalArgumentException("month 必须是 YYYY-MM");
        }
        return LocalDate.parse(month + "-01", DateTimeFormatter.ISO_LOCAL_DATE);
    }

    private record Enterprise(long channelId, String channelNo, String name) {
    }

    private Connection connect() throws SQLException {
        String url = "jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true"
            .formatted(config.database.host, config.database.port, config.database.database);
        return DriverManager.getConnection(url, config.database.username, config.database.password);
    }

    private void prepareReadOnlySession(Connection connection) throws SQLException {
        connection.setReadOnly(true);
        try (Statement statement = connection.createStatement()) {
            statement.execute("SET SESSION TRANSACTION READ ONLY");
        } catch (SQLException ignored) {
            // Some MySQL modes do not allow changing transaction mode outside a transaction.
        }
        try (Statement statement = connection.createStatement()) {
            statement.execute("SET SESSION MAX_EXECUTION_TIME=8000");
        } catch (SQLException ignored) {
            // MAX_EXECUTION_TIME is MySQL-specific. Query timeout still applies.
        }
    }

    private void ensureDatabaseEnabled() {
        if (!config.database.enabled) {
            throw new IllegalStateException("数据库只读查询未启用");
        }
        if (blank(config.database.host) || blank(config.database.username) || blank(config.database.database)) {
            throw new IllegalStateException("数据库连接信息不完整");
        }
    }

    private ObjectNode resultSetPayload(ResultSet resultSet, String executedSql) throws SQLException {
        ResultSetMetaData meta = resultSet.getMetaData();
        ArrayNode columns = Jsons.MAPPER.createArrayNode();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            columns.add(meta.getColumnLabel(i));
        }
        ArrayNode rows = Jsons.MAPPER.createArrayNode();
        while (resultSet.next()) {
            ObjectNode row = Jsons.object();
            for (int i = 1; i <= meta.getColumnCount(); i++) {
                Object value = resultSet.getObject(i);
                if (value == null) {
                    row.putNull(meta.getColumnLabel(i));
                } else {
                    row.put(meta.getColumnLabel(i), String.valueOf(value));
                }
            }
            rows.add(row);
        }
        ObjectNode payload = Jsons.object();
        payload.put("ok", true);
        payload.put("executedSql", executedSql);
        payload.put("rowCount", rows.size());
        payload.set("columns", columns);
        payload.set("rows", rows);
        return payload;
    }

    private void enrichProductRows(ObjectNode payload) {
        ArrayNode rows = (ArrayNode) payload.path("rows");
        for (int i = 0; i < rows.size(); i++) {
            ObjectNode row = (ObjectNode) rows.get(i);
            String price = firstText(row, "sku_price", "sku_ori_price", "prod_price", "ori_price");
            row.put("displayPrice", formatCentMoney(price));
            row.put("displayName", joinNonBlank(row.path("prod_name").asText(""), row.path("sku_name").asText("")));
            row.put("source", "MySQL只读: smqjh_pms.tz_prod/tz_sku");
        }
    }

    private String normalizeSql(String sql) {
        return sql == null ? "" : sql.replace("```sql", "").replace("```", "").trim().replaceAll(";+\\s*$", "");
    }

    private void assertReadOnlySql(String sql) {
        String normalized = sql.trim().toLowerCase(Locale.ROOT);
        if (!normalized.startsWith("select")) {
            throw new IllegalArgumentException("数据库只读通道仅允许 SELECT");
        }
        if (normalized.contains(";")) {
            throw new IllegalArgumentException("数据库只读通道仅允许单条 SELECT");
        }
        if (normalized.contains("--") || normalized.contains("/*") || normalized.contains("*/")) {
            throw new IllegalArgumentException("数据库只读通道拒绝 SQL 注释");
        }
        if (Pattern.compile("(^|[\\s;])(insert|update|delete|drop|alter|truncate|create|replace|grant|revoke|call|set)\\b", Pattern.CASE_INSENSITIVE).matcher(normalized).find()) {
            throw new IllegalArgumentException("数据库只读通道拒绝写入或结构变更语句");
        }
    }

    private void assertAllowedTables(String sql) {
        Matcher matcher = TABLE_PATTERN.matcher(sql);
        List<String> referenced = new ArrayList<>();
        while (matcher.find()) {
            referenced.add(matcher.group(1).replace("`", "").toLowerCase(Locale.ROOT));
        }
        if (referenced.isEmpty()) {
            throw new IllegalArgumentException("SQL 必须包含 FROM 或 JOIN 表");
        }
        List<String> rejected = referenced.stream().filter(table -> !ALLOWED_TABLES.contains(table)).toList();
        if (!rejected.isEmpty()) {
            throw new IllegalArgumentException("拒绝访问未授权表: " + String.join(", ", rejected));
        }
        String lower = sql.toLowerCase(Locale.ROOT);
        if (lower.contains("information_schema.") || lower.contains("performance_schema.") || lower.contains("mysql.") || lower.contains("sys.")) {
            throw new IllegalArgumentException("拒绝访问系统库");
        }
    }

    private String withDefaultLimit(String sql) {
        if (Pattern.compile("\\blimit\\s+\\d+", Pattern.CASE_INSENSITIVE).matcher(sql).find()) {
            return sql;
        }
        return sql + " LIMIT 100";
    }

    private List<String> productKeywordCandidates(String keyword) {
        List<String> candidates = new ArrayList<>();
        addCandidate(candidates, keyword);
        addCandidate(candidates, keyword.replaceAll("\\s+", ""));
        String strippedPackage = keyword
            .replaceAll("\\d+(?:\\.\\d+)?\\s*(?:ml|mL|ML|毫升|g|克|kg|KG|L|l|升)(?:\\s*[xX*×]\\s*\\d+\\s*(?:瓶|罐|箱|盒|包|袋|件|份|支|个)?)?", " ")
            .replaceAll("\\s+", " ")
            .trim();
        addCandidate(candidates, strippedPackage);
        return candidates;
    }

    private void addCandidate(List<String> candidates, String value) {
        if (value != null && !value.isBlank() && !candidates.contains(value.trim())) {
            candidates.add(value.trim());
        }
    }

    private String normalizeProductKeyword(String keyword) {
        return (keyword == null ? "" : keyword)
            .replaceAll("(帮我|麻烦|查询一下|查询|查看|当前|业务系统|系统里面|系统里|后台|我方|我们的|商品库|商品表|商品|描述是什么|商品描述|描述|价格是多少|价格|定价|是多少|是什么|呢)", " ")
            .replaceAll("[，。！？、；：,.!?;:\\n\\r]", " ")
            .replaceAll("\\s+", " ")
            .trim();
    }

    private String normalizeMobile(String value) {
        if (value == null) {
            return "";
        }
        Matcher matcher = Pattern.compile("1\\d{10}").matcher(value);
        return matcher.find() ? matcher.group() : "";
    }

    private String normalizeDate(String value) {
        if (value == null) {
            return "";
        }
        Matcher matcher = Pattern.compile("\\d{4}-\\d{2}-\\d{2}").matcher(value);
        return matcher.find() ? matcher.group() : "";
    }

    private String firstText(ObjectNode row, String... keys) {
        for (String key : keys) {
            String value = row.path(key).asText("");
            if (!value.isBlank() && !"null".equalsIgnoreCase(value)) {
                return value;
            }
        }
        return "";
    }

    private String formatCentMoney(String value) {
        if (value == null || value.isBlank()) {
            return "未获取";
        }
        try {
            double number = Double.parseDouble(value);
            return "¥" + String.format(Locale.ROOT, "%.2f", number / 100.0);
        } catch (NumberFormatException ignored) {
            return value;
        }
    }

    private String joinNonBlank(String first, String second) {
        if (blank(first)) {
            return second;
        }
        if (blank(second) || first.equals(second)) {
            return first;
        }
        return first + " " + second;
    }

    private boolean blank(String value) {
        return value == null || value.isBlank();
    }
}
