package com.common.service.impl;

import com.common.entity.AnalysisAnalysisRule;
import com.common.entity.AnalysisField;
import com.common.entity.AnalysisFilter;
import com.common.entity.AnalysisGroupByColumn;
import com.common.entity.AnalysisGroupByHaving;
import com.common.entity.AnalysisGroupByWindow;
import com.common.entity.AnalysisWhereCondition;
import com.common.service.SqlGeneratorService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import com.common.util.JsonbUtil;
import java.util.ArrayList;
import java.util.List;

/**
 * SQL生成服务实现
 */
@Slf4j
@Service
public class SqlGeneratorServiceImpl implements SqlGeneratorService {

    private static final String DEFAULT_TABLE_NAME = "syslog_normal_alarm";

    @Override
    public String generateSql(AnalysisAnalysisRule rule,
                              List<AnalysisField> fields,
                              List<AnalysisWhereCondition> whereConditions,
                              List<AnalysisFilter> filters,
                              List<AnalysisGroupByColumn> groupByColumns,
                              List<AnalysisGroupByHaving> havingConditions,
                              AnalysisGroupByWindow groupByWindow,
                              String startTime,
                              String endTime) {
        String selectClause = generateSelectClause(fields, groupByWindow);
        String fromClause = generateFromClause(fields, groupByWindow);
        String whereClause = generateWhereClauseFromFilters(filters, startTime, endTime);
        String groupByClause = generateGroupByClause(groupByColumns, groupByWindow);
        String havingClause = generateHavingClauseFromConditions(havingConditions);

        return buildFullSql(selectClause, fromClause, whereClause, groupByClause, havingClause);
    }
    @Override
    public String generateSelectClause(List<AnalysisField> fields, AnalysisGroupByWindow groupByWindow) {
        if (fields == null || fields.isEmpty()) {
            return "SELECT *";
        }

        StringBuilder selectBuilder = new StringBuilder("SELECT ");

        List<String> selectFields = new ArrayList<>();
        for (AnalysisField field : fields) {
            // 根据type字段判断是否用于SELECT（measure或calc类型）
            String type = field.getType();
            //if ("measure".equals(type) || "calc".equals(type) || StringUtils.isBlank(type)) {
            String columnExpr = buildColumnExpression(field);
                // 使用alarm_column_name作为别名
            String alias = StringUtils.isNotBlank(field.getAlarmColumnName()) ? field.getAlarmColumnName() : field.getColumnName();
            selectFields.add(columnExpr + " AS " + alias);
            //selectFields.add(columnExpr );
            //}
        }
        // 如果有时间窗口，添加时间列
        if (groupByWindow != null && StringUtils.isNotBlank(groupByWindow.getWindowType())) {
            String timeColumn = buildTimeWindowExpression(groupByWindow);
            if (StringUtils.isNotBlank(timeColumn)) {
                selectFields.add(timeColumn);
            }
        }

        /**
        //补充log_start_at、log_start_at 字段
        selectFields.add("MIN(log_time)  AS " + "log_start_at");
        selectFields.add("MAX(log_time)  AS  " + "log_end_at");
        //默认补充device_id、origin_log_ids字段
        selectFields.add("ARRAY_AGG(DISTINCT device_id) AS  " + "device_id");
        selectFields.add("ARRAY_AGG(DISTINCT id) AS " + "origin_log_ids");
        **/

        if (selectFields.isEmpty()) {
            return "SELECT *";
        }

        selectBuilder.append(String.join(",\n", selectFields));
        return selectBuilder.toString();
    }

    @Override
    public String generateFromClause(List<AnalysisField> fields, AnalysisGroupByWindow groupByWindow) {
        String tableName = DEFAULT_TABLE_NAME;
        String tableAlias = "t";

        if (fields != null && !fields.isEmpty()) {
            for (AnalysisField field : fields) {
                if (StringUtils.isNotBlank(field.getTableName())) {
                    tableName = field.getTableName();
                }
                if (StringUtils.isNotBlank(field.getTableAlias())) {
                    tableAlias = field.getTableAlias();
                    break;
                }
            }
        }
        return "FROM " + tableName + " AS " + tableAlias;
    }

    @Override
    public String generateWhereClauseFromFilters(List<AnalysisFilter> filters,
                                              String startTime,
                                              String endTime) {
        List<String> whereParts = new ArrayList<>();

        // 添加时间范围条件
        if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
            whereParts.add("log_time >= '" + startTime + "' AND log_time < '" + endTime + "'");
        }

        // 添加配置的WHERE条件
        if (filters != null && !filters.isEmpty()) {
            for (AnalysisFilter filter : filters) {
                String conditionExpr = buildFilterExpression(filter);
                if (StringUtils.isNotBlank(conditionExpr)) {
                    whereParts.add(conditionExpr);
                }
            }
        }

        if (whereParts.isEmpty()) {
            return "";
        }

        return "WHERE " + String.join(" AND ", whereParts);
    }

    @Override
    @Deprecated
    public String generateWhereClause(List<AnalysisWhereCondition> conditions,
                                       String startTime,
                                       String endTime) {
        List<String> whereParts = new ArrayList<>();

        // 添加时间范围条件
        if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
            whereParts.add("log_time >= '" + startTime + "' AND log_time < '" + endTime + "'");
        }

        // 添加配置的WHERE条件
        if (conditions != null && !conditions.isEmpty()) {
            String conditionSql = buildWhereConditions(conditions);
            if (StringUtils.isNotBlank(conditionSql)) {
                whereParts.add("(" + conditionSql + ")");
            }
        }

        if (whereParts.isEmpty()) {
            return "";
        }

        return "WHERE " + String.join(" AND ", whereParts);
    }


    /**
     * 构建过滤条件表达式
     */
    private String buildFilterExpression(AnalysisFilter filter) {
        if (filter == null) {
            return "";
        }

        String columnExpr = filter.getColumnName();
        if (StringUtils.isBlank(columnExpr)) {
            log.warn("过滤条件不完整: columnName={}", filter.getColumnName());
            return "";
        }

        // 如果有函数，应用函数
        if (StringUtils.isNotBlank(filter.getFn())) {
            columnExpr = buildColumnExpression(filter);
        }

        String operator = filter.getOperator();
        // 使用JsonbUtil解析JSONB值
        String valueStr = JsonbUtil.parseString(filter.getValue());

        if (StringUtils.isBlank(operator)) {
            log.warn("过滤条件不完整: operator={}", operator);
            return "";
        }

        switch (operator.toUpperCase()) {
            case "=":
                return columnExpr + " = '" + valueStr + "'";
            case "!=":
                return columnExpr + " != '" + valueStr + "'";
            case ">":
                return columnExpr + " > " + wrapValue(valueStr);
            case "<":
                return columnExpr + " < " + wrapValue(valueStr);
            case ">=":
                return columnExpr + " >= " + wrapValue(valueStr);
            case "<=":
                return columnExpr + " <= " + wrapValue(valueStr);
            case "LIKE":
            case "like":
                return columnExpr + " LIKE '%" + valueStr + "%'";
            case "NOTLIKE":
            case "notLike":
                return columnExpr + " NOT LIKE '%" + valueStr + "%'";
            case "LIKESTART":
            case "likeStart":
                return columnExpr + " LIKE '" + valueStr + "%'";
            case "NOTLIKESTART":
            case "notLikeStart":
                return columnExpr + " NOT LIKE '" + valueStr + "%'";
            case "LIKEEND":
            case "likeEnd":
                return columnExpr + " LIKE '%" + valueStr + "'";
            case "NOTLIKEEND":
            case "notLikeEnd":
                return columnExpr + " NOT LIKE '%" + valueStr + "'";
            case "IN":
            case "in":
                return columnExpr + " IN (" + parseJsonArray(valueStr) + ")";
            case "NOTIN":
            case "notIn":
                return columnExpr + " NOT IN (" + parseJsonArray(valueStr) + ")";
            case "BETWEEN":
            case "between":
                String[] values = valueStr.split(",");
                if (values.length == 2) {
                    return columnExpr + " BETWEEN '" + values[0].trim() + "' AND '" + values[1].trim() + "'";
                }
                return columnExpr + " BETWEEN " + valueStr;
            case "ISNOTEMPTY":
            case "isNotEmpty":
                return columnExpr + " IS NOT NULL AND " + columnExpr + " != ''";
            case "ISEMPTY":
            case "isEmpty":
                return columnExpr + " IS NULL OR " + columnExpr + " = ''";
            case "REGEXP_STR":
            case "regexp_str":
                return columnExpr + " ~ '" + valueStr + "'";
            case "IPCONTAINS":
            case "ipContains":
                return "iprange_contains(" + columnExpr + ", '" + valueStr + "')";
            case "IPNOTCONTAINS":
            case "ipNotContains":
                return "NOT iprange_contains(" + columnExpr + ", '" + valueStr + "')";
            case "IPRANGE":
            case "ipRange":
                String[] ipRange = valueStr.split(",");
                if (ipRange.length == 2) {
                    return columnExpr + " >= '" + ipRange[0].trim() + "' AND " + columnExpr + " <= '" + ipRange[1].trim() + "'";
                }
                return columnExpr + " BETWEEN " + valueStr;
            case "IPNOTINRANGE":
            case "ipNotInRange":
                String[] ipNotRange = valueStr.split(",");
                if (ipNotRange.length == 2) {
                    return "(" + columnExpr + " < '" + ipNotRange[0].trim() + "' OR " + columnExpr + " > '" + ipNotRange[1].trim() + "')";
                }
                return "NOT (" + columnExpr + " BETWEEN " + valueStr + ")";
            case "INGSAFEDOMAIN":
            case "inSafeDomain":
                return "is_in_safe_domain(" + columnExpr + ", '" + valueStr + "')";
            case "NOTINGSAFEDOMAIN":
            case "notInSafeDomain":
                return "NOT is_in_safe_domain(" + columnExpr + ", '" + valueStr + "')";
            case "INBUSINESSDOMAIN":
            case "inBusinessDomain":
                return "is_in_business_domain(" + columnExpr + ", '" + valueStr + "')";
            case "NOTINBUSINESSDOMAIN":
            case "notInBusinessDomain":
                return "NOT is_in_business_domain(" + columnExpr + ", '" + valueStr + "')";
            case "ISINTRANET":
            case "isIntranet":
                return "is_intranet(" + columnExpr + ")";
            case "ISNOTINTRANET":
            case "isNotIntranet":
                return "NOT is_intranet(" + columnExpr + ")";
            default:
                return columnExpr + " " + operator + " " + wrapValue(valueStr);
        }
    }

    @Override
    public String generateGroupByClause(List<AnalysisGroupByColumn> columns, AnalysisGroupByWindow groupByWindow) {
        List<String> groupByFields = new ArrayList<>();

        // 添加分组字段
        if (columns != null && !columns.isEmpty()) {
            for (AnalysisGroupByColumn column : columns) {
                // 构建带表别名的字段表达式
                String columnExpr = buildColumnExpression(column);
                groupByFields.add(columnExpr);
            }
        }

        // 添加时间窗口字段
        if (groupByWindow != null && StringUtils.isNotBlank(groupByWindow.getWindowType())) {
            String timeWindowExpr = buildTimeWindowGroupExpression(groupByWindow);
            if (StringUtils.isNotBlank(timeWindowExpr)) {
                groupByFields.add(timeWindowExpr);
            }
        }

        if (groupByFields.isEmpty()) {
            return "";
        }

        return "GROUP BY " + String.join(", ", groupByFields);
    }

    /**
     * 构建字段表达式，支持表别名
     *
     * @param column 分组字段配置
     * @return 字段表达式
     */
    private String buildColumnExpression(AnalysisGroupByColumn column) {
        //String tableName = column.getTableName();
        String tableName = "";
        //String tableAlias = column.getTableAlias();
        String tableAlias ="";
        String columnName = column.getColumnName();

        // 如果有表别名，使用表别名，否则使用表名
        String qualifiedName = "";
        if (StringUtils.isNotBlank(tableAlias)) {
            qualifiedName = tableAlias + "." + columnSafeWrap(columnName);
        } else if (StringUtils.isNotBlank(tableName)) {
            qualifiedName = tableName + "." + columnSafeWrap(columnName);
        } else {
            //qualifiedName = columnSafeWrap(columnName);
            qualifiedName = columnName;
        }

        return qualifiedName;
    }

    @Override
    public String generateHavingClauseFromConditions(List<AnalysisGroupByHaving> havingConditions) {
        if (havingConditions == null || havingConditions.isEmpty()) {
            return "";
        }

        List<String> havingParts = new ArrayList<>();
        for (AnalysisGroupByHaving condition : havingConditions) {
            String conditionExpr = buildHavingExpression(condition);
            if (StringUtils.isNotBlank(conditionExpr)) {
                havingParts.add(conditionExpr);
            }
        }

        if (havingParts.isEmpty()) {
            return "";
        }

        return "HAVING " + String.join(" AND ", havingParts);
    }

    @Override
    @Deprecated
    public String generateHavingClause(AnalysisAnalysisRule rule) {
        // 从rule_content或rule_expression中解析HAVING条件
        // 这里简化处理，实际需要解析JSON配置
        if (StringUtils.isNotBlank(rule.getRuleExpression())) {
            return "HAVING " + rule.getRuleExpression();
        }
        return "";
    }
    @Override
    public String generateWindowClause(AnalysisGroupByWindow groupByWindow) {
        if (groupByWindow == null || StringUtils.isBlank(groupByWindow.getWindowType())) {
            return "";
        }

        String windowType = groupByWindow.getWindowType().toUpperCase();

        switch (windowType) {
            case "TUMBLE":
                return buildTumbleWindowExpression(groupByWindow);
            case "HOP":
                return buildHopWindowExpression(groupByWindow);
            case "SESSION":
                return buildSessionWindowExpression(groupByWindow);
            default:
                log.warn("不支持的窗口类型: {}", windowType);
                return "";
        }
    }

    /**
     * 构建时间窗口表达式（用于SELECT）
     * 改为protected以便子类复用（达梦安全版子查询包装模式）
     */
    protected String buildTimeWindowExpression(AnalysisGroupByWindow groupByWindow) {
        if (groupByWindow == null) {
            return "";
        }

        String windowType = groupByWindow.getWindowType().toUpperCase();

        switch (windowType) {
            case "TUMBLE":
                return buildTumbleTimeExpression(groupByWindow);
            case "HOP":
                return buildHopTimeExpression(groupByWindow);
            case "SESSION":
                return buildSessionTimeExpression(groupByWindow);
            default:
                return "";
        }
    }

    /**
     * 构建时间窗口GROUP BY表达式
     */
    private String buildTimeWindowGroupExpression(AnalysisGroupByWindow groupByWindow) {
        if (groupByWindow == null) {
            return "";
        }

        String windowType = groupByWindow.getWindowType().toUpperCase();

        switch (windowType) {
            case "TUMBLE":
                return buildTumbleWindowGroupExpression(groupByWindow);
            case "HOP":
                return buildHopWindowGroupExpression(groupByWindow);
            case "SESSION":
                return buildSessionWindowGroupExpression(groupByWindow);
            default:
                return "";
        }
    }

    /**
     * 构建滚动窗口表达式
     */
    private String buildTumbleWindowExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getTumbleWindowSizeUnit());
        Integer size = window.getTumbleWindowSize();

        return String.format("TUMBLE(%s, INTERVAL '%d' %s) AS window_start", timeCol, size, sizeUnit);
    }

    /**
     * 构建滚动窗口时间表达式（用于SELECT）
     */
    private String buildTumbleTimeExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getTumbleWindowSizeUnit());
        Integer size = window.getTumbleWindowSize();
        //return String.format("TUMBLE_START(%s, INTERVAL '%d %s') AS window_time", timeCol, size, sizeUnit);
        return String.format("TUMBLE(%s, INTERVAL '%d' %s) AS window_time", timeCol, size, sizeUnit);
    }

    /**
     * 构建滚动窗口GROUP BY表达式
     */
    private String buildTumbleWindowGroupExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getTumbleWindowSizeUnit());
        Integer size = window.getTumbleWindowSize();

        return String.format("TUMBLE(%s, INTERVAL '%d' %s)", timeCol, size, sizeUnit);
    }

    /**
     * 构建滑动窗口表达式
     */
    private String buildHopWindowExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getHopWindowSizeUnit());
        String slideUnit = convertTimeUnit(window.getHopWindowSlideUnit());
        Integer size = window.getHopWindowSize();
        Integer slide = window.getHopWindowSlide();

        return String.format("HOP(%s, INTERVAL '%d' %s, INTERVAL '%d' %s) AS window_start",
                timeCol, size, sizeUnit, slide, slideUnit);
    }

    /**
     * 构建滑动窗口时间表达式（用于SELECT）
     */
    private String buildHopTimeExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getHopWindowSizeUnit());
        String slideUnit = convertTimeUnit(window.getHopWindowSlideUnit());
        Integer size = window.getHopWindowSize();
        Integer slide = window.getHopWindowSlide();

        //return String.format("HOP_START(%s, INTERVAL '%d %s', INTERVAL '%d %s') AS window_time",
          //      timeCol, size, sizeUnit, slide, slideUnit);
        return String.format("HOP(%s, INTERVAL '%d' %s, INTERVAL '%d' %s) AS window_time",
                timeCol, size, sizeUnit, slide, slideUnit);
    }

    /**
     * 构建滑动窗口GROUP BY表达式
     */
    private String buildHopWindowGroupExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getHopWindowSizeUnit());
        String slideUnit = convertTimeUnit(window.getHopWindowSlideUnit());
        Integer size = window.getHopWindowSize();
        Integer slide = window.getHopWindowSlide();

        return String.format("HOP(%s, INTERVAL '%d' %s, INTERVAL '%d' %s)",
                timeCol, size, sizeUnit, slide, slideUnit);
    }

    /**
     * 构建会话窗口表达式
     */
    private String buildSessionWindowExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getSessionWindowSizeUnit());
        Integer size = window.getSessionWindowSize();

        return String.format("SESSION(%s, INTERVAL '%d' %s) AS window_start", timeCol, size, sizeUnit);
    }

    /**
     * 构建会话窗口时间表达式（用于SELECT）
     */
    private String buildSessionTimeExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getSessionWindowSizeUnit());
        Integer size = window.getSessionWindowSize();

        //return String.format("SESSION_START(%s, INTERVAL '%d %s') AS window_time", timeCol, size, sizeUnit);
        return String.format("SESSION(%s, INTERVAL '%d' %s) AS window_time", timeCol, size, sizeUnit);
    }

    /**
     * 构建会话窗口GROUP BY表达式
     */
    private String buildSessionWindowGroupExpression(AnalysisGroupByWindow window) {
        String timeCol = "log_time";
        String sizeUnit = convertTimeUnit(window.getSessionWindowSizeUnit());
        Integer size = window.getSessionWindowSize();

        return String.format("SESSION(%s, INTERVAL '%d' %s)", timeCol, size, sizeUnit);
    }

    /**
     * 转换时间单位
     */
    private String convertTimeUnit(String unit) {
        if (StringUtils.isBlank(unit)) {
            return "SECOND";
        }
        switch (unit.toLowerCase()) {
            case "s":
            case "second":
            case "seconds":
                return "SECOND";
            case "m":
            case "minute":
            case "minutes":
                return "MINUTE";
            case "h":
            case "hour":
            case "hours":
                return "HOUR";
            case "d":
            case "day":
            case "days":
                return "DAY";
            default:
                return unit.toUpperCase();
        }
    }
    /**
     * 构建HAVING条件表达式
     */
    private String buildHavingExpression(AnalysisGroupByHaving condition) {
        if (condition == null) {
            return "";
        }

        String columnExpr = condition.getColumnName();
        if (StringUtils.isBlank(columnExpr)) {
            log.warn("HAVING条件不完整: columnName={}", condition.getColumnName());
            return "";
        }

        // 如果有聚合函数，应用函数
        if (StringUtils.isNotBlank(condition.getFn())) {
            columnExpr = buildColumnExpression(condition);
        }

        String operator = condition.getOperator();
        // 使用JsonbUtil解析JSONB值
        String valueStr = JsonbUtil.parseString(condition.getValue());

        if (StringUtils.isBlank(operator)) {
            log.warn("HAVING条件不完整: operator={}", operator);
            return "";
        }

        switch (operator.toUpperCase()) {
            case "=":
                return columnExpr + " = '" + valueStr + "'";
            case "!=":
                return columnExpr + " != '" + valueStr + "'";
            case ">":
                return columnExpr + " > " + wrapValue(valueStr);
            case "<":
                return columnExpr + " < " + wrapValue(valueStr);
            case ">=":
                return columnExpr + " >= " + wrapValue(valueStr);
            case "<=":
                return columnExpr + " <= " + wrapValue(valueStr);
            case "LIKE":
            case "like":
                return columnExpr + " LIKE '%" + valueStr + "%'";
            case "NOTLIKE":
            case "notLike":
                return columnExpr + " NOT LIKE '%" + valueStr + "%'";
            case "LIKESTART":
            case "likeStart":
                return columnExpr + " LIKE '" + valueStr + "%'";
            case "NOTLIKESTART":
            case "notLikeStart":
                return columnExpr + " NOT LIKE '" + valueStr + "%'";
            case "LIKEEND":
            case "likeEnd":
                return columnExpr + " LIKE '%" + valueStr + "'";
            case "NOTLIKEEND":
            case "notLikeEnd":
                return columnExpr + " NOT LIKE '%" + valueStr + "'";
            case "IN":
            case "in":
                return columnExpr + " IN (" + parseJsonArray(valueStr) + ")";
            case "NOTIN":
            case "notIn":
                return columnExpr + " NOT IN (" + parseJsonArray(valueStr) + ")";
            case "BETWEEN":
            case "between":
                String[] values = valueStr.split(",");
                if (values.length == 2) {
                    return columnExpr + " BETWEEN '" + values[0].trim() + "' AND '" + values[1].trim() + "'";
                }
                return columnExpr + " BETWEEN " + valueStr;
            case "ISNOTEMPTY":
            case "isNotEmpty":
                return columnExpr + " IS NOT NULL AND " + columnExpr + " != ''";
            case "ISEMPTY":
            case "isEmpty":
                return columnExpr + " IS NULL OR " + columnExpr + " = ''";
            case "REGEXP_STR":
            case "regexp_str":
                return columnExpr + " ~ '" + valueStr + "'";
            case "IPCONTAINS":
            case "ipContains":
                return "iprange_contains(" + columnExpr + ", '" + valueStr + "')";
            case "IPNOTCONTAINS":
            case "ipNotContains":
                return "NOT iprange_contains(" + columnExpr + ", '" + valueStr + "')";
            case "IPRANGE":
            case "ipRange":
                String[] ipRange = valueStr.split(",");
                if (ipRange.length == 2) {
                    return columnExpr + " >= '" + ipRange[0].trim() + "' AND " + columnExpr + " <= '" + ipRange[1].trim() + "'";
                }
                return columnExpr + " BETWEEN " + valueStr;
            case "IPNOTINRANGE":
            case "ipNotInRange":
                String[] ipNotRange = valueStr.split(",");
                if (ipNotRange.length == 2) {
                    return "(" + columnExpr + " < '" + ipNotRange[0].trim() + "' OR " + columnExpr + " > '" + ipNotRange[1].trim() + "')";
                }
                return "NOT (" + columnExpr + " BETWEEN " + valueStr + ")";
            case "INGSAFEDOMAIN":
            case "inSafeDomain":
                return "is_in_safe_domain(" + columnExpr + ", '" + valueStr + "')";
            case "NOTINGSAFEDOMAIN":
            case "notInSafeDomain":
                return "NOT is_in_safe_domain(" + columnExpr + ", '" + valueStr + "')";
            case "INBUSINESSDOMAIN":
            case "inBusinessDomain":
                return "is_in_business_domain(" + columnExpr + ", '" + valueStr + "')";
            case "NOTINBUSINESSDOMAIN":
            case "notInBusinessDomain":
                return "NOT is_in_business_domain(" + columnExpr + ", '" + valueStr + "')";
            case "ISINTRANET":
            case "isIntranet":
                return "is_intranet(" + columnExpr + ")";
            case "ISNOTINTRANET":
            case "isNotIntranet":
                return "NOT is_intranet(" + columnExpr + ")";
            default:
                return columnExpr + " " + operator + " " + wrapValue(valueStr);
        }
    }

    /**
     * 构建列表达式（支持AnalysisFilter和AnalysisGroupByHaving）
     */
    private String buildColumnExpression(AnalysisFilter filter) {
        return buildColumnExpressionInternal(filter.getFn(), filter.getColumnName(), filter.getArguments());
    }

    /**
     * 构建列表达式（支持AnalysisGroupByHaving）
     */
    private String buildColumnExpression(AnalysisGroupByHaving having) {
        return buildColumnExpressionInternal(having.getFn(), having.getColumnName(), having.getArguments());
    }

    /**
     * 内部方法：构建列表达式
     */
    private String buildColumnExpressionInternal(String fn, String columnName, Object arguments) {
        if (StringUtils.isBlank(fn)) {
            return columnName;
        }

        String functionName = fn.toUpperCase();
        String argsStr = parseJsonbArguments(arguments);

        switch (functionName) {
            // 汇聚函数
            case "COUNT":
                return "COUNT(" + columnName + ")";
            case "DEDUPLICATION_COUNT":
                return "COUNT(DISTINCT " + columnName + ")";
            case "FIRST_VALUE":
                return "FIRST_VALUE(" + columnName + ") OVER (ORDER BY " + columnName + ")";
            case "LAST_VALUE":
                return "LAST_VALUE(" + columnName + ") OVER (ORDER BY " + columnName + ")";
            case "SORTATTACKRESULT":
                return "MAX(attack_result) OVER (PARTITION BY " + columnName + ")";
            case "SUM":
                return "SUM(" + columnName + ")";
            case "MAX":
                return "MAX(" + columnName + ")";
            case "MIN":
                return "MIN(" + columnName + ")";
            case "AVG":
                return "AVG(" + columnName + ")";
            case "DUPLICATESANDSPLICE":
                if (StringUtils.isNotBlank(argsStr)) {
                    return "LISTAGG(DISTINCT " + columnName + ", '" + argsStr + "')";
                }
                return "LISTAGG(DISTINCT " + columnName + ", ',')";
            case "CONCAT_AGG":
                if (StringUtils.isNotBlank(argsStr)) {
                    return "LISTAGG(" + columnName + ", '" + argsStr + "')";
                }
                return "LISTAGG(" + columnName + ", ',')";
            case "CONCAT_AGG_ID":
                if (StringUtils.isNotBlank(argsStr)) {
                    return "LISTAGG(" + columnName + ", '" + argsStr + "')";
                }
                return "LISTAGG(" + columnName + ", ',')";
            case "SPLIT_DISTINCT_CONCAT":
                if (StringUtils.isNotBlank(argsStr)) {
                    String[] splitArgs = argsStr.split(",");
                    if (splitArgs.length >= 3) {
                        String separator = splitArgs[0].trim();
                        String delimiter = splitArgs[1].trim();
                        String limit = splitArgs[2].trim();
                        return "LISTAGG(DISTINCT REGEXP_SPLIT(" + columnName + ", '" + delimiter + "'), '" + separator + "') LIMIT " + limit;
                    }
                }
                return columnName;
            //自定添加方法
            case "MODE_WITH_GROUP":

               return   "MAX( " + columnName + ") KEEP (DENSE_RANK FIRST ORDER BY dest_ip DESC)";

            // 聚合函数（兼容旧代码）
            case "ARRAY_AGG":
                return "JSONB_AGG(DISTINCT " + columnName + ")";
            case "STRING_AGG":
                if (StringUtils.isNotBlank(argsStr)) {
                    return "LISTAGG(" + columnSafeWrap(columnName) + ", " + argsStr + ")";
                }
                return "LISTAGG(DISTINCT " + columnName + ", ',')";

            // 时间函数
            case "YEAR":
                return "EXTRACT(YEAR FROM " + columnName + ")";
            case "MONTH":
                return "EXTRACT(MONTH FROM " + columnName + ")";
            case "DAYOFMONTH":
                return "EXTRACT(DAY FROM " + columnName + ")";
            case "HOUR":
                return "EXTRACT(HOUR FROM " + columnName + ")";
            case "MINUTE":
                return "EXTRACT(MINUTE FROM " + columnName + ")";
            case "SECOND":
                return "EXTRACT(SECOND FROM " + columnName + ")";
            case "WEEK":
                return "EXTRACT(WEEK FROM " + columnName + ")";

            // 字符串函数
            case "SUBSTRING":
                if (StringUtils.isNotBlank(argsStr)) {
                    return "SUBSTRING(" + columnName + ", " + argsStr + ")";
                }
                return "SUBSTRING(" + columnName + ", 1)";
            case "SUBSTRING_RANGE":
                if (StringUtils.isNotBlank(argsStr)) {
                    String[] rangeArgs = argsStr.split(",");
                    if (rangeArgs.length == 2) {
                        return "SUBSTRING(" + columnName + ", " + rangeArgs[0].trim() + ", " + rangeArgs[1].trim() + ")";
                    }
                }
                return columnName;
            case "REGEXP_EXTRACT":
                if (StringUtils.isNotBlank(argsStr)) {
                    String[] regexArgs = argsStr.split(",");
                    if (regexArgs.length >= 2) {
                        return "REGEXP_EXTRACT(" + columnName + ", '" + regexArgs[0].trim() + "', " + regexArgs[1].trim() + ")";
                    }
                    return "REGEXP_EXTRACT(" + columnName + ", '" + argsStr + "', 1)";
                }
                return columnName;
            case "SORT_KEY":
                if (StringUtils.isNotBlank(argsStr)) {
                    String[] sortArgs = argsStr.split(",");
                    if (sortArgs.length == 3) {
                        return "CONCAT(" + columnSafeWrap(sortArgs[0].trim()) + ", '" + sortArgs[1].trim() + "', " + columnSafeWrap(sortArgs[2].trim()) + ")";
                    }
                }
                return columnName;

            // 数值函数
            case "FLOOR":
                return "FLOOR(" + columnName + ")";

            // IP相关函数
            case "LOG4JPAYLOADEXTRACTIP":
                return "extract_ip_from_log4j(" + columnName + ")";
            case "LOG4JPAYLOADEXTRACTDOMAIN":
                return "extract_domain_from_log4j(" + columnName + ")";

            // 其他函数
            case "TO_CHAR":
                return "TO_CHAR(" + columnName + ", 'YYYYMMDD')";
            case "HOST":
                return columnName;
            default:
                return functionName + "(" + columnName + ")";
        }
    }

    /**
     * 解析JSONB格式的arguments
     */
    private String parseJsonbArguments(Object arguments) {
        if (arguments == null) {
            return "";
        }
        String argsStr = arguments.toString();
        if (argsStr.trim().startsWith("[") && argsStr.trim().endsWith("]")) {
            try {
                String trimmed = argsStr.trim().substring(1, argsStr.trim().length() - 1);
                if (trimmed.isEmpty()) {
                    return "";
                }
                return trimmed.replace("\"", "");
            } catch (Exception e) {
                log.warn("解析arguments失败: {}", argsStr, e);
                return "";
            }
        }
        return argsStr;
    }

    /**
     * 解析JSON值
     */
    private String parseJsonValue(Object value) {
        if (value == null) {
            return "";
        }
        return JsonbUtil.parseString(value);
        //return value.toString();
    }

    /**
     * 解析JSON数组
     */
    private String parseJsonArray(String value) {
        if (StringUtils.isBlank(value)) {
            return "";
        }
        if (value.trim().startsWith("[") && value.trim().endsWith("]")) {
            try {
                String trimmed = value.trim().substring(1, value.trim().length() - 1);
                if (trimmed.isEmpty()) {
                    return "";
                }
                return trimmed.replace("\"", "'");
            } catch (Exception e) {
                log.warn("解析JSON数组失败: {}", value, e);
                return value;
            }
        }
        return value;
    }

    @Override
    public String buildFullSql(String selectClause,
                               String fromClause,
                               String whereClause,
                               String groupByClause,
                               String havingClause) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append(selectClause).append("\n");
        sqlBuilder.append(fromClause).append("\n");

        if (StringUtils.isNotBlank(whereClause)) {
            sqlBuilder.append(whereClause).append("\n");
        }

        if (StringUtils.isNotBlank(groupByClause)) {
            sqlBuilder.append(groupByClause).append("\n");
        }

        if (StringUtils.isNotBlank(havingClause)) {
            sqlBuilder.append(havingClause).append("\n");
        }

        return sqlBuilder.toString();
    }

    /**
     * 列名安全包装（处理类型转换）
     */
    private String columnSafeWrap(String columnName) {
        // DM不需要类型转换，直接返回列名
        return columnName;
    }

    /**
     * 值包装（处理字符串）
     */
    private String wrapValue(String value) {
        if (value == null) {
            return "NULL";
        }
        if (value.startsWith("'") || value.equalsIgnoreCase("NULL") ||
            value.matches("\\d+")) {
            return value;
        }
        return "'" + value + "'";
    }

    /**
     * 构建列表达式（支持AnalysisField）
     */
    private String buildColumnExpression(AnalysisField field) {
        return buildColumnExpressionInternal(field.getFn(), field.getColumnName(), field.getArguments());
    }

    /**
     * 递归构建WHERE条件树
     * 注意：此方法已弃用，新的表结构中括号信息不存储在where_condition表中
     */
    @Deprecated
    private String buildWhereConditions(List<AnalysisWhereCondition> conditions) {
        if (conditions == null || conditions.isEmpty()) {
            return "";
        }

        // 构建条件树结构
        List<AnalysisWhereCondition> rootConditions = new ArrayList<>();
        for (AnalysisWhereCondition cond : conditions) {
            if (cond.getParentCondId() == null || cond.getParentCondId() == 0) {
                rootConditions.add(cond);
            }
        }

        if (rootConditions.isEmpty()) {
            // 没有根节点，平铺所有条件
            return buildFlatConditions(conditions);
        }

        // 构建树形结构
        List<String> conditionParts = new ArrayList<>();
        for (AnalysisWhereCondition root : rootConditions) {
            conditionParts.add(buildConditionNode(root, conditions));
        }

        return String.join(" ", conditionParts);
    }

    /**
     * 构建单个条件节点（递归）
     * 注意：此方法已弃用，新的表结构中不使用bracketType
     */
    @Deprecated
    private String buildConditionNode(AnalysisWhereCondition node,
                                       List<AnalysisWhereCondition> allConditions) {
        StringBuilder nodeBuilder = new StringBuilder();

        // 添加逻辑运算符（如果有）
        if (StringUtils.isNotBlank(node.getLogicalOp())) {
            nodeBuilder.append(node.getLogicalOp()).append(" ");
        }

        // 查找子条件
        List<AnalysisWhereCondition> children = new ArrayList<>();
        for (AnalysisWhereCondition cond : allConditions) {
            if (node.getCondId().equals(cond.getParentCondId())) {
                children.add(cond);
            }
        }

        // 递归处理子条件
        for (AnalysisWhereCondition child : children) {
            nodeBuilder.append(buildConditionNode(child, allConditions));
        }

        return nodeBuilder.toString();
    }

    /**
     * 构建条件表达式
     * 注意：此方法已弃用，具体的条件表达式应由AnalysisFilter提供
     */
    @Deprecated
    private String buildConditionExpression(AnalysisWhereCondition condition) {
        // 新的表结构中，AnalysisWhereCondition不再包含具体的字段信息
        // 具体的条件表达式应从AnalysisFilter中获取
        return "";
    }

    /**
     * 构建平铺条件（简单场景）
     * 注意：此方法已弃用，新的表结构中使用AnalysisFilter
     */
    @Deprecated
    private String buildFlatConditions(List<AnalysisWhereCondition> conditions) {
        List<String> conditionParts = new ArrayList<>();
        for (AnalysisWhereCondition cond : conditions) {
            if (StringUtils.isNotBlank(cond.getLogicalOp()) && !conditionParts.isEmpty()) {
                conditionParts.add(cond.getLogicalOp());
            }
        }
        return String.join(" ", conditionParts);
    }
}
