/**
 *
 */
package com.cnd3b.common.data;

import com.cnd3b.common.data.db.SQLiteJDBC;
import com.cnd3b.common.parameter.parameter;
import com.cnd3b.common.data.db.DBConnect;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;

/**
 *
 * @author SJW
 *
 */
public class SQLFactory {
    /**
     * SQL模板缓存map,第一次读取模板文件后将内容存在此Map中。减少文件的读取次数，如模板调整需重启服务生效。
     */
    private static HashMap<String, String> modelmap = new HashMap<String, String>();

    /**
     * 需要加载的SQL模板名称
     */
    private String SQLMODELNAME;
    private String SQLMODELNAME_KEY;
    private Object o = null;

    /**
     * SQL参数Map
     */
    HashMap<String, String> parametermap;

    /**
     * SQL参数Map
     */
    HashMap<String, String[]> parametermap_array;

    /**
     * SQL参数Map
     */
    HashMap<String, ArrayList<String>> parametermap_arrayList;

    /**
     * SQL参数Map
     */
    HashMap<String, Integer> parametermap_int;
    HashMap<String, Long> parametermap_long;
    HashMap<String, Double> parametermap_double;
    HashMap<String, String> parametermap_SQL;

    public boolean queryforpage = false;
    int pageSize = 0;
    int pageNumber = 0;
    String orderfields = "";

    /**
     * 构造函数
     *
     * @param SQLMODELNAME 需要加载的SQL模板名称,该模版必须在公共模版文件夹内
     */
    public SQLFactory(String SQLMODELNAME) {
        this.SQLMODELNAME = "SQL/" + SQLMODELNAME;
        this.o = this;
        this.SQLMODELNAME_KEY = o.getClass().getName() + this.SQLMODELNAME;
    }

    public SQLFactory(Object o, String SQLMODELNAME) {
        this.SQLMODELNAME = "SQL/" + SQLMODELNAME;
        this.o = o;
        this.SQLMODELNAME_KEY = o.getClass().getName() + this.SQLMODELNAME;
    }

    public SQLFactory(Object o, String SQLMODELNAME, int pageSize, int pageNumber, String orderfields) {
        this.SQLMODELNAME = "SQL/" + SQLMODELNAME;
        this.o = o;
        queryforpage = true;
        this.pageSize = pageSize;
        this.pageNumber = pageNumber;
        this.orderfields = orderfields;
        this.SQLMODELNAME_KEY = o.getClass().getName() + this.SQLMODELNAME;
    }


    /**
     * 获取SQL模板，第一次获取时判断是否存在于SQL模板缓存modelmap中，
     * 如存
     * 在，则直接取出，如不存在，则读取SQL模板文件，并将其存入modelmap
     *
     * @return
     */
    private String getSQLModel() {
        if (!SQLMODELNAME.contains(".sql")) {
            SQLMODELNAME = SQLMODELNAME + ".sql";
        }
        StringBuffer SQL = new StringBuffer();
        if (!modelmap.containsKey(SQLMODELNAME_KEY) || parameter.isdebug()) {
            InputStream is = null;
            InputStreamReader isr = null;
            try {
                is = o.getClass().getResourceAsStream(SQLMODELNAME);
                isr = new InputStreamReader(is, "GBK");
                char[] ch = new char[2048];
                int byteRead = 0;
                while ((byteRead = isr.read(ch)) != -1) {
                    String str = new String(ch, 0, byteRead);
                    SQL.append(str);
                }
                isr.close();
                is.close();
            } catch (IOException e) {
                new SQLiteJDBC().InsertLogMsg("Sql文件读取", "读取失败：" + SQLMODELNAME, "err");
                e.printStackTrace();
            } finally {
                try {
                    isr.close();
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            /**
             * 将示例代码去除
             */
            if (SQL.toString().contains("--demo_begin")) {
                SQL = SQL.replace(SQL.indexOf("--demo_begin"), SQL.lastIndexOf("--demo_end") + "--demo_end".length(),
                        "");
            }
            if (!"".equals(SQL.toString().trim())) {

                modelmap.put(SQLMODELNAME_KEY, SQL.toString().trim());
            }
        }
        return modelmap.get(SQLMODELNAME_KEY);
    }

    /**
     * 为SQL实例添加文本类型参数值
     *
     * @param key
     * @param value
     */
    public void addParameter(String key, String value) {
        if (this.parametermap == null) {
            this.parametermap = new HashMap<String, String>();
        }
        this.parametermap.put(key, value);
    }

    /**
     * 为SQL实例添加文本类型参数值
     *
     * @param key
     * @param value
     */
    public void addParameter(String key, StringBuffer value) {
        if (this.parametermap == null) {
            this.parametermap = new HashMap<String, String>();
        }
        this.parametermap.put(key, value.toString());
    }

    /**
     * 为SQL实例添加文本类型的参数值，传入数组将自动转成 in语句的后续参数如('1','2','3')
     *
     * @param key
     * @param value
     */
    public void addParameter_in(String key, String[] value) {
        if (this.parametermap_array == null) {
            this.parametermap_array = new HashMap<>();
        }
        this.parametermap_array.put(key, value);
    }

    /**
     * 为SQL实例添加文本类型的参数值，传入数组将自动转成 in语句的后续参数如('1')
     *
     * @param key
     * @param value
     */
    public void addParameter_in(String key, String value) {
        addParameter_in(key, new String[]{value});
    }

    /**
     * 为SQL实例添加文本类型的参数值，传入数组将自动转成 in语句的后续参数如('1')
     *
     * @param key
     * @param value
     */
    public void addParameter_in(String key, Object value) {
        addParameter_in(key, String.valueOf(value));
    }


    /**
     * 为SQL实例添加文本类型的参数值，传入数组将自动转成 in语句的后续参数如('1','2','3')
     *
     * @param key
     * @param value
     */
    public void addParameter_in(String key, ArrayList<String> value) {
        if (this.parametermap_arrayList == null) {
            this.parametermap_arrayList = new HashMap<>();
        }
        this.parametermap_arrayList.put(key, value);
    }

    /**
     * 为SQL实例添加参数值，传入值将直拼接在sql中，成为sql语句的一部分而非参数
     *
     * @param key
     * @param value
     */
    public void addParameter_SQL(String key, String value) {
        if (this.parametermap_SQL == null) {
            this.parametermap_SQL = new HashMap<String, String>();
        }
        this.parametermap_SQL.put(key, value);
    }

    /**
     * 为SQL实例添加参数值，传入值将直拼接在sql中，成为sql语句的一部分而非参数
     *
     * @param key
     * @param value
     */
    public void addParameter_SQL(String key, StringBuffer value) {
        if (this.parametermap_SQL == null) {
            this.parametermap_SQL = new HashMap<String, String>();
        }
        this.parametermap_SQL.put(key, value.toString());

    }

    /**
     * 为SQL实例添加int类型的参数值，
     *
     * @param key
     * @param value
     */
    public void addParameter(String key, boolean value) {
        if (this.parametermap_int == null) {
            this.parametermap_int = new HashMap<String, Integer>();
        }
        this.parametermap_int.put(key, value ? 1 : 0);
    }

    /**
     * 为SQL实例添加int类型的参数值，
     *
     * @param key
     * @param value
     */
    public void addParameter(String key, int value) {
        if (this.parametermap_int == null) {
            this.parametermap_int = new HashMap<String, Integer>();
        }
        this.parametermap_int.put(key, value);
    }

    /**
     * 为SQL实例添加long类型的参数值，
     *
     * @param key
     * @param value
     */
    public void addParameter(String key, long value) {
        if (this.parametermap_long == null) {
            this.parametermap_long = new HashMap<String, Long>();
        }
        this.parametermap_long.put(key, value);
    }

    /**
     * 为SQL实例添加double类型的参数值，
     *
     * @param key
     * @param value
     */
    public void addParameter(String key, double value) {
        if (this.parametermap_double == null) {
            this.parametermap_double = new HashMap<String, Double>();
        }
        this.parametermap_double.put(key, value);
    }

    /**
     * 将当前对象转换成有效的SQL语句
     *
     */
    public String getSQL() {
        String str = getSQLModel();
        if (this.parametermap != null) {
            for (String key : this.parametermap.keySet()) {
                String sqlkey = "$" + key + "$";
                String value = this.parametermap.get(key);
                if (value == null || "null".equalsIgnoreCase(value)) {
                    str = str.replace(sqlkey, "NULL");
                } else {
                    str = str.replace(sqlkey, "'" + value + "'");
                }
            }
        }
        if (this.parametermap_array != null) {
            for (String key : this.parametermap_array.keySet()) {
                String sqlkey = "$" + key + "$";
                String[] values = this.parametermap_array.get(key);
                if (values == null || values.length == 0) {
                    str = str.replace(sqlkey, "('')");
                } else {
                    StringBuilder instr = new StringBuilder();
                    for (String value : values) {
                        if (instr.toString().equals("")) {
                            instr = new StringBuilder("'" + value + "'");
                        } else {
                            instr.append(",'").append(value).append("'");
                        }
                    }
                    str = str.replace(sqlkey, "(" + instr + ")");
                }
            }
        }
        if (this.parametermap_arrayList != null) {
            for (String key : this.parametermap_arrayList.keySet()) {
                String sqlkey = "$" + key + "$";
                ArrayList<String> values = this.parametermap_arrayList.get(key);
                if (values == null || values.size() == 0) {
                    str = str.replace(sqlkey, "('')");
                } else {
                    StringBuilder instr = new StringBuilder();
                    for (String value : values) {
                        if (instr.toString().equals("")) {
                            instr = new StringBuilder("'" + value + "'");
                        } else {
                            instr.append(",'").append(value).append("'");
                        }
                    }
                    str = str.replace(sqlkey, "(" + instr + ")");
                }
            }
        }

        if (this.parametermap_int != null) {
            for (String key : this.parametermap_int.keySet()) {
                String sqlkey = "$" + key + "$";
                int value = this.parametermap_int.get(key);
                str = str.replace(sqlkey, String.valueOf(value));
            }
        }
        if (this.parametermap_long != null) {
            for (String key : this.parametermap_long.keySet()) {
                String sqlkey = "$" + key + "$";
                long value = this.parametermap_long.get(key);
                str = str.replace(sqlkey, String.valueOf(value));
            }
        }
        if (this.parametermap_double != null) {
            for (String key : this.parametermap_double.keySet()) {
                String sqlkey = "$" + key + "$";
                double value = this.parametermap_double.get(key);
                str = str.replace(sqlkey, String.valueOf(value));
            }
        }
        if (this.parametermap_SQL != null) {
            for (String key : this.parametermap_SQL.keySet()) {
                String sqlkey = "$" + key + "$";
                String value = this.parametermap_SQL.get(key);
                str = str.replace(sqlkey, value);
            }
        }
        if (queryforpage) {
            str = str.replaceFirst("select", "with #p2dbconnectstring as (select ROW_NUMBER()over(order by " + orderfields + " )queryrow,");
            str = str + " )select * from #p2dbconnectstring t1 inner join (select count(*)total,CEILING(count(*)/(" + pageSize + "*1.0)) pageTotal from #p2dbconnectstring) t2 on 1=1 where queryrow>(" + pageNumber + "-1)*" + pageSize + " and queryrow<=" + pageNumber + "*" + pageSize;
        }
        return str;
    }

    public Rows runSqlQuery() {
        return new DBConnect().runSqlQuery(this);
    }

    public Rows runSqlQuery(String datakey) {
        return new DBConnect(datakey).runSqlQuery(this);
    }

    public String runSqlUpdate() {
        return new DBConnect().runSqlUpdate(this);
    }

    public String runSqlUpdate(String datakey) {
        return new DBConnect(datakey).runSqlUpdate(this);
    }
}
