package com.agent.management.service;

import com.agent.management.common.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL 安全守卫：白名单校验，禁止 DDL/DML/DCL 操作
 *
 * <p>规则：
 * <ul>
 *   <li>只允许 SELECT / WITH / EXPLAIN 开头</li>
 *   <li>禁止 INSERT / UPDATE / DELETE / DROP / ALTER / CREATE / TRUNCATE / GRANT / REVOKE / MERGE / CALL / EXEC 等关键词</li>
 *   <li>禁止分号（防止多语句注入）</li>
 *   <li>禁止注释（-- 和 /* *\/）</li>
 * </ul>
 * </p>
 */
@Slf4j
@Service
public class SqlGuardService {

    private static final Set<String> ALLOWED_PREFIXES = Set.of("SELECT", "WITH", "EXPLAIN");

    private static final Set<String> FORBIDDEN_KEYWORDS = Set.of(
            "INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "CREATE", "TRUNCATE",
            "GRANT", "REVOKE", "MERGE", "CALL", "EXEC", "EXECUTE",
            "REPLACE", "RENAME", "LOAD", "OUTFILE", "DUMPFILE"
    );

    /** 提取 SQL 第一个非空单词（忽略前导空白和左括号） */
    private static final Pattern FIRST_WORD = Pattern.compile("^\\s*\\(?\\s*([A-Za-z]+)");

    /** 检测分号（语句分隔符） */
    private static final Pattern SEMICOLON = Pattern.compile(";");

    /** 检测注释（-- 单行、/* 多行、MySQL # 单行） */
    private static final Pattern COMMENT = Pattern.compile("--|/\\*|\\*/|#");

    /** 词边界匹配禁止关键词 */
    private static final Pattern FORBIDDEN_PATTERN = Pattern.compile(
            "\\b(" + String.join("|", FORBIDDEN_KEYWORDS) + ")\\b",
            Pattern.CASE_INSENSITIVE
    );

    /**
     * 校验 SQL，违规则抛 BusinessException
     */
    public void validate(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            throw new BusinessException("SQL 不能为空");
        }
        String trimmed = sql.trim();

        // 1. 检查注释和分号
        if (SEMICOLON.matcher(trimmed).find()) {
            throw new BusinessException("禁止使用分号（防止多语句执行）");
        }
        if (COMMENT.matcher(trimmed).find()) {
            throw new BusinessException("禁止使用注释（-- / /* */ / #）");
        }

        // 2. 检查首词白名单
        Matcher m = FIRST_WORD.matcher(trimmed);
        if (!m.find()) {
            throw new BusinessException("无法解析 SQL 首词");
        }
        String firstWord = m.group(1).toUpperCase();
        if (!ALLOWED_PREFIXES.contains(firstWord)) {
            throw new BusinessException("仅允许 SELECT / WITH / EXPLAIN 查询语句，当前首词：" + firstWord);
        }

        // 3. 检查禁止关键词
        Matcher forbidden = FORBIDDEN_PATTERN.matcher(trimmed);
        if (forbidden.find()) {
            throw new BusinessException("SQL 包含禁止的关键词：" + forbidden.group(1));
        }

        log.debug("SQL 校验通过: {}", trimmed.length() > 80 ? trimmed.substring(0, 80) + "..." : trimmed);
    }

    /**
     * 检查 schema 是否在系统库黑名单中
     */
    public void validateSchema(String schema, List<String> forbiddenSchemas) {
        if (schema == null) return;
        for (String blocked : forbiddenSchemas) {
            if (blocked.equalsIgnoreCase(schema)) {
                throw new BusinessException("禁止访问系统库：" + schema);
            }
        }
    }
}
