package com.code2roc.fastface.db;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.code2roc.fastface.util.CommonUtil;
import com.code2roc.fastface.util.ConvertOp;
import com.code2roc.fastface.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.context.annotation.ScopedProxyMode;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.*;

@Component
@Scope(value = "prototype", proxyMode = ScopedProxyMode.TARGET_CLASS)
public class CommonDTO {
    @Autowired
    private JDBCDTO jdbcDTO;
    private Map<String, Object> paramMap;
    private String newGuid;

    public CommonDTO() {
        paramMap = new HashMap<>();
        newGuid = UUID.randomUUID().toString();
    }

    public String getNewGuid() {
        return newGuid;
    }

    public void setNewGuid(String newGuid) {
        this.newGuid = newGuid;
    }

    public Page selectPage(String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
        int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
        int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
        if (pageIndex <= 0)
            pageIndex = 1;
        return selectPage(tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
    }

    public Page selectPage(String tableName, String selectCols, String strWhere, String strOrder, int PageIndex, int PageSize, HashMap<String, Object> paramList) {
        Page page = new Page();
        String sql = getPageSQL(tableName, selectCols, strWhere, strOrder, PageIndex, PageSize);
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
        page.setRows(result);
        int total = selectCount(tableName, strWhere, paramList);
        page.setTotal(total);
        return page;
    }

    public List<LinkedHashMap<String, Object>> selectPageList(String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
        int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
        int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
        if (pageIndex <= 0)
            pageIndex = 1;
        return selectPageList(tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
    }

    public List<LinkedHashMap<String, Object>> selectPageList(String tableName, String selectCols, String strWhere, String strOrder, int PageIndex, int PageSize, HashMap<String, Object> paramList) {
        String sql = getPageSQL(tableName, selectCols, strWhere, strOrder, PageIndex, PageSize);
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        return result;
    }

    public <T> List<T> selectPageList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
        int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
        int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
        if (pageIndex <= 0)
            pageIndex = 1;
        return selectPageList(clazz, tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
    }

    public <T> List<T> selectPageList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strOrder, int PageIndex, int PageSize, HashMap<String, Object> paramList) {
        String sql = getPageSQL(tableName, selectCols, strWhere, strOrder, PageIndex, PageSize);
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> findresult = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        List<T> result = new ArrayList<>();
        for (LinkedHashMap<String, Object> map : findresult) {
            try {
                T obj = clazz.newInstance();
                result.add(ConvertOp.convertLinkHashMapToBean(map, obj));
            } catch (Exception e) {

            }
        }
        return result;
    }

    public List<LinkedHashMap<String, Object>> selectList(String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
        String sql = "";
        sql = " select " + selectCols + " from " + tableName + " where  " + strWhere;
        if (!StringUtil.isEmpty(strOrder)) {
            sql += " order by " + strOrder;
        }
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        paramMap.put("sql", sql);
        List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        return result;
    }

    public <T> List<T> selectList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
        String sql = "";
        sql = " select " + selectCols + " from " + tableName + " where  " + strWhere;
        if (!StringUtil.isEmpty(strOrder)) {
            sql += " order by " + strOrder;
        }
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> findresult = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        List<T> result = new ArrayList<>();
        for (LinkedHashMap<String, Object> map : findresult) {
            try {
                T obj = clazz.newInstance();
                result.add(ConvertOp.convertLinkHashMapToBean(map, obj));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    public int selectCount(String tableName, String strWhere, HashMap<String, Object> paramList) {
        String sql = "select count(*) from " + tableName;
        if (!StringUtil.isEmpty(strWhere)) {
            sql += " where " + strWhere;
        }
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        int total = ConvertOp.convert2Int(jdbcDTO.executeScar(sql,paramMap));
        reset();
        return total;
    }

    public LinkedHashMap<String, Object> selectOne(String tableName, String selectCols, String strWhere, HashMap<String, Object> paramList) {
        List<LinkedHashMap<String, Object>> result = selectList(tableName, selectCols, strWhere, "", paramList);
        if (result.size() > 0) {
            return result.get(0);
        } else {
            return null;
        }
    }

    public <T> T selectOne(Class<T> clazz, String tableName, String selectCols, String strWhere, HashMap<String, Object> paramList) {
        List<T> result = selectList(clazz, tableName, selectCols, strWhere, "", paramList);
        if (result.size() > 0) {
            return result.get(0);
        } else {
            return null;
        }
    }

    public <T> T selectOne(Class<T> clazz, String tableName, String selectCols, String filedName, String fieldValue) {
        String sql = filedName + " = #{" + filedName + "}";
        paramMap.put(filedName, fieldValue);
        List<T> result = selectList(clazz, tableName, selectCols, sql, "", null);
        reset();
        if (result.size() > 0) {
            return result.get(0);
        } else {
            return null;
        }
    }

    public List<LinkedHashMap<String, Object>> selectGroup(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList) {
        String sql = getGroupSQL(tableName, selectCols, strGroupBy, strWhere, strOrder);
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        return result;
    }

    public List<LinkedHashMap<String, Object>> selectGroupList(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList) {
        String sql = getGroupListSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, "");
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        return jdbcDTO.findListByParam(sql, paramMap);
    }

    public List<LinkedHashMap<String, Object>> selectGroupList(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList, String maxTableIDName) {
        String sql = getGroupListSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, maxTableIDName);
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        return jdbcDTO.findListByParam(sql, paramMap);
    }

    public List<LinkedHashMap<String, Object>> selectGroupPageList(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList) {
        int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
        int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
        if (pageIndex <= 0)
            pageIndex = 1;
        int BeginIndex = pageSize * (pageIndex - 1) + 1;
        String sql = getGroupPageSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, pageIndex, pageSize, "");
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        return result;
    }

    public List<LinkedHashMap<String, Object>> selectGroupPageList(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList, String maxTableIDName) {
        int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
        int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
        if (pageIndex <= 0)
            pageIndex = 1;
        int BeginIndex = pageSize * (pageIndex - 1) + 1;
        String sql = getGroupPageSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, pageIndex, pageSize, maxTableIDName);
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        return result;
    }

    public <T> List<T> selectGroupPageList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList) {
        int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
        int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
        if (pageIndex <= 0)
            pageIndex = 1;
        int BeginIndex = pageSize * (pageIndex - 1) + 1;
        String sql = getGroupPageSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, pageIndex, pageSize, "");
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> findresult = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        List<T> result = new ArrayList<>();
        for (LinkedHashMap<String, Object> map : findresult) {
            try {
                T obj = clazz.newInstance();
                result.add(ConvertOp.convertLinkHashMapToBean(map, obj));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    public <T> List<T> selectGroupPageList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList, String maxTableIDName) {
        int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
        int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
        if (pageIndex <= 0)
            pageIndex = 1;
        int BeginIndex = pageSize * (pageIndex - 1) + 1;
        String sql = getGroupPageSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, pageIndex, pageSize, maxTableIDName);
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        List<LinkedHashMap<String, Object>> findresult = jdbcDTO.findListByParam(sql, paramMap);
        reset();
        List<T> result = new ArrayList<>();
        for (LinkedHashMap<String, Object> map : findresult) {
            try {
                T obj = clazz.newInstance();
                result.add(ConvertOp.convertLinkHashMapToBean(map, obj));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    public int selectGroupCount(String tableName, String strWhere, String strGroupBy, HashMap<String, Object> paramList) {
        String sql = "";
        sql = getGroupCountSQL(tableName, strWhere, strGroupBy, "");
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        int total = ConvertOp.convert2Int(jdbcDTO.executeScar(sql,paramMap));
        reset();
        return total;
    }

    public int selectGroupCount(String tableName, String strWhere, String strGroupBy, HashMap<String, Object> paramList, String maxTableIDName) {
        String sql = "";
        sql = getGroupCountSQL(tableName, strWhere, strGroupBy, maxTableIDName);
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (String key : paramList.keySet()) {
                paramMap.put(key, paramList.get(key));
            }
        }
        int total = ConvertOp.convert2Int(jdbcDTO.executeScar(sql,paramMap));
        reset();
        return total;
    }

    public void executeSQL(String sql, HashMap<String, Object> paramList) {
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (Map.Entry<String, Object> entry : paramList.entrySet()) {
                paramMap.put(entry.getKey(), entry.getValue());
            }
        }
        jdbcDTO.executeSQL(sql,paramMap);
        reset();
    }

    public Object executeSQLToQuery(String sql, HashMap<String, Object> paramList) {
        paramMap.put("sql", sql);
        if (null != paramList) {
            for (Map.Entry<String, Object> entry : paramList.entrySet()) {
                paramMap.put(entry.getKey(), entry.getValue());
            }
        }
        Object result = jdbcDTO.executeScar(sql,paramMap);
        reset();
        return result;
    }

    public void reset() {
        this.paramMap.clear();
    }

    ///region 分页语句
    public String getPageSQL(String tableName, String selectCols, String strWhere, String strOrder, int pageIndex, int pageSize) {
        String sql = " select " + selectCols + " from " + tableName + " where  " + strWhere;
        if (!StringUtil.isEmpty(strOrder)) {
            sql += " order by " + strOrder;
        }
        sql += " LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize + ";";

        return sql;
    }

    public String getGroupPageSQL(String tableName, String selectCols, String groupBy, String strWhere, String strOrder, int pageIndex, int pageSize, String maxTableIDName) {
        if (StringUtil.isEmpty(maxTableIDName)) {
            maxTableIDName = "id";
        }
        String sql = "";
        String groupTableName = "(select * from " + tableName + " where " + maxTableIDName + " in (select max(" + maxTableIDName + ") from " + tableName + " where " + strWhere + " group by " + groupBy + ")) aGr ";
        sql = getPageSQL(groupTableName, selectCols, strWhere, strOrder, pageIndex, pageSize);
        return sql;
    }

    public String getGroupCountSQL(String tableName, String strWhere, String groupBy, String maxTableIDName) {
        String sql = "";
        if (StringUtil.isEmpty(maxTableIDName)) {
            maxTableIDName = "id";
        }
        sql = "select count(1) from " + tableName + " where " + maxTableIDName + " in (select max(" + maxTableIDName + ") from " + tableName + " where " + strWhere + " group by " + groupBy + ")";
        return sql;
    }

    public String getGroupListSQL(String tableName, String selectCols, String groupBy, String strWhere, String strOrder, String maxTableIDName) {
        if (StringUtil.isEmpty(maxTableIDName)) {
            maxTableIDName = "id";
        }
        String sql = "";
        sql = "select " + selectCols + " from " + tableName + " where " + maxTableIDName + " in (select max(" + maxTableIDName + ") from " + tableName + " where " + strWhere + " group by " + groupBy + ")";
        if (!StringUtil.isEmpty(strOrder)) {
            sql += " order by " + strOrder;
        }
        return sql;
    }

    public String getGroupSQL(String tableName, String selectCols, String groupBy, String strWhere, String strOrder) {
        String sql = "select " + selectCols + " from " + tableName + " where " + strWhere + " group by " + groupBy + "";
        if (!StringUtil.isEmpty(strOrder)) {
            sql += " order by " + strOrder;
        }
        return sql;
    }
    ///endregion
}