package com.yihu.hos.web.framework.util.sql;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.node.JsonNodeType;
import com.yihu.hos.core.datatype.CollectionUtil;
import com.yihu.hos.core.datatype.StringUtil;
import com.yihu.hos.web.framework.constant.SqlConstants;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lingfeng on 2015/6/23.
 */
public class SqlCreator {

    private String tableName;
    private List<String> itemList;
    private List<String> itemValueList;
    private List<String> conditionList;
    private List<String> orderList;
    private Map<String, Object> keyValueMap;
    private BeanTransformer transformer;
    public SqlCreator(Class tClass) {
        keyValueMap = new HashMap<>();
        conditionList = new ArrayList<>();
        itemList = new ArrayList<>();
        itemValueList = new ArrayList<>();
        orderList = new ArrayList<>();
        transformer = new BeanTransformer(tClass);
    }

    public String insertData() {

        StringBuilder sqlBuffer = new StringBuilder();
        sqlBuffer.append(SqlConstants.INSERT_INTO + tableName + SqlConstants.LEFT_BRACKET);
        for (String item : itemList) {
            sqlBuffer.append(item + SqlConstants.COMMA);
        }
        sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
        sqlBuffer.append(SqlConstants.RIGHT_BRACKET + SqlConstants.VALUES + SqlConstants.LEFT_BRACKET);
        for (String item : itemValueList) {
            sqlBuffer.append(item + SqlConstants.COMMA);
        }
        sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
        sqlBuffer.append(SqlConstants.RIGHT_BRACKET + SqlConstants.SEMICOLON);

        return sqlBuffer.toString();
    }

    public String insertData(String tabelName, JsonNode jsonNode) {

        setTableName(tabelName);
        for (Object key : transformer.columnToProperty.keySet()) {
            String property = StringUtil.toString(transformer.columnToProperty.get(key));
            JsonNode propertyJsonNode = jsonNode.get(property);
            if (propertyJsonNode != null) {
                itemList.add(StringUtil.toString(key));
                itemValueList.add(SqlConstants.COLON + property);
                setKeyValueMapByType(property, propertyJsonNode, keyValueMap);
            }
        }
        return insertData();
    }

    public void setKeyValueMapByType(String property, JsonNode propertyJsonNode, Map<String, Object> keyValueMap) {
        JsonNodeType jsonNodeType = propertyJsonNode.getNodeType();
        if (jsonNodeType.equals(JsonNodeType.STRING)) {
            keyValueMap.put(property, propertyJsonNode.asText());
        } else if (jsonNodeType.equals(JsonNodeType.NUMBER)) {
            keyValueMap.put(property, propertyJsonNode.asInt());
        } else {
            keyValueMap.put(property, null);
        }
    }

    public String countData(String tabelName) {

        StringBuilder sqlBuffer = new StringBuilder();
        setTableName(tabelName);
        sqlBuffer.append(SqlConstants.SELECT);
        sqlBuffer.append(SqlConstants.COUNT);
        sqlBuffer.append(SqlConstants.FROM + tableName);
        if (!CollectionUtil.isEmpty(conditionList)) {
            sqlBuffer.append(SqlConstants.WHERE);
            sqlBuffer.append("1=1" + SqlConstants.AND);
            for (String condition : conditionList) {
                sqlBuffer.append(condition + SqlConstants.AND);
            }
            sqlBuffer.delete(sqlBuffer.length() - 4, sqlBuffer.length() - 1);
        }
        return sqlBuffer.toString();
    }

    public String selectData(String tabelName) {

        setTableName(tabelName);
        for(Object key : transformer.columnToProperty.keySet()){
            itemList.add(StringUtil.toString(key));
        }

        return selectData();
    }

    public String selectData() {

        StringBuilder sqlBuffer = new StringBuilder();
        sqlBuffer.append(SqlConstants.SELECT);
        for (String item : itemList) {
            sqlBuffer.append(item + SqlConstants.COMMA);
        }
        sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
        sqlBuffer.append(SqlConstants.FROM + tableName);
        if (!CollectionUtil.isEmpty(conditionList)) {
            sqlBuffer.append(SqlConstants.WHERE);
            sqlBuffer.append("1=1" + SqlConstants.AND);
            for (String condition : conditionList) {
                sqlBuffer.append(condition + SqlConstants.AND);
            }
            sqlBuffer.delete(sqlBuffer.length() - 4, sqlBuffer.length() - 1);
        }
        if (!CollectionUtil.isEmpty(orderList)) {
            for (String order : orderList) {
                sqlBuffer.append(order);
            }
            sqlBuffer.delete(sqlBuffer.length() - 1, sqlBuffer.length());
        }
        return sqlBuffer.toString();
    }

    public String updateDataByTableKey(String tabelName, JsonNode jsonNode) {

        setTableName(tabelName);
        StringBuilder sqlBuffer = new StringBuilder();
        sqlBuffer.append(SqlConstants.UPDATE + tableName + SqlConstants.SET);
        Map columnToProperty = transformer.columnToProperty;
        for (Object key : columnToProperty.keySet()) {
            String property = StringUtil.toString(columnToProperty.get(key));
            if (!property.equals(SqlConstants.TABLE_KEY)) {
                sqlBuffer.append(key + SqlConstants.EQUAL_MARK + SqlConstants.COLON + property + SqlConstants.COMMA);
                setKeyValueMapByType(property, jsonNode.get(property), keyValueMap);
            }
        }
        sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
        Object tableKey = columnToProperty.get(SqlConstants.TABLE_KEY.toUpperCase());
        sqlBuffer.append(SqlConstants.WHERE + SqlConstants.TABLE_KEY + SqlConstants.EQUAL_MARK + SqlConstants.COLON + tableKey);

        String property = StringUtil.toString(tableKey);
        setKeyValueMapByType(property, jsonNode.get(property), keyValueMap);
        return sqlBuffer.toString();
    }

    public String betweenCondition(String item, Object value1, Object value2) {

        String condition = item + SqlConstants.BETWEEN + SqlConstants.BEGIN + item + SqlConstants.AND + SqlConstants.END + item;
        keyValueMap.put(SqlConstants.BEGIN + item, value1);
        keyValueMap.put(SqlConstants.END + item, value2);
        conditionList.add(condition);
        return condition;
    }

    public String greaterAndEqualCondition(String item, Object value) {
        String condition = item + SqlConstants.GREATER_EQUAL_MARK + SqlConstants.COLON + item;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String greaterCondition(String item, Object value) {
        String condition = item + SqlConstants.GREATER_MARK + SqlConstants.COLON + item;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String lessAndEqualCondition(String item, Object value) {
        String condition = item + SqlConstants.LESS_EQUAL_MARK + SqlConstants.COLON + item;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String lessCondition(String item, Object value) {
        String condition = item + SqlConstants.LESS_MARK + SqlConstants.COLON + item;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String equalCondition(String item, Object value) {
        String column = StringUtil.toString(transformer.propertyToColumn.get(item));
        String condition = column + SqlConstants.EQUAL_MARK + SqlConstants.COLON + item;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String inCondition(String item, Object value) {
        String column = StringUtil.toString(transformer.propertyToColumn.get(item));
        String condition = column + SqlConstants.IN + SqlConstants.LEFT_BRACKET + SqlConstants.COLON + item + SqlConstants.RIGHT_BRACKET;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String equalConditionQue(String item, Object value) {
        String column = StringUtil.toString(transformer.propertyToColumn.get(item));
        String condition = column + SqlConstants.EQUAL_MARK + SqlConstants.QUESTION_MARK;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String likeOrCondition(String item1, String item2, Object value) {
        if (!StringUtil.isEmpty(value)) {
            String column1 = StringUtil.toString(transformer.propertyToColumn.get(item1));
            String column2 = StringUtil.toString(transformer.propertyToColumn.get(item2));
            String condition = SqlConstants.LEFT_BRACKET + column1 + SqlConstants.LIKE + SqlConstants.COLON + item1
                    + SqlConstants.OR + column2 + SqlConstants.LIKE + SqlConstants.COLON + item2 + SqlConstants.RIGHT_BRACKET;
            value = SqlConstants.PERCENT + value + SqlConstants.PERCENT;
            keyValueMap.put(item1, value);
            keyValueMap.put(item2, value);
            conditionList.add(condition);
            return condition;
        } else {
            return SqlConstants.EMPTY;
        }

    }

    public String likeCondition(String item, Object value) {
        String column = StringUtil.toString(transformer.propertyToColumn.get(item));
        String condition = column + SqlConstants.LIKE + SqlConstants.COLON + item;
        value = SqlConstants.PERCENT + value + SqlConstants.PERCENT;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String neCondition(String item, Object value) {
        String column = StringUtil.toString(transformer.propertyToColumn.get(item));
        String condition = column + SqlConstants.NE_MARK + SqlConstants.COLON + item;
        keyValueMap.put(item, value);
        conditionList.add(condition);
        return condition;
    }

    public String order(String item, Object value) {
        String column = StringUtil.toString(transformer.propertyToColumn.get(item));
        String order;
        if (CollectionUtil.isEmpty(orderList)) {
            order = SqlConstants.ORDER_BY + column + SqlConstants.BLANK + value + SqlConstants.COMMA;
        } else {
            order = column + SqlConstants.BLANK + value + SqlConstants.COMMA;
        }
        orderList.add(order);
        return order;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public List<String> getItemList() {
        return itemList;
    }

    public void setItemList(List<String> itemList) {
        this.itemList = itemList;
    }

    public List<String> getItemValueList() {
        return itemValueList;
    }

    public void setItemValueList(List<String> itemValueList) {
        this.itemValueList = itemValueList;
    }

    public List<String> getConditionList() {
        return conditionList;
    }

    public void setConditionList(List<String> conditionList) {
        this.conditionList = conditionList;
    }

    public Map<String, Object> getKeyValueMap() {
        return keyValueMap;
    }

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

    public List<String> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<String> orderList) {
        this.orderList = orderList;
    }

    public BeanTransformer getTransformer() {
        return transformer;
    }

    public void setTransformer(BeanTransformer transformer) {
        this.transformer = transformer;
    }
}
