package com.zzwtec.jdbc.dao;

import com.zzwtec.third.common.OrderType;
import com.zzwtec.third.common.Page;
import com.zzwtec.third.common.SqlParamUtil;
import com.zzwtec.third.utils.ListUtil;
import com.zzwtec.third.utils.StringUtil;
import com.zzwtec.wechat.util.CommonUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * create by Jomchen on 2018/12/17
 */
public class BaseDao<T> {

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    /**
     * 全查询，一般不推荐使用，除非数据量不大
     */
    protected List<T> selectAll(String tableName, RowMapper<T> rowMapper) {
        String sql = "SELECT * FROM " + tableName;
        try {
            return jdbcTemplate.query(sql, rowMapper);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 获取一条记录
     */
    protected T selectOne(String tableName, String id, RowMapper<T> rowMapper) {
        if(StringUtil.isEmpty(id)){
            return null;
        }
        String sql = "SELECT * FROM " + tableName + " WHERE id = ?";
        try {
            return jdbcTemplate.queryForObject(sql, rowMapper, id);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    protected T selectOne(String sql, RowMapper<T> rowMapper, Object... params) {
        try {
            return jdbcTemplate.queryForObject(sql, rowMapper, params);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 根据sql获取单表记录
     */
    protected List<T> selectList(String sql, Class<T> elementType) {
        try {
            return jdbcTemplate.queryForList(sql, elementType);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 根据sql获取单表记录,支持sql预先处理语句参数
     */
    protected List<T> selectList(String sql, Object[] params, Class<T> elementType) {
        try {
            return jdbcTemplate.queryForList(sql, elementType, params);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    protected List<T> selectList(String sql, Object[] params, RowMapper<T> rowMapper) {
        try {
            return jdbcTemplate.query(sql, params, rowMapper);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    protected List<T> selectList(String sql, RowMapper<T> rowMapper, Object... params) {
        try {
            return jdbcTemplate.query(sql, rowMapper, params);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    long selectForNum(String sql, Object... params) {
        try {
            List<Long> count = jdbcTemplate.query(sql, new CountRowMapper(), params);
            if(count!=null && count.size()>0){
                return count.get(0);
            }
            return 0;
        }catch (EmptyResultDataAccessException e){
            return 0;
        }
    }

    /**
     * 更新单表数据
     */
    protected int update(String sql) {
        return jdbcTemplate.update(sql);
    }

    /**
     * 批量更新单表数据
     */
    protected int[] batchUpdate(String sql) {
        return jdbcTemplate.batchUpdate(sql);
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 更新数据
     */
    protected int update(String sql, Object... params) {
        return jdbcTemplate.update(sql, params);
    }

    /**
     * 批量更新单表数据,支持sql预处理语句参数
     */
    protected int[] batchUpdate(String sql, List<Object[]> batchArgs) {
        return jdbcTemplate.batchUpdate(sql, batchArgs);
    }

    /**
     * 添加单表数据
     */
    protected int insert(String sql, Object... params) {
        if (sql == null || !sql.toUpperCase().startsWith("INSERT")) {
            throw new RuntimeException("不是合法的添加数据sql语句: " + sql);
        }
        return jdbcTemplate.update(sql, params);
    }

    /**
     * 按id删除
     */
    protected int deleteById(String tableName, String id) {
        String sql = "DELETE FROM " + tableName + " WHERE id=?";
        return jdbcTemplate.update(sql, id);
    }
    protected int deleteByIds(String tableName, String[] ids) {
        if(ids==null || ids.length==0)return 0;
        String sql = "DELETE FROM " + tableName + " WHERE id IN ("+getSqlInStr(ids.length)+")";
        return jdbcTemplate.update(sql, (Object[])ids);
    }

    /**
     * 获取 SqlRowSet
     */
    protected SqlRowSet selectForRowSet(String sql, Object... args) {
        try {
            return jdbcTemplate.queryForRowSet(sql, args);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 获取row的数据，一般用于count
     */
    protected String getRowColumnValue(SqlRowSet row, String columnLabel) {
        return row.getString(columnLabel);
    }

    protected Page<T> selectPage(Class<T> entityClass,String tableName, Page<T> page, RowMapper<T> rowMapper) {
        if (pageCheckFailed(page)) {
            return new Page<>(0L, 0L, null);
        }

        StringBuilder sqlBuilder = new StringBuilder();
        StringBuilder countSqlBuilder = new StringBuilder();
        StringBuilder dataSqlBuilder = new StringBuilder();
        countSqlBuilder.append("SELECT ");
        if(page.getGroupByFields()!=null && page.getGroupByFields().length>0){
            countSqlBuilder.append("COUNT(DISTINCT ");
            for(int j=0; j < page.getGroupByFields().length; j++){
                if(j!=0){
                    countSqlBuilder.append(",");
                }
                String column = CommonUtil.field2Column(page.getGroupByFields()[j]);
                countSqlBuilder.append(column);
            }
            countSqlBuilder.append(") AS ids FROM ");
        } else {
            if(page.isDistinct() != null && page.isDistinct()){
                countSqlBuilder.append("DISTINCT ");
            }
            countSqlBuilder.append("COUNT(*) AS ids FROM ");
        }
        dataSqlBuilder.append("SELECT ");
        if (page.isDistinct() != null && page.isDistinct()) {
            dataSqlBuilder.append("DISTINCT ");
        }
        dataSqlBuilder.append("* FROM ");
        sqlBuilder.append(tableName);
        String[] conditions = page.getCondition();
        String[] searchFields = page.getSearchFields();
        String[] searchValues = page.getSearchValues();
        List<Object> valueList = new ArrayList<Object>();

        /*if (entityClass.getAnnotation(IgnoreDeletec.class) == null) {
            sqlBuilder.append(" WHERE deletec=0");
        } else {
            sqlBuilder.append(" WHERE 1=1");
        }*/
        sqlBuilder.append(" WHERE 1=1");

        StringBuilder nullSqlBuilder = new StringBuilder();
        if (conditions != null && conditions.length > 0) {
            for (int i = 0; i < conditions.length; i++) {
                String column = CommonUtil.field2Column(searchFields[i]);
                if ("=".equals(conditions[i]) || "!=".equals(conditions[i]) || ">".equals(conditions[i])
                        || ">=".equals(conditions[i]) || "<".equals(conditions[i]) || "<=".equals(conditions[i])) {
                    sqlBuilder.append(" AND ").append(column).append(" ").append(conditions[i]).append(" ?");
                } else if ("like".equalsIgnoreCase(conditions[i]) || "regexp".equalsIgnoreCase(conditions[i])) {
                    sqlBuilder.append(" AND ").append(column).append(" ").append(conditions[i].toUpperCase())
                            .append(" ?");
                } else if ("in".equalsIgnoreCase(conditions[i])) {
                    String[] ins = searchValues[i].split(",");
                    sqlBuilder.append(" AND ");
                    sqlBuilder.append(column);
                    sqlBuilder.append(" IN (");
                    sqlBuilder.append(getSqlInStr(ins.length));
                    sqlBuilder.append(")");

                    for (String item : ins) {
                        valueList.add(item);
                    }
                    continue;
                } else if ("is not".equalsIgnoreCase(conditions[i]) || "is".equalsIgnoreCase(conditions[i])) {
                    nullSqlBuilder.append(" AND ").append(column).append(" ").append(conditions[i]).append(" NULL");
                    continue;
                }
                valueList.add(searchValues[i]);
            }
        }
        if(nullSqlBuilder!=null && nullSqlBuilder.length()>0){
            sqlBuilder.append(nullSqlBuilder.toString());
        }
        countSqlBuilder.append(sqlBuilder.toString());
        String countSql = countSqlBuilder.toString();
        if (page.getGroupByFields() != null && page.getGroupByFields().length > 0) {
            sqlBuilder.append(" GROUP BY ");
            for (int j = 0; j < page.getGroupByFields().length; j++) {
                if (j != 0) {
                    sqlBuilder.append(",");
                }
                String column = CommonUtil.field2Column(page.getGroupByFields()[j]);
                sqlBuilder.append(column);
            }
        }
        if (page.getOrderField() != null && page.getOrderField().length() > 0) {
            sqlBuilder.append(" ORDER BY ");
            if (page.getOrderField().indexOf(",") > 0) {
                String[] orderFields = page.getOrderField().split(",");
                for (int i = 0; i < orderFields.length; i++) {
                    if (i != 0) {
                        sqlBuilder.append(",");
                    }
                    String column = CommonUtil.field2Column(orderFields[i]);
                    sqlBuilder.append(column);
                }
            } else {
                String column = CommonUtil.field2Column(page.getOrderField());
                sqlBuilder.append(column);
            }
            if (page.getOrderType() == OrderType.ASC) {
                sqlBuilder.append(" ASC");
            } else {
                sqlBuilder.append(" DESC");
            }
        }else{//如果参数中不带排序条件，默认按照id排序
            sqlBuilder.append(" ORDER BY id ASC");
        }
        int offset = (page.getPage() - 1) * page.getPageNum();
        sqlBuilder.append(" LIMIT ");
        sqlBuilder.append(page.getPageNum());
        sqlBuilder.append(" OFFSET ");
        sqlBuilder.append(offset);

        dataSqlBuilder.append(sqlBuilder.toString());
        String dataSql = dataSqlBuilder.toString();
        Object[] values = ListUtil.toArray(valueList);

        try {
            List<Long> count = jdbcTemplate.query(countSql, values, new CountRowMapper());
            List<T> list = jdbcTemplate.query(dataSql, values, rowMapper);
            long rows = count.get(0);
            long total = (long) Math.ceil(1.0 * rows /page.getPageNum());
            page.setData(list);
            page.setTotal(total);
            page.setRows(rows);
        }catch (EmptyResultDataAccessException e){
            page.setData(null);
            page.setTotal(0);
            page.setRows(0);
        }
        sqlBuilder = null;
        countSqlBuilder = null;
        dataSqlBuilder = null;
        return page;
    }

    protected String getSqlInStr(int size) {
        if (size > 0) {
            String[] str = new String[size];
            for (int i = 0; i < size; i++) {
                str[i] = "?";
            }
            return CommonUtil.join(str, ",");
        }
        return "";
    }

    /**
     * 提取page对象中的查询条件，返回符合SQL语句要求的条件字符串
     * 返回的条件语句以" AND ..."开头，首字符为空格
     */
    String getConditionSQLByPage(Page<?> page) {
        String[] searchFields = page.getSearchFields();
        String[] conditions = page.getCondition();
        String[] searchValues = page.getSearchValues();
        StringBuilder conditionsSql = new StringBuilder();
        if (conditions != null && conditions.length > 0) {
            for (int i = 0; i < searchFields.length; i++) {
                String column = CommonUtil.field2Column(searchFields[i]);
                if ("=".equals(conditions[i]) || "!=".equals(conditions[i]) || ">".equals(conditions[i])
                        || ">=".equals(conditions[i]) || "<".equals(conditions[i]) || "<=".equals(conditions[i])) {
                    conditionsSql.append(" AND ")
                            .append(column).append(" ")
                            .append(conditions[i]).append(" ")
                            .append(" ?");
                } else if ("LIKE".equalsIgnoreCase(conditions[i]) || "regexp".equalsIgnoreCase(conditions[i])) {
                    conditionsSql.append(" AND ")
                            .append(column).append(" ")
                            .append(conditions[i].toUpperCase()).append(" ")
                            .append(" ?");
                } else if ("IN".equalsIgnoreCase(conditions[i])) {
                    String[] ins = searchValues[i].split(",");
                    conditionsSql.append(" AND ");
                    conditionsSql.append(column);
                    conditionsSql.append(" IN (");
                    conditionsSql.append(getSqlInStr(ins.length));
                    conditionsSql.append(")");
                }
            }
        }
        return conditionsSql.toString();
    }

    /**
     * 提取page对象中的排序字段，返回符合SQL语句要求的排序字符串
     * 返回的排序语句以" ORDER BY "开头，首字符为空格
     */
    String getOrderBySQLByPage(Page<?> page) {
        StringBuilder orderBySql = new StringBuilder();
        if (page.getOrderField() != null && page.getOrderField().length() > 0) {
            orderBySql.append(" ORDER BY ");
            if (page.getOrderField().indexOf(",") > 0) {
                String[] orderFields = page.getOrderField().split(",");
                orderBySql.append(CommonUtil.field2Column(orderFields[0]));
                for (int i = 1; i < orderFields.length; i++) {
                    orderBySql.append(",");
                    orderBySql.append(CommonUtil.field2Column(orderFields[i]));
                }
            } else {
                String column = CommonUtil.field2Column(page.getOrderField());
                orderBySql.append(column);
            }
            if (page.getOrderType() == OrderType.ASC) {
                orderBySql.append(" ASC");
            } else {
                orderBySql.append(" DESC");
            }
        }
        return orderBySql.toString();
    }

    /**
     * 提取page对象中的搜索值，返回Object[]数组
     */
    Object[] getSearchValuesByPage(Page<?> page) {
        List<Object> searchValuesList = new ArrayList<>();
        String[] conditions = page.getCondition();
        String[] searchValues = page.getSearchValues();
        if (conditions != null && conditions.length > 0) {
            for (int i = 0; i < conditions.length; i++) {
                if ("IN".equalsIgnoreCase(conditions[i])) {
                    String[] ins = searchValues[i].split(",");
                    searchValuesList.addAll(Arrays.asList(ins));
                } else {
                    searchValuesList.add(searchValues[i]);
                }
            }
        }
        return SqlParamUtil.convertDataType(searchValuesList.toArray());
    }

    /**
     * String[]类型转换为Object[]类型
     */
    Object[] object(String[] str1, int i1) {
        Object[] obj;
        if (StringUtil.notEmpty(i1)) {
            obj = new Object[str1.length + i1];
        } else {
            obj = new Object[str1.length];
        }
        int i = 0;
        for (String s : str1) {
            obj[i++] = s;
        }
        return obj;
    }

    /**
     * 处理多个String[]合并转换为一个Object[]
     */
    Object[] objects(String[] str1, String[] str2, int i1) {
        Object[] obj = new Object[str1.length + str2.length + i1];
        int i = 0;
        for (String str : str1) {
            obj[i++] = str;
        }
        for (String s : str2) {
            obj[i++] = s;
        }
        return obj;
    }

    /**
     * 检查page对象是否无效
     * @return true：无效；false：有效
     */
    boolean pageCheckFailed(Page<?> page) {
        return (page == null || page.getPage() <= 0 || page.getPageNum() <= 0
                || !((page.getCondition() == null
                && page.getSearchFields() == null
                && page.getSearchValues() == null)
                || (page.getCondition() != null
                && page.getSearchFields() != null
                && page.getSearchValues() != null
                && page.getCondition().length == page.getSearchFields().length
                && page.getSearchFields().length == page.getSearchValues().length)));
    }
}

class CountRowMapper implements RowMapper<Long> {
    @Override
    public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
        return rs.getLong("ids");
    }

}