package com.cpop.core.utils.sql;

import com.cpop.common.utils.ServletUtils;
import com.cpop.common.utils.StringUtils;
import com.cpop.core.base.entity.PageDomain;
import com.cpop.core.base.exception.UtilException;

/**
 * sql操作工具类
 *
 * @author DB
 */
public class SqlUtils {

    public SqlUtils() {
    }

    private static class SqlUtilInstance {
        private static final SqlUtils INSTANCE = new SqlUtils();
    }

    public static SqlUtils getInstance() {
        return SqlUtilInstance.INSTANCE;
    }

    /**
     * 当前记录起始索引
     */
    public final String PAGE_NUM = "page";

    /**
     * 每页显示记录数
     */
    public final String PAGE_SIZE = "pageSize";

    /**
     * 排序列
     */
    public final String ORDER_BY_COLUMN = "orderByColumn";

    /**
     * 排序的方向 "desc" 或者 "asc".
     */
    public final String IS_ASC = "isAsc";

    /**
     * 分页参数合理化
     */
    public final String REASONABLE = "reasonable";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public final String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 封装分页对象
     */
    public PageDomain getPageDomain() {
        PageDomain pageDomain = new PageDomain();
        pageDomain.setPageNum(ServletUtils.getParameterToInt(PAGE_NUM));
        pageDomain.setPageSize(ServletUtils.getParameterToInt(PAGE_SIZE));
        pageDomain.setOrderByColumn(null == ServletUtils.getParameter(ORDER_BY_COLUMN) ? null : SqlUtils.getInstance().escapeOrderBySql(ServletUtils.getParameter(ORDER_BY_COLUMN)));
        pageDomain.setIsAsc(ServletUtils.getParameter(IS_ASC));
        pageDomain.setReasonable(ServletUtils.getParameterToBool(REASONABLE));
        return pageDomain;
    }

    public PageDomain buildPageRequest() {
        return getPageDomain();
    }

    /**
     * 检查字符，防止注入绕过
     */
    public String escapeOrderBySql(String value) {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value)) {
            throw new UtilException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public boolean isValidOrderBySql(String value) {
        return value.matches(SQL_PATTERN);
    }

    /**
     * 设置请求排序数据
     */
    public String startOrderBy() {
        PageDomain pageDomain = getPageDomain();
        if (StringUtils.isNotEmpty(pageDomain.getOrderBy())) {
            return escapeOrderBySql(pageDomain.getOrderBy());
        }else {
            throw new UtilException("获取排序参数失败");
        }
    }
}
