package org.jeecg.modules.online.config.util;


import com.google.common.collect.Lists;
import freemarker.template.TemplateException;
import lombok.extern.slf4j.Slf4j;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.hibernate.tool.schema.TargetType;
import org.jeecg.common.constant.DataBaseConstant;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.online.cgform.entity.OnlCgformField;
import org.jeecg.modules.online.config.db.DataBaseConfig;
import org.jeecg.modules.online.config.db.TableModel;
import org.jeecg.modules.online.config.exception.DBException;
import org.jeecg.modules.online.config.service.DbTableHandleI;

@Slf4j
public class SqlHelper {
    private static DbTableHandleI dbTableHandle;

    public SqlHelper() throws SQLException, DBException {
    	dbTableHandle = TableUtil.getTableHandle();
    }

    public static void createTable(TableModel tableModel) throws IOException, TemplateException, HibernateException, SQLException, DBException {
        String databaseType = TableUtil.getDatabaseType();
        if (DataBaseConstant.DB_TYPE_ORACLE.equals(databaseType)) {
            List<OnlCgformField> fieldList = Lists.newArrayList();

            OnlCgformField field;
            for(Iterator<OnlCgformField> iterator = tableModel.getColumns().iterator(); iterator.hasNext(); fieldList.add(field)) {
            	field = iterator.next();
                if (DbType.INT.equals(field.getDbType())) {
                	field.setDbType(DbType.DOUBLE);
                	field.setDbPointLength(0);
                }
            }

            tableModel.setColumns(fieldList);
        }

        String xml = FreemarkerHelper.process("org/jeecg/modules/online/config/engine/tableTemplate.ftl", getTableData(tableModel, databaseType));
        log.info(xml);
        Map<String,Object> hibernateConfig = new HashMap<>();
        DataBaseConfig dbConfig = tableModel.getDbConfig();
        hibernateConfig.put("hibernate.connection.driver_class", dbConfig.getDriverClassName());
        hibernateConfig.put("hibernate.connection.url", dbConfig.getUrl());
        hibernateConfig.put("hibernate.connection.username", dbConfig.getUsername());
        hibernateConfig.put("hibernate.connection.password", dbConfig.getPassword());
        hibernateConfig.put("hibernate.show_sql", true);
        hibernateConfig.put("hibernate.format_sql", true);
        hibernateConfig.put("hibernate.temp.use_jdbc_metadata_defaults", false);
        hibernateConfig.put("hibernate.dialect", TableUtil.getJdbcDriver(databaseType));
        hibernateConfig.put("hibernate.hbm2ddl.auto", "create");
        hibernateConfig.put("hibernate.connection.autocommit", false);
        hibernateConfig.put("hibernate.current_session_context_class", "thread");
        StandardServiceRegistry standardServiceRegistry = (new StandardServiceRegistryBuilder()).applySettings(hibernateConfig).build();
        MetadataSources metadataSources = new MetadataSources(standardServiceRegistry);
        ByteArrayInputStream input = new ByteArrayInputStream(xml.getBytes());
        metadataSources.addInputStream(input);
        Metadata metadata = metadataSources.buildMetadata();
        SchemaExport schemaExport = new SchemaExport();
        schemaExport.create(EnumSet.of(TargetType.DATABASE), metadata);
        input.close();
        List<Exception> exceptions = schemaExport.getExceptions();
        Iterator<Exception> exceptionIterator = exceptions.iterator();

        Exception exception;
        while(true) {
            if (!exceptionIterator.hasNext()) {
                return;
            }

            exception = exceptionIterator.next();
            if ("java.sql.SQLSyntaxErrorException".equals(exception.getCause().getClass().getName())) {
                SQLSyntaxErrorException e = (SQLSyntaxErrorException)exception.getCause();
                if ("42000".equals(e.getSQLState())) {
                    continue;
                }
                break;
            } else {
                if (!"com.microsoft.sqlserver.jdbc.SQLServerException".equals(exception.getCause().getClass().getName())) {
                    break;
                }

                if (exception.getCause().toString().indexOf("Incorrect syntax near the keyword") != -1) {
                	exception.printStackTrace();
                    throw new DBException(exception.getCause().getMessage());
                }

                log.error(exception.getMessage());
            }
        }
        throw new DBException(exception.getMessage());
    }

    public List<String> getUpdateTableSql(TableModel table) throws DBException, SQLException {
        String databaseType = TableUtil.getDatabaseType();
        String tableName = TableUtil.fixTableName(table.getTableName(), databaseType);
        String sql = "alter table  " + tableName + " ";
        List<String> updateTableSql = new ArrayList<>();

        try {
            Map var6 = this.c((String)null, tableName);
            Map var7 = this.c(table);
            Map var8 = this.a(table.getColumns());
            Iterator var9 = var7.keySet().iterator();

            label72:
            while(true) {
                while(true) {
                    String var10;
                    while(var9.hasNext()) {
                        var10 = (String)var9.next();
                        ColumnMeta var11;
                        if (!var6.containsKey(var10)) {
                            var11 = (ColumnMeta)var7.get(var10);
                            String var17 = (String)var8.get(var10);
                            if (var8.containsKey(var10) && var6.containsKey(var17)) {
                                ColumnMeta var13 = (ColumnMeta)var6.get(var17);
                                String var14 = dbTableHandle.getReNameFieldName(var11);
                                if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(databaseType)) {
                                	updateTableSql.add(var14);
                                } else {
                                	updateTableSql.add(sql + var14);
                                }

                                String var15 = this.getUpdateOnlCgformFieldSql(var10, var11.getColumnId());
                                updateTableSql.add(var15);
                                if (!var13.equals(var11)) {
                                	updateTableSql.add(sql + this.getUpdateColumnSql(var11, var13));
                                    if (DataBaseConstant.DB_TYPE_POSTGRESQL.equals(databaseType)) {
                                    	updateTableSql.add(sql + this.getSpecialHandle(var11, var13));
                                    }
                                }

                                if (!DataBaseConstant.DB_TYPE_SQLSERVER.equals(databaseType) && !var13.b(var11)) {
                                	updateTableSql.add(this.getCommentSql(var11));
                                }
                            } else {
                            	updateTableSql.add(sql + this.getAddColumnSql(var11));
                                if (!DataBaseConstant.DB_TYPE_SQLSERVER.equals(databaseType) && StringUtils.isNotEmpty(var11.getComment())) {
                                	updateTableSql.add(this.getCommentSql(var11));
                                }
                            }
                        } else {
                            var11 = (ColumnMeta)var6.get(var10);
                            ColumnMeta var12 = (ColumnMeta)var7.get(var10);
                            if (!var11.a(var12, databaseType)) {
                            	updateTableSql.add(sql + this.getUpdateColumnSql(var12, var11));
                            }

                            if (!DataBaseConstant.DB_TYPE_SQLSERVER.equals(databaseType) && !DataBaseConstant.DB_TYPE_ORACLE.equals(databaseType) && !var11.b(var12)) {
                            	updateTableSql.add(this.getCommentSql(var12));
                            }
                        }
                    }

                    var9 = var6.keySet().iterator();

                    while(var9.hasNext()) {
                        var10 = (String)var9.next();
                        if (!var7.containsKey(var10.toLowerCase()) && !var8.containsValue(var10.toLowerCase())) {
                        	updateTableSql.add(sql + this.getDropColumnSql(var10));
                        }
                    }
                    break label72;
                }
            }
        } catch (SQLException var16) {
            throw new RuntimeException();
        }

        log.info(" db update sql : " + updateTableSql.toString());
        return updateTableSql;
    }

    private static Map<String, Object> getTableData(TableModel var0, String var1) {
        HashMap var2 = new HashMap();
        Iterator var3 = var0.getColumns().iterator();

        while(var3.hasNext()) {
            OnlCgformField var4 = (OnlCgformField)var3.next();
            var4.setDbDefaultVal(c(var4.getDbDefaultVal()));
        }

        var2.put("entity", var0);
        var2.put("dataType", var1);
        return var2;
    }

    private Map<String, ColumnMeta> c(String var1, String var2) throws SQLException {
        HashMap var3 = new HashMap();
        Connection var4 = null;

        try {
            var4 = TableUtil.getConnection();
        } catch (Exception var17) {
        	log.error(var17.getMessage(), var17);
        }

        DatabaseMetaData var5 = var4.getMetaData();
        ResultSet var6 = var5.getColumns((String)null, var1, var2, "%");

        while(var6.next()) {
            ColumnMeta var7 = new ColumnMeta();
            var7.setTableName(var2);
            String var8 = var6.getString("COLUMN_NAME").toLowerCase();
            var7.setColumnName(var8);
            String var9 = var6.getString("TYPE_NAME");
            int var10 = var6.getInt("DECIMAL_DIGITS");
            String var11 = dbTableHandle.getMatchClassTypeByDataType(var9, var10);
            var7.setColunmType(var11);
            int var12 = var6.getInt("COLUMN_SIZE");
            var7.setColumnSize(var12);
            var7.setDecimalDigits(var10);
            String var13 = var6.getInt("NULLABLE") == 1 ? "Y" : "N";
            var7.setIsNullable(var13);
            String var14 = var6.getString("REMARKS");
            var7.setComment(var14);
            String var15 = var6.getString("COLUMN_DEF");
            String var16 = c(var15) == null ? "" : c(var15);
            var7.setFieldDefault(var16);
            log.info("getColumnMetadataFormDataBase --->COLUMN_NAME:" + var8.toUpperCase() + " TYPE_NAME :" + var9 + " DECIMAL_DIGITS:" + var10 + " COLUMN_SIZE:" + var12);
            var3.put(var8, var7);
        }

        return var3;
    }

    private Map<String, ColumnMeta> c(TableModel var1) {
        HashMap var2 = new HashMap();
        List var3 = var1.getColumns();
        Iterator var5 = var3.iterator();

        while(var5.hasNext()) {
            OnlCgformField var6 = (OnlCgformField)var5.next();
            ColumnMeta var4 = new ColumnMeta();
            var4.setTableName(var1.getTableName().toLowerCase());
            var4.setColumnId(var6.getId());
            var4.setColumnName(var6.getDbFieldName().toLowerCase());
            var4.setColumnSize(var6.getDbLength());
            var4.setColunmType(var6.getDbType().toLowerCase());
            var4.setIsNullable(var6.getDbIsNull() == 1 ? "Y" : "N");
            var4.setComment(var6.getDbFieldTxt());
            var4.setDecimalDigits(var6.getDbPointLength());
            var4.setFieldDefault(c(var6.getDbDefaultVal()));
            var4.setPkType(var1.getJformPkType() == null ? "UUID" : var1.getJformPkType());
            var4.setOldColumnName(var6.getDbFieldNameOld() != null ? var6.getDbFieldNameOld().toLowerCase() : null);
            log.info("getColumnMetadataFormCgForm ----> DbFieldName: " + var6.getDbFieldName().toLowerCase() + " | DbType: " + var6.getDbType().toLowerCase() + " | DbPointLength:" + var6.getDbPointLength() + " | DbLength:" + var6.getDbLength());
            var2.put(var6.getDbFieldName().toLowerCase(), var4);
        }

        return var2;
    }

    private Map<String, String> a(List<OnlCgformField> var1) {
        HashMap var2 = new HashMap();
        Iterator var3 = var1.iterator();

        while(var3.hasNext()) {
            OnlCgformField var4 = (OnlCgformField)var3.next();
            var2.put(var4.getDbFieldName(), var4.getDbFieldNameOld());
        }

        return var2;
    }

    private String getDropColumnSql(String fieldName) {
        return dbTableHandle.getDropColumnSql(fieldName);
    }

    private String getUpdateColumnSql(ColumnMeta cgformcolumnMeta, ColumnMeta datacolumnMeta) throws DBException {
        return dbTableHandle.getUpdateColumnSql(cgformcolumnMeta, datacolumnMeta);
    }

    private String getSpecialHandle(ColumnMeta cgformcolumnMeta, ColumnMeta datacolumnMeta) {
        return dbTableHandle.getSpecialHandle(cgformcolumnMeta, datacolumnMeta);
    }

    private String getReNameFieldName(ColumnMeta columnMeta) {
        return dbTableHandle.getReNameFieldName(columnMeta);
    }

    private String getAddColumnSql(ColumnMeta columnMeta) {
        return dbTableHandle.getAddColumnSql(columnMeta);
    }

    private String getCommentSql(ColumnMeta columnMeta) {
        return dbTableHandle.getCommentSql(columnMeta);
    }

    private String getUpdateOnlCgformFieldSql(String oldDbFieldName, String id) {
        return "update onl_cgform_field set DB_FIELD_NAME_OLD = '" + oldDbFieldName + "' where ID ='" + id + "'";
    }

    private int a(String oldDbFieldName, String id, Session session) {
        return session.createSQLQuery("update onl_cgform_field set DB_FIELD_NAME_OLD= '" + oldDbFieldName + "' where ID ='" + id + "'").executeUpdate();
    }

    private static String c(String var0) {
        if (StringUtils.isNotEmpty(var0)) {
            try {
                Double.valueOf(var0);
            } catch (Exception var2) {
                if (!var0.startsWith("'") || !var0.endsWith("'")) {
                    var0 = "'" + var0 + "'";
                }
            }
        }

        return var0;
    }

    public String getDropIndexsSql(String indexName, String tableName) {
        return dbTableHandle.dropIndexs(indexName, tableName);
    }

    public String getCountIndexSql(String indexName, String tableName) {
        return dbTableHandle.countIndex(indexName, tableName);
    }

    public static List<String> getIndexes(String table) throws SQLException {
        Connection conn = null;
        ResultSet indexInfo = null;
        ArrayList<String> indexes = new ArrayList<>();

        try {
        	conn = TableUtil.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();
            indexInfo = metaData.getIndexInfo(null, null, table, false, false);
            indexInfo.getMetaData();

            while(indexInfo.next()) {
                String indexName = indexInfo.getString("INDEX_NAME");
                if (oConvertUtils.isEmpty(indexName)) {
                	indexName = indexInfo.getString("index_name");
                }

                if (oConvertUtils.isNotEmpty(indexName)) {
                    indexes.add(indexName);
                }
            }
        } catch (SQLException e) {
        	log.error(e.getMessage(), e);
        } finally {
            if (conn != null) {
            	conn.close();
            }

        }

        return indexes;
    }
}