package com.ruoyi.system.dto;

import com.ruoyi.system.domain.tool.PublicSQLConfig;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.MessageFormat;
import java.util.List;
import java.util.Map;

public class PublicSQL {
    private String changTableNameFromObject(Object object)
    {
        return com.ruoyi.common.utils.StringUtils.toUnderScoreCase(object.getClass().getSimpleName());
    }


    private String insertListSql(List<?> list, String tableName)
    {
        if(StringUtils.isBlank(tableName))
        {
            tableName = changTableNameFromObject(list.get(0));
        }
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO  "+tableName);
        String values = "";
        String mfStr = "";
        Field[] fields = list.get(0).getClass().getDeclaredFields( );
        for(Field field:fields)
        {
            PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
            if(null != publicSQLConfig && !publicSQLConfig.isSelect())
            {
                continue;
            }
            if(!"".equals(values) )
            {
                mfStr += ",";
                values += ",";
            }
            values += "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`";
            mfStr += "#'{'list[{0}]."+field.getName()+"}";
        }
        sb.append("("+values+")");
        sb.append("VALUES ");
        MessageFormat mf = new MessageFormat("("+mfStr+")");
        for (int i = 0; i < list.size(); i++) {
            sb.append(mf.format(new Object[]{i}));
            if (i < list.size() - 1) {
                sb.append(",");
            }
        }
        return sb.toString();
    }

    public String insertAllToTable(Map map)
    {
        List<?> list = (List<?>) map.get("list");
        String tableName = (String) map.get("tableName");
        return insertListSql(list,tableName);
    }
    /**
     * 添加对象
     * @return
     */
    public String insertAll(Map map)
    {
        List<?> list = (List<?>) map.get("list");
        return insertListSql(list,null);
    }

    /**
     * 添加对象
     * @param object 对象
     * @return
     */
    public String insert(Object object)
    {
        StringBuffer stringBuffer = new StringBuffer("insert into ");

        String tableName = changTableNameFromObject(object);
        stringBuffer.append(tableName + "(");
        Field[] fields = object.getClass().getDeclaredFields( );
        StringBuffer values = new StringBuffer("(");
        for(Field field:fields)
        {//
            PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
            if(null != publicSQLConfig && !publicSQLConfig.isSelect())
            {
                continue;
            }
            Method method;
            try {
                method = object.getClass().getMethod("get"+com.ruoyi.common.utils.StringUtils.getName(field.getName()));
                Object value = method.invoke(object);
                if(null != value)
                {
                    if(!"(".equals(values.toString()) )
                    {
                        stringBuffer.append(",");
                        values.append( ",");
                    }
                    stringBuffer.append( "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`");
                    values.append( "'"+ escapeSql(value+"")+"'");
                }
            } catch (NoSuchMethodException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (SecurityException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalArgumentException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


        }
        stringBuffer.append( ")");
        values.append( ")");
        return stringBuffer.append(" values ").append(values).toString();
    }

    /**
     * 指定表名添加对象
     * @return
     */
    public String insertToTable(Map map)
    {
        Object object = map.get("object");
        String tableName = changTableNameFromObject(object);
        if(map.containsKey("tableName"))
        {
            tableName = map.get("tableName").toString();
        }
        StringBuffer sql = new StringBuffer();
        sql.append("insert into ");
        sql.append(tableName + "(");
        Field[] fields = object.getClass().getDeclaredFields( );
        String values = "(";

        for(Field field:fields)
        {
            PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
            if(null != publicSQLConfig && !publicSQLConfig.isSelect())
            {
                continue;
            }
            Method method;
            try {
                method = object.getClass().getMethod("get"+com.ruoyi.common.utils.StringUtils.getName(field.getName()));
                Object value = method.invoke(object);
                if(null != value)
                {
                    if(!"(".equals(values) )
                    {
                        sql.append(",");
                        values += ",";
                    }
                    sql.append("`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`");
                    values += "'"+escapeSql(value+"")+"'";
                }
            } catch (NoSuchMethodException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (SecurityException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalArgumentException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


        }
        sql.append(")");
        values += ")";
        return sql.toString()+" values "+values;
    }

    /**
     * 更新对象不为空的属性
     * @param para
     * @return
     */
    public String updateObject(Map<String,Object> para)
    {
        Object object =  para.get("object");
        String whereFieldNames = (String) para.get("whereFieldNames");
        if( null != para.get("tablename"))
        {

        }
        String tableName = (String) para.get("tablename");
        if(null == tableName)
        {
            tableName = changTableNameFromObject(object);
        }
        String sql = "update ";
        sql += tableName;
        Field[]  fields = object.getClass().getDeclaredFields();
        if(null != fields && fields.length !=0 )
        {
            sql += " set ";
            int j = 0;
            for(int i=0;i<fields.length;i++)
            {
                Field field = fields[i];
                PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
                if(null != publicSQLConfig && !publicSQLConfig.isSelect())
                {
                    continue;
                }
                try {
                    Method method = object.getClass().getMethod("get"+com.ruoyi.common.utils.StringUtils.getName(field.getName()));
                    Object value = method.invoke(object);
                    if(null != value)
                    {
                        if(j!=0)
                        {
                            sql += ",";
                        }
                        sql += "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+"='"+escapeSql(value+"")+"'";
                        j++;

//						if(i!=0)
//						{
//							sql += ",";
//						}
//		    			sql += "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+"='"+value+"'";
                    }
                } catch (NoSuchMethodException e) {

                    e.printStackTrace();
                } catch (SecurityException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

            }

            sql += " where 1=1 ";
            String[] wheres = whereFieldNames.split(",");
            if(StringUtils.isNotBlank(whereFieldNames))
            {
                for(int i =0;i<wheres.length;i++)
                {
                    try {
                        Method method = object.getClass().getMethod("get"+com.ruoyi.common.utils.StringUtils.getName(wheres[i]));
                        Object value = method.invoke(object);
                        sql += " and ";
                        sql += com.ruoyi.common.utils.StringUtils.toUnderScoreCase(wheres[i]) + "='"+escapeSql(value+"")+"'";
//    	    			sql += com.ruoyi.common.utils.StringUtils.getName(wheres[i]) + "='"+value+"'";
                    } catch (IllegalAccessException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (IllegalArgumentException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (NoSuchMethodException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (SecurityException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }

                }
            }


        }
        return sql;
    }

    /**
     * 获得对象
     */
    public String getObject(Map<String, Object> para)
    {
        Class<?> clas = (Class<?>) para.get("class");
        String idName = (String) para.get("idName");
        String values = (String) para.get("values");
        String tableName = null;

        if(para.containsKey("tableName"))
        {
            tableName = (String) para.get("tableName");
        }

        String select = "";
        if(para.containsKey("select") && null != para.get("select"))
        {
            select = (String) para.get("select");
        }else{
            select = getSelect(clas);
        }

        if(StringUtils.isBlank(tableName))
        {
            tableName = com.ruoyi.common.utils.StringUtils.toUnderScoreCase(clas.getSimpleName());
        }

        String[] idnames = idName.split(",");
        String[] valuess = values.split(",");

        String where = "";
        for(int i=0;i<idnames.length;i++)
        {
            if(i != 0)
            {
                where += " and ";
            }
            where += "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(idnames[i])+"`='"+escapeSql(valuess[i]+"")+"'";

        }
        String sql = "select "+select+" from "+tableName + " where " +where;
        return sql;
    }

    /**
     * 通过条件删除数据
     * @param para
     * @return
     */
    public String deleteObjectByContent(Map<String, Object> para)
    {
        Class<?> objectCalss = (Class<?>) para.get("objectCalss");
        String tableName = null;
        if(para.containsKey("tableName"))
        {
            tableName = (String) para.get("tableName");
        }

        @SuppressWarnings("unchecked")
        Map<String,String> map  = (Map<String, String>) para.get("map");

        if(StringUtils.isBlank(tableName))
        {
            tableName = com.ruoyi.common.utils.StringUtils.toUnderScoreCase(objectCalss.getSimpleName());
        }
        String sql = "delete from ";
        sql += tableName + " where 1=1 ";
        for(String key:map.keySet())
        {
            sql += " and "+"`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(key)+"`"+"='"+escapeSql(map.get(key)+"")+"'";
        }
        return sql;
    }

    /**
     * 通过id删除数据
     * @param para
     * @return
     */
    public String deleteObjectById(Map<String, Object> para)
    {
        Class<?> objectCalss = (Class<?>) para.get("objectCalss");
        String tableName = null;
        if(para.containsKey("tableName"))
        {
            tableName = (String) para.get("tableName");
        }

        String id  = (String) para.get("id");
        if(StringUtils.isBlank(tableName))
        {
            tableName = com.ruoyi.common.utils.StringUtils.toUnderScoreCase(objectCalss.getSimpleName());
        }
        String sql = "delete from ";
        sql += tableName + " where 1=1 ";
        sql += " and "+"`id`"+"='"+escapeSql(id)+"'";
        return sql;
    }

    /**
     * 获取对象列表总数
     */
    public String getObjectListTotle(Map<String, Object> para)
    {
        Object object = para.get("object");
        @SuppressWarnings("unchecked")
        Map<String,String> whereMap = (Map<String, String>) para.get("whereMap");

        String tableName = changTableNameFromObject(object);
        String sql = "select count(*) from "+tableName;
        String where = " where 1=1 ";

        String like = "";

        Field[]  fields = object.getClass().getDeclaredFields();
        if(null != fields && fields.length !=0 )
        {
            for(Field field:fields)
            {
                PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
                if(null != publicSQLConfig && !publicSQLConfig.isSelect())
                {
                    continue;
                }
                try {
                    Method method;
                    method = object.getClass().getMethod("get"+ com.ruoyi.common.utils.StringUtils.getName(field.getName()));
                    Object value = method.invoke(object);
                    if(null != value)
                    {
                        String orther = "";
                        String s = "=";
                        if(null != whereMap && null != whereMap.get(field.getName()))
                        {
                            s = whereMap.get(field.getName());
                            if("like".equals(s))
                            {
                                value = "%"+value+"%";
                                like += " or " + "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+s+" '"+escapeSql(value+"")+"'"+orther ;
                                continue;
                            }
                            if("time".equals(s))
                            {
                                s = ">";
                                orther = " and `"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`< '"+whereMap.get("end_"+field.getName())+"'";
                            }
                            if("in".equals(s))
                            {
                                s = "in";
                                value = (value+"").replace(",","','");
                                where += " and `"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+s+" ("+"'"+value+"'"+")"+orther;
                                continue;
                            }
                        }
                        where += " and `"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+s+" '"+escapeSql(value+"")+"'"+orther;
                    }
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (SecurityException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

            }
        }
        sql += where;
        if(StringUtils.isNoneBlank(like))
        {
            sql += " and (1=2 "+like+")";
        }
        return sql;
    }

    /**
     * 获取对象列表
     */
    public String getObjectList(Map<String, Object> para)
    {
        Object object = para.get("object");
        @SuppressWarnings("unchecked")
        Map<String,String> whereMap = (Map<String, String>) para.get("whereMap");
        String selectStr = " * ";
        String order = "";
        if(para.containsKey("order") && null != para.get("order"))
        {
            order = para.get("order") +"";
        }
        Integer pageSize = 0;
        if(para.containsKey("pageSize") && null != para.get("pageSize"))
        {
            pageSize =  (Integer) para.get("pageSize");
        }
        Integer pageNo = 0;
        if(para.containsKey("pageNo") && null != para.get("pageNo"))
        {
            pageNo = (Integer) para.get("pageNo");
        }
        if(para.containsKey("selectStr") && null != para.get("selectStr"))
        {
            selectStr = para.get("selectStr")+"";
        }else{
            selectStr = getSelect(object.getClass());
        }
        String tableName = changTableNameFromObject(object);

        if(para.containsKey("tableName") && null != para.get("tableName"))
        {
            tableName = para.get("tableName")+"";
        }
        String sql = "select "+selectStr+" from "+tableName;
        String where = " where 1=1 ";
        String like = "";

        Field[]  fields = object.getClass().getDeclaredFields();
        if(null != fields && fields.length !=0 )
        {
            for(int i=0;i<fields.length;i++)
            {
                Field field = fields[i];
                PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
                if(null != publicSQLConfig && !publicSQLConfig.isSelect())
                {
                    continue;
                }
                try {
                    Method method;
                    method = object.getClass().getMethod("get"+ com.ruoyi.common.utils.StringUtils.getName(field.getName()));
                    Object value = method.invoke(object);
                    if(!(null == value))
                    {
                        String orther = "";
                        String s = "=";
                        if(!(null == whereMap || null == whereMap.get(field.getName())))
                        {
                            s = whereMap.get(field.getName());
                            if("like".equals(s))
                            {
                                value = "%"+value+"%";
                                like += " or " + "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+s+" '"+escapeSql(value+"")+"'"+orther ;
                                continue;
                            }
                            if("time".equals(s))
                            {
                                s = ">";
                                orther = " and `"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`< '"+whereMap.get("end_"+field.getName())+"'";
                            }
                            if("in".equals(s))
                            {
                                s = "in";
                                value = (value+"").replace(",","','");
                                where += " and `"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+s+" ("+"'"+value+"'"+")"+orther;
                                continue;
                            }
                        }
                        where += " and `"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+s+" '"+escapeSql(value+"")+"'"+orther;
                    }
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (SecurityException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

            }
        }
        sql += where;
        if(StringUtils.isNoneBlank(like))
        {
            sql += " and (1=2 "+like+")";
        }
        if(StringUtils.isNotBlank(order))
        {
            sql += " order by "+order;
        }
        if(0 != pageSize && 0 != pageNo)
        {
            sql += " limit "+((pageNo-1)*pageSize)+","+pageSize;
        }
        return sql;
    }

    /**
     * 添加或更新对象
     * INSERT INTO test(`in1`,`str1`) VALUES ('1','1');
     * @param object 对象
     * @return
     */
    public String saveOrUpdateObject(Object object)
    {
        String sql = "insert into ";
        String tableName = changTableNameFromObject(object);
        sql += tableName + "(";
        Field[] fields = object.getClass().getDeclaredFields( );
        String values = "(";
        String update = "";
        for(Field field:fields)
        {//
            PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
            if(null != publicSQLConfig && !publicSQLConfig.isSelect())
            {
                continue;
            }
            Method method;
            try {
                method = object.getClass().getMethod("get"+com.ruoyi.common.utils.StringUtils.getName(field.getName()));
                Object value = method.invoke(object);
                if(null != value)
                {
                    if(!"(".equals(values) )
                    {
                        sql += ",";
                        values += ",";
                        update += ",";
                    }
                    sql += "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`";
                    values += "'"+ escapeSql(value+"")+"'";
                    update += "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+"=VALUES("+"`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`)";
                }
            } catch (NoSuchMethodException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (SecurityException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalArgumentException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


        }
        sql += ")";
        values += ")";
        return sql+" values "+values+" ON DUPLICATE KEY UPDATE "+update;
    }

    /**
     * 添加或更新对象列表
     * INSERT INTO `test` (`in1`,`str1`)VALUES ('1','2'),('2','2') ON DUPLICATE KEY UPDATE `in1`=VALUES(`in1`),`str1`=VALUES(`str1`);
     * @param objectlist 对象列表
     * @return
     */
    public String saveOrUpdateObjectList(List<Object> objectlist)
    {
        StringBuffer sb  =new StringBuffer();
        String update = "";
        for(int i = 0; i<objectlist.size();i++)
        {
            Object object = objectlist.get(i);

            Field[] fields = object.getClass().getDeclaredFields( );
            if(i==0)
            {
                String tableName = changTableNameFromObject(object);
                sb.append("INSERT INTO `"+tableName+"` ");
                sb.append("(");
                for(Field field:fields)
                {
                    PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
                    if(null != publicSQLConfig && !publicSQLConfig.isSelect())
                    {
                        continue;
                    }
                    if(!"".equals(update) )
                    {
                        sb.append(",");
                        update += ",";
                    }
                    sb.append("`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`");
                    update += "`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`"+"=VALUES("+"`"+com.ruoyi.common.utils.StringUtils.toUnderScoreCase(field.getName())+"`)";
                }
                sb.append(")");
                sb.append("VALUES ");
            }else{
                sb.append(",");
            }
            for(int j=0;j<fields.length;j++)
            {
                Field field  = fields[j];
                Method method;
                try {
                    PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
                    if(null != publicSQLConfig && !publicSQLConfig.isSelect())
                    {
                        continue;
                    }
                    method = object.getClass().getMethod("get"+com.ruoyi.common.utils.StringUtils.getName(field.getName()));
                    Object value = method.invoke(object);
                    if(null == value)
                    {
                        value = "";
                    }
                    if(j!=0)
                    {
                        sb.append(",");
                    }else{
                        sb.append("(");
                    }
                    sb.append("'"+ escapeSql(value+"")+"'");
                    if(j==fields.length-1)
                    {
                        sb.append(")");
                    }
                } catch (NoSuchMethodException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (SecurityException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

            }
        }
        sb.append(" ON DUPLICATE KEY UPDATE ");
        sb.append(update);
        return sb.toString();
    }

    public String getObjectListBySQL(Map<String, Object> para)
    {
        return para.get("sql")+"";
    }

    public String updateBySql(String sql)
    {
        return sql;
    }

    public String selectCountBySql(String sql)
    {
        if(!sql.toUpperCase().startsWith("SELECT COUNT("))
        {
            try {
                throw new Exception("不是count查询");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return sql;
    }

    public static String escapeSql(String str) {
        return str == null ? null : StringUtils.replace(str, "'", "''");
    }

    private String getSelect(Class<?> clas)
    {
        String select = "";
        Field[] fields = clas.getDeclaredFields();
        for(Field field:fields)
        {
            PublicSQLConfig publicSQLConfig = field.getAnnotation(PublicSQLConfig.class);
            if(null != publicSQLConfig && !publicSQLConfig.isSelect())
            {
                continue;
            }
            if(!"".equals(select))
            {
                select += ",";
            }
            String fieldName = field.getName();
            select +="`"+ com.ruoyi.common.utils.StringUtils.toUnderScoreCase(fieldName) +"` "+fieldName;
        }
        if("".equals(select))
        {
            select = "*";
        }
        return select;
    }
}