package org.springblade.datasource.transform.demo;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.druid.util.JdbcUtils;
import com.google.common.collect.Lists;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springblade.datasource.enums.DsTypeEnum;
import org.springblade.datasource.support.DynamicDataSourceSupport;
import org.springblade.datasource.transform.*;
import org.springblade.datasource.transform.general.GeneralModel;
import org.springblade.datasource.transform.mysql.SQLColumnTypeEnum;
import org.springblade.etl.entity.bi.ReportDatasource;

import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

/**
 * @author 喜欢番茄火锅
 * @date 2023/8/16 17:41
 */
@Slf4j
//@Component
public class MysqlTransformDemo {

    protected final Map<IndexEnum, String> indexTypeMap = new ConcurrentHashMap<>();

    {
        //数据类型
        indexTypeMap.put(IndexEnum.PRIMARY, "PRIMARY KEY");
        indexTypeMap.put(IndexEnum.NORMAL, "INDEX");
        indexTypeMap.put(IndexEnum.UNIQUE, "UNIQUE INDEX");
    }

    /**
     * 根据通用表信息创建表对象
     *
     * @param tableInfo
     * @return
     * @author 王瑞
     * @date 2023/8/16
     */
    @SneakyThrows
    public void creatTable(TableInfo tableInfo, ReportDatasource datasource) {

        String columSql = buildColumSql(tableInfo.getTableName(), tableInfo.getTableComment(), tableInfo.getColumnInfo());
        log.info("创建表sql 语句,sql:{}", columSql);
        List<String> indexSql = buildIndexSql(tableInfo.getTableName(), tableInfo.getIndexInfo());
        log.info("创建表索引sql 语句,sql:{}", indexSql);
        Connection connection = null;
        try {
            connection = DynamicDataSourceSupport.createConnection(datasource);
            Statement statement = connection.createStatement();
            ResultSet tables = connection.getMetaData().getTables(null, null, tableInfo.getTableName(), null);
            if (tables.next()) {
                statement.executeUpdate("DROP TABLE " + tableInfo.getTableName() + ";");
            }

            statement.executeUpdate(columSql);
            indexSql.stream().forEach(sql -> {
                try {
                    statement.executeUpdate(sql);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据源类型
     *
     * @return
     */
    public DsTypeEnum dsType() {
        return DsTypeEnum.mysql;
    }

    private String buildColumSql(String tableName, String tableComment, List<ColumnInfo> columnInfos) {
        StringBuilder fieldSql = new StringBuilder();
        columnInfos.stream().forEach(columnInfo -> fieldSql.append(String.format(
                "%s %s comment '%s',",
                columnInfo.getColumnName(),
                getColumnType(columnInfo),
                columnInfo.getColumnComments()
        )));
        fieldSql.deleteCharAt(fieldSql.length() - 1);
        return String.format("CREATE TABLE %s (%s) COMMENT '%s' ;", tableName, fieldSql, tableComment);
    }

    private List<String> buildIndexSql(String tableName, List<IndexInfo> indexInfos) {
        List<String> list = Lists.newArrayList();

        if (CollectionUtil.isEmpty(indexInfos)) {
            return list;
        }
        indexInfos.stream().forEach(indexInfo -> {
                    StringBuilder indexSql = new StringBuilder();
                    indexSql.append(String.format(
                            "ALTER TABLE %s ADD %s %s ( %s ) ;",
                            tableName,
                            getIndexType(indexInfo),
                            indexInfo.getPrimaryKey() ? "" : indexInfo.getIndexName(),
                            getIndexColumn(indexInfo.getIndexColumn())));
                    list.add(indexSql.toString());
                }

        );
        return list;
    }

    private String getIndexColumn(List<IndexInfo.IndexColumn> list) {
        StringBuilder sb = new StringBuilder();
        list.stream().sorted((Comparator.comparing(IndexInfo.IndexColumn::getOrdinalPosition))).forEach(v -> {
            sb.append(v.getColumnName() + ",");
        });
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    private String getIndexType(IndexInfo indexInfo) {
        StringBuilder sb = new StringBuilder();
        if (indexInfo.getPrimaryKey()) {
            return " PRIMARY KEY ";
        }
        if (!indexInfo.getNonUnique()) {
            sb.append(" UNIQUE ");
        }
        sb.append(" INDEX ");
        return sb.toString();
    }

    public String getColumnType(ColumnInfo columnInfo) {
        ColumnEnum columnEnum = columnInfo.getColumnType();
        switch (columnEnum) {
            case DECIMAL:
                return "decimal(30, 4)";
            case STRING:
                Integer columnSize = columnInfo.getColumnSize();
                if (columnSize != null && columnSize == 65535) {
                    return "text";
                }
                return "varchar(255)";
            case LONG:
                return "bigint(20)";
            case INTEGER:
                return "int(11)";
            case DOUBLE:
                return "double";
            case FLOAT:
                return "float";
            case DATE:
                return "datetime";
            default:
                return "";
        }

    }

    /**
     * 将源信息转换成通用表数据
     *
     * @return {@link TableInfo}
     * @author 王瑞
     * @date 2023/8/16
     */
    public TableInfo parseTableInfo(ReportDatasource datasource, List<GeneralModel> model, String tableViewName, String tableComment) {
        if (datasource == null) {
            return null;
        }
        Connection connection = null;
        try {
            connection = DynamicDataSourceSupport.createConnection(datasource);
            DatabaseMetaData metaData = connection.getMetaData();
            String schemaPattern = datasource.getDsDb();
            TableInfo tableInfo = new TableInfo();
            tableInfo.setTableName(tableViewName);
            tableInfo.setTableComment(tableComment);
            tableInfo.setColumnInfo(getColumnInfo(tableViewName, metaData, schemaPattern));
            tableInfo.setIndexInfo(getIndexInfo(tableViewName, metaData, schemaPattern));

            return tableInfo;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            JdbcUtils.close(connection);
        }
        return null;
    }


    private List<IndexInfo> getIndexInfo(String tableViewName, DatabaseMetaData metaData, String schemaPattern) throws SQLException {
        ResultSet rs = null;
        List<IndexInfo> list = new ArrayList<>();
        HashMap<String, IndexInfo> map = new HashMap<>();

        rs = metaData.getIndexInfo(null, schemaPattern, tableViewName, false, false);
        ResultSetMetaData md2 = rs.getMetaData();
        while (rs.next()) {
//                System.out.println(md2.getColumnName(i) + "==" + rs.getObject(i));

            String indexName = rs.getString("INDEX_NAME");

            IndexInfo indexInfo = map.get(indexName);
            if (indexInfo != null) {
                List<IndexInfo.IndexColumn> indexColumn = indexInfo.getIndexColumn();
                indexColumn(rs, indexInfo, indexColumn);
                continue;
            }
            indexInfo = new IndexInfo();
            if ("PRIMARY".equals(indexName)) {
                indexInfo.setPrimaryKey(true);
            }
            indexInfo.setIndexName(indexName);
            indexInfo.setNonUnique(rs.getBoolean("NON_UNIQUE"));

            ArrayList<IndexInfo.IndexColumn> indexColumns = Lists.newArrayList();
            indexColumn(rs, indexInfo, indexColumns);
            indexInfo.setIndexColumn(indexColumns);
            map.put(indexName, indexInfo);

            System.out.println("===================");

        }
        return map.keySet().stream().map(map::get).collect(Collectors.toList());
    }

    private void indexColumn(ResultSet rs, IndexInfo indexInfo, List<IndexInfo.IndexColumn> indexColumn) throws SQLException {
        IndexInfo.IndexColumn indexColumn2 = indexInfo.new IndexColumn();
        indexColumn2.setColumnName(rs.getString("COLUMN_NAME"));
        indexColumn2.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
        indexColumn2.setAscOrDesc(rs.getString("ASC_OR_DESC"));

        indexColumn.add(indexColumn2);
    }

    private List<ColumnInfo> getColumnInfo(String tableViewName, DatabaseMetaData metaData, String schemaPattern) throws SQLException {
        ResultSet rs = metaData.getColumns(null, schemaPattern, tableViewName, "%");
        List<ColumnInfo> list = new ArrayList<>();
        while (rs.next()) {
            ColumnInfo columnInfo = new ColumnInfo();

            columnInfo.setColumnComments(rs.getString("REMARKS"));
            columnInfo.setDataType(rs.getString("TYPE_NAME"));
            columnInfo.setColumnName(rs.getString("COLUMN_NAME"));
            columnInfo.setColumnSize(rs.getInt("COLUMN_SIZE"));
            columnInfo.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
            columnInfo.setColumnType(SQLColumnTypeEnum.resolve(rs.getInt("DATA_TYPE")).getColumnEnum());
            //IS_NULLABLE - YES/NO  NULLABLE - 1(允许为空)/2(不确定)/0(不允许为空)
            columnInfo.setNullAbleStatus("yes".equalsIgnoreCase(rs.getString("IS_NULLABLE")) ? 1 : 0);
            list.add(columnInfo);
        }
        rs.close();
        return list;
    }


}
