Administrator / xlyAi

package com.xly.util;

import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.expression.LongValue;

@Slf4j
public class SqlWhereUtil {

    /**
     * 去除为空的条件 和 bCheck=1 的条件
     * 然后判断是否还有有效的 WHERE 条件
     *
     * @param sql 原始SQL
     * @return true: 去除后还有有效条件; false: 去除后没有条件了
     */
    public static boolean hasValidConditionAfterClean(String sql) {
        if (StrUtil.isBlank(sql)) {
            return false;
        }

        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            Expression whereExpression = null;

            if (statement instanceof Select) {
                Select select = (Select) statement;
                PlainSelect plainSelect = select.getPlainSelect();
                if (plainSelect != null) {
                    whereExpression = plainSelect.getWhere();
                }
            } else if (statement instanceof Update) {
                Update update = (Update) statement;
                whereExpression = update.getWhere();
            } else if (statement instanceof Delete) {
                Delete delete = (Delete) statement;
                whereExpression = delete.getWhere();
            }

            if (whereExpression == null) {
                return false;
            }

            // 清理条件
            Expression cleanedExpression = cleanExpression(whereExpression);
            return cleanedExpression != null;

        } catch (JSQLParserException e) {
            log.warn("SQL 解析失败，使用简单判断: {}", sql, e);
            return hasValidConditionAfterCleanSimple(sql);
        }
    }

    /**
     * 递归清理表达式
     * 返回 null 表示整个表达式被清空
     */
    private static Expression cleanExpression(Expression expression) {
        if (expression == null) {
            return null;
        }

        // 处理 AND 表达式
        if (expression instanceof AndExpression) {
            AndExpression and = (AndExpression) expression;
            Expression left = cleanExpression(and.getLeftExpression());
            Expression right = cleanExpression(and.getRightExpression());

            if (left == null && right == null) {
                return null;
            } else if (left == null) {
                return right;
            } else if (right == null) {
                return left;
            } else {
                return new AndExpression(left, right);
            }
        }

        // 处理 OR 表达式
        if (expression instanceof OrExpression) {
            OrExpression or = (OrExpression) expression;
            Expression left = cleanExpression(or.getLeftExpression());
            Expression right = cleanExpression(or.getRightExpression());

            if (left == null && right == null) {
                return null;
            } else if (left == null) {
                return right;
            } else if (right == null) {
                return left;
            } else {
                return new OrExpression(left, right);
            }
        }

        // 处理括号表达式
        if (expression instanceof Parenthesis) {
            Parenthesis parenthesis = (Parenthesis) expression;
            Expression content = cleanExpression(parenthesis.getExpression());
            if (content == null) {
                return null;
            }
            return new Parenthesis(content);
        }

        // 判断是否为需要去除的条件
        if (shouldRemoveCondition(expression)) {
            return null;
        }

        return expression;
    }

    /**
     * 判断条件是否应该被去除
     */
    private static boolean shouldRemoveCondition(Expression expression) {
        // 1. IS NULL / IS NOT NULL
        if (expression instanceof IsNullExpression) {
            return true;
        }

        // 2. 等值比较: = '' , != '' , <> ''
        if (expression instanceof EqualsTo) {
            EqualsTo equals = (EqualsTo) expression;
            if (isEmptyString(equals.getRightExpression()) || isEmptyString(equals.getLeftExpression())) {
                return true;
            }
            // bCheck = 1
            if (isCheckOneCondition(equals)) {
                return true;
            }
        }

        // !=
        if (expression instanceof NotEqualsTo) {
            NotEqualsTo notEquals = (NotEqualsTo) expression;
            if (isEmptyString(notEquals.getRightExpression()) || isEmptyString(notEquals.getLeftExpression())) {
                return true;
            }
        }

        // 3. 函数判断为空: IFNULL(col, '') = '' , TRIM(col) = '' , NVL(col, '') = '' 等
        if (expression instanceof EqualsTo) {
            EqualsTo equals = (EqualsTo) expression;
            if (isFunctionReturnEmptyCheck(equals)) {
                return true;
            }
        }

        // 4. 其他比较操作符中可能包含空值判断
        if (expression instanceof ComparisonOperator) {
            ComparisonOperator comp = (ComparisonOperator) expression;
            if (isEmptyString(comp.getLeftExpression()) || isEmptyString(comp.getRightExpression())) {
                return true;
            }
        }

        return false;
    }

    /**
     * 判断表达式是否为空字符串常量 ''
     */
    private static boolean isEmptyString(Expression expression) {
        if (expression == null) {
            return false;
        }
        if (expression instanceof StringValue) {
            StringValue stringValue = (StringValue) expression;
            return "".equals(stringValue.getValue());
        }
        return false;
    }

    /**
     * 判断是否为 bCheck = 1 或 1 = bCheck
     */
    private static boolean isCheckOneCondition(EqualsTo equals) {
        Expression left = equals.getLeftExpression();
        Expression right = equals.getRightExpression();

        // bCheck = 1
        if (isColumnNameCheck(left) && isOneValue(right)) {
            return true;
        }
        // 1 = bCheck
        if (isOneValue(left) && isColumnNameCheck(right)) {
            return true;
        }
        return false;
    }

    /**
     * 判断是否为 bCheck 列（不区分大小写）
     */
    private static boolean isColumnNameCheck(Expression expression) {
        if (expression instanceof Column) {
            Column column = (Column) expression;
            String columnName = column.getColumnName();
            return columnName != null && columnName.equalsIgnoreCase("bCheck");
        }
        return false;
    }

    /**
     * 判断是否为数字 1
     */
    private static boolean isOneValue(Expression expression) {
        if (expression instanceof LongValue) {
            LongValue longValue = (LongValue) expression;
            return longValue.getValue() == 1;
        }
        return false;
    }

    /**
     * 判断是否为函数返回空值判断，如：IFNULL(col, '') = ''
     */
    private static boolean isFunctionReturnEmptyCheck(EqualsTo equals) {
        Expression left = equals.getLeftExpression();
        Expression right = equals.getRightExpression();

        // 情况1: 函数 = ''
        if (isNullReturnFunction(left) && isEmptyString(right)) {
            return true;
        }
        // 情况2: '' = 函数
        if (isEmptyString(left) && isNullReturnFunction(right)) {
            return true;
        }
        return false;
    }

    /**
     * 判断是否为可能返回 NULL 的函数（在空值判断场景下）
     * 如: IFNULL(col, '') , NVL(col, '') , TRIM(col) , COALESCE(col, '')
     */
    private static boolean isNullReturnFunction(Expression expression) {
        if (!(expression instanceof Function)) {
            return false;
        }

        Function function = (Function) expression;
        String funcName = function.getName();

        if (funcName == null) {
            return false;
        }

        String upperName = funcName.toUpperCase();

        // 常见的空值处理函数
        switch (upperName) {
            case "IFNULL":
            case "NVL":
            case "NVL2":
            case "COALESCE":
            case "NULLIF":
                return true;
            case "TRIM":
            case "LTRIM":
            case "RTRIM":
                // TRIM 函数去除空格后判断为空
                return true;
            default:
                return false;
        }
    }

    // ===================== 简单判断方法（降级方案） =====================

    /**
     * 简单判断：去除条件后是否还有有效条件
     */
    private static boolean hasValidConditionAfterCleanSimple(String sql) {
        if (StrUtil.isBlank(sql)) {
            return false;
        }

        String upper = sql.toUpperCase();

        // 先找到 WHERE 位置
        int whereIdx = upper.indexOf("WHERE");
        if (whereIdx == -1) {
            return false;
        }

        // 提取 WHERE 后面的条件
        String whereClause = sql.substring(whereIdx + 5);

        // 去除 GROUP BY、ORDER BY、LIMIT 之后的内容
        int groupIdx = upper.indexOf("GROUP BY", whereIdx);
        int orderIdx = upper.indexOf("ORDER BY", whereIdx);
        int limitIdx = upper.indexOf("LIMIT", whereIdx);
        int endIdx = sql.length();
        if (groupIdx != -1) endIdx = Math.min(endIdx, groupIdx);
        if (orderIdx != -1) endIdx = Math.min(endIdx, orderIdx);
        if (limitIdx != -1) endIdx = Math.min(endIdx, limitIdx);

        String condition = sql.substring(whereIdx + 5, endIdx);

        // 去除需要过滤的条件
        String cleaned = condition;

        // 去除 IS NULL / IS NOT NULL
        cleaned = cleaned.replaceAll("(?i)\\bIS\\s+(NOT\\s+)?NULL\\b", "");

        // 去除 = '' / != '' / <> ''
        cleaned = cleaned.replaceAll("(?i)\\s*(=|!=|<>)\\s*''\\s*", "");
        cleaned = cleaned.replaceAll("(?i)\\s*''\\s*(=|!=|<>)\\s*", "");

        // 去除 bCheck = 1
        cleaned = cleaned.replaceAll("(?i)\\bbCheck\\s*=\\s*1\\b", "");

        // 去除函数空值判断: IFNULL(..., '') = ''
        cleaned = cleaned.replaceAll("(?i)\\b(IFNULL|NVL|COALESCE|TRIM)\\s*\\([^)]+\\)\\s*=\\s*''", "");
        cleaned = cleaned.replaceAll("(?i)\\b''\\s*=\\s*(IFNULL|NVL|COALESCE|TRIM)\\s*\\([^)]+\\)", "");

        // 去除多余的 AND/OR
        cleaned = cleaned.replaceAll("(?i)\\s+(AND|OR)\\s+", " ");
        cleaned = cleaned.trim();

        // 判断是否还有有效条件（非空且不是纯粹的 true/false）
        if (StrUtil.isBlank(cleaned)) {
            return false;
        }

        // 如果只剩下 1=1 或 true 等恒真条件，也算无效
        if (cleaned.matches("(?i)^\\s*(1\\s*=\\s*1|true)\\s*$")) {
            return false;
        }

        return true;
    }
}