package com.infoepoch.pms.dispatchassistant.common.utils;



import com.infoepoch.pms.dispatchassistant.common.exception.ValidationException;

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

/**
 * @program: scm
 * @description: sqlUtil
 * @author: zhangyd
 * @create: 2019-11-18 10:36
 **/
public class OracleUtils {

    /**
     * @Description: 拼接sql
     * @Param: [sql, andMap]
     * @Author: zhangyd
     */
    public static List<Object> combinationSql(StringBuffer sql, Map<String, Object> andMap, int pageIndex, int pageSize, String orderByColumn,boolean isDesc) {
        List<Object> list = combinationSql(sql, andMap);
        if (StringUtils.isNotBlank(orderByColumn)) {
            if(isDesc)
              sql.append(" ORDER BY " + orderByColumn + " DESC");
            else
                sql.append(" ORDER BY " + orderByColumn);
        }
        if (pageIndex >= 0 && pageSize > 0) {
            // 切换分页查询
            // sql.insert(0, "SELECT * FROM(SELECT PAGE_TEMP.*, ROWNUM RN FROM ( ");
            // sql.append(" ) PAGE_TEMP ) WHERE RN <= ? AND RN >= ? ");
            sql.insert(0, "SELECT TEMP.* FROM (SELECT INNER_TEMP.*, ROWNUM RN FROM ( ");
            sql.append(" ) INNER_TEMP WHERE ROWNUM <= ? ) TEMP WHERE TEMP.RN >= ? ");
            list.add(pageIndex * pageSize);
            list.add((pageIndex - 1) * pageSize + 1);
        }
        return list;
    }

    /**
     * @Description: 拼接sql
     * @Param: [sql, andMap]
     * @Author: zhangyd
     */
    public static List<Object> combinationSql(StringBuffer sql, Map<String, Object> andMap, int pageIndex, int pageSize, String orderByColumn) {
        List<Object> list = combinationSql(sql, andMap);
        if (StringUtils.isNotBlank(orderByColumn)) {
            sql.append(" ORDER BY " + orderByColumn + " DESC");
        }
        if (pageIndex >= 0 && pageSize > 0) {
            // 切换分页查询
            // sql.insert(0, "SELECT * FROM(SELECT PAGE_TEMP.*, ROWNUM RN FROM ( ");
            // sql.append(" ) PAGE_TEMP ) WHERE RN <= ? AND RN >= ? ");
            sql.insert(0, "SELECT TEMP.* FROM (SELECT INNER_TEMP.*, ROWNUM RN FROM ( ");
            sql.append(" ) INNER_TEMP WHERE ROWNUM <= ? ) TEMP WHERE TEMP.RN >= ? ");
            list.add(pageIndex * pageSize);
            list.add((pageIndex - 1) * pageSize + 1);
        }
        return list;
    }

    /**
     * @Description: 拼接sql
     * @Param: [sql, andMap]
     * @Author: zhangyd
     */
    public static List<Object> combinationSql(StringBuffer sql, Map<String, Object> andMap, int pageIndex, int pageSize) {
        List<Object> list = combinationSql(sql, andMap);
        if (pageIndex >= 0 && pageSize > 0) {
            // 切换分页查询
            // sql.insert(0, "SELECT * FROM(SELECT PAGE_TEMP.*, ROWNUM RN FROM ( ");
            // sql.append(" ) PAGE_TEMP ) WHERE RN <= ? AND RN >= ? ");
            sql.insert(0, "SELECT TEMP.* FROM (SELECT INNER_TEMP.*, ROWNUM RN FROM ( ");
            sql.append(" ) INNER_TEMP WHERE ROWNUM <= ? ) TEMP WHERE TEMP.RN >= ? ");
            list.add(pageIndex * pageSize);
            list.add((pageIndex - 1) * pageSize + 1);
        }
        return list;
    }

    /**
     * @Description: 拼接sql
     * @Param: [sql, andMap]
     * @Author: zhangyd
     */
    public static List<Object> combinationSql(StringBuffer sql, Map<String, Object> andMap, String orderByColumn, boolean isDesc) {
        List<Object> list = combinationSql(sql, andMap);
        if (StringUtils.isBlank(orderByColumn))
            return list;
        if (isDesc)
            sql.append(" ORDER BY " + orderByColumn + " DESC");
        else
            sql.append(" ORDER BY " + orderByColumn);
        return list;
    }

    /**
     * @Description: 拼接sql
     * @Param: [sql, andMap]
     * @Author: zhangyd
     */
    public static List<Object> combinationSql(StringBuffer sql, Map<String, Object> andMap) {
        StringBuilder formatSql = new StringBuilder();
        //参数集合
        List<Object> params = new ArrayList<>();
        if (andMap == null || andMap.size() == 0)
            return params;
        //参数占位符集合
        List<String> placeholders = new ArrayList<>();
        for (Map.Entry<String, Object> param : andMap.entrySet()) {
            //占位符个数
            int placeholderCount = StringTool.searchCharCount(param.getKey(), '?');
            //1、判断数据类型,如果是自定义的类，则抛出异常
            if (param.getValue() != null && param.getValue().getClass().getClassLoader() != null)
                throw new ValidationException("参数类型非法。");
            //2、判断没有占位符
            if (placeholderCount == 0) {
                placeholders.add(param.getKey());
                continue;
            }
            //3、判断单个占位符,且不是集合类型
            if (placeholderCount == 1 && !(param.getValue() instanceof List)) {
                placeholders.add(param.getKey());
                params.add(param.getValue());
                continue;
            }
            //4、判断有占位符,且是集合类型
            if (placeholderCount >= 1 && param.getValue() instanceof List) {
                List<Object> values = castList(param.getValue(), Object.class);
                placeholders.add(param.getKey());
                params.addAll(values);
                continue;
            }
            throw new ValidationException("参数类型非法。");
        }
        formatSql.append(StringUtils.join(placeholders, " AND "));
        if(sql.toString().toUpperCase().contains(" WHERE "))
            sql.append(0 != placeholders.size() ? " AND " + formatSql.toString() : "");
        else
            sql.append(0 != placeholders.size() ? " WHERE " + formatSql.toString() : "");
        return params;
    }

    /**
     * @Description: 复制参数值个数
     * @Author: zhangyd
     */
    public static List<Object> cloneParams(Object param, int size) {
        List<Object> params = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            params.add(param);
        }
        return params;
    }

    /**
     * @Description: 拼接In查询条件
     * @Param: [params]
     * @Author: zhangyd
     */
    public static <T> String placeholderIn(List<T> params) {
        if (params == null || params.size() == 0)
            return "";
        String[] strings = new String[params.size()];
        for (int i = 0; i < params.size(); i++) {
            strings[i] = " ? ";
        }
        return StringUtils.join(strings, ", ");
    }

    /**
     * @Description: 拼接In查询条件
     * @Param: [columnName, params]
     * @Author: zhangyd
     */
    public static String placeholderIn(String columnName, Object params) {
        if (params == null)
            return " 1 = 0 ";
        if (params instanceof List) {
            List<Object> values = castList(params, Object.class);
            return placeholderIn(columnName, values);
        }
        return " " + columnName + " IN (" + params.toString() + ")";
    }

    /**
     * @Description: 拼接not In查询条件
     * @Param: [columnName, params]
     * @Author: zhangyd
     */
    public static String placeholderNotIn(String columnName, Object params) {
        if (params == null)
            return " 1 = 0 ";
        if (params instanceof List) {
            List<Object> values = castList(params, Object.class);
            return placeholderIn(columnName, values);
        }
        return " " + columnName + " NOT IN (" + params.toString() + ")";
    }

    /**
     * @Description: 拼接In查询条件
     * @Param: [columnName, params]
     * @Author: zhangyd
     */
    public static <T> String placeholderIn(String columnName, List<T> params) {
        if (params == null || params.size() == 0)
            return " 1 = 0 ";
        List<String> sqls = new ArrayList<>();
        splitList(sqls, columnName, params.size());
        if (sqls.size() == 1)
            return StringUtils.join(sqls, " OR ");
        return " (" + StringUtils.join(sqls, " OR ") + " )";
    }

    /**
     * @Description: 拆分 IN 大于1000
     * @Param: [sqls, columnName, size]
     * @Author: zhangyd
     */
    private static void splitList(List<String> sqls, String columnName, int size) {
        String[] strings = new String[size];
        if (size - 1000 > 0) {
            strings = new String[1000];
            for (int i = 0; i < 1000; i++) {
                strings[i] = " ? ";
            }
            sqls.add(" " + columnName + " IN (" + StringUtils.join(strings, ", ") + ")");
            splitList(sqls, columnName, size - 1000);
        } else {
            for (int i = 0; i < size; i++) {
                strings[i] = " ? ";
            }
            sqls.add(" " + columnName + " IN (" + StringUtils.join(strings, ", ") + ")");
            }
        }

    /**
     * 忽略大小写判断数组中是否包含某元素
     *
     * @param strings 待判断数组
     * @param str     是否包含的元素
     * @return 包含 -> true，不包含 -> false
     */
    private static boolean ignoreCase(String[] strings, String str) {
        for (String string : strings) {
            if (string.equalsIgnoreCase(str))
                return true;
        }
        return false;
    }

    /**
     * @Description: Object 转 list
     * @Param: [obj, clazz]
     * @Author: zhangyd
     */
    private static <T> List<T> castList(Object obj, Class<T> clazz) {
        List<T> result = new ArrayList<T>();
        if (obj instanceof List<?>) {
            for (Object o : (List<?>) obj) {
                result.add(clazz.cast(o));
            }
            return result;
        }
        return null;
    }

    /**
     * @Description: 数据库类型转java类型
     * @Param: [dataType, dataPrecision, dataScale]
     * @Author: zhangyd
     */
    public static String convertType(String dataType, Integer dataPrecision, Integer dataScale) {
        dataType = dataType.toLowerCase();
        if (dataType.contains("varchr") || dataType.contains("nclob"))
            return "String";
        if (dataType.contains("timestamp") || dataType.contains("date"))
            return "Date";
        if (dataType.contains("number")) {
            if (dataPrecision == 1 && dataScale == 0)
                return "Boolean";
            if (dataPrecision > 1 && dataScale > 0)
                return "BigDecimal";
            if (dataPrecision > 1 && dataScale == 0)
                return "Integer";
        }
        return "String";
    }
}

