package org.springblade.datasource.transform.oracle;

import cn.hutool.core.collection.CollectionUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springblade.core.tool.exception.ServiceException;
import org.springblade.datasource.support.DynamicDataSourceSupport;
import org.springblade.datasource.transform.*;
import org.springblade.datasource.transform.util.ListUtils;
import org.springblade.etl.entity.bi.ReportDatasource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Locale;
import java.util.stream.Collectors;

/**
 * @author 喜欢番茄火锅
 * @date 2023/8/21 16:42
 */
@Slf4j
public class OracleCreateTableHandle {


    /**
     * 根据通用表信息创建表对象
     *
     * @param tableInfo
     * @param targetDatasource
     * @return
     * @author 王瑞
     * @date 2023/8/21
     */
    @SneakyThrows
    public void creatTable(TableInfo tableInfo, ReportDatasource targetDatasource, boolean deleteSourceTable) {
        tableInfo.setTableName(tableInfo.getTableName().toUpperCase(Locale.ROOT));
        List<ColumnInfo> columnInfo = tableInfo.getColumnInfo();

        String columSql = buildColumnSql(tableInfo);
        log.info("创建表sql 语句,sql:{}", columSql);

        String tableCommentSql = buildTableCommentSql(tableInfo.getTableName(), tableInfo.getTableComment());
        log.info("创建表注释sql 语句,sql:{}", tableCommentSql);

        List<String> columnCommentSql = buildColumnCommentSql(tableInfo.getTableName(), columnInfo);
        log.info("创建字段注释sql 语句,sql:{}", columnCommentSql);
        Connection connection = DynamicDataSourceSupport.createConnection(targetDatasource);
        Statement statement = connection.createStatement();

        ResultSet tables = connection.getMetaData().getTables(null, targetDatasource.getDbSchema(), tableInfo.getTableName(), new String[]{"TABLE", "VIEW"});
        if (tables.next()) {
//                statement.executeUpdate("DROP TABLE " + tableInfo.getTableName() + "");
            List<ColumnInfo> sourceColumn = new OracleParseTableHandle().getSourceColumn(tableInfo.getTableName(), connection.getMetaData(), targetDatasource.getDbSchema());
            List<String> collect = tableInfo.getColumnInfo().stream().map(ColumnInfo::getColumnName).collect(Collectors.toList());
            List<String> collect2 = sourceColumn.stream().map(ColumnInfo::getColumnName).collect(Collectors.toList());
            if (!ListUtils.equalLists(collect, collect2)) {
                throw new ServiceException("Oracle数据源,表名称:" + tableInfo.getTableName() + "已存在且字段不符!");
            }
            //如果检测通过，直接返回，不再创建表
            return;
        }

        statement.executeUpdate(columSql);
        statement.executeUpdate(tableCommentSql);
        for (String s : columnCommentSql) {
            statement.executeUpdate(s);
        }
        statement.close();

    }

    private String buildTableCommentSql(String tableName, String tableComment) {
        return String.format("COMMENT ON TABLE %s is '%s' ", tableName, tableComment);
    }

    private List<String> buildColumnCommentSql(String tableName, List<ColumnInfo> columnComment) {
        return columnComment.stream().map(columnInfo ->
                String.format(
                        "COMMENT ON COLUMN %s.%s is '%s' ",
                        tableName,
                        columnInfo.getColumnName(),
                        columnInfo.getColumnComments()
                )
        ).collect(Collectors.toList());
    }

    private String buildColumnSql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        tableInfo.getColumnInfo().stream().forEach(columnInfo -> fieldSql.append(String.format(
                "%s %s ,",
                columnInfo.getColumnName(),
                getColumnType(columnInfo)
        )));
        StringBuilder primaryKey = new StringBuilder();
        List<IndexInfo> collect = null;
        if (CollectionUtil.isNotEmpty(tableInfo.getIndexInfo())) {
            collect = tableInfo.getIndexInfo().stream().filter(IndexInfo::getPrimaryKey).collect(Collectors.toList());
            if (CollectionUtil.isNotEmpty(collect)) {
                collect.get(0).getIndexColumn().stream().forEach(v -> {
                    primaryKey.append(v.getColumnName() + ",");
                });
                if (StringUtils.isNotBlank(primaryKey)) {
                    primaryKey.deleteCharAt(primaryKey.length() - 1);
                    fieldSql.append(String.format("PRIMARY KEY (%s)", primaryKey));
                }
            }
        } else {
            fieldSql.deleteCharAt(fieldSql.length() - 1);
        }
        return String.format("CREATE TABLE %s (%s) ", tableInfo.getTableName(), fieldSql, tableInfo.getTableComment());
    }


    public String getColumnType(ColumnInfo columnInfo) {
        ColumnEnum columnEnum = columnInfo.getColumnType();
        Integer columnSize = columnInfo.getColumnSize() == null ? 9999 : columnInfo.getColumnSize();
        Integer decimalDigits = columnInfo.getDecimalDigits();
        DefColumnSize defColumnSize = null;
        switch (columnEnum) {

            case DECIMAL:
                Integer p = TransformUtil.getColumnSizeOrDecimalDigits(columnSize, 38, 38, 1);
                Integer s = TransformUtil.getColumnSizeOrDecimalDigits(decimalDigits, 0, 127, -83);
                return String.format("NUMBER(%s,%s)", p, s);
            case STRING:
                if (columnSize != null && columnSize < 1) {
                    return "VARCHAR2(4000)";
                }
                Integer string = TransformUtil.getColumnSizeOrDecimalDigits(columnSize * 2, 2000, 4000, 1000);
                return String.format("VARCHAR2(%s)", string);
            case LONG:
                Integer lp = TransformUtil.getColumnSizeOrDecimalDigits(columnSize, 38, 38, 1);
                Integer ls = TransformUtil.getColumnSizeOrDecimalDigits(decimalDigits, 0, 127, -83);
                return String.format("NUMBER(%s,%s)", lp, ls);
            case INTEGER:
            case DOUBLE:
                Integer dp = TransformUtil.getColumnSizeOrDecimalDigits(columnSize, 38, 38, 1);
                Integer ds = TransformUtil.getColumnSizeOrDecimalDigits(decimalDigits, 8, 127, -83);
                return String.format("NUMBER(%s,%s)", dp, ds);
            case FLOAT:
                Integer f = TransformUtil.getColumnSizeOrDecimalDigits(columnSize, 126, 126, 1);
                return String.format("FLOAT(%s)", f);
            case DATE:
                return String.format("DATE");
            case BOOL:
                Integer bool = TransformUtil.getColumnSizeOrDecimalDigits(columnSize, 6, 4000, 5);
                return String.format("VARCHAR2(%s)", bool);
            default:
                throw new ServiceException("暂不支持该数据库类型!");
        }

    }
}
