package com.walker.jdbc;

import com.fasterxml.jackson.databind.node.ArrayNode;
import com.walker.infrastructure.utils.JsonUtils;
import com.walker.jdbc.dao.PersistenceException;
import com.walker.jdbc.util.StringUtils;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.IdentityHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * <pre>
 * 该类有两个用处，一是绑定页面查询条件，二是生成and形式的查询条件。
 * Contorller中：
 *   &#64;ModelAttribute("queryVo") QueryVo queryVo
 * Service中：
 *   sql = sql.append(queryVo.generateAndSql()); 生成and形式的sql语句
 *   queryVo.getQueryMap() 生成对应的sql语句参数
 *   如需非页面中的查询条件，可使用putLike,putEqual方法自行添加查询条件
 * 页面中：
 *  input type="text" name="likeItem['rz.username']" value="${queryVo.likeItem['rz.username']}"
 *   其中likeItem表示模糊查询，equalItem表示精确查询。
 *   如果要使用别名可用 别名.字段名
 * 注：暂不支持字段名别名的情况
 * </pre>
 *
 * @author 张亮
 * @author 时克英 修改
 * @date 2022-10-10
 */
public class QueryVo implements Serializable {
    // 序列化版本号
    private static final long serialVersionUID = 1L;

    public static final String SPACE = " ";

    /**
     * 常用sql语句组成片段
     */
    private static final String PERCENT = "%";
    private static final String LIKE = " like ";
    private static final String AND = " and ";
    private static final String OR = " or ";

    private static final String EQUAL = " = ";
    private static final String UNEQUAL = " != ";
    private static final String IN = " in ";
    private static final String NOTIN = " not in ";
    private static final String GREATER = " >= ";
    private static final String LESS = " <= ";
    private static final String WHERE = " where ";
    private static final String ORDER_BY = " order by ";
    /**
     * 等于、相似、大于、小于、不等于、包含、不包含对象
     */
    private Map<String, Object> equalItem;
    private Map<String, Object> likeItem;
    private Map<String, Object> greaterItem;
    private Map<String, Object> lessItem;
    private Map<String, Object> unequalItem;
    private Map<String, Object> includeItem;
    private Map<String, Object> notincludeItem;

    //默认添加后缀
    private Map<String, Object> defalutSuffixItem;
    private Integer sfzsgd;

    /**
     * 记录非重复变量
     */
    private Map<String, Object> countMap;
    private List keyList;
    /**
     * 自定义sql
     */
    private StringBuilder extSql;
    /**
     * 自定义order by sql
     */
    private StringBuilder orderBySql;
    /**
     * 排序用字段名，正序倒序
     */
    private String qvSortName;
    private String qvSortOrder;
    /**
     * 列选择的所有数据
     */
    private String toggleColumnValue = "";
    /**
     * 生成的查询语句用值
     */
    private Map<String, Object> queryMap;
    /**
     * 保存页面查询条件字符串
     */
    private String serializeQuery = "";
    /**
     * 临时排除查询条件
     */
    private Map<String, Object> excludeQueryVO;

    private  List<Map<String,Object>> labelCount;


    public QueryVo() {
        equalItem = new SaveIdentityHashMap<>();
        likeItem = new SaveIdentityHashMap<>();
        greaterItem = new SaveIdentityHashMap<>();
        lessItem = new SaveIdentityHashMap<>();
        countMap = new SaveIdentityHashMap<>();
        unequalItem = new SaveIdentityHashMap<>();
        includeItem = new SaveIdentityHashMap<>();
        notincludeItem = new SaveIdentityHashMap<>();
        defalutSuffixItem = new HashMap<>();
        excludeQueryVO = new HashMap<>();
        keyList = new ArrayList<>();
        labelCount = new ArrayList<>();
        queryMap = new HashMap<>();
        extSql = new StringBuilder();
        orderBySql = new StringBuilder();
        qvSortName = null;
        qvSortOrder = null;
    }

    /**
     * 生成and格式的sql，默认不带where
     *
     * @return
     * @author 张亮
     */
    public StringBuffer generateAndSql() {
        return generateAndSql(false);
    }

    /**
     * 生成and格式的sql，可选择是否带where
     *
     * @return
     * @author 张亮
     */
    public StringBuffer generateAndSql(boolean generateWhere) {
        StringBuffer whereQuery = new StringBuffer(SPACE);
        boolean paramEmpty = true;
        paramEmpty = genSql(equalItem, whereQuery, paramEmpty, EQUAL, false, generateWhere, keyList, countMap);
        paramEmpty = genSql(likeItem, whereQuery, paramEmpty, LIKE, true, generateWhere, keyList, countMap);
        paramEmpty = genSql(greaterItem, whereQuery, paramEmpty, GREATER, false, generateWhere, keyList, countMap);
        paramEmpty = genSql(lessItem, whereQuery, paramEmpty, LESS, false, generateWhere, keyList, countMap);
        paramEmpty = genSql(unequalItem, whereQuery, paramEmpty, UNEQUAL, false, generateWhere, keyList, countMap);
        paramEmpty = genSql(includeItem, whereQuery, paramEmpty, IN, false, generateWhere, keyList, countMap);
        paramEmpty = genSql(notincludeItem, whereQuery, paramEmpty, NOTIN, false, generateWhere, keyList, countMap);
        //附加sql不为空才生成
        if (!extSql.toString().equals(com.walker.infrastructure.utils.StringUtils.EMPTY_STRING)) {
            if (generateWhere && paramEmpty) {
                //需要生成where没有参数
                whereQuery.append(WHERE).append(extSql);
            } else {
                //不需要生成where没有参数
                whereQuery.append(AND).append(extSql);
            }
        }
        if(excludeQueryVO.size()==0) {//如果有排除分组函数，不执行排序
            if (StringUtils.isNotEmpty(qvSortName)) {
                if (StringUtils.isNotEmpty(qvSortOrder)) {
                    whereQuery.append(ORDER_BY + qvSortName + SPACE + qvSortOrder);
                }
            } else {
                if (StringUtils.isNotEmpty(orderBySql.toString())) {
                    whereQuery.append(SPACE).append(orderBySql);
                }
            }
        }

        return whereQuery.append(SPACE);
    }

    private boolean genSql(Map<String, Object> item, StringBuffer whereQuery, boolean paramEmpty, String operator, boolean like, boolean generateWhere, List keyList, Map<String, Object> countMap) {

        boolean flag_in=operator.equals(IN) || operator.equals(NOTIN);
        for (Map.Entry<String, Object> entry : item.entrySet()) {
            if(excludeQueryVO.containsKey(entry.getKey())){
                continue;
            }
            String key = "";
            if (keyList.contains(entry.getKey())) {
                if (!countMap.containsKey(entry.getKey())) {
                    countMap.put(entry.getKey(), 0);
                } else {
                    countMap.put(entry.getKey(), countMap.get(entry.getKey() + 1));
                }
                key = entry.getKey() + com.walker.infrastructure.utils.StringUtils.STRING_UNDERLINE + countMap.get(entry.getKey());
            } else {
                keyList.add(entry.getKey());
                key = entry.getKey();
            }

            if (StringUtils.isNotEmpty(String.valueOf(entry.getValue())) && !String.valueOf(entry.getValue()).equalsIgnoreCase("[]")) {
                if(flag_in){
                    if (generateWhere && paramEmpty) {
                        whereQuery.append(WHERE + entry.getKey() + operator + "(:" + key+")");
                        generateWhere = false;
                    } else {
                        whereQuery.append(AND + entry.getKey() + operator +  "(:" + key+")");
                    }
                    List<Object> value=new ArrayList<>();
                    if(entry.getValue().toString().startsWith("[") && entry.getValue().toString().endsWith("]")){
//                        JSONArray array= new JSONArray(entry.getValue().toString());
//                        if(array.length()>0&&array.get(0).toString().startsWith(";")&&array.get(0).toString().endsWith(";")) {
//                            for (int i = 0; i < array.length(); i++) {
//                                String v = array.get(i).toString();
//                                value.add(v.substring(1, v.length() - 1));
//                            }
//                        }else{
//                            value=array.toList();
//                        }
                        try {
                            ArrayNode arrayNode = JsonUtils.toJsonArray(entry.getValue().toString());
                            if(arrayNode.size() > 0
                                    && arrayNode.get(0).toString().startsWith(";") && arrayNode.get(0).toString().endsWith(";")){
                                for (int i = 0; i < arrayNode.size(); i++) {
                                    String v = arrayNode.get(i).toString();
                                    value.add(v.substring(1, v.length() - 1));
                                }
                            } else {
                                for (int i = 0; i < arrayNode.size(); i++) {
                                    value.add(arrayNode.get(i).toString());
                                }
                            }
                        } catch (Exception e) {
                            throw new RuntimeException(e);
                        }

                    }else{
                        if(entry.getValue().toString().startsWith(";")&&entry.getValue().toString().endsWith(";")) {
                            List<String> templist=Arrays.asList(String.valueOf(entry.getValue()).trim().split(","));
                            for (String tv:templist) {
                                value.add(tv.substring(1, tv.length() - 1));
                            }
                        }else{
                            value=Arrays.asList(String.valueOf(entry.getValue()).trim().split(com.walker.infrastructure.utils.StringUtils.DEFAULT_SPLIT_SEPARATOR));
                        }
                        value.remove("");
                    }
                    queryMap.put(key, value);
                }else {
                    if (generateWhere && paramEmpty) {
                        whereQuery.append(WHERE + entry.getKey() + operator + ":" + key);
                    } else {
                        whereQuery.append(AND + entry.getKey() + operator + ":" + key);
                    }
                    String value = "";
                    if (like) {
                        value = PERCENT + String.valueOf(entry.getValue()).trim() + PERCENT;
                    } else {
                        if(defalutSuffixItem.containsKey(key)){
                            value = String.valueOf(entry.getValue()).trim()+defalutSuffixItem.get(key);
                        }else{
                            value = String.valueOf(entry.getValue()).trim();
                        }
                    }
                    paramEmpty = false;
                    queryMap.put(key, value);
                }
            }
        }
        return paramEmpty;
    }

    /**
     * 获取sql查询用的参数Map("key","value")
     *
     * @return
     * @author 张亮
     */
    public Map<String, Object> getQueryMap() {
        return queryMap;
    }

    /**
     * 增加order by语句
     *
     * @param orderBySqlOnce 自己定义的order by语句
     */
    public void appendOrderBySql(String orderBySqlOnce) {
        orderBySql.append(orderBySqlOnce);
    }

    /**
     * 增加一段自己定义的sql语句
     *
     * @param extSqlonce 自己定义的sql语句
     * @return
     */
    public void appendExtSql(String extSqlonce) {
        extSql.append(extSqlonce);
    }

    /**
     * 增加一个等于查询条件
     *
     * @param key
     * @param value
     */
    public void addEqualItem(String key, Object value) {
        equalItem.put(key, value);
    }

    /**
     * 增加一个like查询条件
     *
     * @param key
     * @param value
     */
    public void addLikeItem(String key, Object value) {
        likeItem.put(key, value);
    }
    public void addIncludeItem(String key, Object value) {
        includeItem.put(key, value);
    }
    public void initIncludeItem(String key,Object value){ includeItem.put(key,";"+value+";");}

    /**
     * 增加一个小于查询条件
     *
     * @param key
     * @param value
     */
    public void addLessItem(String key, Object value) {
        lessItem.put(key, value);
    }

    /**
     * 增加一个大于查询条件
     *
     * @param key
     * @param value
     */
    public void addGreaterItem(String key, Object value) {
        greaterItem.put(key, value);
    }

    /**
     * 增加一个不等于的变量
     *
     * @param key
     * @param value
     */
    public void addUnequalItem(String key, Object value) {
        unequalItem.put(key, value);
    }

    /**
     * 增加一个绑定变量值
     *
     * @param key
     * @param value
     */
    public void addQueryMap(String key, Object value) {
        queryMap.put(key, value);
    }

    /**
     * 增加一组绑定变量值
     *
     * @param map
     */
    public void addQueryMap(Map map) {
        queryMap.putAll(map);
    }

    /**
     * 获取一个equal变量
     *
     * @param key
     * @return equalItem中的一个变量
     */
    public Object getEqualItemValue(String key) {
        return getObject(equalItem, key);
    }
    /**
     * 获取一个include变量
     *
     * @param key
     * @return includeItem中的一个变量
     */
    public Object getIncludeItemValue(String key) {
        return getObject(includeItem, key);
    }

    /**
     * 获取一个like变量
     *
     * @param key
     * @return
     */
    public Object getLikeItemValue(String key) {
        return getObject(likeItem, key);
    }

    private Object getObject(Map<String, Object> item, String key) {
        for (Map.Entry<String, Object> entry : item.entrySet()) {
            if (entry.getKey().equals(key)) {
                return entry.getValue();
            }
        }
        return null;
    }

    public Map<String, Object> getEqualItem() {
        return equalItem;
    }

    public void setEqualItem(Map<String, Object> equalItem) {
        this.equalItem = equalItem;
    }

    public Map<String, Object> getLikeItem() {
        return likeItem;
    }

    public void setLikeItem(Map<String, Object> likeItem) {
        this.likeItem = likeItem;
    }

    public String getToggleColumnValue() {
        return toggleColumnValue;
    }

    public void setToggleColumnValue(String toggleColumnValue) {
        this.toggleColumnValue = toggleColumnValue;
    }

    public String getSerializeQuery() {
        return serializeQuery;
    }

    public void setSerializeQuery(String serializeQuery) {
        this.serializeQuery = serializeQuery;
    }

    public Map<String, Object> getLessItem() {
        return lessItem;
    }

    public void setLessItem(Map<String, Object> lessItem) {
        this.lessItem = lessItem;
    }

    public Map<String, Object> getGreaterItem() {
        return greaterItem;
    }

    public void setGreaterItem(Map<String, Object> greaterItem) {
        this.greaterItem = greaterItem;
    }

    public Map<String, Object> getUnequalItem() {
        return unequalItem;
    }

    public void setUnequalItem(Map<String, Object> unequalItem) {
        this.unequalItem = unequalItem;
    }

    public Map<String, Object> getIncludeItem() {
        return includeItem;
    }

    public void setIncludeItem(Map<String, Object> includeItem) {
        this.includeItem = includeItem;
    }

    public Map<String, Object> getNotincludeItem() {
        return notincludeItem;
    }

    public void setNotincludeItem(Map<String, Object> notincludeItem) {
        this.notincludeItem = notincludeItem;
    }

    public Map<String, Object> getDefalutSuffixItem() {
        return defalutSuffixItem;
    }

    public void setDefalutSuffixItem(Map<String, Object> defalutSuffixItem) {
        this.defalutSuffixItem = defalutSuffixItem;
    }

    /**
     * 增加一个绑定变量值
     *
     * @param key
     * @param value
     */
    public void addExcludeQueryVO(String key, Object value) {
        excludeQueryVO.put(key, value);
    }
    public Map<String, Object> getExcludeQueryVO() {
        return excludeQueryVO;
    }
    public void clearExcludeQueryVO(){
        excludeQueryVO.clear();
        keyList.clear();
        countMap.clear();
    }

    public List<Map<String, Object>> getLabelCount() {
        return labelCount;
    }

    public void setLabelCount(List<Map<String, Object>> labelCount) {
        this.labelCount = labelCount;
    }

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public String getQvSortName() {
        return qvSortName;
    }

    public void setQvSortName(String qvSortName) {
        if(!columnCheck(qvSortName)) {
            throw new PersistenceException(qvSortName+"为非法的属性值");
        }
        this.qvSortName = qvSortName;
    }

    public String getQvSortOrder() {
        return qvSortOrder;
    }

    public void setQvSortOrder(String qvSortOrder) {
        if(StringUtils.isNotEmpty(qvSortOrder)) {
            if(!qvSortOrder.equalsIgnoreCase("DESC")&&!qvSortOrder.equalsIgnoreCase("ASC")) {
                throw new PersistenceException(qvSortName+"为非法的关键字");
            }
        }
        this.qvSortOrder = qvSortOrder;
    }

    public Integer getSfzsgd() {
        if(sfzsgd==null){
            sfzsgd = 0;
        }
        return sfzsgd;
    }

    public void setSfzsgd(Integer sfzsgd) {
        this.sfzsgd = sfzsgd;
    }

    private static Pattern p = Pattern.compile("^[A-Za-z0-9_\\.]+$");

    /**
     * 检查列名是否合法
     * @param column
     * @return
     */
    private boolean columnCheck(String column) {
        if(column == null ||column.isEmpty()) {
            return true;
        } else {
            Matcher m = p.matcher(column);
            return m.matches();
        }
    }

    class SaveIdentityHashMap<K, V> extends IdentityHashMap<K, V> {
        @Override
        public V put(K k, V v) {
            if(!columnCheck(k.toString())) {
                throw new PersistenceException(k+"为非法的属性名");
            }
            return super.put(k, v);
        }
    }
}