package cc.smtweb.framework.core.db.impl;

import cc.smtweb.framework.core.common.SwEnum;
import cc.smtweb.framework.core.db.DbEngine;
import cc.smtweb.framework.core.db.cache.ModelTableCache;
import cc.smtweb.framework.core.db.vo.ModelField;
import cc.smtweb.framework.core.db.vo.ModelIndex;
import cc.smtweb.framework.core.db.vo.ModelTable;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.text.MessageFormat;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: AKhh
 * Date: 12-12-21 下午2:41
 * 启动时检查数据库并自动建表、字段、索引；
 */
@Slf4j
@SuppressWarnings("UnusedDeclaration")
public class DatabaseUtil {
    private static final int INDEX_NAME_MAX_LENGTH = 30; //索引名称长度；
    private static final String MODULE = DatabaseUtil.class.getName();
    private IDatabaseInfo databaseInfo;
    //是否需要根据实体定义清理数据库字段
    private boolean is_db_clean_field = false;
    //是否需要根据实体定义清理数据库表
    private boolean is_db_clean_table = false;

    public DatabaseUtil(boolean is_db_clean_field, boolean is_db_clean_table) {
        this.is_db_clean_field = is_db_clean_field;
        this.is_db_clean_table = is_db_clean_table;
        this.databaseInfo = new DefaultDatabaseInfoImpl();
    }

    /**
     * 检测数据库结构和配置是否一致，根据配置增加表或字段信息；
     */
    public void checkDb() {
        UtilTime timer = new UtilTime();
        timer.setLog(true);

        timer.timerString("Start - 开始获取连接的数据库的元信息！");
        this.databaseInfo.printDatabaseInfo();
        // get ALL tables from this database
        Map<String, IDatabaseInfo.TableCheckInfo> tableNames = this.databaseInfo.getTables();
        //Set<IDatabaseInfo.TableCheckInfo> fkTableNames = (tableNames == null ? null : new HashSet<IDatabaseInfo.TableCheckInfo>(tableNames.values()));
        //Set<IDatabaseInfo.TableCheckInfo> indexTableNames = (tableNames == null ? null : new HashSet<IDatabaseInfo.TableCheckInfo>(tableNames.values()));
        if (tableNames == null) {
            String message = "不能从数据库获取表名信息，将被跳过...";
            log.error(message, MODULE);
            return;
        }
        timer.timerString("已从数据库获取所有的表名信息！");
        // get ALL column info, put into hashmap by table name
        Map<String, List<IDatabaseInfo.ColumnCheckInfo>> colInfo = this.databaseInfo.getColumnInfo(tableNames);
        if (colInfo == null) {
            String message = "不能从数据库获取字段列信息，将被跳过...";
            log.error(message, MODULE);
            return;
        }
        timer.timerString("已获取所有的数据库字段列信息。");
        //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
        timer.timerString("开始检查表，列定义信息.....................................");
        Collection<ModelTable> ModelTableDefList = ModelTableCache.getInstance().getAll();
        Iterator<ModelTable> ModelTableIter = ModelTableDefList.iterator();
        int curEnt = 0;
        int totalEnt = ModelTableDefList.size();
        List<ModelTable> entitiesAdded = new LinkedList<>();    // 数据库中没有的实体, 通过建表
        // 数据库中有表, 也有对应的实体
        Map<IDatabaseInfo.TableCheckInfo, ModelTable> entitiesExist = new HashMap<>();
        while (ModelTableIter.hasNext()) {
            curEnt++;
            ModelTable entity = ModelTableIter.next();
            String entMessage = "(" + timer.timeSinceLast() + "ms) Checking #" + curEnt + "/" + totalEnt + " Entity " + entity.getName();
            log.info(entMessage, MODULE);
            String k = entity.getSchemaTableName().toUpperCase();  //表名
            // -make sure all entities have a corresponding table
            if (tableNames.containsKey(k)) {
                IDatabaseInfo.TableCheckInfo table = tableNames.remove(k);
                entitiesExist.put(table, entity);
                if (colInfo != null) {
                    List<String> delCols = new ArrayList<>();
                    Map<String, ModelField> fieldColNames = new HashMap<>();
                    for (int fnum = 0, len = entity.getFields().size(); fnum < len; fnum++) {
                        ModelField field = entity.getFields().get(fnum);
                        fieldColNames.put(field.getName().toUpperCase(), field);  // XML读出来是小写，数据库读取来是大写.
                    }
                    List<IDatabaseInfo.ColumnCheckInfo> colList = colInfo.get(k);
                    int numCols = 0;
                    if (colList != null) {
                        for (; numCols < colList.size(); numCols++) {
                            IDatabaseInfo.ColumnCheckInfo ccInfo = colList.get(numCols);
                            ccInfo.columnName = ccInfo.columnName.toUpperCase();
                            // -list all columns that do not have a corresponding field
                            if (fieldColNames.containsKey(ccInfo.columnName)) {
                                ModelField field = fieldColNames.remove(ccInfo.columnName);
                                SwEnum.DataTypeBean modelFieldType = SwEnum.DataType.instance.getByValue(field.getDataType());
                                if (modelFieldType != null) {
                                    // make sure each corresponding column is of the correct type
                                    if (!ccInfo.typeName.equalsIgnoreCase(modelFieldType.sqlType)) {
                                        String message = "警告: 表[" + entity.getName() + "]字段[" + ccInfo.columnName + "] 字段类型不一致：：：数据库：[" + ccInfo.typeName + "], " +
                                                "实体定义：[" + modelFieldType.sqlType + "]！";
                                        log.error(message, MODULE);
                                    }
                                    if (modelFieldType.dataLength != 0 && ccInfo.columnSize != -1 && modelFieldType.dataLength != ccInfo.columnSize) {
                                        String message = "警告: 表[" + entity.getName() + "]字段[" + ccInfo.columnName + "] 字段长度不一致：：：数据库：[" + ccInfo.columnSize + "], " +
                                                "实体定义：[" + modelFieldType.dataLength + "]！";
                                        log.debug(message, MODULE);
                                    }
                                    /*if (decimalDigits != -1 && decimalDigits != ccInfo.decimalDigits) {
                                        String message = "警告: 表[" + entity.getName() + "]字段[" + ccInfo.columnName + "] 小数位数不一致：：：数据库：[" + ccInfo.decimalDigits + "], " +
                                                "实体定义：[" + decimalDigits + "]！";
                                        log.debug(message, MODULE);
                                    }*/
                                } else {
                                    String message = "警告: 表[" + entity.getName() + "]字段[" + ccInfo.columnName + "] 字段类型定义错误[" + field.getDataType() + "]！";

                                    log.error(message, MODULE);
                                }
                            } else {
                                String message = "表\"" + entity.getName() + "\"字段\"" + ccInfo.columnName + "\"没有定义！";
                                log.debug(message, MODULE);
                                if (is_db_clean_field) {
                                    delCols.add(ccInfo.columnName);
                                }
                            }
                        }
                    }
                    // -display message if number of table columns does not match number of entity fields
                    if (!is_db_clean_field) {
                        if (numCols != entity.getFields().size()) {
                            String message = "表[" + entity.getName() + "]定义了" + entity.getFields().size() + "个字段，但数据库有" + numCols + "个字段！";
                            log.debug(message, MODULE);
                        }
                    } else if (delCols.size() > 0) {
                        String errMsg = dropColumn(entity, delCols);
                        String message;
                        if (errMsg != null && errMsg.length() > 0) {
                            message = "清理字段出错，表：\"" + entity.getName() + "\": " + errMsg;
                            log.error(message, MODULE);
                        } else {
                            message = "清理字段成功，表：\"" + entity.getName() + "\"";
                            log.debug(message, MODULE);
                        }
                    }
                    // -list all fields that do not have a corresponding column
                    for (String colName : fieldColNames.keySet()) {
                        ModelField field = fieldColNames.get(colName);
                        String message = "Field \"" + field.getName() + "\" of entity \"" + entity.getName() + "\" is missing its corresponding column \"" + field.getName() + "\"";
                        log.debug(message, MODULE);
                        // add the column
                        String errMsg = addColumn(entity, field);
                        if (errMsg != null && errMsg.length() > 0) {
                            message = "加字段出错，表：\"" + entity.getName() + "\".\"" + field.getName() + "\": " + errMsg;
                            log.error(message, MODULE);
                        } else {
                            message = "加字段成功，表：\"" + entity.getName() + "\".\"" + field.getName() + "\"";
                            log.debug(message, MODULE);
                        }
                    }
                    //修复主键
                    if (StringUtils.isEmpty(table.pk)) {
                        String errMsg = addPk(entity);
                        String message;
                        if (errMsg != null && errMsg.length() > 0) {
                            message = "修复主键出错，表：\"" + entity.getName() + "\": " + errMsg;
                            log.error(message, MODULE);
                        } else {
                            message = "修复主键成功，表：\"" + entity.getName() + "\"";
                            log.debug(message, MODULE);
                        }
                    }
                }
            } else {
                String message = "数据库没有找到表\"" + entity.getName() + "\"";
                log.debug(message, MODULE);
                // create the table
                String errMsg = createTable(entity);
                if (StringUtils.isNotEmpty(errMsg)) {
                    message = "建表\"" + entity.getName() + "\"出错: " + errMsg;
                    log.error(message, MODULE);
                } else {
                    entitiesAdded.add(entity);
                    message = "建表成功\"" + entity.getName() + "\"";
                    log.debug(message, MODULE);
                }
            }
        }
        timer.timerString("After Individual Table/Column Check");
        // -list all tables that do not have a corresponding entity
        Iterator<String> tableNamesIter = tableNames.keySet().iterator();
        while (tableNamesIter != null && tableNamesIter.hasNext()) {
            String tableName = tableNamesIter.next();
            if (!is_db_clean_table) {
                String message = "表\"" + tableName + "\"没有实体定义文件！";
                log.debug(message, MODULE);
            } else {
                String message, errMsg = dropTable(tableNames.get(tableName).catalog, tableName);
                if (StringUtils.isNotEmpty(errMsg)) {
                    message = "删除表\"" + tableName + "\"出错: " + errMsg;
                    log.error(message, MODULE);
                } else {
                    message = "删除表成功\"" + tableName + "\"";
                    log.debug(message, MODULE);
                }
            }
        }

        // for each newly added table, add declared indexes
        for (ModelTable curEntity : entitiesAdded) {
            String indErrMsg = this.createDeclaredIndices(curEntity);
            if (indErrMsg != null && indErrMsg.length() > 0) {
                String message = "建索引出错，表\"" + curEntity.getName() + "\": " + indErrMsg;
                log.error(message, MODULE);
            } else {
                String message = "建索引成功，表 \"" + curEntity.getName() + "\"";
                log.debug(message, MODULE);
            }
        }
        // 补充索引 get ALL column info, put into hashmap by table name
        Map<String, Map<String, Set<String>>> tableIndexListMap = this.databaseInfo.getIndexInfo(entitiesExist.keySet(), true);
        for (ModelTable entity : entitiesExist.values()) {
            // 实体未定义索引.
            if (entity.getIndexes().isEmpty())
                continue;
            // get existing index map for this table
            String k = entity.getSchemaTableName().toUpperCase();  //表名
            Map<String, Set<String>> exists_indexes = tableIndexListMap.get(k);
            Set<String> setEnabled = new HashSet<>();//有效的索引
            for (ModelIndex mi : entity.getIndexes()) {
                if (SwEnum.IndexType.PK.value.equalsIgnoreCase(mi.getType())) continue;

                Set<String> dbfields = null;
                String indexName = getDbIndexName(entity, mi);
                if (exists_indexes != null) dbfields = exists_indexes.get(indexName);
                setEnabled.add(mi.getName());
                if (!hasDiff(mi.getFields(), dbfields)) {//没有变化，不管
                    continue;
                }
                if (dbfields != null) deleteDeclaredIndex(entity.getSchemaTableName(), indexName);

                // 建数据库已经有表的不存在的索引
                String indErrMsg = this.createDeclaredIndex(entity, mi);
                if (indErrMsg != null && indErrMsg.length() > 0) {
                    String message = "建索引出错，表：\"" + entity.getName() + "\".\"" + mi.getName() + "\": " + indErrMsg;
                    log.error(message, MODULE);
                } else {
                    String message = "Created declared index [" + mi.getName() + "]  for entity \"" + entity.getName() + "\"";
                    log.debug(message, MODULE);
                }
            }
            for (String indexName : exists_indexes.keySet()) {
                if (!setEnabled.contains(indexName)) deleteDeclaredIndex(entity.getSchemaTableName(), indexName);
            }
        }
        timer.timerString("数据库结构检测和同步完成！");
    }

    //顺序不一致，也要重建
    private boolean hasDiff(String fields1, Set<String> fields2) {
        if (StringUtils.isEmpty(fields1) || fields2 == null) return true;
        String[] f1 = fields1.split(",");
        if (fields1.length() != fields2.size()) return true;
        int n = 0;
        for (String s : fields2) {
            if (!s.equalsIgnoreCase(f1[n].toLowerCase())) return true;
            n++;
        }
        return false;
    }

    /**
     * 建表
     *
     * @param entity ModelTable
     * @return String 错误消息
     */
    public String createTable(ModelTable entity) {
        if (entity == null) {
            return "ModelTable was null and is required to create a table";
        }

//        entity.setCurCreated(true);
        StringBuilder sqlBuf = new StringBuilder("CREATE TABLE ");
        sqlBuf.append(entity.getSchemaTableName());
        sqlBuf.append(" (");
        for (ModelField field : entity.getFields()) {
            SwEnum.DataTypeBean type = SwEnum.DataType.instance.getByValue(field.getDataType());

            if (type == null) {
                return "Field type [" + field.getDataType() + "] not found for field [" + field.getName() + "] of entity [" + entity.getName() + "], not creating table.";
            }

            sqlBuf.append(field.getName());
            sqlBuf.append(" ");
            sqlBuf.append(type.getSqlTypeCreate());

            if (field.getName().equalsIgnoreCase(entity.getIdField())) {
                sqlBuf.append(" NOT NULL , ");
            } else {
                if (!StringUtils.isEmpty(field.getDefaultValue())) {
                    sqlBuf.append(" DEFAULT '").append(field.getDefaultValue()).append("'");
                }
                sqlBuf.append(" , ");
            }
        }
        String pkName = "PK_" + entity.getName();
        if (pkName.length() > INDEX_NAME_MAX_LENGTH) {
            pkName = pkName.substring(0, INDEX_NAME_MAX_LENGTH);
        }
        sqlBuf.append("CONSTRAINT ");
        sqlBuf.append(pkName);
        sqlBuf.append(" PRIMARY KEY (");
        sqlBuf.append(entity.getIdField());
        sqlBuf.append("))");

        try {
            DbEngine.getInstance().update(sqlBuf.toString());
        } catch (Exception e) {
            return e.getMessage();
        }
        addTableComment(entity);
        //加上字段注解:
        for (ModelField field : entity.getFields()) {
            addColumnComment(entity, field);
        }
        return null;
    }

    public String addColumn(ModelTable entity, ModelField field) {
        if (entity == null || field == null) {
            return "ModelTable or ModelField where null, cannot add column";
        }
        SwEnum.DataTypeBean type = SwEnum.DataType.instance.getByValue(field.getDataType());
        if (type == null) {
            return "Field type not found for field [" + field.getName() + "] of entity [" + entity.getName() + "], not adding column.";
        }
        StringBuilder sqlBuf = new StringBuilder(128);
        sqlBuf.append(MessageFormat.format("ALTER TABLE {0} ADD {1} {2}", entity.getSchemaTableName(), field.getName(), type.getSqlTypeCreate()));
        if (!StringUtils.isEmpty(field.getDefaultValue())) {
            sqlBuf.append(" DEFAULT '").append(field.getDefaultValue()).append("'");
        }
        log.debug("正在增加字段" + entity.getName() + "." + field.getName());
        try {
            DbEngine.getInstance().update(sqlBuf.toString());
        } catch (Exception e) {
            sqlBuf.setLength(0);
            sqlBuf.append("ALTER TABLE ").append(entity.getSchemaTableName()).append(" ADD COLUMN ").append(field.getName()).append(" ").append(type.getSqlTypeCreate());
            if (StringUtils.isNotEmpty(field.getDefaultValue())) {
                sqlBuf.append(" DEFAULT '").append(field.getDefaultValue()).append("'");
            }
            try {
                DbEngine.getInstance().update(sqlBuf.toString());
            } catch (Exception e1) {
                return e.getMessage();
            }
        }
        //加上字段注解:
        addColumnComment(entity, field);
        return null;
    }

    public String addPk(ModelTable entity) {
        if (entity == null) {
            return "实体为null！";
        }
        if (StringUtils.isEmpty(entity.getPkFieldName())) {
            return "表" + entity.getName() + "没有定义主键！";
        }
        String pkName = "PK_" + entity.getName();
        if (pkName.length() > INDEX_NAME_MAX_LENGTH) {
            pkName = pkName.substring(0, INDEX_NAME_MAX_LENGTH);
        }
        StringBuilder sqlBuf = new StringBuilder(128);
        sqlBuf.append("ALTER TABLE ").append(entity.getSchemaTableName()).append(" ADD CONSTRAINT ")
                .append(pkName).append(" PRIMARY KEY (").append(entity.getIdField()).append(")");
        try {
            DbEngine.getInstance().update(sqlBuf.toString());
        } catch (Exception e) {
            return e.getMessage();
        }
        return null;
    }

    //删除表列
    public String dropColumn(ModelTable entity, List<String> delCols) {
        if (delCols.isEmpty()) return null;
        StringBuilder sql = new StringBuilder(128);
        sql.append("ALTER TABLE " + entity.getSchemaTableName()).append("\nDROP COLUMN(");
        for (String col : delCols) {
            sql.append(col).append(",");
        }

        log.debug("正在删除表字段" + entity.getName());
        try {
            DbEngine.getInstance().update(sql.substring(0, sql.length() - 1) + ")");
        } catch (Exception e) {
            return e.getMessage();
        }
        return null;
    }

    //删除表
    public String dropTable(String catalog, String tableName) {
        if (StringUtils.isNotEmpty(catalog)) tableName = catalog + "." + tableName;
        log.debug("正在删除表" + tableName);
        try {
            DbEngine.getInstance().update("drop table " + tableName);
        } catch (Exception e) {
            return e.getMessage();
        }
        return null;
    }

    private String addTableComment(ModelTable entity) {
        log.debug("正在增加表说明" + entity.getName());
        try {
            DbEngine.getInstance().update("ALTER TABLE " + entity.getSchemaTableName() + " COMMENT '" + entity.getTitle() + "'");
        } catch (Exception e) {
            return e.getMessage();
        }
        return null;
    }

    private String addColumnComment(ModelTable entity, ModelField field) {
        log.debug("正在增加字段说明" + entity.getName() + "." + field.getName());
        // comment on column BASE_ENUM.DESCRIPTION is
        SwEnum.DataTypeBean type = SwEnum.DataType.instance.getByValue(field.getDataType());
        if (type == null) return null;
        try {
            String defValue = StringUtils.isNotEmpty(field.getDefaultValue()) ? "'" + field.getDefaultValue() + "'": "";
            String dvs = StringUtils.isNotEmpty(field.getDefaultValue()) ? " DEFAULT '" + field.getDefaultValue() + "'": "";
            String sql = "ALTER TABLE " + entity.getSchemaTableName() + " MODIFY COLUMN " + field.getName() + " " + type.getSqlTypeCreate() + dvs + " COMMENT '" + field.getRemark() + "'";
            DbEngine.getInstance().update(sql);
        } catch (Exception e) {
            return e.getMessage();
        }
        return null;
    }

    /**
     * 建立索引
     *
     * @param entity 实体定义对象；
     * @return 错误消息，处理正确会返回null
     */
    public String createDeclaredIndices(ModelTable entity) {
        if (entity == null) {
            return "ModelTable was null and is required to create declared indices for a table";
        }
        StringBuilder retMsgsBuffer = new StringBuilder();
        // go through the indexes to see if any need to be added
        Iterator<ModelIndex> indexesIter = entity.getIndexes().iterator();
        for (ModelIndex modelIndex : entity.getIndexes()) {
            String retMsg = createDeclaredIndex(entity, modelIndex);
            if (retMsg != null && retMsg.length() > 0) {
                if (retMsgsBuffer.length() > 0) {
                    retMsgsBuffer.append("\n");
                }
                retMsgsBuffer.append(retMsg);
            }
        }
        if (retMsgsBuffer.length() > 0) {
            return retMsgsBuffer.toString();
        } else {
            return null;
        }
    }

    private String createDeclaredIndex(ModelTable entity, ModelIndex modelIndex) {
        log.debug("正在创建索引" + entity.getName() + "." + modelIndex.getName() + ".....");
        String createIndexSql = makeIndexClause(entity, modelIndex);
        if (StringUtils.isEmpty(createIndexSql)) return null;
        try {
            DbEngine.getInstance().update(createIndexSql);
        } catch (Exception sqle) {
            return "SQL Exception while executing the following:\n" + createIndexSql + "\nError was: " + sqle.toString();
        }
        return null;
    }

    /**
     * 构造建立索引的脚本
     *
     * @param entity     实体
     * @param modelIndex 索引定义；
     * @return 脚本；
     */
    private String makeIndexClause(ModelTable entity, ModelIndex modelIndex) {
        if (SwEnum.IndexType.PK.value.equalsIgnoreCase(modelIndex.getType())) return null;
        SwEnum.IndexTypeBean type = SwEnum.IndexType.instance.getByValue(modelIndex.getType());
        return "CREATE " + (SwEnum.IndexType.U.value.equalsIgnoreCase(modelIndex.getType()) ? SwEnum.IndexType.U.fullName: "") +
                " INDEX " + getDbIndexName(entity, modelIndex) +
                " ON " + entity.getSchemaTableName() + " (" + modelIndex.getFields() + ")";
    }

    /**
     * 删除已经实体已经定义的索引
     *
     * @param entity 实体
     * @return sql
     */
    private String deleteDeclaredIndices(ModelTable entity) {
        if (entity == null) {
            return "ModelTable was null and is required to delete foreign keys indices for a table";
        }
        StringBuilder retMsgsBuffer = new StringBuilder();
        // go through the relationships to see if any foreign keys need to be added
        for (ModelIndex modelIndex : entity.getIndexes()) {
            String retMsg = deleteDeclaredIndex(entity, modelIndex);
            if (retMsg != null && retMsg.length() > 0) {
                if (retMsgsBuffer.length() > 0) {
                    retMsgsBuffer.append("\n");
                }
                retMsgsBuffer.append(retMsg);
            }
        }
        if (retMsgsBuffer.length() > 0) {
            return retMsgsBuffer.toString();
        } else {
            return null;
        }
    }

    /**
     * 删除已经定义的索引
     *
     * @param entity     实体
     * @param modelIndex 索引；
     * @return sql
     */
    private String deleteDeclaredIndex(ModelTable entity, ModelIndex modelIndex) {
        String deleteIndexSql = "DROP INDEX " + entity.getSchemaTableName() + "." + getDbIndexName(entity, modelIndex);
        try {
            DbEngine.getInstance().update(deleteIndexSql);
        } catch (Exception sqle) {
            return "SQL Exception while executing the following:\n" + deleteIndexSql + "\nError was: " + sqle.toString();
        }
        return null;
    }

    private String deleteDeclaredIndex(String tableName, String indexName) {
        String deleteIndexSql = "DROP INDEX " + indexName + " ON " + tableName;
        try {
            DbEngine.getInstance().update(deleteIndexSql);
        } catch (Exception sqle) {
            return "SQL Exception while executing the following:\n" + deleteIndexSql + "\nError was: " + sqle.toString();
        }
        return null;
    }

    private String getDbIndexName(ModelTable entity, ModelIndex modelIndex) {
        return entity.getAbbr() + "_" + modelIndex.getName();
    }
}