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

import cc.smtweb.framework.core.common.SwConsts;
import cc.smtweb.framework.core.db.DbEngine;
import cc.smtweb.framework.core.db.cache.ModelDatabaseCache;
import cc.smtweb.framework.core.db.vo.ModelDatabase;
import cc.smtweb.framework.core.util.SpringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.support.JdbcUtils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Function;

/**
 * Created with IntelliJ IDEA.
 * User: AKhh
 * Date: 12-12-21 下午10:14
 * 获取连接的数据库相关信息,from ofbiz
 */
@Slf4j
public class DefaultDatabaseInfoImpl implements IDatabaseInfo {
    private static final String MODULE = DefaultDatabaseInfoImpl.class.getName();
    private DbEngine dbEngine = SpringUtil.getBean(DbEngine.class);

    @SuppressWarnings("unchecked")
    public void printDatabaseInfo() {
        dbEngine.doConn(connection -> {
            try {
                DatabaseMetaData dbData = connection.getMetaData();
                // 数据库信息
                try {
                    log.debug("Database Product Name is " + dbData.getDatabaseProductName(), MODULE);
                    log.debug("Database Product Version is " + dbData.getDatabaseProductVersion(), MODULE);
                } catch (SQLException sqle) {
                    log.debug("Unable to get Database name & version information", MODULE);
                }
                // JDBC Driver Info
                try {
                    log.debug("Database Driver Name is " + dbData.getDriverName(), MODULE);
                    log.debug("Database Driver Version is " + dbData.getDriverVersion(), MODULE);
                } catch (SQLException sqle) {
                    log.debug("Unable to get Driver name & version information", MODULE);
                }
                // Db/Driver support settings
                try {
                    log.debug("Database Setting/Support Information (those with a * should be true):", MODULE);
                    log.debug("- supports transactions    [" + dbData.supportsTransactions() + "]*", MODULE);
                    log.debug("- isolation None           [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE) + "]", MODULE);
                    log.debug("- isolation ReadCommitted  [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED) + "]", MODULE);
                    log.debug("- isolation ReadUncommitted[" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED) + "]", MODULE);
                    log.debug("- isolation RepeatableRead [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ) + "]", MODULE);
                    log.debug("- isolation Serializable   [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE) + "]", MODULE);
                    log.debug("- is case sensitive        [" + dbData.supportsMixedCaseIdentifiers() + "]", MODULE);
                    log.debug("- stores LowerCase         [" + dbData.storesLowerCaseIdentifiers() + "]", MODULE);
                    log.debug("- stores MixedCase         [" + dbData.storesMixedCaseIdentifiers() + "]", MODULE);
                    log.debug("- stores UpperCase         [" + dbData.storesUpperCaseIdentifiers() + "]", MODULE);
                    log.debug("- max table name length    [" + dbData.getMaxTableNameLength() + "]", MODULE);
                    log.debug("- max column name length   [" + dbData.getMaxColumnNameLength() + "]", MODULE);
                    log.debug("- max schema name length   [" + dbData.getMaxSchemaNameLength() + "]", MODULE);
                    log.debug("- concurrent connections   [" + dbData.getMaxConnections() + "]", MODULE);
                    log.debug("- concurrent statements    [" + dbData.getMaxStatements() + "]", MODULE);
                    log.debug("- ANSI SQL92 Entry         [" + dbData.supportsANSI92EntryLevelSQL() + "]", MODULE);
                    log.debug("- ANSI SQL92 Itermediate   [" + dbData.supportsANSI92IntermediateSQL() + "]", MODULE);
                    log.debug("- ANSI SQL92 Full          [" + dbData.supportsANSI92FullSQL() + "]", MODULE);
                    log.debug("- ODBC SQL Grammar Core    [" + dbData.supportsCoreSQLGrammar() + "]", MODULE);
                    log.debug("- ODBC SQL Grammar Extended[" + dbData.supportsExtendedSQLGrammar() + "]", MODULE);
                    log.debug("- ODBC SQL Grammar Minimum [" + dbData.supportsMinimumSQLGrammar() + "]", MODULE);
                    log.debug("- outer joins              [" + dbData.supportsOuterJoins() + "]*", MODULE);
                    log.debug("- limited outer joins      [" + dbData.supportsLimitedOuterJoins() + "]", MODULE);
                    log.debug("- full outer joins         [" + dbData.supportsFullOuterJoins() + "]", MODULE);
                    log.debug("- group by                 [" + dbData.supportsGroupBy() + "]*", MODULE);
                    log.debug("- group by not in select   [" + dbData.supportsGroupByUnrelated() + "]", MODULE);
                    log.debug("- column aliasing          [" + dbData.supportsColumnAliasing() + "]", MODULE);
                    log.debug("- order by not in select   [" + dbData.supportsOrderByUnrelated() + "]", MODULE);
                    // this doesn't work in HSQLDB, other databases? Debug.logInfo("- named parameters         [" + dbData.supportsNamedParameters() + "]", MODULE);
                    log.debug("- alter table add column   [" + dbData.supportsAlterTableWithAddColumn() + "]*", MODULE);
                    log.debug("- non-nullable column      [" + dbData.supportsNonNullableColumns() + "]*", MODULE);
                } catch (Exception e) {
                    log.error("Unable to get misc. support/setting information", e);
                }
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
            return null;
        });
    }

    //需要考虑分库的情况
    @SuppressWarnings("unchecked")
    public Map<String, TableCheckInfo> getTables() {
        final Map<String, TableCheckInfo> tables = new TreeMap<>();
        dbEngine.doConn(new Function<Connection, Object>() {
            @Override
            public Object apply(Connection connection) {
                try {
                    DatabaseMetaData dbData = connection.getMetaData();
                    if (dbData == null) {
                        return null;
                    }
                    System.out.println("获取数据库表信息...............................");
                    boolean needsUpperCase = false;
                    try {
                        needsUpperCase = dbData.storesLowerCaseIdentifiers() || dbData.storesMixedCaseIdentifiers();
                    } catch (SQLException sqle) {
                        String message = "Error getting identifier case information... Error was:" + sqle.toString();
                        log.error(message, MODULE);
                    }
                    String lookupSchemaName = null;
                    if (dbData.supportsSchemasInTableDefinitions()) {
                        lookupSchemaName = dbData.getUserName();
                    }
                    if (dbEngine.isMysql()) {//非oracle才需要
                        String dbName = dbEngine.getDbSchema();
                        Collection<ModelDatabase> list = ModelDatabaseCache.getInstance().getAll();
                        if (list != null && !list.isEmpty()) {
                            for (ModelDatabase db : list) {
                                getTableSet(dbData, dbName + "_" + db.getName(), needsUpperCase, lookupSchemaName);
                            }
                        }
                        //当前库都要去读
                        getTableSet(dbData, dbName, needsUpperCase, lookupSchemaName);
                    } else {
                        //当前库都要去读
                        getTableSet(dbData, null, needsUpperCase, lookupSchemaName);
                    }
                } catch (SQLException e) {
                    log.error(e.getMessage(), e);
                }
                return null;
            }

            private void getTableSet(DatabaseMetaData dbData, String catalog, boolean needsUpperCase, String lookupSchemaName) {
                ResultSet tableSet = null;
                try {
                    String[] types = {"TABLE"/*,"VIEW", "ALIAS", "SYNONYM"*/};
                    tableSet = dbData.getTables(catalog, lookupSchemaName, null, types);
                    while (tableSet.next()) {
                        String tableName = tableSet.getString("TABLE_NAME");
                        // for those databases which do not return the schema name with the table name (pgsql 7.3)
                        //boolean appendSchemaName = false;
                        if (needsUpperCase && tableName != null) {
                            tableName = tableName.toUpperCase();
                        }
                        //if (appendSchemaName) {
                        //    tableName = String.format("%s.%s", lookupSchemaName, tableName);
                        //}
                        String tableType = tableSet.getString("TABLE_TYPE");
                        // only allow certain table types
                        if (tableType != null &&
                                !"TABLE".equalsIgnoreCase(tableType) &&
                                !"BASE TABLE".equalsIgnoreCase(tableType) &&
                                !"VIEW".equalsIgnoreCase(tableType) &&
                                !"ALIAS".equalsIgnoreCase(tableType) &&
                                !"SYNONYM".equalsIgnoreCase(tableType)) {
                            continue;
                        }
                        try (ResultSet pkSet = dbData.getPrimaryKeys(catalog, lookupSchemaName, tableName)) {
                            String s = "";
                            while (pkSet.next()) {
                                s += "," + pkSet.getString(4);
                            }
                            TableCheckInfo table = new TableCheckInfo();
                            table.tableName = tableName;
                            table.catalog = catalog;
                            if (StringUtils.isNotEmpty(s)) {
                                table.pk = s.substring(1);
                            }
                            tables.put(catalog.toUpperCase() + "." + tableName, table);
                        }
                    }
                } catch (SQLException sqle) {
                    String message = "Error getting next table information... Error was:" + sqle.toString();
                    log.error(message, MODULE);
                } finally {
                    JdbcUtils.closeResultSet(tableSet);
                }
            }
        });
        return tables;
    }

    @SuppressWarnings("unchecked")
    //获取列信息
    public Map<String, List<ColumnCheckInfo>> getColumnInfo(final Map<String, TableCheckInfo> dbTables) {
        // if there are no tableNames, don't even try to get the columns
        final Map<String, List<ColumnCheckInfo>> colInfo = new HashMap<>();
        if (dbTables.size() == 0) {
            return colInfo;
        }
        dbEngine.doConn(new Function<Connection, Object>() {
            @Override
            public Object apply(Connection connection) {
                try {
                    DatabaseMetaData dbData = connection.getMetaData();
                    boolean needsUpperCase = false;
                    try {
                        needsUpperCase = dbData.storesLowerCaseIdentifiers() || dbData.storesMixedCaseIdentifiers();
                    } catch (SQLException sqle) {
                        String message = "Error getting identifier case information... Error was:" + sqle.toString();
                        log.error(message, MODULE);
                    }

                    String lookupSchemaName = null;
                    if (dbData.supportsSchemasInTableDefinitions()) {
                        lookupSchemaName = dbData.getUserName();
                    }

                    if (dbEngine.isMysql()) {//非oracle才需要
                        String dbName = dbEngine.getDbSchema();
                        Collection<ModelDatabase> list = ModelDatabaseCache.getInstance().getAll();
                        if (list != null && !list.isEmpty()) {
                            for (ModelDatabase db : list) {
                                String dn = SwConsts.DEF_DB_NAME.equals(db.getName()) ? "": ("_" + db.getName());
                                getColumnInfo(dbData, dbName + dn, needsUpperCase, lookupSchemaName);
                            }
                        }
                    }

//                    getColumnInfo(dbData, null, needsUpperCase, lookupSchemaName);
                } catch (SQLException e) {
                    log.error(e.getMessage(), e);
                }
                return null;
            }

            private void getColumnInfo(DatabaseMetaData dbData, String catalog, boolean needsUpperCase, String lookupSchemaName) {
                ResultSet rsCols = null;
                try {
                    rsCols = dbData.getColumns(catalog, lookupSchemaName, null, null);
                    while (rsCols.next()) {
                        ColumnCheckInfo ccInfo = new ColumnCheckInfo();

                        ccInfo.tableName = rsCols.getString("TABLE_NAME");
                        // for those databases which do not return the schema name with the table name (pgsql 7.3)
                        //boolean appendSchemaName = false;
                        // AKzz : 不在表名前加用户名.
                        //if (ccInfo.tableName != null && lookupSchemaName != null && !ccInfo.tableName.startsWith(lookupSchemaName)) {
                        //    appendSchemaName = true;
                        //}
                        if (needsUpperCase && ccInfo.tableName != null) {
                            ccInfo.tableName = ccInfo.tableName.toUpperCase();
                        }
                        //if (appendSchemaName) {
                        //    ccInfo.tableName = lookupSchemaName + "." + ccInfo.tableName;
                        //}
                        // ignore the column info if the table name is not in the list we are concerned with
                        String k = catalog.toUpperCase() + "." + ccInfo.tableName;
                        if (!dbTables.containsKey(k)) {
                            continue;
                        }
                        ccInfo.columnName = rsCols.getString("COLUMN_NAME");
                        if (needsUpperCase && ccInfo.columnName != null) {
                            ccInfo.columnName = ccInfo.columnName.toUpperCase();
                        }
                        // NOTE: this may need a toUpperCase in some cases, keep an eye on it
                        ccInfo.typeName = rsCols.getString("TYPE_NAME");
                        ccInfo.columnSize = rsCols.getInt("COLUMN_SIZE");
                        ccInfo.decimalDigits = rsCols.getInt("DECIMAL_DIGITS");
                        // NOTE: this may need a toUpperCase in some cases, keep an eye on it
                        ccInfo.isNullable = rsCols.getString("IS_NULLABLE");
                        List<ColumnCheckInfo> tableColInfo = colInfo.computeIfAbsent(k, k1 -> new ArrayList<>());

                        tableColInfo.add(ccInfo);
                    }
                } catch (SQLException sqle) {
                    String message = "Error getting table fields information... Error was:" + sqle.toString();
                    log.error(message, MODULE);
                } finally {
                    JdbcUtils.closeResultSet(rsCols);
                }
            }
        });
        return colInfo;
    }

    @SuppressWarnings("unchecked")
    //获取索引
    public Map<String, Map<String, Set<String>>> getIndexInfo(final Collection<TableCheckInfo> tableNames, final boolean include_nounique) {
        final Map<String, Map<String, Set<String>>> indexInfo = new HashMap<>();
        if (!tableNames.isEmpty()) {
            for (TableCheckInfo c : tableNames) {
                String k = c.catalog.toUpperCase() + "." + c.tableName;
                indexInfo.put(k, new HashMap<>());
            }
            dbEngine.doConn(connection -> {
                try {
                    DatabaseMetaData dbData = connection.getMetaData();
                    boolean needsUpperCase = false;
                    try {
                        needsUpperCase = dbData.storesLowerCaseIdentifiers() || dbData.storesMixedCaseIdentifiers();
                    } catch (SQLException sqle) {
                        String message = "Error getting identifier case information... Error was:" + sqle.toString();
                        log.error(message, MODULE);
                    }

//                    int totalIndices = 0;
                    String lookupSchemaName = null;
                    if (dbData.supportsSchemasInTableDefinitions()) {
                        lookupSchemaName = dbData.getUserName();
                    }

                    for (TableCheckInfo curTableName : tableNames) {
                        ResultSet rsCols = null;
                        try {
                            // false for unique, we don't really use unique indexes
                            // true for approximate, don't really care if stats are up-to-date
                            rsCols = dbData.getIndexInfo(curTableName.catalog, lookupSchemaName, curTableName.tableName, !include_nounique, true);
                            while (rsCols != null && rsCols.next()) {
                                // NOTE: The code in this block may look funny, but it is designed so that the wrapping loop can be removed
                                // skip all index info for statistics
                                if (rsCols.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic) continue;
                                //HACK: for now skip all "unique" indexes since our foreign key indices are not unique, but the primary key ones are
                                //原来的方法: 只取了NON_UNIQUE == true的索引.
                                //if (!rsCols.getBoolean ("NON_UNIQUE")) continue;
                                boolean unique = rsCols.getBoolean("NON_UNIQUE");

                                //for (int x = 1; x<=rsCols.getMetaData().getColumnCount();x++){
                                //    System.out.println(rsCols.getMetaData().getColumnName(x) + " = " + rsCols.getString(x));
                                //}

                                //System.out.println("INDEX_NAME=" + rsCols.getString("INDEX_NAME")   + " --- "+ "COLUMN_NAME=" + rsCols.getString("COLUMN_NAME") );

                                //改动, 可以取所有的索引.
                                if (include_nounique) {
                                    // 要取nouniqune的
                                } else {
                                    // 不取nouniqune的,
                                    if (!unique) continue;
                                }
                                String k = curTableName.catalog.toUpperCase() + "." + curTableName.tableName;

                                String indexName = rsCols.getString("INDEX_NAME");
                                if (needsUpperCase && indexName != null) {
                                    indexName = indexName.toUpperCase();
                                }
                                Map<String, Set<String>> tableIndexList = indexInfo.computeIfAbsent(k, k1 -> new HashMap<>());
                                Set<String> set = tableIndexList.computeIfAbsent(indexName, k1 -> new LinkedHashSet<>());
                                //                                    totalIndices++;
                                set.add(rsCols.getString("COLUMN_NAME"));
                            }
                        } catch (Exception e) {
                            log.debug("Error getting index info using lookupSchemaName " + lookupSchemaName, e);
                        } finally {
                            JdbcUtils.closeResultSet(rsCols);
                        }
                    }
                } catch (SQLException e) {
                    log.error(e.getMessage(), e);
                }
                return null;
            });
        }
        return indexInfo;
    }
}
