package cn.com.qmth.stmms.ms.commons.utils;

import org.apache.poi.ss.formula.functions.T;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description: sql工具类
 * @Author: wangliang
 * @Date: 2018/10/27
 */
@Component("sqlUtil")
public class SqlUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(SqlUtil.class);

    @Autowired
    private EntityManager entityManager;

    /**
     * 执行sql 把结果转换成map，支持分页
     *
     * @param sql
     * @param curPage
     * @param pageSize
     * @param arguments
     * @return
     */
    public PageInfo execSqlForMapPageInfo(String sql, Integer curPage, Integer pageSize, Object... arguments) {
        if (sql == null || curPage == null || pageSize == null || arguments == null) {
            throw new NullPointerException();
        }
        return (PageInfo) execSqlTrans(sql, null, 1, true, curPage, pageSize, arguments);
    }

    /**
     * 执行sql 把结果转换成map
     *
     * @param sql
     * @param arguments
     * @return
     */
    public List<T> execSqlForMap(String sql, Object... arguments) {
        if (sql == null || arguments == null) {
            throw new NullPointerException();
        }
        Query query = (Query) execSqlTrans(sql, null, 1, false, null, null, arguments);
        return query.getResultList();
    }

    /**
     * 执行sql 把结果转换成map
     *
     * @param sql
     * @param arguments
     * @return
     */
    public List<T> execSqlForMapNative(String sql, Object... arguments) {
        if (sql == null || arguments == null) {
            throw new NullPointerException();
        }
        Query query = (Query) execSqlTrans(sql, null, 1, arguments);
        return query.getResultList();
    }

    /**
     * 执行sql 把结果按顺序排进List，支持分页
     *
     * @param sql
     * @param curPage
     * @param pageSize
     * @param arguments
     * @return
     */
    public PageInfo execSqlForListPageInfo(String sql, Integer curPage, Integer pageSize, Object... arguments) {
        if (sql == null || curPage == null || pageSize == null || arguments == null) {
            throw new NullPointerException();
        }
        return (PageInfo) execSqlTrans(sql, null, 2, true, curPage, pageSize, arguments);
    }

    /**
     * 执行sql 把结果按顺序排进List
     *
     * @param sql
     * @param arguments
     * @return
     */
    public List<T> execSqlForList(String sql, Object... arguments) {
        if (sql == null || arguments == null) {
            throw new NullPointerException();
        }
        Query query = (Query) execSqlTrans(sql, null, 2, false, null, null, arguments);
        return query.getResultList();
    }

    /**
     * 执行sql 把结果通过setter方法注入到指定的对像属性中，支持分页
     *
     * @param sql
     * @param cla
     * @param curPage
     * @param pageSize
     * @param arguments
     * @return
     */
    public PageInfo execSqlForAliasToBeanPageInfo(String sql, Class cla, Integer curPage, Integer pageSize, Object... arguments) {
        if (sql == null || cla == null || curPage == null || pageSize == null || arguments == null) {
            throw new NullPointerException();
        }
        return (PageInfo) execSqlTrans(sql, cla, 3, true, curPage, pageSize, arguments);
    }

    /**
     * 执行sql 把结果通过setter方法注入到指定的对像属性中
     *
     * @param sql
     * @param cla
     * @param arguments
     * @return
     */
    public List<T> execSqlForAliasToBean(String sql, Class cla, Object... arguments) {
        if (sql == null || cla == null || arguments == null) {
            throw new NullPointerException();
        }
        Query query = (Query) execSqlTrans(sql, cla, 3, false, null, null, arguments);
        return query.getResultList();
    }

    /**
     * 执行sql 把结果转换成map
     *
     * @param sql
     * @return
     */
    public List<T> execSqlForMap(String sql) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

    /**
     * 执行sql 把结果按顺序排进List
     *
     * @param sql
     * @return
     */
    public List<T> execSqlForList(String sql) {
        Query query = entityManager.createNativeQuery(sql);
        return query.getResultList();
    }

    /**
     * sql 拼接and条件
     *
     * @param sql
     * @param fields
     * @param fieldValues
     * @param opers
     * @return
     */
    public String sqlConditionAnd(String sql, String[] fields, Object[] fieldValues, String[] opers) {
        if (sql == null || fields == null || fieldValues == null || opers == null) {
            throw new NullPointerException();
        }
        return sqlConditionTrans(sql, "and", fields, fieldValues, opers);
    }

    /**
     * sql findInSetAnd
     *
     * @param sql
     * @param field
     * @param fieldValue
     * @return
     */
    public String sqlFindInSetAnd(String sql, String field, String fieldValue) {
        if (sql == null || field == null || fieldValue == null) {
            throw new NullPointerException();
        }
        return new StringBuffer(sql).append(" FIND_IN_SET ").append("(" + fieldValue + "," + field + ")").toString();
    }

    /**
     * sql findInSetOr
     *
     * @param sql
     * @param field
     * @param fieldValue
     * @return
     */
    public String sqlFindInSetOr(String sql, String field, String fieldValue) {
        if (sql == null || field == null || fieldValue == null) {
            throw new NullPointerException();
        }
        return new StringBuffer(sql).append(" or FIND_IN_SET ").append("(" + fieldValue + "," + field + ")").toString();
    }

    /**
     * sql 拼接or条件
     *
     * @param sql
     * @param fields
     * @param fieldValues
     * @param opers
     * @return
     */
    public String sqlConditionOr(String sql, String[] fields, Object[] fieldValues, String[] opers) {
        if (sql == null || fields == null || fieldValues == null || opers == null) {
            throw new NullPointerException();
        }
        return sqlConditionTrans(sql, "or", fields, fieldValues, opers);
    }

    /**
     * sql 排序
     *
     * @param sql
     * @param field
     * @return
     */
    public String sqlOrderBy(String sql, String field) {
        if (sql == null || field == null) {
            throw new NullPointerException();
        }
        return new StringBuffer(sql).append(" order by ").append(" " + field + " ").toString();
    }

    /**
     * sql 排序 降序
     *
     * @param sql
     * @param field
     * @return
     */
    public String sqlOrderByDesc(String sql, String field) {
        if (sql == null || field == null) {
            throw new NullPointerException();
        }
        return new StringBuffer(sql).append(" order by ").append(" " + field + " ").append(" desc ").toString();
    }

    /**
     * sql group by
     *
     * @param sql
     * @param field
     * @return
     */
    public String sqlGroupBy(String sql, String field) {
        if (sql == null || field == null) {
            throw new NullPointerException();
        }
        return new StringBuffer(sql).append(" group by ").append(" " + field + " ").toString();
    }

    /**
     * sql sqlLimit
     *
     * @param sql
     * @param curPage
     * @param pageSize
     * @return
     */
    public String sqlLimit(String sql, Integer curPage, Integer pageSize) {
        return new StringBuffer(sql).append(" limit ").append(curPage).append(",").append(pageSize).toString();
    }

    /**
     * sql union
     *
     * @param sql
     * @return
     */
    public String sqlUnion(String sql) {
        return new StringBuffer(sql).append(" union ").toString();
    }

    /**
     * sql union all
     *
     * @param sql
     * @return
     */
    public String sqlUnionAll(String sql) {
        return new StringBuffer(sql).append(" union all ").toString();
    }

    /**
     * sql 条件转换
     *
     * @param sql
     * @param conditionStr
     * @param fields
     * @param fieldValues
     * @param opers
     * @return
     */
    private String sqlConditionTrans(String sql, String conditionStr, String[] fields, Object[] fieldValues, String[] opers) {
        StringBuffer stringBuffer = new StringBuffer(sql);
        try {
            for (int i = 0; i < fields.length; i++) {
                if (fieldValues[i] instanceof String) {
                    stringBuffer.append(" " + conditionStr + " ").append(" " + fields[i] + " ").append(" " + opers[i] + " ").append("'" + fieldValues[i] + "'");
                } else {
                    stringBuffer.append(" " + conditionStr + " ").append(" " + fields[i] + " ").append(" " + opers[i] + " ").append(fieldValues[i]);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return stringBuffer.toString();
    }

    /**
     * sql 占位符替换
     *
     * @param sql
     * @param str
     * @param fromIndex
     * @param count
     * @param arguments
     * @return
     */
    private String searchStrAndRep(String sql, String str, int fromIndex, int count, Object... arguments) {
        if (sql == null || str == null || arguments == null) {
            throw new NullPointerException();
        }
        try {
            int index = sql.indexOf(str, fromIndex);
            if (index != -1) {
                StringBuilder stringBuilder = new StringBuilder(sql);
                if (arguments[count] instanceof String) {
                    stringBuilder = new StringBuilder(stringBuilder.substring(0, index)).append(stringBuilder.replace(0, index + 1, "'" + arguments[count] + "'"));
                } else {
                    stringBuilder = new StringBuilder(stringBuilder.substring(0, index)).append(stringBuilder.replace(0, index + 1, String.valueOf(arguments[count])));
                }
                return searchStrAndRep(stringBuilder.toString(), str, index + 1, count + 1, arguments);
            } else {
                LOGGER.info("execSql:" + sql);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return sql;
    }

    /**
     * sql 执行count
     *
     * @param sql
     * @param args
     * @return
     */
    private Integer getTotalCount(String sql, List args) {
        try {
            String countSql = getCountSqlWithOutDistinct(sql);
            Query query = entityManager.createNativeQuery(countSql);
            for (int i = 0; i < args.size(); i++) {
                query.setParameter(i + 1, args.get(i));
            }
            return Integer.parseInt(query.getSingleResult().toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * sql count拼接
     *
     * @param sql
     * @return
     * @throws Exception
     */
    private String getCountSqlWithOutDistinct(String sql) throws Exception {
        String newsql = null;
        String countCondition = " count(*) ";
        if (sql.toLowerCase().contains("distinct")) {
//            throw new Exception("不能处理空字符串和包含distinct的sql");
            countCondition = " count(distinct t.id) ";
        }
        try {
            int start = sql.toLowerCase().indexOf("select");
            int end = sql.toLowerCase().lastIndexOf("from");
            newsql = sql.substring(0, start + 6) + countCondition + sql.substring(end);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newsql;
    }

    /**
     * sql 结果trans转换
     *
     * @param sql
     * @param cla
     * @param type
     * @param isShowPage
     * @param curPage
     * @param pageSize
     * @param arguments
     * @return
     */
    private Object execSqlTrans(String sql, Class cla, int type, boolean isShowPage, Integer curPage, Integer pageSize, Object... arguments) {
        Query query = null;
        try {
            List<Object> args = new ArrayList<>();
            for (Object s : arguments) {
                args.add(s);
            }
            Integer total = getTotalCount(sql, args);
            total = total == null ? 0 : total;
            query = entityManager.createNativeQuery(sql);
            switch (type) {
                case 2:
                    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.TO_LIST);
                    break;
                case 3:
                    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(cla));
                    break;
                default:
                    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                    break;
            }
            for (int i = 0; i < args.size(); i++) {
                query.setParameter(i + 1, args.get(i));
            }
            sqlPrint(sql, arguments);
            if (isShowPage) {
                //设置分页信息
                PageInfo pageInfo = new PageInfo();
                query.setMaxResults(pageSize);
                query.setFirstResult(curPage * pageSize);
                pageInfo.setTotal(total);
                pageInfo.setList(query.getResultList());
                return pageInfo;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return query;
    }

    /**
     * sql 结果trans转换
     *
     * @param sql
     * @param cla
     * @param type
     * @param arguments
     * @return
     */
    private Object execSqlTrans(String sql, Class cla, int type, Object... arguments) {
        Query query = null;
        try {
            List<Object> args = new ArrayList<>();
            for (Object s : arguments) {
                args.add(s);
            }
            query = entityManager.createNativeQuery(sql);
            switch (type) {
                case 2:
                    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.TO_LIST);
                    break;
                case 3:
                    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(cla));
                    break;
                default:
                    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                    break;
            }
            for (int i = 0; i < args.size(); i++) {
                query.setParameter(i + 1, args.get(i));
            }
            sqlPrint(sql, arguments);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return query;
    }

    /**
     * sql打印,替代占位符
     *
     * @param sql
     * @param arguments
     * @return
     */
    public String sqlPrint(String sql, Object... arguments) {
        try {
            int lastIndexOrder = sql.lastIndexOf("?");
            if (lastIndexOrder == -1) {
                LOGGER.error("没有?参数");
            } else {
                return searchStrAndRep(sql, "?", 0, 0, arguments);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sql;
    }
}